50 questions in the database

Problem and Description: -- 1. student table Student (S #, Sname, Sage, Ssex) -- S # Student ID, Sname Student name, Sage year of birth, Ssex Student gender -- 2. course curriculum Course (C #, Cname, T #) -- C # -- Course No., Cname Course name, T # instructor No. -- 3. instructor table Teacher (T #, Tname) -- T # instructor ID, Tname instructor name

Problem and Description: -- 1. student table Student (S #, Sname, Sage, Ssex) -- S # Student ID, Sname Student name, Sage year of birth, Ssex Student gender -- 2. course curriculum Course (C #, Cname, T #) -- C # -- Course No., Cname Course name, T # instructor No. -- 3. instructor table Teacher (T #, Tname) -- T # instructor ID, Tname instructor name

Problem and description:
-- 1. Student table
Student (S #, Sname, Sage, Ssex) -- S # Student ID, Sname Student name, Sage year of birth, Ssex Student gender
-- 2. Curriculum
Course (C #, Cname, T #) -- C # -- Course No., Cname Course name, T # instructor No.
-- 3. Instructor table
Teacher (T #, Tname) -- T # instructor ID, Tname instructor name
-- 4. Orders table
SC (S #, C #, score) -- S # student ID, C # course number, score
*/
-- Create Test Data

Create table Student (S # varchar (10), Sname nvarchar (10), Sage datetime, Ssex nvarchar (10 ))
Insert into Student values ('01', n' Zhao re', '2017-01-01 ', n' mal ')
Insert into Student values ('02 ', n' ', '2017-12-21 ', n' Male ')
Insert into Student values ('03', n' sun feng', '2017-05-20 ', n' mal ')
Insert into Student values ('04 ', n' Li Yun', '2017-08-06 ', n' male ')
Insert into Student values ('05 ', n' Zhou mei', '2017-12-01', n' female ')
Insert into Student values ('06', n' Hulan ', '2017-03-01', n' female ')
Insert into Student values ('07', n' Zheng Zhu ', '2017-07-01', n' female ')
Insert into Student values ('08', n'wangju', '2017-01-20 ', n'female ')
Create table Course (C # varchar (10), Cname nvarchar (10), T # varchar (10 ))
Insert into Course values ('01', n' ', '02 ')
Insert into Course values ('02', n' math ', '01 ')
Insert into Course values ('03', n' ', '03 ')
Create table Teacher (T # varchar (10), Tname nvarchar (10 ))
Insert into Teacher values ('01', n' Zhang san ')
Insert into Teacher values ('02', n' Li si ')
Insert into Teacher values ('03', n' Wang wu ')
Create table SC (S # varchar (10), C # varchar (10), score decimal (18, 1 ))
Insert into SC values ('01', '01', 80)
Insert into SC values ('01', '02', 90)
Insert into SC values ('01', '03', 99)
Insert into SC values ('02', '01', 70)
Insert into SC values ('02', '02', 60)
Insert into SC values ('02', '03', 80)
Insert into SC values ('03', '01', 80)
Insert into SC values ('03', '02', 80)
Insert into SC values ('03', '03', 80)
Insert into SC values ('04 ', '01', 50)
Insert into SC values ('04 ', '02', 30)
Insert into SC values ('04 ', '03', 20)
Insert into SC values ('05 ', '01', 76)
Insert into SC values ('05 ', '02', 87)
Insert into SC values ('06', '01', 31)
Insert into SC values ('06', '03', 34)
Insert into SC values ('07', '02', 89)
Insert into SC values ('07', '03', 98)
Insert into SC values ('09', '03', 98)





-- 1. query the information and scores of students whose scores are higher than those of the "01" course and "02" Course
-- 1.1 check whether "01" course and "02" course exist at the same time.

-- 1.2 If "01" course exists but "02" course does not exist (null is displayed if it does not exist) (if the same content exists below, it will not be explained)


-- 2. query the information and scores of students whose scores are lower than those of the "01" course.
-- 2.1 check whether "01" course and "02" course exist at the same time.

-- 2.2 check whether "01" course and "02" course exist at the same time and "01" course does not exist but "02" Course


-- 3. query the student numbers, names, and average scores of students whose average score is greater than or equal to 60.


-- 4. query the student numbers, names, and average scores of students whose average score is less than 60
-- 4.1 SQL statement used to query student information with scores in the SC table.

-- 4.2 SQL statement used to query student information that does not have scores in the SC table.


-- 5. query the student numbers, student names, total number of course selections, and total scores of all courses.
-- 5.1 query all SQL statements with scores.

-- 5.2 query all SQL statements (including those with or without scores.


-- 6. query the number of teachers surnamed Li.



-- 7. Query Information of students who have learned how to teach "Zhang San"


-- 8. query the information of students who have never learned how to teach "Zhang San ".


-- 9. query the information of the student who has learned the course "01" and has also learned the course "02 ".


-- 10. query the information of students who have learned the course "01" but have not learned the course "02 ".


-- 11. Query Information of students who have not completed all courses


-- 12. query the information of at least one course and the same students whose student ID is "01"



-- 13. query the information of other students whose courses are identical to those of "01"


-- 14. query the names of students who have not learned any course taught by "Zhang San"



-- 15. query the student ID, name, and average score of two or more failed courses



-- 16. retrieve information about students whose scores are less than 60 in descending order.


-- 17. display the scores and average scores of all students in all courses from high to low on average


-- 18. query the highest score, lowest score, and average score of each subject in the following format: course ID, course name, highest score, lowest score, average score, pass rate, medium rate, excellent rate, and excellent rate
-- Pass> = 60, medium: 70-80, excellent: 80-90, excellent:> = 90


-- 19. sort by score and display the ranking


-- 20. query the total score of the student and rank the student
-- 20.1 query the total score of the student

-- 20.2 query the total score of the students and rank the students. SQL 2000 is completed using a subquery. When the total score is repeated, the ranking vacancy and the ranking vacancy are retained.


-- 20.3 query the total score of the students and rank them. If the total score is repeated, both the ranking vacancy and the ranking vacancy are retained.


-- 21. query the average scores of different courses taught by different teachers from high to low.


-- 22. query information about students whose scores are 2nd to 3rd in all courses and their scores.


-- 23. count the number of students in each score segment of each subject: course No., course name, [100-85], [85-70], [70-60], [0-60] and percentage
-- 23.1 count the number of students in each score segment of each subject: course No., course name, [100-85], [85-70], [70-60], [0-60]
-- Horizontal display

-- Vertical display 1 (display existing score segments)

-- Vertical display 2 (display existing score segments, and nonexistent score segments with 0)


-- 23.2 count the number of students in each score segment of each subject: course No., course name, [100-85], [85-70], [70-60], [<60], and percentage
-- Horizontal display

-- Vertical display 1 (display existing score segments)


-- 24. query average scores and rankings of Students
-- 24.1 query the average score of the students and rank them. SQL 2000 is completed using a subquery. If the average score is repeated, both the ranking vacancy and the ranking vacancy are retained.


-- 25. query the top three records of each subject
-- Repeat a 25.1 score to reserve a ranking vacancy

-- When the 25.2 score is repeated, the ranking vacancy is not retained and the ranking is merged.


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


-- 27. query the student ID and name of only two courses.


-- 28. query the number of boys and girls


-- 29. query the student information whose name contains the word "wind ".


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


-- 31. query the list of students born in 1990 (note: the type of the Sage column in the Student table is datetime)


-- 32. query the average scores of each course. The results are sorted in descending order based on the average scores. The average scores are the same and the course numbers are sorted in ascending order.


-- 33. query the student ID, name, and average score of all students whose average score is greater than or equal to 85


-- 34. query the names and scores of students whose course names are "Mathematics" and whose scores are less than 60.


-- 35. query the courses and scores of all students;


-- 36. query the name, course name, and score of any course whose score is over 70;


-- 37. Fail to query courses


-- 38. query the student ID and name of the student whose course number is 01 and whose score is higher than 80;


-- 39. Number of students per course


-- 40. query the students with the highest scores and their scores among the students who take the course "Zhang San ".



-- 41. query the student numbers, course numbers, and scores of students with the same course scores.


-- 42. query the first two of the best scores for each course

-- 43. count the number of optional students in each course (the number of students with more than 5 students is counted ). The course number and the number of electives are required to be output. The query results are sorted in descending order of the number of students. If the number of students is the same, they are listed in ascending order.


-- 44. Retrieve student IDs that take at least two courses


-- 45. query the student information of all optional courses


-- 46. query the age of each student
-- 46.1 is calculated based on the year.

-- 46.2 is calculated based on the date of birth. The current month and day <the date of birth, the age minus one

-- 47. query the students who celebrated their birthdays this week.

-- 48. query the students who have their birthdays next week.

-- 49. query the students who have their birthdays this month

-- 50. query the students who have their birthdays next month.