>>>>>>>>>>
Practice Time: 2016.12.16
Editing time: 2016-12-20-->22:12:08
title :
related to: multi-table query, exists, COUNT (), group BY, order by
1.1Relationship Mode
Student Student；
SNO: study number;
SNAMEName
AgeAge
SEX: Gender
Course Course: CNO: Course code, CNAME: Course name, TEACHER: Teacher
Student Performance SC: SNO: School Number, CNO: Course code, score: Score
1.2Requires a SQL language to complete the creation of tables and the insertion of data.
1.3Require two in SQL language to complete the following requirements:
(1)To retrieve the names of at least the students who have taken all the courses offered by the "Cheng June" teacher (SNAME);
(2)Search "Li Qiang" students do not study the course number (CNO);
(3)Search for student number (SNO) with no less than 3 courses;
(4)Retrieves the name of the student taking all courses (SNAME).
(5)Retrieving student information that does not learn "C language"
1.4Request three please use SQL language to complete the following query: (1) query all the courses taught by the teacher of "Cheng June", (2) to inquire the results of all the courses of "Li Qiang", (3) to inquire the average result of the course named "C Language" and (4) to inquire about the students ' information of all the courses.
1.5Request four (1) to retrieve the course number and course name of teacher Wang's course. (2) Search for the number and name of the male student older than 23 years old. (3) to retrieve the name of the female student who has at least one course in the course taught by teacher Wang. (4) Retrieve the course number of the course which Li does not study. (5) Retrieve the student number of at least two elective courses. (6) To retrieve the course number and the course name of the course that all students are enrolled in. (7) The search elective course contains the student number taught by Mr. Wang. (8) The number of courses to be enrolled by the students is counted. (9) The average age of students seeking elective K1 courses. (10) To seek the average grade of students in each course of teacher Wang's course. (11) The number of students enrolled in each course (more than 2 participants are counted). Require the output of the course number and the number of elective, the results of the query in descending order of numbers, if the number is the same, in ascending order by course number. (12) The name of the student is larger than that of Li, who is younger than his. (13) The names and ages of all students whose names are preceded by Lee are searched. (14) The student number and course number are searched in SC for the null score. (15) The name and age of the male students who are older than the average age of the female classmates. (16) The name and age of the male students who are older than all female classmates. >>>>>>>>>>
Answer:
########
1.2 Requirements One ... Complete table creation and data insertion in SQL language。 #创建studentCREATE TABLE Student (sno int) COMMENT ' School Number ', Sname VARCHAR (COMMENT ' name ', Age INT (3) COMMENT ' ages ', sex VARCHAR (1) COMMENT ' sex ') #insert插入student数据INSERT into student VALUES (1, ' Li Qiang ', 18, ' male ') INSERT into Student (Sno,sname,age,sex) VALUES (2, ' Liu Li ', 188, ' female ') INSERT into student (Sno,sname,age,sex) VALUES (3, ' Phoenix ', 19, ' female ') INSERT into student (Sno,sname,age,sex) VALUES (4, ' Lara ', 20, ' female ') insert into student (Sno,sname,age,sex) VALUES (5, ' Zhang you ', 21, ' Male ') INSERT into student (Sno,sname,age,sex) VALUES (6, ' Monkey King ', 500, ' male ') #create创建courseCREATE TABLE Course (CNO varchar (Ten) COMMENT ' Course code ', CNAME varchar (COMMENT ' course name ', teacher varchar COMMENT ' teacher ') # Insert Inserts course data INSERT into course (cno,cname,teacher) VALUES (' K1 ', ' C ', ' Wang Hua ') insert into course (Cno,cname,teacher) VALUES (' K2 ', ' c+ language ', ' Xue ') insert into course (Cno,cname,teacher) VALUES (' K3 ', ' C + + language ', ' brother Xue ') insert into course (cno,cname , teacher) VALUES (' K4 ', ' Java language ', ' Xue ') INSERT into course (cno,cname,teacher) VALUES (' K5 ', ' Database principle ', ' Cheng June 'Insert into course (Cno,cname,teacher) VALUES (' K6 ', ' Linux language ', ' brother Xue ') insert into course (Cno,cname,teacher) VALUES (' K7 ', ' PHP language ', ' brother Xue ') INSERT into course (cno,cname,teacher) VALUES (' K8 ', ' compiler principle ', ' Cheng June ') #create创建表SCCREATE TABLE SC (Sno INT COMMENT ' study number ', CNO VARCHAR COMMENT ' Course code ', score INT (Ten) COMMENT ' score ') #insert插入sc数据INSERT into SC (SNO,CNO, Score) VALUES (1, ' K1 ', ()) insert into SC (Sno,cno,score) VALUES (2, ' K1 ', +) insert into SC (Sno,cno,score) VALUES (5, ' K1 ', 92 Insert INTO SC (Sno,cno,score) VALUES (2, ' K5 ', +) insert into SC (Sno,cno,score) VALUES (5, ' K5 ', +) insert into SC (SNO,CNO, Score) VALUES (5, ' K8 ', +)
########
1.3 Requirements two ..... Complete the following requirements in SQL language: (1) The name of the student who has enrolled at least one of the courses taught by the Cheng June Teacher (SNAME);#检索学生姓名SELECT sname from Student#检索选修 Course code for the Cheng June courseSELECT cno from course WHERE ' teacher ' = ' Cheng June '#检索选修课程的代码的学号Select Sno from SC where CNO in (SELECT cno from course where ' teacher ' = ' Cheng June ')#结合Select Sname from student where Sno in (select Sno from SC where CNO in (select CNO from course where ' teacher ' = ' Cheng June ')) # #以上 Result is
ErrorThe Query Method! The correct results are as follows (using EXISTS): SELECT * from student where not EXISTS (SELECT * from course where teacher = ' Cheng June ' and not EXISTS (select * FROM SC WHERE sno=student. ' Sno ' and Cno=course. ' CNO ')) (2) search "Li Qiang" students do not study the course number (CNO); SELECT cno from course WHERE not EXISTS (SELECT * from student where sname = ' Li Qiang ' and EXISTS (SELECT * from SC where SC. ' Sno ' =student. ' Sno ' and SC. ' CNO ' =course. ' C No ')) (2.2) Check the subject number and grade of all students
SELECT st.sno,st.sname,cr.cname,sc. ' Score ', Cr.teacher
From student St,sc,course CR
WHERE st.sno=sc. ' Sno ' and Cr.cno = sc. ' CNO '
(2.3) Check the name of the student who did not take the Cheng June Teacher's Course: SELECT * FROM Student St where not EXISTS (SELECT * From course cr WHERE teacher = ' Cheng June ' and EXISTS (S Elect * from SC where SC. ' CNO ' =cr.cno and SC. ' Sno ' =st.sno) (2.4) Find Li Qiang teacher student select * FROM student st,sc,course CR WHERE Sname= ' Li Qiang ' and SC. ' Sno ' =st ' Sno ' and SC. ' CNO ' =cr.cno (3) to retrieve the student number (CNO) of not less than 3 courses sno;#检索学号select Sno from SCSelect Sno from SC GROUP by SNO have COUNT (*) >= 3 (4) Retrieves the name of the student taking all courses (SNAME) #错误写法: SELECT SNAME from student WHERE Sno In (select Sno from SC GROUP by SNO have COUNT (*) >= 3) #正确写法: Select sname from student WHERE not EXISTS (SELECT * F ROM course where not EXISTS (SELECT * from SC WHERE sc.sno=student. ' Sno ' and Sc.cno=course. ' CNO ')) (5) Retrieves student information that does not learn "C" (for all Students have to learn C language, so change C to "compiler principle") #错误写法: SELECT * from student where Sno in (select Sno from SC where CNO in (select CNO from Course Where CNAME <> ' C language ') #正确写法: SELECT * from student where not EXISTS (SELECT * from course where cname = ' compiling principle ' and EX Ists (SELECT * from SC WHERE SC. ' Sno ' =student. ' Sno ' and SC. ' CNO ' =course. ' CNO ') #########
1.4 Requirements Three please use SQL language to complete the following query: (1) query all courses taught by "Cheng June" teacher; Select cname from course WHERE teacher = ' Cheng June ' (2) query "Li Qiang" students of all courses; Select Course.cname,sc.score from C OURSE,SC where EXISTS (SELECT * from student where sname = ' Li Qiang ' and EXISTS (SELECT * from SC where SC. ' Sno ' =student. ' Sno ' A ND SC. ' cno ' =course ' CNO ') GROUP by Course.cname (3) To inquire about the average grade of the course entitled "C Language";#C语言课程标号: Select CNO from course where cname = ' C language 'Select AVG (Score) from SC WHERE cno = (SELECT cno from course where cname = ' C language ') (4) Query the student information for all courses. SELECT * FROM student where not EXISTS (SELECT * from course where not EXISTS (SELECT * from SC where sc.sno=student. ' Sno ') and Sc.cno=course. ' CNO '))
########
1.5 Requirements Four(1) To retrieve the course number and course name of the course taught by Mr. Wang Hua. select cno,cname from course WHERE teacher = ' Wang Hua ' (2) retrieves the number and name of a male student older than 23 years old. select Sno,sname from Student WHERE age >= and sex = ' man ' (3) Retrieve the name of the female student at least one of the courses taught by teacher Wang. select sname from student where EXISTS (SELECT * from course where teacher = ' Wang Hua ' and EXISTS (SELECT * FROM SC where Sno=student. ' Sno ' and Cno=course. ' CNO ') and sex = ' Women ' (4) Retrieve the course number of the course that Li Qiang students do not study. Select CNO from course where isn't EXISTS (SELECT * from student where sname = ' Li Qiang ' and EXISTS (SELECT * from SC where sno=st Udent. ' Sno ' and Cno=course. ' CNO ') (5) Retrieves the student number of at least two elective courses. SELECT Sno from SC GROUP by SNO have COUNT (*) >= 2 (6) Retrieves the course number and course name of the course that all students take. Select Cno,cname from course where is not EXISTS (SELECT * from student where not EXISTS (SELECT * from SC where SC. ' Sno ' =stude NT. ' Sno ' and SC. ' CNO ' =course. ' CNO ') (7) The search elective course contains the student number taught by Mr. Wang Hua. Select Sno from student where EXISTS (SELECT * from course where teacher = ' Wang Hua ' and EXISTS (SELECT * from SC where SC. ' Sno ' = Student. ' Sno 'and SC. ' CNO ' =course ' CNO ') (8) count the number of courses that all students take. SELECT sname,sc. ' Sno ', COUNT (CNO) from Student,sc where sc. ' Sno ' =student. ' Sno ' GROUP by sno (9) The average age of students seeking elective K1 courses. Select AVG (age) from the student where EXISTS (SELECT * from SC where cno= ' K1 ' and SC. ' Sno ' =student. ' Sno ') (10) Ask for the course of the teacher Wang Hua Average student scores for each course. select Sno,avg (Score) from SC where EXISTS (SELECT * from course WHERE teacher= ' Cheng June ' and SC. ' CNO ' =course. ' CNO ') GROUP by CNO (11) counts the number of students enrolled in each course (more than 2 courses are counted). Require the output of the course number and the number of elective, the results of the query in descending order of numbers, if the number is the same, in ascending order by course number. SELECT COUNT (Sno), CNO from SC GROUP by CNO have count (Sno) >=2 ORDER by Count (Sno) asc (12) The search number is larger than Li Qiang's classmate, and the student's name is younger than his. 。 select sname from student where sno> (select Sno from student where Sname= ' Li Qiang ') and age> (select age from Stude NT WHERE sname= ' Li Qiang ') (13) retrieves the names and ages of all students whose names begin with Lee. select sname,age from student where sname like '% Li ' (14) retrieves the student number and course number in SC with a null score. select sno,cno from Student,course where EXISTS (SELECT * from SC where score is NULLand SC. ' Sno ' =student ' Sno ' and SC. ' CNO ' =course. ' CNO ' (15) The name and age of the male student who is older than the average age of the female classmate. Select Sname,age from student where sex = ' male ' and age > (SELECT AVG (age) from student where sex = ' woman ') (16) Age The name and age of the male student who is older than all female classmates. Select Sname,age from student where age> (select Max (age) from student where sex= ' female ') and sex= ' Male ' >>>>>> ;>>>>>>>>>>>>>> 1 students enrolled in the course # Way one: SELECT * from course WHERE CNO = ( Select CNO from SC where sno=1) #方式二: SELECT * FROM Course where exists (SELECT * from student where Sno = 1 and EXIST S (SELECT * from SC WHERE SC. ' Sno ' =student.sno and SC. ' CNO ' =course. ' cno ') Inquiry No. 1 students take the course of "Cheng June" teacher select * from cours e where teacher = ' Cheng June ' and EXISTS (SELECT * from student where Sno = 1 and EXISTS (SELECT * from SC where SC. ' Sno ' =student . Sno and SC. ' CNO ' =course. ' CNO ') Enquiry number 1 Whether the student has enrolled in the "Cheng June" Teacher's course, if not elective, please output the course information that the classmate does not have elective select * FROM Course WHERE Teacher = ' Cheng June ' and not EXISTS (SELECT * from student WHERE sno = 1 and EXISTS (SELECT * from SC WHERE SC. ' Sno ' =student.sno and SC. ' CNO ' =course. ' CNO ') View student name information for the Cheng June Teacher Course: SEL ECT * FROM student where EXISTS (SELECT * from course where teacher = ' Cheng June ' and exists (SELECT * from SC where SC. ' SN O ' =student.sno and SC. ' CNO ' =course. ' CNO ') View student information for students who do not have all elective Cheng June teacher courses SELECT * FROM student WHERE EXISTS (SELECT * FROM Course wher E teacher = ' Cheng June ' and not EXISTS (SELECT * from SC WHERE SC. ' Sno ' =student.sno and SC. ' CNO ' =course. ' CNO ')) view the name of the student who did not take the Cheng June Teacher's course. Interest: SELECT * from student where not EXISTS (SELECT * from course where teacher = ' Cheng June ' and EXISTS (SELECT * from SC where SC. ' Sno ' =student.sno and SC. ' CNO ' =course. ' CNO ') view the name of the student who took all of the Cheng June Teacher's courses: SELECT * FROM student WHERE not EXISTS (SELECT * FROM CO Urse WHERE teacher= ' Cheng June ' and not EXISTS (SELECT CNO from SC WHERE SC. ' Sno ' =student. ' Sno ' and course. ' CNO ' =sc. ' CNO ') &n BSP; (Note: Personal writing, does not guarantee that all correct, best-written, if there is an opinion, please point out) >>>>>>>>>>
MySQL exercises -2016.12.16
