Scribd Logo
Upload

Welcome to Scribd!

  • 3 views

    Dbms

    Uploaded by

    Mariam shahzadi

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd

    Dbms

    Uploaded by

    Mariam shahzadi
    3 views4 pages

    Original Title

    dbms

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    3 views4 pages

    Dbms

    Uploaded by

    Mariam shahzadi

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 4

    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)

    You might also like