Query writing and relational algebra Expression, Write the following 4 QUESTION
ID: 3842811 • Letter: Q
Question
Query writing and relational algebra Expression, Write the following 4 QUESTION USING sql queries AND if needed using the Cape Codd DB on bottom
13. Get a list of departments and the total items on order for each department, sorted from largest to smallest quantity. Use a join.
Use the PC Manufacturer sample DB to write these additional queries as well as relational algebra expression.
1. Find the model number and price of all products (of any type) made by maker B.
2. Find those makers that sell laptops, not PCs.
3. Find those makers of at least two different computers. All queries should be contained in a single plain text file with a sql extension. Queries must be in the order above. Don't forget to separate each query with a semi-colon
CREATE DATABASE cape_codd;
USE cape_codd;
CREATE TABLE RETAIL_ORDER (
OrderNumber Integer NOT NULL,
StoreNumber Integer NULL,
StoreZip Char (9) NULL,
OrderMonth Char (12) NOT NULL,
OrderYear Integer NOT NULL,
OrderTotal Decimal(9,2) NULL,
CONSTRAINT RETAIL_ORDER_PK PRIMARY KEY (OrderNumber)
);
CREATE TABLE SKU_DATA (
SKU Integer NOT NULL,
SKU_Description Char (35) NOT NULL,
Department Char (30) NOT NULL,
Buyer Char (30) NULL,
CONSTRAINT SKU_DATA_PK PRIMARY KEY (SKU)
);
CREATE TABLE ORDER_ITEM (
OrderNumber Integer NOT NULL,
SKU Integer NOT NULL,
Quantity Integer NOT NULL,
Price Decimal(9,2) NOT NULL,
ExtendedPrice Decimal(9,2) NOT NULL,
CONSTRAINT ORDER_ITEM_PK PRIMARY KEY (SKU, OrderNumber),
CONSTRAINT SKU_Relationship FOREIGN KEY (SKU)
REFERENCES SKU_DATA (SKU),
CONSTRAINT RETAIL_ORDER_Relationship FOREIGN KEY (OrderNumber)
REFERENCES RETAIL_ORDER (OrderNumber)
);
CREATE TABLE WAREHOUSE (
Warehouse Char(30) NOT NULL,
Manager Char(30) NULL,
SquareFeet Integer NULL,
CONSTRAINT WAREHOUSE_PK PRIMARY KEY (Warehouse)
);
CREATE TABLE INVENTORY (
SKU Integer NOT NULL,
Warehouse Char (30) NOT NULL,
Description Char (35) NOT NULL,
QuantityOnHand Integer NULL,
QuantityOnOrder Integer NULL,
CONSTRAINT INVENTORY_PK PRIMARY KEY (SKU, Warehouse),
CONSTRAINT SKU_DATA_Relationship FOREIGN KEY (SKU)
REFERENCES SKU_DATA (SKU),
CONSTRAINT WAREHOUSE_Relationship FOREIGN KEY (Warehouse)
REFERENCES WAREHOUSE (Warehouse)
);
INSERT INTO RETAIL_ORDER VALUES (
1000, 10, '98110', 'December', 2008, 445);
INSERT INTO RETAIL_ORDER VALUES (
2000, 20, '02335', 'December', 2008, 310);
INSERT INTO RETAIL_ORDER VALUES (
3000, 10, '98110', 'January', 2009, 480);
INSERT INTO SKU_DATA VALUES (
100100, 'Std. Scuba Tank, Yellow', 'Water Sports', 'Pete Hansen');
INSERT INTO SKU_DATA VALUES (
100200, 'Std. Scuba Tank, Magenta', 'Water Sports', 'Pete Hansen');
INSERT INTO SKU_DATA VALUES (
101100, 'Dive Mask, Small Clear', 'Water Sports', 'Nancy Meyers');
INSERT INTO SKU_DATA VALUES (
101200, 'Dive Mask, Med Clear', 'Water Sports', 'Nancy Meyers');
INSERT INTO SKU_DATA VALUES (
201000, 'Half-dome Tent', 'Camping', 'Cindy Lo');
INSERT INTO SKU_DATA VALUES (
202000, 'Half-dome Tent Footprint', 'Camping', 'Cindy Lo');
INSERT INTO SKU_DATA VALUES (
301000, 'Light Fly Climbing Harness', 'Climbing', 'Jerry Martin');
INSERT INTO SKU_DATA VALUES (
302000, 'Locking carabiner, Oval', 'Climbing', 'Jerry Martin');
INSERT INTO ORDER_ITEM VALUES (
1000, 201000, 1, 300, 300);
INSERT INTO ORDER_ITEM VALUES (
1000, 202000, 1, 130, 130);
INSERT INTO ORDER_ITEM VALUES (
2000, 101100, 4, 50, 200);
INSERT INTO ORDER_ITEM VALUES (
2000, 101200, 2, 50, 100);
INSERT INTO ORDER_ITEM VALUES (
3000, 100200, 1, 300, 300);
INSERT INTO ORDER_ITEM VALUES (
3000, 101100, 2, 50, 100);
INSERT INTO ORDER_ITEM VALUES (
3000, 101200, 1, 50, 50);
/* WAREHOUSE data */
INSERT INTO WAREHOUSE VALUES (
'Atlanta', 'Jones', 125000);
INSERT INTO WAREHOUSE VALUES (
'Chicago', 'Smith', 100000);
INSERT INTO WAREHOUSE VALUES (
'New Jersey', 'Evans', 150000);
INSERT INTO WAREHOUSE VALUES (
'Seattle', 'Rogers', 130000);
/* INVENTORY data */
INSERT INTO INVENTORY VALUES (
100100, 'Atlanta', 'Std. Scuba Tank, Yellow', 250, 0);
INSERT INTO INVENTORY VALUES (
100100, 'Chicago', 'Std. Scuba Tank, Yellow', 100, 50);
INSERT INTO INVENTORY VALUES (
100100, 'New Jersey', 'Std. Scuba Tank, Yellow', 100, 0);
INSERT INTO INVENTORY VALUES (
100100, 'Seattle', 'Std. Scuba Tank, Yellow', 200, 0);
INSERT INTO INVENTORY VALUES (
100200, 'Atlanta', 'Std. Scuba Tank, Magenta', 200, 30);
INSERT INTO INVENTORY VALUES (
100200, 'Chicago', 'Std. Scuba Tank, Magenta', 75, 75);
INSERT INTO INVENTORY VALUES (
100200, 'New Jersey', 'Std. Scuba Tank, Magenta', 100, 100);
INSERT INTO INVENTORY VALUES (
100200, 'Seattle', 'Std. Scuba Tank, Magenta', 250, 0);
INSERT INTO INVENTORY VALUES (
101100, 'Atlanta', 'Dive Mask, Small Clear', 0, 500);
INSERT INTO INVENTORY VALUES (
101100, 'Chicago', 'Dive Mask, Small Clear', 0, 500);
INSERT INTO INVENTORY VALUES (
101100, 'New Jersey', 'Dive Mask, Small Clear', 300, 200);
INSERT INTO INVENTORY VALUES (
101100, 'Seattle', 'Dive Mask, Small Clear', 450, 0);
INSERT INTO INVENTORY VALUES (
101200, 'Atlanta', 'Dive Mask, Med Clear', 100, 500);
INSERT INTO INVENTORY VALUES (
101200, 'Chicago', 'Dive Mask, Med Clear', 50, 500);
INSERT INTO INVENTORY VALUES (
101200, 'New Jersey', 'Dive Mask, Med Clear', 475, 0);
INSERT INTO INVENTORY VALUES (
101200, 'Seattle', 'Dive Mask, Med Clear', 250, 250);
INSERT INTO INVENTORY VALUES (
201000, 'Atlanta', 'Half-dome Tent', 2, 100);
INSERT INTO INVENTORY VALUES (
201000, 'Chicago', 'Half-dome Tent', 10, 250);
INSERT INTO INVENTORY VALUES (
201000, 'New Jersey', 'Half-dome Tent', 250, 0);
INSERT INTO INVENTORY VALUES (
201000, 'Seattle', 'Half-dome Tent', 0, 250);
INSERT INTO INVENTORY VALUES (
202000, 'Atlanta', 'Half-dome Tent Footprint', 10, 250);
INSERT INTO INVENTORY VALUES (
202000, 'Chicago', 'Half-dome Tent Footprint', 1, 250);
INSERT INTO INVENTORY VALUES (
202000, 'New Jersey', 'Half-dome Tent Footprint', 100, 0);
INSERT INTO INVENTORY VALUES (
202000, 'Seattle', 'Half-dome Tent Footprint', 0, 200);
INSERT INTO INVENTORY VALUES (
301000, 'Atlanta', 'Light Fly Climbing Harness', 300, 250);
INSERT INTO INVENTORY VALUES (
301000, 'Chicago', 'Light Fly Climbing Harness', 250, 250);
INSERT INTO INVENTORY VALUES (
301000, 'New Jersey', 'Light Fly Climbing Harness', 0, 250);
INSERT INTO INVENTORY VALUES (
301000, 'Seattle', 'Light Fly Climbing Harness', 0, 250);
INSERT INTO INVENTORY VALUES (
302000, 'Atlanta', 'Locking carabiner', 1000, 0);
INSERT INTO INVENTORY VALUES (
302000, 'Chicago', 'Locking carabiner', 1250, 0);
INSERT INTO INVENTORY VALUES (
302000, 'New Jersey', 'Locking carabiner', 500, 500);
INSERT INTO INVENTORY VALUES (
302000, 'Seattle', 'Locking carabiner', 0, 1000);
Explanation / Answer
13: I have written the query to get the list of departments and Quantity of items in that department. if you need order wise you can uncomment the order number
select Department,
--OrderNumber
SUM(Quantity) AS Qauntity
from SKU_DATA SD
inner join ORDER_ITEM OI
on sd.SKU=oi.SKU
group by Department--,OrderNumber
order by SUM(Quantity) DESC
Note: For the subparts 1,2 and 3 we need to have PC manufacturer sample DB. Please provide the bak file or script file. Thank you.