MySQL Query Provide the SQL statements for each query. The table schema has been

Question

MySQL Query

Provide the SQL statements for each query. The table schema has been given:

Problem 1.

A. The numbers (CNO) and names (CNAME) of courses taken by J. Brown, sorted on CNO
in descending order.
B. The names of female students in any course taken by J. Brown. Do it using a nested
query with “IN”.
C. The names of female students who take every course taken by J. Brown.
D. The names of female students who take every course taken by J. Brown and no other
course

CNAME 21 Computer Science 17 Chemist 15 PhysicS 12 Mathematics l 13 Mathematics III RESULT CNO CNO STUDENT SNO SNAME ADDRESS SEX SCORE 31 L. Gatlin Holcomb F 11 N. Park Whitney M 22 S. Conner: Holcomb F 24 D. Davissc E. Quad M 19 1. Blake Holcomb M 4 T. Smith Cambridg F SNO 2 2 2 2 2 2 31 14 2 63 4 Histo 3 Geography 2 Biology 72 1 N. J. Sloan Whitney F 14 D. E. Knutl Whitney M 6 J. Brown Whitney 2 M. Robert Cambridg M 14 33 U. Smith Cambridg F 14 37 2 13 13 13 13 97 72 11 NULL 14 61 52 63

Explanation / Answer

Question A):

SQL Query :

select CNO,CNAME from COURSE where CNO in (select CNO from RESULT where SNO in (select SNO from STUDENT where SNAME = 'J.Brown'))

here we are using inner queries first the inner most query will get executed and we will get the SNO from STUDENT table where SNAME is J.Brown(i.e 6),then we will get the CNOs from RESULT where SNO is of J.Brown(13,15) and then finally we will get the required CNO and CNAMEs of those CNOs.

ANS:

13|Mathematics III
15|Physics

Question B):

SQL Query :

select SNAME from STUDENT where SNO in (select SNO from RESULT where CNO in (select CNO from RESULT where SNO in (select SNO from STUDENT where SNAME = 'J.Brown'))) and SEX == 'F'

In this second query is also pretty similar to the first one, only the difference is in first query we were getting the final output values from COURSE table and here we have to take those from SUDENT table. In the inner most query we will get the SNO for J.Brown(i.e. 6) in the next inner query we will get the CNOs for the SNO of J.Brown(i.e. 13 and 15) and in the last subquery we will get all the SNOs for those CNOs we have got from previous subquery(i.e 33,4,24,11,14) and the we have just added the filtering for SEX= 'F'.

ANS:

T.Smith
U.Smith

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.