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

Last updated at April 16, 2024 by

This question asked in CBSE Comptt, 2014

Consider the following tables SCHOOL and ADMIN and answer the following questions:

Question 4 Long Answer 5 - image1.jpg

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

 

Question 4 Long Answer 5 - image2.jpg

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 (i) Long Answer 5 - image 1.jpg

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 (i) Long Answer 5 - image 2.jpg

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 (ii) Long Answer 5 - image 2.jpg

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 (iii) Long Answer 5 - image 2.jpg

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 .

 

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.

Ask a doubt
Davneet Singh's photo - Co-founder, Teachoo

Made by

Davneet Singh

Davneet Singh has done his B.Tech from Indian Institute of Technology, Kanpur. He has been teaching from the past 14 years. He provides courses for Maths, Science, Social Science, Physics, Chemistry, Computer Science at Teachoo.