How Many Products By Department with correlated subqueries 2 : Correlated Subquery « Query Select « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Query Select
3. Correlated Subquery

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;