CSCI 5333.4 DBMS
Homework #4

(1) tee c:\users\li\mysql_out_1.txt

(2) and (3)

-- H4 Q2
CREATE TABLE IF NOT EXISTS H4_employee (
   EmployeeID varchar(7) PRIMARY KEY,
   Firstname varchar(20) NOT NULL,
   Lastname varchar(20) NOT NULL,
   Address varchar(100),
   Status integer(2) DEFAULT 1,
   INDEX USING BTREE (Lastname, Firstname)
);

INSERT INTO H4_employee VALUE ('1000000', 'Sharon', 'Hall', 'Somewhere in Texas', DEFAULT);

-- H4 Q3
CREATE TABLE IF NOT EXISTS H4_department (
   DeptCode varchar(4) PRIMARY KEY,
   DeptName varchar(20) UNIQUE,
   Office varchar(50) NOT NULL,
   HeadId varchar(7),
   FOREIGN KEY (HeadId) REFERENCES H4_employee(EmployeeID)
);

INSERT INTo H4_department VALUES('1000','ASTR','Delta 110',NULL), ('2000','CSCI','Delta 120','1000000');

-- House cleaning
DROP TABLE H4_department;
DROP TABLE H4_employee;

(4)

-- H4 Q4
select SName as "Supplier", PName as "Blue Part", Weight, Quantity as "Number"
from Supplier, Supply, Part
where Supplier.SNUM = Supply.SNUM
and Supply.PNum = Part.PNum
and Part.Color = 'Blue';

(5)

-- H4 Q5
SELECT SName
FROM Supplier, Supply
WHERE Supplier.SNum = Supply.SNum
GROUP BY SName
HAVING COUNT(*) > 2;

(6)

-- H4 Q6
SELECT SName AS "Supplier", COUNT(Part.PNum) as "# Parts supplied", AVG(WEIGHT) as "Average part weight"
FROM Supplier, Supply, Part
WHERE Supplier.SNum = Supply.SNum
AND Supply.PNum = Part.PNum
GROUP BY SName;

(7)

-- H4 Q7
SELECT *
FROM Supplier
WHERE SCity REGEXP '^[adsl]+$';

(8)

SELECT DISTINCT SNUM
FROM Supply, Part
WHERE Supply.PNum = Part.PNum
and not Color = 'Green'
Order by SNum;