MySQL Test3

Home > Preview

The flashcards below were created by user slc53 on FreezingBlue Flashcards.


  1. Write a SELECT that returns these columns from the orders table: order_id, order_date, card_number only for VISA cards. Display the date in this format: 3rd September, 2016.
    • SELECT order_id, DATE_FORMAT(order_date, '%D %M, %Y') AS 'Order Date', card_number
    • FROM orders
    • WHERE card_type ='Visa';
  2. Write a SELECT statement that returns these columns: product_name, list_price and date_added. Return only the rows with a list price from 400 and to 700 only. Sort the result set in from most current to oldest date by the date_added column.
    • SELECT product_name, list_price, date_added
    • FROM products
    • WHERE list_price BETWEEN 400 and 700
    • ORDER BY date_added DESC;
  3. Write a SELECT statement that returns these columns: last_name, first_name, order_date, product_name, item_price, discount_amount, and quantity.Sort the final result set by last name, then order date within last name.
    • SELECT last_name, first_name,     order_date,
    •     product_name,
    •     item_price, discount_amount, quantity
    • FROM customers c
    •     JOIN orders o
    •         ON c.customer_id = o.customer_id    JOIN order_items oi
    •         ON o.order_id = oi.order_id
    •     JOIN products p
    •         ON oi.product_id = p.product_id
    • ORDER BY last_name, order_date;
  4. Write a SELECT statement that returns these columns:Display the number of orders, and total of shipping amount in the Orders table, note heading.
    • SELECT count(*) AS order_count, sum(ship_amount) AS 'Shipping Total'
    • FROM orders;
  5. Write a SELECT statement that returns one row for each category, that shows the number of products in each category and the total of all the product list prices for each category. Note the totals on last row.
    • SELECT category_name,    count(*) AS 'Number of Products in Category',    sum(list_price) AS 'Total of Product List Prices in Category'
    • FROM products p
    •     JOIN categories c
    •         ON p.category_id = c.category_id
    • GROUP BY category_name WITH ROLLUP;
  6. Write a SELECT statement that returns these two columns:category_name and the product_id from the Products tableReturn one row for each category that has never been used.
    • SELECT category_name, product_id
    • FROM products p
    •     RIGHT JOIN categories c
    •         ON p.category_id = c.category_id
    • WHERE product_id IS NULL;
  7. Write a SELECT statement that returns one row for each customer that has orders with these columns:The first and last names, a count of the number of their orders, and the total amount of orders for each customer. (Hint: First, subtract the discount amount from the price. Then, multiply by the quantity.)Return only those rows where the customer has more than 1 order.Sort the result set in descending sequence by the sum of the line item amounts.
    • SELECT first_name, last_name, count(*) AS order_count, sum(quantity*(item_price-discount_amount)) AS order_total
    • FROM order_items oi
    •     JOIN orders o
    •         ON oi.order_id = o.order_id
    •     JOIN customers c
    •         ON o.customer_id = c.customer_id
    • GROUP BY c.customer_id
    • HAVING order_count >1
    • ORDER BY order_total DESC;
  8. Write a SELECT statement that answers this question: Which products have a list price that’s greater than the average list price for all products? Return the product_name and list_price columns for each product and sort the results by the list_price column in descending sequence.
    • SELECT product_name, list_price
    • FROM products
    • WHERE list_price>
    •     (SELECT AVG(list_price)
    •     FROM products)
    • ORDER BY list_price DESC;
  9. Write an INSERT statement that adds this row to the Categories table:
    category_name: Used Equipment,
    coding statement so MySQL automatically generates the category_id column.
    • INSERT INTO categories (category_name)
    • VALUE
    • ('Used Equipment');
  10. Write an UPDATE statement that modifies the row you just added to the Categories table. This statement should change the category_name column to “Previously Enjoyed Equipment”, and it should use the category_id column to identify the row.
    • UPDATE categories
    • SET category_name = 'Previously Enjoyed Equipment'
    • WHERE category_id = 5;
  11. Write a DELETE statement that deletes the row you added to the Categories table in question 9. This statement should use the category_id column to identify the row.
    • DELETE FROM categories
    • WHERE category_id = 5;
  12. Write an INSERT statement that adds this row to the Customers table, using a column list: ( /4)email_address: comp53@slc.com password: (empty string) first_name: your first name last_name: your last name
    • INSERT INTO customers
    • (email_address, password, first_name, last_name)
    • VALUES
    • ('comp53@slc.com', '', 'Jeff', 'Harkness');
  13. Write an UPDATE statement that modifies your password to a movie title that you like. Check for your first and last name.
    • UPDATE customers
    • SET password = 'In the Name of the Father'
    • WHERE first_name = 'Jeff' AND last_name = 'Harkness';
  14. Write a SELECT statement to display all the rows in the Customers table.
    • SELECT *
    • FROM customers;
  15. Write an UPDATE statement that modifies the Customers table. Change the password column to “reset” for every customer in the table.
    • UPDATE customers
    • SET password = 'reset';
  16. Write the statement to create an index for the last name column in the Customers table, use a standard index name.
    • CREATE INDEX customers_last_name_ix
    •     ON customers (last_name);
  17. Add a new column to the administrators table, naming it cell_phone. You determine data type and length.You can look this up online or in Chapter 11 of your text.
    • ALTER TABLE administrators
    • ADD cell_phone VARCHAR(12);
  18. Write an UPDATE statement that will add a cell phone number to the first row in the administrators table, you decide on phone number.
    • UPDATE administrators
    • SET cell_phone = '123-456-7890'
    • WHERE admin_id = 1;
  19. How many rows in the invoices table?
    114
  20. How many rows in the vendors table?
    122
  21. Find the number, average, and sum of all the invoices after Jan 1, 2014.
    • -- pg 173 #1select 'After 1/1/2014' as selection_date,
    •     count(*) as number_of_invoices,
    •    round(avg(invoice_total),2) as
    •       avg_invoice_amt,
    •     sum(invoice_total) as total_invoice_amt
    • from invoices
    • where invoice_date >'2014-01-01';
  22. Find the number of distinct vendors, and the number, average, and sum of all the invoices after Jan 1, 2014.
    • -- pg 173 #4
    • select count(distinct vendor_id) as
    •       number_of_vendors,
    •     count(vendor_id) as num_of_invoices,
    •     round(avg(invoice_total),2) as
    •       avg_invoice_amt,
    •     sum(invoice_total) as total_invoice_amt
    • from invoices
    • where invoice_date > '2014-01-01';
  23. Display invoice_date and 'hi-looking for dates after May 1, 2014' as selection-date for all invoices after may 1, 2014.
    • SELECT invoice_date,
    •    'hi-looking for dates after May 1, 2014'
    •       AS selection_date
    • FROM invoices
    • WHERE invoice_date >'2014-05-01';
  24. How to determine the number of rows in a table
    SELECT count(*) FROM invoices;
  25. Display After Jan1, 2014' AS selection_date, the number_of_invoices, highest_inv_total, and lowest_invoice_total for all invoices after Jan1, 2014.
    • SELECT 'After Jan1, 2014' AS selection_date,
    •     COUNT(*) AS number_of_invoices,
    •     MAX(invoice_total) AS highst_inv_total,
    •     MIN(invoice_total) AS lowst_invoice_total
    • FROM invoices
    • WHERE invoice_date>'2014-01-01';
  26. What is the basic query structure
    • SELECT
    • FROM
    •    JOIN
    •       ON
    • WHERE
    • GROUP BY name [WITH ROLLUP]
    • HAVING
    • ORDER BY (if not WITHROLLUP)
    • (can't use DISTINCT in any aggregate functions WITH ROLLUP)
  27. Find the number invoices as invoice-qty and average invoice amount as invoice_avg for each vendor. Display on the vendors with averages over 500 and sort from high to low by number of invoices
    • SELECT vendor_name,
    •     Count(*) as invoice_qty,
    •     round(avg(invoice_total),2) as invoic_avg
    • FROM vendors v
    •     JOIN invoices i
    •         ON v.vendor_id = i.vendor_id
    • GROUP BY vendor_name
    • HAVING avg(invoice_total)>500
    • ORDER BY invoice_qty DESC;
  28. create a copy of the invoices table
    • CREATE TABLE invoices_copy AS
    • SELECT *
    • FROM invoices;
  29. delete invoices_copy table
    DROP TABLE invoices_copy;
  30. turn of 'safe mode' in MySQL
    Edit/Preferences/SQL Editor/deselect 'Safe Updates'/restart workbench
  31. CREATE TABLE colour_sample(
    color_id INT NOT NULL Auto_inc,
    color_number INT NOT NULL default 0,
    color_name VARCHAR(50)  );
    Assign auto color_id, num 606 and no name.
    • INSERT INTO color_sample(color_number) VALUES
    • (606);

    Result = (auto,606,null)
  32. CREATE TABLE colour_sample(
    color_id INT NOT NULL Auto_inc,
    color_number INT NOT NULL default 0,
    color_name VARCHAR(50)  );
    Assign Yellow and the default id and number)
    • INSERT INTO color_sample(color_name) VALUES
    • ('Yellow')
    • Result = (auto,0,'Yellow')
  33. CREATE TABLE colour_sample(
    color_id INT NOT NULL Auto_inc,
    color_number INT NOT NULL default 0,
    color_name VARCHAR(50)  );
    Assign color_id 4, color_number 808.
    • INSERT INTO color_sample (color_id,color_number) VALUES
    • (4,808);
    • or
    • INSERT INTO color_sample VALUES
    • (4,808,NULL);
  34. CREATE TABLE colour_sample(
    color_id INT NOT NULL Auto_inc,
    color_number INT NOT NULL default 0,
    color_name VARCHAR(50)  );
    Assign id 5, number 0, name null.
    • INSERT INTO color_sample (color_id) VALUES
    • (5);
    • OR
    • INSERT INTO color_sample  VALUES
    • (5,default,null);
  35. Display the average invoice total for each vendor in the invoices table and the overall average.
    • select vendor_id, avg(invoice_total)
    • FROM invoices
    • GROUP BY vendor_id WITH ROLLUP;
  36. displays the number of invoices for each vendor
    • SELECT vendor_id, COUNT(*) AS invoice_qty
    • FROM invoices
    • GROUP BY vendor_id;
  37. update
    • UPDATE table_name
    • SET column_name_1=expressions_1
    •    [, column_name2=expressions_2]
    • [WHERE search_condition];
  38. delete record
    • DELETE FROM table_name
    • [WHERE search_condtion]

Card Set Information

Author:
slc53
ID:
321723
Filename:
MySQL Test3
Updated:
2016-07-07 17:24:07
Tags:
MySQL Test3
Folders:

Description:
MySQL Test3
Show Answers:

Home > Flashcards > Print Preview