SELECT STATEMENT

Basic Select Syntax : 

SELECT column_name1,column_name2… column_name_n FROM table-name
[WHERE Clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause];

• The SELECT query statement is used to retrieve stored data from a database in the form of table-like structure called result set.
• It is one of the largest use query/statement.

          

Example : How to show/display all existing users in RDBMS.

SQL> select * from all_users; (Press Enter)
------------------------------------------------------------
SQL> select * from all_users order by created; (Press Enter)

Example : How to display all created tables list in (a logged in users) RDBMS.

SQL> select * from tab; (Press Enter)

Example : How to display all records or fields data of a table (say here ’employee’) in RDBMS.

SQL> select * from employee; (Press Enter)
------------------------------------------
SQL> select from employee; (Press Enter)

Example : How to display all specific columns or fields data of a table (say here ’employee’) in RDBMS.

SQL> select emp_id, emp_dept, emp_name, emp_sal from employee; (Press Enter)

Example : How to display first top 10 highest salaried records from a table (say here ’employee’) in RDBMS.

SQL> select TOP 10 emp_id, emp_dept, emp_name, emp_sal from employee 
order by emp_sal desc; (Press Enter)

Example : How to display all except first top 20 highest salaried records from a table (say here ’employee’) in RDBMS.

SQL> select emp_id, emp_dept, emp_name, emp_sal from employee order by emp_sal desc 
offset 20 rows; (Press Enter)

Example : How to display the top 10 to 25 highest salaried records from a table (say here ’employee’) in RDBMS.

SQL> select emp_id, emp_dept, emp_name, emp_sal from employee order by emp_sal desc
offset 10 rows 
fetch next 15 rows only; (Press Enter)

Example : How to display specific columns or fields data from a table having name like as SQL keyword (say here ’Order’) in RDBMS.

SQL> select order_id, order_date, order_qty from [order]; (Press Enter)

NB: Here order table is inside big bracket, mainly due to it is also SQL keyword.

Example : SQL Query/Statement to display extra Bonus Salary 2500 than Basic Salary of employee table in RDBMS.

SQL> select emp_id, emp_name, emp_sal as Basic_Salary, emp_sal+2500 as Bonus_Salary from employee; (Press Enter)

Example : SQL Query/Statement to display 1 year increment salary 500/months from Basic Salary of employee table in RDBMS.

SQL> select emp_id, emp_name, emp_sal as Basic_Salary, 12 * (emp_sal+500) as Increment_Salary from employee; (Press Enter)

Example : SQL Query/Statement to display per annum salary of each employee of a company from employee table in RDBMS.

SQL> select emp_id, emp_name, emp_sal * 12 as Annual_Salary from employee; (Press Enter)

Example : SQL Query/Statement to display output of more than one fields in one  column (concatenate) of an employee table in RDBMS.

SQL> select emp_first_name || emp_middle_name as "Employee_Name" from employee; (Press Enter)

Example : SQL Query/Statement to display all the employees with their post name as output in the form of a message such as  “Robert is a Manager.” from an employee table in RDBMS.

SQL> select emp_name ||' ' || 'is a'|| emp_dept || '.' as "Employee_Details" from employee; (Press Enter)

Aggregate Functions

Example : SQL Query/Statement to display the last name field of employee table in Capital Letters in RDBMS.

SELECT UPPER(emp_last_name) AS SirName
 FROM employee;
----------------------------------------------------------------
SELECT lower(emp_first_name) FROM employee;

Example : SQL Query/Statement to display the full name in capital letters using two different fields first name & last name with single space from employee table in RDBMS.

SELECT UPPER(emp_first_name) + ' ' + UPPER(emp_last_name) AS FullName
 FROM employee;

Example : SQL Query/Statement to display the gross total amount  salary expend by a company having employee table in RDBMS.

SQL> select sum(emp_sal) from employee; (Press Enter)
----------------------------------------------------------------
SQL> select sum(emp_sal) from employee where emp_dept="Manager"; (Press Enter)

Example : SQL Query/Statement to display total number of rows/tuples of employee table in RDBMS.

SQL> select count(*) from employee; (Press Enter)

Example : SQL Query/Statement to display total number of employees in a company having employee table in RDBMS.

SQL> select count(emp_name) from employee; (Press Enter)

Example : SQL Query/Statement to display total number of departments in a company having employee table in RDBMS.

SQL> select count(distinct emp_dept) from employee; (Press Enter)

Example : SQL Query/Statement to display maximum, minimum & average salary of an employee of a company from employee table in RDBMS.

SQL> select max(emp_sal), min(emp_sal),avg(emp_sal) from employee; (Press Enter)
----------------------------------------------------------------
SQL> select max(emp_sal) as Maximum_Salary, min(emp_sal) as Minimum_Salary, avg(emp_sal) as Average_Salary from employee; (Press Enter)
----------------------------------------------------------------
SQL> select max(emp_sal) "Maximum_Salary", min(emp_sal)  "Minimum_Salary", avg(emp_sal) "Average_Salary" from employee; (Press Enter)
----------------------------------------------------------------
SQL> select max(distinct emp_sal), min(distinct emp_sal) from employee; (Press Enter) 

NB : [To display single maximum & minimum value from multiple same maximum & minimum values.]

Example : SQL Query/Statement to display the highest salary paid by the company in the year 2017 from employee table in RDBMS.

SELECT MAX(emp_salary)
  FROM employee
WHERE YEAR(emp_sal_date) = 2017;

NB : Here, YEAR is a keyword to extract year from a date value.MAX & MIN function applied with number, string and date values.

Example : SQL Query/Statement to display the last salary paid by the company in the year 2017 from employee table in RDBMS.

SELECT MAX(emp_sal_date)
  FROM employee
WHERE YEAR(emp_sal_date) = 2017;

Where Clause

Example : How to display employee id and name of employee whose salary is greater than 15000 from employee table in RDBMS.

SQL> select emp_id, emp_name from employee where emp_sal>15000; (Press Enter)
----------------------------------------------------------------
SQL> select emp_id, emp_name from employee where emp_dept="Manager"; (Press Enter)
----------------------------------------------------------------
SQL> select emp_id, emp_name from employee where emp_sal>15000 order by emp_sal; (Press Enter)
----------------------------------------------------------------
SQL> select emp_id, emp_name from employee where emp_sal>15000 order by emp_sal desc; (Press Enter)

Example : SQL Query/Statement to display annual salary of Robert John from employee table in RDBMS.

SQL> select emp_id, emp_name, emp_sal * 12 from employee where emp_name="Robert John"; (Press Enter)

Order by Clause

Example : SQL Query/Statement to display employee id and name of employee whose salary is less than 15000  in ascending order from employee table in RDBMS.

SQL> select emp_id, emp_name from employee 
where emp_sal<15000 
order by emp_sal; (Press Enter)
---------------------------------------------------------------
SQL> select emp_id, emp_name from employee 
where emp_sal<15000 
order by emp_sal asc; (Press Enter)
---------------------------------------------------------------
SQL> select emp_id, emp_name from employee 
where emp_sal<15000 
order by emp_dept, emp_addr; (Press Enter)

NB: Output first arranged by emp_dept and then by emp_addr.

Example : SQL Query/Statement to display employee id and name of employee whose salary is less than 15000  in descending order from employee table in RDBMS.

SQL> select emp_id, emp_name from employee 
where emp_sal<15000 
order by emp_sal desc; (Press Enter)

Example : SQL Query/Statement to display employee id, name of employee, name of department  whose salary is greater than 15000  in ascending & descending order from employee table in RDBMS.

SQL> select emp_id, emp_dept, emp_name from employee 
where emp_sal>15000 
order by emp_sal asc, emp_dept desc; (Press Enter)

Group by Clause

[NB : Normally applies with aggregate SQL functions.]

Example : SQL Query/Statement to count the total number of employees in each department of a company in employee table in RDBMS.

SQL> select count(emp_id) from employee 
group by emp_dept; (Press Enter)

Example : SQL Query/Statement to list the total number of employees in each department of a company in employee table in RDBMS.

SQL> select count(emp_id), emp_name, emp_dept from employee 
group by emp_dept
order by count(emp_id) desc; (Press Enter)

Having Clause

[NB : Similar as ‘where’ clause but works with group by clause in grouping records. Both ‘where’ and ‘group by’ clause can be used in the same query]

Example : SQL Query/Statement to list the number of employees in each department of a company having number less than 5 in employee table in RDBMS.

SQL> select count(emp_id), emp_name, emp_dept from employee 
group by emp_dept
having count(emp_id) < 5
order by count(emp_id) desc; (Press Enter)

Example : SQL Query/Statement to list the number of employees in each department except Manager of a company having number less than 5 in employee table in RDBMS.

SQL> select count(emp_id), emp_name, emp_dept from employee
where emp_dept <> 'Manager' 
group by emp_dept
having count(emp_id) < 5
order by count(emp_id) desc; (Press Enter)

Example : SQL Query/Statement to list the number of employees in each department with salary between 20,000 and 27,000 except Manager of a company having number less than 5 in employee table in RDBMS.

SQL> select count(emp_id), emp_name, emp_dept from employee
where emp_dept <> 'Manager' 
group by emp_dept
having count(emp_id) < 5
 AND emp_sal BETWEEN 20000 and 27000
order by count(emp_id) desc; (Press Enter)

And, Or, Not Clause

Example : How to display those specific records/data having name = Robert and salary = 15000 from a table employee in RDBMS.

SQL> select * from employee 
   where emp_name="Robert" AND emp_salary=15000; (Press Enter)
---------------------------------------------------------------
SQL> select emp_id, emp_name, emp_sal from employee 
   where emp_name="Robert" and emp_salary=15000; (Press Enter)

Example : SQL statement to display all the salary details of employees from Manager or Clerk department from employee table in RDBMS.

SQL> select * from employee 
   where emp_dept="Manager" OR emp_dept="Clerk"; (Press Enter)
---------------------------------------------------------------
SQL> select emp_id, emp_name, emp_sal from employee 
   where emp_dept="Manager" or emp_dept="Clerk"; (Press Enter)

Example : SQL statement to display all the department details except Manager department from employee table in RDBMS.

SQL> select * from employee 
   where NOT emp_dept="Manager"; (Press Enter)
---------------------------------------------------------------
SQL> select emp_id, emp_name, emp_sal from employee 
   where NOT emp_dept="Manager"; (Press Enter)

Example : SQL statement to display those records of employee whose salary not lie in between 13000 to 27000 from employee table in RDBMS.

SQL> select emp_id, emp_name, emp_sal, emp_dept from employee 
   where NOT(emp_sal=13000 AND emp_sal=27000)
   order by emp_sal; (Press Enter)

Like Clause

Example : SQL Query/Statement to display employee details where name start with Ra . . . letters from employee table in RDBMS.

SQL> select * from employee 
   where emp_name LIKE 'Ra%'
   order by emp_name; (Press Enter)

NB: Use % for zero/any string matches (like * symbol) and _ for single character match (like ? symbol).

Example : SQL Query/Statement to display employee details where name start with Ra . . .  and end with . . . te letters from employee table in RDBMS.

SQL> select * from employee 
   where emp_name LIKE 'Ra%'
 AND emp_name LIKE '%te'
   order by emp_name; (Press Enter)

Example : SQL Query/Statement to display employee details whose name start with P letter after that there are only four letters. Find it from employee table in RDBMS.

SQL> select * from employee 
   where emp_name LIKE 'P____'
 order by emp_name; (Press Enter)

In, Between Clause

Example : SQL Query/Statement to display employee id, name and salary of employee having Manager, Clerk and Supervisor department of a company in employee table in RDBMS.

SQL> select emp_id, emp_name, emp_sal from employee where emp_dept IN ('Manager','Clerk','Supervisor')order by emp_sal; (Press Enter)

Example : SQL Query/Statement to display employee details of all departments except Clerk and Supervisor of a company in employee table in RDBMS.

SQL> select emp_id, emp_name, emp_sal from employee 
where emp_dept NOT IN ('Clerk','Supervisor')order by emp_name; (Press Enter)

Example : SQL Query/Statement to display employee details having age between 25 to 45 from employee table in RDBMS.

SQL> select emp_id, emp_name, emp_dept, emp_sal from employee where emp_age BETWEEN 25 AND 45
order by emp_age; (Press Enter)

NB: Between clause include starting and ending values also (>= AND <=).

Example : SQL Query/Statement to display employee details having age not between 25 to 45 from employee table in RDBMS.

SQL> select emp_id, emp_name, emp_dept, emp_sal from employee where emp_age NOT BETWEEN 25 AND 45
order by emp_age; (Press Enter)

Example : SQL Query/Statement to display the salary details of employee between two date 5/25/2017 to 8/23/2019 from employee table in RDBMS.

SQL> select emp_id, emp_name, emp_dept, emp_sal from employee where emp_sal_date BETWEEN '5/25/2017' AND '8/23/2019'
order by emp_sal_date; (Press Enter)

Null Clause

Example : SQL Query/Statement to List those records of employee whose Fax Number is not available in employee table in RDBMS.

SQL> select emp_id, emp_name, emp_dept, emp_sal from employee where emp_fax_num IS NULL
Order by emp_name; (Press Enter)

Example : SQL Query/Statement to List those records of employee whose Fax Number is must available in employee table in RDBMS.

SQL> select emp_id, emp_name, emp_dept, emp_sal from employee where emp_fax_num IS NOT NULL
Order by emp_name; (Press Enter)

Alias Clause

[NB: used to short the name of table/columns in a database. Useful in joining and sql functions. It only exists for the duration of the query execution.] 

Syntax :

SQL> SELECT column_name1 AS aliasname
1, column_name2 AS      
     aliasname
2, ---
  FROM table_name aliasname
 WHERE condition ---;

Any, All Clause

[NB : used with WHERE or HAVING clause and operate in sub-queries and gives multiple values. ANY clause returns true if any one sub-query values matches the given condition like OR and ALL clause returns true if all of the sub-query values match the condition like AND.]

Syntax :

Any

SQL> SELECT column_names FROM table-name
WHERE column-name OperatorSymbol ANY
(SELECT column-name
  FROM table-name
  WHERE condition
)

All

SQL> SELECT column_names FROM table-name
WHERE column-name OperatorSymbol ALL
(SELECT column-name
  FROM table-name
  WHERE condition
)

NB : OperatorSymbol may be = > >= < <= .

Exists Clause

[NB : checks for the existence of any records in a sub-query and gives true if the sub-query returns one or more records.]

Syntax :

SQL> SELECT column-names FROM table-name
WHERE EXISTS
    (SELECT column-name FROM table-name 
      WHERE condition);

SELECT INTO STATEMENT

NB : This statement copies data/records from a table for new fresh table.

Example : SQL Query/Statement to copy all the employee records having department= Manager into new table ManagerEmployee from employee table in RDBMS.

SQL> SELECT * INTO ManagerEmployee
  FROM Employee
 WHERE emp_dept = 'Manager';
---------------------------------------------------------------
SQL> SELECT emp_id, emp_name, emp_dept, emp_sal INTO  
     ManagerEmployee
  FROM Employee
 WHERE emp_dept = 'Manager';

INSERT INTO SELECT STATEMENT

NB : This statement copies data/records from a table to another table. It requires datatype matching in both the tables.

Example : SQL Query/Statement to copy all the employee records having department= Manager into new table ManagerEmployee from employee table in RDBMS.

SQL> INSERT INTO customer (cust_id, cust_name, cust_addr, cust_mob) SELECT(emp_id, emp_name, emp_addr, emp_mob) 
  FROM Employee
 WHERE emp_dept = 'clerk';

SQL INJECTION

- SQL Injection is a technique to inject/insert/place malicious SQL codes inside the created database to disturb the normal functioning of database either by modifying it or destroying it or for hacking purpose.
- It is one of the most common web hacking techniques today.