SQL TUTORIALS

Answer Key

1. CREATE TABLE orders(id INT PRIMARY KEY, order_date DATE NOT NULL, num_items INT NOT NULL, subtotal_amount FLOAT NOT NULL, tax_amount FLOAT NOT NULL, customer_id INT, employee_id INT, FOREIGN KEY (customer_id) REFERENCES customers(id), FOREIGN KEY (employee_id) REFERENCES employees(id));
2. COPY customers(id, first_name, last_name, phone_num, address, city, state, zip, age)
FROM 'file_path\customers.csv'
DELIMITER ','
CSV HEADER;
3. COPY employees(id, first_name, last_name, hire_date, wage)
FROM 'file_path\customers.csv'
DELIMITER ','
CSV HEADER;
4. COPY orders(id, order_date, num_items, subtotal_amount, tax_amount, customer_id, employee_id)
FROM 'file_path\orders.csv'
DELIMITER ','
CSV HEADER;
5. INSERT INTO categories VALUES
(1,'Furniture', 'This includes all items that furnish a home or backyard. Examples inlude couches, coffe tables, and ottomans.'),
(2, 'Small Appliances', 'This includes all items that are used to assist customers in their daily activities around the home. Examples include small fridges, soda can dispensers, and coffee makers.'),
(3, 'Bedding', 'This includes all items that are related to sleeping. Examples include mattresses, fitted sheets, loose sheets, and pillow cases.');
6. UPDATE employees
SET wage = wage * 1.5
WHERE hire_date < '3/1/2020';
7. UPDATE orders
SET tax_amount = tax_amount/2
WHERE num_items > 10;
8. DELETE FROM customers
WHERE city IS NOT NULL AND state IS NULL;