Complete all of the exercises below using the my_guitar_shop database. Once you
ID: 3807606 • Letter: C
Question
Complete all of the exercises below using the my_guitar_shop database. Once you have a working query, paste your code into Notepad. Place all of your work in one Notepad file. Be sure to properly number each exercise SQL code with chapter and exercise number.
Your first task is to create this database in MySQL using the techniques learned in chapters 1 and 2.
Retrieve data from a single table. 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.
2.) Add an ORDER BY clause to this statement that sorts the result set by list price in ascending 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.
Answer to 3.2 (Just to get you started)
SELECT product_code, product_name, list_price, discount_percent
FROM products
ORDER BY list_price DESC;
3.) 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 descending sequence. Return only the customers whose last name begins with letters from B to Z. NOTE: When comparing strings of characters, ‘B’ comes before any string of characters that begins with ‘B. For example, ‘B’ comes before ‘Bumstead’.
4.) Write a SELECT statement that returns these columns from the Products table:
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.
product_name The product_name column list_price The list_price column date_added The date_added columnExplanation / Answer
Here is the set of SQL statements for the question. Please have the tables created and insert some data into them and then run these queries.
In order to sort the results by a column name, we use ORDER BY columnname . This generally sorts in ascending order. But if we need the column to be sorted by descending order , we need to use ORDER BY columnname DESC.
To specify any conditions on a column value, we use the WHERE clause. For ex: WHERE list_price>500 means
all rows where the column list_price has a value greater than 500.
Answer to Q1
SELECT product_code, product_name, list_price, discount_percent
FROM products
Answer to Q2
SELECT product_code, product_name, list_price, discount_percent
FROM products
ORDER BY list_price DESC;
Answer to Q3
SELECT last_name+", "+first_name as full_name
FROM Customers
WHERE last_name>='B' AND last_name<='Z'
ORDER BY last_name DESC;
In the above query + is used to concatenate the values of the columns last_name and first_nameand using comma and space in between. + with strings as concatenation operator. Also relation operators >= and <= work by doing string comparisons
Answer to Q4
SELECT product_name, list_price, date_added
FROM Products
WHERE list_price>500 and list_price<2000
ORDER BY date_added DESC;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.