Introduction to SQL and Advanced Functions
Question 1
Explain the fundamental differences between DDL, DML, and DQL commands in SQL. Provide
one example for each type of command.
Answer:
DDL (Data Definition Language) commands are used to define and manage database structures such as
tables, schemas, and indexes. Examples include CREATE, ALTER, and DROP.
Example:
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(50)
);
DML (Data Manipulation Language) commands are used to insert, update, and delete data from tables.
Example:
INSERT INTO Students VALUES (1, 'Shubham');
DQL (Data Query Language) commands are used to retrieve data from the database.
Example:
SELECT * FROM Students;
Question 2
What is the purpose of SQL constraints? Name and describe three common types of constraints.
Answer:
SQL constraints are rules applied to table columns to maintain data accuracy and integrity.
1. PRIMARY KEY
○ Ensures unique identification of each record.
○ Example: CustomerID.
2. NOT NULL
○ Prevents a column from containing NULL values.
○ Example: CustomerName must always have a value.
3. UNIQUE
○ Ensures all values in a column are different.
○ Example: Email addresses should be unique.
Constraints help prevent invalid or duplicate data from entering the database.
Question 3
Explain the difference between LIMIT and OFFSET clauses in SQL.
Answer:
LIMIT specifies the maximum number of records to return.
OFFSET specifies how many records should be skipped before returning results.
To retrieve the third page when each page contains 10 records:
SELECT *
FROM Products
LIMIT 10 OFFSET 20;
The query skips the first 20 records and returns the next 10 records.
Question 4
What is a Common Table Expression (CTE) in SQL?
Answer:
A Common Table Expression (CTE) is a temporary result set defined using the WITH clause. It improves
query readability and simplifies complex SQL statements.
Example:
WITH HighPriceProducts AS (
SELECT ProductName, Price
FROM Products
WHERE Price > 100
)
SELECT *
FROM HighPriceProducts;
Benefits:
● Improves readability.
● Simplifies complex queries.
● Can be reused within the same query.
Question 5
Describe SQL Normalization and the first three normal forms.
Answer:
Normalization is the process of organizing data in a database to reduce redundancy and improve data
integrity.
First Normal Form (1NF)
● Each column contains atomic values.
● No repeating groups.
Second Normal Form (2NF)
● Must be in 1NF.
● All non-key attributes depend on the entire primary key.
Third Normal Form (3NF)
● Must be in 2NF.
● No transitive dependency.
Normalization improves database efficiency and consistency.
Question 6
Create Database
CREATE DATABASE ECommerceDB;
USE ECommerceDB;
Create Categories Table
CREATE TABLE Categories (
CategoryID INT PRIMARY KEY,
CategoryName VARCHAR(50) NOT NULL UNIQUE
);
Create Products Table
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(100) NOT NULL UNIQUE,
CategoryID INT,
Price DECIMAL(10,2) NOT NULL,
StockQuantity INT,
FOREIGN KEY (CategoryID)
REFERENCES Categories(CategoryID)
);
Create Customers Table
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE,
JoinDate DATE
);
Create Orders Table
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE NOT NULL,
TotalAmount DECIMAL(10,2),
FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID)
);
Insert Categories
INSERT INTO Categories VALUES
(1,'Electronics'),
(2,'Books'),
(3,'Home Goods'),
(4,'Apparel');
Insert Products
INSERT INTO Products VALUES
(101,'Laptop Pro',1,1200.00,50),
(102,'SQL Handbook',2,45.50,200),
(103,'Smart Speaker',1,99.99,150),
(104,'Coffee Maker',3,75.00,80),
(105,'Novel : The Great SQL',2,25.00,120),
(106,'Wireless Earbuds',1,150.00,100),
(107,'Blender X',3,120.00,60),
(108,'T-Shirt Casual',4,20.00,300);
Insert Customers
INSERT INTO Customers VALUES
(1,'Alice Wonderland','alice@[Link]','2023-01-10'),
(2,'Bob the Builder','bob@[Link]','2022-11-25'),
(3,'Charlie Chaplin','charlie@[Link]','2023-03-01'),
(4,'Diana Prince','diana@[Link]','2021-04-26');
Insert Orders
INSERT INTO Orders VALUES
(1001,1,'2023-04-26',1245.50),
(1002,2,'2023-10-12',99.99),
(1003,1,'2023-07-01',145.00),
(1004,3,'2023-01-14',150.00),
(1005,2,'2023-09-24',120.00),
(1006,1,'2023-06-19',20.00);
Question 7
SELECT
[Link],
[Link],
COUNT([Link]) AS TotalNumberOfOrders
FROM Customers c
LEFT JOIN Orders o
ON [Link] = [Link]
GROUP BY [Link], [Link], [Link]
ORDER BY [Link];
Question 8
SELECT
[Link],
[Link],
[Link],
[Link]
FROM Products p
JOIN Categories c
ON [Link] = [Link]
ORDER BY [Link], [Link];
Question 9
WITH ProductRank AS
(
SELECT
[Link],
[Link],
[Link],
ROW_NUMBER() OVER
(
PARTITION BY [Link]
ORDER BY [Link] DESC
) AS RankNo
FROM Products p
JOIN Categories c
ON [Link] = [Link]
)
SELECT
CategoryName,
ProductName,
Price
FROM ProductRank
WHERE RankNo <= 2;
Question 10 (Sakila Database Queries)
1. Top 5 Customers by Total Spending
SELECT
CONCAT(c.first_name,' ',c.last_name) AS CustomerName,
[Link],
SUM([Link]) AS TotalSpent
FROM customer c
JOIN payment p
ON c.customer_id = p.customer_id
GROUP BY c.customer_id
ORDER BY TotalSpent DESC
LIMIT 5;
2. Top 3 Movie Categories by Rentals
SELECT
[Link] AS CategoryName,
COUNT(*) AS RentalCount
FROM rental r
JOIN inventory i ON r.inventory_id=i.inventory_id
JOIN film_category fc ON i.film_id=fc.film_id
JOIN category cat ON fc.category_id=cat.category_id
GROUP BY [Link]
ORDER BY RentalCount DESC
LIMIT 3;
3. Films Available at Each Store and Never Rented
SELECT
store_id,
COUNT(*) AS TotalFilms,
SUM(
CASE
WHEN inventory_id NOT IN
(
SELECT inventory_id FROM rental
)
THEN 1
ELSE 0
END
) AS NeverRented
FROM inventory
GROUP BY store_id;
4. Monthly Revenue
SELECT
MONTH(payment_date) AS MonthNo,
SUM(amount) AS Revenue
FROM payment
WHERE YEAR(payment_date)=2023
GROUP BY MONTH(payment_date)
ORDER BY MonthNo;
5. Customers Renting More Than 10 Times
SELECT
c.customer_id,
CONCAT(c.first_name,' ',c.last_name) AS CustomerName,
COUNT(r.rental_id) AS RentalCount
FROM customer c
JOIN rental r
ON c.customer_id=r.customer_id
WHERE r.rental_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
GROUP BY c.customer_id
HAVING COUNT(r.rental_id) > 10;