Download and install docker
https://hub.docker.com/editions/community/docker-ce-desktop-mac?tab=description
Download and install MySQLWorkBench
Note you will need to right click and select open when it tells you "can’t be opened because Apple cannot check it for malicious software."
https://www.mysql.com/products/workbench/
docker-compose -f dockerImage.yaml up
Hostname: 127.0.0.1 Port: 3306 user: root password: password
Server -> Data Import -> Import from self contained file -> devDay.sql
Users id (PK,Int) firstname(String) lastname(String) username(String)
Phonnumbers user_id(Fk,Int) phonenumber(String)
Emails user_id(Fk,Int) email(String)
Product id(PK,Int) name(String) price(Double) category(Int) created_at(Date)
Orders id(Pk,Int) user_id(Fk,Int) product_id(Fk,Int) purchase_date(Date)
A Common Table Expression (CTE) is the result set of a query which exists temporarily and for use only within the context of a larger query.
Syntax
SELECT t1.data1,t2.data2 FROM Table1 as t1 JOIN Table2 as t2 ON
Table1.someKey = Table2.someKey
Joins are used to query related data from different tables.
Syntax
SELECT Table1.data1,Table2.data2 FROM Table1 JOIN Table2 ON
Table1.someKey = Table2.someKey
Inner Join
Inner Join willl only show rows that match from both tables
Left Join
Left Join will show all matching rows from the 'left' (first) table and null for the non matching from the 'right' (second) table
Right Join
Right Join does the opposite of Left Join and will return null on the 'left' (first) table and all matching rows on the 'right' (second) table
Query Users and there email addresses using a left and inner join use the 'users' table as the left table and 'emails' as the right
Answer
SELECT u.firstname, u.lastname,e.email
FROM users u
LEFT JOIN emails e on u.id = e.user_id
Query Users and there email addresses using a left and inner join use the 'emails' table as the left table and 'users' as the right
Answer
SELECT u.firstname, u.lastname,e.email
FROM emails e
LEFT JOIN users u on u.id = e.user_id
Syntax
SELECT data1, data2, aggregate_function(data3)
FROM table
Aggregate Functions AVG() COUNT() COUNT(DISTINCT) MAX() MIN() SUM()
Get the number of unquie products ordered from the order table
Answer
SELECT count(distinct product_id)
From orders o
Create a query to get the total price of all the products
Answer
SELECT SUM(p.price) as total_price
From products p;
GROUP BY returns one row for each group. In other words, it reduces the number of rows in the result set. It is usually used with an aggregate function
Syntax
SELECT data1, data2, aggregate_function(data3)
FROM table
GROUP BY data1
Create a query that shows who has the most orders
Answer
SELECT u.firstname, COUNT(*) as num_orders FROM users u
left join orders o on u.id = o.user_id
group by u.firstname
order by num_orders desc;
create a query that shows the total purchase order of ever user
Answer
SELECT u.firstname, SUM(p.price) as total_purchase
From users u, products p
join orders o on p.id
where u.id = o.user_id
group by u.firstname;
Get the number of unquie orders from the order table
Answer
SELECT u.firstname, p.name, count(o.id)
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
GROUP BY u.firstName, p.name;