How Many Products By Department with JOINs

    
SQL>
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 Department.Name AS "Department",
  2         COUNT(Product.Name) AS "Products"
  3  FROM Product
  4     INNER JOIN ProductCategory
  5     ON Product.ProductID = ProductCategory.ProductID
  6     INNER JOIN Category
  7     ON ProductCategory.CategoryID = Category.CategoryID
  8     INNER JOIN Department
  9     ON Category.DepartmentID = Department.DepartmentID
 10  GROUP BY Department.Name
 11  ORDER BY Department.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;

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.	Get Categories and Products (with Joins)
11.	Joining table to use between ... and clause
12.	Nested Multi-Table Equi-Joins
13.	How Many Featured Products By Department with JOINs
14.	Non-Equi Joins

How Many Products By Department with JOINs : Table Join Basics « Table Joins « Oracle PL / SQL

Related examples in the same category