Week 5 – Case Assignment – How to use SQL to work with a database

select products(7)

USE my_guitar_shop2;

SELECT productName,description,listPrice
FROM products
WHERE description LIKE '%electric%';

select billing(8)

USE my_guitar_shop2;

SELECT firstName, lastName, line1, line2, city, state, zipCode
FROM customers c
INNER JOIN addresses a 
ON c.customerID = a.customerID
WHERE lastName LIKE 'sherwood';

count products by category(9)

USE my_guitar_shop2;

SELECT categoryID, categoryName,
(SELECT COUNT(*) FROM products
 WHERE products.categoryID =1) AS productCount
FROM categories
WHERE categoryID=1;

insert customer(10)

USE my_guitar_shop2;

INSERT INTO customers (emailAddress,password,firstName,lastName)
VALUES ('johnsmith@example.com','sesame','John','Smith');

update customer(11)

USE my_guitar_shop2;

UPDATE customers
SET password = '5e5ame!'
WHERE customerID = 4;

delete customer(12)

USE my_guitar_shop2;

DELETE FROM customers
WHERE customerID = 4;