Scribd
Upload
2 views3 pages

SQL Test 3

The document outlines the creation of three SQL tables: Customers, Products, and Orders, along with sample data inserts. It includes a series of queries and stored procedure requirements for retrieving order details, calculating totals, and summarizing customer activity. Additionally, it poses questions regarding SQL clauses such as WHERE, HAVING, and GROUP BY.

Uploaded by

Digication Institute
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
2 views3 pages

SQL Test 3

The document outlines the creation of three SQL tables: Customers, Products, and Orders, along with sample data inserts. It includes a series of queries and stored procedure requirements for retrieving order details, calculating totals, and summarizing customer activity. Additionally, it poses questions regarding SQL clauses such as WHERE, HAVING, and GROUP BY.

Uploaded by

Digication Institute
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

SQL Test – 3

-- Customers Table

CREATE TABLE Customers (

CustomerID INT PRIMARY KEY,

CustomerName VARCHAR(100),

City VARCHAR(50),

Country VARCHAR(50)

);

INSERT INTO Customers VALUES

(1, 'Amit Kumar', 'Delhi', 'India'),

(2, 'Sara Wilson', 'London', 'UK'),

(3, 'Michael Brown', 'New York', 'USA'),

(4, 'Neha Verma', 'Pune', 'India'),

(5, 'Daniel Chen', 'Vancouver', 'Canada'),

(6, 'Riya Mehta', 'Ahmedabad', 'India');

-- Products Table—

CREATE TABLE Products (

ProductID INT PRIMARY KEY,

ProductName VARCHAR(100),

Category VARCHAR(50),

Price DECIMAL(10,2)

);

INSERT INTO Products VALUES

(201, 'Laptop', 'Electronics', 75000),

(202, 'Smartphone', 'Electronics', 30000),


(203, 'Table', 'Furniture', 12000),

(204, 'Headphones', 'Accessories', 5000),

(205, 'Office Chair', 'Furniture', 9000);

-- Orders Table—

CREATE TABLE Orders (

OrderID INT PRIMARY KEY,

CustomerID INT,

ProductID INT,

Quantity INT,

OrderDate DATE,

FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),

FOREIGN KEY (ProductID) REFERENCES Products(ProductID)

);

INSERT INTO Orders VALUES

(3001, 1, 201, 1, '2024-08-10'),

(3002, 2, 202, 2, '2024-09-15'),

(3003, 3, 203, 1, '2024-09-20'),

(3004, 1, 204, 3, '2024-10-05'),

(3005, 4, 201, 2, '2024-10-08'),

(3006, 5, 205, 1, '2024-10-12');

Q1.

Display all orders along with customer name and product name.

Q2.

Calculate the total amount for each order.

Q3.

Find the total spending by each customer.

Q4.
List all customers who purchased Furniture products.

Q5.

Display all orders placed in September 2024.

Q6.

Find the highest priced product that was ordered.

Q7.

Create a stored procedure sp_GetCustomerOrders

• Input: Customer Name

• Output: Order details of that customer

Q8.

Create a stored procedure sp_CategorySales

• Input: Category name

• Output: Total sales amount for that category

Q9.

Create a stored procedure sp_MonthlyOrderSummary

• Input: Month, Year

• Output: Total Orders, Total Sales

Q10.

Create a stored procedure sp_CustomersWithNoOrders

• Output: Customers who have never placed any order

Q11.

What is the difference between WHERE and HAVING clause?

Q12.

What is the use of GROUP BY clause?

You might also like