MICROSOFT ACCESS SQL exercise

USE THE NORTHWIND DATABASE THAT IS AVAILABLE IN THE SAMPLE TEMPLATES AREA OF THE MICROSOFT ACCESS APPLICATION

Exercises

1:

Select all columns and records from the Customers table.

2:

Select all columns and records for the StockItems table.

3:

Select the customer names, website URL and delivery city id for all of the customers.

4:

Select the Stock Item Name, .unit price and tax rate of records in the stock items table

5:

Select the full name, preferred name, email address and logon name from the people table.

6:

From the customers table, select the customer name, postal code as zip code, and delivery address line 2 as shipping address.

7:

Using the customers table, select only the unique Postal City IDs.

8:

Using the orders table, select only the distinct customer IDs.

9:

Select the product name and recommended retail price where the price is more than $40.

10:

Select the Customer name, website URL and phone number of customer whose name begins with K.

11:

Select the stock item name, typical weight per unit and search details where search details are between C and F inclusive.

12:

Select the stock item name, typical weight per unit and search details where search details are not between C and F inclusive.

13:

Determine the number orders placed in the last five years using November 7 as the day. Use single quotes around the date.

14:

Select the customer ID and order date for orders placed between March 15, 2016 and April 15 2016.

15:

Select the salespersonID, customer ID and order date for orders placed between in January 2017.

16:

Select the country and region from countries that are in Europe or Americas regions.

17:

Select the full name, preferred name and email address from people that are salespersons.

18:

Select the full name, preferred name and email address from people that are salespersons and permitted to logon.

19:

Select the country and sub region from countries that are in Asia regions sorted by subregion and country.

20:

Select the country and sub region from countries that are in Asia regions sorted by subregion and country.

21:

Select the stock item ID, bin location where reorder level is 50 sorted by bin location and stockItemID.

22:

Select the supplier name, delivery city id and supplier category id from suppliers sorted by suppliers name in reverse order (z before a) .

23:

Select the bin location, stock item id, quantity on hand and reorder level from the stock items holding table. Sort by bin location and quantity on hand

24:

Total the quantity sold for each product (description) using the order lines table .

25:

Total the quantity sold for each order using the order lines table .

26:

Total the quantity sold for each order using the order lines table only displaying orders with quantity greater than 250 units total.

27:

Find the average quantity of the order lines.

28:

Find the average unit price of the order lines.

29:

Count the number of orders in the orders table.

30:

Count the number of transactions by each customer on the CustomerTransactions table.