SQL statement Query

Question 1:
Problem description:
S (SNO, sname) Student Relationship. Sno is the student ID, and sname is the name
C (CNO, cname, cteacher) Course relationship. CNO is the course number, cname is the course name, And cteacher is the course teacher
SC (SNO, CNO, scgrade) Course Selection relationship. Scgrade is the score
1. (1) find out the names of all students who have taken the course taught by Mr. Li Ming.
-- Implementation code: method 1
Select *
From SC, C, S
Where SC. CNO = C. CNO and SC. Sno = S. SnO and cteacher = 'liming'
--- Method 2 :-------------
Select *
From S
Where SnO IN (select SnO
From C, SC
Where C. [CNO] = SC. [CNO] And cteacher = 'liming ')
 
(2) find out the names of all students who have not taken the course taught by Mr. Li Ming.
-- Implementation code: method 1
Select sname
From S
Where [SnO] Not in (select SC. [SnO]
From SC, c
Where SC. CNO = C. CNO and cteacher = 'liming ')
 
(3) find out the names of all students whose course number is "1"
-- Implementation code
Select sname
From S
Where [SnO] Not in (select [SnO] from SC where CNO = '1 ')
 
Certificate ------------------------------------------------------------------------------------------------------------------------------------------
2. List the names and average scores of students whose two or more courses fail.
-- Implementation code:
Select S. Sno, S. sname, avg_scgrade = AVG (SC. scgrade)
From S, SC, (select SnO
From SC
Where scgrade <60
Group by SnO
Having count (distinct CNO)> = 2)
Where S. Sno = A. SnO and SC. Sno = A. SnO
Group by S. Sno, S. sname
Certificate --------------------------------------------------------------------------------------------------------------------------------------
3. (1) list the names of all students who have learned both the "Chinese" Course and the "Mathematics" Course
-- Implementation code: method 1
Select S. Sno, S. sname
From S ,(
Select SC. SnO
From SC, c
Where SC. CNO = C. CNO and C. cname in ('China', 'mat ')
Group by SnO
Having count (distinct C. CNO) = 2) SC
Where S. Sno = SC. SnO
--- Method 2 :--------------------------
Select *
From (select S. Sno from S, SC, C where S. Sno = SC. SnO and SC. CNO = C. CNO and C. cname = 'China') T1,
(Select S. Sno from S, SC, C where S. Sno = SC. SnO and SC. CNO = C. CNO and C. cname = 'mat') T2,
S
Where t1.sno = t2.sno and t1.sno = S. SnO
------------------
(2) Use standard SQL nested statements to query the names of trainees who take all courses
Method 1: An SQL statement. Pay attention to the underlined part!

Select S. Sno, S. sname
From S ,(
Select SnO
From SC
Group by SnO
Having count (*) = (select count (*) from C) SC
Where S. Sno = SC. SnO

(3) query student IDs with more than 2 Optional Courses
-- Implementation code:
Select SnO from SC group by SnO having count (CNO)> 2
Query the student ID and name of more than 2 Optional Courses
-- Implementation code:
Select SnO, sname
From S
Where SnO IN (select SnO from SC group by SnO having count (CNO)> 2)

(4) list the top scores of all subjects with more than 3 students, and display the subject number and score fields.
--- The positive solution is as follows: (the number of students whose subjects are 2 and 3 is greater than 3)
Select CNO, max (scgrade) as 'highest point'
From SC
Where CNO in (select CNO from SC group by CNO having count (SNO)> 3)
Group by CNO
Order by CNO
Certificate -----------------------------------------------------------------------------------------------------------------------------------
4. List the student IDs, names, and scores of all students whose scores are higher than those of the math course.
-- Implementation code: method 1
Select *
From (select S. Sno, S. sname, SC. scgrade from S, SC, c
Where S. Sno = SC. SnO and SC. CNO = C. CNO and C. cname = 'China') T1,
(Select S. Sno, S. sname, SC. scgrade from S, SC, c
Where S. Sno = SC. SnO and SC. CNO = C. CNO and C. cname = 'mat') T2
Where t1.sno = t2.sno and t1.scgrade> t2.scgrade
Certificate ------------------------------------------------------------------------------------------------------------------------------------
5. query the number of students who have selected the course
-- Implementation code:
Select students = count (distinct [SnO]) from SC

Summary:
1. Select * from a, B where a. ID = B. ID and
Select * from a inner join B on A. ID = B. Id. The result set is the same;
2. When "finding out the names of all students who have not taken the course taught by Mr. Li Ming,
-- It is better to use where [SnO] Not in (which can be a column value generated by an SQL statement.
Select sname
From S
Where [SnO] Not in (select SC. [SnO]
From SC, c
Where SC. CNO = C. CNO and cteacher = 'liming ')
3. Select count (*) from C can be directly substituted into SQL statements, rather than stored procedures;
Select S. Sno, S. sname
From S ,(
Select SC. SnO
From SC, c
Where SC. CNO = C. CNO
Group by SnO
Having count (*) = (select count (*) from C) SC
Where S. Sno = SC. Sno:
Studentname classname grade
Mike English 65
Mike math 75
Jerry English 80
Jerry math 68
Lida English 88
Lida Chinese 77
Lida math 90
Find the students whose scores are greater than 75.
The following result is displayed:
Studentname classname grade
Lida English 88
Lida Chinese 77
Lida math 90
 
Select t1.studentname, classname, grade
From # T1, (select studentname
From #
Where grade> 75
Group by studentname
Having count (*) = (select count (*) from (select distinct classname from #) a) T2
Where t1.studentname = t2.studentname
 
Note:
1. "A" in from (select distinct classname from #) A must be available. The record set obtained from (select distinct classname from #) is treated as a new table.
2. When a group by statement is used in an SQL statement, functions such as Count (), sum (), AVG (), max (), and min () are generally used;
However, if you directly use these functions without using the group by statement, all datasets are treated as a group for processing.

Question 2:
The schema of a relational database is known as follows:
Employee EMP (employee ID, name, salary, Department)
Department dept (Department No., Department name, department manager employee No)
(1) Use the query analyzer to create a table. The requirements are as follows:
A. The primary external code of each table.
B. Enter the employee name and department name.
C. The salary is an integer from 800 to 5000.
(2) Insert the following data:
Employee (e01 Zhao 2500 d02
E02 RMB 3000 d02
E03 sun 6000 d01
E04 Li 5500 d01
E05 week 3500 d03
E06 Wu 2000 d04
E07 Zheng 3000 d04)
Department (d01, personnel, e04
D02, information, e01
D03, sales, e05
D04, finance, e06)
(3) use SQL to express the following query: Find the names and salaries of employees whose salaries are higher than the salaries of their department managers.
Select *
From emp a, (select EMP. Name, EMP. Salary, Dept .*
From EMP, Dept
Where EMP. employee ID = Dept. employee ID of the department manager) B
Where. department = B. department No. and. salary> B. salary (4) completed by SQL: Change the salary of department manager d01 to the average salary of all employees in the department.
Update EMP
Set salary = (select AVG (salary) from EMP where Department = 'd01 ')
From EMP, Dept
Where EMP. employee ID = Dept. employee ID of the department manager and department ID = 'd01'
(5) add an advertisement department numbered d05. Insert into dept values ('d05 ', 'ads', 'e08') Summary: Do not rush to answer questions. Check the table structure first, and try again! In question (3), the employee ID (e01.) is the primary key and the ID field. Determine the ID field first, and then use (e01.) according to the identification field logic ..) connect the two tables to obtain the name, salary, and other data set B of each manager in the department table. The number of records in this data set = the number of records in the Department (Dept) table. Then, the dataset B is connected to the employee (EMP) table to obtain the name and salary of the employee whose salary is higher than the manager's salary in the department ". Question 1: create the following basic tables. The table structure is as follows:
Borrower:
The name of the certificate number is a class name
03001 Li Lei Information Department 03-1
03002 Zhao information 03-1
03003 computers 03-2
03004 sun computer 03-3
......
Loans:
Borrow certificate number book registration number date of borrow
03001 t01 2004
03001 t02 2004
03001 t03 2004
03002 t01 2005
03002 t02 2005
03003 t03 2006
03003 t04 2006
03004 t05 2007
.......
Books:
Suo Shu No. Book title author book Register No. Publishing House Price
Tp311.13 Data Structure Li Wei t01 science 19.00
Tp311.13 Data Structure Li Wei t02 science 19.00
Tp120065 introduction to Data Structure Li Wei t03 Beihang 16.50
Tp.1599 Data Communication Yang zhit04 Tsinghua 28.50 (1): searches for the number, name, Department name, and number of books of the student who borrowed less than three books.
--- The positive solution is as follows ------------
Select *
From borrower B, (select count (*) as the number of books to borrow, the card number as the card number
From loans
Group by library ID
Having count (*) <3)
Where B. debit card number = A. debit card number (2). Search for the name, Department name, title, and date of the student of the same name as any of the books borrowed by ZHAO Lei.
--- The positive solution is as follows ------------
Select name, Department name, book registration number, borrow date
From borrower BB, loans LL
Where BB. Library id = ll. Library ID and name <> 'lily'
And book registration number in (select Book Registration Number
From borrower B, loans l
Where B. certificate number = L. library ID and B. name = 'lil') (3). Create the SB view of the books borrowed by the student of the Information Management Department. The attribute columns of this view are the Document ID, name, class, book registration number, and book number, composition of publishing house and borrowing date
 
Question 2:
There is a database of electives for a student, which contains the following three tables:
Student (student ID, name, gender, age, Department)
Course (course number, course name, instructor );
Optional Courses (student ID, course number, score );
Use SQL to complete the following functions:
(1) When creating a table, you must declare in the definition:
A. The primary external code of each table.
B. The student's age is between 16 and 30.
C. The student name and Course name cannot be blank.
D. Set the score to either a null value or an integer ranging from 0 to 100.
(2) Insert the following data:
Students (101, Zhang San, male, 16, mathematics)
102, Li Si, male, 18, computer
103, Wang Ling, female, 17, Chinese
105, Li Fei, male, 19, computer
109, Zhao Si, female, 18, history
110, Li Ping, male, 20, chemistry)
Course (203, operating system, Cheng Yu
279, advanced mathematics, Wang Bei
210, modern literature, Wang Lin
243, organic chemistry, Shen Tong
204, data structure, Zhang Qing)
Optional Courses (101,203, 82
105,203, 59
102,279, 90
101,279, 88
105,279, 82
110,279, 68
109,210, 72
103,210, 90
110,243, 92
101,204, 85
105,204, 91
102,204, 56
101,210, 77
101,243, 88)
(3) use SQL to complete the following queries:
1. List the names and scores of all optional courses
------ The solution is as follows ----------------
Select Course name, score
From students, electives, courses
Where student. Student ID = electives. Student ID and electives. course No. = course. course no. And name = 'zhang san' 2. List the names of all students who pass the course
------ The solution is as follows ----------------
Select T1. name
From student T1, (select student ID
From electives
Where score> = 60
Group by student ID
Having count (*) = (select count (*) from (select distinct course No. From course) A) T2
Where T1. student ID = T2. student ID 3. List the names and scores of the students with the highest scores among the students who take the courses taught by Zhang Qing.
------ The solution is as follows ----------------
Select top 1 Name, score
From student, optional
Where student. Student ID = electives. Student ID and student. Student ID in (select student ID from course, optional where electives. Course number = course. Course number and instructor = 'zhang qing ')
Order by score DESC (4), delete all course selection records with failed scores
Delete from select where score <60 (5). Change the score of the 105 course to the average score of the course.

Update electives
Set score = (select AVG (score) from electives where course number = '000000 ')
From electives
Where student ID = '20140901' and course number = '20160901'