📦 Products Database — SQL Learning Guide
🔧 Create Table
CREATE TABLE products(
product_id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
sku_code CHAR(8) UNIQUE NOT NULL,
price NUMERIC(10,2) CHECK (price > 0),
stock_quantity INT DEFAULT 0 CHECK (stock_quantity >= 0),
is_available BOOLEAN DEFAULT TRUE,
category TEXT NOT NULL,
adden_on DATE DEFAULT CURRENT_DATE,
last_update TIMESTAMP DEFAULT NOW()
);
📟 Insert Sample Data
INSERT INTO products (name, sku_code, price , stock_quantity, is_available, category)
VALUES
('Wireless Mouse', 'WM123456', 699.99, 50, TRUE, 'Electronics'),
('Bluetooth Speaker', 'BS234567', 1499.00, 30, TRUE, 'Electronics'),
('Laptop Stand', 'LS345678', 799.50, 20, TRUE, 'Accessories'),
('USB-C Hub', 'UC456789', 1299.99, 15, TRUE, 'Accessories'),
('Notebook', 'NB567890', 99.99, 100, TRUE, 'Stationery'),
('Pen Set', 'PS678901', 199.00, 200, TRUE, 'Stationery'),
('Coffee Mug', 'CM789012', 299.00, 75, TRUE, 'Home & Kitchen'),
('LED Desk Lamp', 'DL890123', 899.00, 40, TRUE, 'Home & Kitchen'),
('Yoga Mat', 'YM901234', 499.00, 25, TRUE, 'Fitness'),
('Water Bottle', 'WB012345', 349.00, 60, TRUE, 'Fitness');
✏️ Basic Clause Practice Questions
Q1. Show the name and price of all products.
SELECT name, price FROM products;
Q2. Show all products where the category is 'Electronics'.
SELECT * FROM products WHERE category = 'Electronics';
Q3. Group products by category. Show each category once.
SELECT category FROM products GROUP BY category;
Q4. Show categories that have more than 1 product.
SELECT category, COUNT(*) FROM products
GROUP BY category
HAVING COUNT(*) > 1;
Q5. Show all products sorted by price in ascending order.
SELECT * FROM products ORDER BY price ASC;
Q6. Show only the first 3 products from the table.
SELECT * FROM products LIMIT 3;
Q7. Show product name as "Item_Name" and price as "Item_Price".
SELECT name AS Item_Name, price AS Item_Price FROM products;
Q8. Show all the unique categories from the products table.
SELECT DISTINCT category FROM products;
🎓 Test 2 Questions
Q1. Display the name and price of the cheapest product in the entire table.
SELECT name, price FROM products
WHERE price = (SELECT MIN(price) FROM products);
Q2. Find the average price of products that belong to 'Home & Kitchen' or
'Fitness'.
SELECT category, AVG(price) AS avg_price
FROM products
WHERE category IN ('Home & Kitchen', 'Fitness')
GROUP BY category;
Q3. Show product names and stock quantity where product is available, stock
> 50, and price != 299.
SELECT name, stock_quantity FROM products
WHERE is_available = TRUE
AND stock_quantity > 50
AND price != 299.00;
Q4. Find the most expensive product in each category.
SELECT category, MAX(price) AS max_price
FROM products
GROUP BY category;
Q5. Show all unique categories in uppercase, sorted in descending order.
SELECT DISTINCT UPPER(category) AS category_upper
FROM products
ORDER BY category_upper DESC;
👩🎓 One-to-One Relationship: Students & Profiles
Tables
CREATE TABLE students (
student_id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE student_profiles (
student_id INT PRIMARY KEY,
address TEXT,
age INT,
phone VARCHAR(15)
);
Insert Data
INSERT INTO students (name)
VALUES
('Akarsh Vyas'), ('Simran Mehta'), ('Rohan Gupta');
INSERT INTO student_profiles (student_id, address, age, phone)
VALUES
(1, 'Delhi, India', 22, '9999999999'),
(2, 'Mumbai, India', 21, '8888888888'),
(3, 'Bangalore, India', 23, '7777777777');
Foreign Key Constraint
ALTER TABLE student_profiles
ADD CONSTRAINT fk_student_id
FOREIGN KEY (student_id)
REFERENCES students(student_id);
Join Query
SELECT s.student_id, [Link], [Link], [Link], [Link]
FROM students s
JOIN student_profiles sp ON s.student_id = sp.student_id;
📈 One-to-Many Relationship: Students & Marks
Tables & Data
CREATE TABLE students (
student_id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE marks (
mark_id SERIAL PRIMARY KEY,
student_id INT,
subject VARCHAR(50),
marks INT,
FOREIGN KEY (student_id) REFERENCES students(student_id)
);
INSERT INTO students (name)
VALUES ('Akarsh Vyas'), ('Simran Mehta'), ('Rohan Gupta');
INSERT INTO marks (student_id, subject, marks)
VALUES
(1, 'English', 85), (1, 'Math', 89), (1, 'Science', 92),
(2, 'English', 80), (2, 'Math', 75), (2, 'Science', 78),
(3, 'English', 72), (3, 'Math', 70), (3, 'Science', 74);
📆 One-to-Many: Products & Orders. (Files are attached in
Github)
Tables
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
category TEXT,
price NUMERIC(10,2),
stock_quantity INT,
is_available BOOLEAN,
added_on DATE
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
product_id INT,
quantity INT,
order_date DATE,
customer_name VARCHAR(50),
payment_method VARCHAR(50),
CONSTRAINT fk_product FOREIGN KEY (product_id)
REFERENCES products(product_id) ON DELETE CASCADE
);
Sample Queries
Q1. Show each order along with the product name and price
SELECT o.order_id, o.customer_name, p.product_name, [Link]
FROM orders o
JOIN products p ON o.product_id = p.product_id;
Q2. Show all products even if they were never ordered
SELECT p.product_name, o.order_id
FROM products p
LEFT JOIN orders o ON p.product_id = o.product_id;
Q3. Show orders for only 'Electronics' category
SELECT o.order_id, p.product_name, [Link]
FROM orders o
JOIN products p ON o.product_id = p.product_id
WHERE [Link] = 'Electronics';
Q4. List all orders sorted by product price (high to low)
SELECT o.order_id, p.product_name, [Link]
FROM orders o
JOIN products p ON o.product_id = p.product_id
ORDER BY [Link] DESC;
Q5. Show number of orders placed for each product
SELECT p.product_name, COUNT(o.order_id) AS total_orders
FROM products p
LEFT JOIN orders o ON p.product_id = o.product_id
GROUP BY p.product_name;
Q6. Show total revenue earned per product
SELECT p.product_name, SUM([Link] * [Link]) AS revenue
FROM products p
JOIN orders o ON p.product_id = o.product_id
GROUP BY p.product_name;
Q7. Show products where total order revenue > ₹2000
SELECT p.product_name, SUM([Link] * [Link]) AS total_revenue
FROM products p
JOIN orders o ON p.product_id = o.product_id
GROUP BY p.product_name
HAVING SUM([Link] * [Link]) > 2000;
Q8. Show unique customers who ordered 'Fitness' products
SELECT DISTINCT o.customer_name
FROM orders o
JOIN products p ON o.product_id = p.product_id
WHERE [Link] = 'Fitness';
🌐 Many-to-Many: Students & Courses
Tables & Sample Data
CREATE TABLE students (
student_id INT PRIMARY KEY,
student_name VARCHAR(100)
);
CREATE TABLE courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(100)
);
CREATE TABLE student_courses (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
-- Sample Mapping
INSERT INTO student_courses (student_id, course_id) VALUES
(1, 101), (1, 102), (2, 101), (2, 103), (3, 102);
Queries
Q1. Show student and course names
SELECT s.student_name, c.course_name
FROM student_courses sc
JOIN students s ON sc.student_id = s.student_id
JOIN courses c ON sc.course_id = c.course_id;
Q2. List all courses taken by 'Simran'
SELECT c.course_name
FROM student_courses sc
JOIN students s ON sc.student_id = s.student_id
JOIN courses c ON sc.course_id = c.course_id
WHERE s.student_name = 'Simran';
📄 Views
View 1: Available Fitness Products
CREATE VIEW available_fitness_products AS
SELECT product_id, name, price, stock_quantity
FROM products
WHERE category = 'Fitness' AND is_available = TRUE;
View 2: Low Stock Products
CREATE VIEW low_stock_products AS
SELECT name, category, stock_quantity
FROM products
WHERE stock_quantity < 30;
⚖️ Stored Procedure
Add Product Procedure
CREATE PROCEDURE add_product(
p_name VARCHAR,
p_sku CHAR(8),
p_price NUMERIC,
p_qty INT,
p_category TEXT
)
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO products(name, sku_code, price, stock_quantity, category)
VALUES (p_name, p_sku, p_price, p_qty, p_category);
RAISE NOTICE 'Product added successfully!';
END;
$$;