Retrieving data Using SELECT STATEMENT

1. Determine the Structure of DEPT Table and its Contents

a. To see the structure of the DEPT table:
DESC DEPT;
b. To see the contents of the DEPT table:
SELECT * FROM DEPT;

2. Determine the Structure of EMP Table and its Contents

a. To see the structure of the EMP table:
DESC EMP;
b. To see the contents of the EMP table:
SELECT * FROM EMP;

3. Display the Ename and Deptno from Emp table whose Empno is 7788
SELECT ENAME, DEPTNO FROM EMP WHERE EMPNO = 7788;

Restricting and Sorting Data

1. Display Ename, Sal, Comm of employees who earn commission and sort
records in descending order of Salary and Comm using numeric position
SELECT ENAME, SAL, COMM FROM EMP WHERE COMM IS NOT NULL ORDER BY 2 DESC, 3
DESC; -- 2: SAL, 3: COMM

2. HR department need a query to Display all unique job codes from the EMP
table
SELECT DISTINCT JOB FROM EMP;

3. HR department wants more descriptive column headings for its report on

employee Display employee report with column aliases: Emp #, Employee, Job,
Hire Date
SELECT EMPNO AS "Emp #", ENAME AS "Employee", JOB AS "Job", HIREDATE AS "Hire
Date" FROM EMP;

4. HR department requested a report of all employees with their job ids.

Display Last Name and Job ID concatenated, with alias: Employee, Title
SELECT ENAME || ', ' || JOB AS "Employee and Title" FROM EMP;

5. Display all data from EMP table, separating columns with commas, and use
alias THE_OUTPUT
SELECT EMPNO || ',' || ENAME || ',' || JOB || ',' || HIREDATE || ',' || MGR AS "THE_OUTPUT"
FROM EMP;

6. Display Ename, Job, Hiredate of employees named SCOTT or TURNER,

ordered by Hiredate
SELECT ENAME, JOB, HIREDATE FROM EMP WHERE ENAME IN ('SCOTT', 'TURNER') ORDER
BY HIREDATE ASC;

7. Display Ename and Deptno of all employees in departments 20 or 30, sorted

by Ename
SELECT ENAME, DEPTNO FROM EMP WHERE DEPTNO IN (20, 30) ORDER BY ENAME ASC;
8. Modify previous query: Display last name and salary of employees who earn
between 2000–3000 and are in department 20 or 30, with aliases
SELECT ENAME AS "Employee", SAL AS "Monthly Salary" FROM EMP
WHERE SAL BETWEEN 2000 AND 3000 AND DEPTNO IN (20, 30);

9. Display last name and hire date for employees hired in 1981
SELECT ENAME, HIREDATE FROM EMP WHERE TO_CHAR(HIREDATE, 'YYYY') = '1981';

10. Display ENAME and SAL of employees who earn more than an amount
specified by user (Prompt)
SELECT ENAME, SAL FROM EMP WHERE SAL > &Enter_Salary;

11. Display last name and job title of employees who do not have a manager
SELECT ENAME, JOB FROM EMP WHERE MGR IS NULL;

12. Query prompts for Manager ID and displays EMPNO, ENAME, SAL, DEPTNO
with sorting option
ELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE MGR = &Manager_ID ORDER BY
&Sort_Column;

13. Prompt for MGR ID, and display EMPNO, ENAME, SAL, DEPTNO of their
employees. Allow sorting on any selected column.
SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE MGR = &MGR_ID ORDER BY
&Sort_Column;
Example prompt:
Enter value for MGR_ID: 7839
Enter value for Sort_Column: SAL

14. Display all employee last names where the 3rd letter is ‘A’
SELECT ENAME FROM EMP WHERE SUBSTR(ENAME, 3, 1) = 'A';

15. Display last names of all employees who have both ‘A’ and ‘S’ in their name
SELECT ENAME FROM EMP WHERE ENAME LIKE '%A%' AND ENAME LIKE '%S%';

16. Display ENAME, JOB, SAL of all employees whose job is CLERK and salary is
800, 950, or 1300
SELECT ENAME, JOB, SAL FROM EMP WHERE JOB = 'CLERK' AND SAL IN (800, 950, 1300);

Single Row Funtion to Cusomize the Output

1. Display the current date. Label the column as "Date".

SELECT SYSDATE AS "Date" FROM DUAL;

2. Display Empno, Last Name, Salary, and New Salary (15.5% increase)
SELECT EMPNO, ENAME, SAL, ROUND(SAL + (SAL * 15.5 / 100)) AS "New Salary" FROM
EMP;

3. Add a column that shows the increase amount as "Increase"

SELECT EMPNO, ENAME, SAL, ROUND(SAL + (SAL * 15.5 / 100)) AS "New Salary",
ROUND(SAL * 15.5 / 100) AS "Increase" FROM EMP;

4. Display Ename with only the first letter uppercase, rest lowercase, and
length of Ename for names starting with J, A, or M
SELECT INITCAP(ENAME) AS "Employee Name", LENGTH(ENAME) AS "Name Length"
FROM EMP WHERE UPPER(SUBSTR(ENAME, 1, 1)) IN ('J', 'A', 'M') ORDER BY ENAME;
5. Prompt the user to enter a letter; display all employees whose last name
starts with that letter
SELECT ENAME FROM EMP WHERE ENAME LIKE '&Enter_Letter%';

6. Display employee name and number of months worked (rounded up), label
column as MONTHS_WORKED
SELECT ENAME, ROUND (MONTHS_BETWEEN (SYSDATE, HIREDATE)) AS
MONTHS_WORKED
FROM EMP ORDER BY MONTHS_WORKED;

7. Dream Salaries (Earns Monthly but Wants <3 Times Salary)

SELECT last_name || ' earns ' || TO_CHAR(salary, '9999.99') || ' monthly but wants < ' ||
TO_CHAR(salary * 3, '9999.99') AS "Dream Salaries" FROM employees;

8. Format Salary with $ and Left Padding

SELECT last_name, LPAD(TO_CHAR(salary, '$999,999.00'), 15, ' ') AS SALARY
FROM employees;

9. Salary Review Date: First Monday After 6 Months

SELECT last_name, hire_date, TO_CHAR(NEXT_DAY(ADD_MONTHS(hire_date, 6)-1,
'MONDAY'), 'Day, "the" DDth "of" Month, YYYY') AS REVIEW FROM employees;

10. Day of Week of Joining (Start with Monday)

SELECT last_name, hire_date, TO_CHAR(hire_date, 'Day') AS DAY FROM employees
ORDER BY TO_CHAR(hire_date, 'D'); -- Oracle NLS_TERRITORY must be set to start week
on Monday

11. Show Commission or "No Commission"

SELECT last_name, NVL(TO_CHAR(commission_pct), 'No Commission') AS COMM FROM
employees;

12. Asterisks per $1000 of Salary

SELECT SUBSTR(last_name, 1, 8) AS NAME, RPAD('*', TRUNC(salary / 1000), '*') AS
EMPLOYEES_AND_THEIR_SALARIES FROM employees ORDER BY salary DESC;

13. DECODE to Display Grade Based on JOB_ID

SELECT last_name, job_id, DECODE(job_id,
'PRESIDENT', 'A',
'MANAGER', 'B',
'SALESMAN', 'C',
'CLERK', 'D',
'Unknown') AS GRADE FROM employees;

14. Rewrite Above Using CASE

SELECT last_name, job_id,
CASE job_id
WHEN 'PRESIDENT' THEN 'A'
WHEN 'MANAGER' THEN 'B'
WHEN 'SALESMAN' THEN 'C'
WHEN 'CLERK' THEN 'D'
ELSE 'Unknown'
END AS GRADE FROM employees;

Reporting Aggregated Data Using Group Function

1. Highest, Lowest, Sum, and Average Salary
SELECT MAX(salary) AS Maximum, MIN(salary) AS Minimum, SUM(salary) AS
Sum, ROUND(AVG(salary)) AS Average FROM employees;
2. Rounded Group Functions
SELECT JOB, ROUND(MIN(SAL)) AS Minimum, ROUND(MAX(SAL)) AS Maximum,
ROUND(SUM(SAL)) AS Sum, ROUND(AVG(SAL)) AS Average FROM EMP GROUP BY
JOB;
3. Min, Max, Sum, Avg Salary by Job Type
SELECT job_id, MIN(salary) AS Min_Salary, MAX(salary) AS Max_Salary,
SUM(salary) AS Total_Salary, ROUND(AVG(salary)) AS Avg_Salary FROM
employees GROUP BY job_id;

4. Number of People with the Same Job

SELECT job_id, COUNT(*) AS Num_Employees FROM employees GROUP BY job_id;

5. Number of Managers
SELECT COUNT(DISTINCT manager_id) AS "Number of Managers" FROM
employees WHERE manager_id IS NOT NULL;

6. Difference Between Highest and Lowest Salaries

SELECT MAX(salary) - MIN(salary) AS DIFFERENCE FROM employees;

7. Manager ID and Salary of Lowest-Paid Employee (with conditions)

SELECT manager_id, MIN(salary) AS Lowest_Salary FROM employees WHERE
manager_id IS NOT NULL GROUP BY manager_id HAVING MIN(salary) > 2000
ORDER BY Lowest_Salary DESC;

8. Total Employees & Number Hired in 1980, 1981, 1982

SELECT COUNT(*) AS Total_Employees,
SUM(CASE WHEN EXTRACT(YEAR FROM hire_date) = 1980 THEN 1 ELSE 0 END)
AS "Hired_1980",
SUM(CASE WHEN EXTRACT(YEAR FROM hire_date) = 1981 THEN 1 ELSE 0 END)
AS "Hired_1981",
SUM(CASE WHEN EXTRACT(YEAR FROM hire_date) = 1982 THEN 1 ELSE 0 END)
AS "Hired_1982" FROM employees;

Using Set Operation

1. Matrix Query to Display Job, Department-wise Salary, and Total Salary
SELECT job,
SUM(CASE WHEN deptno = 10 THEN sal ELSE 0 END) AS "Dept 10 Salary",
 SUM(CASE WHEN deptno = 20 THEN sal ELSE 0 END) AS "Dept 20 Salary",
SUM(CASE WHEN deptno = 30 THEN sal ELSE 0 END) AS "Dept 30 Salary",
SUM(sal) AS "Total Salary" FROM employeesn GROUP BY job;

2. Set Operator for DEPTNO-wise and JOB-wise Salary Totals

SELECT deptno, NULL AS job, SUM(sal) AS total_salary FROM employees GROUP
BY deptno UNION SELECT NULL AS deptno, job, SUM(sal) AS total_salary FROM
employees GROUP BY job;

3. Set Operator to Display JOB and DEPTNO for DEPTNOs 20, 10, 30 (in
that order)
SELECT job, deptno FROM employees WHERE deptno = 20 UNION ALL
SELECT job, deptno FROM employees WHERE deptno = 10 UNION ALL
SELECT job, deptno FROM employees WHERE deptno = 30;

Display Data from Multiple Tables Using Joins

1. Display employee number, name, salary, department name, and

location using natural join.
SELECT EMPNO, ENAME, SAL, DNAME, LOC FROM EMP NATURAL JOIN DEPT;

2. Display job, manager, salary, commission, and department name of

all salesmen using equi join.
SELECT JOB, MGR, SAL, COMM, DNAME FROM EMP E, DEPT D WHERE E.DEPTNO
= D.DEPTNO AND JOB = 'SALESMAN';

3. Display employee name, job, department number, and department

name of all employees working in DALLAS using equi join.
SELECT ENAME, JOB, E.DEPTNO, DNAME FROM EMP E, DEPT D WHERE E.DEPTNO
= D.DEPTNO AND LOC = 'DALLAS';

4. Display employee name, employee number, manager name, and

manager number using self join.
SELECT E1.ENAME AS Employee, E1.EMPNO AS "Emp#", E2.ENAME AS Manager,
E1.MGR AS "Mgr#" FROM EMP E1 JOIN EMP E2 ON E1.MGR = E2.EMPNO;

5. Display employee name, employee number, manager name, and

manager number using LEFT OUTER JOIN. Include employees without
managers.
SELECT E1.ENAME AS Employee, E1.EMPNO AS "Emp#", E2.ENAME AS Manager,
E1.MGR AS "Mgr#" FROM EMP E1 LEFT JOIN EMP E2 ON E1.MGR = E2.EMPNO
ORDER BY E1.EMPNO;

6. Display employee name, job, department name, salary, and salary

grade using non-equi join.
SELECT E.ENAME, E.JOB, D.DNAME, E.SAL, S.GRADE FROM EMP E JOIN DEPT D ON
E.DEPTNO = D.DEPTNO JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND
S.HISAL;

7. Display employee name and department name using RIGHT OUTER

JOIN. Include departments without employees.
SELECT E.ENAME, D.DNAME FROM EMP E RIGHT JOIN DEPT D ON E.DEPTNO =
D.DEPTNO;

8. Display employee name and hiredate along with their manager’s

name and hiredate using self join. Only show employees hired before
their managers.
SELECT E1.ENAME AS Employee, E1.HIREDATE AS Emp_HireDate, E2.ENAME AS
Manager, E2.HIREDATE AS Mgr_HireDate FROM EMP E1 JOIN EMP E2 ON E1.MGR
= E2.EMPNO WHERE E1.HIREDATE < E2.HIREDATE;

9. Display employee number, name, department name, and location of

clerks using USING clause.
SELECT EMPNO, ENAME, DNAME, LOC FROM EMP JOIN DEPT USING(DEPTNO)
WHERE JOB = 'CLERK';

10. Display employee name, salary, manager, and department name for
employees whose salary is more than 2000 using ON clause.
SELECT ENAME, SAL, MGR, DNAME FROM EMP E JOIN DEPT D ON E.DEPTNO =
D.DEPTNO WHERE SAL > 2000;

11. Display employee number, job, department number, department

name, and location using LEFT OUTER JOIN.
SELECT EMPNO, JOB, E.DEPTNO, DNAME, LOC FROM EMP E LEFT OUTER JOIN
DEPT D ON E.DEPTNO = D.DEPTNO;

12. Display employee name and department name using RIGHT OUTER
JOIN. Include all departments even if they don’t have any employees.
SELECT ENAME, DNAME FROM EMP E RIGHT OUTER JOIN DEPT D ON E.DEPTNO =
D.DEPTNO;
13. Display employee number, department name, and location using
FULL OUTER JOIN. Include all employees and all departments.
SELECT EMPNO, DNAME, LOC FROM EMP E FULL OUTER JOIN DEPT D ON
E.DEPTNO = D.DEPTNO;