mysq, comprehensive query of database

Record the comprehensive query of the database and review it to deepen your impression

Creating a teaching database contains four basic tables:

Teacher status table Teacher (Tno teacher number, TName teacher name, TDept teacher’s department);
Basic course table Course (Cno course number, Cname course name, cpno prerequisite course number, Tno teacher number);
Student status table Student (Sno student number, Sname, Sage age, Sex gender, sdept department);
Student performance basic table SC student curriculum (Sno student number, Cno course number, Grade score);

-- Delete table
DROP TABLE IF EXISTS Student;
--Create table
CREATE TABLE Student(
Sno CHAR(9) PRIMARY KEY,--student number primary key
Sname VARCHAR(20) NOT NULL,-- name is not empty and unique
Sage INT NOT NULL,-- age
Sex VARCHAR(5) NOT NULL,-- Gender
Sdept VARCHAR(10) NOT NULL -- system
)charset=utf8;

-- Delete table
DROP TABLE IF EXISTS Course;
--Create table
CREATE TABLE Course(
Cno CHAR(5) PRIMARY KEY,-- course number primary key
Cname VARCHAR(20) NOT NULL ,-- course name is not empty
Cpno CHAR(5) ,-- Priority course number
Tno VARCHAR(5) -- teacher number
)charset=utf8;

-- Delete table
DROP TABLE IF EXISTS Teacher;
--Create table
CREATE TABLE Teacher(
Tno VARCHAR(5) PRIMARY KEY,--teacher number primary key
Tname VARCHAR(20) NOT NULL ,--teacher name is not empty
Tdept VARCHAR(20) NOT NULL -- teacher number
)charset=utf8;

-- Delete table
DROP TABLE IF EXISTS SC;
--Create table
CREATE TABLE SC(
Sno CHAR(9),--student number primary key
Cno CHAR(5),-- course number primary key
Grade INT NOT NULL, -- grade
foreign key(Sno) references Student(Sno),
foreign key(Cno) references Course(Cno)
)charset=utf8;

If foreign key constraints are needed, just add them additionally to avoid errors in subsequent SQL delete and modify statements

alter table course add foriegn key(Cpno) references course(Cno);
alter table course add foreign key(Tno) references teacher(Tno);
alter table sc add foreign key(Sno) references student(Sno);
alter table sc add foreign key(Cno) references course(Cno);
Add table information

Practice 24 SQL statements. After verification, they all run correctly

1. Query the names and ages of all female classmates;

SELECT Sname,Sage FROM student where Sex = 'female';

2. Query the course numbers and names of all courses offered by teachers in the Computer Department;

 SELECT Cname,Cno FROM Course
WHERE Tno IN (
SELECT Tno FROM Teacher WHERE Tdept = 'Computer Science Department');

3. Query the course numbers of all female students’ selected courses;

SELECT Cno FROM sc WHERE Sno IN (SELECT Sno FROM student WHERE Sex = 'female');

4. Query the names and ages of students whose scores in at least one course are higher than 90 points;

SELECT Sname,Sage
FROM student WHERE Sno IN (
SELECT Sno FROM sc WHERE Grade > 90);

5. Query the names and grades of all students who took the elective “Database Principles”;

Two ways:
SELECT student.Sname,sc.Grade FROM student
JOIN sc ON student.Sno = sc.Sno
JOIN course ON sc.Cno = course.Cno
WHERE Cname = 'Database Principle';

SELECT student.Sname,sc.Grade
FROM student,sc,course
WHERE student.Sno = sc.Sno AND sc.Cno = course.Cno AND Cname = 'Database Principle';

?

6. Query the student numbers and names of students who have not taken “VB Design”;

SELECT Sno,Sname
FROM Student
WHERE Sno IN (
SELECT Sno FROM sc
WHERE Cno NOT IN (
SELECT Cno FROM course WHERE Cname = 'VB Design'));

7. Query the course names and numbers of courses that are not taught by teachers in the computer department;

SELECT Cname,Cno FROM Course
WHERE Tno NOT IN (
SELECT Tno FROM Teacher WHERE Tdept = 'Computer Science Department');

8. Query the student ID and course number of students who have not taken course “21”;

SELECT student.Sno,Cno FROM student,sc WHERE student.Sno=sc.Sno and Cno !='21';

9. Query the information of all students between the ages of 18 and 20 (including 20 and 18);

SELECT * FROM student WHERE Sage >= 18 AND Sage <= 20;
SELECT student.* FROM student WHERE Sage BETWEEN 18 AND 20;

10. Query the student numbers of students who have taken at least two courses, “21” and “41”;

SELECT Sno FROM sc WHERE Cno IN ('21', '41')
GROUP BY Sno HAVING COUNT(DISTINCT Cno) >= 2;

11. Check the student numbers and grades of students who took courses taught by Mr. Chu Liang

SELECT Sno,Grade FROM sc
WHERE Cno IN (
SELECT Cno FROM course WHERE Tno IN (
SELECT Tno FROM teacher WHERE Tname = 'Chu Liang'));

12. Query the student numbers of students who have taken at least two courses

SELECT Sno FROM sc WHERE Cno
GROUP BY Sno HAVING COUNT(DISTINCT Cno) >= 2;

13. Query the number of students taking advanced mathematics courses

SELECT count(Sno) AS number of people FROM sc
WHERE Cno IN (
SELECT Cno FROM Course WHERE Cname = 'Advanced Mathematics')

14. Query the average score of advanced mathematics courses

SELECT avg(Grade) AS Advanced Mathematics Average Score
FROM sc WHERE Cno IN (
SELECT Cno FROM Course WHERE Cname = 'Advanced Mathematics');

15. Query the name, age and gender of all students with the surname Hei

SELECT Sname,Sage,Sex FROM student WHERE Sname LIKE 'Black%';

16. Query the names of students whose student ID is older than the student Scarlet Witch’ but younger than him

SELECT Sname FROM Student
WHERE Sno > (
SELECT Sno FROM student WHERE Sname = 'Scarlet Witch'
)
AND Sage < (
SELECT Sage FROM student WHERE Sname = 'Scarlet Witch'
);

17. Query the names and ages of male students who are older than the average age of all female classmates

SELECT Sname,Sage FROM student
WHERE Sage > (
SELECT avg(Sage)FROM student WHERE Sex = 'female')
AND Sex = 'Male';

18. Query the student number of students who have only chosen one course

SELECT Student.Sno FROM Student
JOIN SC ON Student.Sno = SC.Sno
GROUP BY Student.Sno HAVING COUNT(SC.Cno) = 1;

19. Insert the student number, name and gender of students whose scores in each course are greater than or equal to 80 points into another existing basic table STU (Sno, SNAME, SEX)

CREATE TABLE STU (
Sno CHAR (9) PRIMARY KEY,
-- Student number primary key
SNAME VARCHAR (20) NOT NULL,
-- Name is not empty and unique
SEX VARCHAR (5) NOT NULL -- sex
) charset = utf8;
INSERT INTO STU (Sno, SNAME, SEX) SELECT
student.Sno,
student.Sname,
student.Sex
FROM student
WHERE student.Sno IN (
SELECT sc.Sno FROM sc GROUP BY sc.Sno HAVING MIN(sc.Grade) >= 80);

20. Change the teacher number of the English course to 412

UPDATE teacher SET Tno = 413
WHERE Tno IN (
SELECT Tno FROM course WHERE Cname = 'Level 4 English');

21. Improve the grades of female students whose grades are lower than the overall average by 5%

UPDATE sc
SET Grade = Grade * 1.05
WHERE Sno IN (
SELECT Sno FROM student WHERE Sex = 'female'
)
AND Grade < (
SELECT avg_grade FROM
(
SELECT avg(Grade) AS avg_grade
FROM sc
) AS avg_G
);

22. Delete Teacher Bai Mo’s class information from the basic table Course

update course set Tno=null
WHERE course.Tno IN (
SELECT Tno FROM teacher WHERE Tname = '白黑');


DELETE FROM course where Tno =(Select Tno from teacher WHERE Tname='白黑');
?

23. Check the names of teachers who teach at least two courses and their departments.

SELECT Tname,Tdept
FROM teacher left JOIN course ON teacher.Tno = course.Tno
GROUP BY Tname,Tdept HAVING COUNT(Cno) >= 2;

24. Query all teachers in the Computer Science Department

SELECT * FROM teacher WHERE Tdept = 'Computer Science Department';

Conclusion

For questions about foreign key constraints, please see

mysql, use sql statements to create basic tables for student-course database_sql creates student tables-CSDN Blog

Creating a teaching database contains four basic tables:

Add table information

Practice 24 SQL statements. After verification, they all run correctly

Conclusion