Student Table Timetable Score table Teacher Table common SQL statements

Student Table Timetable score table Teacher Table 50 commonly used SQL statements to build a table

Student (s#,sname,sage,ssex) Student table
Course (c#,cname,t#) timetable
SC (s#,c#,score) score table
Teacher (t#,tname) Teacher table

----If Database exists The same name DataTable deletes it.  IF EXISTS (select  table_name from  Information_schema. TABLES where  table_name =  ' Student ' ) drop  table  Student;  ----Create TABLE  create  table  Student (s# varchar  (20 ), Sname varchar  (10 ), Sage int , Ssex varchar  (2 ))   

50 Questions about the table

2. Check the number of all students who have a "001" course that is higher than the "002" Course Score:

   SELECT a.S#FROM (SELECT s#, scoreFROM SCWHERE C# = ‘001‘) a, (  SELECT s#, score  FROM SC  WHERE C# = ‘002‘  ) bWHEREAND a.s# = b.s#;

4. Query students with average scores greater than 60 score and average score:

   select  s#, avg  (Score) from  scgroup  BY  S #having  avg  (Score) > 60 ;   

   Through the GROUP BY clause, you can make aggregate functions such as SUM, COUNT, MAX, and AVG work with data that belong to a group. Grades that belong to the same classmate will only return one row of values, except for the s# field, where the other fields are returned through the aggregate function.

   The

   Having clause allows us to filter groups of data, where clauses filter records before aggregation. That is, before the GROUP BY clause and the HAVING clause. The HAVING clause filters the group records after aggregation.

6. Query "Li" number of teachers:

    select  count  ( distinct  (Tname)) from  Teacherwhere  tname like   ' li% ' ;   

   The

   may contain duplicate values in the table, and the keyword distinct is used to return unique values. The DISTINCT statement can only display fields that are specified by distinct, and other fields are not possible.
   SELECT DISTINCT name, ID from A returns name+id different rows

8. Query The student's number, name, course number, total:

    select  stu. s#, Stu. Sname, count  (SCS. C #), sum  (scs.score) from  Student Stuleft  join  SC scsON  Stu. s# = SCS. S#group  by  stu. s#, Stu. Sname   

   • inner JOIN, satisfies the commutative law, "a INNER join B" and "B inner join a" are equal
   • left outer JOIN, producing Left table (Student) is fully set, the right table (SC) matches the value, there is no matching null value,
   • full outer JOIN, produces the whole of the left and right tables, no matching null, satisfies the commutative law.

10. Check the number and name of the students who did not take the "Li Ming" teacher Course:

    SELECT stu.S#, stu.SnameFROM Student stuWHERE stu.S# NOT IN (SELECT DISTINCT scs.S#FROM SC scs, Course crs, Teacher tchWHERE scr.C# = crs.C# AND tch.T# = crs.T# AND tch.Tname = ‘李明‘);

12. Find the highest score and the lowest score: Course ID, highest score, lowest score

Resources:

• Http://www.cnblogs.com/zengxiangzhan/archive/2009/09/23/1572276.html

Student Table Timetable score table Teacher Table common SQL statements