One SQL statement interview question: for students who take all courses, SQL statements

One SQL statement interview question: for students who take all courses, SQL statements

For a job interview a few days ago, there was an SQL question: three tables are provided: students, courses, and scores.

A seemingly simple problem made me difficult. I changed it and changed it, painted it and scratched my ears. Because there were not many blank positions in the exam, I had to give up. The mood is greatly affected, although the company still obtains an offer.

But I feel ashamed of myself!

So I tried on the machine:

Create a table first

Use test; gocreate table student (sno varchar (50) not null, name varchar (50) not null); insert into student (sno, name) values ('001 ', 'zhang san'); insert into student (sno, name) values ('002 ', 'Li si'); insert into student (sno, name) values ('003 ', '王'); create table class (cno varchar (50) not null, name varchar (50) not null) insert into class (cno, name) values ('c01 ', 'Data struct'); insert into class (cno, name) values ('c02 ',' OS '); insert into class (cno, name) values ('c03 ', 'computer composition principle '); insert into class (cno, name) values ('c04', 'network basics '); create table score (sno varchar (50) not null, cno varchar (50) not null, score decimal () not null) insert into score (sno, cno, score) values ('001', 'c01', 80 ); insert into score (sno, cno, score) values ('001', '02', 85); insert into score (sno, cno, score) values ('001 ', 'c03', 89); insert into score (sno, cno, score) values ('001', 'c04 ', 87); insert into score (sno, cno, score) values ('002 ', 'c01', 80); insert into score (sno, cno, score) values ('003', 'c04 ', 70 );

I think of three ways:

1,

[SQL]View plaincopy

1. Select * from student s
2. Where not exists (select 1 from class c
3. Where not exists (select 1 from score
4. Where sno = s. sno and cno = c. cno ));

Two not exists. I wrote only one.

From embedded to external,

1) there is no course selected for the current student

Select 1 from class c where not exists (select 1 from score where sno = s. sno and cno = c. cno)

2) There is no such situation. 1) that is, there is no electives for the current student.

In other words, the current student takes all the courses

2,

[SQL]View plaincopy

1. Select * from student where sno not in (
2. Select st. sno from student st, class c
3. Where not exists (select 1 from score
4. Where sno = st. sno and cno = c. cno)
5. )

The nested statements are for students who have not taken the course.

Students outside of this list

3,

[SQL]View plaincopy

1. Select * from student where sno in (
2. Select st. sno from student st
3. Inner join score SC on st. sno = SC. sno
4. Group by st. sno
5. Having count (*) = (select count (*) from class)
6. )

This statement is easy to understand. However, the efficiency may not be high. I'm not sure whether (select count (*) from class) will be executed many times.