SQL query Exercise

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

Answer: SELECT * from Kaoshi where name is not in (select name from Kaoshi where fenshu<80)

2. The student table is as follows:
AutoNumber Number name Course number Course name score
1 2005001 Zhang 30,001 Math 69
2 2005002 li 40,001 Math 89
3 2005001 Zhang 30,001 Math 69
Delete the same student redundancy information except for the automatic numbering

Answer: Delete tablename where auto number not in (select min (autonumber) from TableName Group by number, name, course number, course name, score)

Note: Min () gets the smallest value after grouping

3. A table called team, there is only one field name, a total of 4 records, respectively, A,b,c,d, corresponding to four teams, now four teams to play, with an SQL statement to show all possible combinations of matches.

Answer: SELECT * from Kaoshi as a inner joins Kaoshi as B on A.name<b.name

Select A.name,b.name from Team as a inner joins team B on A.name < b.name

4.

How to put such a watch
Year Month amount
1991 1 1.1
1991 2 1.2
1991 3 1.3
1991 4 1.4
1992 1 2.1
1992 2 2.2
1992 3 2.3
1992 4 2.4
Chacheng such a result
Year M1 M2 M3 M4
1991 1.1 1.2) 1.3 1.4
1992 2.1 2.2) 2.3 2.4

Answer:

Select distinct A.A,A.C,B.C,C.C,D.C from Kaoshi as a inner joins Kaoshi as B on a.b<b.b
INNER JOIN Kaoshi as C on b.b<c.b
INNER JOIN Kaoshi as D on c.b<d.b

Answer two.

Select year,
(select amount from AAA m where Month=1 and M.year=aaa.year) as M1,
(select amount from AAA m where month=2 and M.year=aaa.year) as M2,
(select amount from AAA m where month=3 and m.year=aaa.year) as M3,
(select amount from AAA m where month=4 and M.year=aaa.year) as M4
From AAA GROUP by year

5.

Original table:
CourseID Coursename Score
-------------------------------------
1 Java 70
2 Oracle 90
3 XML 40
4 JSP 30
5 Servlet 80
-------------------------------------
For readability, the results after querying this table are as follows (pass score 60):
CourseID Coursename score Mark
---------------------------------------------------
1 Java Pass
2 Oracle Pass
3 XML fail
4 jsp @ fail
5 Servlet Pass
---------------------------------------------------
Write out this query statement

Answer:

Select A.courseid,a.coursename,a.score, (case-a.score>=60 Then ' pass ' else ' fail ' end) as-mark from Kaoshi as a

-----------------6

CREATE TABLE Testtable1
(
ID int IDENTITY,
Department varchar (12)
)

SELECT * FROM Testtable1
INSERT into testtable1 values (' design ')
INSERT into testtable1 values (' Market ')
INSERT into testtable1 values (' aftermarket ')
/*
Results
ID Department
1 design
2 Market
3 after-sales
*/
CREATE TABLE Testtable2
(
ID int IDENTITY,
Dptid int,
Name varchar (12)
)
INSERT into testtable2 values (1, ' Zhang San ')
INSERT into testtable2 values (1, ' John Doe ')
INSERT into Testtable2 values (2, ' Harry ')
INSERT into Testtable2 values (3, ' Peng VI ')
INSERT into Testtable2 values (4, ' Chen Qi ')
/*
With an SQL statement, how to display the following results
ID Dptid Department Name
1 1 design Zhang San
2 1 Design John Doe
3 2 Market Harry
4 3 after-sales Peng Liu
5 4 Black Chen Seven
*/

Answer:

Select A.id,a.dptid,isnull (b.department, ' black ') Department,a.name from Testtable2 as a left join Testtable1 as B
On A.dptid=b.id

Note IsNull () He's null for blacks, not for free, normal.

SQL query Exercise