Skip to main content

100 SQL queries which You should learn

1. Query to see list of all table from database
SELECT *FROM TAB;
2.Viewing all column from table
SELECT *FROM employees;
3.Viewing specific column from table
SELECT First_name, last_name FROM employees;

g2

4.Giving alias name to column
SELECT first_name, salary "monthly salary" FROM employees

g3

5. Using concatenation operator in Select statement
SELECT first_name || last_name FROM employees

g4

6.Display Unique value from column
SELECT DISTINCT department_id FROM employees

g5

7.Display employees name whose salary greater than 12000
SELECT last_name, salary
FROM employees
WHERE salary>12000;
8. Display employee whose employee id is 176
SELECT last_name, Employee_id
FROM employees
WHERE employee_id=176;
9.Display employees name who are earning between 5000 and 10000
SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 5000 AND 10000;
10. Display employee whose last name is Matos or Taylor
SELECT last_name,employee_id,hire_date
FROM employees
WHERE last_name IN(‘Matos’,’Taylor’);
11. Display employee name who join in 17 feb 1996
SELECT last_name FROM employees WHERE hire_date = '17-FEB-96' ;
12. Display employee who is working in Department no 20 or 50
SELECT first_name, department_id FROM employees WHERE department_id IN(20,50)
13. Display employee who has no manager
SELECT first_name, last_name FROM employees WHERE manager_id IS NULL;
14.Display employee whose name end with a
SELECT first_name FROM employees WHERE first_name LIKE '%a';
15.Display employee whose name start with K
SELECT first_name FROM employees WHERE first_name LIKE 'K%';
16. Dispaly employees name whose name start with K and end with n
SELECT first_name FROM employees WHERE first_name LIKE 'K%n';
17.Display employee whose salary greater than 10000 and Job_id ending with N

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary >= 10000
AND job_id LIKE '%N' ;

18.Dispaly employee whose job_id IT_PROG,SH_CLERK,SA_REP
SELECT last_name, job_id
FROM employees
WHERE job_id
NOT IN ('IT_PROG', 'SH_CLERK', 'SA_REP') ;

19. Dispaly employee name in accending order
SELECT last_name, job_id
FROM employees
ORDER BY last_name;

or

SELECT last_name, job_id
FROM employees
ORDER BY last_name ASC;

20. Dispaly employee name and salary from employess table in descending order
SELECT last_name,salary
FROM employees
ORDER BY salary DESC;
21.Dispaly employee name along with annual salary
SELECT employee_id, last_name, salary*12 "Annual Salary"
FROM employees
ORDER BY "Annual Salary" ;
22.Dispaly employee name in lower case
SELECT LOWER(First_name) FROM employees;

lower

23. display employee name in upper case
SELECT UPPER(First_name) FROM employees;

upper-case

24. Dispaly employee name in camel-case
SELECT INITCAP(First_name) FROM employees;

initcap

25. concat two string Bhavesh and Lakhani
SELECT concat('Bhavesh','Lakhani') FROM dual;
Result value:BhaveshLakhani
26. Concat first_name and last_name in employees table
SELECT CONCAT(First_name,last_name) FROM employees;
27.Display employee name and length of his name.
SELECT  First_name, LENGTH(First_name) FROM employees
28.Display employee whose name length is 4 character.
SELECT  First_name, LENGTH(First_name)
FROM employees
WHERE LENGTH(First_name)=4;
29. Replacing character in String
SELECT REPLACE ('oracle', 'o','p') FROM dual;
Result value:pracle
30. Removing First character from String
SELECT TRIM('o' FROM 'oracle') FROM dual;
Result value:racle
31.SUBSTR function
SELECT SUBSTR('Hello World',1,5) FROM dual;
Result value:Hello
32.SUBSTR function
SELECT SUBSTR('Hello World',7,5) FROM dual;
result value:world
33.SUBSTR function
SELECT SUBSTR('Hello World',-2,4) FROM dual;
result value:ld
34.
SELECT SUBSTR('Hello World',-5,5) FROM dual;
result value:world
35. SUBSTR function
SELECT SUBSTR('HELLO WORLD',1) FROM DUAL;
36.Display employee whose name start from A using substr function?
SELECT First_name FROM employees WHERE SUBSTR(first_name,1,1)='A';
37.substr function
SELECT INSTR('Hello World','l',1,2) FROM DUAL;
result value:4
38.INSTR function
SELECT INSTR('Hello World','l',-1,2) FROM DUAL;
result value:4
39.  Substr function
SELECT INSTR('Hello World','l',-1,-2) FROM dual;
result value:ERROR at line 2: ORA-01428: argument '-2' is out of range General Function:
40.Display sysdate in DD/MM/YYYY format
SELECT TO_CHAR(sysdate,'dd/mm/yyyy') FROM DUAL;
result value: 18/11/2016
41. List all the employs to join a organization in the month of dec?
SELECT First_name, Hire_date
FROM employees
WHERE TO_CHAR (hire_date,'mon')='dec'
42.Display employees who joined on sunday?
SELECT first_name,hire_date 
FROM employees 
WHERE TO_CHAR(hire_date,'DY')='SUN';
43.Dispaly employee salary like $458498
SELECT TO_CHAR(salary, '$99,999.00') SALARY
FROM employees
WHERE last_name = 'Ernst';
44.Display employees name of department_id 80 and append :’_US’ Postfix
  SELECT last_name,
  UPPER(CONCAT(SUBSTR (LAST_NAME, 1, 8), '_US'))
  FROM employees
  WHERE department_id = 60;
45.NVL
SELECT last_name,salary,commission_pct,
        salary + NVL(commission_pct, 0) "total sal"
FROM employees
46.NVL2
SELECT last_name, salary, commission_pct,
NVL2(commission_pct,'SAL+COMM', 'SAL') income
FROM employees WHERE department_id =80;

47. implement query to increase salary of employees with below criteria

a) If the job is IT_PROG increment is 10%.
b) If the job is ST_CLERK increment is 15%.
b) If the job is SA_REP increment is 20%.

SELECT last_name, job_id, salary,
CASE job_id
WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary 
END "REVISED_SALARY"
FROM employees;
48.implement query to increase salary of employees with below criteria

a) If the job is IT_PROG increment is 10%.
b) If the job is ST_CLERK increment is 15%.
b) If the job is SA_REP increment is 20%.

SELECT last_name, job_id, salary,
DECODE
(job_id, 
'IT_PROG', 1.10*salary,
'ST_CLERK', 1.15*salary,
'SA_REP', 1.20*salary,
 salary)
REVISED_SALARY
FROM employees;
49.Round function
SELECT ROUND(349.2344,2) FROM dual                         
Result value: 349.23

SELECT ROUND(349.243433) FROM dual                
Result value: 349 

SELECT ROUND(349.99) FROM dual                 
Result value: 350  
  
SELECT ROUND(349.50) FROM dual             
Result value: 350      
50. Trunc function
SELECT TRUNC(349.234,2) FROM dual
result value: 349.23

SELECT TRUNC(349.23) FROM dual
result value: 349
51.MoD function
SELECT MOD(5,2) FROM dual;
 result value: 1
52.Display employees who is earning salary in even number
SELECT first_name,salary FROM employees WHERE MOD(salary,2)=0;
53.Display system date
SELECT sysdate FROM dual
54.Display employees name with number of months they completed in company.
SELECT last_name, (SYSDATE-hire_date)/30 AS Months
FROM employees
WHERE department_id = 90;
55.Months between two date
SELECT months_between(sysdate,'28-feb-1992') FROM dual;
56. Add days into date
SELECT ADD_MONTHS(sysdate,4) FROM dual;
result value:28-03-17
57.Finding next day
SELECT NEXT_DAY(sysdate,'Friday') "NEXT DAY" FROM DUAL
result value:02-12-16
58.finding last_day of month
SELECT last_day('1-11-2016') FROM dual;
result value:30-11-16
59.Count the number of employee working in department number 80
SELECT COUNT(*) FROM employees WHERE department_id=80;
Result value:34
60.Display How many employee who is working in department number 80 and earning some commission
SELECT COUNT(commission_pct) FROM employees WHERE department_id = 80;
Result value: 3
61. Find the average salary of employees who are working in department number 80
SELECT AVG(salary) FROM employees WHERE department_id=80
62.Display the max sal of the employee table?
SELECT MAX(salary) FROM employees;
63.Display the minimum salary of the employee table?
SELECT MIN(salary) FROM employees;
64.find the sum of all the salary of employees table
SELECT SUM(salary) FROM employees;
65.Display department wise average salary
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;
66.Display number of employee working in each department?
SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;
67.Display job wise highest salary ?
SELECT job_id ,MAX(Salary) FROM employees GROUP BY Job_id;
68.job wise average salary and max salary?
SELECT department_id ,AVG(Salary),MAX(salary) FROM employees GROUP BY department_id;
69.Display job wise highest salary only if the salary is greater than twelve thousands
SELECT job_id ,MAX(Salary)
FROM employees
GROUP BY Job_id
HAVING MAX(salary)>12000;
70.Display employee name who is earning more than Lex
SELECT first_name,Salary
FROM employees
WHERE salary > (SELECT salary FROM employees WHERE first_name='Lex' );
71.Display employees whose job_id same as Ernst and earning less then Ernst.
SELECT last_name, job_id, salary
FROM employees
WHERE job_id= (SELECT job_id FROM employees WHERE last_name='Ernst')
AND   salary <(SELECT salary FROM employees WHERE last_name='Ernst')
72.Using Group Functions in a Subquery
SELECT last_name, job_id, salary
FROM employees
WHERE salary =(SELECT MAX(salary) FROM employees);
73.Multi row sub query
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG')
74.Natural join
SELECT first_name,department_id,department_name
FROM EMP1 NATURAL JOIN Departments;

75.Cartesian join
SELECT first_name,department_name FROM emp1,departments;
76.Inner join or equi join
SELECT e.first_name, d.department_id, d.department_name
FROM EMP1 e INNER JOIN Departments d
ON e.Department_id=d.Department_id;
77.Right outer join
SELECT e.first_name, d.department_id, d.department_name
FROM EMP1 e Right outer JOIN Departments d
ON e.Department_id=d.Department_id;
78.Left outer join
SELECT e.first_name, d.department_id, d.department_name
FROM EMP1 e LEFT OUTER JOIN Departments d
ON e.Department_id=d.Department_id;
79. Full outer join
SELECT e.first_name, d.department_id, d.department_name
FROM EMP1 e FULL OUTER JOIN Departments d
ON e.Department_id=d.Department_id;
80.UNION
— The UNION operator returns rows from both queries after eliminating duplications.
SELECT employee_id, job_id
FROM employees
UNION
SELECT employee_id, job_id
FROM job_history;

81.UNION ALL

–The UNION ALL operator returns rows from both queries, including all duplications.

SELECT employee_id, job_id, department_id
FROM employees
UNION ALL
SELECT employee_id, job_id, department_id
FROM job_history
ORDER BY employee_id;
82.INTERSECT
— The INTERSECT operator returns rows that are common to both queries.
SELECT employee_id, job_id
FROM employees
INTERSECT
SELECT employee_id, job_id
FROM job_history;
83.MINUS

–The MINUS operator returns all the distinct rows selected by the first query,
but not present in the second query result set.

SELECT employee_id
FROM employees
MINUS
SELECT employee_id
FROM job_history;
84.Creating Table
CREATE TABLE Mycontact(id NUMBER(10),mobile NUMBER(10), name VARCHAR(10) );
85.Viewing table description
DESCRIBE Mycontact
86.Creating table using sub-query:
CREATE TABLE EMP
AS 
SELECT employee_id, last_name,
salary*12 ANNUAL_SAL,
hire_date FROM employees;
87.Add a new column into table
ALTER TABLE Employees ADD deptno NUMBER(5) NOT NULL;
88.Modifying Column
ALTER TABLE EMPLOYEES MODIFY SALARY NUMBER(15,2);
89.Use the ALTER TABLE syntax to put a table into the read-only mode:
ALTER TABLE employees READ ONLY;
90.Dropping a Table
DROP TABLE EMP;
91.Renaming table name
RENAME old_table_name TO new_table_name;
92.Truncate table
TRUNCATE TABLE table_name;
93.Inserting data into each and Every column
INSERT INTO departments
            (department_id,department_name, manager_id, location_id)
     VALUES (70, 'Public Relations', 100, 1700);
94.Inserting data into specific column
INSERT INTO departments(department_id,department_name)
                VALUES (110, 'IT');
95.Inserting data without specifying column name
INSERT INTO departments
VALUES(80, 'Finance', 112, 1636);
96.Inserting data using Another table
INSERT INTO EMP1(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct FROM employees;
97.Modifying department id of employee who is having employees_id 100
UPDATE employees
SET department_id = 80
WHERE employee_id = 100
98.Setting department_id 80 for All employees
UPDATE EMP1
SET department_id = 80;
99.Updating two columns with a sub query
UPDATE EMPLOYEES
SET job_id = (SELECT job_id FROM employees WHERE employee_id = 100),
    salary = (SELECT salary FROM employees WHERE employee_id = 100)
WHERE employee_id = 113;
100.Updating table based on another table
UPDATE EMP1
SET department_id = (SELECT department_id FROM employees WHERE employee_id = 110)
WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 100);
101.Deleting row from table
DELETE FROM EMPLOYEES
WHERE EMP_Name = ‘SAM';
102.Create view which contain Employee data who is in department number 50.
CREATE VIEW Emp50
AS SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 50;
103.Retrieving Data from a View
SELECT * FROM EMP50;
104.Modifying view
CREATE OR REPLACE VIEW EMP50
(Employee_id, last_name, salary)
AS SELECT employee_id, 
          first_name || ' '|| last_name, 
          salary
FROM employees WHERE department_id = 50;
105.Creating sequence
CREATE SEQUENCE dept_Number_seq
INCREMENT BY 10
START WITH 500
MAXVALUE 9999
NOCACHE
NOCYCLE;
106.Viewing the current value for sequence.
Select dept_Number_seq.CURRVAl from dual;
107.Using Sequence into Query:
INSERT INTO departments(department_id,department_name, location_id)
                VALUES (dept_deptid_seq.NEXTVAL,'Tech Support', 2500);
108.Modifying a Sequence
ALTER SEQUENCE dept_Number_seq
INCREMENT BY 1000
MAXVALUE 999999
NOCACHE
NOCYCLE;
109.Improve the speed of query access to the FIRST_NAME column in the EMPLOYEES(using index)
CREATE INDEX EMP_FIRST_NAME
ON employees(FIRST_NAME);
110.Removing an Index
DROP INDEX EMP_FIRST_NAME;
111.Creating database user
CREATE USER EMP_10
IDENTIFIED BY emp1234;
112.granting specific system privileges to a user
GRANT create session, create table,
create sequence, create view
TO demo;
113.Creating role
SQL uses role-based security, which allows you to assign permissions to a role, or group of users.
CREATE ROLE MGR_ROLE;
114.Assigning create table and Create view privilege to MGR_ROLE
GRANT create table, create view, Create session
TO MGR_ROLE;
115.Granting Role to user;
GRANT MGR_ROLE TO hr,scott;
116.Changing Your Password:
ALTER USER EMP_10
IDENTIFIED BY emp4321
117.Grant privileges to insert data to users and roles:
GRANT insert(department_name, location_id)
ON departments
TO EMP_10, MGR_ROLE;
118.Revoking Object Privileges
REVOKE select, insert
ON departments
FROM EMP_10
119.Query to find first max salary
SELECT first_name,salary
FROM employees
WHERE salary=(SELECT MAX(salary) FROM employees)
120.Query to find second maximum salary
SELECT MAX(Salary) FROM Employees
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employees )