Scribd
Upload
7 views3 pages

SQL Queries for Item and Customer Data

The document contains a series of SQL queries designed to retrieve specific information from a database, including item costs, unique descriptions, employee details, vendor emails, sales data, and customer information. It also includes sorting and filtering operations based on various criteria such as price, names, and locations. The queries demonstrate fundamental SQL operations like SELECT, DISTINCT, ORDER BY, and WHERE clauses.

Uploaded by

thangvietta2003
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
7 views3 pages

SQL Queries for Item and Customer Data

The document contains a series of SQL queries designed to retrieve specific information from a database, including item costs, unique descriptions, employee details, vendor emails, sales data, and customer information. It also includes sorting and filtering operations based on various criteria such as price, names, and locations. The queries demonstrate fundamental SQL operations like SELECT, DISTINCT, ORDER BY, and WHERE clauses.

Uploaded by

thangvietta2003
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

Student Name: ASG-2: SQL

Student ID: SELECT Statement, Sorting and Filtering, Expressions and Functions

1. Find the cost and the price of each item.

SELECT ItemCost, ItemPrice


FROM item;

2. Find the unique item description(s) in the ITEM table

SELECT DISTINCT ItemDescription


FROM item;

3. Find the email addresses, last name, and first name of each employee.

SELECT LastName, FirstName, EmailAddress


FROM employee;

4. Find the email addresses of all vendors of the store.

SELECT EmailAddress
FROM vendor;

5. Find the CustomerID and EmployeeID in the SALE table.

SELECT CustomerID, EmployeeID


FROM sale;

6. Find the list of the dates in which the store has (at least) a sale.

SELECT DISTINCT SaleDate


FROM sale;

7. Sort CUSTOMERS by their first names (just return customers first name column).

SELECT FirstName
FROM customer
ORDER BY FirstName

1
8. Sort CUSTOMERS table by customers first names (return all columns).

SELECT *
FROM customer
ORDER BY FirstName

9. Sort CUSTOMERS by their first names and just return customers last name column.

SELECT LastName
FROM customer
ORDER BY FirstName

10. Sort CUSTOMERS table by customers’ addresses, city, and ZIP (return all columns).

SELECT *
FROM customer
ORDER BY Address, City, ZIP;

11. Sort the items by their price from most expensive to the least expensive (return all columns).

SELECT *
FROM item
ORDER BY ItemPrice DESC;

12. Return the item description of the 5 most expensive items.

SELECT ItemDescription
FROM item
ORDER BY ItemPrice DESC LIMIT 5;

13. Find all customers from Bellevue.

SELECT *
FROM customer
WHERE City = ‘Bellevue’;

14. Return all items that are more expensive than $500.

SELECT *
FROM item
WHERE ItemPrice > 500;

2
15. Return all vendors except for European Specialties.

SELECT *
FROM vendor
WHERE CompanyName != ‘European Specialties’;

16. Find all the sales before 1/5/2019

SELECT *
FROM sale
WHERE SaleDate < ‘2019-01-05’;

17. Find all the items whose descriptions start with “Dining Table”.

SELECT *
FROM item
WHERE ItemDescription LIKE ‘Dining Table%’;

18. Retrieve all the items that has the margin = price - cost higher than $300.

SELECT *
FROM item
WHERE ItemPrice - ItemCost > 300;

19. Find the total sales done by employee id = 1.

SELECT SUM(Total) AS TotalSale


FROM sale
WHERE EmployeeID = 1;

20. Find the average cost of all items whose description contains ”Antique”

SELECT AVG(ItemCost)
FROM item
WHERE ItemDescription LIKE ‘%Antique%’;

You might also like