Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / Query Select

 

How Many Products By Department with correlated subqueries 1

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> 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> 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> CREATE TABLE ProductCategory (   2  ProductID INT NOT NULL,   3  CategoryID INT NOT NULL,   4  PRIMARY KEY (ProductID, CategoryID)   5  ); 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", 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> 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;