Products--Warehouse table query

Database Description: This query is based on MySQL

I. Establishment of the table

1.1 Tables and fields to be created

Employee Form E (employeeid,name,department): Employee number, name, department;

Product table P (prodectid,name,model,size,color): Product number, product name, model, size, color;

Warehouse Table W (Warehouseid,name,address,employeeid): Warehouse number, warehouse name, address, owner number;

Inventory table L (warehouseid,prodectid,quantity): Warehouse number, product number, product quantity.

1.2 Table relationships

Warehouse Table W is a one-to-many association with Employee table E (One-many) and EmployeeID as a foreign key when building a warehouse table.

The Warehouse table W is a one-to-one association with the inventory table L (One-one), which is somewhat special when building a warehouse table because this table has two foreign keys and no primary key, as shown in:

Second, query

1. Query the name of each product and the total stock of the product:

Sql:

Select Name,sum (Quantity) From P,l where P.productid=l.productid group by name

Analysis: This SQL requires grouping of query results, that is, the same product names are grouped together, and then the inventory quantity per group is calculated.

2. Query for product names that exist in all warehouses

Sql:

Select name from P Where not exists (SELECT * FROM W Where not exists (SELECT * FROM L where P.productid=l.productid and W.warehouseid=l.warehouseid))

Analysis: This query is more difficult than the first, the main research on the SQL not exists understanding and application, the SQL query logic is first in the Warehouse table W query all the warehouses do not have goods , based on this result, then check the commodity table p in the product name --The name of the product that does not exist in the result, which is present in all warehouses. The splitting of this SQL is described as follows:

1. Check the warehouse table W for all items not in the warehouse

Sql:

SELECT * FROM W,p Where NOT EXISTS (SELECT * FROM L where P.productid=l.productid and W.warehouseid=l.warehouseid)

Results:

Figure 1

Analysis:

First look at the data information in the Inventory table L:

From the above data information can be seen warehouse 1 only merchandise 1, the lack of goods 2, 3, so the above query results should have 2 records; Warehouse 2, Warehouse 3 Similarly, the above query results should have 1 records, see "Figure 1", sure enough. now the focus is on, now that all the warehouses do not exist in the goods, then again using not exists, query the commodity table p in the product name-and in this result ("Figure 1") does not exist, that is, all the warehouses exist in the name of the product.

2. Search for product names that do not exist in the above results in the product table P

Select name from P Where NOT EXISTS (SELECT * FROM W Where NOT EXISTS (SELECT * FROM L where P.productid=l.productid and W.warehouseid=l.warehouseid))

Results:

Products--Warehouse table query