1. Create a table Students with columns Student_ID, Name, and Marks.
Calculate the average marks of students and retrieve students
scoring above average.
Ans
-- Step 1: Create the Students table
CREATE TABLE Students (
Student_ID INT PRIMARY KEY,
Name VARCHAR(100),
Marks INT
);
-- Step 2: Insert sample data into the Students table
INSERT INTO Students (Student_ID, Name, Marks) VALUES
(1, 'Alice', 85),
(2, 'Bob', 75),
(3, 'Charlie', 95),
(4, 'David', 65),
(5, 'Eva', 80);
-- Step 3: Calculate the average marks
SELECT AVG(Marks) AS Average_Marks FROM Students;
-- Step 4: Retrieve students scoring above the average marks
SELECT *
FROM Students
WHERE Marks > (SELECT AVG(Marks) FROM Students);
Output of 1
2. Create tables Customers and Orders for an e-commerce platform.
Insert data and retrieve customer orders totaling over $100.
Ans
-- Step 1: Create the Customers table
CREATE TABLE Customers (
Customer_ID INT PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(100)
);
-- Step 2: Create the Orders table
CREATE TABLE Orders (
Order_ID INT PRIMARY KEY,
Customer_ID INT,
Order_Total DECIMAL(10, 2),
Order_Date DATE,
FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID)
);
-- Step 3: Insert data into the Customers table
INSERT INTO Customers (Customer_ID, Name, Email) VALUES
(1, 'Alice', 'alice@[Link]'),
(2, 'Bob', 'bob@[Link]'),
(3, 'Charlie', 'charlie@[Link]');
-- Step 4: Insert data into the Orders table
INSERT INTO Orders (Order_ID, Customer_ID, Order_Total, Order_Date)
VALUES
(101, 1, 150.00, '2024-12-01'),
(102, 2, 50.00, '2024-12-02'),
(103, 1, 200.00, '2024-12-03'),
(104, 3, 75.00, '2024-12-04'),
(105, 2, 120.00, '2024-12-05');
-- Step 5: Retrieve customer orders totaling over $100
SELECT
[Link] AS Customer_Name,
[Link] AS Customer_Email,
O.Order_ID,
O.Order_Total,
O.Order_Date
FROM
Customers C
JOIN
Orders O
ON
C.Customer_ID = O.Customer_ID
WHERE
O.Order_Total > 100;
Output of 2
3. Create a table Products with columns Product_ID, Name, Price, and
Category. Retrieve products priced between $50-$100.
Ans
-- Step 1: Create the Products table
CREATE TABLE Products (
Product_ID INT PRIMARY KEY,
Name VARCHAR(100),
Price DECIMAL(10, 2),
Category VARCHAR(50)
);
-- Step 2: Insert data into the Products table
INSERT INTO Products (Product_ID, Name, Price, Category) VALUES
(1, 'Wireless Mouse', 25.99, 'Electronics'),
(2, 'Bluetooth Speaker', 65.50, 'Electronics'),
(3, 'Office Chair', 95.00, 'Furniture'),
(4, 'Table Lamp', 45.00, 'Home Decor'),
(5, 'Running Shoes', 85.99, 'Sports');
-- Step 3: Retrieve products priced between $50 and $100
SELECT *
FROM Products
WHERE Price BETWEEN 50 AND 100;
Output of 3
4. Create a table called Flights with columns Flight_ID, Departure,
Arrival, and Fare. Retrieve flights departing after 6 PM.
Ans
-- Step 1: Create the Flights table
CREATE TABLE Flights (
Flight_ID INT PRIMARY KEY,
Departure TIME,
Arrival TIME,
Fare DECIMAL(10, 2)
);
-- Step 2: Insert data into the Flights table
INSERT INTO Flights (Flight_ID, Departure, Arrival, Fare) VALUES
(1, '[Link]', '[Link]', 150.00),
(2, '[Link]', '[Link]', 200.00),
(3, '[Link]', '[Link]', 180.00),
(4, '[Link]', '[Link]', 120.00),
(5, '[Link]', '[Link]', 170.00);
-- Step 3: Retrieve flights departing after 6 PM
SELECT *
FROM Flights
WHERE Departure > '[Link]';
Output of 4
5. Create tables Teachers and Classes for a school management
system. Insert data and retrieve teachers assigned to more than two
classes.
Ans
-- Step 1: Create the Teachers table
CREATE TABLE Teachers (
Teacher_ID INT PRIMARY KEY,
Name VARCHAR(100),
Subject VARCHAR(50)
);
-- Step 2: Create the Classes table
CREATE TABLE Classes (
Class_ID INT PRIMARY KEY,
Class_Name VARCHAR(50),
Teacher_ID INT,
FOREIGN KEY (Teacher_ID) REFERENCES Teachers(Teacher_ID)
);
-- Step 3: Insert data into the Teachers table
INSERT INTO Teachers (Teacher_ID, Name, Subject) VALUES
(1, 'Alice', 'Mathematics'),
(2, 'Bob', 'Science'),
(3, 'Charlie', 'History');
-- Step 4: Insert data into the Classes table
INSERT INTO Classes (Class_ID, Class_Name, Teacher_ID) VALUES
(101, 'Grade 6', 1),
(102, 'Grade 7', 1),
(103, 'Grade 8', 1),
(104, 'Grade 6', 2),
(105, 'Grade 7', 2),
(106, 'Grade 8', 3);
-- Step 5: Retrieve teachers assigned to more than two classes
SELECT
T.Teacher_ID,
[Link],
[Link],
COUNT(C.Class_ID) AS Total_Classes
FROM
Teachers T
JOIN
Classes C
ON
T.Teacher_ID = C.Teacher_ID
GROUP BY
T.Teacher_ID, [Link], [Link]
HAVING
COUNT(C.Class_ID) > 2;
Output of 5
6. Order Management*
Create two tables, Orders and Customers.
Orders: - Order_ID (Primary Key) - Customer_ID (Foreign Key) -
Order_Date - Total_Amount
Customers: - Customer_ID (Primary Key) - Name - Email – Phone
Insert 5 customers and 10 orders. Retrieve orders for each
customer
Ans
-- Step 1: Create the Customers table
CREATE TABLE Customers (
Customer_ID INT PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(100),
Phone VARCHAR(20)
);
-- Step 2: Create the Orders table
CREATE TABLE Orders (
Order_ID INT PRIMARY KEY,
Customer_ID INT,
Order_Date DATE,
Total_Amount DECIMAL(10, 2),
FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID)
);
-- Step 3: Insert data into the Customers table
INSERT INTO Customers (Customer_ID, Name, Email, Phone) VALUES
(1, 'Alice', 'alice@[Link]', '123-456-7890'),
(2, 'Bob', 'bob@[Link]', '234-567-8901'),
(3, 'Charlie', 'charlie@[Link]', '345-678-9012'),
(4, 'David', 'david@[Link]', '456-789-0123'),
(5, 'Eva', 'eva@[Link]', '567-890-1234');
-- Step 4: Insert data into the Orders table
INSERT INTO Orders (Order_ID, Customer_ID, Order_Date, Total_Amount)
VALUES
(1, 1, '2024-12-01', 150.00),
(2, 1, '2024-12-03', 200.00),
(3, 2, '2024-12-02', 50.00),
(4, 2, '2024-12-04', 120.00),
(5, 3, '2024-12-05', 180.00),
(6, 3, '2024-12-06', 75.00),
(7, 4, '2024-12-07', 220.00),
(8, 4, '2024-12-08', 300.00),
(9, 5, '2024-12-09', 90.00),
(10, 5, '2024-12-10', 135.00);
-- Step 5: Retrieve orders for each customer
SELECT
[Link] AS Customer_Name,
[Link] AS Customer_Email,
O.Order_ID,
O.Order_Date,
O.Total_Amount
FROM
Customers C
JOIN
Orders O
ON
C.Customer_ID = O.Customer_ID
ORDER BY
[Link], O.Order_Date;
Output of 6
7. Employee Department* Create two tables, Employees and
Departments. Employees: - Employee_ID (Primary Key) -
Department_ID (Foreign Key) - Name – Salary
Departments: - Department_ID (Primary Key) - Department_Name -
Location Insert 5 departments and 10 employees.
Retrieve employees by department.
Ans
-- Step 1: Create the Departments table
CREATE TABLE Departments (
Department_ID INT PRIMARY KEY,
Department_Name VARCHAR(100),
Location VARCHAR(100)
);
-- Step 2: Create the Employees table
CREATE TABLE Employees (
Employee_ID INT PRIMARY KEY,
Department_ID INT,
Name VARCHAR(100),
Salary DECIMAL(10, 2),
FOREIGN KEY (Department_ID) REFERENCES
Departments(Department_ID)
);
-- Step 3: Insert data into the Departments table
INSERT INTO Departments (Department_ID, Department_Name, Location)
VALUES
(1, 'HR', 'New York'),
(2, 'Finance', 'Chicago'),
(3, 'Engineering', 'San Francisco'),
(4, 'Marketing', 'Los Angeles'),
(5, 'Sales', 'Boston');
-- Step 4: Insert data into the Employees table
INSERT INTO Employees (Employee_ID, Department_ID, Name, Salary)
VALUES
(1, 1, 'Alice', 50000.00),
(2, 1, 'Bob', 55000.00),
(3, 2, 'Charlie', 60000.00),
(4, 2, 'David', 65000.00),
(5, 3, 'Eva', 70000.00),
(6, 3, 'Frank', 75000.00),
(7, 4, 'Grace', 45000.00),
(8, 4, 'Hannah', 47000.00),
(9, 5, 'Ivy', 55000.00),
(10, 5, 'Jack', 60000.00);
-- Step 5: Retrieve employees by department
SELECT
D.Department_Name,
[Link] AS Employee_Name,
[Link]
FROM
Departments D
JOIN
Employees E
ON
D.Department_ID = E.Department_ID
ORDER BY
D.Department_Name, [Link];
Output of 7
8. Product Sales*
Create two tables, Products and Sales.
Products: - Product_ID (Primary Key) - Product_Name – Price
Sales: - Sale_ID (Primary Key) - Product_ID (Foreign Key) - Sale_Date
- Quantity Insert 5 products and 10 sales.
Retrieve total sales for each product.
Ans
-- Step 1: Create the Products table
CREATE TABLE Products (
Product_ID INT PRIMARY KEY,
Product_Name VARCHAR(100),
Price DECIMAL(10, 2)
);
-- Step 2: Create the Sales table
CREATE TABLE Sales (
Sale_ID INT PRIMARY KEY,
Product_ID INT,
Sale_Date DATE,
Quantity INT,
FOREIGN KEY (Product_ID) REFERENCES Products(Product_ID)
);
-- Step 3: Insert data into the Products table
INSERT INTO Products (Product_ID, Product_Name, Price) VALUES
(1, 'Laptop', 800.00),
(2, 'Smartphone', 500.00),
(3, 'Tablet', 300.00),
(4, 'Headphones', 150.00),
(5, 'Smartwatch', 200.00);
-- Step 4: Insert data into the Sales table
INSERT INTO Sales (Sale_ID, Product_ID, Sale_Date, Quantity) VALUES
(1, 1, '2024-12-01', 3),
(2, 2, '2024-12-02', 5),
(3, 3, '2024-12-03', 2),
(4, 4, '2024-12-04', 8),
(5, 5, '2024-12-05', 4),
(6, 1, '2024-12-06', 1),
(7, 2, '2024-12-07', 3),
(8, 3, '2024-12-08', 4),
(9, 4, '2024-12-09', 6),
(10, 5, '2024-12-10', 2);
-- Step 5: Retrieve total sales for each product
SELECT
P.Product_Name,
[Link],
SUM([Link]) AS Total_Quantity_Sold,
SUM([Link] * [Link]) AS Total_Sales_Amount
FROM
Products P
JOIN
Sales S
ON
P.Product_ID = S.Product_ID
GROUP BY
P.Product_Name, [Link]
ORDER BY
P.Product_Name;
Output of 8
9. Student Courses*
Create two tables, Students and Courses.
Students: - Student_ID (Primary Key) - Name – Department
Courses: - Course_ID (Primary Key) - Course_Name - Student_ID
(Foreign Key) Insert 5 students and 10 courses. Retrieve courses
enrolled by each student.
ans
-- Step 1: Create the Students table
CREATE TABLE Students (
Student_ID INT PRIMARY KEY,
Name VARCHAR(100),
Department VARCHAR(100)
);
-- Step 2: Create the Courses table
CREATE TABLE Courses (
Course_ID INT PRIMARY KEY,
Course_Name VARCHAR(100),
Student_ID INT,
FOREIGN KEY (Student_ID) REFERENCES Students(Student_ID)
);
-- Step 3: Insert data into the Students table
INSERT INTO Students (Student_ID, Name, Department) VALUES
(1, 'Alice', 'Computer Science'),
(2, 'Bob', 'Mathematics'),
(3, 'Charlie', 'Physics'),
(4, 'David', 'Chemistry'),
(5, 'Eva', 'Biology');
-- Step 4: Insert data into the Courses table
INSERT INTO Courses (Course_ID, Course_Name, Student_ID) VALUES
(1, 'Data Structures', 1),
(2, 'Algorithms', 1),
(3, 'Database Systems', 2),
(4, 'Linear Algebra', 2),
(5, 'Quantum Mechanics', 3),
(6, 'Electromagnetism', 3),
(7, 'Organic Chemistry', 4),
(8, 'Inorganic Chemistry', 4),
(9, 'Genetics', 5),
(10, 'Ecology', 5);
-- Step 5: Retrieve courses enrolled by each student
SELECT
[Link] AS Student_Name,
[Link] AS Student_Department,
C.Course_Name
FROM
Students S
JOIN
Courses C
ON
S.Student_ID = C.Student_ID
ORDER BY
[Link], C.Course_Name;
Output of 9
10. Hospital Patients*
Create two tables, Doctors and Patients.
Doctors: - Doctor_ID (Primary Key) - Name - Specialty
Patients: - Patient_ID (Primary Key) - Doctor_ID (Foreign Key) - Name
- Admission_Date
-- Step 1: Create the Doctors table
CREATE TABLE Doctors (
Doctor_ID INT PRIMARY KEY,
Name VARCHAR(100),
Specialty VARCHAR(100)
);
-- Step 2: Create the Patients table
CREATE TABLE Patients (
Patient_ID INT PRIMARY KEY,
Doctor_ID INT,
Name VARCHAR(100),
Admission_Date DATE,
FOREIGN KEY (Doctor_ID) REFERENCES Doctors(Doctor_ID)
);
-- Step 3: Insert data into the Doctors table
INSERT INTO Doctors (Doctor_ID, Name, Specialty) VALUES
(1, 'Dr. Smith', 'Cardiology'),
(2, 'Dr. Johnson', 'Neurology'),
(3, 'Dr. Williams', 'Orthopedics'),
(4, 'Dr. Brown', 'Pediatrics'),
(5, 'Dr. Davis', 'Dermatology');
-- Step 4: Insert data into the Patients table
INSERT INTO Patients (Patient_ID, Doctor_ID, Name, Admission_Date)
VALUES
(1, 1, 'Alice', '2024-12-01'),
(2, 1, 'Bob', '2024-12-02'),
(3, 2, 'Charlie', '2024-12-03'),
(4, 2, 'David', '2024-12-04'),
(5, 3, 'Eva', '2024-12-05'),
(6, 3, 'Frank', '2024-12-06'),
(7, 4, 'Grace', '2024-12-07'),
(8, 4, 'Hannah', '2024-12-08'),
(9, 5, 'Ivy', '2024-12-09'),
(10, 5, 'Jack', '2024-12-10');
-- Step 5: Retrieve patients assigned to each doctor
SELECT
[Link] AS Doctor_Name,
[Link] AS Doctor_Specialty,
[Link] AS Patient_Name,
P.Admission_Date
FROM
Doctors D
JOIN
Patients P
ON
D.Doctor_ID = P.Doctor_ID
ORDER BY
[Link], P.Admission_Date; Insert 5 doctors and 10 patients.
Retrieve patients assigned to each doctor
Output of 10