Scribd
Upload
3 views11 pages

Database Query Exercises and Overview

The lesson plan outlines a session on databases, including an introduction to the Sakila database and a review of cardinality and participation constraints. It features workshops on Module 3 queries and a query competition, alongside exercises related to the FarmersMarket and classicmodels databases. Each exercise involves writing queries to extract specific datasets from the databases.

Uploaded by

esp115215
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
3 views11 pages

Database Query Exercises and Overview

The lesson plan outlines a session on databases, including an introduction to the Sakila database and a review of cardinality and participation constraints. It features workshops on Module 3 queries and a query competition, alongside exercises related to the FarmersMarket and classicmodels databases. Each exercise involves writing queries to extract specific datasets from the databases.

Uploaded by

esp115215
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

Lesson plan

Topic Time

Introducing sakila DB 20 min.

Revising cardinality ratio and 40 min.

participation constraints

Workshop on Module 3 queries 45 min.

The query competition 15 min.

Hany Osman, UNFC 1


FarmersMarket DB, no cardinality and participation details

The FarmersMarket database


involves eleven tables. Ten of
them are shown in the ER diagram
given below.

The database records information


about vendors, products, booth
assignment to vendors,
customers, market date and
purchases.

Hany Osman, UNFC 2


FarmersMarket DB, with cardinality and participation details

Hany Osman, UNFC 3


Exploring the classicmodels database
The classicmodels* database is a retailer of scale models of classic cars. It contains typical
business data, including information about customers, products, sales orders, sales order
line items, and more.

The classicmodels sample database schema consists of the following tables:


 customers: stores customer’s data.
 products: stores a list of scale model cars.
 productlines: stores a list of product lines.
 orders: stores sales orders placed by customers.
 orderdetails: stores sales order line items for every sales order.
 payments: stores payments made by customers based on their accounts.
 employees: stores employee information and the organization structure such as who reports to
whom.
 offices: stores sales office data.

*[Link]
Hany Osman, UNFC 4
Classicmodels DB, with no cardinality and participation details

Hany Osman, UNFC 5


Classicmodels DB, with cardinality and participation details

Hany Osman, UNFC 6


Exercise 1

Prepare a dataset that shows details of the products considered in the classicmodels database.
The required details are the product name, product line, product vendor, and product description

Hany Osman, UNFC 7


Exercise 2
In the classicmodels database, it is required to check whether there is an outlier in the payment
amounts and identify which customer had made this payment.

Write a query that prepares the dataset needed to run this investigations.

Hany Osman, UNFC 8


Exercise 3
In the farmers_market database, it is required to study the price of products, rounded to the
closet dollar, at each vendor along with the quantity available.

Write a query that prepares the dataset needed to conduct this study.

Hany Osman, UNFC 9


Exercise 4
In the classicmodels database, it is required to provide the following list.

Customer_name Full_address

Write a query that prepares the dataset needed to provide this list.

Hany Osman, UNFC 10


Exercise 5
Write the query used to retrieves the following dataset needed from the sakila database

Hany Osman, UNFC 11

You might also like