2016030208-sql50 Problem Solving Exercises

　　Database build table scripts and used data please refer to: http://www.cnblogs.com/zhtzyh2012/p/5235826.html

SQL50 exercises See: http://blog.sina.com.cn/s/blog_6d1d0bf80100zm8l.html

　　--Creating a database, tables, and data for the teaching system
--Student (sno,sname,sage,ssex) Student table
--Course (CNO,CNAME,TNO) timetable
--SC (sno,cno,score) score table
--Teacher (tno,tname) Teacher table

1, the inquiry "001" course is higher than "002" of all students of the school number;
Step1: The table that queries all student's school numbers and grades is the SC table
Step2: Query The results of course 1 and course 2
Select Score, sno from SC where sc.cno = 1; Alias a
Select Score, sno from SC where sc.cno = 2; Alias b
Step3: Compare scores for courses 1 and 2 for the same person
A.score > B.score and A.sno = B.sno
　　Select A.sno from (select Score, sno from SC where sc.cno = 1) A,
(select Score, sno from SC where sc.cno = 2) b
where A.score > B.score and A.sno = B.sno;
　　

2. Search for students with average scores greater than 60 points, and average scores;

Step1: The table involved in the query is SC

Step2: Average achievement Avg () method, grouping after using aggregate function

　　Select Sno, AVG (score) from SC GROUP by SNO have AVG (score) > 60;

　　

　　

　　

　　

　　

　　

　　

2016030208-sql50 Problem Solving Exercises