XII- CS-- SOLVED SQL queries (SQL PRACTICAL ASSIGNMENS)
Assignment - SQL XII-COMPUTER SCIENCE
1.
Table
: EMP
EMPNO
|
ENAME
|
JOB
|
HIREDATE
|
SAL
|
DOB
|
DEPTNO
|
7839
|
REA
|
MANAGER
|
12-DEC-98
|
5000
|
10-SEP-48
|
10
|
1234
|
PREM
|
CLERK
|
11-FEB-77
|
12000
|
12-DEC-50
|
20
|
6754
|
SITA
|
MANAGER
|
12-MAR-99
|
39000
|
12-MAR-68
|
50
|
6574
|
GITA
|
SALESMAN
|
11-JUN-99
|
7000
|
11-JUN-61
|
30
|
9876
|
HONEY
|
CLERK
|
12-JUN-00
|
12000
|
12-JUN-70
|
20
|
8976
|
REETA
|
SALESMAN
|
10-SEP-88
|
9000
|
10-SEP-48
|
30
|
1234
|
HINA
|
CLERK
|
11-FEB-77
|
39000
|
12-DEC-83
|
40
|
6794
|
SITA
|
MANAGER
|
12-MAR-99
|
10000
|
12-MAR-68
|
20
|
6578
|
HINA
|
SALESMAN
|
11-JUN-99
|
8000
|
11-JUN-60
|
30
|
9899
|
RAM
|
CLERK
|
12-JUN-00
|
12000
|
12-JUN-70
|
20
|
8988
|
REENA
|
SALESMAN
|
10-SEP-88
|
6000
|
10-SEP-48
|
30
|
6679
|
TASREEN
|
CLERK
|
12-JUN-00
|
22000
|
12-JUN-70
|
40
|
[A]. (e) Display names of employees whose names include either of
the substring “TA” or “RE”or “NA”.
(f) Display data of all employees sorted by their department,
seniority and salary.
(g) Count the no. of employees who have are not managers.
(h) To display all employees who are retired and not working now a
days
(suppose Retiring Age=60).
(i) Show the average salary for all departments with more than 3
people for a job.
(j) Find out number of employees having ‘MANAGER’ as job.
(k) Create view DEPT20 with empno, name and the salary of employees
for dept 20.
(l) Display department no. and number of employees in each
department.
(m)Show sum and average salary for
depttNo=20.
(n)Display all employees have unique names.
(o)Find al employess whose deptt is
same as of ‘TASREEN’.
(p)Increase the salary of all MANAGER employees
by 10%,
(q)Find the deptno that is paying max
salaries to its employees.
(r) Display the details of all the employees
having salary less than 10000.
(s)
Display the list of all SALESMAN who are
getting salary beween 6000 and 8000.(inclusive range)
[B] Find the output of the
following :
1. SELECT SYSDATE FROM
DUAL;
2. SELECT ENAME,SAL FROM
EMPLOYEE WHERE JOB IN (‘CLERK’,’SALESMAN’);
3. SELECT COUNT(*) FROM
EMP;
4.
SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB;
2.[A] Write SQL
commands for the statements (i) to (vi) and give outputs for SQL queries (vii)
to (x)
TABLE NAME : WORKERS
W_ID
|
FIRSTNAME
|
LASTNAME
|
ADDRESS
|
CITY
|
102
|
Sam
|
Tones
|
33 Elm St.
|
Paris
|
105
|
Sarah
|
Ackerman
|
440 U.S. 110
|
New York
|
144
|
Manila
|
Sengupta
|
24 Friends
Street
|
New Delhi
|
210
|
George
|
Smith
|
83 First Street
|
Howard
|
255
|
Mary
|
Jones
|
842 Vine Ave.
|
Losantiville
|
300
|
Robert
|
Samuel
|
9 Fifth Cross
|
Wasington
|
335
|
Henry
|
Williams
|
12Moore Street
|
Boston
|
403
|
Ronny
|
Lee
|
121 Harrison St.
|
New York
|
451
|
Pat
|
Thompson
|
11 Red Road
|
Paris
|
TABLE NAME : DESIG
W_ID
|
SALARY
|
BENEFITS
|
DESIGNATION
|
102
|
75000
|
15000
|
Manager
|
105
|
85000
|
25000
|
Director
|
144
|
70000
|
15000
|
Manager
|
210
|
75000
|
12500
|
Manager
|
255
|
50000
|
12000
|
Clerk
|
300
|
45000
|
10000
|
Clerk
|
335
|
40000
|
10000
|
Clerk
|
400
|
32000
|
7500
|
Salesman
|
451
|
28000
|
7500
|
Salesman
|
(i)
To display w_id, firstname, address and city of all employees living in new york from the table workers.
(ii)
To display the content of workers table
in ascending order of lastname.
(iii)
To display the firstname, lastname and total salary of all clerks from the
tables workers and design, where total salary is
calculated as salary + benefits.
(iv)
To display the minimum salary among
Managers and Clerks from the table DESIG.
(v)
To display the W_ID FIRSTNAME,
DESIGNATION of all employees.
(vi)
To display the name of all Clerks.
(vii)
SELECT FIRSTNAME, SALARY FROM WORKERS,
DESIG WHERE DESIGNATION = ‘Manager’ AND WORKERS.W_ID = DESIG.W_ID
(viii)
SELECT COUNT(DISTINCT DESIGNATION) FROM
DESIG.
(ix)
SELECT DESIGNATION, SUM(SALARY FROM
DESIGNATION GROUP BY DESIGNATION HAVING COUNT(*)<3;
(x)
SELECT SUM(BENEFITS) FROM WORKERS WHERE
DESIGNATION = ‘Salesman’;
3. (a)Write SQL commands for (a) to ( j)
and write output for (h) on the basis of Teacher relation given below.
TABLE NAME : TEACHER
No
|
Name
|
Age
|
Department
|
Date of Join
|
Salary
|
Gender
|
1.
|
Ram
|
34
|
Computer
|
10/01/97
|
12000
|
M
|
2.
|
Sharmila
|
31
|
History
|
24/03/98
|
20000
|
F
|
3.
|
Sandeep
|
32
|
Maths
|
12/12/96
|
30000
|
M
|
4.
|
Sangeeta
|
35
|
History
|
01/07/99
|
40000
|
F
|
5.
|
Rakesh
|
42
|
Maths
|
05/09/97
|
25000
|
M
|
6.
|
Shyam
|
50
|
History
|
27/02/97
|
30000
|
M
|
7.
|
Shiv Om
|
44
|
Computer
|
25/02/97
|
21000
|
M
|
8.
|
Shalakha
|
33
|
Maths
|
31/07/97
|
20000
|
F
|
(a)
To show all information about
the teacher of history department.
(b)
To list the names of female
teachers who are in Maths department
(c)
To list names of all teachers
with their date of joining in ascending order.
(d)
To display students name,
fee,age for male teacher only
(e)
To count the number of teachers
with age>23.
(f)
To insert a new row in the
TEACHER table with the following data:
9,”Raja”,26,”Computer”,13/05/95,2300,”M”.
(g)To show all information about the teachers in this table
(h)Add a new column named “Address”.
(i)Arrange the whole table in the alphabetical order to name
(j)Display the age of the teachers whose name starts with ‘S,.
(k)Give the output of following statement.
(i)
Select COUNT(distinct
department) from TEACHER.
(ii)
Select MAX(Age)from Teacher
where Gender=”F”
(iii)
Select AVG(Salary) from Teacher
where Dateofjoin< ‘12/07/96’
(iv)
Select SUM(Salary) from teacher where
Dateofjoin< ‘12/07/96’
4. Q4. Write the SQL commands for (a) to (d) and
write the output for (e) on
the basis of table Hospital
:
No
|
Name
|
Age
|
Department
|
Dateofadmin
|
Charge
|
Gender
|
1
|
Arpit
|
62
|
Surgery
|
21/01/06
|
300
|
M
|
2
|
Zayana
|
18
|
ENT
|
12/12/05
|
250
|
F
|
3
|
Kareem
|
22
|
Orthopedic
|
19/02/06
|
450
|
M
|
4
|
Abhilash
|
26
|
Surgery
|
24/11/06
|
300
|
M
|
5
|
Dhanya
|
24
|
ENT
|
20/10/06
|
350
|
F
|
6
|
Siju
|
23
|
Cardiology
|
10/10/06
|
800
|
M
|
7
|
Ankita
|
16
|
ENT
|
13/04/06
|
100
|
F
|
8
|
Divya
|
15
|
Cardiology
|
10/11/06
|
500
|
F
|
9
|
Nidhin
|
25
|
Orthopedic
|
12/05/06
|
700
|
M
|
10
|
Hari
|
28
|
Surgery
|
19/03/06
|
450
|
M
|
(a)
To
show all information about the patients of cardiology department.
(b)
To
list the name of female patients who are in ENT department.
(c)
To
list names of all patients with their date of admission in ascending order.
(d)
To
count the no of patients with age > 20.
(e)
Give
the output of the following SQL commands:
(i)
Select COUNT(DISTINCT charges ) from
hospital;
(ii)
Select MIN(age) from hospital where Gender=’M’;
(iii)
Select
SUM(charges) from hospital where Gender =’F’;
(iv) Select
avg(charges) from hospital where dateofadm>{12/05/06};
********************************SOLUTION************************************
TABLE
NAME : WORKERS
W_ID
|
FIRSTNAME
|
LASTNAME
|
ADDRESS
|
CITY
|
102
|
Sam
|
Tones
|
33 Elm St.
|
Paris
|
105
|
Sarah
|
Ackerman
|
440 U.S. 110
|
New York
|
144
|
Manila
|
Sengupta
|
24 Friends Street
|
New Delhi
|
210
|
George
|
Smith
|
83 First Street
|
Howard
|
255
|
Mary
|
Jones
|
842 Vine Ave.
|
Losantiville
|
300
|
Robert
|
Samuel
|
9 Fifth Cross
|
Wasington
|
335
|
Henry
|
Williams
|
12Moore Street
|
Boston
|
403
|
Ronny
|
Lee
|
121 Harrison St.
|
New York
|
451
|
Pat
|
Thompson
|
11 Red Road
|
Paris
|
TABLE
NAME : DESIG
W_ID
|
SALARY
|
BENEFITS
|
DESIGNATION
|
102
|
75000
|
15000
|
Manager
|
105
|
85000
|
25000
|
Director
|
144
|
70000
|
15000
|
Manager
|
210
|
75000
|
12500
|
Manager
|
255
|
50000
|
12000
|
Clerk
|
300
|
45000
|
10000
|
Clerk
|
335
|
40000
|
10000
|
Clerk
|
400
|
32000
|
7500
|
Salesman
|
451
|
28000
|
7500
|
Salesman
|
(i)
Write
commands to create above 2 tables.
(ii)
To
display w_id, firstname, address
and city of all employees living
in new york from the table workers.
(iii)
To
display the content of workers table in ascending order of lastname.
(iv)
To
display the firstname, lastname
and total salary of all clerks from the tables workers and design,
where total salary is calculated as salary
+ benefits.
(v)
To
display the W_ID FIRSTNAME, DESIGNATION of all employees.
................................................SOLUTION..............................
(i) CREATE TABLE WORKERS(W_ID INTEGER PRIMARY KEY , FIRSTNAME VARCHAR2(20) NOT NULL ,
LASTNAME VARCHAR2(20), ADDRESS VARCHAR2(20),CITY VARCHAR2(20));
CREATE TABLE DESIG (W_ID INTEGER PRIMARY KEY, SALARY NUMBER(8,2),
BENEFITS NUMBER(8,2),DESIGNATION VARCHAR2(20) );
(ii) To display W_ID, FIRSTNAME, ADDRESS and CITY of all employees living in NEW YORK from the table WORKERS.
SELECT W_ID, FIRSTNAME, ADDRESS ,CITY FROM WORKERS WHERE CITY='NEW YORK';
(iii) To display the content of workers table in ascending order of LASTNAME.
SELECT * FROM WORKERS ORDER BY LASTNAME ASC;
(iv) To display the FIRSTNAME, LASTNAME and total salary of all clerks from the tables
WORKERS and DESIG, where total salary is calculated as SALARY + BENEFITS.
SELECT FIRSTNAME,LASTNAME, (SALARY+BENEFITS) AS "TOTAL SALARY" FROM WORKERS,DESIG
WHERE WORKERS.W_ID=DESIG.W_ID;
(v) To display the W_ID FIRSTNAME, DESIGNATION of all employees.
SELECT WORKERS.W_ID,FIRSTNAME,DESIGNATION FROM WORKERS,DESIG
WHERE WORKERS.W_ID=DESIG.W_ID;
***********************************************************************
**********************************************************
Question 1:
CREATE TABLE EMPLOYEE
(EMPNO INT PRIMARY KEY,
ENAME VARCHAR(20) NOT NULL,
JOB VARCHAR(20) ,
HIREDATE DATE,
SAL DECIMAL CHECK(SAL>=5000),
DOB DATE NOT NULL,
DEPTNO INTEGER);
INSERT INTO EMPLOYEE VALUES(7839,'REA','MANAGER','12-DEC-98',5000,'12-JAN-51','10');
INSERT INTO EMPLOYEE VALUES(1234,'PREM','CLERK','11-FEB-77',12000,'12-DEC-50',20);
INSERT INTO EMPLOYEE VALUES(6754,'SITA','MANAGER','12-MAR-99',39000,'12-MAR-68',50);
INSERT INTO EMPLOYEE VALUES(6574,'GITA','SALESMAN','11-JUN-99',7000,'11-JUN-61',30);
INSERT INTO EMPLOYEE VALUES(9876,'HONEY','CLERK','12-JUN-00',12500,'12-JUN-70',20);
INSERT INTO EMPLOYEE VALUES(8976,'REETA','SALESMAN','10-SEP-88',9000,'12-JAN-51',30);
INSERT INTO EMPLOYEE VALUES(1243,'HINA','CLERK','11-FEB-77',39000,'12-DEC-83',40);
INSERT INTO EMPLOYEE VALUES(6794,'SITA','MANAGER','12-MAR-99',10000,'12-MAR-68',20);
INSERT INTO EMPLOYEE VALUES(6578,'HINA','SALESMAN','11-JUN-99',8000,'11-JUN-60',30);
INSERT INTO EMPLOYEE VALUES(9899,'RAM','CLERK','12-JUN-00',12000,'12-JUN-70',20);
INSERT INTO EMPLOYEE VALUES(8988,'REENA','SALESMAN','10-SEP-88',6000,'12-JAN-51',30);
INSERT INTO EMPLOYEE VALUES(6679,'TASREEN','CLERK','12-JUN-00',22500,'12-JUN-70',40);
[A]. (e) Display names of employees whose names include either of the substring “TA” or “RE”or “NA”.
SQL> SELECT ENAME FROM EMPLOYEE WHERE ((ENAME LIKE '%TA%') OR (ENAME LIKE '%RE%'
) OR (ENAME LIKE '%NA%'));
ENAME
--------------------
REA
SITA
PREM
GITA
REETA
SITA
HINA
REENA
TASREEN
HINA
10 rows selected.
(f) Display all data of all employees sorted by their department, seniority and salary.
SELECT * FROM EMPLOYEE ORDER BY DEPTNO,HIREDATE,SAL;
Display DEPTNO,HIREDATE,SAL of all employees sorted by their department, seniority and salary.
SQL> SELECT DEPTNO,HIREDATE,SAL FROM EMPLOYEE ORDER BY DEPTNO,HIREDATE,SAL;
DEPTNO HIREDATE SAL
---------- --------- ----------
10 12-DEC-98 5000
20 11-FEB-77 12000
20 12-MAR-99 10000
20 12-JUN-00 12000
20 12-JUN-00 12500
30 10-SEP-88 6000
30 10-SEP-88 9000
30 11-JUN-99 7000
30 11-JUN-99 8000
40 11-FEB-77 39000
40 12-JUN-00 22500
DEPTNO HIREDATE SAL
---------- --------- ----------
50 12-MAR-99 39000
12 rows selected.
Display DEPTNO,HIREDATE,SAL of all employees sorted by their department , seniority(in ascending) and salary (in descending).
SQL> SELECT DEPTNO,HIREDATE,SAL FROM EMPLOYEE ORDER BY DEPTNO,HIREDATE ASC,SAL DESC;
DEPTNO HIREDATE SAL
---------- --------- ----------
10 12-DEC-98 5000
20 11-FEB-77 12000
20 12-MAR-99 10000
20 12-JUN-00 12500
20 12-JUN-00 12000
30 10-SEP-88 9000
30 10-SEP-88 6000
30 11-JUN-99 8000
30 11-JUN-99 7000
40 11-FEB-77 39000
40 12-JUN-00 22500
DEPTNO HIREDATE SAL
---------- --------- ----------
50 12-MAR-99 39000
12 rows selected.
(g) Count the no. of employees who have are not managers.
SQL> SELECT COUNT(*) FROM EMPLOYEE WHERE (NOT JOB='MANAGER');
COUNT(*)
----------
9
SQL> SELECT COUNT(*) FROM EMPLOYEE WHERE (JOB !='MANAGER');
COUNT(*)
----------
9
SQL> SELECT COUNT(*) AS "NO OF EMPLOYEES" FROM EMPLOYEE WHERE (JOB !='MANAGER');
NO OF EMPLOYEES
---------------
9
(h) To display all employees who are retired and not working now a days
(suppose Retiring Age=60).
SQL> SELECT ENAME ,SYSDATE , DOB FROM EMPLOYEE WHERE
2 MONTHS_BETWEEN(SYSDATE,DOB) >=720;
ENAME SYSDATE DOB
-------------------- --------- ---------
REA 25-OCT-12 12-JAN-51
PREM 25-OCT-12 12-DEC-50
REETA 25-OCT-12 12-JAN-51
REENA 25-OCT-12 12-JAN-51
DO NOT TRY THIS--->>> SELECT ENAME FROM EMPLOYEE WHERE MONTHS_BETWEEN(DOB,SYSDATE) >=720;
(i) Show the average salary for all JOBS with more than 3 people for a job.
SELECT AVG(SAL) FROM EMPLOYEE GROUP BY JOB HAVING COUNT(*) >3;
SQL> SELECT JOB, AVG(SAL)FROM EMPLOYEE GROUP BY JOB HAVING CO
UNT(*) >3;
JOB AVG(SAL) COUNT(*) SUM(SAL)
-------------------- ---------- ---------- ----------
CLERK 19600 5 98000
SALESMAN 7500 4 30000
SELECT DEPTNO, AVG(SAL) FROM EMPLOYEE GROUP BY DEPTNO HAVING JOB IN ((SELECT JOB FROM EMPLOYEE GROUP BY JOB HAVING (CO
UNT(*) >3));
(j) Find out number of employees having ‘MANAGER’ as job.
(k) Create view DEPT20 with empno, name and the salary of employees for dept 20.
(l) Display department no. and number of employees in each department.
(m)Show sum and average salary for depttNo=20.
(n)Display all employees have unique names.
(o)Find al employess whose deptt is same as of ‘TASREEN’.
(p)Increase the salary of all MANAGER employees by 10%,
(q)Find the deptno that is paying max salaries to its employees.
(r) Display the details of all the employees having salary less than 10000.
(s) Display the list of all SALESMAN who are getting salary beween 6000 and 8000.(inclusive range)
[B] Find the output of the following :
1. SELECT SYSDATE FROM DUAL;
2. SELECT ENAME,SAL FROM EMPLOYEE WHERE JOB IN (‘CLERK’,’SALESMAN’);
3. SELECT COUNT(*) FROM EMP;
4. SELECT JOB, AVG(SAL) FROM EMP GROUP BY JOB;
SELECT ENAME ,SYSDATE , DOB FROM EMPLOYEE WHERE
MONTHS_BETWEEN(SYSDATE,DOB) >=720;
**********************************************************************************
