12 SQL statements for shared collation together with data _mssql

I think I try to write SQL, debugging debugging or helpful, read someone else's SQL example seems to read, their own writing may not be correct thinking, debugging to pass, write concise.

Follow the example of a popular online student timetable: http://www.jb51.net/article/30655.htm

This text is reproduced on the Internet rotten, some of the SQL is suitable for use in the application system, some "report" feeling heavier, mainly want to review the former. The first 20 is pretty good, after 30 obvious bias report style, and the following selected examples of mixed, selected 12 example to do exercises, (in fact, a lot of grammar, case, Any/all, union and so did not include), with MySQL database, and share their own created data. About these 12 SQL, fixed in the original text where there are flaws.

SQL is the basic skill, if can write well also quite wonderful, still continue to practice. Never advocate trying to write complex SQL to solve business problems. If complex SQL exists in the application system, it often reveals the problem that the business logic is leaking down to the SQL layer, which is not conducive to maintenance and expansion, although it does often improve the running efficiency. The specific circumstances of their own choice.
The following examples are more general SQL, in fact, for a specific database, need to learn a lot, such as the Oracle DB decode function, ROWID, rownum, connect by although not universal, but very practical.

Data can be downloaded here, just as an exercise, without any foreign key associations: Http://xiazai.jb51.net/database/20120626051553698.txt

The sorted SQL is below:
Student (s#,sname,sage,ssex) Student table
Course (c#,cname,t#) timetable
SC (s#,c#,score) score sheet
Teacher (t#,tname) Teacher's Table

1. Choose the school number for each subject to pass
Select distinct ' s# ' from SC where ' s# ' to (select ' s# ' from SC where score <60)
2. Check the number of all students with a "1" course score higher than the "2" course;
Select C01. ' s# ' from (select ' s# ', ' score ' from SC where ' C # ' =1) C01,
(SELECT ' s# ', ' score ' from SC where ' C # ' =2 ') C02
where C01. ' s# ' = C02. ' s# ' and C01.score > C02.score
3. The student number and average score of the students with average score greater than 60 points;
Select ' s# ', AVG (score) from SC Group BY ' s# ' has avg (score) > 60
4. Check all students ' study number, name, course number, total score;
Select student. ' s# ', student. ' Sname ', Count (' C # '), sum (score) from student left OUTER join SC on student. ' s# ' = sc. ' s# ' gr OUP by student. ' s# ', SC. ' s# '

5. Query did not learn the "cotyledons" teacher class student number, name;
Select student. ' s# ', student. ' Sname ' from student where student. ' s# ' is not in (select DISTINCT (sc. ' s# ') from teacher, course , SC where tname= ' cotyledons ' and teacher. ' t# ' = course. ' t# ' and SC. ' C # ' = course. ' C # '
6. Query learned "001" and also learned the number "002" course of the student number, name
Select student. ' s# ', student.sname from student, SC where student. ' s# ' = sc. ' s# ' and SC. ' C # ' = 1 and exists (SELECT * FROM SC sc_2 where sc_2. ' C # ' =2 and SC. ' s# ' =sc_2. ' s# ')
7. Query learned the "cotyledons" teacher taught all the students of the class number, name (ingenious)
Select ' s# ', sname from student where ' s# '
Select ' s# ' from SC, teacher, course where tname= ' cotyledons ' and teacher. ' t# ' =course. ' t# ' and course. ' C # ' = sc. ' C # ' GROUP BY ' s# ' Having count (SC. ' C # ') =
(select count (' C # ') from teacher, course where tname= ' cotyledons ' and teacher. ' t# ' =course. ' t# '))

8. The number and name (representative) of all students who inquired for the course number "002" were lower than the course number "001" course.
Select ' s# ', sname from (select Student. ' s# ', Student.sname, score, (select Score from SC sc_2 where student. ' s# ' =sc_2. ' s# ' and sc_2. ' C # ' =2 ' Score2 from student, SC where
sc. ' s# ' =student. ' s# ' and SC. ' C # ' =1 s_2 where Score2 < score
9. Inquires the student number and name of the students who have not learned all classes
Select student. ' s# ', sname from student, SC where student. ' s# ' = sc. ' s# ' GROUP by ' s# ', sname have count (' C # ') < (sel ECT count (' C # ') from course)

10. Inquiries at least one course and the student number "002" Students learn the same student number and name;
SELECT DISTINCT (sc. ' s# '), sname from student, SC where student. ' s# ' =sc. ' s# ' and ' C # ' in (SELECT ' C # ' from SC where ' s# ' =00 2)
11. The grade of "cotyledons" teacher in the "SC" table is changed to the average grade of the course;

Update SC INNER JOIN
(select SC2. ' C # ', AVG (Sc2.score) score from SC sc2, teacher, course where
SC2. ' C # ' =course. ' C # ' and Tname= ' cotyledons ' and teacher. ' t# ' = course. ' t# ' and course. ' C # ' =sc2. ' C # ' to course. ' C # ' C. ' C # ' =sc3. ' C # ' set Sc.score=sc3.score
12. Query number 2nd students to learn the course he has learned the student number; (Note: The first condition of the where statement filters out the records that do not satisfy C #, and group by, which is more clear)
Select ' s# ' from SC where ' C # ' in (SELECT ' C # ' from SC where ' s# ' =2)
GROUP BY ' s# ' has count (*) = (select count (*) from SC where ' s# ' =2);

The author is a man in the lake