BASIC SQL SELECT STATEMENT

BASIC SQL SELECT STATEMENT

• A SELECT Statement just  projection of the data from the base or master table.
• The SQL SELECT statement returns a result set of records from one or more tables. A SELECT statement retrieves zero or more rows from one or more database tables or database views.

SELECT STATEMENT SYNTAX

SELECT * FROM TABLE_NAME;

SELECT/ FROM    : CLAUSE 
*               : All Column

SELECT * FROM Employees;

SELECT/FROM  : CLAUSE
*            : ALL COLUMN
Employees    : TABLE NAME.

It Means all column from employees table.Its just a projection of the data with arithmetic operation, Alias Name, NULL, Distinct, Unique etc.

Example 

SELECT First_Name FROM Employees;

Its Show the First_Name column from the employees table.

Arithmetic Operation in select Statement:

SELECT First_Name,Last_Name,Salary, Salary+1000 FROM Employees;

The above Query shows the following output.

Here there is one new column added into the table but its only projection of the data. The data of the new column not stored in the database. 

    
Note: By Using arithmetic operation in the SELECT Statement BODMOS Rule automatically applied by the operator precedence.

Operator Precedence Rule 
() / * + - 

Example: Consider the current salary is 1000 now increment the salary 100 for every month what is the annual salary for the employee?

Select 12*1000+100 from dual;

if the above query result shows 12100 because of BODMOS Rule.

Select 12*(1000+100) from dual;


Its shows the correct output 13200.

ALIAS NAME: 

• Column Renaming 
• Mostly Used with Expressions
• AS is optional
• Double Quote is optional(When Space between the Alias Name Double Quote is Mandatory)
• With in the Double Quotes the text can display with  format given  by the user. Eg:"Name","NAME" , "NaMe"

select first_name AS "NAME" from employees; 
-- The Columns name changed into NAME

select first_name "Name" from employees;
-- The Columns name changed into Name With in the quotes case sensitive

select first_name name from employees; 
-- The Columns name changed into NAME

select first_name Employee_Name from employees;
-- The Columns name changed into EMPLOYEE_NAME

select first_name Employee Name the employees;

-- ORA-00923: FROM keyword not found where expected Quote is mandatory when space between the alias name  

SELECT First_Name,Last_Name,Salary, Salary+1000 "UPDATED SALARY" FROM Employees;

Here the Column name change into the alias name Updated Salary.

NULL:

Null Values represents unknown or unavailable values

Condition 

NULL |= NULL
NULL |= SPACE
NULL |= SPECIAL CHARACTER
NULL |= NULL [Each Null is Unique]
NULL with(=,+,-,*,/) arithmetic values also null.

Arithmetic Operation with NULL 

Example Query : 

select first_name, Salary,Commission_pct,Commission_pct+5 from employees; 

Here the Commission_pct+5 COLUMNS represents adding only if commission is a value else the value is null only.

DISTINCT OR UNIQUE:

To eliminate the duplicate of records or data in the table.  

SELECT First_Name FROM Employees;

After running this queries it returns 107 rows

SELECT DISTINCT First_Name FROM Employees;

After running this queries it returns 91  rows. Remaining Rows or duplicate. i.e some names are repeated. 

CONCATENATION :

Its used to merge the two or more column data into a single one in the select statement.

SELECT First_name || Salary "Name & Salary" from employees;

Similarly

SELECT First_name ||' Salary is '|| Salary "Name & Salary" from employees;

Basic Interview Questions SQL and Select Statement: Click Here