Please write SQL queries for all questions in the assignment. Also include the n

Question

Please write SQL queries for all questions in the assignment. Also include the number of rows resulting from the query.
1 of 3 HW 1 How to retrieve data from a single table 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 connectly 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. 2. 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, acomma, 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. NOTE: When comparing strings of characters. M' comes before any string of characters that begins with M For example, M' comes before Murach 3. Write a SELECT statement that returns these columns from the Products table: product name The product name column The list price column list price date added The date added column Retum 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.

Explanation / Answer

Q1) We have to return 4 columns from the table. This is a general select query, whose syntax is as follows:

select [column_names] from table_name;

where you can pas multiple comma separated column names in [column_names].

So for the first question query will be:

select product_code,product_name,list_price,discount_price from products;

the second part of the question is to sort by list_price in descending order. We have to use the order by statement for this. Syntax to sort a table in descending order by a column is , order by column_name desc;

We have to add this statement in the previous select statement, so it becomes,

select product_code,product_name,list_price,discount_price from products order by list_price desc;

Q2) For this question we have to use the CONCAT function.

CONCAT(column1,column2) concats the 2 columns.

So for our question the query would be,

select concat(last_name," , ",first_name) as full_name from customers;

We used alias full_name in the above statement to name the newly generated column.

we also have to sort it in ascending order by last_name, so query becomes:

select concat(last_name," , ",first_name) as full_name from customers order by last_name asc;

Finally we need to select only those names whose last_name begins from M to Z. Here we need to use the where clause along with LIKE operator.

To get names starting with M , the syntax is where name like 'M%' , for matching a range we can use as where name like '[M-Z]%' ;

So our final query becomes :

select concat(last_name," , ",first_name) as full_name from customers where last_name like '[M-Z]%' order by last_name asc;

Q3) In this question we have to again use the where clause for returning resuts that matches the comparision.

so query is:

select * from products where list_price > 500 AND list_price < 2000;

Now we need to sort by date_added so query becomes:

select * from products where list_price > 500 AND list_price < 2000 order by date_added desc;

Q4) In this question we need to use the round function to round result to 2 decimal places.

Syntax to round a column to 2 decimal places is round(column_name,2)

So our query to round discount_amount and discount_price and then sort it in desc order by discount_price becomes:

select round(discount_amount,2), round(discount_price,2) from products order by discount_price desc;

To limit number of rows to 5, use the LIMIT operator as:

select round(discount_amount,2), round(discount_price,2) from products order by discount_price desc limit 5;

As per chegg policy, you should not post more than 4 sub-questions at a time. Please post rest sub-questions as a separate question.

Please give a thumbs up, if you liked the solution

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---