Question 4 - Past Year - 5 Mark Questions - Chapter 9 - Structured Query Language

Transcript

This question asked in CBSE Comptt, 2014 Consider the following tables SCHOOL and ADMIN and answer the following questions: Table: SCHOOL CODE TEACHERNAME SUBJECT DOJ PERIODS EXPERIENCE 1001 Ravi Shankar English 12/03/2000 24 10 1009 Priya Rai Physics 03/09/1998 26 12 1203 Lisa Anand English 09/04/2000 27 5 1045 Yash Raj Maths 24/08/2000 24 15 1123 Ganan Physics 16/07/1999 28 3 1167 Harish B Chemistry 19/10/1999 27 5 1215 Umesh Physics 11/05/1998 22 16 Table: ADMIN CODE Gender Designation 1001 Male Vice Principal 1009 Female Co-ordinator 1203 Female Co-ordinator 1045 Male HOD 1123 Male Senior Teacher 1167 Male Senior Teacher 1215 Male HOD Write SQL statements for the following: Question 4 (i) To display TEACHERNAME, PERIODS of all teachers whose periods are more than 25. Answer: To display TEACHERNAME, PERIODS of all teachers whose periods are more than 25, the WHERE clause followed by the condition ie., PERIODS>25 should be used in the query. QUERY: SELECT TEACHERNAME,PERIODS FROM SCHOOL WHERE PERIODS>25; Question 4 (ii) To display all the information from the table SCHOOL in descending order of experience. Answer: To display all the information from the table SCHOOL in descending order of experience, the ORDER BY clause followed by the column name ie., EXPERIENCE followed by the keyword DESC should be used in the query. QUERY: SELECT * FROM SCHOOL ORDER BY EXPERIENCE DESC; Question 4 (iii) To display DESIGNATION without dupli-cate entries from the table ADMIN. Answer: To display DESIGNATION without duplicate entries from the table ADMIN, the DISTINCT keyword should be used in the query. QUERY: SELECT DISTINCT DESIGNATION FROM ADMIN; Question 4 (iv) To display TEACHERNAME, CODE and corresponding DESIGNATION from tables SCHOOL and ADMIN of Male teachers. Answer: To display TEACHERNAME, CODE and corresponding DESIGNATION from tables SCHOOL and ADMIN of Male teachers, The WHERE clause followed by the condition ie., GENDER=’MALE’ is used to select only those records of Male teachers. The tables SCHOOL and ADMIN should be joined using an EQUI JOIN ie., SCHOOL.CODE=ADMIN.CODE QUERY: SELECT TEACHERNAME,SCHOOL.CODE,DESIGNATION FROM SCHOOL,ADMIN WHERE GENDER=’MALE’ AND SCHOOL.CODE=ADMIN.CODE; Question 4 (v) Find the primary key of table SCHOOL. Answer: A primary key is an attribute which can uniquely identify the records in a relation/table. In table SCHOOL, the attribute with distinct values which can uniquely identify each record is CODE. So, the primary key of table SCHOOL is CODE.