SQL>
SQL> CREATE TABLE Department (
2 DepartmentID INT NOT NULL PRIMARY KEY,
3 Name VARCHAR(50) NOT NULL,
4 Description VARCHAR(200) NULL);
SQL>
SQL> CREATE SEQUENCE DepartmentIDSeq;
SQL>
SQL> CREATE OR REPLACE TRIGGER DepartmentAutonumberTrigger
2 BEFORE INSERT ON Department
3 FOR EACH ROW
4 BEGIN
5 SELECT DepartmentIDSeq.NEXTVAL
6 INTO :NEW.DepartmentID FROM DUAL;
7 END;
8 /
SQL>
SQL> INSERT INTO Department (Name, Description)
2 VALUES ('Software', 'Coding');
SQL> INSERT INTO Department (Name, Description)
2 VALUES ('Hardware', 'Building');
SQL> INSERT INTO Department (Name, Description)
2 VALUES ('QA', 'Testing');
SQL>
SQL>
SQL>
SQL>
SQL> CREATE TABLE Category (
2 CategoryID INT NOT NULL PRIMARY KEY,
3 DepartmentID INT NOT NULL,
4 Name VARCHAR(50) NOT NULL,
5 Description VARCHAR (200) NULL);
SQL>
SQL> CREATE SEQUENCE CategoryIDSeq;
SQL>
SQL> CREATE OR REPLACE TRIGGER CategoryAutonumberTrigger
2 BEFORE INSERT ON Category
3 FOR EACH ROW
4 BEGIN
5 SELECT CategoryIDSeq.NEXTVAL
6 INTO :NEW.CategoryID FROM DUAL;
7 END;
8 /
SQL> INSERT INTO Category (DepartmentID, Name, Description)
2 VALUES (1, 'Local', 'In town');
SQL> INSERT INTO Category (DepartmentID, Name, Description)
2 VALUES (1, 'Remote', 'Telecommute');
SQL> INSERT INTO Category (DepartmentID, Name, Description)
2 VALUES (2, 'Masks', 'By bits');
SQL> INSERT INTO Category (DepartmentID, Name, Description)
2 VALUES (3, 'Wireless', 'Not connected');
SQL> INSERT INTO Category (DepartmentID, Name, Description)
2 VALUES (3, 'Wired', 'Connected');
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> CREATE TABLE Product (
2 ProductID INT NOT NULL PRIMARY KEY,
3 Name VARCHAR(50) NOT NULL,
4 Description VARCHAR(1000) NOT NULL,
5 Price NUMBER NULL,
6 ImagePath VARCHAR(50) NULL,
7 soldout NUMBER(1,0) NULL,
8 Promotion NUMBER(1,0) NULL);
SQL>
SQL> CREATE SEQUENCE ProductIDSeq;
SQL>
SQL> CREATE OR REPLACE TRIGGER ProductAutonumberTrigger
2 BEFORE INSERT ON Product
3 FOR EACH ROW
4 BEGIN
5 SELECT ProductIDSeq.NEXTVAL
6 INTO :NEW.ProductID FROM DUAL;
7 END;
8 /
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath,soldout, Promotion)
2 VALUES ('Pen', 'Ball Pen',5.99, 'pen.jpg', 1, 0);
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath,soldout, Promotion)
2 VALUES ('Ruler','Long',14.99, 'ruler.jpg', 0, 0);
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath,soldout, Promotion)
2 VALUES ('Desk', 'Computer Desk',5.99, 'desk.jpg', 0, 1);
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ('PC', 'Notebook',49.99, 'pc.jpg', 0, 1);
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ('Mouse', 'Wireless',9.99, 'mouse.jpg', 1, 0);
SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ('Keyboard','keyboard',3.75, 'keyboard.jpg', 0, 0);
SQL>
SQL>
SQL> CREATE TABLE ProductCategory (
2 ProductID INT NOT NULL,
3 CategoryID INT NOT NULL,
4 PRIMARY KEY (ProductID, CategoryID)
5 );
SQL>
SQL>
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (1,3);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,1);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,3);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (3,3);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (4,1);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (5,2);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,3);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,4);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (7,4);
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (8,5);
SQL>
SQL> SELECT D.Name AS "Department",
2 (SELECT COUNT(ProductID)
3 FROM ProductCategory INNER JOIN Category
4 ON ProductCategory.CategoryID = Category.CategoryID
5 WHERE Category.DepartmentID = D.DepartmentID) AS "Products"
6 FROM Department D;
Software
3
Hardware
4
QA
3
SQL>
SQL>
SQL>
SQL>
SQL> SELECT D.Name AS "Department", COUNT(P.Name) AS "Products"
2 FROM Department D, Product P
3 WHERE ProductID IN
4 (SELECT ProductID
5 FROM ProductCategory INNER JOIN Category
6 ON ProductCategory.CategoryID = Category.CategoryID
7 WHERE Category.DepartmentID = D.DepartmentID)
8 GROUP BY D.Name
9 ORDER BY D.Name;
Hardware
4
QA
1
Software
3
SQL>
SQL>
SQL> drop table department;
SQL> drop sequence DepartmentIDSeq;
SQL> drop table Product;
SQL> drop table ProductCategory;
SQL> drop table Category;
SQL> drop sequence CategoryIDSeq;
SQL> drop sequence ProductIDSeq;