Question

SQL

I have a database

CREATE TABLE vendor

( vid CHAR(2) NOT NULL,

vname VARCHAR(25) NOT NULL,

PRIMARY KEY (vid) );

CREATE TABLE category

( catid CHAR(2) NOT NULL,

catname VARCHAR(25) NOT NULL,

PRIMARY KEY (catid) );

CREATE TABLE product

( pid CHAR(3) NOT NULL,

pname VARCHAR(25) NOT NULL,

price NUMERIC (7,2) NOT NULL,

vid CHAR(2) NOT NULL,

categoryid CHAR(2) NOT NULL,

PRIMARY KEY (pid));

CREATE TABLE region

( rid CHAR NOT NULL,

rname VARCHAR(25) NOT NULL,

PRIMARY KEY (rid) );

CREATE TABLE store

( sid VARCHAR(3) NOT NULL,

szip CHAR(5) NOT NULL,

rid CHAR NOT NULL,

PRIMARY KEY (sid));

CREATE TABLE customer

( cid CHAR(7) NOT NULL,

cname VARCHAR(15) NOT NULL,

czip CHAR(5) NOT NULL,

gender CHAR(1) NOT NULL,

PRIMARY KEY (cid) );

CREATE TABLE salestrans

( tid VARCHAR(8) NOT NULL,

custid CHAR(7) NOT NULL,

storeid VARCHAR(3) NOT NULL,

sdate VARCHAR(10) NOT NULL,

PRIMARY KEY (tid));

CREATE TABLE soldvia

( prodid CHAR(3) NOT NULL,

transid VARCHAR(8) NOT NULL,

numitems INT NOT NULL,

PRIMARY KEY (prodid, transid));

INSERT INTO vendor VALUES ('PG','Pacifica Gear');

INSERT INTO vendor VALUES ('MK','Mountain King');

INSERT INTO category VALUES ('CP','Camping');

INSERT INTO category VALUES ('FW','Footwear');

INSERT INTO product VALUES ('1X1','Zzz Bag',100,'PG','CP');

INSERT INTO product VALUES ('2X2','Easy Boot',70,'MK','FW');

INSERT INTO product VALUES ('3X3','Cosy Sock',15,'MK','FW');

INSERT INTO product VALUES ('4X4','Dura Boot',90,'PG','FW');

INSERT INTO product VALUES ('5X5','Tiny Tent',150,'MK','CP');

INSERT INTO product VALUES ('6X6','Biggy Tent',250,'MK','CP');

INSERT INTO product VALUES ('7X7','Comfy Pad',50,'PG','CP');

INSERT INTO product VALUES ('8X8','Turkey Jerky',2,'PG','CP');

INSERT INTO region VALUES ('C','Chicagoland');

INSERT INTO region VALUES ('T','Tristate');

INSERT INTO region VALUES ('D','Downstate');

INSERT INTO region VALUES ('O','OutOfstate');

INSERT INTO store VALUES ('S1','60600','C');

INSERT INTO store VALUES ('S2','60605','C');

INSERT INTO store VALUES ('S3','35400','T');

INSERT INTO customer VALUES ('1-2-333','Tina','60137', 'F');

INSERT INTO customer VALUES ('2-3-444','Tony','60611', 'M');

INSERT INTO customer VALUES ('4-5-666','Pam ','35401', 'F');

INSERT INTO salestrans VALUES ('T111','1-2-333','S1','1/1/2011');

INSERT INTO salestrans VALUES ('T222','2-3-444','S2', '1/1/2011');

INSERT INTO salestrans VALUES ('T333','1-2-333','S3', '2/1/2011');

INSERT INTO salestrans VALUES ('T444','4-5-666','S3', '2/1/2011');

INSERT INTO salestrans VALUES ('T555','2-3-444','S3', '2/1/2011');

INSERT INTO soldvia VALUES ('1X1','T111',1);

INSERT INTO soldvia VALUES ('2X2','T222',1);

INSERT INTO soldvia VALUES ('3X3','T333',5);

INSERT INTO soldvia VALUES ('1X1','T333',1);

INSERT INTO soldvia VALUES ('4X4','T444',1);

INSERT INTO soldvia VALUES ('2X2','T444',2);

INSERT INTO soldvia VALUES ('4X4','T555',4);

INSERT INTO soldvia VALUES ('5X5','T555',2);

INSERT INTO soldvia VALUES ('6X6','T555',1);

1. List the name and ID of customers who made a sales transaction on 2/1/2011 a) b) c) Write this query using the 'in' or a quantified predicate. (10pts) Write this query using the exists predicate. (10pts) Write this query as a single block query. (10pts) 2. List the customer name and store zipcode of all female customers who made a sales transaction at a store in the region named 'Chicagoland'. (10pts) 3. List the customer name and customer zipcode of all customers who did not buy any items from store S2. a) Write this query using a nested query. (10pts) b) Write this query using the EXCEPT operation (Oracle uses minus). (10pts) 4. Display the total number of products that cost less than $100. (10pts) 5. For each vendor, display the vendor name and the cost of the product with the lowest cost in category CP. (10pts) 6. Retrieve the TID and total number of products sold for each transaction that has more than 1 product. (10pts) 7. List the region name, store id and store zip for all regions. Include all regions, even those regions without any stores. (10pts)

Answer 1

1.

a)

The SQL query to display name and id of the customers who made a transaction on 2/1/2011 using ‘in’ is given as follows:

SELECT cname, cid FROM customer, salestrans where customer.cid = salestrans.custid and sdate in ('2/1/2011');

Number of Records: 3 cnane Tina Pam Tony cid 1-2-333 4-5-666 2-3-444

b)

The SQL query to display name and id of the customers who made a transaction on 2/1/2011 using ‘exists’ is given as follows:

SELECT cname, cid FROM customer where exists (select custid from salestrans where custid = customer.cid and sdate = '2/1/2011');

c)

The SQL query to display name and id of the customers who made a transaction on 2/1/2011 using single block query is given as follows:

SELECT cname, cid FROM customer, salestrans where customer.cid = salestrans.custid and sdate = '2/1/2011';

2.

The SQL query to display the customer name and store zip code of all female customers who made a sales transaction at a store in the region named ‘Chicagoland’ is given as follows:

SELECT cname, szip FROM customer join salestrans on salestrans.custid = customer.cid join store on store.sid = salestrans.storeid join region on region.rid = store.rid where rname = 'Chicagoland' and gender = 'F';

3.

a)

The SQL query to display the customer name and customer zip code of all customers who did not buy any items from store S2 using nested query is given as follows:

select cname, czip from customer where cname <> (select cname from customer, salestrans where customer.cid = salestrans.custid and storeid = 'S2');

b)

The SQL query to display the customer name and customer zip code of all customers who did not buy any items from store S2 using nested query is given as follows:

select cname, czip from customer, salestrans except select cname, czip from customer, salestrans where customer.cid = salestrans.custid and salestrans.storeid = 'S2';

4.

The SQL query to display the total number of products that cost less than $100 is given as follows:

select count(price) as NumberOfProducts from product where price < 100;

5.

The SQL query to display the vendor name and cost of product with the lowest cost in category CP is given as follows:

select vname, price, min(price) as MinPrice from vendor, product where vendor.vid = product.vid and categoryid = 'CP';

6.

The SQL query to display the TID and total number of products sold for each transaction that has more than 1 product is given as follows:

select tid, count(pid) as TotalProduct from product, salestrans, soldvia where salestrans.tid = soldvia.transid and soldvia.prodid = product.pid and numItems > 1;

7.

The SQL query to display the region name, store id, and store zip for all regions even those regions which do not have any stores is given as follows:

select rname, sid, szip from region left outer join store on region.rid = store.rid;