Lab 1.1: Column Selection & Alphabetical Sorting

Objective: Retrieve specific product identifiers and sort the inventory by name.

SELECT product_id, product_code, product_name 
FROM products 
ORDER BY product_name ASC;

Lab 1.19: Threshold Filtering (List Price)

Objective: Filter for high-value items with a list price exceeding 400.

SELECT product_name, list_price, date_added
FROM products
WHERE list_price > 400
ORDER BY category_id DESC;

Lab 1.20: Null Capability Testing

Objective: Isolate records with missing ship dates to identify pending orders.

SELECT order_id, order_date, ship_date
FROM orders
WHERE ship_date IS NULL;

Lab 2.1: Artist & Album Data Retrieval

Primary Syntax (Terminal Original):

SELECT AlbumId, Title, albums.ArtistId, Name
FROM albums, artists
WHERE albums.ArtistId = artists.ArtistId;

Alternate Syntax (Explicit JOIN):

SELECT al.AlbumId, al.Title, al.ArtistId, ar.Name 
FROM albums AS al 
JOIN artists AS ar ON al.ArtistId = ar.ArtistId;

Lab 2.15: Product Price & Discount Formatting

Primary Syntax (Terminal Original):

SELECT product_id, product_name, 
       ROUND(discount_percent, 1) AS discount_percent,
       ROUND(list_price, 0) AS list_price
FROM products;

Alternate Syntax (Explicit Column Selection):

SELECT product_name, 
       ROUND(list_price, 0) AS formatted_price, 
       ROUND(discount_percent, 1) AS formatted_discount
FROM products;

Lab 3.1: Table Creation via Subquery

Assignment Objective: Create a new table in my_guitar_shop called guitars with the same columns as the products table. Populate the table by selecting all of the guitars from the products table using the category_id of 1. After creating the table run the following statement to set the primary key and auto_increment the product_id. If you do not do this, you may have trouble with some of the later queries. --ALTER TABLE guitars modify product_id INT primary key NOT NULL AUTO_INCREMENT; AlbumId, Title, ArtistId, and the artist's Name

Primary Syntax (Terminal Original):

CREATE TABLE guitars AS
SELECT *
FROM products
WHERE category_id = 1;

ALTER TABLE guitars 
MODIFY product_id INT PRIMARY KEY NOT NULL AUTO_INCREMENT;

Alternate Syntax (Explicit DDL):

CREATE TABLE guitars LIKE products;
INSERT INTO guitars SELECT * FROM products WHERE category_id = 1;

Lab 3.2: Record Insertion

Assignment Objective: Write an INSERT statement that adds this row to the Guitars table: category_id: 1, product_code: Gibson J45, product_name: Gibson J-45 Custom Acoustic/Electric Guitar, description: Number one selling acoustic guitar, list_price: 2799.00, discount_percent: 20.00, date-added: use today's date.

Primary Syntax (Terminal Original):

INSERT INTO guitars (category_id, product_code, product_name, description, list_price, discount_percent, date_added)
VALUES (1, 'Gibson J45', 'Gibson J-45 Custom Acoustic/Electric Guitar', 'Number one selling acoustic guitar', 2799.00, 20.00, NOW());

Lab 3.3: Value Update

Assignment Objective: Write an UPDATE statement that modifies the row you just added to the guitars table. This statement should change the list_price to 2999.99 using the product_code column to identify the row.

Primary Syntax (Terminal Original):

UPDATE guitars
SET list_price = 2999.99
WHERE product_code = 'Gibson J45';

Lab 3.4: Row Deletion

Assignment Objective: Write a DELETE statement that deletes product 6 from the Guitars table.

Primary Syntax (Terminal Original):

DELETE FROM guitars
WHERE product_id = 6;

Lab 3.5: Named Column Insertion

Assignment Objective: Write an INSERT statement that adds this row to the Guitars table: Column names are on left - values on right product_id: The next automatically generated ID, category_id: 1, product_code: dgx_640, product_name: Yamaha DGX 640 Guitar, list_price: 799.99, date_added: Today’s date/time. Use a named column list for this statement. Note that you will have to address the fact that the description column does not have a default value assigned to it. You may address this in several ways your choice of how you choose to, but the statement must run

Primary Syntax (Terminal Original):

INSERT INTO guitars (category_id, product_code, product_name, description, list_price, date_added)
VALUES (1, 'dgx_640', 'Yamaha DGX 640 Guitar', '', 799.99, NOW());

Lab 3.6: Multi-Column Update

Assignment Objective: Write an UPDATE statement that modifies the product you added in problem 5 . This statement should change the discount_percent column to 35% and the description to "Yamaha Lightweight Deluxe Electric Guitar ".

Primary Syntax (Terminal Original):

UPDATE guitars
SET discount_percent = 35.00,
    description = 'Yamaha Lightweight Deluxe Electric Guitar'
WHERE product_code = 'dgx_640';

Lab 3.7: Pattern Match Deletion

Assignment Objective: Write a DELETE statement that deletes all the guitars from the Guitars table where the product_name includes the word 'Yamaha'

Primary Syntax (Terminal Original):

DELETE FROM guitars
WHERE product_name LIKE '%Yamaha%';

Lab 3.8: Conditional Mass Update

Assignment Objective: Write an UPDATE statement that modifies the Guitars table. Change the discount_percent to 10% for any row that has a discount_percent less than 10%

Primary Syntax (Terminal Original):

UPDATE guitars
SET discount_percent = 10.00
WHERE discount_percent < 10.00;

Lab 3.9: Subquery Price Reduction

Assignment Objective: Write a statement that reduces the list_price by $100 for any guitar that has a match in the order_items table. Use a subquery like the example on page 157.

Primary Syntax (Terminal Original):

UPDATE guitars
SET list_price = list_price - 100
WHERE product_id IN (SELECT DISTINCT product_id FROM order_items);

Alternate Syntax (JOIN Update):

UPDATE guitars g
JOIN order_items oi ON g.product_id = oi.product_id
SET g.list_price = g.list_price - 100;

Lab 3.10: Final Reclassification

Assignment Objective: Change the category_id to 2 for any row in the guitars table that has a product_code of strat.

Primary Syntax (Terminal Original):

UPDATE guitars
SET category_id = 2
WHERE product_code = 'strat';

Lab 5.1: The IN Keyword

Assignment Objective: Write a SELECT statement that returns the same result set as this SELECT statement, but don’t use a join. Instead, use a subquery in a WHERE clause that uses the IN keyword. This query would list category names only if there are products associated with the category.

Primary Syntax (Terminal Original):

SELECT category_name
FROM categories
WHERE category_id IN 
    (SELECT DISTINCT category_id 
     FROM products);

Alternate Syntax (Explicit JOIN):

SELECT DISTINCT category_name 
FROM categories c 
JOIN products p ON c.category_id = p.category_id;

Lab 5.2: Comparison Subqueries

Assignment Objective: Write a SELECT statement that answers this question: Which products have a list price that is greater than the average list price? Return the product_name and list_price columns. Sort the results by the list_price column in descending order.

Primary Syntax (Terminal Original):

SELECT product_name, list_price
FROM products
WHERE list_price > 
    (SELECT AVG(list_price) 
     FROM products)
ORDER BY list_price DESC;

Alternate Syntax (CTE Approach):

WITH AvgPrice AS (SELECT AVG(list_price) as val FROM products)
SELECT product_name, list_price 
FROM products, AvgPrice 
WHERE list_price > val;

Lab 5.3: The NOT IN Operator

Assignment Objective: Write a SELECT statement that returns the category_name column from the Categories table. Return a row only if the category_id has never been used in the Products table. This query would list category names that don’t have any products associated with them.

Primary Syntax (Terminal Original):

SELECT category_name
FROM categories
WHERE category_id NOT IN 
    (SELECT category_id 
     FROM products);

Alternate Syntax (NOT EXISTS):

SELECT category_name FROM categories c
WHERE NOT EXISTS (SELECT * FROM products p WHERE p.category_id = c.category_id);

Lab 5.6: Correlated Subqueries

Assignment Objective: Write a SELECT statement that returns the email_address, order_id, and the order total for each customer. To do this, you can return the total for each order from the orders table and use a subquery to return the email address for each customer.

Primary Syntax (Terminal Original):

SELECT (SELECT email_address FROM customers 
        WHERE customers.customer_id = orders.customer_id) AS email_address, 
       order_id, 
       (SELECT SUM((item_price - discount_amount) * quantity) 
        FROM order_items 
        WHERE order_items.order_id = orders.order_id) AS order_total
FROM orders;

Lab 5.12: The ALL Operator

Assignment Objective: USE an ALL operator with a subquery to display the category_name, category_id, product_id, product_name and list_price of every product that has a list price greater than any product that has a category id = 2.

Primary Syntax (Terminal Original):

SELECT category_name, products.category_id, product_id, product_name, list_price
FROM products 
JOIN categories ON products.category_id = categories.category_id
WHERE list_price > ALL 
    (SELECT list_price 
     FROM products 
     WHERE category_id = 2);

Alternate Syntax (MAX Aggregate):

SELECT category_name, p.category_id, product_id, product_name, list_price
FROM products p
JOIN categories c ON p.category_id = c.category_id
WHERE list_price > (SELECT MAX(list_price) FROM products WHERE category_id = 2);

Lab 6.11: Date Calculation

Assignment Objective: Write a SELECT statement that returns these columns from the Orders table: order_id, order_date, ship_date, and a column named days_to_ship that shows the number of days between the order date and the ship date. Use the appropriate date/time functions.

Primary Syntax (Terminal Original):

SELECT order_id, order_date, ship_date,
       DATEDIFF(ship_date, order_date) AS days_to_ship
FROM orders;

Alternate Syntax (DATE_ADD Integration):

/* If a logic-check for a 2-day lead time was required */
SELECT order_id, DATE_ADD(order_date, INTERVAL 2 DAY) AS projected_date
FROM orders;

Lab 6.12: Temporal Components

Assignment Objective: Write a SELECT statement that returns these columns from the Orders table: order_id, order_date, the year of the order_date, the day of the order_date, and a column that adds 30 days to the order_date.

Primary Syntax (Terminal Original):

SELECT order_id, order_date,
       YEAR(order_date) AS order_year,
       DAY(order_date) AS order_day,
       DATE_ADD(order_date, INTERVAL 30 DAY) AS thirty_days
FROM orders;

Lab 6.13: String Indexing & Splitting

Assignment Objective: Write a SELECT statement that returns the card_number column from the Orders table. Include a column that returns the length of the card_number column. Include a column that returns the last four digits of the card_number column. Include a column that displays the card number in this format: XXXX-XXXX-XXXX-1234. (where 1234 are the actual last four digits of the card)

Primary Syntax (Terminal Original):

SELECT card_number,
       LENGTH(card_number) AS card_length,
       RIGHT(card_number, 4) AS last_four,
       CONCAT('XXXX-XXXX-XXXX-', RIGHT(card_number, 4)) AS formatted_card
FROM orders;

Lab 6.14: Business Logic Aging

Assignment Objective: Write a SELECT statement that returns the order_id, order_date, and ship_date from the orders table. Include a column named days_to_ship that returns the number of days between the order_date and the ship_date. Return only the orders that haven't been shipped yet.

Primary Syntax (Terminal Original):

SELECT order_id, order_date, ship_date,
       DATEDIFF(ship_date, order_date) AS days_to_ship
FROM orders
WHERE ship_date IS NULL;

Lab 6.1: String Concatenation & Type Conversion

Assignment Objective: Write a SELECT statement that returns these columns from the Order_items table. order_id, item_price, discount_amount Use implicit conversion and the CONCAT function to insert a ‘$’ in the front of the item_price and discount_amount values.

Primary Syntax (Terminal Original):

SELECT order_id, 
       CONCAT('$', item_price) AS item_price, 
       CONCAT('$', discount_amount) AS discount_amount
FROM order_items;

Terminal Output:

Lab 6.9: Date Formatting

Assignment Objective: Write a SELECT statement that returns the order_date and a formatted version of it using the DATE_FORMAT function. Use this format: ‘MM/DD/YYYY HH:MM AM/PM’

Primary Syntax (Terminal Original):

SELECT order_date, 
       DATE_FORMAT(order_date, '%m/%d/%Y %h:%i %p') AS formatted_date
FROM orders;

Terminal Output:

Lab 6.10: Numeric Precision & Seeding

Assignment Objective: For each product, display the list_price, list_price rounded to 1 decimal place, list_price truncated to 1 decimal place, smallest integer >= list_price, argest integer <= list_price, and a random number between 1 and 10 using the product id as a seed.

Primary Syntax (Terminal Original):

SELECT list_price,
       ROUND(list_price, 1) AS price_round,
       TRUNCATE(list_price, 1) AS price_truncate,
       CEILING(list_price) AS price_ceil,
       FLOOR(list_price) AS price_floor,
       FLOOR(1 + (RAND(product_id) * 10)) AS random_val
FROM products;

Terminal Output: