Student Table Timetable Score table Teacher Table common SQL statements

Source: Internet
Author: User

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
  1. 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#;
  2. 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.

  3. 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

  4. 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.
  5. 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 = ‘李明‘);
  6. 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.