Interview SQL statement

Explain common SQL statements with four tables for students, courses, scores, and instructors. First, let's look at the relationship between the four tables.

Student (s #, sname, sage, ssex) Student table

Course (C #, cname, T #) Curriculum

SC (s #, C #, score) Orders table

Teacher (T #, tname) Instructor table

Clear and clear, the following uses SQL to create a table structure:

Create Database db_school;
Use db_school;
Drop table t_student;
Create Table t_student (
Id char (3) primary key,
Sname varchar (20 ),
Sage int,
Ssex varchar (2)
);
Exec sp_help t_student;

Create Table t_teacher (
Id char (3) primary key,
Tname varchar (20)
);
Create Table t_course (
Id char (3) primary key,
Cname varchar (20 ),
T_id char (3 ),
Foreign key (t_id) References t_teacher (ID)
);
Exec sp_help t_course;
Create Table t_ SC (
S_id char (3 ),
C_id char (3 ),
Primary Key (s_id, c_id ),
Foreign key (s_id) References t_student (ID ),
Foreign key (c_id) References t_course (ID)
);
Alter table t_ SC add score int;

Query statement:
-- 1 query the student ID of all the students whose scores are higher than those of the "001" Course
;
Select * From t_ SC;
Select
Sc1.s _ id from t_ SC SC1, t_ SC SC2 where sc1.s _ id = sc2.s _ id and sc1.c _ id =
'001' and sc2.c _ id = '002' and sc1.score> sc2.score;
-- 2. query the student ID and average score of students whose average score is greater than 60;

Select s_id, AVG (score) average score from t_ SC group by s_id having AVG (score)> 60;
-- 3 query the student ID, name, number of course selections, and total score of all students;

Select * From t_ SC;
Select ID, sname from t_student T;
Select ID, sname, TT. cnum, TT. totalscore
From t_student t
Left join (
Select s_id, count (c_id) cnum, sum (score) totalscore from t_ SC group by s_id
) TT
On T. ID = TT. s_id
-- 4 query the number of teachers surnamed "Li"

Select * From t_teacher;
Select count (*) from t_teacher where tname like 'Lee %'
-- 5. query the student ID and name of the student who has not learned the course "ye ping;

Select * From t_teacher;
Select ss. ID, ss. sname from t_student SS where ss. id not in (
Select S. ID from t_student S, t_ SC SC where S. ID = SC. s_id and SC. c_id in (
Select C. ID from t_course C, t_teacher t where T. tname = 'peiping 'and C. t_id = T. ID
)
);
-- 6 query the student ID and name of the student who has learned "001" and has also learned the course number "002"
;

Select * From t_ SC;
Select * From t_ SC SC, t_student s where S. ID = SC. s_id and c_id = '001' and s_id in (
Select s_id from t_ SC SC, t_student s where S. ID = SC. s_id and c_id = '002 ')

-- 7. query the student ID and name of all the students who have learned the course taught by instructor Zhang;

Select C. ID from t_teacher T, t_course c Where C. t_id = T. ID and T. tname = 'instructor Zhang ';

Select S. *, TT. * From t_student s right join
(
Select distinct s_id from t_ SC SC where SC. c_id in (
Select C. ID from t_teacher T, t_course c Where C. t_id = T. ID and T. tname = 'instructor Zhang'
)
) Tt on s. ID = TT. s_id;

-- 8. query the student ID and name of all students whose scores are lower than those of course number 001;

Train of Thought: first, find out the student ID number of course 002 with better scores than course 001. How can we know that course 002 has better scores than course 001? First, both the score fields are compared and involved.
To the t_ SC table, if you compare the same table, you can consider self-join, next, we will first investigate the student ID of all students whose score of "002" is lower than that of course no. "001,
With the student ID, you can get the name.

Select sc1.s _ id from t_ SC SC1, t_ SC SC2
Where sc1.s _ id = sc2.s _ id -- same student
And sc1.c _ id = '001' -- 001 subject
And sc2.c _ id = '002' -- 002
And sc2.score <sc1.score

How can I get the ID with the student ID? The student table contains the student ID and name. The key is how to connect the above table with the student table?

There are several ways to use table join or right join.

1.

Select T. ID, T. sname from t_student t,
(Select sc1.s _ id from t_ SC SC1, t_ SC SC2
Where sc1.s _ id = sc2.s _ id -- same student
And sc1.c _ id = '001' -- 001 subject
And sc2.c _ id = '002' -- 002
And sc2.score <sc1.score) TT
Where T. ID = TT. s_id

This method is not commonly used and looks awkward.

2. Join

Select T. ID, T. sname from t_student t right join (
Select sc1.s _ id from t_ SC SC1, t_ SC SC2
Where sc1.s _ id = sc2.s _ id -- same student
And sc1.c _ id = '001' -- 001 subject
And sc2.c _ id = '002' -- 002
And sc2.score <sc1.score
) Tt on t. ID = TT. s_id -- join condition. Only when the IDs of the two tables are the same indicates that they are a person.

The student ID obtained is used as the primary table (not a master-slave table), and t_student is the appendix. Because the student ID is used as the primary table, right join and right join are used to represent the primary table on the right, you can join left with the third question to understand the use of join.

---- 9. query the student ID and name of all students whose course scores are less than 60;

Select T. ID, T. sname from t_student t right join (
Select distinct s_id from t_ SC where score <60
) Tt on t. ID = TT. s_id

Why do we use right join? Let's take a look at the difference between left join and right join.

Left join: The left table is the primary table, and the query result is: join query result + data that exists in the left table but does not meet the conditions in the right table.

Right join: The right table is the primary table, and the query result is: join query result + data that exists in the right table but does not meet the conditions in the left table.

-- 10

,
Query the student ID and name of the student who has not completed all the courses;

Select T. ID, T. sname from t_student t right join (
Select s_id from t_ SC group by s_id having count (c_id) <(select count (*) from t_course)
) Tt on t. ID = TT. s_id

-- 11. query the student ID and name of at least one course and the student whose student ID is 002;

12

, Query at least the student ID is"

001
"Student ID and name of other students in one course;

Select T. ID, T. sname from t_student t join (
Select distinct s_id from t_ SC where c_id in (
Select c_id from t_ SC where s_id = '002'
)
) Tt on t. ID = TT. s_id and T. ID! = '002'

13


In the "SC" table,
Instructor Zhang'"The scores of the courses taught by the teacher are changed to the average scores of the courses;

1. Join Mode

Update SC
Set SC. Score = avgscore
From t_ SC SC
Join (
Select c_id, AVG (score) avgscore from t_ SC group by c_id
) T on SC. c_id = T. c_id
Join t_course TT on TT. ID = SC. c_id
Join t_teacher TTT on TTT. ID = TT. t_id and TTT. tname = 'instructor Zhang'

2. Non-join

Update
SC
Set
SC. Score = TT. avg_score
From t_ SC SC,
(
Select c_id, AVG (score) avg_score from t_ SC where c_id in (
Select C. ID from t_teacher T, t_course C where T. ID = C. t_id and T. tname = 'instructor Zhang'
) Group by c_id
) TT
Where TT. c_id = SC. c_id

14

, Query and"
001

The student ID and name of the student whose course is identical;

Select stuid, sname from t_student where stuid
In (
Select AA. s_id from
(
Select s_id from t_ SC
Group by s_id having count (*) = (select count (*) from t_ SC where s_id = '001 ')
-- Calculate the number of students with the same number of courses as student 001
) AA
Inner join
(
Select s_id from t_ SC where c_id in (
Select c_id from t_ SC where s_id = '001'
) -- Students who have learned any course of student 001
Group by s_id having count (*) = (select count (*) from t_ SC where s_id = '001 ')
-- And the number of courses that students on the 001 level are the same as the number of courses that students on the 001 level.
) BB on AA. s_id = BB. s_id and AA. s_id <> '001'
)

15

Delete SC table records for learning "ye ping" Course

Delete from t_ SC
Where c_id in (
Select distinct c_id from t_ SC SC
Join t_course C on SC. c_id = C. ID
Join t_teacher t on C. t_id = T. ID
And T. tname = 'peiping ')

 17

The average score shows the "Chinese" and "Mathematics" course scores of all students in the form of student ID, Chinese, mathematics, and valid courses, valid average score

Select tttt. t_s_id,
Max (Case cname when 'China' then score else' 'end) language,
Max (Case cname when 'mate' then score else ''end) math,
Max (cnum) valid course count,
Average max (avgscore) score
From
(
Select T. s_id t_s_id, T. Score, TT. cname, TTT. avgscore, TTT. cnum from t_ SC t
Join t_course tt on t. c_id = TT. ID
And (TT. cname = 'China' or tt. cname = 'mat ')
Join (
Select s_id, AVG (score) avgscore, count (c_id) cnum from t_ SC group by s_id
) TTT on T. s_id = TTT. s_id -- order by avgscore DESC
) Tttt
Group by tttt. t_s_id order by average score DESC

18


Query the highest score and lowest score of each subject: displayed in the following form: course Id, highest score, lowest score

Select c_id, max (score) highest score, min (score) lowest score from t_ SC group by c_id;

19

The average score of each subject ranges from high to high and the pass rate.

Select TTT. c_id1, TTT. avgscore, TTT. Percentage from
(
Select *
From
(Select c_id c_id1, AVG (score) avgscore from t_ SC T group by c_id) T
Join (
Select c_id, sum (case when score-60> = 0 then 1 else 0 end) * 100/count (*) Percentage from t_ SC group by c_id
) TT on TT. c_id = T. c_id1
) TTT
Order by TTT. avgscore, percentage DESC

20

Query the average score and pass rate of the following courses (Use
"1 line"

Show): Mathematics (
001

), Language (
002

)

Select
Max (case when TTT. cname = 'mate' then TTT. avgscore else ''end) Average math score,
Max (case when TTT. cname = 'mate' then TTT. jgpercent else ''end) Percentage of passing mathematics,
Max (case when TTT. cname = 'China' then TTT. avgscore else' end) Average Chinese score,
Max (case when TTT. cname = 'China' then TTT. jgpercent else' end) Percentage of pass in Chinese
From (
Select * from
(Select c_id, AVG (score) avgscore,
Sum (case when score-60> = 0 then 1 else 0 end) * 100/count (*) jgpercent
From t_ SC T group by c_id) T
Join
T_course TT on TT. ID = T. c_id and TT. cname in ('China', 'mat ')
) TTT

21

Query the average scores of different courses taught by different teachers from high to low

Select tttt. tname, tttt. cname, AVG (tttt. Score) avgscore from
(
Select T. *, TT. cname, TT. t_id, TTT. tname from t_ SC t
Join t_course tt on t. c_id = TT. ID
Join t_teacher TTT on TTT. ID = TT. t_id
) Tttt
Group by tttt. cname, tttt. tname order by tttt. tname, tttt. avgscore DESC;

-- 22. query the scores of 2nd to 3rd students in the following courses: Chinese (001), Mathematics (002), English (003), and average score of students.

Select
*
From
(
Select *, row_number () over (partition by tttt. c_id order by tttt. Score DESC) Rn from
(
Select * From t_ SC t
Join t_course tt on t. c_id = TT. CID
Join (
Select s_id Sid, AVG (score) avgscore from t_ SC group by s_id
) TTT on T. s_id = TTT. Sid
Join t_student s on T. s_id = S. stuid
) Tttt
) Ttttt
Where ttttt. Rn> = 2 and ttttt. Rn <= 3

-- 23. Print the score of each subject in Statistics. Number of students in each score segment: course Id, course name, [100-85], [85-70], [70-60], [<60]

Select *
From
(Select T. c_id,
Sum (
Case when score> = 0 and score <60 then 1 else 0 end
) Score_0_60,
Sum (
Case when score> = 60 and score <70 then 1 else 0 end
) Score_60_70,
Sum (
Case when score> = 70 and score <80 then 1 else 0 end
) Score_70_85,
Sum (
Case when score> = 80 and score <100 then 1 else 0 end
) Score_85_100
From t_ SC t
Group by T. c_id) TT join t_course TTT on TT. c_id = TTT. CID

 -- 24. query average scores and rankings of Students

Select *, row_number () over (order by avgscore DESC) Rn from
(Select s_id, AVG (score) avgscore from t_ SC group by s_id) T;

-- 25. query the top three records of each subject's score: (parallel scores are not considered)

Select * From t_ SC order by c_id, score DESC;
Select * from
(Select c_id, score, row_number () over (partition by c_id order by score DESC) Rn from t_ SC) TT
Where TT. Rn <= 3;

-- 26. query the number of students selected for each course

Select c_id, count (s_id) from t_ SC group by c_id

-- 27. query the student ID and name of all the students who have selected only two courses.

Select * from
(
Select s_id from t_ SC group by s_id having count (c_id) = 2
) T
Left join t_student tt on t. s_id = TT. stuid

-- 28. query the number of boys and girls

Select * From t_student;
Select ssex, count (stuid) from t_student group by ssex;

 -- 29 query the student list with the last name "Zhang"

Select * From t_student where sname like 'sheet % ';

--30. query the list of same-name students and count the number of students with the same name

Select sname, ssex, count (stuid) from t_student group by sname, ssex

 

	4. Drill credibility sqlserver2000 database software video tutorial SQL video tutorial
	$20.0

	SQL Server 200 advanced guide DBA Guide/#21 brand new genuine
	$18.0