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

Xiaosheng blog: http://xsboke.blog.51cto.com

-Thank you for your reference. If you have any questions, you are welcome to communicate.

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 category, SUM (cost) as total cost from products group by category

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 intoproducts_new from products where category = 'fruit'

Select *

From products

Where factory date > = '2011-04-01' and type = 'fruit'

Select SUM (cost) as total cost, category

From products

Group by species

Select *

From products

Where type = 'fruit'

Order by cost desc.

Select *

From products

Where type = 'vegetable' and cost between'1' and'5'

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'

Select products. Name, products. Species, products. Cost, sales. Selling price, sales. Place of sale

From products inner join sales

On products. Name = sales. Name

Select products. Name, products. Species, products. Cost, sales. Selling price, sales. Place of sale

From products inner join sales

On products. Name = sales. Name

Where 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, score 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 between1 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

Select score

From [student-1]

Where name = 'Wang Ming'

Update [student-1]

Set score = score + 2

Where name = 'Wang Ming'

Select *

From [student-1]

Where remarks is not null

Select *

From [student-1]

Where score >'95'

Delete

From [student-1]

Where name = 'Wang Ming'

Insert into [student-1]

(name) values ('Yu Mei')

Select *

From [student-1]

Where score > = '95' and class =' 2'

Select *

From [student-1]

Where score > = '95' and class between' 1' and'2'

Select AVG (grade) as GPA

From [student-1]

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 date of birth > = '1950'and date of birth 35 and age = 50 or age =' 50' or age

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.