How to retrieve data from a single table

Exercise 1

1. Write a SELECT statement that returns three columns from the Vendors table:

   • vendor_name,
   • vendor_contact_last_name,
   • and vendor_contact_first_name.

2. Then, run this statement to make sure it works correctly.

3. 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.

Solution 1

SELECT
    vendor_name,
    vendor_contact_last_name,
    vendor_contact_first_name
FROM
    vendors
ORDER BY
    vendor_contact_last_name ASC,
    vendor_contact_first_name ASC;

Exercise 2

1. 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: Doe, John

2. 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.

Solution 2

SELECT
    CONCAT(vendor_contact_last_name, ', ', vendor_contact_first_name) AS 'full_name'
FROM
    vendors
WHERE
    vendor_contact_last_name LIKE 'a%'
    OR vendor_contact_last_name LIKE 'b%'
    OR vendor_contact_last_name LIKE 'c%'
    OR vendor_contact_last_name LIKE 'e%'
ORDER BY
    vendor_contact_last_name ASC;

Exercise 3

1. Write a SELECT statement that returns these column names and data from the Invoices table:

   • Due Date (The invoice_due_date column)
   • Invoice Total (The invoice_total column)
   • 10% (10% of the value of invoice_total)
   • Plus 10% (The value of invoice_total plus 10%)

2. 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.

3. Sort the result set in descending sequence by invoice_due_date.

Solution 3

SELECT
    invoice_due_date    AS 'Due Date',
    invoice_total       AS 'Invoice Total',
    invoice_total / 10  AS '10%',
    invoice_total * 1.1 AS 'Plus 10%'
FROM
    invoices
WHERE
    invoice_total >= 500
    AND invoice_total <= 1000
ORDER BY
    invoice_due_date DESC;

Exercise 4

1. Write a SELECT statement that returns these columns from the Invoices table:

   • invoice_number (The invoice_number column)
   • invoice_total (The invoice_total column)
   • payment_credit_total (Sum of the payment_total and credit_total columns)
   • balance_due (The invoice_total column minus the payment_total and credit_total columns)

2. Return only invoices that have a balance due that’s greater than $50.

3. Sort the result set by balance due in descending sequence.

4. Use the LIMIT clause so the result set contains only the rows with the 5 largest balances.

Solution 4

SELECT
    invoice_number,
    invoice_total,
    SUM(payment_total + credit_total)                 AS 'payment_credit_total',
    SUM(invoice_total - payment_total - credit_total) AS 'balance_due'
FROM
    invoices
WHERE
    invoice_total - payment_total - credit_total > 50
ORDER BY
    balance_due DESC
LIMIT 5;

Exercise 5

1. Write a SELECT statement that returns these columns from the Invoices table:

   • invoice_number (The invoice_number column)
   • invoice_date (The invoice_date column)
   • balance_due (The invoice_total column minus the payment_total and credit_total columns)
   • payment_date (The payment_date column)

2. Return only the rows where the payment_date column contains a null value. This should retrieve 11 rows.

Solution 5

SELECT
    invoice_number,
    invoice_date,
    SUM(invoice_total - payment_total - credit_total) AS 'balance_due',
    payment_date
FROM
    invoices
WHERE
    payment_date IS NULL
GROUP BY
    invoice_number;

Exercise 6

1. Write a SELECT statement without a FROM clause that uses the CURRENT_DATE function to return the current date in its default format.

2. Use the DATE_FORMAT function to format the current date in this format: mm-dd-yyyy, This displays the month, day, and four‐digit year of the current date.

3. Give this column an alias of current_date. To do that, you must enclose the alias in quotes since that name is already used by the CURRENT_DATE function.

Solution 6

SELECT DATE_FORMAT(CURRENT_DATE, '%m-%d-%y') AS 'current_date';

Exercise 7

1. Write a SELECT statement without a FROM clause that creates a row with these columns:

   • starting_principal (Starting principal of $50,000)
   • interest (6.5% of the principal)
   • principal_plus_interest (The principal plus the interest)

2. To calculate the third column, add the expressions you used for the first two columns.

Solution 7

SELECT 50000 AS 'starting principal',
       50000 * 0.065 AS 'interest',
       50000 * 1.065 AS 'starting principal plus interest';