Average Price Per Departments Having More Than 3 Products
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> 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>
SQL> SELECT Department.Name AS "Department",
2 COUNT(Product.Name) AS "Products",
3 AVG(Product.Price) AS "Average Price"
4 FROM Product
5 INNER JOIN ProductCategory
6 ON Product.ProductID = ProductCategory.ProductID
7 INNER JOIN Category
8 ON ProductCategory.CategoryID = Category.CategoryID
9 INNER JOIN Department
10 ON Category.DepartmentID = Department.DepartmentID
11 GROUP BY Department.Name
12 HAVING COUNT(Product.Price)>=3
13 ORDER BY AVG(Product.Price) DESC;
Software
3 24.99
Hardware
4 7.68
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