[SOLVED] Dbs211 lab 04 – multi-table queries and views

Objective:
The purpose of this lab is to introduce students to querying data from multiple tables. Relationships are
used in relational databases to reduce redundant and repetitive data, but it is necessary to reconnect
these tables when extracting data and obtaining information. Student will be able to:
• produce query results containing data from multiple tables using ANSI-92 joins and
demonstrate their knowledge of inner, outer and full joins.
• To actively troubleshoot queries to handle potentially ambiguous fields across multiple
tables through the use of aliases
• Students learn to create and modify views.Submission:
Your submission will be a single WORD file with the solutions provided.
Your submission needs to follow the same question order and clearly indicate the answers to
each question. Make sure every SQL statement terminates with a semicolon.
ALL questions must be answered using ANSI-92 JOINs unless otherwise stated. ANSI-89 are
obsolete and should not be used in new query derivations. We only teach them in case you see
them in the workplace, that you know what they are and how they work.Tasks:
Select data from multiple tables
1. Create a query that shows retail customers first name and last name along with their
sales rep employee number and their first name, last name, city, phone numberand postal
code for all retail customers who live in Singapore.
a. Answer this question using an ANSI-89 Join
b. Answer this question using an ANSI-92 Join2. Create a query that displays all retail payments made by retail customers from USA.
a. Sort the output by Customer Number.
b. Only display the Customer Number, Customer Name,Country, Payment
Date and Amount.1. Make sure the date is displayed clearly to know what date it is. (i.e. what date is
02-04-19??? – Feb 4, 2019, April 2, 2019, April 19, 2002, ….)
3. Create a query that shows all Canada customers who have made a payment. Display
only the customer number ,customer name, amount sorted by customer number.Views and Joins
4. Display all the retail orders with quantity ordered, price of each item, who have
their order shipped and who live in Denmark
5. Create a view (vwProductOrder) to list all the retail products with the following
data:
Product code, product name, msrp, buyprice, quantity ordered, and price for each
product in every order.
b) Write a statement to view the results of the view just created.6. Using the vwProductOrder view, display the product order information with product
name, buyprice ,order line number anwhose buy price is in the range from $30 to $40 and
whose product code starts with ‘s32’. Sort the output based on product name and then buy
price. (Hint: orderLineNumber is not in the view then how can you get in this query?)7. Create a query that displays the customer order information with customer number,
first name, last name, phone, and credit limits for all customers who do not have any
orders.8. Create a view (vwEmployeeManager) to display the information of all retail
employees first name and last name and their managers first name and managers
last name if there is any manager that the employee reports to. Include all
employees, including those who do not report to anyone.9. Modify the vwEmployeeManager view so the view returns only employee information
foremployees who have a manager. Do not DROP and recreate the view – modify it.
(Google is your friend).
10. Drop both vwProductOrder and vwEmployeeManager views.