Scribd Logo
Upload

Welcome to Scribd!

  • 9 views

    64561de777ea248b8d82aeac LAB3

    Uploaded by

    cvhai12022004

    Copyright:

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

    64561de777ea248b8d82aeac LAB3

    Uploaded by

    cvhai12022004
    9 views2 pages

    Original Title

    1729134984867_64561de777ea248b8d82aeac_LAB3

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    9 views2 pages

    64561de777ea248b8d82aeac LAB3

    Uploaded by

    cvhai12022004

    Copyright:

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

    DBI202 LAB3

    Use the W3Schools database and create the SQL query of the requirements
    below. Each question is placed in separate sql file, for example, Q1.sql, Q2.sql,
    Q10.sql, …., Q20.sql:

    0. Create Q0.sql to display the student's information

    SELECT 'He190001 Vu Van Huy' AS 'StudentName'

    1. List product name for all products, their supplier names, and product prices,
    sorted by price in descending order.

    2. Find all customers name who placed orders in January 1997, including
    information about the customer's name, order ID, and order date.

    3. Calculate the total revenue for each employee, including the employee's name
    and the total revenue (TotalRevenue) they handled.

    4. List the names of products that have never been ordered.

    5. Find the names of suppliers who have 5 or more products (NumberOfProducts)


    in the database.

    6. List all order IDs, customer names, employee names, and shipper names for
    orders placed in 1997.

    7. Calculate the average quantity of products (AvgQuantity) ordered in each order,


    sorted in descending order by the average quantity of products.

    8. Find the names and cities of all customers who live in the same city as at least
    two different suppliers.

    9. List orders where the total value exceeds 8000, including the order ID and total
    value (TotalValue).

    [email protected] 1/2
    10.Find the names of all products priced higher than the average price of all
    products in the database.

    11.List all orders where no product has been delivered yet.

    12.Find the countries that have more than two different suppliers
    (NumberOfSuppliers).

    13.Find the names of customers who have ordered products from at least three
    different suppliers (NumberOfSuppliers).

    14.List the names of suppliers and the number of products (NumberOfProducts)


    they provide.

    15.Find the names of products, product category IDs, and prices for the most
    expensive products in each category.

    16.List order IDs, product quantities (Numbers), and total amounts (TotalCost) for
    orders that contain 3 or more types of products and have a total value above
    5000.

    17.Find employees (*) whose birthdays are in the 3rd quarter.

    18.List countries that have only customers and no suppliers.

    19.Find the names and prices of products where the price is at least twice the
    average price of all products in the same product category.

    20.List information for all potential customers (those who have never placed an
    order), including customer ID, customer name, and country.

    [email protected] 2/2

    You might also like