SAMPLE PAPER-MYSQL

SAMPLE PAPER

XI - INFORMATICS PRACTICES

Time: 1 hr 30 min MM: 30

Note: All questions are compulsory. Marks are indicated before questions.

1. Which command is used to list the structure of a table? (½)

2. What is the purpose of ALTER TABLE command? (½)

3. What is the difference between DDL and DML? Explain with an example. (1)

4. What is Foreign Key? Define with an example. (1)

5. Give the examples of character functions. (1)

6. Give the examples of numeric functions. (1)

7. Write SQL commands to create the table COLLEGE with following specifications. (7)

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

1. Write SQL commands to insert 5 records in COLLEGE table.

2. Write SQL command to insert default age.

3. Add one more column Address of type char (20) in the COLLEGE table.

4. Modify the column Address as char (25).

5. Insert address in first record of COLLEGE table.

6. Change the name of the column Address to Home_Address.

7. Create a new table StudentDetail with Name,Age & Gender fields from COLLEGE table.

8. Remove the Primary Key from COLLEGE TABLE.

9. Delete all the tuples of COLLEGE table.

10. Delete the last attribute of COLLEGE table.

11. Delete the College table.

12. What is the purpose of WHERE clause?

13. What is the purpose of DISTINCT clause?

14. What is column alias?

8. Write SQL commands to the HOSPITAL table. (5)

TABLE: HOSPITAL

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

1. To select all the information of patients of cardiology department.

2. To list the names of female patients who are in ENT department.

3. To list names of all patients with their date of admission in ascending order.

4. To display Patient's Name, Charges, Age of only male patients.

5. To count the number of patients with Age<30.

6. To insert a new row in the HOSPITAL table with the following data:

11,'Aftab',24,'Surgery','1998-02-25',NULL,'M'

1. To display all the patients whose charges are NULL.

2. To list position of character 'a' in the names of patients.

3. To display first three characters of the patient name.

4. Change age & charges of Kailash to 21, 1000 respectively.

1. Given the following EMPLOYEE relation. Write SQL command. (13)

TABLE: EMPLOYEE

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

1. To display the employee name and the incremented value of SAL as SAL+300.

2. To list the employee name and his annual salary (Annual salary=12*sal+100).

3. Display the ename and sal where comm is NULL.

4. To list the unique jobs from the table.

5. To list the salary where salary is less than the commission.

6. To list the mgr which are in 7902, 7566, 7788.

7. To list all the columns in the ascending order of deptno and descending order of salary.

8. To display the employee name and job of employees hired between Feb 20, 1981 and May 1, 1981.

9. Display the ename and deptno of all employees in department 20 and 30 in alphabetical order.

10. To list the name and salary of all the employees who earn more than 1200 and are in department 10 or 40.

11. To list all the employees who do not have manager.

12. To list name and salary of all employees who earn commissions.

13. To list the names of all employees where the second letter of their name is an 'a'.

Write the output of the following:

14. SELECT LEFT(ENAME,4), COMM+SAL*20 FROM EMPLOYEE WHERE ENAME='Ashok Singhal';

15. SELECT ENAME FROM EMPLOYEE WHERE ENAME LIKE 'a%';

16. SELECT AVG(SAL),MAX(SAL),MIN(SAL),SUM(SAL) FROM EMPLOYEE WHERE JOB LIKE 'SALES%';

17. SELECT COUNT(COMM) FROM EMPLOYEE WHERE DEPT=20;

18. SELECT DEPT, MAX(SAL) FROM EMPLOYEE GROUP BY DEPT HAVING MAX(SAL)>2900;

19. SELECT CONCAT('Nimawat',NULL,'School');

20. SELECT SUBSTR('OurIndia',4,3);

21. SELECT TRIM(' INDIA ');

22. SELECT MOD(99,2);

23. SELECT ROUND(-2578.78,1);

24. SELECT TRUNCATE(1.865,1)+SQRT(169);

25. SELECT SUBSTR(TRIM(' INDIA IS GREAT '),3,9);

26. Write a SQL function to remove trailing # character from a given expression.

P='NITIN#KUMAR##########'