$$ In our Canvas Shell, go to $$CSD$-138$ Resources$\backslash$Book Resources $($including

scripts to setup our databases etc.$)$ to download and the file

create$\_$my$\_$guitar$\_$shop.sql

$$ Then to run the file

o Copy and paste the content of the file into MySQL Workbench and run it

o Or$,$ open the file in MySQL Workbench and run it

o This will create the schema $($i$.$e$.$ database$)$ titled my$\_$guitar$\_$shop

$$ Then use the schema, my$\_$guitar$\_$shop to answer each problem in this

Assignment

$$ Create a text file

o with a $.$sql file extension

o At the top of the script have

$$ use my$\_$guitar$\_$shop;

o In your sql file, for each problem

$$ In a commented section, write

$$ The problem number

$$ The problem statement

$$ followed next by your sql statement submission for the problem

$$ Note$->$ I only want to see your last version of your sql

statement for the problem

o No listing of previous intermediate sql work

$1.$ Select all data from the customers table

$2.$ Select First Name and Email Address

$3.$ Order result by First Name in Ascending order

$4.$ Show only first $5$ rows of the result

$5.$ Write query that will produce the following result for the people who have name Allan

Allan email address is allan.sherwood@yahoo.com

$6.$ Select all data from Order$\_$Items table such that item$\_$price $-$ discount$\_$amount $>$

$1000$

$7.$ Find products that have product name starting with "Gibson" and product code is

$"$sg$"$

$8.$ Find products with the following product codes : strat, sg$,$ washburn

$9.$ Find all product that don't have following product code: strat, sg$,$washburn

$10.$ Find all orders that have no ship date set

$11.$ Write a SQL statement that selects from Products table the following information

product$\_$name The product$\_$name column

list$\_$price The list$\_$price column

The two columns should be joined into $1$ column and result should looks like

Find all the products that contain word $$Gibson$.$

$12.$ Write a SQL statement that selects these columns from the Orders table:

order$\_$id The order$\_$id column

ship$\_$amount The ship$\_$amount column

tax$\_$amount The tax$\_$amount column

Round the ship$\_$amount and tax$\_$amount columns rounded to $0$ decimal places.

Return only the rows with a ship$\_$amount that is greater than $5$ and tax$\_$amount is

less than $80.$

$13.$ Write a SQL statement that selects these columns from the Products table:

product$\_$name The product$\_$name column

list$\_$price The list$\_$price column

discount$\_$percent The discount$\_$percent column

discount$\_$amount A column that$$s calculated from the previous two

columns

discount$\_$price A column that$$s calculated from the previous three

columns

Only return products with list$\_$price greater than $200$ and less than $800$

Sort it by product$\_$name in descending sequence.

$14.$ Write a SQL statement that selects these columns from the Order$\_$Items table:

item$\_$id The item$\_$id column

item$\_$price The item$\_$price column

discount$\_$amount The discount$\_$amount column

quantity The quantity column

price$\_$total A column that$$s calculated by multiplying the item

price by the quantity

discount$\_$total A column that$$s calculated by multiplying the

discount amount by the quantity

item$\_$total A column that$$s calculated by subtracting the

discount amount from the item price and then

multiplying by the quantity

Only return rows where the item$\_$total is greater than $455.$

Sort the result set by item total in descending sequence.

$15.$ Write a SQL statement that selects these columns from the Orders table:

order$\_$id The order$\_$id column

order$\_$date The order$\_$date column

ship$\_$amount The ship$\_$amount column

ship$\_$date The ship$\_$date column

Round the ship$\_$amount to $1$ decimal place.

Only return data where ship$\_$date doesn$$t contain null.

Limit results to $5$ rows.

$16.$ Write a SQL statement without a FROM clause that uses the NOW function to create

a row with these columns:

today$\_$unformatted The NOW function unformatted

today$\_$formatted The NOW function in this format:

$\text{'}$MM$/$DD$/$YYYY$$

This displays a $2$ digit number for the month, $2$ digit number of the day, and a four$-$

digit year.

$17.$ Write a SQL SELECT statement without a FROM clause that creates a row with

these columns:

Item$\_$price $10(10$ dollars$)$

Discount$\_$percent $\mathrm{.}25(25$ percent$)$

Discount$\_$amount Item$\_$price multiplied by Discount$\_$percent

Final$\_$price Item$\_$price minus $($Item$\_$price multiplied by

Discount$\_$percent$)$

To calculate the third column, add the expressions you used for the first and third