In: Computer Science
All questions in this assignment refer to the “om” database (or Schema) that you will find in your MySQL Workbench program if you have run the sample database install script.
Please save all of your answers in one script (.sql) or type all your answers into Notepad++ and submit them as a single .sql file. You are encouraged to test your SQL statements in Workbench, and please use the ‘tidy’ tool to properly format your SQL before you save it as a Script from Workbench or Notepad++. Use comments to indicate the question number for each answer. Please give all code. Don't give same like other code. Please do with error free.
1. Write a SELECT statement that returns the title, artist and unit_price columns from the items table. Return only those items that have a unit_price of at least $16, but less than $17.50
2. Write a SELECT statement to return all columns from the customers table where the customer’s state is Ohio (OH). Sort the results by last name in descending order.
3. Write a SELECT statement to return all columns from the customers table whose zip code begins with a “9”
4. Write a SELECT statement to return the order_id from the orders table where the number of days between the order_date and the shipped_date is less than 6
5. Expand the statement in question 4 to include the number of days between order and ship date as a column alias called ‘processing_days’ and sort the results by that column in ascending order
1.
SELECT title, artist, unit_price
FROM items
WHERE unit_price>= 16 AND unit_price<17;
2.
SELECT * FROM customers
WHERE state= 'OH'
ORDER BY last_name DESC;
3.
SELECT * FROM customers
WHERE zipcode LIKE '9%';
EXPLANATION:
Select * from tableName returns all the information in the table. But when WHERE clause is used, then it filters the rows according to the condition given.
LIKE operator is used to compare pattern where zipcode starts from 9.
4.
SELECT order_id
FROM orders
WHERE DATEDIFF(order_date, shipped_date) <6;
5.
SELECT order_id, DATEDIFF(order_date, shipped_date) AS "processing_days"
FROM orders
ORDER BY "processing_days" ASC;
EXPLANATION:
Selects order_id and number of days between order date and shipped.
DATEDIFF() is a function in MySQL to find out the difference in days between two intervals.
Keyword 'AS' is used to Rename the column name.
ORDER BY columnName ASC; is used to sort the results in the ascending order of columnName.