SQL Basics Exercises

SELECT * from Student;
SELECT * from Courseselect;
select* from Teacherselect;
SELECT * from SC;
--Create test data
CREATE TABLE Student (s# varchar), Sname nvarchar (ten), Sage datetime,ssex nvarchar (10))
INSERT into Student values (' ', n ' Lei ', ' 1990-01-01 ', n ' Men ')
INSERT into Student values (' 1990-12-21 ', n ' money Electric '
INSERT into Student values (' ', n ' Sun Wind ', ' 1990-05-20 ', n ' Men ')
INSERT into Student values (' ', n ' Levin ', ' 1990-08-06 ', n ' Men ')
INSERT into Student values (' + ', n ' Zhou Mei ', ' 1991-12-01 ', n ' female ')
INSERT into Student values (' ' ', n ' Wu Lan ', ' 1992-03-01 ', n ' Women ')
INSERT into Student values (' ", N ' Qingshou ', ' 1989-07-01 ', n ' Women ')
INSERT into Student values (' A ', n ' Wang Ju ', ' 1990-01-20 ', n ' Women ')

CREATE Table Course (C # varchar), Cname nvarchar (ten), t# varchar (10))
INSERT into Course values (' ', N ' language ', ' 02 ')
INSERT into Course values (' Up ', N ' math ', ' 01 ')
INSERT into Course values (' ', N ' English ', ' 03 ')

CREATE TABLE Teacher (t# varchar), Tname nvarchar (10))
INSERT into Teacher values (' ', N ' Zhang San ')
INSERT into Teacher values (' N ' John Doe ')
INSERT into Teacher values (' ', N ' Harry ')

CREATE TABLE SC (s# varchar), C # varchar (ten), Score decimal (18,1))
INSERT into SC values (' 01 ', ' 01 ', 80)
INSERT into SC values (' 01 ', ' 02 ', 90)
INSERT into SC values (' 01 ', ' 03 ', 99)
INSERT into SC values (' 02 ', ' 01 ', 70)
INSERT into SC values (' 02 ', ' 02 ', 60)
INSERT into SC values (' 02 ', ' 03 ', 80)
INSERT into SC values (' 03 ', ' 01 ', 80)
INSERT into SC values (' 03 ', ' 02 ', 80)
INSERT into SC values (' 03 ', ' 03 ', 80)
INSERT into SC values (' 04 ', ' 01 ', 50)
INSERT into SC values (' 04 ', ' 02 ', 30)
INSERT into SC values (' 04 ', ' 03 ', 20)
INSERT into SC values (' 05 ', ' 01 ', 76)
INSERT into SC values (' 05 ', ' 02 ', 87)
INSERT into SC values (' 06 ', ' 01 ', 31)
INSERT into SC values (' 06 ', ' 03 ', 34)
INSERT into SC values (' 07 ', ' 02 ', 89)
INSERT into SC values (' 07 ', ' 03 ', 98)

Student (sid,sname,sage,ssex) Student table

Course (Cid,cname,tid) Timetable SC (sid,cid,score) score table teacher (tid,tname) Teacher Table Practice content:1. Check the number of all students who have a "1" course with a higher grade than "a 2" course;Select A.sid from (select Sid,score from SC where cid=1) A, (select Sid,score from SC where cid=3) b where A.score>b.sco Re and a.sid=b.sid; This problem knowledge point, nested query and the name of the table to be found alias2. Search for students with average scores greater than 60 points, and average scores;SELECT Sid,avg (Score) from the SC GROUP by SID has Avg (score) >60; This knowledge point, the group by statement is used to combine aggregate functions to group result sets based on one or more columns. Group by cannot be followed by where,having instead of where3. Check all students ' student number, name, number of courses selected, Total SELECT Student.sid,student.sname,count (sc.cid), sum (score) from Student left Outer JOIN SC in Student.sid=sc.cid GROUP by S tudent.sid,sname--wrote his own answer: Select A.s#,a.sname,c.classnum,c.scoresum from Student A, (select b.s#, COUNT (C #) as Classnum, SUM (B.score) as scoresum from SC B Group by s#) c

where a.s#=c.s#

4. Query the number of teachers whose surname is "Li";Select COUNT (teacher.tid) from teacher where teacher.tname like ' li% '5. Inquiry did not learn the "cotyledons" teacher class students of the school number, name;Select Student.sid,student.sname from Student where Sid is not in (SELECT distinct (SC.SID) from Sc,course,teacher where SC. Cid=course.cid and Teacher.id=course.tid and Teacher.tname= ' cotyledons '); The knowledge point of this problem, distinct is the function of the heavy6. The query learns "'" and has also learned the number and name of the students of the course;Select A.sid,a.sname from (select Student. Sname,student. SID from STUDENT,COURSE,SC where cname= ' C + + ' and Sc.sid=student.sid and Sc.cid=course.cid) A, (select student. Sname,student. SID from STUDENT,COURSE,SC where cname= ' 中文版 ' and Sc.sid=student.sid and sc.cid=course.cid) b where a.sid=b.sid; The standard answer (but it seems bad) SELECT student.s#,student.sname from Student,sc WHERE student.s#=sc. s# and SC. C#= ' 001 ' and exists (SELECT * from SC as sc_2 WHERE sc_2.s#=sc.  s# and sc_2.c#= ' 002 '); The knowledge point of this problem, exists is to find the data in the collection, as is the alias7. Inquire about the students ' School number and name of all the classes taught by the "cotyledons" teacher;Select A.sid,a.sname from (select Student.sid,student.sname from STUDENT,TEACHER,COURSE,SC where teacher. Tname= ' Yang Weiwei ' and Teacher.tid=course.tid and Course.cid=sc.cid and STUDENT.SID=SC.SID) A standard answer: SELECT sid,sname from student where SID in (SELECT SID from SC, Course, Teacher WHERE sc.cid=course.cid and Teacher.tid=course.tid and Teacher.tname= ' Yang Wei Wei ' GROUP by Sid has count (sc.cid) = (SELECT count (CID) from Course,teacher WHERE Teacher.tid=course.tid and Tname= ' Yang Weiwei ') )8. Find the number and name of all students who have a lower grade than the course number "" for the course number "";Select A.sid,a.sname from (select Student. Sid,student.sname,sc. Score from STUDENT,SC where Student.sid=sc.sid and Sc.cid=1) A, (select student. Sid,student.sname,sc.score from STUDENT,SC where Student.sid=sc.sid and sc.cid=2) b where A.score<b.score and a.sid=b. Sid Standard Answer: Select Sid,sname from (select Student.sid,student.sname,score, (select score from SC sc_2 WHERE sc_2.sid=student. Sid and Sc_2.cid=1) Score2 from Student,scwhere Student.sid=sc.sid and cid=1) s_2 WHERE score2 <score;9. Check the student's number and name of the students who have less than the score of the course;Select Sid,sname from Student where Sid isn't in (SELECT student.sid from STUDENT,SC where Student.sid=sc.sid and score>6 0); The knowledge points of this problem, first detect more than 60 points, and then not in is less than 60 points10. Inquire about the student's number and name of the students who have not studied all the classes;SELECT student.sid,student.sname from Student,sc WHERE student.sid=sc.sid GROUP by Student.sid,student.sname have cou NT (CID) < (SELECT count (CID) from Course);11. Inquiry at least one class with the student number is "" The students learn the same student number and name;12. Inquiry at least to learn the number of students to "" All of the classmates of the other students study number and name;SELECT student.sid,student. Sname from STUDENT,SC where Student.sid=sc.sid and CID in (select CID from SC WHERE sid=1) This problem knowledge point, SELECT sid,sname from Stud ENT,SC where Student.sid=sc.sid and Cid in (the SELECT cid from SC WHERE sid=1) is wrong because the from is followed by two tables, which is not clear which table is inside the SID and Sname so the error is raised "Column not explicitly defined" is shown13. Change the performance of the class "Cotyledons" in the "SC" table to the average grade of the course;Update SC Set score= (select AVG (score) from Sc,course,teacher where Course.cid=sc.cid and Course.tid=teacher.tid and teach Er.tname= ' Yang Weiwei ')14. Inquiries and the "" number of students to study the course of the same class of other students and the name of the school;Select Sid from SC where CID in (select CID from SC where sid=6) the GROUP by Sid has count (*) = (SELECT count (*) from SC WHE RE sid=6); The knowledge points of the problem, judged by the quantity15. Delete the SC table record of the "cotyledons" teacher class;Delete from SC s where s.cid in (select C.cid from teacher t,course c where T.tid = C.tid and tname= ' plums ') the knowledge points of this problem, nested queries can be distributed to consider, First find out what class the teacher has handed in the ID, and then delete the value of those IDs16. Insert some records into the SC table, which require the following conditions: not numbered "" The student number of the course, the average grade of the course;Insert into SC Select sid,2, (select AVG (score) from SC where cid=2) from Student where Sid is not in (select Sid from SC wher E cid=2); 17. Average grades from high to low show all students of the "database", "Enterprise Management", "English" the course scores, as shown in the following form: Student ID, database, business management, English, effective course number, effective average score;(Didn't do it)18. Find the highest and lowest scores for each section: shown in the following form: Course ID, highest score, lowest score;Select CID as course number, MAX (score) as highest score, Min (score) as lowest score from SC GROUP by CID Standard answer (but not well run) SELECT L.cid as course Id,l.score as highest score,  R.score as Min. from SC L, SC as R WHERE l.cid = r.cid and L.score = (SELECT MAX (Il.score) from SC as il,student as IM where l.cid = Il.cid and Im.sid=il.sid GROUP by il.cid) and R.score = (SELECT MIN (Ir.score) from SC as IR WHERE r.cid = Ir.cid GROUP by Ir.cid);19. From low to high and the percentage of passing rates from highest to lowest in each section26. Check the number of students enrolled in each courseSelect Sc.cid,count (sc.sid) from Sc,course where Sc.cid=course.cid GROUP by sc.cid27. Find out the number and name of all students who have only one course of studySelect Sc.sid,student.sname,count (CID) As course number from SC, Student WHERE sc.sid=student.sid GROUP by Sc.sid, Student.sname ha Ving Tsun count (CID) = 1;32. Check the average score for each course, the results are ranked in ascending order by average, and the average grade is the same, descending by course numberSELECT Cid,avg (Score) from SC GROUP by CID ORDER by AVG (score), CID DESC;37. Check for a failing course and arrange it from the largest to the smaller course numberSELECT Cid,sid from SC WHERE score <60 ORDER by CID38. Check the student's number and name for the course number and the course score above;Select Student.sid,student.sname from Sc,student where sc.cid=1 and sc.score>60 and Sc.sid=student.sid40. Check the names and achievements of the students who have the highest scores in the courses offered by the "cotyledons" teacherSelect Student.sname,sc.score from sc,student,teacher,course C where teacher.tname= ' plums ' and Teacher.tid=c.tid and c.cid= Sc.cid and Sc.sid=student.sid and sc.score= (select Max (score) from SC where sc.cid=c.cid)41. Check each course and the corresponding number of electivesSelect Sc.cid, COUNT (sc.sid) from Sc,student where Sc.sid=student.sid group by Sc.cid 43. Query the top two of the best results for each door44. Count the number of students enrolled in each course (more than a person's course is counted). Require the output of the course number and the number of elective, query results in descending order of numbers, query results in descending order of numbers, if the number is the same, in ascending order by course numberSelect Sc.cid,count (sc.cid) from Sc,course where Sc.cid=course.cid Group by sc.cid ORDER BY sc.cid Desc45. Retrieve the student number of at least two elective coursesSELECT SID from SC GROUP by SID has Count (*) > = 2the use of rownumCheck the results of the second to fourth place in all grades.SELECT * FROM (select RowNum p,t.score from (select S.score Score from SC s ORDER by score desc) t) TT where tt.p>1 and T T.p<547. Inquire about the names of students who have not learned any of the courses taught by the "cotyledons" teacherSELECT DISTINCT SID from SC where SID is not in (select Sc.sid from Sc,course,teacher where Sc.cid=course.cid and course.tid=t Eacher.tid and Teacher.tname= ' Yang Weiwei ')48. Check the number of students with two or more failed courses and their average scores49. Search for "" course score is less than, in descending order by fractions of the student numberSelect Sc.sid from Sc,course where sc.cid=course.cid and Course.cname= ' Java ' and sc.score<9050. Delete the results of the "classmate" courseDelete from SC where sid=1 and cid=1

SQL Basics Exercises