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)