Let$$s explore the data through new queries.

Q$5.$ Let$$s explore the data through new queries.

a$)$ Write a query that shows the product name, product buy price, and MSRP and give each column an appropriate alias.

SELECT productName AS Product$\_$Name, buyPrice AS Buy$\_$Price, MSRP AS Retail$\_$Price

FROM products;

b$)$ We will now create a calculated attribute. Using the last code $($Q$5$a$)$ and add a new column with the markup for each product $($see the formula below$).$ Give it an appropriate alias.

NOTE: You can create calculated columns by using parenthesis and typing your formula. Refer to the columns as if they were variables in your formula $($that is$,$ selling price would be the MSRP$,$ Cost would be the buyPrice$).$

Now we will create filters based on the calculated fields.

c$)$ Please add a filter so that we only see products $($rows$)$ with an MSRP higher than $100,$ and a markup higher than $100\%.$

Note: the alias used for the Markup in the SELECT statement cannot be referenced in the WHERE clause since the WHERE clause runs before the SELECT statement $($See page $2$ of this file to see the order of operations$).$ Therefore, you will need to repeat the formula for MARKUP in the where clause$).$

Working with Data in Multiple Tables

For the following questions, we will start using different forms of joins. All of the operations and functions we ran in the first $5$ questions can be directly applied to queries composed of multiple tables.

Q$6.$ Let$$s compare $2$ types of joins $($Equi$-$Join vs Inner Join$)$:

a$)$ The simplest type of join is an equi$-$join. Use an equi$-$join $($joining with equality in WHERE clause$)$ to get shared data between the $$products$$ and $$productlines$$ tables. Give an alias to each table so that we can differentiate between them with ease, and keep all columns.

NOTE: These $2$ tables are connected through the productLine. Note that your output will return $2$ copies of the productLine column as a result of the equijoin $(1$ from each table$).$

b$)$ Replicate the join in Q$6$a using an INNER Join instead of the WHERE clause.

NOTE: These $2$ tables are connected through the productLine. Note that your output will return $1$ copy of the productLine column since we used an INNER Join rather than an equi$-$join.

Q$7.$ Joining $3$ or more Tables: It is possible to use these same joins repeatedly to join $3$ or more tables. Just keep adding AND functions after the WHERE clause if using an equi$-$join, or use extra lines with INNER JOIN $[$next$\_$table$]$ ON $[$equality$].$ See example below:

a$)$ Use an equi$-$join to join the data from the tables productLines, products and order details. Display the $$textDescription$$ from the productlines table, the product$$s name and MSRP from the products table, and the order Number and quantity ordered from the orderdetails table.

b$)$ Use INNER JOINs to join the data from the tables productLines, products and order details. Display the $$textDescription$$ from the productlines table, the product$$s name and MSRP from the products table, and the order Number and quantity ordered from the orderdetails table.

Q$8.$ Outer Joins. Now let$$s practicing including all data from one of the tables included in our queries. We will repeat the same query for each situation just altering the type of outer query.

For each of the following lettered subsections, join the customer table with the orders table. Remember to:

$$ display all attributes

$$ use aliases for the tables.

$$ Record from which table each attribute is coming from when specifying how the tables will be joined.

a$)$ LEFT OUTER Join.

b$)$ RIGHT OUTER Join.

Q$9.$ Now we will create a self$-$join. In our model some employees report to other employees. Create a query that will allow you to see the first and last names of the employees, the employee$$s office code, the code of their manager, their manager$$s first and last name, and job title.

Q$10.$ Finally, create a view of the query in Q$9$ so that we can reuse it later on$.$