Shulou(Shulou.com)06/01 Report--

Summary of T-SQL query statement

Security code: where there is a will, there is a way, burn one's bridges and sink the boat, hundred and two Qin Guan will eventually belong to Chu.

Note: there are many tables and grammars, so they are summed up as sql server sentences. If you need them, you can refer to them. Thank you!

Case 1: the table is shown in the following figure

Lab requirements:

1. Query the products table for fruit information with a factory date later than April 2014.

Statement: select * from products

Where factory date > '2014-04-30'

And type = 'fruit'

2. Query the total cost of all fruits, vegetables and nuts in the products table.

Statement: select type, SUM (cost)

Total cost of as from products

Group by species

3. Query the information of all fruits in the products table and display the results in the order of cost from high to low.

Statement: select * from products

Where type = 'fruit'

Order by cost desc

4. Query vegetable information with a cost of 1-5 yuan in products table

Statement: select * from products

Where type = 'vegetable'

And cost between 1 and 5

5. Insert the name, type and factory date information of all the fruits in the products table into the new table products_new

Statement: select name, type, factory date

Into products_new

From products where type = 'fruit'

Case 2: the table is shown in the following figure

Lab requirements:

1. Query the name, category, cost, place of sale and price of the product in the products table and sales table.

Sentence: select products. Name, products. Species, products. Cost, sales. Place of sale, sales. selling price

From products,sales

Where products. Name = sales. Name

Or: select a. Name, a. Species, a. Cost, b. Place of sale, b. selling price

From products as an inner join sales as b on a. Name = b. Name

2. Query the product name, category, cost and selling price sold to Hainan in products table and sales table.

Sentence: select products. Name, products. Species, products. Cost, sales. selling price

From products,sales

Where products. Name = sales. Name and sales location = 'Hainan'

Case 3: the table is shown in the following figure

Lab requirements:

1. Inquire about Wang Ming's achievements? Result: Wang Ming 90

Select name, grade

From student where name = 'Wang Ming'

2. Add 2 points to Wang Ming?

Update student set score = score + 2

Where name = 'Wang Ming'

3. Query the students whose remarks are not empty?

Select * from student

Where remarks is not null

4. inquire about the students whose scores are more than 95 points?

Select * from student

Where score > 95

5. Wang Ming transferred to another school and deleted Wang Ming.

Delete from student

Where name = 'Wang Ming'

6. Here comes a new classmate whose name is Yu Mei?

Insert into student

Values (20'Yu Mei', '110110110', 'diligent 100')

7. who has a query score of more than 95 and whose class is Class 2?

Select * from student

Where score > 95 and class = 2

8. Who has a query score of more than 95 and whose class is Class 1 or Class 2?

Select * from student

Where score > 95

And class between 1 and 2

9. Inquire about the GPA?

Select avg (grade) as grade point average from student

10. Use truncate to delete all data

Truncate table student

Experimental case 4: the table is shown in the following figure

Lab requirements:

1. Display all the contents of the table

Select * from employee Information Table

2. Display all information about employees with a salary greater than 5000

Select * from employee Information Table

Where salary > = 5000

3. Display all information about all employees born between 1950 and 1988

Select * from employee Information Table

Where birth date > = '1950' and birth date

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.