VOOZH about

URL: https://en.wikibooks.org/wiki/SQL_Exercises/The_warehouse

⇱ SQL Exercises/The warehouse - Wikibooks, open books for an open world

Jump to content
From Wikibooks, open books for an open world

Relational Schema

[edit | edit source]

👁 Image

Exercises

[edit | edit source]

1. Select all warehouses.

Click to see solution
SELECT*FROMWarehouses;


2. Select all boxes with a value larger than $150.

Click to see solution
SELECT*FROMBoxes
WHEREValue>150;


3. Select all distinct contents in all the boxes.

Click to see solution
SELECTDISTINCTContents
FROMBoxes;


4. Select the average value of all the boxes.

Click to see solution
SELECTAVG(Value)
FROMBoxes;


5. Select the warehouse code and the average value of the boxes in each warehouse.

Click to see solution
SELECTWarehouse,AVG(Value)
FROMBoxes
GROUPBYWarehouse;


6. Same as previous exercise, but select only those warehouses where the average value of the boxes is greater than 150.

Click to see solution
SELECTWarehouse,AVG(Value)
FROMBoxes
GROUPBYWarehouse
HAVINGAVG(Value)>150;


7. Select the code of each box, along with the name of the city the box is located in.

Click to see solution
SELECTBoxes.Code,Location
FROMWarehousesINNERJOINBoxes
ONWarehouses.Code=Boxes.Warehouse;


8. Select the warehouse codes, along with the number of boxes in each warehouse. Optionally, take into account that some warehouses are empty (i.e., the box count should show up as zero, instead of omitting the warehouse from the result).

Click to see solution
/* Not taking into account empty warehouses */
SELECTWarehouse,COUNT(*)
FROMBoxes
GROUPBYWarehouse;

/* Taking into account empty warehouses */
SELECTWarehouses.Code,COUNT(Boxes.Code)
FROMWarehousesLEFTJOINBoxes
ONWarehouses.Code=Boxes.Warehouse
GROUPBYWarehouses.Code;


9. Select the codes of all warehouses that are saturated (a warehouse is saturated if the number of boxes in it is larger than the warehouse's capacity).

Click to see solution
SELECTCode
FROMWarehouses
WHERECapacity<
(
SELECTCOUNT(*)
FROMBoxes
WHEREWarehouse=Warehouses.Code
);

/* Alternate method not involving nested statements */
SELECTWarehouses.Code
FROMWarehousesJOINBoxesONWarehouses.Code=Boxes.Warehouse
GROUPBYWarehouses.code,Warehouses.Capacity
HAVINGCount(Boxes.code)>Warehouses.Capacity


10. Select the codes of all the boxes located in Chicago.

Click to see solution
/* Without subqueries */
SELECTBoxes.Code
FROMWarehousesRIGHTJOINBoxes
ONWarehouses.Code=Boxes.Warehouse
WHERELocation='Chicago';

/* With a subquery */
SELECTCode
FROMBoxes
WHEREWarehouseIN
(
SELECTCode
FROMWarehouses
WHERELocation='Chicago'
);


11. Create a new warehouse in New York with a capacity for 3 boxes.

Click to see solution
INSERT
INTOWarehouses
(Location,Capacity)
VALUES('New York',3);


12. Create a new box, with code "H5RT", containing "Papers" with a value of $200, and located in warehouse 2.

Click to see solution
INSERTINTOBoxes
VALUES('H5RT','Papers',200,2);


13. Reduce the value of all boxes by 15%.

Click to see solution
UPDATEBoxesSETValue=Value*0.85;


14. Apply a 20% value reduction to boxes with a value larger than the average value of all the boxes.

Click to see solution
UPDATEBoxes
SETBoxes.value=Boxes.value*0.8
WHEREBoxes.codeIN
(
SELECT*FROM
(
SELECTBx.code
FROMBoxesASBx
WHEREBx.value>
(
SELECTAVG(B.value)
FROMBoxesASB
)
)ASBxs
);


15. Remove all boxes with a value lower than $100.

Click to see solution
DELETEFROMBoxesWHEREValue<100;


16. Remove all boxes from saturated warehouses.

Click to see solution
DELETEFROMBoxes
WHEREWarehouseIN
(
SELECT*FROM
(
SELECTCode
FROMWarehouses
WHERECapacity<
(
SELECTCOUNT(*)
FROMBoxes
WHEREWarehouse=Warehouses.Code
)
)ASBxs
);

Table creation code

[edit | edit source]
CREATETABLEWarehouses(
CodeINTEGERPRIMARYKEYNOTNULL,
LocationTEXTNOTNULL,
CapacityINTEGERNOTNULL
);

CREATETABLEBoxes(
CodeTEXTPRIMARYKEYNOTNULL,
ContentsTEXTNOTNULL,
ValueREALNOTNULL,
WarehouseINTEGERNOTNULL,
CONSTRAINTfk_Warehouses_CodeFOREIGNKEY(Warehouse)REFERENCESWarehouses(Code)
);


Click to see MySQL syntax.
CREATETABLEWarehouses(
CodeINTEGERNOTNULL,
LocationVARCHAR(255)NOTNULL,
CapacityINTEGERNOTNULL,
PRIMARYKEY(Code)
);
CREATETABLEBoxes(
CodeVARCHAR(255)NOTNULL,
ContentsVARCHAR(255)NOTNULL,
ValueREALNOTNULL,
WarehouseINTEGERNOTNULL,
PRIMARYKEY(Code),
FOREIGNKEY(Warehouse)REFERENCESWarehouses(Code)
)ENGINE=INNODB;

Sample dataset

[edit | edit source]
INSERTINTOWarehouses(Code,Location,Capacity)VALUES(1,'Chicago',3);
INSERTINTOWarehouses(Code,Location,Capacity)VALUES(2,'Chicago',4);
INSERTINTOWarehouses(Code,Location,Capacity)VALUES(3,'New York',7);
INSERTINTOWarehouses(Code,Location,Capacity)VALUES(4,'Los Angeles',2);
INSERTINTOWarehouses(Code,Location,Capacity)VALUES(5,'San Francisco',8);

INSERTINTOBoxes(Code,Contents,Value,Warehouse)VALUES('0MN7','Rocks',180,3);
INSERTINTOBoxes(Code,Contents,Value,Warehouse)VALUES('4H8P','Rocks',250,1);
INSERTINTOBoxes(Code,Contents,Value,Warehouse)VALUES('4RT3','Scissors',190,4);
INSERTINTOBoxes(Code,Contents,Value,Warehouse)VALUES('7G3H','Rocks',200,1);
INSERTINTOBoxes(Code,Contents,Value,Warehouse)VALUES('8JN6','Papers',75,1);
INSERTINTOBoxes(Code,Contents,Value,Warehouse)VALUES('8Y6U','Papers',50,3);
INSERTINTOBoxes(Code,Contents,Value,Warehouse)VALUES('9J6F','Papers',175,2);
INSERTINTOBoxes(Code,Contents,Value,Warehouse)VALUES('LL08','Rocks',140,4);
INSERTINTOBoxes(Code,Contents,Value,Warehouse)VALUES('P0H6','Scissors',125,1);
INSERTINTOBoxes(Code,Contents,Value,Warehouse)VALUES('P2T6','Scissors',150,2);
INSERTINTOBoxes(Code,Contents,Value,Warehouse)VALUES('TU55','Papers',90,5);
Category: