My SQL Sample Paper

CLASS TEST
XI - INFORMATICS PRACTICES

Time: 1 hour MM: 20
Note: All questions are compulsory. Marks are indicated before questions.
1. Write the output of the following: - 3 Marks
i. SELECT CONCAT('Nimawat',NULL,'School');
ii. SELECT CHAR(65,67.3,69.3);
iii. SELECT DAYOFMONTH(‘2009-02-03);
iv. SELECT MOD(79,2)+ ROUND(-7344.48,1)+ TRUNCATE(15.865,-1)+SQRT(196);
v. SELECT SUBSTR(TRIM(' INDIA IS GREAT '),-5,3);
vi. Write a SQL function to remove leading $ character from a given expression.
P=’$$$$MUKESH$KUMAR'
2. What is Foreign Key? Define with an example. - 1 Mark
3. Write SQL commands to create the table COLLEGE with following specifications. 6 - Marks

Field Name	Data Type	Constraints
Cno	Int(4)	Primary Key
Name	Varchar(20)
Department	varchar(15)
Dateofadm	date
Fees	Double(7,2)
Gender	Char(1)

i. To list the structure of the table COLLEGE?
ii. Write SQL commands to insert 3 records in COLLEGE table.
iii. Add one more column Age of type int(2) default 18 in the COLLEGE table.
iv. Write SQL command to insert default Age.
v. Modify the column Age as int (3).
vi. Insert Age in first record of COLLEGE table.
vii. Change the name of the column Age to Student_Age.
viii. Create a new table StudentDetail with Name, Age & Gender fields from COLLEGE table.
ix. Remove the Primary Key from COLLEGE TABLE.
x. Delete all the tuples of COLLEGE table.
xi. Delete the last attribute of COLLEGE table.
xii. Delete the College table.

4. Write SQL commands to the HOSPITAL table. - 4 Marks

No	Name	Age	Department	Dateofadm	Charges	Gender
1	Arpit	62	Surgery	1998-01-21	300	M
2	Zarina	22	ENT	1997-12-12	250	F
3	Kareem	32	Orthopedic	1998-02-19	200	M
4	Arun	12	Surgery	1998-01-11	300	M
5	Zabed	30	ENT	1998-01-12	250	M
6	Ketaki	16	ENT	1998-02-24	250	F
7	Ankita	29	Cardiology	1998-02-20	800	F
8	Zareen	45	Gynecology	1998-02-22	300	F
9	Kailash	19	cardiology	1998-01-13	800	M
10	Shilpa	23	Nuclear Medicine	1998-02-21	400	F

i. To list the names of female patients who are in ENT department.
ii. To list names of all patients with their date of admission in ascending order.
iii. To count the number of patients with Age<30.>5. Given the following EMPLOYEE relation. Write SQL command. 6 - Marks

ENO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPT
7369	Sunita Sharma	CLERK	7902	1980-12-17	2800	NULL	20
7499	Ashok Singhal	SALESMAN	7698	1981-02-20	3600	300	30
7521	Rohit Rana	SALESMAN	7698	1981-02-22	5250	500	30
7566	Jyoti Lamba	MANAGER	7839	1981-04-02	4975	NULL	20
7654	Martin S.	SALESMAN	7698	1981-09-28	6250	1400	30
7698	Binod Goel	MANAGE	7839	1981-05-01	5850	NULL	30
7782	Cheten Gupta	MANAGER	7839	1981-06-09	2450	NULL	10
7788	Sudhir Rawat	ANALYST	7566	1987-04-19	5000	NULL	20
7839	Kavita Sharma	PRESIDENT	NULL	1981-11-17	5000	NULL	10
7844	Tushar Tiwari	SALESMAN	7698	1981-09-08	4500	0	30
7876	Anand Rathi	CLERK	7788	1987-05-23	6100	NULL	20
7900	Jagdeep Rana	CLERK	7698	1981-12-03	4950	NULL	30
7902	Sumit Vats	ANALYST	7566	1981-12-03	3500	3600	20
7934	Manoj Kaushik	CLERK	7782	1982-01-23	5300	NULL	10

i. To list the employee name and his annual salary (Annual salary=12*sal+100).
ii. To list the unique jobs from the table.
iii. To list the mgr which are in 7902, 7566, 7788.
iv. To list Comm as 1000 which are NULL.
v. To list all the columns in the ascending order of deptno and descending order of salary.
vi. To display the employee name and job of employees hired between Feb 20, 1981 and May 1, 1981.
vii. To list the name and salary of all the employees who earn more than 1200 and are in department 10 or 40.
viii. To list all the employees who do not have manager.
ix. To list name and salary of all employees who earn commissions.
x. To list the names of all employees where the second letter of their name is an 'a'.
Write the output of the following:
xi. SELECT LEFT(ENAME,4), COMM+SAL*20 FROM EMPLOYEE WHERE ENAME='Ashok Singhal';
xii. SELECT ENAME FROM EMPLOYEE WHERE ENAME LIKE 'a%';