MySQL Beginner advanced 03

When the faithful for the national pay, ever afraid of broken?
Now the world is red, who keeps the Jiangshan?
The industry is not, the body is tired, the temples have been autumn.
You and me, endure will be the wish, to the East flow?

The database structure is as follows:
Warehouse (warehouse number, city, area)
Order Form (employee number, supplier number, order ticket, Order date)
Supplier (vendor number, supplier name, address)
Staff (warehouse number, employee number, salary)

The specific data are as follows:

Warehouse table:
Warehouse Number City Area
WH1 Beijing 370
WH2 Shanghai 500
WH3 Guangzhou 200
WH4 Wuhan 400

Order Form:
Employee number supply firm Order No. Order Date
E3 S7 OR67 06/23/01
E1 S4 OR73 07/28/01
E5 S4 OR76 05/25/01
E6 S6 OR77 05/26/01
E3 S4 OR79 06/13/01
E1 S2 OR80 08/29/01
E3 S3 OR90 09/01/01
E3 S3 OR91 07/13/01

Supplier Table:
Vendor number supplier Name Address
S2 Electronics Co., Ltd. Suzhou
S3 Electronics Factory Xian
S4 Hua Tong Electronics Co., Ltd. Beijing
S6 607 Factory Zhengzhou
S7 AI Electronics Factory Beijing

Staff table:
Warehouse number, employee number, payroll.
WH2 E1 1220
WH1 E3 1210
WH2 E4 1250
WH3 E5 1230
WH1 E6 1250

Use the SQL statement to complete:
Ddl
1. Write the statement that created the table above
Command:
CREATE TABLE warehouse (warehouse number varchar) PRIMARY key,
City varchar (10),
area int);
CREATE TABLE supplier (vendor number varchar) Primary key,
Supplier name varchar (30),
Address varchar (10));
CREATE TABLE staff (warehouse number varchar (10),
Employee number varchar (primary) key,
wage int);
CREATE table order form (Employee number varchar (references) staff (employee number),
Supplier number varchar (references) suppliers (supplier number),
Order number varchar (primary key),
Order Date varchar (10));
Dml
2. Give the INSERT statement that inserts the above data
Command:
INSERT into warehouse values (' WH1 ', ' Beijing ', 370);
INSERT into warehouse values (' WH2 ', ' Shanghai ', 500);
INSERT into warehouse values (' WH3 ', ' Guangzhou ', 200);
INSERT into warehouse values (' WH4 ', ' Wuhan ', 400);

INSERT into supplier values (' S2 ', ' Famous electronics company ', ' Suzhou ');
INSERT into supplier values (' S3 ', ' Zhenhua electronics factory ', ' Xian ');
INSERT into supplier values (' S4 ', ' Hua-powered subsidiaries ', ' Beijing ');
INSERT into supplier values (' S6 ', ' 607 factory ', ' Zhengzhou ');
INSERT into supplier values (' S7 ', ' ai Hua Electronics Factory ', ' Beijing ');

INSERT into staff values (' WH2 ', ' E1 ', 1220);
INSERT into staff values (' WH1 ', ' E3 ', 1210);
INSERT into staff values (' WH2 ', ' E4 ', 1250);
INSERT into staff values (' WH3 ', ' E5 ', 1230);
INSERT into staff values (' WH1 ', ' E6 ', 1250);

INSERT INTO order list values (' E3 ', ' S7 ', ' OR67 ', ' 06/23/01 ');
INSERT INTO order list values (' E1 ', ' S4 ', ' OR73 ', ' 07/28/01 ');
INSERT INTO order list values (' E5 ', ' S4 ', ' OR76 ', ' 05/25/01 ');
INSERT INTO order list values (' E6 ', ' S6 ', ' OR77 ', ' 05/26/01 ');
INSERT INTO order list values (' E3 ', ' S4 ', ' OR79 ', ' 06/13/01 ');
INSERT INTO order list values (' E1 ', ' S2 ', ' OR80 ', ' 08/29/01 ');
INSERT INTO order list values (' E3 ', ' S3 ', ' OR90 ', ' 09/01/01 ');
INSERT INTO order list values (' E3 ', ' S3 ', ' OR91 ', ' 07/13/01 ');
Single-Table Query
3. Retrieving all information in employee relations
Command:
SELECT * FROM Employees;
Results:

4. Retrieving all information in a supplier relationship
Command:
SELECT * from supplier;
Results:

5. Retrieving all orders after June
Command:
SELECT * from order form where order date like ' 06% ' or order date like ' 07% ' or order date like ' 08% ' or order date like ' 09% ' or order date like ' 10% ' or order date like ' 11% ' or order date like ' 12% ';

Results:

6. Retrieving warehouses with an area greater than 400
Command:
SELECT * from warehouse where area > 400;
Results:

7. Search for employees who pay more than 1210
Command:
SELECT * from staff where wages > 1210;
Results:

8. The search warehouse is "WH1" or "WH2" and the city with an area greater than 400
Command:
Select City from warehouse where (warehouse number = ' WH1 ' or warehouse number = ' WH2 ') and area > 400;
Results:

9. Find warehouses with warehouses in the area of 400 to 600
Command:
SELECT * from warehouse where area between and 600;
Results:

10. Find out the names of all suppliers with the name "Factory"
Command:
Select Supplier name from supplier where supplier name like '% plant% ';
Results:

11. Find out which suppliers are not in Xian
Command:
SELECT * FROM supplier where address! = ' Xian ';
Results:

12. Find the warehouse that is not in Beijing
Command:
SELECT * FROM warehouse where city! = ' Beijing ';
Results:

13. Sort out all employees ' information in descending order of wages
Command:
SELECT * FROM Employee ORDER by salary Desc;
Results:

14. Sort by warehouse number in ascending order and then descending by salary
Command:
SELECT * FROM Employee ORDER by warehouse number ASC, payroll desc;
Results:

15. Check the warehouse table for a few warehouses.
Command:
Select COUNT (*) warehouse number from warehouse;
Results:

16. Count the number of warehouses in the staff table
Command:
Select COUNT (distinct warehouse number) warehouse number from staff;
Results:

17. Find the Total warehouse area
Command:
Select SUM (area) total area from warehouse;
Results:

Aggregate queries
18. Number of orders per employee
Command:
Select employee number, COUNT (order number) orders from order group by employee number;
Results:

19. Employees with orders of more than 3
Command:
Select employee number, COUNT (order ticket) Order number from order group by employee number (order tracking) > 3;
Results:

Multi-Table Query
20. Find out who is working in a warehouse with an area greater than 400
Command: Select employee number from employee, warehouse where worker. Warehouse number = Warehouse. Warehouse number and area > 400;

Results:

21. Find out who is working in Beijing and their wages
Command: Select employee number, salary from employee, warehouse where worker. Warehouse number = Warehouse. Warehouse number and city = ' Beijing ';

Results:

22. Find employees with wages greater than 1215 and the cities in which they are located
Command: Select employee number, city from employee, warehouse where worker. Warehouse number = Warehouse. Warehouse number and payroll > 1215;

Results:

Sub-query
23. Which cities have at least one order (from a warehouse perspective)
Command: Select city from Warehouse, order form, employee where order form. Employee Number = employee. Employee number and warehouse. Warehouse number = employee. Warehouse Number GROUP by City having Count (*) >= 1;

Results:

24. Find the city without any orders
Command: Select city from warehouse where warehouse number not in (select warehouse number from employee where employee number in (select employee from Purchase order));

Results:

25. Identify and E4 all employees with the same wage
Command:
SELECT * FROM Employee WHERE salary = (select salary from employee where employee number = ' E4 ');
Results:

26. Identify all employees in warehouses with a warehouse area greater than 400
Command: SELECT * from staff where warehouse number in (select warehouse number from warehouse where area > 400);

Results:

27. Find out the suppliers ' workers in Xian and their wages
Command:

Results:

28. Find employees who are not working in the Beijing Warehouse
Command: Select employee number, salary from employee where warehouse number not in (select warehouse number from warehouse where city = ' Beijing ');

Results:

29. Find out the employees who work in the Beijing warehouse
Command: Select employee number, salary from employee where warehouse number in (select warehouse number from warehouse where city = ' Beijing ');

Results:

30. The total wages of employees in Guangzhou and Shanghai Warehouse
Command: Select SUM (Payroll) Total wage from employee where warehouse number in (select warehouse number from warehouse where city in (' Guangzhou ', ' Shanghai '));

Results:

31. Ask for the average area of the warehouse where all employees pay more than 1210
Command: Select AVG (area) from warehouse where warehouse number in (select warehouse number from employee GROUP by warehouse number having min (wage) > 1210);

Results:

32. The highest wage for employees in Shanghai Warehouse
Command: Select MAX (Salary) maximum wage from worker where warehouse number in (select warehouse number from warehouse where city = ' Shanghai ');

Results:

33. Wages of employees with orders greater than or equal to 2
Command: Select salary from employee where employee number in (select employee number from order group by employee number has count (*) >= 2);

Results:

---restore content ends---

The database structure is as follows:
Warehouse (warehouse number, city, area)
Order Form (employee number, supplier number, order ticket, Order date)
Supplier (vendor number, supplier name, address)
Staff (warehouse number, employee number, salary)

The specific data are as follows:

Warehouse table:
Warehouse Number City Area
WH1 Beijing 370
WH2 Shanghai 500
WH3 Guangzhou 200
WH4 Wuhan 400

Order Form:
Employee number supply firm Order No. Order Date
E3 S7 OR67 06/23/01
E1 S4 OR73 07/28/01
E5 S4 OR76 05/25/01
E6 S6 OR77 05/26/01
E3 S4 OR79 06/13/01
E1 S2 OR80 08/29/01
E3 S3 OR90 09/01/01
E3 S3 OR91 07/13/01

Supplier Table:
Vendor number supplier Name Address
S2 Electronics Co., Ltd. Suzhou
S3 Electronics Factory Xian
S4 Hua Tong Electronics Co., Ltd. Beijing
S6 607 Factory Zhengzhou
S7 AI Electronics Factory Beijing

Staff table:
Warehouse number, employee number, payroll.
WH2 E1 1220
WH1 E3 1210
WH2 E4 1250
WH3 E5 1230
WH1 E6 1250

Use the SQL statement to complete:
Ddl
1. Write the statement that created the table above
Command:
CREATE TABLE warehouse (warehouse number varchar) PRIMARY key,
City varchar (10),
area int);
CREATE TABLE supplier (vendor number varchar) Primary key,
Supplier name varchar (30),
Address varchar (10));
CREATE TABLE staff (warehouse number varchar (10),
Employee number varchar (primary) key,
wage int);
CREATE table order form (Employee number varchar (references) staff (employee number),
Supplier number varchar (references) suppliers (supplier number),
Order number varchar (primary key),
Order Date varchar (10));
Dml
2. Give the INSERT statement that inserts the above data
Command:
INSERT into warehouse values (' WH1 ', ' Beijing ', 370);
INSERT into warehouse values (' WH2 ', ' Shanghai ', 500);
INSERT into warehouse values (' WH3 ', ' Guangzhou ', 200);
INSERT into warehouse values (' WH4 ', ' Wuhan ', 400);

INSERT into supplier values (' S2 ', ' Famous electronics company ', ' Suzhou ');
INSERT into supplier values (' S3 ', ' Zhenhua electronics factory ', ' Xian ');
INSERT into supplier values (' S4 ', ' Hua-powered subsidiaries ', ' Beijing ');
INSERT into supplier values (' S6 ', ' 607 factory ', ' Zhengzhou ');
INSERT into supplier values (' S7 ', ' ai Hua Electronics Factory ', ' Beijing ');

INSERT into staff values (' WH2 ', ' E1 ', 1220);
INSERT into staff values (' WH1 ', ' E3 ', 1210);
INSERT into staff values (' WH2 ', ' E4 ', 1250);
INSERT into staff values (' WH3 ', ' E5 ', 1230);
INSERT into staff values (' WH1 ', ' E6 ', 1250);

INSERT INTO order list values (' E3 ', ' S7 ', ' OR67 ', ' 06/23/01 ');
INSERT INTO order list values (' E1 ', ' S4 ', ' OR73 ', ' 07/28/01 ');
INSERT INTO order list values (' E5 ', ' S4 ', ' OR76 ', ' 05/25/01 ');
INSERT INTO order list values (' E6 ', ' S6 ', ' OR77 ', ' 05/26/01 ');
INSERT INTO order list values (' E3 ', ' S4 ', ' OR79 ', ' 06/13/01 ');
INSERT INTO order list values (' E1 ', ' S2 ', ' OR80 ', ' 08/29/01 ');
INSERT INTO order list values (' E3 ', ' S3 ', ' OR90 ', ' 09/01/01 ');
INSERT INTO order list values (' E3 ', ' S3 ', ' OR91 ', ' 07/13/01 ');
Single-Table Query
3. Retrieving all information in employee relations
Command:
SELECT * FROM Employees;
Results:

4. Retrieving all information in a supplier relationship
Command:
SELECT * from supplier;
Results:

5. Retrieving all orders after June
Command:
SELECT * from order form where order date like ' 06% ' or order date like ' 07% ' or order date like ' 08% ' or order date like ' 09% ' or order date like ' 10% ' or order date like ' 11% ' or order date like ' 12% ';

Results:

6. Retrieving warehouses with an area greater than 400
Command:
SELECT * from warehouse where area > 400;
Results:

7. Search for employees who pay more than 1210
Command:
SELECT * from staff where wages > 1210;
Results:

8. The search warehouse is "WH1" or "WH2" and the city with an area greater than 400
Command:
Select City from warehouse where (warehouse number = ' WH1 ' or warehouse number = ' WH2 ') and area > 400;
Results:

9. Find warehouses with warehouses in the area of 400 to 600
Command:
SELECT * from warehouse where area between and 600;
Results:

10. Find out the names of all suppliers with the name "Factory"
Command:
Select Supplier name from supplier where supplier name like '% plant% ';
Results:

11. Find out which suppliers are not in Xian
Command:
SELECT * FROM supplier where address! = ' Xian ';
Results:

12. Find the warehouse that is not in Beijing
Command:
SELECT * FROM warehouse where city! = ' Beijing ';
Results:

13. Sort out all employees ' information in descending order of wages
Command:
SELECT * FROM Employee ORDER by salary Desc;
Results:

14. Sort by warehouse number in ascending order and then descending by salary
Command:
SELECT * FROM Employee ORDER by warehouse number ASC, payroll desc;
Results:

15. Check the warehouse table for a few warehouses.
Command:
Select COUNT (*) warehouse number from warehouse;
Results:

16. Count the number of warehouses in the staff table
Command:
Select COUNT (distinct warehouse number) warehouse number from staff;
Results:

17. Find the Total warehouse area
Command:
Select SUM (area) total area from warehouse;
Results:

Aggregate queries
18. Number of orders per employee
Command:
Select employee number, COUNT (order number) orders from order group by employee number;
Results:

19. Employees with orders of more than 3
Command:
Select employee number, COUNT (order ticket) Order number from order group by employee number (order tracking) > 3;
Results:

Multi-Table Query
20. Find out who is working in a warehouse with an area greater than 400
Command: Select employee number from employee, warehouse where worker. Warehouse number = Warehouse. Warehouse number and area > 400;

Results:

21. Find out who is working in Beijing and their wages
Command: Select employee number, salary from employee, warehouse where worker. Warehouse number = Warehouse. Warehouse number and city = ' Beijing ';

Results:

22. Find employees with wages greater than 1215 and the cities in which they are located
Command: Select employee number, city from employee, warehouse where worker. Warehouse number = Warehouse. Warehouse number and payroll > 1215;

Results:

Sub-query
23. Which cities have at least one order (from a warehouse perspective)
Command: Select city from Warehouse, order form, employee where order form. Employee Number = employee. Employee number and warehouse. Warehouse number = employee. Warehouse Number GROUP by City having Count (*) >= 1;

Results:

24. Find the city without any orders
Command: Select city from warehouse where warehouse number not in (select warehouse number from employee where employee number in (select employee from Purchase order));

Results:

25. Identify and E4 all employees with the same wage
Command:
SELECT * FROM Employee WHERE salary = (select salary from employee where employee number = ' E4 ');
Results:

26. Identify all employees in warehouses with a warehouse area greater than 400
Command: SELECT * from staff where warehouse number in (select warehouse number from warehouse where area > 400);

Results:

27. Find out the suppliers ' workers in Xian and their wages
Command:

Results:

28. Find employees who are not working in the Beijing Warehouse
Command: Select employee number, salary from employee where warehouse number not in (select warehouse number from warehouse where city = ' Beijing ');

Results:

29. Find out the employees who work in the Beijing warehouse
Command: Select employee number, salary from employee where warehouse number in (select warehouse number from warehouse where city = ' Beijing ');

Results:

30. The total wages of employees in Guangzhou and Shanghai Warehouse
Command: Select SUM (Payroll) Total wage from employee where warehouse number in (select warehouse number from warehouse where city in (' Guangzhou ', ' Shanghai '));

Results:

31. Ask for the average area of the warehouse where all employees pay more than 1210
Command: Select AVG (area) from warehouse where warehouse number in (select warehouse number from employee GROUP by warehouse number having min (wage) > 1210);

Results:

32. The highest wage for employees in Shanghai Warehouse
Command: Select MAX (Salary) maximum wage from worker where warehouse number in (select warehouse number from warehouse where city = ' Shanghai ');

Results:

33. Wages of employees with orders greater than or equal to 2
Command: Select salary from employee where employee number in (select employee number from order group by employee number has count (*) >= 2);
Results:

MySQL Beginner advanced 03