Question
MYSQL
Enter and run your own SELECT statements In these exercises, you'll enter and run your own SELECT statements. To do that, you can open the query for an example that is similar to the statement you need to write, copy the statement into a new SQL tab, and modify the statement. That can save you both time and syntax errors. 6. Write a SELECT statement that returns three columns from the Vendors table: vendor_name, vendor_contact last name, and vendor_contact first_name. Then, run this statement to make sure it works correctly. Add an ORDER BY clause to this statement that sorts the result set by last name and then first name, both in ascending sequence. Then, run this state- ment 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 Vendors table named full_name that joins the vendor_contact_last_name and vendor_contact_first_name columns Format this column with the last name, a comma, a space, and the first name like this 7. Doe, John Sort the result set by last name and then first name in ascending sequence. Return only the contacts whose last name begins with the letter A, B, C, or E This should retrieve 41 rows. Write a SELECT statement that returns these column names and data from the Invoices table: 8. Due Date Invoice Total 10% Plus 10% The invoice_due_date column The invoice_total column 10% of the value of invoice total The value of invoice-total plus 10% Return only the rows with an invoice total that's greater than or equal to 500 and less than or equal to 1000. This should retrieve 12 rows. Sort the result set in descending sequence by invoice_due_date. Write a SELECT statement that returns these columns from the Invoices table: 9. The invoice_number column The invoice total column invoice_number invoice_total payment_credit_total Sum of the payment_total and credit_total balance_due columns The invoice_total column minus the payment_total and credit_total columns
Explanation / Answer
6.
select vendor_name, vendor_contact_last_name, vendor_contact_first_name
from vendors
order by vendor_contact_last_name, vendor_contact_first_name
7.
select
CONCAT(vendor_contact_last_name, ', ', vendor_contact_first_name) as name
from vendors
where
vendor_contact_last_name REGEXP '^(A|B|C|E)'
order by vendor_contact_last_name, vendor_contact_first_name
8.
select invoice_due_date as 'Due Date',
invoice_total as 'Invoice Total',
0.1 * invoice_total,
1.1 * invoice_total
where (invoice_total >= 500 or invoice_total <= 100)
order by invoice_due_date desc;
9.
select invoice_number ,
invoice_total,
(payment_total + credit_total) as 'payment_credit_total',
(invoice_total - payment_total - credit_total) as 'balance_due'
having (balance_due > 50)
order by balance_due desc
limit 5;
=====
Answering your first 4 questions in the given time limit.. i request you to please post other questions in a seperate thread so that we can answer them better.. Thank-you.
