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
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#;
-
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.
-
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
-
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.
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 = ‘李明‘);
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