SQL--HOME ASSIGNMENTS(XII Class)

Assignment - SQL

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. 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

(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. 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.

(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};

5.Write down the output produced by following queries after execution.

a) SELECT CONCAT(‘PRIYA’,’JAIN’) FROM DUAL;

b) SELECT SUBSTR(‘INFORMATICS PRACTICES’,3,4) FROM DUAL;

c) SELECT INSTR(‘INDOOR OUTDOOR’,’OR’) FROM DUAL;

d) SELECT LENGTH(‘INFORMATICS PRACTICES’) FROM DUAL;

e) SELECT LOWER(RIGHT(‘JAVA’,2))FROM DUAL;

f) SELECT MOD(11,3) FROM DUAL;

g) SELECT SIGN(-67) FROM DUAL;

h) SELECT SQRT(81) FROM DUAL;

i) SELECT DATEOFMONTH(‘2009-02-03’)FROM DUAL;

j) SELECT ROUND(15.193,1) FROM DUAL;

j) SELECT ROUND (15.193,1) FROM DUAL;

6. What is relation? What is the difference between a tuple and an attribute?

7 (a) Define the following terminologies used in Relational Algebra:

(i) selection (ii) projection (iii) union (iv) Cartesian product

8. (b) Consider the following tables EMPLOYEE and SALARIES. Write SQL commands for the statements (i) to (iv) and give outputs for SQL queries (v) to (viii)

TABLE: EMPLOYEE

EMPID	EMPNAME	SECTION	SEX	EXPERIENCE
101	Sabeena	PRODUCTION	F	10
102	Siva	ACCOUNTS	M	15
103	Ayaz	INSPECTION	M	10
104	Gayathri	PRODUCTION	F	8
105	Poorna	ACCOUNTS	F	9
106	Giridhar	INSPECTION	M	10
107	Sindhu	PRODUCTION	F	13
108	Suganya	ACCOUNTS	F	11
109	Hemanth	PRODUCTION	M	15
110	Suprakya	ACCOUNTS	F	7

TABLE: SALARIES

EMPID	SALARY	INCENTIVE	ALLOWANCE
101	18000	1800	1325
103	23150	3100	1121
105	13120	2300	1240
106	12550	1250	1005
107	11145	1100	1290
109	18900	1990	1200
110	21400	2100	1312

i. Display EMPNAME of all staff who are in “ACCOUNTS” having more than 10 years experience from the table EMPLOYEE.

ii. Display the average PAY of all staff working in “INSPECTION” department using the tables EMPLOYEE and SALARIES. The total pay is calculated as PAY= SALARY +INCENTIVE+ ALLOWANCE.

iii. Display the minimum INCENTIVE of female staff.

iv. Display the highest ALLOWANCE among all male staff.

v. SELECT count (*) from EMPLOYEE where SEX = “F”.

vi. SELECT EMPNAME, SECTION, ALLOWANCE from EMPLOYEE, SALARIES where SECTION = “ACCOUNTS” and EMPLOYEE.EMPID = SALARIES.EMPID.

vii. SELECT EMPNAME from EMPLOYEE where SECTION= INSPECTION and SEX=’M’

viii. Select count (distinct SECTION) from EMPLOYEE

9.(a) What do you understand by Primary Key, Candidate Keys & foreign key?

(b) Consider the following tables GAMES and PLAYER. Write SQL commands for the statements (i) to (iv) and give outputs for SQL queries (v) to (viii)

Table: GAMES

GCode	GameName	Number	PrizeMoney	ScheduleDate
101	Carom Board	2	5000	23-Jan-2004
102	Badminton	2	12000	12-Dec-2003
103	Table Tennis	4	8000	14-Feb-2004
105	Chess	2	9000	01-Jan-2004
108	Lawn Tennis	4	25000	19-Mar-2004

Table: PLAYER

PCode	Name	Gcode
1	Nabi Ahmad	101
2	Ravi Sahai	108
3	Jatin	101
4	Nazneen	103

(i) To display the name of all Games with their Gcodes

(ii) To display details of those games which are having PrizeMoney more than 7000.

(iii) To display the content of the GAMES table in ascending order of ScheduleDate.

(iv)To display sum of PrizeMoney for each of the Number of participation groupings (as shown in column Number 2 or 4)

(v ) SELECT COUNT(DISTINCT Number) FROM GAMES;

(vi) SELECT MAX(ScheduleDate),MIN(ScheduleDate) , COUNT(*) FROM GAMES GROUP BY Game Name;

(vii) SELECT SUM(PrizeMoney) FROM GAMES;

(viii) SELECT DISTINCT Gcode FROM PLAYER;

Differentiate between DDL and DML statements ?

110. (a) What do you understand by DOMAIN and TUPLE of a RELATION? 2

(b) Write a SQL commands for (i) to (iv) and write the output for (v) on the basis of table FURNITURE. 7

Table : FURNITURE

NO	ITEMNAME	TYPE	DATEOFSTOCK	PRICE	DISCOUNT
1	White lotus	Double Bed	23/02/02	30000	25
2	Pink feather	Baby cot	20/01/02	7000	20
3	Dolphin	Baby cot	19/02/02	9500	20
4	Decent	Office Table	01/01/02	25000	30
5	Comfort Zone	Double Bed	12/01/02	25000	25
6	Donald	Baby cot	24/02/02	6500	15
7	Royal Finish	Office Table	20/02/02	18000	30
8	Royal tiger	Sofa	22/02/02	31000	30
9	Econo sitting	Sofa	13/12/01	9500	25
10	Eating Paradise	Dining table	19/02/02	11500	25

(i) To list the ITEMNAME which are priced at more than 15000 from the FURNITURE table.

(ii) To list ITEMANME and TYPE of those items, in which DATEOFSOTCK is before 22/01/02 from FURNITURE table in descending order of ITEMNAME.

(iii) To display ITEMANME and DATEOFSTOCK of those items, in which the discount percentage is more than 25 from FURNITURE table.

(iv) To count the number of items, whose TYPE is “sofa” from FURNITURE table.

(v) Give the output of the following SQL statement.

(a) SELECT AVG(DISCOUNT) from FURNITURE where

GROUP BY ITEMNAME

(b) Select SUM(PRICE) FROM FURNITURE WHERE

DATEOFSTOCK<{12/02/02};

(VI) To display ITEMNAME whose name start with the letter ‘D’.

10.c) Write SQL commands for the statements (i) to (v) and give outputs for SQL queries (vi) to (vii) : on the basis of the table FURNITURE and ARRIVALS

i) To show all information about the baby cots from furniture table.

ii) To list the itemname which are priced at more than 15000 from the furniture table.

iii) To list itemname and type of those items, in which dateofstock is before 22/01/02 from the furniture

table in descending order of itemname.

iv) To display itemname and dateofstock of those items, in which the discount percentage is more than 25

from furniture table.

v) To count the number of items, whose type is ‘Sofa’ from furniture table.

vi) Select AVG(discount) from furniture, arrivals ;

vi) Select COUNT(distinct type) from Furniture ;

************************ THE END******************************

Search This Blog

Computer Science & Informatics Practices : XI , XII

SQL--HOME ASSIGNMENTS(XII Class)

Popular posts from this blog

XII- CS-- SOLVED SQL queries (SQL PRACTICAL ASSIGNMENS)

Python-MySQL Connectivity