SQL Statement Interview Summary

1. Use an SQL statement to find out the names of students who have more than 80 points per course

Name Kecheng Fenshu
Zhang San language 81
Zhang San Mathematics 75
John Doe Language 76
John Doe Mathematics 90
Harry Language 81
Harry Mathematics 100
Harry English 90

A:select distinct name from table where name is not in (select DISTINCT name from table where fenshu<=80)


2. A SQL statement interview question about GROUP by
Table content:
2005-05-09 wins
2005-05-09 wins
2005-05-09 Negative
2005-05-09 Negative
2005-05-10 wins
2005-05-10 Negative
2005-05-10 Negative

How do you write SQL statements if you want to generate the following results?

Outcome
2005-05-09 2 2
2005-05-10 1 2
------------------------------------------
To connect using a function:
SELECT Rq,sum (case when shengfu= ' wins ' then 1 else 0 end) wins, SUM (case when shengfu= ' negative ' then 1 ELSE 0 end) negative from TMP GROUP by Rq
External connection:
Select N.rq,n. Win, M. Negative from (select Rq,count (*) wins from TMP WHERE shengfu= ' wins ' GROUP by RQ) n INNER JOIN (select Rq,count (*) negative From TMP WHERE shengfu= ' negative ' GROUP by RQ] m on N.RQ=M.RQ;
Self-connect:
SELECT A.rq, a.a1 wins, B.b1 negative from
(SELECT rq,count (RQ) A1 from TMP WHERE shengfu= ' wins ' GROUP by RQ) A,
(SELECT Rq,count (RQ) B1 from TMP WHERE shengfu= ' negative ' GROUP by RQ) b
WHERE A.rq=b.rq;


The ID in a table has multiple records, the records of all the IDs are detected, and the total number of records is displayed.
------------------------------------------
The Select ID, COUNT (*) from the TB group by ID has the count (*) >1
SELECT * FROM (select COUNT (ID) as count from table Group by ID) T where t.count>1





SQL data: The big difference that we learn in class is that the location of the subquery is very flexible and can appear after the FROM clause (you need to alias this query).
Can also appear in the target column of SELECT, please read the following examples (some places alias directly after the space, and some places are followed by the alias of the way).
Note: Mainly to learn the use of basic sentences or phrases

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

Problem:
1, the inquiry "001" course is higher than "002" of all students of the school number;
Select a.s#
From (select S#,score from SC where c#= ' 001′) A,
(select S#,score from SC where c#= ' 002′) b
where A.score>b.score and a.s#=b.s#;

2, the query average score is more than 60 points of the student's number and average score;
Select S#,avg (Score)
From SC
GROUP BY s# have AVG (score) >60;

3, inquiry all students of the school number, name, number of courses selected, total;
Select Student.s#,student.sname,count (SC. C #), SUM (score)
From Student to Outer join SC on STUDENT.S#=SC. s#
GROUP BY Student.s#,sname

4, inquire the surname "Li" the number of teachers;
Select COUNT (Distinct (tname))
From Teacher
Where tname like ' li% ';

5, the inquiry did not learn "cotyledons" teacher class students of the school number, name;
Select Student.s#,student.sname
From Student
where s# not in (select DISTINCT (SC. s#) from Sc,course,teacher where SC. C#=course.c# and teacher.t#=course.t# and Teacher.tname= ' cotyledons ');

6, the inquiry learned "001" and also learned the number "002" course of the students ' study number, name;
Select Student.s#,student.sname
From STUDENT,SC
where STUDENT.S#=SC. s# and SC. C#= ' 001′and exists (Select * from SC as sc_2 where SC_2.S#=SC. s# and sc_2.c#= ' 002′);
7, the inquiry has learned "Cotyledons" the teacher teaches all classes The student's school number, the name;
Select S#,sname
From Student
where s# in
(select s# from SC, Course, Teacher
where SC. C#=course.c# and teacher.t#=course.t# and Teacher.tname= ' cotyledons ' GROUP by s# have count (SC. C #) = (select count (C #)
From Course,teacher where teacher.t#=course.t# and Tname= ' cotyledons '));

8, check all the course results are less than 60 points of the student's school number, name;
Select S#,sname
From Student
where s# not in (the Select student.s# from STUDENT,SC where S.S#=SC. s# and score>60);

9, the inquiry did not learn all the class student's school number, the name;
Select Student.s#,student.sname
From STUDENT,SC
where STUDENT.S#=SC. s#
GROUP BY Student.s#,student.sname have Count (C #) < (select count (C #) from Course);

10, the inquiry has at least one course and the school number for "1001" students learn the same student number and name;
Select S#,sname
From STUDENT,SC
where STUDENT.S#=SC. s# and C # in (select C # from SC where s#= ' 1001 ');

11, delete learning "cotyledons" the SC table record of the teacher class;
Delect SC
From course, Teacher
where COURSE.C#=SC. C # and course.t#= teacher.t# and tname= ' cotyledons ';

12. Check the highest and lowest score of each section: show the following form: Course ID, highest score, lowest score
SELECT l.c# course id,l.score highest score, R.score lowest score
From SC L, SC R
WHERE l.c# = r.c#
and
L.score = (SELECT MAX (Il.score)
From SC il,student IM
WHERE IL. C # = l.c# and IM. S#=il. s#
GROUP by IL. C #)
and
R.score = (SELECT MIN (Ir.score)
From SC IR
WHERE IR. C # = r.c#
GROUP by IR. C #);

13, check the average grade of students and their rankings
Select 1+ (select COUNT (distinct average score)
From (SELECT S#,avg (score) Average score
From SC
GROUP by s#) T1
WHERE Average score > T2. Average score) rank, s# student number, average score
From (SELECT S#,avg (score) average score from SC GROUP by s#) T2
ORDER by average grade desc;

14. Check the records of the top three grades of each section: (Consider the results and the situation)
SELECT t1. s# as student id,t1. C # as course Id,score as fraction
From SC T1
WHERE score in (SELECT TOP 3 score
From
(select DISTINCT score from SC order) by score Desc)
ORDER by T1. C #;

(2), check the records of the top three grades of each section: (Bu considering the results and the situation)
SELECT t1. s# as student id,t1. C # as course Id,score as fraction
From SC T1
WHERE score in (SELECT TOP 3 score
From SC
WHERE t1. c#= C #
ORDER by score DESC)
ORDER by T1. C #;

15. Check the top two of the best results for each door
SELECT t1. s# as student id,t1. C # as course Id,score as fraction
From SC T1
WHERE score in (SELECT TOP 2 score
From SC
WHERE t1. c#= C #
ORDER by score DESC)
ORDER by T1. C #;

Add:
Already know the original table
Year Salary
——————
2000 1000
2001 2000
2002 3000
2003 4000

Solution:
Select B.year,sum (a.salary)
From salary A,salary b
where A.year<=b.year
GROUP BY B.year
Order BY B.year;

In the interview process repeatedly encountered a problem of SQL query, query a (id,name) table 31st to 40th record, ID as the primary key may not be continuous growth of the column, the complete query statement as follows:
Method One:
Select Top 10 *
From A
where ID > (select MAX (ID) from (select top with ID from A ORDER by ID) T) the ORDER by ID
Method Two:
Select Top 10 *
From A
where ID not in (select top with ID from A order by ID)
ORDER BY ID