DBMS SQL Practical with Output
1. Display records of all the employees
Query:
SELECT * FROM employee;
Output:
empno | ename | salary | hiredate | dno | designation
101 | mark | 20000 | 20-JAN-10 | d1 | clerk
102 | steve | 120000 | 02-DEC-10 | d2 | general manager
103 | bill | 80000 | 07-SEP-13 | d3 | officer
104 | zuckerberg | 70000 | 29-JUN-15 | d1 | assitsant manager
105 | smith | 90000 | 13-SEP-20 | d1 | chief manager
2. Display empno, ename and hiredate of all the employees
Query:
SELECT empno, ename, hiredate FROM employee;
Output:
empno | ename | hiredate
101 | mark | 20-JAN-10
102 | steve | 02-DEC-10
103 | bill | 07-SEP-13
104 | zuckerberg | 29-JUN-15
105 | smith | 13-SEP-20
3. Display records of employees who joined after 1-jan-2010
Query:
SELECT * FROM employee WHERE hiredate > TO_DATE('01-JAN-2010', 'DD-MON-YYYY');
Output:
102 | steve | 120000 | 02-DEC-10 | d2 | general manager
103 | bill | 80000 | 07-SEP-13 | d3 | officer
104 | zuckerberg | 70000 | 29-JUN-15 | d1 | assitsant manager
105 | smith | 90000 | 13-SEP-20 | d1 | chief manager
4. Display records of employees who joined in the year 2020
Query:
SELECT * FROM employee WHERE hiredate BETWEEN TO_DATE('01-JAN-2020', 'DD-MON-YYYY') AND
TO_DATE('31-DEC-2020', 'DD-MON-YYYY');
Output:
105 | smith | 90000 | 13-SEP-20 | d1 | chief manager
5. Display records of employees in alphabetical order of name
Query:
DBMS SQL Practical with Output
SELECT * FROM employee ORDER BY ename;
Output:
103 | bill | 80000 | 07-SEP-13 | d3 | officer
101 | mark | 20000 | 20-JAN-10 | d1 | clerk
105 | smith | 90000 | 13-SEP-20 | d1 | chief manager
102 | steve | 120000 | 02-DEC-10 | d2 | general manager
104 | zuckerberg | 70000 | 29-JUN-15 | d1 | assitsant manager
6. Display total employees in each department
Query:
SELECT dno, COUNT(*) FROM employee GROUP BY dno;
Output:
dno | count
d1 | 3
d2 | 1
d3 | 1
7. Display average and maximum salary in each department
Query:
SELECT dno, AVG(salary) AS average_salary, MAX(salary) AS maximum_salary FROM employee GROUP BY dno;
Output:
dno | average_salary | maximum_salary
d1 | 60000.00 | 90000.00
d2 | 120000.00 | 120000.00
d3 | 80000.00 | 80000.00
8. Display records of employees whose name begin with 'a' and ends with 'k'
Query:
SELECT * FROM employee WHERE ename LIKE 'a%k';
Output:
No records found.
9. Display records of employee working in d1,d2 and d3 departments
Query:
SELECT * FROM employee WHERE dno IN ('d1', 'd2', 'd3');
Output:
All records, since all employees are in d1, d2, or d3.
10. Display name of employees whose name start with 'a' or 'j'
DBMS SQL Practical with Output
Query:
SELECT ename FROM employee WHERE ename LIKE 'a%' OR ename LIKE 'j%';
Output:
No matching records.
11. Display empno, ename and dname of employees
Query:
SELECT empno, ename, dname FROM employee JOIN department ON employee.dno = department.dno;
Output:
101 | mark | marketing
102 | steve | accounts
103 | bill | research
104 | zuckerberg | marketing
105 | smith | marketing
12. Display empno, ename, and dname of employee working in department located at patna
Query:
SELECT empno, ename, dname FROM employee JOIN department ON employee.dno = department.dno WHERE
dlocation = 'patna';
Output:
102 | steve | accounts
13. Display empno, ename, and dname of employee working in department located at patna or ranchi
Query:
SELECT empno, ename, dname FROM employee JOIN department ON employee.dno = department.dno WHERE
dlocation IN ('patna','ranchi');
Output:
101 | mark | marketing
102 | steve | accounts
104 | zuckerberg | marketing
105 | smith | marketing
14. Display records of employees who joined after 1-sep-2020 and working in ranchi location
Query:
SELECT * FROM employee e JOIN department d ON e.dno = d.dno WHERE dlocation = 'ranchi' AND hiredate >
TO_DATE('01-SEP-2020', 'DD-MON-YYYY');
Output:
105 | smith | 90000 | 13-SEP-20 | d1 | chief manager
DBMS SQL Practical with Output
15. Display records of employee who earn more than 50000
Query:
SELECT * FROM employee WHERE salary > 50000;
Output:
102 | steve | 120000
103 | bill | 80000
104 | zuckerberg | 70000
105 | smith | 90000
16. Display name, designation and salary of employees not in the range 10000 and 50000
Query:
SELECT ename, designation, salary FROM employee WHERE salary NOT BETWEEN 10000 AND 50000;
Output:
steve | general manager | 120000
bill | officer | 80000
zuckerberg | assitsant manager| 70000
smith | chief manager | 90000
17. Display name of employees working in d1 department in alphabetical order of name
Query:
SELECT ename FROM employee WHERE dno='d1' ORDER BY ename;
Output:
mark
smith
zuckerberg
18. Display name and salary of employees in descending order of salary
Query:
SELECT ename, salary FROM employee ORDER BY salary DESC;
Output:
steve | 120000
smith | 90000
bill | 80000
zuckerberg | 70000
mark | 20000
19. Display list of designations of employees
Query:
SELECT DISTINCT designation FROM employee;
DBMS SQL Practical with Output
Output:
clerk
general manager
officer
assitsant manager
chief manager
20. Explain the following SQL concepts with SQL examples
Query:
Topics: Alter, Drop, Group By, Subquery, Join, Index, Insert, Delete, Update, PK/FK, Union, Intersect, Minus
Output:
ALTER TABLE employee ADD age NUMBER(3);
DROP TABLE employee;
SELECT dno, COUNT(*) FROM employee GROUP BY dno;
SELECT * FROM employee WHERE salary > (SELECT AVG(salary) FROM employee);
SELECT empno, ename, dname FROM employee JOIN department ON employee.dno = department.dno;
CREATE INDEX idx_ename ON employee(ename);
INSERT INTO employee VALUES('106','james','60000','10-JAN-2022','d4','analyst');
DELETE FROM employee WHERE empno = '106';
UPDATE employee SET salary = salary + 1000 WHERE empno = '101';
ALTER TABLE employee ADD CONSTRAINT fk_dno FOREIGN KEY (dno) REFERENCES department(dno);
SELECT empno FROM employee WHERE dno = 'd1'
UNION
SELECT empno FROM employee WHERE dno = 'd2';
