1)List the names and addresses of all customers.
Query:
SELECT CustomerName, CustomerAddress
FROM Customer_T
2)Get all orders placed in the last 30 days, showing only order
date and customer ID
Query:
SELECT OrderDate, CustomerID
FROM Order_T
WHERE OrderDate >= #10/10/2024# OR #10/11/2024-30#
In your SQL query, Date() represents the current date in Access. If today's date is, for example, November 9, 2024, then Date() would evaluate to
#11/9/2024#.
When you use Date() - 30, it calculates the date 30 days before the current date. So, if today's date is #11/9/2024#, then Date() - 30 would be
#10/10/2024#.
3) Retrieve the total price of each product in the order details by
multiplying quantity and unit price.
- Example: SELECT (Quantity * UnitPrice) AS TotalPrice FROM
OrderDetails.
Query:
SELECT Count(OrderedQuantity*ProductStandardPrice) AS TotalPrice
FROM OrderLine_T, Product_T
4) Show all products in a specific category, such as 'Living Room
Furniture', along with their prices
Query:
SELECT ProductDescription, ProductStandardPrice
FROM Product_T
5) Find the names of all employees in the 'Sales' department.
Query:
SELECT SalespersonName
From Salesperson_T
6) Count the total number of orders placed by each customer,
showing only the customer ID and the order count.
Query:
SELECT Count ( OrderID) AS OrderCount, OrderID
FROM OrderLine_T
GROUP BY OrderID
7) List all orders and display only the employee ID who processed
each order.
Query:
8) Retrieve all product names and IDs for products that are
currently in stock.
Query:
SELECT ProductID, ProductDescription
FROM Product_T
9) Get the names and IDs of customers who have placed orders
with a total quantity above 100.
Query:
10) Retrieve the average price of all products in a single table
without grouping by category.
- Example: SELECT AVG(Price) AS AveragePrice FROM Products;
Query:
SELECT AVG(ProductStandardPrice) AS AveragePrice
FROM Product_T
11) List the top 5 most expensive products by showing their
names and prices.
Query:
SELECT TOP 5 ProductDescription, ProductStandardPrice
FROM [Product_T]
ORDER BY ProductStandardPrice DESC
12) Get all order details where the quantity ordered is greater
than 10.
Query:
SELECT *
FROM OrderLine_T
WHERE OrderedQuantity > 10
13) Find the total revenue generated from all products by
summing the unit prices in the products table.
- Example: SELECT SUM(UnitPrice) AS TotalRevenue FROM
Products;
Query:
SELECT SUM(ProductStandardPrice) AS TotalRevenue
FROM Product_T
14) Show all customers who registered within the past year but
not within the last 6 months.
Query:
15) Get the employee IDs and salaries of the top 3 highest-paid
employees.
Query:
Salaries column is not provided
16) List all orders placed by customers in a specific city, showing
only the order date and customer ID.
Query:
17) Retrieve the total quantity ordered for a single product by
summing up quantities in the order details table.
- Example: SELECT SUM(Quantity) AS TotalQuantity FROM
OrderDetails WHERE ProductID = [specific product ID];
Query:
SELECT ProductID, SUM(OrderedQuantity) AS TotalQuantity
FROM OrderLine_T
WHERE ProductID = 1
GROUP BY ProductID
18) Find customers who have placed more than 10 orders,
showing only their customer IDs.
Query:
SELECT CustomerID
FROM Order_T
GROUP BY CustomerID
HAVING COUNT(OrderID) > 10
19) Get the total count of products available in the products
table.
Query:
SELECT COUNT(ProductID) AS TotalProducts
FROM Product_T
20) List all employees who do not have a department assigned by
checking for null values in the department field.
- Example: SELECT EmployeeID, Name FROM Employees WHERE
DepartmentID IS NULL.
Query:
SELECT EmployeeID, EmployeeName
FROM Employee_T
WHERE EmployeeSupervisor IS NULL
(Department wasn’t given in the tables)
