MySQL example practice - MySQL

Tengmu 2022-02-13 08:51:36 阅读数:117

mysql example practice mysql

For reference only

 Insert picture description here


Database principle and application


Please, teacher “ Wang Gang ” Start the course with a grade of 90 Names of students with scores above 、 Course name and grades

SELECT full name ,
Course name ,
achievement
FROM Study ,
Course ,
Student ,
Teachers'
WHERE Study . Student number = Student . Student number
AND Study . Course no. = Course . Course no.
AND Study . Teacher number = Teachers' . Teacher number
AND Teachers' . Teacher's name =" Wang Gang "
AND Study . achievement >= 90;

Please take an elective course “ Wang Gang ” Names of students and colleges in all courses taught by the teacher

SELECT full name ,
The name of the College
FROM Student ,
college
WHERE Student . College code = college . College code
AND Student . Student number IN ( SELECT DISTINCT Student number
FROM Study
WHERE NOT EXISTS ( SELECT *
FROM Teachers' ,
Teaching
WHERE Teachers' . Teacher number = Teaching . Teacher number
AND Teachers' . Teacher's name =" Wang Gang "
AND NOT EXISTS ( SELECT *
FROM Study AS fir
WHERE fir. Student number = Study . Student number
AND fir. Course no. = Teaching . Course no. ) ) );

Please have no elective courses “ Software Engineering ” Student ID and name of

SELECT Student number , full name
FROM Student
WHERE Student number NOT IN (
SELECT Student number
FROM Study , Course
WHERE Study . Course no. = Course . Course no.
AND Course name = " Software Engineering ");

Modified to 2021.5.4, Thank God Hu for reminding orz

Students who have taken at least two courses are required to have a student number

SELECT Student number
FROM Study ,
Course
WHERE Study . Course no. = Course . Course no.
GROUP BY Student number
HAVING COUNT( Course . Course no. ) >= 2;

Seeking courses “ economics ” The names of the failed students and their test scores

SELECT achievement ,
full name
FROM Study ,
Student ,
Course
WHERE Study . Student number = Student . Student number
AND Course . Course no. = Study . Course no.
AND Course name = " economics "
AND achievement < 60;

Please at least take an elective course with the student number “T06” Students who take the same courses have the same student number

SELECT DISTINCT Student number
FROM Study
WHERE NOT EXISTS ( SELECT *
FROM Study AS fir
WHERE fir. Student number = "T06"
AND NOT EXISTS ( SELECT *
FROM Study AS sec
WHERE Study . Student number = sec. Student number
AND fir. Course no. = sec. Course no. ) );

Please at least take “C3,C4” Names of students and colleges in both courses

SELECT full name ,
The name of the College
FROM Study ,
Student ,
college
WHERE Study . Student number = Student . Student number
AND college . College code = Student . College code
AND Course no. = "C3"
AND Study . Student number IN ( SELECT Student number
FROM Study
WHERE Course no. = "C4" );

Inquire about “ Wang Shi ” Students have no elective course number and course name

SELECT Course no. ,
Course name
FROM Course AS a
WHERE Course no. NOT IN ( SELECT b . Course no.
FROM Course AS b,
Study ,
Student
WHERE b . Course no. = Study . Course no.
AND Study . Student number = Student . Student number
AND full name = " Wang Shi " );

Query the course number of the course not taken by any students

SELECT Course no.
FROM Course
WHERE Course no. NOT IN ( SELECT Course no.
FROM Study );

Ask for the names of students who have taken all courses

SELECT full name
FROM Student
WHERE NOT EXISTS ( SELECT *
FROM Course
WHERE Course no. NOT IN ( SELECT Course no.
FROM Study
WHERE Student number = Student . Student number ) );

Inquire about the courses of each college “ economics ” The average score of , And in the order from high to low

SELECT The name of the College ,
AVG( achievement ) AS " average "
FROM Study ,
Student ,
college ,
Course
WHERE Study . Student number = Student . Student number
AND Student . College code = college . College code
AND Study . Course no. = Course . Course no.
AND Course name = " economics "
GROUP BY Student . College code
ORDER BY AVG( achievement ) DESC;

Query elective courses “ economics ” The student's name and department , The results are arranged according to each department , At the same time, the grades are ranked from high to low

SELECT Student . full name ,
The name of the College ,
achievement
FROM Course ,
Study ,
Student ,
college
WHERE Course . Course no. = Study . Course no.
AND Student . Student number = Study . Student number
AND college . College code = Student . College code
AND Course name = " economics "
ORDER BY achievement DESC;

At school 30-45 Between ( contain 30 and 45) The course number and course name of the course and the instructor

SELECT Teaching . Course no. ,
Course name ,
Teacher's name
FROM Teaching ,
Course ,
Teachers'
WHERE Teaching . Course no. = Course . Course no.
AND Teaching . Teacher number = Teachers' . Teacher number
AND Class hours BETWEEN 30
AND 45;

Retrieve elective courses “ economics ” The name of the student with the highest score

SELECT full name
FROM Student ,
Study ,
Course
WHERE Student . Student number = Study . Student number
AND Course . Course no. = Study . Course no.
AND Course . Course name = " economics "
AND achievement = ( SELECT MAX( achievement )
FROM Study AS a,
Course AS b
WHERE a. Course no. = b. Course no.
AND b. Course name = " economics " );

The number of students who have selected more than 5 Course number and course name of human course

SELECT Course no. ,
Course name
FROM Course
WHERE Course name IN ( SELECT Course name
FROM Study ,
Course
WHERE Study . Course no. = Course . Course no.
GROUP BY Course name
HAVING COUNT( Student number ) > 5 );
copyright:author[Tengmu],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130851340546.html