SQL Basics Exercises

Name	Kecheng	Fenshu
Tom	Chinese	81
Tom	Mathematical	75
John doe	Chinese	76
John doe	Mathematical	90
Harry	Chinese	81
Harry	Mathematical	100
Harry	English	90

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

1 CREATE TABLE Chengji1 (2Name varchar ( -) notNULL,3Kecheng varchar ( -) notNULL,4FenshuintNotNULL5 )6Insert Chengji1 (Name,kecheng,fenshu) VALUES ('Zhang San','language',Bayi),('Zhang San','Mathematics', the),('John Doe','language', the),('John Doe','Mathematics', -),('Harry','language',Bayi),('Harry','Mathematics', -),('Harry','English', -)7 8 Select* fromChengji19The--distinct is used to return only different values--Ten SelectDistinct name fromChengji1whereName notinch(SelectDistinct name fromChengji1whereFenshu <= the ) One--the reason for increasing the HAVING clause is that the WHERE keyword cannot be used with the aggregate function-- A SelectName fromCHENGJI1 GROUP by name have min (Fenshu) > the ----- - SelectName fromCHENGJI1 GROUP BY name has COUNT (Kecheng) >=3and MIN (Fenshu) >= the 

View Code

2. Delete the same student redundancy information except auto-numbering

1 CREATE TABLE Stutable1 (2IdintPrimary Key IDENTITY (1,1) notNULL,3Stunumber varchar ( -) notNULL,4Name varchar ( -) notNULL,5Kcnumber varchar ( -) notNULL,6Kecheng varchar ( -) notNULL,7FenshuintNotNULL8 )9Insert Stutable1 (Stunumber,name,kcnumber,kecheng,fenshu) VALUES ('2005001','Zhang San','0001','Mathematics', the),('2005002','John Doe','0001','Mathematics', the),('2005001','Zhang San','0001','Mathematics', the)Ten Select* fromStutable1 OneDelete Stutable1whereID notinch(SelectMIN (ID) fromStutable1 GROUP BY Stunumber,name,kcnumber,kecheng,fenshu)

Answer

3, Interview questions: How to put such a table

Chacheng such a result

CREATE TABLE yma ( yearintNotNULL, MonthintNotNULL, Amountdecimal( -,1) notNULL) Insert Yma values (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)Select* fromYmadelete fromymaSelectYear , (SelectAmount fromYma mwheremonth =1and m.year= Yma.year) asM1, (SelectAmount fromYma mwheremonth =2and m.year= Yma.year) asm2, (SelectAmount fromYma mwheremonth =3and m.year= Yma.year) asM3, (SelectAmount fromYma mwheremonth =4and m.year= Yma.year) asM4 fromYma GROUP by year

Answer

4. Description: Copy table (copy data, source table name: A target table name: b)

int NULL  intnulldecimal (1 null) Insert Into YMA1 (year,month,amount)Select from ymaselectfrom YMA1

Answer

SQL Basics Exercises