CREATE TABLE Store (
store_no SERIAL PRIMARY KEY,
store_name VARCHAR(100),
phone_no VARCHAR(20),
email VARCHAR(100),
fax_no VARCHAR(20),
street_no VARCHAR(20),
suburb VARCHAR(50),
state VARCHAR(50),
postcode VARCHAR(10),
manager_emp_no INT
);
CREATE TABLE Department (
dept_no SERIAL PRIMARY KEY,
dept_title VARCHAR(100),
phone_no VARCHAR(20),
email VARCHAR(100),
store_no INT,
supervisor_emp_no INT
);
CREATE TABLE Employee (
emp_no SERIAL PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
address VARCHAR(100),
mobile_no VARCHAR(20),
email VARCHAR(100),
TFN VARCHAR(20),
salary_type VARCHAR(10),
salary DECIMAL(10, 2),
hourly_rate DECIMAL(10, 2),
joining_date DATE,
dept_no INT
);
CREATE TABLE Payslip (
payslip_no SERIAL PRIMARY KEY,
hours_worked INT,
emp_no INT,
store_no INT,
gross_pay DECIMAL(10, 2)
);
CREATE TABLE Product (
prod_no SERIAL PRIMARY KEY,
prod_name VARCHAR(100),
brand VARCHAR(50),
description TEXT,
price DECIMAL(10, 2)
);
CREATE TABLE Supplier (
supplier_id SERIAL PRIMARY KEY,
supplier_name VARCHAR(100),
supplier_phone VARCHAR(20),
supplier_email VARCHAR(100)
);
CREATE TABLE Supply (
supplier_id INT,
prod_no INT,
supply_price DECIMAL(10, 2),
PRIMARY KEY (supplier_id, prod_no)
);
CREATE TABLE Inventory (
store_no INT,
prod_no INT,
quantity_available INT,
quantity_ordered INT,
PRIMARY KEY (store_no, prod_no)
);
CREATE TABLE Customer (
cust_no SERIAL PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
mobile_no VARCHAR(20),
address VARCHAR(100)
);
CREATE TABLE Orders (
order_no SERIAL PRIMARY KEY,
order_date DATE,
cust_no INT,
store_no INT
);
CREATE TABLE Order_Details (
order_no INT,
prod_no INT,
quantity INT,
PRIMARY KEY (order_no, prod_no)
);
ALTER TABLE Department
ADD CONSTRAINT fk_store_no FOREIGN KEY (store_no) REFERENCES Store(store_no),
ADD CONSTRAINT fk_supervisor_emp_no FOREIGN KEY (supervisor_emp_no) REFERENCES Employee(emp_no);
ALTER TABLE Employee
ADD CONSTRAINT fk_dept_no FOREIGN KEY (dept_no) REFERENCES Department(dept_no);
ALTER TABLE Payslip
ADD CONSTRAINT fk_emp_no FOREIGN KEY (emp_no) REFERENCES Employee(emp_no),
ADD CONSTRAINT fk_store_no FOREIGN KEY (store_no) REFERENCES Store(store_no);
ALTER TABLE Supply
ADD CONSTRAINT fk_supplier_id FOREIGN KEY (supplier_id) REFERENCES Supplier(supplier_id),
ADD CONSTRAINT fk_prod_no FOREIGN KEY (prod_no) REFERENCES Product(prod_no);
ALTER TABLE Inventory
ADD CONSTRAINT fk_store_no FOREIGN KEY (store_no) REFERENCES Store(store_no),
ADD CONSTRAINT fk_prod_no FOREIGN KEY (prod_no) REFERENCES Product(prod_no);
ALTER TABLE Orders
ADD CONSTRAINT fk_cust_no FOREIGN KEY (cust_no) REFERENCES Customer(cust_no),
ADD CONSTRAINT fk_store_no FOREIGN KEY (store_no) REFERENCES Store(store_no);
ALTER TABLE Order_Details
ADD CONSTRAINT fk_order_no FOREIGN KEY (order_no) REFERENCES Order(order_no),
ADD CONSTRAINT fk_prod_no FOREIGN KEY (prod_no) REFERENCES Product(prod_no);
-- Insert sample data into Store table
INSERT INTO Store (store_name, phone_no, email, fax_no, street_no, suburb, state, postcode, manager_emp_no)
VALUES
('Central Store', '1234567890', '[email protected]', '123-4567', '10', 'Central City', 'Central State', '12345', 1),
('North Store', '2345678901', '[email protected]', '234-5678', '20', 'North City', 'North State', '23456', 2),
('East Store', '3456789012', '[email protected]', '345-6789', '30', 'East City', 'East State', '34567', 3),
('South Store', '4567890123', '[email protected]', '456-7890', '40', 'South City', 'South State', '45678', 4),
('West Store', '5678901234', '[email protected]', '567-8901', '50', 'West City', 'West State', '56789', 5);
-- Insert sample data into Department table
INSERT INTO Department (dept_title, phone_no, email, store_no, supervisor_emp_no)
VALUES
('Electronics', '1234567890', '[email protected]', 1, 6),
('Clothing', '2345678901', '[email protected]', 2, 7),
('Grocery', '3456789012', '[email protected]', 3, 8),
('Toys', '4567890123', '[email protected]', 4, 9),
('Furniture', '5678901234', '[email protected]', 5, 10);
-- Insert sample data into Employee table
INSERT INTO Employee (first_name, last_name, address, mobile_no, email, TFN, salary_type, salary, hourly_rate, joining_date, dept_no)
VALUES
('John', 'Doe', '123 Main St', '1234567890', '[email protected]', '123-456-789', 'Full-time', 50000, NULL, '2020-01-01', 1),
('Jane', 'Smith', '456 Elm St', '2345678901', '[email protected]', '234-567-890', 'Part-time', NULL, 20, '2021-02-01', 2),
('Jim', 'Brown', '789 Oak St', '3456789012', '[email protected]', '345-678-901', 'Full-time', 55000, NULL, '2019-03-01', 3),
('Jake', 'White', '321 Pine St', '4567890123', '[email protected]', '456-789-012', 'Part-time', NULL, 25, '2022-04-01', 4),
('Jill', 'Black', '654 Cedar St', '5678901234', '[email protected]', '567-890-123', 'Full-time', 60000, NULL, '2020-05-01', 5);
-- Insert sample data into Payslip table
INSERT INTO Payslip (hours_worked, emp_no, store_no, gross_pay)
VALUES
(160, 1, 1, 4000),
(80, 2, 2, 1600),
(160, 3, 3, 4400),
(80, 4, 4, 2000),
(160, 5, 5, 4800);
-- Insert sample data into Product table
INSERT INTO Product (prod_name, brand, description, price)
VALUES
('Laptop', 'Dell', '15-inch laptop', 1000),
('Shirt', 'Nike', 'Cotton shirt', 50),
('Apple', 'Local Farm', 'Fresh apple', 2),
('Toy Car', 'Hot Wheels', 'Miniature car', 10),
('Sofa', 'Ikea', 'Comfortable sofa', 500);
-- Insert sample data into Supplier table
INSERT INTO Supplier (supplier_name, supplier_phone, supplier_email)
VALUES
('Tech Supplies', '1234567890', '[email protected]'),
('Clothing Co', '2345678901', '[email protected]'),
('Fresh Farm', '3456789012', '[email protected]'),
('Toy Distributors', '4567890123', '[email protected]'),
('Furniture World', '5678901234', '[email protected]');
-- Insert sample data into Supply table
INSERT INTO Supply (supplier_id, prod_no, supply_price)
VALUES
(1, 1, 900),
(2, 2, 40),
(3, 3, 1),
(4, 4, 8),
(5, 5, 450);
-- Insert sample data into Inventory table
INSERT INTO Inventory (store_no, prod_no, quantity_available, quantity_ordered)
VALUES
(1, 1, 50, 10),
(2, 2, 100, 20),
(3, 3, 200, 30),
(4, 4, 150, 25),
(5, 5, 30, 5);
-- Insert sample data into Customer table
INSERT INTO Customer (first_name, last_name, mobile_no, address)
VALUES
('Alice', 'Green', '1234567890', '123 Apple St'),
('Bob', 'Blue', '2345678901', '456 Berry St'),
('Charlie', 'Red', '3456789012', '789 Cherry St'),
('Diana', 'Yellow', '4567890123', '321 Date St'),
('Eve', 'Purple', '5678901234', '654 Fig St');
-- Insert sample data into Order table
INSERT INTO Orders (order_date, cust_no, store_no)
VALUES
('2023-01-01', 1, 1),
('2023-02-01', 2, 2),
('2023-03-01', 3, 3),
('2023-04-01', 4, 4),
('2023-05-01', 5, 5);
-- Insert sample data into Order_Details table
INSERT INTO Order_Details (order_no, prod_no, quantity)
VALUES
(1, 1, 1),
(2, 2, 2),
(3, 3, 3),
(4, 4, 4),
(5, 5, 5);
-- Query 1:
SELECT (first_name || ' ' || last_name) AS "Customer full name", phone_no, address
FROM Customer
ORDER BY cust_no DESC;
-- Query 2:
SELECT * FROM Product
WHERE price < 100;
-- Query 3:
SELECT od.prod_no, p.prod_name, od.quantity, p.price AS unit_price, (od.quantity * p.price) AS total_amount
FROM Order_Details od
JOIN Product p ON od.prod_no = p.prod_no
WHERE od.order_no = 1005;
-- Query 4:
SELECT store_name, (street_no || ' ' || suburb || ' ' || state || ' ' || postcode) AS full_address, fax_no
FROM Store
WHERE LOWER(street_no) LIKE '%george%';
-- Query 5:
SELECT order_no
FROM Order_Details
GROUP BY order_no
HAVING COUNT(DISTINCT prod_no) >= 2;
-- Query 6:
SELECT c.cust_no, (c.first_name || ' ' || c.last_name) AS name, c.address
FROM Customer c
LEFT JOIN Order o ON c.cust_no = o.cust_no
WHERE o.order_no IS NULL;
-- Query 7:
SELECT e.first_name, e.last_name, e.emp_no, d.dept_title, s.store_name
FROM Employee e
JOIN Department d ON e.dept_no = d.dept_no
JOIN Store s ON d.store_no = s.store_no;
-- Query 8:
SELECT s.store_name, COUNT(e.emp_no) AS num_employees
FROM Store s
JOIN Department d ON s.store_no = d.store_no
JOIN Employee e ON d.dept_no = e.dept_no
WHERE d.dept_title = 'Account'
GROUP BY s.store_name;
-- Query 9:
SELECT *
FROM Orders
WHERE order_date > '2017-06-30' AND order_date < '2018-07-01';
-- Query 10:
SELECT cust_no, COUNT(order_no) AS total_orders
FROM Orders
GROUP BY cust_no;
-- Query 11:
SELECT o.order_no, o.order_date, COUNT(od.prod_no) AS num_products
FROM Orders o
JOIN Order_Details od ON o.order_no = od.order_no
GROUP BY o.order_no, o.order_date
ORDER BY o.order_no DESC;
Write, Run & Share PostgreSQL queries online using OneCompiler's PostgreSQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for PostgreSQL. Getting started with the OneCompiler's PostgreSQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose database as 'PostgreSQL' and start writing queries to learn and test online without worrying about tedious process of installation.
PostgreSQL is a open source relational database system and is also knows as Postgres.
CREATE command is used to create a table, schema or an index.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
....);
ALTER command is used to add, modify or delete columns or constraints from the database table.
ALTER TABLE Table_name ADD column_name datatype;
TRUNCATE command is used to delete the data present in the table but this will not delete the table.
TRUNCATE table table_name;
DROP command is used to delete the table along with its data.
DROP TABLE table_name;
RENAME command is used to rename the table name.
ALTER TABLE table_name1 RENAME to new_table_name1;
INSERT Statement is used to insert new records into the database table.
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
Select statement is used to select data from database tables.
SELECT column1, column2, ...
FROM table_name;
UPDATE statement is used to modify the existing values of records present in the database table.
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
DELETE statement is used to delete the existing records present in the database table.
DELETE FROM table_name where condition;