SQL Statement Small Exercise

Source: Internet
Author: User

1. use a 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 isn't in (select distinct name from table where fenshu<=80) keyword distinct Used to return a unique different value

B:select name from table group by name have min (fenshu) >80 keyword having can with aggregate function (aggregate functions such as COUNT, SUM, AVG, MIN, or MAX) function with

C:select name from table group by name have Count (Kecheng) >=3 and Min (Fenshu) >=80

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

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

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

3, How to put such a table

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

A: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

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

A:insert to B (A, B, c) select D,e,f from A;

5, There is a table, there are 3 fields: Chinese, maths, English.

Greater than or equal to 80 means good, greater than or equal to 60 means passing, less than 60 points for failing.

Display format:

English for Chinese mathematics

Fail to pass a good grade

A:select

(Case when language >=80 then ' excellent '

When language >=60 then ' pass '

Else ' fail ') as language,

(Case when Math >=80 then ' excellent ')

When math >=60 then ' pass '

Else ' fail ') as mathematics,

(Case when English >=80 then ' excellent '

When English >=60 then ' pass '

Else ' fail ') as English,

From table

6, the creation of the table modified delete

Create a student form with the following information, school number, name, age, gender, home address, contact number

A:

Create table Stu (study number int,

Name varchar (8),

Age int,

Sex varchar (4),

Home address varchar (50),

Contact Phone int

);

Modify the structure of student tables, add a column of information, education

Alter table Stu Add education varchar (6);

Modify the structure of student tables, delete a column of information, home address

Alter table Stu Drop column home address

Add the following information to the student table:

School Number name Age gender contact phone education

1A22 Male 123456 Primary School

2B21 Male 119 Middle School

3C23 Male 110 High School

4D18 Female 114 University

Insert into Stu values (1, ' A ', 22, ' Male ', 123456, ' primary ')

Insert into Stu values (2, ' B ', 21, ' Male ', 119, ' secondary ')

Insert into Stu values (3, ' C ', 23, ' Male ', 110, ' High school ')

Insert into Stu values (4, ' D ', 18, ' female ', 114, ' university ')

Revise the data of the student table and change the number of students whose numbers begin with 11 to "junior college"

Update Stu Set education = ' College ' where contact phone like ' 11% '

Delete Student table data, name starts with C, gender is ' male ' record deleted

Delect from stu where sex = ' man ' and name like ' c% '

Check the data of the student table, will all age less than 22 years old, the education is "tertiary", the student's name and school number show

Select name, school number from Stu where age <22 and education = ' Junior College '

Query the Student table data, query all information, list the first 25% records

Select Top percent * from Stu

Check out the names of all students, gender, age descending order

Select name, gender from Stu order by age DESC

Search by gender group for all average ages

Select avg (age) from Stu Group by gender

7, query A (id,name) table in the 31st to 40th record, ID as the primary key may not be continuous growth of the column, the complete query statement as follows:

A:select Top * from a Where ID > (select MAX (ID) from (select top with ID from a, order by a) T) order by a

8, the query table A in the presence of duplicate ID more than three records, the complete query statement as follows:

SELECT * FROM (select COUNT (ID) as count from table Group by ID) T where t.count>3

9. say the meaning of the following aggregate numbers: AVG, SUM, max, Min, Count, COUNT (*)

AVG: Averaging

Sum: Sum

Max: Ask for maximum value

Min: Find minimum value

COUNT (*): Returns all rows

Keyword meaning

Transaction Transaction Trigger TRIGGER continue continue unique unqiue

Primary key primary key identity column identity foreign key foreign key check check

Constraint constraint

10, randomly take out 10 data

Select Top * FROM tablename ORDER by NEWID ()

11, the query average score is more than 60 points of the student's number and average score;

Select Stuid,avg (Score)

From Scores

GROUP BY STUID have AVG (score) >60;

12, https://www.toutiao.com/a6400624100788601090/

13.

SQL Statement Small Exercise

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.