6. SQL Completion Test Exercise

Create DATABASE Kaoshi
Use Kaoshi
--Table (i) Student (student table)
(The property name data type can be null meaning
Sno Char (3) No study number (main code)
Sname Char (8) No student name
Ssex Char (2) No student sex
Sbirthday datetime student Birth date
Class Char (5) available in Student's Class)

CREATE TABLE Student
(
Sno Char (3) primary key NOT NULL,--number (main code)
Sname Char (8) NOT null,--student name
Ssex char (2) NOT null,--student sex
Sbirthday datetime,--Student date of birth
Class char (5),--Student's class
)

--Table (ii) Course (curriculum)
(The property name data type can be null meaning
Cno Char (5) No course number (main code)
Cname Varchar (10) No course name
Tno Char (3) No faculty number (outside code))

CREATE TABLE Course
(
CNO Char (5) primary key NOT NULL,--curriculum (Master code)
CNAME varchar (TEN) NOT null,--course name
TNO Char (3) NOT NULL,--faculty number (outside code)
)

--Table (iii) score (score table)
(The property name data type can be null meaning
Sno Char (3) No study number (external code)
Cno Char (5) No course number (outside code)
Degree Decimal (4,1) can score
Main code: sno+ Cno)

CREATE TABLE Score
(
Sno Char (3) NOT NULL,--school number (outside code)
CNO Char (5) NOT NULL,--curriculum (outside code)
degree Decimal (4,1),--score Table
)

--(Table (iv) Teacher (Teacher's table)
Whether the property name data type can be null meaning
Tno Char (3) No Faculty number (main code)
Tname Char (4) No Faculty name
Tsex Char (2) No Faculty sex
Tbirthday DateTime Faculty Birth date
Prof Char (6) Available title
Depart Varchar (10) whether the faculty is located in the Department)

CREATE TABLE Teacher
(
TNO Char (3) primary key NOT NULL,--faculty number (master code)
Tname char (4) NOT NULL,--faculty name
Tsex char (2) NOT NULL,--faculty sex
Tbirthday datetime,--Faculty Birth date
Prof Char (6),--title
Depart varchar () NOT NULL,--Faculty Department
)

Table (i) Student
Sno Sname Ssex Sbirthday class
108 Zeng Hua men 1977-09-01 95033
105 Kuanming 1975-10-02 95031
107 Wang Liju 1976-01-23 95033
101 Li Junnan 1976-02-20 95033
109 Wang Fang Women 1975-02-10 95031
103 Lu Junnan 1974-06-03 95031

SELECT * FROM Student
INSERT into student values (' 108 ', ' Zeng Hwa ', ' Male ', ' 1977-09-01 ', ' 95033 ')
INSERT into student values (' 105 ', ' Kuanming ', ' Male ', ' 1975-10-02 ', ' 95031 ')
INSERT into student values (' 107 ', ' Wang Li ', ' female ', ' 1976-01-23 ', ' 95033 ')
INSERT into student values (' 101 ', ' Li June ', ' male ', ' 1976-02-20 ', ' 95033 ')
INSERT into student values (' 109 ', ' Wang Fang ', ' female ', ' 1975-02-10 ', ' 95031 ')
INSERT into student values (' 103 ', ' contacts ', ' Male ', ' 1974-06-03 ', ' 95031 ')

Table (ii) Course
Cno Cname Tno
3-105 Introduction to Computers 825
3-245 Operating System 804
6-166 Digital Circuit 856
9-888 Advanced Mathematics 831
SELECT * FROM Course
Insert into course values (' 3-105 ', ' Introduction to Computer ', ' 825 ')
Insert into course values (' 3-245 ', ' OS ', ' 804 ')
Insert into course values (' 6-166 ', ' digital circuit ', ' 856 ')
Insert into course values (' 9-888 ', ' Advanced math ', ' 831 ')

Table (iii) score
Sno Cno Degree
103 3-245 86
105 3-245 75
109 3-245 68
103 3-105 92
105 3-105 88
109 3-105 76
101 3-105 64
107 3-105 91
108 3-105 78
101 6-166 85
107 6-166 79
108 6-166 81

INSERT into score values (' 103 ', ' 3-245 ', 86)
INSERT into score values (' 105 ', ' 3-245 ', 75)
INSERT into score values (' 109 ', ' 3-245 ', 68)
INSERT into score values (' 103 ', ' 3-105 ', 92)
INSERT into score values (' 105 ', ' 3-105 ', 88)
INSERT into score values (' 109 ', ' 3-105 ', 76)
INSERT into score values (' 101 ', ' 3-105 ', 64)
INSERT into score values (' 107 ', ' 3-105 ', 91)
INSERT into score values (' 108 ', ' 3-105 ', 78)
INSERT into score values (' 101 ', ' 6-166 ', 85)
INSERT into score values (' 107 ', ' 6-166 ', 79)
INSERT into score values (' 108 ', ' 6-166 ', 81)

Table (iv) Teacher
Tno tname tsex tbirthday Prof Depart
804 Li Chengnan 1958-12-02 Associate Professor Computer Department
856 Zhang Xunan 1969-03-12 Lecturer, Department of Electronic Engineering
825 Wang Ping 1972-05-05 Assistant computer Department
831 Liu Bing 1977-08-14 teaching assistant, Department of Electronic Engineering
select* from teacher
Insert into teacher values (' 804 ', ' sung ', ' Male ', ' 1958-12-02 ', ' associate Professor ', ' Computer Department ')
Insert into teacher values (' 856 ', ' Zhang Xu ', ' Male ', ' 1969-03-12 ', ' lecturer ', ' Electronic Engineering Department ')
Insert into teacher values (' 825 ', ' Wang Ping ', ' female ', ' 1972-05-05 ', ' ta ', ' computer Department ')
Insert into teacher values (' 831 ', ' Liu Bing ', ' female ', ' 1977-08-14 ', ' ta ', ' Electronic Engineering Department ')

Use Ceshi
Go

--1, queries the sname, Ssex, and class columns of all records in the student table.
Select Sname,ssex,class from Student
--2, query teachers all units that are not duplicated depart column.
Select distinct depart from teacher
--3, queries all records of the student table.
Select *from Student
--4, queries all records in the score table for scores from 60 to 80.
Select *from score where degree between and 80
--5, query the records in the score table for grades 85, 86, or 88.
Select *from score where degree in (85,86,88)
--6, inquires the student table "95031" class or the sex is "female" the classmate record.
Select *from student where class= ' 95031 ' or ssex= ' women '
--7, queries all records of the student table in descending order of class.
Select *from Student ORDER BY class Desc
--8, CNO Ascending, Degree descending queries all records of the score table.
Select *from score ORDER BY cno,degree Desc
--9, check the number of students in "95031" class. --aggregate function: Computes a series of arithmetic operations, such as summing or counting, for data columns
Select COUNT (*) from student where class= ' 95031 '
--sum (), AVG (), Max (), Min ()
Select MAX (degree) as maxfen,min (degree) Minfen from score where cno= ' 3-105 '
--10, check the student number and course number of the highest score in the score table. (sub-query or sort)
Select *from Score WHERE degree = (select MAX (degree) from score)
Select top 1 *from score order BY degree DESC
--11, check the average score for each course. --when grouping and aggregation are combined, group first, then aggregate each group separately
Select AVG (degree) as average score, CNO from score group by CNO
--12, query the average score of a course that has at least 5 students enrolled in the score table and starts with 3.
Select Cno,avg (degree) as degreess from score where CNO like ' 3% '
GROUP BY CNO have COUNT (*) >=5 ORDER by degreess
--13, the query score is greater than 70, and the SNO column is less than 90.
Select Sno from score where degree>70 and degree <90
--14, Sname, CNO, and degree columns for all students are queried.
Select Sname,cno,degree from Student,score where student. Sno=score.sno
--
Select Sname,cno,degree from Score
Inner JOIN student on score.sno=student. Sno
--15, queries all students for SNO, CNAME, and degree columns.
Select Sno,cname, degree from score joins course on SCORE.CNO=COURSE.CNO
--
Select Sno, (select CNAME from course where score.cno=course.cno) as course name, degree from score
--16, querying all students for sname, CNAME, and degree columns
Select Sname,cname,degree from Score
Join student on Student.sno=score.sno
Join Course on Course.cno=score.cno

--17, query "95033" class student's average score.
Select AVG (degree) from score where Sno in (select Sno from Student where class= ' 95033 '
)

--18, assume that a grade table is created with the following command:
CREATE table grade (Low Int,upp Int,rank char (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 ')
--Sno, CNO and rank columns are now available for all students.
Select Sno,cno,rank from score join grade on degree between low and UPP order by rank

Select Sno,cno, (select RANK from grade where score.degree between Low and UPP) as level from score

--19, query Elective "3-105" course performance is higher than the "109" student scores of all the students record.
Select *from score where cno= ' 3-105 ' and degree >
(select degree from score where cno= ' 3-105 ' and sno= ' 109 ')

--20, query score the number of students who choose to learn more than the highest score of the record.
--1. Query number appears two times and above the school number
Select Sno from score Group by SNO have COUNT (*) >=2
--2. Query the score of the person who studied the number in step 1
Select *from score where Sno in (select Sno from Score Group by Sno have COUNT (*) >=2)
--3. Find out the highest score for everyone
Select Max (degree) from score where Sno in (select Sno from Score Group by Sno have COUNT (*) >=2)
--4. Remove the highest score, Step 3, from the results of step 2
Select *from score where Sno in (
Select Sno from score Group by SNO have COUNT (*) >=2
)
and degree! = (
Select Max (degree) from score
)

--21, the query score is higher than the school number is "109", the course number is "3-105" all records of the results.
Select *from score where degree > (select degree from score where cno= ' 3-105 ' and sno= ' 109 ')

--22, inquiry, and student number 108 are the SNO, sname and Sbirthday of all students born in the same year.
Select Sno, Sname, sbirthday from student where year (sbirthday) =
(Select year (sbirthday) from Student where sno= ' 108 ')
SELECT * FROM Student

--Time-date function:
--year (Time Date column): Year taken
--month (): Take month value
--day (): Take Date value

--23, inquires "Zhang Xu" the student achievement which the teacher teaches.
Select *from score where Cno in
(select Cno from Course where Tno in
(select Tno from Teacher where Tname= ' Zhang Xu '))

--24, the name of the teacher who has more than 5 students who have enrolled in a course.
Select Tname from Teacher where Tno in
(select Tno from Course where Cno in
(select Cno from Score Group by Cno have COUNT (*) >5))

--25, check the records of 95033 classes and 95031 classes of all students.
Select *from student where class in (' 95033 ', ' 95031 ')
Select *from score where Sno in (select Sno from student where class in (' 95033 ', ' 95031 '))
SELECT * from Student,score where class in (' 95033 ', ' 95031 ') and Student.sno=score.sno
--26, the inquiry existence has 85 points above the course CNO.
Select distinct CNO from score where degree>=85

--27, the results of the "computer department" teacher's teaching schedule.
Select *from score where CNO in (
Select CNO from Course where TNO in (
Select TNO from teacher where depart= ' Computer Department '))

--28, inquires into the tname and prof of teachers with different titles of "computer department" and "Electronic Engineering department".
Select Tname,prof from teacher where Prof not in (
Select prof from teacher where depart = ' Electronic Engineering Department '
And Prof in (select Prof from teacher where depart = ' computer system ')
) and depart in (' Computer department ', ' Electronic Engineering Department ')

--Query the title name of the same title in two departments
Select prof from teacher where depart = ' Electronic Engineering ' and Prof in (select Prof from teacher where depart = ' computer system ')

--29, inquires the elective number is "3-105" the course and the result is at least higher than the elective number "3-245" Schoolmate's CNO, Sno and degree, and presses degree from high to low order.
Select *from score where cno= ' 3-105 ' and degree> (select MAX (degree) from score where cno= ' 3-245 ') Order BY degree DESC
Select *from score where cno= ' 1-101 ' and degree >any (select degree from score where cno= ' 1-102 ')

--30, inquires the elective number is "3-105" and the result is higher than the elective number "3-245" the classmate's CNO, Sno and degree.
Select *from score where cno= ' 3-105 ' and degree >all (select degree from score where cno= ' 3-245 ')

--31, check the name, sex and birthday of all teachers and classmates.
Select Sname,ssex,sbirthday from Student
Union
Select Tname,tsex,tbirthday from teacher
--Note: The number of columns to correspond, the type of column to correspond in order

--32 the name, sex and birthday of all "female" teachers and "female" classmates.
Select Sname,ssex,sbirthday from student where ssex= ' woman '
Union
Select Tname,tsex,tbirthday from teacher where tsex= ' woman '

--33, the results of students who have a lower average score than the course. --related sub-query
SELECT * FROM score a where a.degree<
(select AVG (degree) from score b where b.cno=a.cno)

Select AVG (degree) from score where cno= ' 2-104 '

--34, query all the teachers tname and depart.
Select Tname,depart from teacher where TNO on (select distinct TNO from course)

--35 the Tname and depart of all teachers who have not lectured.
Select Tname,depart from teacher where TNO not in (select distinct TNO from course)

--36, check the class number of at least 2 boys.
Select class from student where ssex= ' Man ' GROUP by class has COUNT (*) >=2

--37, query student table in the name of "Wang" classmate Records.
Select *from Student where sname not like ' King% '

--38, query the name and age of each student in the student table.
Select Sname,year (GETDATE ())-year (Sbirthday) as age from student

--39, queries the student table for the maximum and minimum sbirthday date values.
Select MAX (Sbirthday), MIN (sbirthday) from student

--40, check all records in the student table in order of class and age from large to small.
Select *from Student ORDER by class Desc,sbirthday ASC
--41, query "male" teachers and their courses.
Select *from Course join teacher on Course.tno=teacher.tno where tsex= ' man '

--42, Sno, CNO, and degree columns for the highest scores of students.
Select *from Score WHERE degree = (select MAX (degree) from score)

--43, inquiries and "Li June" with the sex of all the students of the sname.
Select sname from student where Ssex in (select Ssex from student where Sname= ' Li June ')

--44, inquiries and "Li June" with the same sex and classmates sname.
Select sname from student where ssex on (select Ssex from student
Where Sname= ' Li June ') and class in (select Class from Student
Where Sname= ' Li June ')
--45, query all of the "male" students who took the "Introduction to Computer" course.
Select *from score where Sno in (select Sno from student where ssex = ' man ') and
CNO in (select CNO from course where cname= ' Introduction to Computers ')

Select *from Student
Select *from Teacher
Select *from Score
Select *from Course
Update teacher set depart= ' computer system ' where tno= ' 888 '

6. SQL Completion Test Exercise