47 questions help

Create Database mydb

Go

Use mydb

Create Table student

(

-- Student ID

Sno varchar (3) not null primary key,

-- Name

Sname varchar (4) not null,

-- Gender

Ssex varchar (2) not null,

-- Date of birth

Sbirthday datetime,

-- Class

Class varchar (5)

)

Create Table teacher

(

-- Instructor ID

TNO varchar (3) not null primary key,

-- Instructor name

Tname varchar (4) not null,

-- Instructor gender

Tsex varchar (2) not null,

-- Instructor Date of birth

Tbirthday datetime,

-- Title

Prof varchar (6 ),

-- Department

Depart varchar (10)

)

Create Table Course

(

-- Course number

CNO varchar (5) not null primary key,

-- Course name

Cname varchar (10) Not null,

-- Instructor ID

TNO varchar (3) references teacher (TNO)

)

Create Table score

(

-- Student ID

Sno varchar (3) not null references student (SNO ),

-- Course number

CNO varchar (5) not null references course (CNO ),

-- Score

Degree decimal (4, 1)

)

Insert into student

Values ('20170901', 'zeng hua', 'male', '2017-09-01 ', '20170901 ')

 

Insert into student

Values ('20170901', 'kuang ming', 'male', '2017-10-02 ', '20170901 ')

 

Insert into student

Values ('20170101', 'lily', 'female ', '2017-01-23', '20170101 ')

 

Insert into student

Values ('000000', 'Li Jun ', 'male', '2017-02-20', '20170101 ')

 

Insert into student

Values ('000000', 'wang fang', 'female ', '2017-02-10', '2017010 ')

 

Insert into student

Values ('20140901', 'lujun', 'male', '1970-06-03 ', '123 ')

 

Insert into teacher

Values ('20170901', 'licheng ', 'male', '2017-12-02', 'associate Professor ', 'computer Department ')

Insert into teacher

Values ('201312', 'zhang xu', 'male', '2017-03-12 ', 'lecturer', 'department of Electronic Engineering ')

Insert into teacher

Values ('20170901', 'wangping', 'female ', '2017-', 'ta ', 'computer system ')

Insert into teacher

Values ('20170901', 'Liu bing', 'female ', '2017-08-14', 'ta ', 'department of Electronic Engineering ')

 

Insert into course

Values ('3-105 ', 'introduction to computers', '123 ')

Insert into course

Values ('3-245 ',' OS ', '123 ')

Insert into course

Values ('6-166 ', 'Digital circuit', '123 ')

Insert into course

Values ('9-888 ', 'higher mathematics', '123 ')

 

 

Insert into score

Values ('20140901', '3-103 ', '86 ')

Insert into score

Values ('20140901', '3-105 ', '75 ')

Insert into score

Values ('20140901', '3-109 ', '68 ')

Insert into score

Values ('20180101', '3-20180101', '92 ')

Insert into score

Values ('20140901', '3-105 ', '88 ')

Insert into score

Values ('20140901', '3-109 ', '76 ')

Insert into score

Values ('20140901', '3-101 ', '64 ')

Insert into score

Values ('20180101', '3-20180101', '91 ')

Insert into score

Values ('20140901', '3-108 ', '78 ')

Insert into score

Values ('20140901', '6-20160901', '85 ')

Insert into score

Values ('20140901', '6-107 ', '79 ')

Insert into score

Values ('20140901', '6-20160301', '81 ')

 

Select * from student

Select * from teacher

Select * from course

Select * from Score

-- 1. query the sname, ssex, and class columns of all records in the student table.

-- 2. query all the units of the instructor, that is, the non-duplicate depart column.

 

-- 3. query all records in the student table.

 

-- 4. query all records with scores between 60 and 80 in the score table.

 

-- 5. query records with scores of or 88 in the score table.

 

-- 6. query the student table's "95031" Class or "female" student records.

 

-- 7. query all records in the student table in descending order of class.

 

-- 8. query all records in the score table in descending order of CNO and degree.

 

-- 9. query the number of students in the "95031" class.

 

-- 10. query the student ID and course number for the highest score in the score table.

 

-- 11. query the average score of the '3-105 'Course.

 

-- 12. query the average number of courses selected by at least five students starting with 3 in the score table.

 

-- 13. query the sno columns whose scores are between 70 and 90 when each student chooses multiple courses.

 

-- 14. query the sname, CNO, and degree columns of all students.

 

 

-- 15. query the sno, cname, and degree columns of all students.

 

-- 16. query the sname, cname, and degree columns of all students.

 

-- 17. query the average score of the course selected in the "95033" class.

 

-- 18. Assume that a grade Table is created using the following command:

-- Create Table grade (low int, UPP int, rank varchar (1 ))

-- Insert into grade values (90,100, 'A ')

-- Insert into grade values (80, 89, 'B ')

-- Insert into grade values (70,79, 'C ')

-- Insert into grade values (60, 69, 'D ')

-- Insert into grade values (0, 59, 'E ')

-- Query the sno, CNO, and rank columns of all students.

 

-- 19. query the records of all students whose scores are higher than those of the "3-109" course. // No sub-Query

 

-- 20. query the records of the students whose scores are not the highest score among the students who have selected multiple courses in the score.

-- 21. query all records with scores higher than the student ID "109" and the course number "3-105.

-- 22. query the records of all students born in the same year with a student ID of 108.

Year (sbirthday) is the same as Month day.

 

-- 23. query the student scores of "Zhang Xu.

 

-- 24. query the names of instructors with more than five students taking a course.

 

The subquery does not need to be duplicated.

-- 25. query the records of all students in class 95033 and class 95031.

 

Union

-- 26. query the CNO of a course with a score of more than 85.

 

-- 27. query the "Computer System" course list.

 

Score .*

-- 28. query the tname and Prof of teachers with different titles of "computer department" and "electronic engineering department. (Important)

 

 

-- 29. query the CNO, SnO, and degree numbers of students whose electives are "3-" and whose scores are at least higher than those of the electives "3-", and sort them in the descending order of degree.

 

-- 30. query the CNO, SnO, and degree numbers of students whose electives are "3-" and whose scores are higher than those of the electives "3. (change the score of students 3-105 to 95 before proceeding to this question)

 

 

-- 31. query the names, sex, and birthday of all teachers and students.

 

-- 32. query the names, sex, and birthday of all "female" teachers and "female" students.

 

-- 33. query the score table of the students whose scores are lower than the average scores of the course.

 

-- 34. query the tname and depart of all instructors.

 

-- 35 query the tname and depart of all instructors who have not given lectures.

Column name is null cannot be = (This question uses left join to form a large number of cartesian products, which is slow in operation)

Not in is efficient for subqueries.

 

-- 36. query the number of at least two boys.

 

-- 37. query the records of students who do not have the surname "Wang" in the student table.

The not like solution is the same, and non-customs subqueries are more efficient than related subqueries.

 

-- 38. query the age of each student in the student table.

Year (getdate () returns the current time year

 

-- 39. query the largest and smallest sbirthday date values in the student table.

 

-- 40. query all the records in the student table in the order of shift number and age.

 

-- 41. Query "male" teachers and their courses.

 

-- 42. query the sno, CNO, and degree columns of the highest score.

 

-- 43. query the sname of all the same-sex students as "Li Jun.

 

-- 44. query sname of students with the same gender and class as "Li Jun.

 

-- 45. query the student list of all male students taking the "Introduction to computer science" course.

 

-- 46. query the information of the student with the highest score in the score table. // Multi-layer nesting

 

 

47 questions help