Simple SQL, complex query, and basic function Query

Use student database; drop database student database; create database student database on (name = student database _ data file, filename = 'e: \ SQL Sever 2000 \ student database _ data file. MDF ', size = 10, maxsize = 50, filegrowth = 5) log on (name = student database _ log file, filename = 'e: \ SQL Sever 2000 \ student database _ log file. ldf', size = 5 MB, maxsize = 25 MB, filegrowth = 5 MB) gocreate table student table (SNO char (4), Sn char (8), sex char (2) not null, age int not null, DEPT int not null, primary key (SNO), check (age between 14 and 45 ), Check (sex = 'male' or sex = 'female ') Create Table curriculum (CNO char (4), CN char (8) Not null, primary key (CNO )) create Table Student Course Selection table (SNO char (4), CNO char (4) not null, grade int null, primary key (SNO, CNO), foreign key (SNO) references student table (SNO), foreign key (CNO) References curriculum (CNO) insert into student table values ('s1', 'xu Lin', 'female ', 17,2 ); insert into student table values ('s2 ', 'Guo Hua', 'mal',); insert into student table values ('s3', 'xu ping', 'female ', 20, 1); insert Into student table values ('s4 ', 'lin xinming', 'male', 23, 6); insert into student table values ('s5 ', 'zhang Jiajie', 'male ', 19, 6); insert into student table values ('s6 ', 'zhang ting', 'female', 21, 3); insert into student table values ('s7 ', 'zhao da', 'male', 18, 6); insert into student table values ('ss', 'zhao Shulin ', 'male', 19, 3 ); insert into curriculum values ('c1 ', 'mat'); insert into curriculum values ('c2', 'English '); insert into curriculum values ('c3 ', 'C language '); insert into curriculum values ('c4', 'database'); insert into curriculum values ('C5 ', 'political'); insert into curriculum values ('c6 ', 'Physical'); insert into curriculum values ('c7', 'Psychology '); insert into student course selection table values ('s1', 'c1', 80); insert into student course selection table values ('s1', 'c2', 85 ); insert into student course selection table values ('s1', 'c6 ', 75); insert into student course selection table values ('s1', 'c4', 56 ); insert into student course selection table values ('s1', 'c5 ', 90); insert into student course selection table values ('s2', 'c1', 47 ); insert into student course selection table values ('s2', 'c3', 80); insert into student course selection table values ('s2 ',' C4 ', 75); insert into student course selection table values ('s2', 'c5 ', 70); insert into student course selection table values ('s6', 'c1 ', 95); insert into student course selection table values ('s6 ', 'c2', 80); insert into student course selection table values ('s6 ', 'c3', 87 ); insert into student course selection table values ('s3', 'c1', 75); insert into student course selection table values ('s3', 'c2', 70 ); insert into student course selection table values ('s3', 'c3', 85); insert into student course selection table values ('s3', 'c4', 86 ); insert into student course selection table values ('s3', 'c5 ', 90); insert into student course selection table values ('s3 ', 'C6', 99); insert into student course selection table values ('s4 ', 'c1', 83); insert into student course selection table values ('s4 ', 'c2 ', 85); insert into student course selection table values ('s4', 'c3', 83); insert into student course selection table values ('s5 ', 'c2 ', 99); select * from curriculum; -- * Indicates select * from curriculum for all columns in the table; select * from course selection table for students; select SnO from course selection table for students; select distinct SnO from Student Course Selection table; -- distinct removes duplicate rows in the result -- where Clause limits query conditions -- where clause is used to limit query conditions. conditional expressions are available, you can also use a key. /* There are two conditional expressions: 1. arithmetic expressions: >,<,=, <>,>=, <=,!> ,! <,! = 2. logical expression: not, And, or Keyword: between, in, not in */use student database; -- from the student, the score is greater than 85, if the number is less than 95, the student number and course number, and the Chinese character display attribute select SnO student number, the sno course number from the student selection table where grade> 85 and Grade <95; -- from the student, the names and ages of students S2, S4, and S6 with student IDs respectively are retrieved, and the names of select Sn students are displayed in Chinese characters. Age age from student table where SnO IN ('s2 ', 's4 ', 's6'); -- Multi-Table query -- 1. Write the names of multiple tables following the from statement, then use the WHERE clause to limit the join conditions between tables -- 2. Use the join and on keywords. Join is used to connect two tables, on is used to provide the conditions for connecting the two tables -- to retrieve the course number, course name, and score of the course learned by Li Guohua. The result title is displayed in the select course list in Chinese. CNO Course Number, CN Course name, grade score from Student Course Selection table, course schedule, student table where Student Course Selection table. CNO = course schedule. CNO and student table. sno = Student Course Selection table. snO and student table. sn = 'Li Guohua '; select course schedule. CNO course number, CN Course name, grade score from curriculum join student course selection table on curriculum. CNO = Student Course Selection table. CNO join student table on student table. sno = Student Course Selection table. snowhere student table. sn = 'Li Guohua '; ------------------------------------- self-Table query --------------------------------- search for names, ages, gender, and age use databases of all students older than Xu ping; select student table 1.sn: name of a student older than Xu ping; Student table 1.sex gender; Student table 1.age; Student table 2.ag E. Xu ping's age from student table 1, student Table 2 where student table 1.age> student table 2.age and student table 2.sn= 'xu ping'; -- The like --- like clause must be associated with the specified symbol, query condition --------- 1. Search for the department of the student surnamed Zhao and the selected course name select Dept, CN Course name from student table, course list, and where student table. sno = Student Course Selection table. snO and course schedule. CNO = Student Course Selection table. CNO and Sn like 'sheet % '; select dept; CN Course name from student table join student course selection table on student table. sno = Student Course Selection table. sno join course schedule on course schedule. CNO = Student Course Selection table. CNO where Sn like 'sheet % '; -- retrieve the names of students whose scores are higher than 90, the names and scores of the selected courses, and select s as the title. N Student name, CN Course name, grade score from student table join student course selection table on Student Course Selection table. sno = student table. snojoin curriculum on curriculum. CNO = Student Course Selection table. cnowhere grade like '9% '; -------------------- complex query --------------------------------- use multiple select... from... use the where statement to implement nested query: Find the Student name select Sn from the student table where dept in (select dept from student table where Sn = 'zhang ting') in the same department as Zhang Ting '); -- find out the names of students of the same age as Zhao Shulin. The results do not include Zhao Shulin himself. Select Sn: Student names of the same age as Zhao Shulin from student table where age in (select age from student table where Sn = 'zhao Shulin ') and Sn <> 'zhao Shulin '; /* Analysis: 1. Use the clause to find out If Zhao Shulin's age is 19 select age from the student table where Sn = 'zhao Shulin '; 2. Find the Student name "select Sn" with the age of 19 from the student table where age = 193, and use the "in" keyword to associate the above two parts to find out the students of the same age as Zhao Shulin, use the <> operation to queue up Zhao Shulin */-- to find the student numbers, student names, and Gender select student tables for both course Numbers C3 and C5. sno student number, Sn Student name, sex gender from student table, Student Course Selection table where student table. sno = Student Course Selection table. snO and CNO = 'c3' and student table. snO IN (select SnO from Student Course Selection table whe Re CNO = 'c5 '); ---- // ------ select student table. sno, Sn, sex from student table join student course table on student table. sno = Student Course Selection table. snowhere CNO = 'c3' and student table. snO IN (select SnO from student selection table where CNO = 'c5 ');/* Analysis: 1. Number of students whose course number is C5 2. Number of students whose course number is C3, student name and age 3. Set the first and second steps to "and" Operation */-- find the student number whose course number is C3 and whose name is political and the Student name is select Sno, SN from student table where SnO IN (select SnO from Student Course Selection table where CNO = 'c3' and snoin (select SnO from course list, where course list of student course selection. CNO = Student Course Selection table. CN O and course schedule. CN = 'political '); -------- // --------- select student table. sno, Sn from student table join student course table on student table. sno = Student Course Selection table. snowhere CNO = 'c3' and student table. snO IN (select SnO from student selection table where CNO in (select CNO from curriculum where Cn = 'political ');/* analysis: 1. First, find the course number "Politics" is C5 2. Find the student number "C5" found in the First Step 3. Find the student number and Student name "C3" Selected Course number 4. for step 2 and Step 3 'and' operation */---- Not in ------- find the Student name select Sn from student table where SnO not in (select SnO from student course Selection table where CNO = 'C 3 ');/* Analysis: 1. In the student selection table, find the student number whose course number is C3. 2. In the student table, find the Student name of the student whose student number is not C3. 3. Insert the first step to the second step. Note: Use in ('c1 ', 'c2 ') this is the relationship between 'and'. When not in ('c1 ', 'c2') is the relationship between 'and' */---- exists, not exists query -- query the student number and Student name of the elective course C4. use the exists and in keywords to query them respectively -- 1. Use existsselect SnO, SN from student table where exists (select * from student selection table where student selection table. sno = student table. snO and Student Course Selection table. CNO = 'c4');/* select SnO, Sn from student table where exists (select student selection table. sno, Sn from student selection table where student selection Course schedule. sno = student table. snO and Student Course Selection table. CNO = 'c4'); 1. In the nested query with the exists keyword, the data tables in the peripheral query are connected to the data tables in the nested clause, is to list two table join conditions in the nested clause 2, in the nested clause (select *.....) is followed by the Select keyword * instead of the column name. */Select SnO, Sn from student table where SnO IN (select SnO from Student Course Selection table where CNO = 'c1 '); -- find the Student name select Sn from student table where SnO not in (select SnO from Student Course table where CNO = 'c3') without selecting course number C3 '); --------------------------------------------------------------- select Sn from student table where not exists (select SnO from student selection table where student selection table. sno = student table. snO and Student Course Selection table. CNO = 'c3'); -- not exists can easily solve 'no... ',' nonexistent ', 'all' query difficulty -- retrieve the Student name and student ID selec of all courses selected T Sn Student name, SnO student number from student table where not exists (select * from Course table where not exists (select * from Student Course Selection table where student table. sno = Student Course Selection table. snO and course schedule. CNO = Student Course Selection table. (CNO);/* 1. Retrieve the courses in the course list. These courses do not exist in the student course list. 2. If the first step has no results, it indicates that such a student does not exist and there is no course, they did not select. That is: there are students who have chosen all courses. 3. If the first step has a result, it indicates that there are such students who have chosen courses. If they have not selected a course, that is, there are no students who have chosen all courses. */----------------------- Function query ---------------------------------------- 1. Retrieve the number of students in the school, number of departments, average age of students, maximum age of students, minimum age of students. select count (SNO) students, count (distinct Dept), AVG (AGE) average age, max (AGE), min (AGE) minimum age of the student from the student table; -- Name of the column following group, it should be the same as the column name after the keyword 'select' -- the column after 'group by' can be any number -- 2. Calculate the average age of the students by gender select sex gender, AVG (AGE) average age from student table group by sex; -- calculate the average score of students based on student ID, gender, and age. select student table. sno student ID, sex gender, Age age, AVG (grade) average student score from student table, Student Course Selection table where Student Course Selection table. sno = student table. sno group by student table. sno, Sex, Age; -- find out the names, gender, age, average score, and number of course subjects of at least four elective courses. Result title: The Chinese/* having clause must be combined with the group by clause. The having clause must be used to specify the having clause. The grouping query condition of the having clause must be the statistical function */select Sn name, sex gender, age, AVG (grade) average score, count (student selection table. CNO) Number of course selection courses from student selection table join student table on student selection table. sno = student table. sno join course schedule on Student Course Selection table. CNO = course schedule. cnogroup by Sn, sex, agehaving count (student selection table. CNO)> = 4; -- find out the total score of the students who have selected more than three courses (fail to count), and list the total score rankings. Result The title is selected SnO student number in Chinese. sum (grade) total score from student elective table where grade> = 60 group by snohaving count (*)> = 3 order by 2 DESC; -- find the student select table with the most data of the selected course. SN Student name from Student Course Selection table, student table where Student Course Selection table. sno = student table. snogroup by student table. snhaving count (*)> = All (select count (*) from student elective table group by SnO); -- if a student chooses more than four courses, then, add 5 points for each course. update the database update student selection table set grade = Grade + 5 where SnO IN (select SnO from student selection table group by SnO having count (*)> = 4 );