DBMS Lab — Complete SQL Guide
CS261 | Semester IV | All Questions Solved with Beginner-Friendly Explanations
■ Common Schema — Understanding the Tables
Think of these 4 tables like 4 notebooks that are connected to each other:
Table Columns What it stores
CUSTOMER CustomerID, Name, City Info about every customer
ORDERS OrderID, CustomerID Each order, linked to a customer
PRODUCT ProductID, ProductName List of all products
ORDER_DETAILS OrderID, ProductID Which product belongs to which order
■ PRIMARY KEY = unique ID for each row (like a roll number). FOREIGN KEY = a column that links
to another table's Primary Key.
■ Question 1 — Basic Queries + Inner Joins
1a Create the Tables with Primary Keys and Foreign Keys
We create all 4 tables. Notice how ORDERS has a FOREIGN KEY pointing to CUSTOMER, and
ORDER_DETAILS has two FOREIGN KEYS pointing to both ORDERS and PRODUCT.
-- 1. CUSTOMER table
CREATE TABLE CUSTOMER (
CustomerID INT PRIMARY KEY,
Name VARCHAR(50),
City VARCHAR(50)
);
-- 2. ORDERS table
-- CustomerID is a Foreign Key → links back to CUSTOMER
CREATE TABLE ORDERS (
OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES CUSTOMER(CustomerID)
);
-- 3. PRODUCT table
CREATE TABLE PRODUCT (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(50)
);
-- 4. ORDER_DETAILS table (links Orders to Products)
CREATE TABLE ORDER_DETAILS (
OrderID INT,
ProductID INT,
PRIMARY KEY (OrderID, ProductID),
FOREIGN KEY (OrderID) REFERENCES ORDERS(OrderID),
FOREIGN KEY (ProductID) REFERENCES PRODUCT(ProductID)
);
1b Insert at least 5 records (one customer with no orders, one product never ordered)
We insert 5 customers — Vikram (ID=5) is intentionally given NO orders. We insert 5 products — Monitor
(ID=5) is never added to any order.
-- 5 Customers (Vikram has NO orders)
INSERT INTO CUSTOMER VALUES (1, 'Arjun', 'Bangalore');
INSERT INTO CUSTOMER VALUES (2, 'Priya', 'Bangalore');
INSERT INTO CUSTOMER VALUES (3, 'Rahul', 'Mumbai');
INSERT INTO CUSTOMER VALUES (4, 'Sneha', 'Delhi');
INSERT INTO CUSTOMER VALUES (5, 'Vikram', 'Bangalore'); -- NO orders
-- 5 Orders (linked to customers 1-4)
INSERT INTO ORDERS VALUES (101, 1);
INSERT INTO ORDERS VALUES (102, 2);
INSERT INTO ORDERS VALUES (103, 3);
INSERT INTO ORDERS VALUES (104, 4);
INSERT INTO ORDERS VALUES (105, 1);
-- 5 Products (Monitor is NEVER ordered)
INSERT INTO PRODUCT VALUES (1, 'Laptop');
INSERT INTO PRODUCT VALUES (2, 'Phone');
INSERT INTO PRODUCT VALUES (3, 'Tablet');
INSERT INTO PRODUCT VALUES (4, 'Keyboard');
INSERT INTO PRODUCT VALUES (5, 'Monitor'); -- NEVER ordered
-- Order_Details (which product in which order)
INSERT INTO ORDER_DETAILS VALUES (101, 1);
INSERT INTO ORDER_DETAILS VALUES (102, 2);
INSERT INTO ORDER_DETAILS VALUES (103, 3);
INSERT INTO ORDER_DETAILS VALUES (104, 4);
INSERT INTO ORDER_DETAILS VALUES (105, 2);
1c Display all customers from Bangalore
Go into the CUSTOMER table and show only rows where City = 'Bangalore'. This is a simple WHERE filter
— no joins needed.
SELECT Name, City
FROM CUSTOMER
WHERE City = 'Bangalore';
-- Result: Arjun, Priya, Vikram
1d Customer Name and Order ID for all customers who placed orders
We need data from TWO tables: CUSTOMER (for Name) and ORDERS (for OrderID). We connect them
using INNER JOIN on CustomerID. Only customers who have orders appear — Vikram is excluded.
SELECT [Link], [Link]
FROM CUSTOMER C
INNER JOIN ORDERS O ON [Link] = [Link];
-- Vikram is NOT shown because he has no matching order
■ INNER JOIN = 'Show me only rows that MATCH on both sides.' If a customer has no orders, they
are left out completely.
1e Customer Name and Product Name for all customers who ordered products
Now we need 4 tables. Think of it as a chain of joins — like following a trail of breadcrumbs: Customer →
Orders → Order_Details → Product.
SELECT [Link], [Link]
FROM CUSTOMER C
INNER JOIN ORDERS O ON [Link] = [Link]
INNER JOIN ORDER_DETAILS OD ON [Link] = [Link]
INNER JOIN PRODUCT P ON [Link] = [Link];
-- Each INNER JOIN follows the Foreign Key link to the next table
1f Order ID and Product Name for all orders
Similar to 1e, but we start from ORDERS (not CUSTOMER) and join through ORDER_DETAILS to reach
PRODUCT.
SELECT [Link], [Link]
FROM ORDERS O
INNER JOIN ORDER_DETAILS OD ON [Link] = [Link]
INNER JOIN PRODUCT P ON [Link] = [Link];
1g Bangalore customers → Orders → Products (filter + 4-table join)
Same 4-table chain as 1e, but we add a WHERE clause at the end to filter only Bangalore customers. The
WHERE runs after all the joins.
SELECT [Link], [Link], [Link]
FROM CUSTOMER C
INNER JOIN ORDERS O ON [Link] = [Link]
INNER JOIN ORDER_DETAILS OD ON [Link] = [Link]
INNER JOIN PRODUCT P ON [Link] = [Link]
WHERE [Link] = 'Bangalore';
-- WHERE filters AFTER the joins are done
■ Question 2 — Outer Joins + Advanced Queries
■ KEY IDEA: INNER JOIN shows only MATCHING rows. OUTER JOINS show EVERYONE — even
if there is no match — the missing side gets NULL (blank/empty).
Join Type What it returns Clue in the question
LEFT JOIN ALL rows from left table + matches from right (NULL if "including
no match) customers with no orders"
RIGHT JOIN ALL rows from right table + matches from left (NULL if "including
no match) orders with no customer"
FULL OUTER JOIN ALL rows from BOTH tables (NULL where no match) "all customers AND all orders"
2a All customers and their orders (including customers with NO orders)
We want ALL customers — even Vikram who has no orders. Use LEFT JOIN: CUSTOMER is on the LEFT
so all its rows appear. Vikram will show with NULL in the OrderID column.
SELECT [Link], [Link]
FROM CUSTOMER C
LEFT JOIN ORDERS O ON [Link] = [Link];
-- Vikram appears with OrderID = NULL
2b All products including those never ordered
PRODUCT is on the LEFT, so all products appear. 'Monitor' (never added to ORDER_DETAILS) will show
with NULL in OrderID.
SELECT [Link], [Link]
FROM PRODUCT P
LEFT JOIN ORDER_DETAILS OD ON [Link] = [Link];
-- Monitor appears with OrderID = NULL
2c All orders along with product names (including orders with no products)
ORDERS is on the LEFT, so all orders show up. We LEFT JOIN twice to reach PRODUCT. If an order has
no product in ORDER_DETAILS, ProductName = NULL.
SELECT [Link], [Link]
FROM ORDERS O
LEFT JOIN ORDER_DETAILS OD ON [Link] = [Link]
LEFT JOIN PRODUCT P ON [Link] = [Link];
-- Orders with no product show ProductName = NULL
2d Customer Name, Order ID, Product Name for ALL customers (full LEFT chain)
This is the most complete query. We start from CUSTOMER and LEFT JOIN the entire chain. This means:
• Customers with no orders → NULL for OrderID and ProductName
• Orders with no products → NULL for ProductName
• Every customer is guaranteed to appear at least once
SELECT [Link], [Link], [Link]
FROM CUSTOMER C
LEFT JOIN ORDERS O ON [Link] = [Link]
LEFT JOIN ORDER_DETAILS OD ON [Link] = [Link]
LEFT JOIN PRODUCT P ON [Link] = [Link];
-- The most 'inclusive' query — nobody is left out
2e ALL customers AND ALL orders including unlinked ones (Full Outer Join)
We need rows from both sides even when there is no match. MySQL does not support FULL OUTER JOIN
directly, so we simulate it by combining a LEFT JOIN + RIGHT JOIN using UNION.
-- LEFT JOIN: All customers + their matching orders
SELECT [Link], [Link]
FROM CUSTOMER C
LEFT JOIN ORDERS O ON [Link] = [Link]
UNION
-- RIGHT JOIN: All orders + their matching customers
SELECT [Link], [Link]
FROM CUSTOMER C
RIGHT JOIN ORDERS O ON [Link] = [Link];
-- UNION merges both results and removes duplicates
-- Customers with no orders → show with NULL OrderID
-- Orders with no customer → show with NULL Name
■ UNION combines two SELECT results into one. It automatically removes duplicate rows. Use
UNION ALL if you want to keep duplicates.
■ Golden Rules — Remember These Always
Rule What to do
Question says 'only matched / who placed orders' → Use INNER JOIN
Question says 'including those with no orders' → Use LEFT JOIN (keep left table complete)
Question says 'including products never ordered' → Use LEFT JOIN with PRODUCT on the left
Question says 'all customers AND all orders' → Use FULL OUTER JOIN (simulate with LEFT + RIGHT + UNION in M
Need data from multiple tables → Chain multiple JOINs, one per link in the path
Need to filter after joining → Add WHERE clause at the end of the query
■ JOIN Quick Reference
JOIN Type Left Table Intersection Right Table
INNER JOIN Matched only ✓ Included Matched only
LEFT JOIN ALL rows ✓ Included Matched only
RIGHT JOIN Matched only ✓ Included ALL rows
FULL OUTER JOIN ALL rows ✓ Included ALL rows
LEFT EXCLUDING Non-match only ✗ Excluded Not shown
RIGHT EXCLUDING Not shown ✗ Excluded Non-match only
CROSS JOIN ALL rows N/A ALL rows × ALL
Apply these patterns to any exam question: find the tables you need → chain the joins → decide INNER vs OUTER
→ add WHERE if filtering is needed.