Get Categories and Products (with Joins) : Table Join Basics « Table Joins « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Table Joins
  3. Table Join Basics

Get Categories and Products (with Joins)

   
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);

Table created.

SQL>
SQL> CREATE SEQUENCE ProductIDSeq;

Sequence created.

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  /

Trigger created.

SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath,soldout, Promotion)
  2  VALUES ('Pen', 'Ball Pen',5.99, 'pen.jpg', 1, 0);

1 row created.

SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath,soldout, Promotion)
  2  VALUES ('Ruler', 'Long',14.99, 'ruler.jpg', 0, 0);

1 row created.

SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
  2  VALUES ('Desk', 'Computer Desk',5.99, 'desk.jpg', 0, 1);

1 row created.

SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
  2  VALUES ('PC', 'Notebook',49.99, 'pc.jpg', 0, 1);

1 row created.

SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
  2  VALUES ('Mouse', 'Wireless',9.99, 'mouse.jpg',  1, 0);

1 row created.

SQL>
SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
  2  VALUES ('Keyboard', 'keyboard',3.75, 'keyboard.jpg', 0, 0);

1 row created.

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 C.Name as "Category Name", P.Name as "Product Name"
  2  FROM Product P
  3  INNER JOIN ProductCategory PC ON P.ProductID = PC.ProductID
  4  INNER JOIN Category C ON PC.CategoryID = C.CategoryID
  5  ORDER BY C.Name, P.Name;

Category Name
--------------------------------------------------
Product Name
--------------------------------------------------
Local
PC

Local
Ruler

Masks
Desk

Masks
Keyboard

Masks
Pen

Masks
Ruler

Remote
Mouse

Wireless
Keyboard


8 rows selected.

SQL>
SQL>
SQL>
SQL> drop table Product;

Table dropped.

SQL> drop table ProductCategory;

Table dropped.

SQL> drop table Category;

Table dropped.

SQL> drop sequence CategoryIDSeq;

Sequence dropped.

SQL> drop sequence ProductIDSeq;

Sequence dropped.








Related examples in the same category

1.The query shows that the join is performed with the other WHERE conditions
2.Adding an Analytical Function to a Query that Contains a Join (and Other WHERE Conditions)
3.Used a GROUP BY in a query with no ordering or analytical function
4.Adding Ordering to the Query Containing the GROUP BY
5.Supplying Table Aliases
6.Add the USING clause
7.Join table using
8.Join with a subquery
9.Get Categories and Products (with Alternate Join Syntax)
10.Joining table to use between ... and clause
11.Nested Multi-Table Equi-Joins
12.How Many Featured Products By Department with JOINs
13.How Many Products By Department with JOINs
14.Non-Equi Joins