DATABASE SYSTEM
BSIT F19 (Afternoon)
LAB 08
Instructions:
• You must complete all tasks individually. Absolutely NO collaboration is
allowed.
• Any traces of plagiarism/cheating would result in an “F” grade in this course.
• Late submissions will NOT be accepted, in any case.
Submission Procedure:
You are required to submit PDF file.
The file should contain the question, the query and the screen short of output
after running the query.
You have to submit your lab file on following email: dbsit19aft@gmail.com
The subject of the email should be RollNumber_LabNumber. e.g.
BITF19A123_Lab01
Note: Use following tables for your lab tasks.
EMP (EMPNO, ENAME, JOB, SAL, HIREDATE, COMM, MGR, DEPTNO)
DEPT (DEPTNO, DNAME, LOC)
SALGRADE(GRADE,LOSAL,HISAL)
Dual
TASK 1
WRITE A QUERY TO DISPLAY DATA OF ALL THE EMPLOYEES WHO WERE
HIRED BEFORE KING.
ANS:
SELECT *
FROM emp
WHERE hiredate < (SELECT hiredate FROM emp WHERE ename='KING')
TASK 2
FIND THE SALARIES OF THOSE EMPLOYEES WHO EARN MORE THEN JONES'.
ANS:
SELECT *
FROM emp
where sal >
(select sal
from emp
where empno=7566)
TASK 3
DISPLAY EMPLOYEE HAVING MINIMUM SALARY WITH HIS NAME.
ANS:
SELECT *
FROM emp
WHERE sal =
(select min(sal) from emp)
TASK 4
FIND ALL EMPLOYEES WHO HAVE THE SAME JOB AS BLAKE.
ANS:
SELECT *
FROM emp
WHERE job = (SELECT job FROM emp WHERE ename='BLAKE')
TASK 5
FIND ALL EMPLOYEES WHO EARN MORE THAN THE AVERAGE SALARY OF
EMPLOYEES IN THEIR OWN DEPARTMENT AND SORT BY DEPARTMENT NO.X
ANS:
SELECT ename, deptno, sal
FROM emp
WHERE sal < (SELECT AVG(sal) FROM emp)
ORDER BY deptno
TASK 6
FIND ALL EMPLOYEES WHO EARN MORE THAN ANY EMPLOYEE IN DEPARTMENT 30.
ANS:
SELECT ename, sal, deptno
FROM emp
WHERE sal =
(SELECT MAX(sal) from emp where deptno=30)
TASK 7
FIND THE JOB WHICH HAS THE HIGHEST AVERAGE SALARY.
ANS:
SELECT *
FROM emp
WHERE sal =
(SELECT MAX(AVG(sal))
FROM emp
GROUP BY job)
TASK 8
FIND ALL JOBS WITH EITHER THE SAME JOB AS 'JONES' OR SALARY GREATER
THAN OR EQUAL TO FORD, ORDER BY JOB AND SALARY
ANS:
SELECT job
FROM emp
WHERE ename='JONES'
OR sal >=
(SELECT sal
FROM emp
WHERE ename = 'FORD')
ORDER BY job, sal
TASK 8
FIND AVERAGE AND SUM OF THE SALARIES OF EACH JOB EXCLUDING THE JOB OF
BLAKE.
ANS:
SELECT job, AVG(sal) as "Average salary", SUM(sal) as "Sum of Salary"
FROM emp
WHERE job <>
(SELECT job
FROM emp
WHERE ename = 'BLAKE')
GROUP BY job;
TASK 9
DISPLAY MAXIMUM SALARY FROM EMPLOYEE TABLE. NOW DECREASE MAXIMUM
SALARY BY 10% AND DISPLAY IT.
ANS:
SELECT MAX(sal) as "Maximum Salary", MAX(sal)-MAX(sal)*10/100 as "Decreased Salary"
FROM emp
TASK 10
DISPLAY THE NAME OF OLDEST AND LATEST HIRED EMPLOYEE.
ANS:
SELECT ename
FROM emp
WHERE hiredate = (SELECT MAX (hiredate) FROM emp)
OR
hiredate = (SELECT MIN(hiredate) FROM emp)
TASK 11
DISPLAY AVERAGE SALARY OF ALL THE EMPLOYEES ACCORDING TO THEIR
JOBS AND DEPARTMENTS.ALSO DISPLAY JOBS AND DEPARTMENT.
ANS:
SELECT AVG (SAL) as "Average Salary", deptno, job
FROM emp
GROUP BY job, deptno
TASK 12
FIND TOTAL SALARY OF ALL THE EMPLOYEES WORKING IN SAME
DEPARTMENT.DISPLAY TOTAL SALARY ALONG WITH DEPARTMENT
NUMBERS.SORT DATA ACCORDING TO DEPTNO.
ANS:
SELECT SUM(sal) as "Sum of Salaries", SUM(deptno) as "Sum of departments"
from emp
GROUP BY deptno
ORDER BY deptno
TASK 13
COUNT NUMBER OF ROWS OF DEPARTMENT TABLE.
ANS:
SELECT COUNT(*) as "Rows"
from dept
TASK 14
DISPLAY MAXIMUM SALARIES FROM ALL THE JOBS.ALSO DISPLAY JOBS.BUT
MAXIMUM SALARY SHOULD BE GREATER THAN 2000.
ANS:
TASK 15
FIND MAXIMUM FROM AVERAGE SALARY OF ALL THE JOBS AND DISPLAY
THEM.
ANS:
SELECT MAX(AVG(sal)) as "OUTPUT"
FROM emp
GROUP BY job
GOOD LUCK 😊
