SQL>
SQL>
SQL> CREATE TABLE ProductCategory (
2 ProductID INT NOT NULL,
3 CategoryID INT NOT NULL,
4 PRIMARY KEY (ProductID, CategoryID)
5 );
Table created.
SQL>
SQL>
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (1,3);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,1);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,3);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (3,3);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (4,1);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (5,2);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,3);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,4);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (7,4);
1 row created.
SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (8,5);
1 row created.
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);
Table created.
SQL>
SQL> CREATE SEQUENCE CategoryIDSeq;
Sequence created.
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 /
Trigger created.
SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (1, 'Local', 'In town');
1 row created.
SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (1, 'Remote', 'Telecommute');
1 row created.
SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (2, 'Masks', 'By bits');
1 row created.
SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (3, 'Wireless', 'Not connected');
1 row created.
SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (3, 'Wired', 'Connected');
1 row created.
SQL>
SQL>
SQL> SELECT Category.CategoryID, Name
2 FROM Category INNER JOIN ProductCategory
3 ON Category.CategoryID = ProductCategory.CategoryID
4 AND ProductCategory.ProductID = 6;
CATEGORYID NAME
---------- --------------------------------------------------
3 Masks
4 Wireless
2 rows selected.
SQL>
SQL>
SQL> SELECT CategoryID, Name
2 FROM Category
3 WHERE CategoryID IN
4 (SELECT CategoryID
5 FROM ProductCategory
6 WHERE ProductCategory.ProductID = 6);
CATEGORYID NAME
---------- --------------------------------------------------
3 Masks
4 Wireless
2 rows selected.
SQL>
SQL>
SQL> drop table Category;
Table dropped.
SQL> drop table ProductCategory;
Table dropped.
SQL> drop sequence CategoryIDSeq;
Sequence dropped.