Interview Classic database query questions (students, courses, elective courses)

This is a few days ago a recent graduation friend's face question, is the interview often encountered the SQL Classic topic, records ()

Here I set up a corresponding table structure on the computer database for verifying the correctness of SQL statements.

To build a table statement:

• Student Table (Student_info)

CREATE TABLE ' Student_info ' (
' No ' varchar (255) Not NULL,
' Name ' varchar (255) DEFAULT NULL,
' Sex ' varchar (255) DEFAULT NULL,
' Age ' int (ten) DEFAULT NULL,
' Dept ' varchar (255) DEFAULT NULL,
PRIMARY KEY (' no ')
) Engine=innodb DEFAULT Charset=utf8;

• Timetable (Course_info)

CREATE TABLE ' Course_info ' (
' No ' varchar (255) Not NULL,
' Name ' varchar (255) DEFAULT NULL,
' Hours ' int (ten) is DEFAULT NULL,
PRIMARY KEY (' no ')
) Engine=innodb DEFAULT Charset=utf8;

• Selected timetable (choice_info)

CREATE TABLE ' Choice_info ' (
' Student_no ' varchar (255) is not NULL,
' Course_no ' varchar (255) is not NULL,
' Grade ' int (ten) DEFAULT NULL
) Engine=innodb DEFAULT Charset=utf8;

The data in each table is inserted as provided in the diagram

1. Find out which courses the students have taken and ask for a list of course names, course numbers, elective numbers and best results;

SELECT
Course. ' Name ',
Course. ' No ',
Count (choice.course_no) ' count ',
MAX (Choice.grade) Maxgrade
From
Course_info Course,
Choice_info Choice
WHERE
Choice.course_no = course. ' No '
GROUP by
Choice.course_no;

2. Count the number of elective courses for each student and display them in descending order of the number of elective courses (note: Students are not included in the class)

SELECT
Student. NAME,
Count (choice.course_no) ' count '
From
Choice_info Choice
Right joins Student_info student on choice.student_no = student. NO
GROUP by
Choice.student_no
ORDER by
COUNT (choice.course_no) DESC;

3. Query the average number of students who have more than 2 elective doors and the number of elective courses;

SELECT
A. ' Name ',
A.avg,
A.count
From
(
SELECT
Student. ' Name ' ' name ',
Ifnull (avg (Choice.grade), 0) ' avg ',
Count (choice.course_no) ' count '
From
Student_info Student
Left JOIN choice_info choice on choice.student_no = student. ' No '
GROUP by
Student. ' No '
) A
WHERE
A.count > 2;

4. Check the names of all students with test scores, name of course and exam results, and place the results in a new permanent table (assuming the new table is named Tb_rel_new)

Note: This topic contains new tables and insert query data

• Inquire

SELECT
A. ' Name ' Studentname,
Course. ' Name ' Coursename,
A.grade
From
Course_info Course
Left JOIN (
SELECT
Student. ' Name ' ' name ',
Choice.grade grade,
Choice.course_no Courseno
From
Student_info Student
Right JOIN choice_info choice on choice.student_no = student. ' No '
) A on A.courseno = course. ' No ';

• Create a tb_rel_new table

CREATE TABLE ' tb_rel_new ' (
' Studentname ' VARCHAR (255) DEFAULT NULL,
' Coursename ' VARCHAR (255) DEFAULT NULL,
' Grade ' INT (Ten) DEFAULT NULL
) ENGINE = INNODB DEFAULT CHARSET = UTF8;

• Inserting data

INSERT into Tb_rel_new (
Studentname,
Coursename,
Grade
) SELECT
A. ' Name ' Studentname,
Course. ' Name ' Coursename,
A.grade
From
Course_info Course
Left JOIN (
SELECT
Student. ' Name ' ' name ',
Choice.grade grade,
Choice.course_no Courseno
From
Student_info Student
Right JOIN choice_info choice on choice.student_no = student. ' No '
) A on A.courseno = course. ' No ';

Summary: This topic mainly examines the database SQL Foundation, the interview must quickly clarify the data relations, the problem solves.

Interview Classic database query questions (students, courses, elective course)