SQL Classic four-table query

Topic

Student (SID, Sname, Sage, ssex)--Student Information sheet (student number increment, student name, student birth date, gender);

Teacher (TID, tname)--Teacher Information sheet (teacher number increment, teacher name)

Course (CID, CNAME, TID)--Curriculum (course number increment, course name, teacher number foreign key)

SC (SID, CID, score)--score table (student number, course number, score)

Create database work;

Use of work;
Set names work;

# Create a table
CREATE TABLE Student (
Sid Int Primary Key auto_increment,
sname varchar (20),
Sage Date,
Ssex varchar (10)
)

CREATE TABLE Teacher (
TID int primary Key auto_increment,
Tname varchar (20)
);

CREATE TABLE Course (
CID int primary Key auto_increment,
CNAME varchar (20),
Tid int,
Foreign key (TID) References teacher (TID)
)

CREATE TABLE SC (
Sid Int,
CID int,
Score int
)

# Insert Data

# Insert data to student table first
INSERT INTO student values
(1, ' lei ', ' 1990-01-01 ', ' Male '),
(2, ' money electricity ', ' 1990-12-21 ', ' Male '),
(3, ' Sun Feng ', ' 1990-05-20 ', ' Male '),
(4, ' Levin ', ' 1990-08-06 ', ' Male '),
(5, ' Zhou Mei ', ' 1991-12-01 ', ' Women '),
(6, ' Wu Lan ', ' 1992-03-01 ', ' Women '),
(7, ' Qingshou ', ' 1989-07-01 ', ' Women '),
(8, ' Wang Ju ', ' 1990-01-20 ', ' female ');
# Teacher Table
Insert INTO teacher values
(1, ' Zhang San '),
(2, ' John Doe '),
(3, ' Harry ');
# timetable
INSERT INTO course values
(1, ' language ', 2),
(2, ' language ', 1),
(3, ' language ', 3);
# score Table
INSERT INTO SC values
(1,1,90),
(1,2,80),
(1,3,90),
(2,1,70),
(2,2,60),
(2,3,80),
(3,1,80),
(3,2,80),
(3,3,80),
(4,1,50),
(4,2,30),
(4,3,20),
(5,1,76),
(5,2,87),
(6,1,31),
(6,3,34),
(7,2,89),
(7,3,98);

Problem

--1. Information and course scores for students with "01" courses higher than "02" courses
Select
St.sid,
St.sname,
St.sage,
St.ssex,
Sc1.score as course 1 score,
Sc2.score as Course 1 score
From SC SC1,
SC SC2,
Student St
where Sc1.sid = Sc2.sid
and Sc1.sid = St.sid
and sc1.cid = 1
and sc2.cid = 2
and Sc1.score > Sc2.score

--2. Check the top 5 information of the Student's table;
SELECT * FROM student St limit 5;

--3. Check the student number and student name and average score of students with average scores greater than or equal to 60 points
Select
S.sname,
Sc.sid,
AVG (Sc.score)
From student S,
Sc
where S.sid = Sc.sid
GROUP BY Sc.sid
Having avg (Sc.score) >= 60

---4. Query the student information with the word "Feng" in the name
SELECT * FROM Student St where st.sname like '% wind ';

---5. Check the name and score of the student whose class is "math" with a score below 60
Select
St.sname,
Sc.score
From SC,
Student St
where St.sid = Sc.sid
and Sc.score < 60
and Sc.cid = (select
Co.cid
From Course Co
where co.cname = ' math ')

-6. Check the curriculum and scores of all students;
Select
St.sname,
Co.cname,
Sc.score
From Student St,
Sc
Course Co
where St.sid = Sc.sid
and co.cid = Sc.cid;

---7, the inquiry did not learn the "Zhang San" teacher teaching students of the information
SELECT *
From Student St
where St.sid not in (select St1.sid
From student St1,
Sc
Course Co,
Teacher Te
where St1.sid = Sc.sid
and co.cid = Sc.cid
and Co.tid = Te.tid
and Te.tname = ' Zhang San ');

---8. Information of students who have studied "Zhang San" and taught by teachers
Select
st1.*
From student St1,
Sc
Course Co,
Teacher Te
where St1.sid = Sc.sid
and co.cid = Sc.cid
and Co.tid = Te.tid
and Te.tname = ' Zhang San '

---9, the information of the students who have studied the courses numbered "01" and have also studied the course numbered "02".
Select
St.sid,
St.sname,
St.sage,
St.ssex
From SC SC1,
SC SC2,
Student St
where Sc1.sid = Sc2.sid
and Sc1.sid = St.sid
and sc1.cid = 1
and sc2.cid = 2

---10, the information of the students who have studied the number "01" but have not learned the course number "02"
Select
St.sid,
St.sname,
St.sage,
St.ssex
From Student St,
Sc
where St.sid = Sc.sid
and sc.cid = 1
and St.sid not in (select DISTINCT
Sc1.sid
From SC SC1,
SC SC2
where sc1.cid = 2)

---11. Information for students who have not studied all the courses

SELECT * FROM Student St
where St.sid not in
(SELECT
Distinct ST.SID
From SC SC1,
SC SC2,
SC SC3,
Student St
where Sc1.sid = Sc2.sid
and Sc1.sid = Sc3.sid
and Sc1.sid = St.sid
and sc1.cid = 1
and sc2.cid = 2
and sc3.cid = 3)

---12, inquire at least one class and study number for "01" students learn the same classmate information
SELECT DISTINCT
st.*
From Student St,
Sc
where St.sid = Sc.sid
and Sc.cid in (select
Sc.cid
From SC
where Sc.sid = 1)
and St.sid! = 1

--13, query and "01" Number of students to learn the course exactly the same as other students ' information
Select St.sid, St.sname, St.sage, St.ssex
from Student St,
SC
where St.sid = Sc.sid
and St.sid! = 1
and sc.cid in (select
Sc.cid
from SC
where Sc.sid = 1)
GROUP by St.s ID
has count (*) = (select
Count (*)
from SC
where Sc.sid = 1)

--14, query the name of the student who has not learned any of the courses taught by the "Zhang San" teacher #方法1:
Select *
from Student St
where st.sid not in (select
St.sid
from Student St,
SC
where Sc.s id = St.sid
and sc.cid = (select
Co.cid
from Course Co
where Co.tid = (select
Te.tid
from teacher Te
where te.tname = ' Zhang San '))
#方法2:
Select
s.*
from student s
where s.sid not in (select
Sc1.sid
From SC SC1,
Course C,
Teacher T
where sc1.cid = C.cid
and C.tid = T.tid
and t.tname = ' Zhang San ');

--15. Find out the number and name of all students with only two courses
Select
St.sid,
St.sname,
St.sage,
St.ssex
From Student St,
Sc
where St.sid = Sc.sid
GROUP BY Sc.sid
Having count (*) = 2

--16. Check the list of students born in 1990 (note: The type of sage column in the student table is datetime)

SELECT * FROM Student St where st.sage >= ' 1990-01-01 ' and st.sage <= ' 1990-12-31 ';
Select s.* from student s where s.sage like ' 1990-% ';
SELECT * FROM student St where st.sage between ' 1990-01-01 ' and ' 1990-12-31 ';

--17, the average score of each course is queried, and the results are arranged in descending order of average grades, and the average grade is the same, in ascending order by course number
Select Sc.cid, AVG (sc.score) from SC Group by Sc.cid ORDER by AVG (Sc.score) desc, sc.cid

--18. Check the name, course name and score of any course score above 70 points;
#方法1:
Select
S.sname,
C.cname,
Score
from student S,
SC,
Course c
where s.sid = sc.sid and sc.cid = C.cid
and score >;
#方法2:
Select St.sname, Co.cname, Sc.score
from Student St,
Course Co,
SC
where st.sid = Sc.sid
and S C.cid = Co.cid
and St.sid in (select
Sc.sid
from SC
Group by Sc.sid
have min (sc.score) >=)

--19. Number, name and average score of all students with average scores greater than or equal to 85
Select
St.sid,
St.sname,
avg (sc.score) Avgscore
from Student St,
SC
where st.sid = Sc.sid
Group BY Sc.sid
has avg (sc.score) >= (

)

---20. The course of failing the inquiry
Select
St.sname,
Co.cname,
Sc.score
From Student St,
Course Co,
Sc
where St.sid = Sc.sid
and sc.cid = Co.tid
and Sc.score < 60

---21. The number and name of the student whose course number is 01 and the course score is more than 80;
Select
St.sid,
St.sname
From Student St,
Sc
where St.sid = Sc.sid
and sc.cid = 1
and Sc.score < 60

--22. Number of students per course
Select Sc.cid, COUNT (*) from the SC group by Sc.cid
Select Cid,count (SID) from the SC group by SC.CID;

-23. The number of students enrolled in each course is counted (more than 5 participants). Require the output of the course number and the number of elective, the results of the query in descending order of people, if the number is the same, in ascending order by course number
Select
Sc.cid,
COUNT (*) sumstudent
From SC
GROUP BY Sc.cid
Having count (*) > 5
ORDER BY sumstudent Desc, sc.cid

--24. Check the student number, course number, student score of the students with the same grades
Select
Sc1.sid, Sc1.cid,sc2.sid, Sc2.cid,sc1.score
From SC SC1,
SC SC2
where Sc1.sid! = Sc2.sid
and sc1.cid! = Sc2.cid
and Sc1.score = Sc2.score

--25. Search for the student number of at least two elective courses
Select
Sc.sid
From SC
GROUP BY Sc.sid
Having Count (*) >= 2

---26. Inquiry about the students who have enrolled in all courses
Select
st.*
From SC,
Student St
where St.sid = Sc.sid
GROUP BY Sid
Having count (sc.cid) = 3;

--27. Check the age of each student
Select S.sname, (to_days (' 2018-06-23 ')-to_days (s.sage))/365 as age from student s;

--28. Find students for the birthday this month
Select S.sname from student s where s.sage like ' _____07% ';

--39. Information for students who have studied all the courses
Select
s.*
From student S,
SC SC1,
SC SC2,
SC SC3
where sc1.cid = 1
and sc2.cid = 2
and sc3.cid = 3
and Sc1.sid = Sc2.sid
and Sc1.sid = Sc3.cid
and S.sid = Sc1.sid
Group BY S.sid;

--30. Check the score of the 2nd to 5th place in the course 2, descending order
SELECT * FROM Course Co, sc where co.cid = Sc.cid and co.cid=2 order by sc.score desc limit 1,4

SQL Classic four-table query