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 2:

a. The courses for which there are no grades.
b. The numbers (SNO) and names (SNAME) of students having no scores at all (NULL
score value is considered as no score).
c. The possible names of Miss U. Smith's boyfriend. We know that he has taken every
course that was taken by Miss Smith.

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

(a)

select CNO,CNAME from COURSES

where CNO not in( select distinct CNO from RESULT );

(b)

select SNO,SNAME from STUDENT

where SNO not in (select distinct SNO from RESULT where SCORE is not null);

(c)

Query:

select SNO,SNAME from STUDENT where SNO in(

select r2.SNO from (select r1.SNO,r1.CNO,r2.SNO from RESULT r1,RESULT r2 where r1.CNO = r2.CNO and r1.SNO != r2.SNO and r1.SNO=(select SNO from STUDENT where SNAME = 'U. Smith'))

group by r2.SNO having count(r1.CNO)=(select count(CNO) from RESULT where SNO = (select SNO from STUDENT where SNAME = 'U. Smith'))

);

If you have any doubt then you can ask.

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

---

---

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