Tools SQL.

[mdcj0208]

This is my first SQL code!!!

CREATE TABLE sales (
purchase_num INT PRIMARY KEY,
customer_id VARCHAR(50),
item_code VARCHAR(50),
date_of_purchase DATE
);

INSERT INTO sales (purchase_num, customer_id, item_code, date_of_purchase)
VALUES
(1, 'CUST-0001', 'A_1', '2016-03-01'),
(2, 'CUST-0002', 'B_1', '2016-03-01'),
(3, 'CUST-0003', 'A_1', '2016-04-01'),
(4, 'CUST-0004', 'C_2', '2016-04-01');

/DATE FORMAT = YYYY-DD-MM/

CREATE TABLE customers (
customer_id VARCHAR(100) PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email_address VARCHAR(100),
complaints INT NULL
);

INSERT INTO customers (customer_id, first_name, last_name, email_address, complaints)
VALUES
('CUST-0001', 'Adam', 'Jones',
'[email protected]', 2),
('CUST-0002', 'Brandon', 'Harris',
'[email protected]', 1),
('CUST-0003', 'Nicole', 'Gray',
'[email protected]', 1),
('CUST-0004', 'Ashley', 'Darver', '[email protected]', 0);

CREATE TABLE items(
item_code VARCHAR(50) PRIMARY KEY,
item VARCHAR(50),
price DECIMAL(5,2)
);

INSERT INTO items( item_code, item, price)
VALUES
( 'A_1', 'Hammer', 46.89),
('A_2', 'Screwdriver', 23.79),
('B_1', 'Pliers', 14.99),
('B_2', 'Wrench', 31.49),
('C_1', 'Power Drill', 109.99),
('C_2', 'Screws', 8.99);

SELECT sales.customer_id, sales.item_code, customers.first_name, customers.last_name
FROM sales
JOIN customers
ON sales.customer_id = customers.customer_id
JOIN items
ON items.item_code = sales.item_code;