Homework Chapter 3 How to retrieve data from a single table Exercises (Use account mgs to connect My Guitar Shop database(mgs)) Enter and run your own SELECT statements In these exercises, you’ll...

This is oracle SQL assignment


Homework Chapter 3 How to retrieve data from a single table Exercises (Use account mgs to connect My Guitar Shop database(mgs)) Enter and run your own SELECT statements In these exercises, you’ll enter and run your own SELECT statements. 1. Write a SELECT statement that returns four columns from the Products table: product_code, product_name, list_price, and discount_percent. Then, run this statement to make sure it works correctly. Add an ORDER BY clause to this statement that sorts the result set by list price in descending sequence. Then, run this statement again to make sure it works correctly. This is a good way to build and test a statement, one clause at a time. Write a SELECT statement that returns one column from the Customers table named full_name that joins the last_name and first_name columns. Format this column with the last name, a comma, a space, and the first name like this: Doe, John Sort the result set by last name in ascending sequence. Return only the customers whose last name begins with letters from M to Z. Write a SELECT statement that returns these columns from the Products table: product_nameThe product_name column list_priceThe list_price column date_addedThe date_added column Return only the rows with a list price that’s greater than 500 and less than 2000. Sort the result set in descending sequence by the date_added column. Write a SELECT statement that returns these column names and data from the Products table: product_nameThe product_name column list_priceThe list_price column discount_percentThe discount_percent column discount_amountA column that’s calculated from the previous two columns discount_priceA column that’s calculated from the previous three columns Use the ROWNUM pseudo column so the result set contains only the first 5 rows. Sort the result set by discount price in descending sequence. Write a SELECT statement that returns these column names and data from the Order_Items table: item_idThe item_id column item_priceThe item_price column discount_amountThe discount_amount column quantityThe quantity column price_totalA column that’s calculated by multiplying the item price by the quantity discount_totalA column that’s calculated by multiplying the discount amount by the quantity item_totalA 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 500. Sort the result set by item total in descending sequence. Work with nulls and test expressions Write a SELECT statement that returns these columns from the Orders table: order_idThe order_id column order_dateThe order_date column ship_dateThe ship_date column Return only the rows where the ship_date column contains a null value. Write a SELECT statement that uses the SYSDATE function to create a row with these columns: today_unformattedThe SYSDATE function unformatted today_formattedThe SYSDATE function in this format: MM-DD-YYYY This displays a number for the month, a number for the day, and a four-digit year. Use a FROM clause that specifies the Dual table. Write a SELECT statement that creates a row with these columns: price100 (dollars) tax_rate.07 (7 percent) tax_amountThe price multiplied by the tax totalThe price plus the tax To calculate the fourth column, add the expressions you used for the first and third columns. Use a FROM clause that specifies the Dual table. Deliverables:  1. Create a textfile titled  with extension .sql in Assignment’s ”Homework Chapter 3” DropBox 6. Tips:    7. 1. Run your query in SQL Delveloper using an icon "Run a script/F5" 8. 2. To copy your query or output click your query(or output) and use a combination of keys: ctr+A(means select all) and ctr+C (means to copy all) 9. 2. To past your query and output to the file use a combination of  keyboard keys ctr+ V (means past)