6.7 How to insert a foreign key MySQL exercises

1 command: ALTER TABLE add FOREIGN KEY constraint foreign key name
Foreign key (field name to add foreign key table) references associated table name (associated field name);
Note: Foreign key names cannot be duplicated

2 adding foreign keys when building a table
Foreign KEY (o_buyer_id) references S_user (u_id),
Foreign KEY (o_seller_id) references S_user (u_id)

Warehouse table plus exercises

table (i)worker staff table

Property name	Data type	can be empty	Include
"	varchar	No	Employee number (main code)
ware_id	varchar  (a)	No	Warehouse number (outside code)
wages	varchar  ()	No	Payroll

table (ii)warehouse( warehouse table)

Property name	Data type	can be empty	Include
ware_id	< p>varchar	No	Warehouse number (main code)
City	VA rchar	No	City /td>
Area	varchar  (	No	Area

table (three ) Table (iii)order (order form )

Property name	Data type	Can be empty	Meaning
work_id	varchar (20)	Whether	Employee number (outside code)
supp_id	varchar (20)	Whether	Vendor number (external code)
order_id	varchar (20)	Can	Order tracking Number
Main code: work_id+ supp_id

Table (iv)supply ( supplier table )

/table>

Property name	Data type	can be empty	Include
supp_id	varchar  (20	No	Vendor number (main code)
Supp_name	varchar  (20) /p>	No	Supplier name
Supp_area	varchar  (+) /td>	No	address

--1. Retrieve all wage values from employee relations. Select wages from worker;

--2. Retrieving all records in a warehouse relationship. SELCT * from Warehouse;

--3. Search for employee numbers with a salary of more than $1230. Select work_id from worker where wages >1230;

--4. Retrieve which warehouses have workers with a salary of more than 1210 yuan.

Select ware_id from worker where wages>1210;

--5. Give a worker's number that works in the warehouse "WH1" or "WH2" and has a salary of less than 1250 yuan.

Select work_id from worker where (ware_id = "WH1" or ware_id = "wh2") and wages<1250;

--6. Find out the number of employees with more than $1230 in wages and the city in which they are located.

Select work_id, city from worker as a, warehouse as B

where a.ware_id = b.ware_id and wages>1230;

Select Work_id,city from warehouse join worker on warehouse.ware_id = worker.ware_id and wages>1230;

--7. Find out the number of employees working in warehouses larger than 400 and the cities where these workers work.

Select work_id,city from worker as a,warehouse as B Where (a.ware_id = b.ware_id and area>400);

--★8. Which cities have at least one warehouse staff wages of 1250 yuan.

Select city from Warehouse where ware_id in (select ware_id from worker where wages=1250);

Select city from worker as a,warehouse as B where a.ware_id=b.ware_id and a.wages=1250;

--9. Inquire about the warehouse where all employees are paid more than 1210 yuan.

SELECT * FROM warehouse where ware_id not in (select ware_id from worker where wages<=1210) and ware_id in (select Ware_i D from worker);

Select *from Warehouse where ware_id in (select ware_id from worker where 1210< all (select wages from worker where war EHOUSE.WARE_ID=WORKER.WARE_ID))

where warehouse.ware_id=worker.ware_id is

put The ware_id value in the Warehouse table corresponds to the ware_id value one by one in the worker table

The default is from wh1 to start looking.

--10. Find all employees who earn the same salary as E4.

Select work_id from worker where wages = (select wages from worker where work_id = "E4");

--11. Retrieves employee information for wages in the range of $1220 to $1240.

Select * from worker where wages between 1220 and 1240;

--★12. Retrieve all company information from supplier relationships, not factory or other supplier information.

Select *from Supply where supp_name like "% company";

--13. Find out all the supplier information that is not in Beijing.

Select *from Supply where Supp_area! = "Beijing";

--14. Retrieve all employee information in ascending order by employee's salary value.

Select *from worker order by wages ASC;

--15. Sort by warehouse number first, then sort by salary and export all employee information.

Select *from worker order by Ware_id,wages;

--16. Find out the number of supplier locations

Select count (distinct supp_area) from supply;

-17. The total number of wages to be paid.

Select sum (wages) from worker;

--18. The sum of the wages of the warehouse workers in Beijing and Shanghai.

Select sum (wages) from the worker where ware_id in (select ware_id from warehouse where city = "Beijing" or "Shanghai");

--19. Ask all employees to pay more than 1210 yuan of the average area of the warehouse.

Select AVG (area) from warehouse where ware_id not in (select ware_id from worker where wages<=1210) and ware_id in (sel ECT ware_id from worker);

Select AVG (area) from warehouse where ware_id in (select ware_id from worker where wage >1210);

--20. The maximum wage value of the employees working in the WH2 warehouse.

Select Max (wages) from worker where ware_id = "WH2";

--21. Ask for the average salary of employees in each warehouse.

Select Ware_id,avg (wages) from the worker group by WARE_ID;

--22. Ask for the average salary of at least two employees per warehouse.

Select Ware_id,count (*), AVG (wages) from the worker group by WARE_ID have Count (work_id) >=2

--23. Locate the purchase order for the vendor that has not been identified.

SELECT * from order1 where supp_id = "";

--24. Lists the purchase order information for the vendor that has been identified.

SELECT * from order1 where supp_id = "";

-25. Query the vendor name.

Select Supp_name from supply;

--★26. Add a new field total amount in the order form to indicate the total amount that should be paid to complete the order form.

ALTER TABLE Order1 add amount varchar (a) not null;

-27. List the order form information for each employee who has the highest total amount handled.

Select Max (amount) from Order1 Group by work_id;

--28. Retrieves information about which warehouses do not yet have workers ' warehouses.

SELECT * FROM warehouse where ware_id not in (select distinct ware_id from worker group by WORK_ID have Count (work_id) & GT;0);

--29. Retrieves information about which warehouses have at least one employee's warehouse.

SELECT * from warehouse where ware_id in (select ware_id from worker group by WORK_ID have Count (work_id) >=1);

--★30. Retrieve the warehouse number where the employee's salary is greater than or equal to the wages of any employee in the WH1 warehouse.

Select distinct ware_id from worker where wages >=any (select wages from worker where ware_id = "wh1");

Select distinct ware_id from worker where wages >= (select min (wages) from worker where ware_id = "wh1");

--★31. Retrieve the warehouse number where the employee's wages are greater than or equal to the wages of all employees in the WH1 warehouse.

Select distinct ware_id from worker where wages >=all (select wages from worker where ware_id = "wh1");

6.7 How to insert a foreign key MySQL exercises