---
# System prepended metadata

title: 'MySQL: Go from SQL Beginner to Expert'
tags: [Udemy, Data, Software Engineering, Database]

---

# MySQL: Go from SQL Beginner to Expert
###### tags: `Data` `Udemy` `Database` `Software Engineering`

## Creating Database and Table
### Database
```sql
CREATE DATABASE soap_store;
```
### Table
```sql
CREATE TABLE cats (
    name VARCHAR(50),
    age INT
);
 
CREATE TABLE dogs (
    name VARCHAR(50),
    breed VARCHAR(50),
    age INT
);
```


## Inserting Data
:::warning
Must use <''> instead of <""> to insert VARCHAR data.
:::
### Basic
```sql
INSERT INTO cats(name, age)
VALUES 
    ('Kevin', 18);
```

### Multiple
```sql
INSERT INTO cats(name, age)
VALUES 
    ('Kevin', 18)
    ('David', 20);
```
### Working with NOT NULL
We can prevent the users from inserting null data by setting **NOT NULL** while we are creating the tables.

```sql
CREATE TABLE cats (
    name VARCHAR(50) NOT NULL,
    age INT NOT NULL
);
```
### Primary Keys
#### Why and How
Since the identical data can not be recognized, we use **unique primary keys** to mark each data.

```sql
CREATE TABLE unique_cats (
    cat_id INT,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    PRIMARY KEY (cat_id)
);

```
#### Auto Increment
It's annoying to insert primary keys everytime, so we can set **AUTO INCREMENT** to generate the keys automatically.
```sql
CREATE TABLE unique_cats2 (
    cat_id INT AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    PRIMARY KEY (cat_id)
);
```
## CRED
### Read
#### Select all columns
```sql
SELECT * FROM cats;
-- * -> give me all columns
```
#### Select one single column
```sql
SELECT name FROM cats;
```

#### Select multiple certain columns
```sql
SELECT name, age FROM cats;
```

#### WHERE clause
```sql
SELECT name, age FROM cats WHERE age=4;
```
#### Alias
The **AS** name is only temporary for this query.
```sql
SELECT cat_id AS id, age FROM cats WHERE cat_id=age;
```

### Update

```sql
UPDATE cats SET breed='Shorthair'
WHERE breed='Tabby';
```


#### A Good Rule of Updating
To prevern any unexpected update, we must SELECT the tables before we update.

#### Error

> You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option ....

##### Resolve
```sql
SET SQL_SAFE_UPDATES = 0;
```

### Delete

```sql
DELETE FROM cats WHERE name='Egg';
```

## String Functions
### CONCAT
```sql
SELECT CONCAT(author_fname, '!', author_lname) as fullname from books;
    -> author_fname!author_lname
```

### CONCAT_WS
```sql
SELECT CONCAT_WS('!', author_fname, author_lname, 'hi') as fullname from books;
    -> author_fname!author_lname!hi
```

### SUBSTRING
```sql
SELECT SUBSTRING('Hello World', 1, 4);
    -> hell
```

### REPLACE
```sql
SELECT REPLACE('Hello World', 'Hell', '%$#@');
SELECT REPLACE('Hello World', 'l', '7');
SELECT REPLACE(title, 'e ', '3') FROM books;
```

### CHAR_LENGTH
```sql
SELECT CONCAT(author_lname, ' is ', CHAR_LENGTH(author_lname), ' characters long') FROM books;
```

### UPPER & LOWER
```sql
SELECT CONCAT('MY FAVORITE BOOK IS ', UPPER(title)) FROM books;
SELECT CONCAT('MY FAVORITE BOOK IS ', LOWER(title)) FROM books;
```

### Others
```sql
SELECT INSERT('Hello Bobby', 6, 0, 'There'); 
SELECT LEFT('omghahalol!', 3);
SELECT RIGHT('omghahalol!', 4);
SELECT REPEAT('ha', 4);
SELECT TRIM('  pickle  ');
```

## Refining Selections
### DISTINCT
This sentence must **go after SELECT and before column name**.
```sql
SELECT DISTINCT CONCAT((author_lname), ' ', author_fname) from books;
```

### ORDER BY
This sentence can **after SELECT**.
```sql
SELECT author_lname, released_year FROM books ORDER BY released_year;
SELECT author_lname, released_year FROM books ORDER BY released_year desc;
```

### More On ORDER BY
Can use index instead of column name ob ORDER BY.
```sql
SELECT author_lname, released_year FROM books ORDER BY 2;
```

ORDER BY supports the users to include AS;
```sql
SELECT concat(author_lname, ' ', author_fname) AS author_name 
FROM books 
ORDER BY author_name;
```

### LIMIT
This sentence must **go after SELECT and before column name**.
```sql
SELECT DISTINCT CONCAT((author_lname), ' ', author_fname) from books;
```

### LIKE
#### Why LIKE?
> There is a book I am looking for...
> But I can't remember the title!
> But I know the author's first name is David or Dan or Dave or something with a D and then an A...

This sentence work with WHERE together.

```sql
SELECT title FROM books WHERE author_fname LIKE '%da%';
SELECT title FROM books WHERE author_fname LIKE '%n';
```

'_' means exactly one character.
```sql
SELECT title FROM books WHERE author_fname LIKE '_';
SELECT title FROM books WHERE author_fname LIKE '____';
```

#### Escaping Wildcard
```sql
SELECT title FROM books WHERE author_fname LIKE '%/%%';
```

## Aggregate Functions
### COUNT
> How many books are in the database???

Didn't play well with other things.
```sql
SELECT COUNT(*) FROM books;
```
> How many distinct books are in the database???
```sql
SELECT COUNT(DISTINCT author_fname) FROM books;
```
> How many title contain 'the'???
```sql
SELECT COUNT(*) FROM books WHERE title LIKE "%the%";
```
### GROUP BY 
Really important stuff!!!! 
GROUP BY summarizes and aggregrates identical data into single row.
> COUNT how many books each author has written.
```sql
SELECT author_lname, count(*) FROM books GROUP BY author_lname;

SELECT 
    author_lname, COUNT(*) AS books_written
FROM
    books
GROUP BY author_lname
ORDER BY books_written DESC;
```
#### Multiple Columns
```sql
SELECT CONCAT(author_fname, ' ', author_lname) AS author,  COUNT(*)
FROM books
GROUP BY author;
```

### MIN & MAX
```sql
SELECT MAX(pages) FROM books;
SELECT MIN(author_lname) FROM books;
```

### Subquries
> What if I want the title of the longest book?
```sql
SELECT title, pages FROM books
WHERE pages = (SELECT MAX(pages) FROM books);
```

## Revisiting Data types
### CHAR vs. VARCHAR
#### CHAR
CHAR has **fixed length**

```sql
CHAR(3)
```
* Only 3 characters allowed
* If the characters smaller than 3, database would expand it by using the space to fit 3 characters.
* CHAR is faster for fixed length text e.g. State Abbreviations: CA, NY

### DECIMAL
```sql
DECIMAL(5, 2)
-> 999.99
   |||||| -> 5
       || -> 2
```

### FLOAT vs. DOUBLE
|        | Memory needed |
| ------ | ------------- |
| FLOAT  | 4 Bytes       |
| DOUBLE | 8 Bytes       |

### DATES, TIMES, & DATETIME
#### DATES
Format: 'YYYY-MM-DD'
#### TIMES
Format: 'HH:MM:SS'
It's not a time of a day, it's just an amount of time.
#### DATETIME
Format: 'YYYY-MM-DD HH:MM:SS'
#### EXAMPLE
```sql
CREATE TABLE people (
    name VARCHAR(100),
    birthdate DATE,
    birthtime TIME,
    birthdt DATETIME
);
 
INSERT INTO people(name, birthdate, birthtime, birthdt)
VALUES(
    'Elton', 
    '1996-11-26', 
    '11:00:00', 
    '1996-11-26 11:00:00'
)
```
### CURDATE, CURTIME, & NOW
```sql
INSERT INTO people (name, birthdate, birthtime, birthdt)
VALUES ('Hazel', CURDATE(), CURTIME(), NOW());
```

### DATE Functions & TIME Functions
#### DATE Functions
```sql
SELECT 
    birthdate,
    MONTHNAME(birthdate),
    YEAR(birthdate)
FROM people;
```

#### TIME Functions
```sql
SELECT 
    birthdt,
    MONTH(birthdt),
    DAY(birthdt),
    HOUR(birthdt),
    MINUTE(birthdt)
FROM people;
```
### DATE Math
#### DATEDIFF
```sql
SELECT 
    birthdate, DATEDIFF(CURDATE(), birthdate)
FROM people;
```
#### DATE_ADD
```sql
SELECT DATE_ADD(CURDATE(), INTERVAL 1 YEAR);
```
#### Syntax
```sql
SELECT NOW() + INTERVAL 1 YEAR;
```

### TIMESTAMPS
Format: 'YYYY-MM-DD HH:MM:SS'
TIMESTAMPS has the smallest cost because of it's range.

### DEFAULT & ON UPDATE TIMESTAMPS
```sql
CREATE TABLE captions(
	text VARCHAR(100),
	created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

UPDATE captions_2 SET text='i love live!!!';
SELECT * FROM captions_2;
```

## Logical Operators

### NOT Like
```sql
SELECT * FROM books
WHERE title NOT LIKE '%e%';
```
### AND
```sql
SELECT title, author_lname, released_year FROM books
WHERE released_year > 2010
AND author_lname = 'Eggers';
```
### OR
```sql
SELECT title, author_lname, released_year FROM books
WHERE author_lname='Eggers' OR
released_year > 2010;
```

### BETWEEN
```sql
SELECT title, released_year FROM books
WHERE released_year <= 2015
AND released_year >= 2004;
 
SELECT title, released_year FROM books
WHERE released_year BETWEEN 2004 AND 2014;
```

### Comparing Dates
```sql
SELECT * FROM people WHERE HOUR(birthtime)
BETWEEN 12 AND 16;
```

### IN 
```sql
SELECT title, author_lname FROM books
WHERE author_lname IN ('Carver', 'Lahiri', 'Smith');
```

### CASE
```sql
SELECT title, released_year,
CASE
    WHEN released_year >= 2000 THEN 'modern lit'
    ELSE '20th century lit' 
END AS genre
FROM books;
```

### Exercise
Make this happen!
| author_fname | author_lname | COUNT|
| -------- | -------- | -------- |
| Jhumpa       | Lahiri         | 2 books |
| Neil         | Gaiman         | 3 books |
| Dave         | Eggers         | 3 books |
| Michael      | Chabon         | 1 book  |
| Patti        | Smith          | 1 book  |
| Raymond      | Carver         | 2 books |
| Don          | DeLillo        | 1 book  |
| John         | Steinbeck      | 1 book  |
| David        | Foster Wallace | 2 books |
| Dan          | Harris         | 1 book  |
| Freida       | Harris         | 1 book  |
| George       | Saunders       | 1 book  |

```sql
SELECT author_fname, author_lname,
	CASE
        WHEN COUNT(*) = 1 THEN '1 book'
        ELSE CONCAT(COUNT(*), ' books')
	END AS count
FROM books
WHERE author_lname IS NOT NULL
GROUP BY author_fname, author_lname;
```
## More Constraints
### UNIQUE & CHECK
```sql
CREATE TABLE contacts (
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(15) NOT NULL UNIQUE,
    age INT CHECK (age > 0)
);
```
### Named Constraints
```sql
CREATE TABLE users2 (
    username VARCHAR(20) NOT NULL,
    age INT,
    CONSTRAINT age_not_negative CHECK (age >= 0)
);
```
### Multiple Column Constraints
```sql
CREATE TABLE companies (
    name VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL,
    CONSTRAINT name_address UNIQUE (name , address)
);
 
CREATE TABLE houses (
  purchase_price INT NOT NULL,
  sale_price INT NOT NULL,
  CONSTRAINT sprice_gt_pprice CHECK(sale_price >= purchase_price)
);
```
## Alter Table
### Adding Columns
```sql
ALTER TABLE companies 
ADD COLUMN phone VARCHAR(15);
 
ALTER TABLE companies
ADD COLUMN employee_count INT NOT NULL DEFAULT 1;
```

### Dropping Columns
```sql
ALTER TABLE companies DROP COLUMN phone;
```

### Renaming
```sql
ALTER TABLE companies
RENAME COLUMN name TO company_name;
```

### Renaming
```sql
ALTER TABLE companies
MODIFY company_name VARCHAR(100) DEFAULT 'unknown';
```

### Adding Constraint
```sql
ALTER TABLE houses 
ADD CONSTRAINT positive_pprice CHECK (purchase_price >= 0);
```

## One to Many
### Data is Messy!!!!
Real world data is **messy** and **interrelated**.
#### Relationship Basics
1. One to One Relationship
2. One to Many Relationship
3. Many to Many Relationship

### One to Many Relationship
####  Why Many?
We could use one table to store.....
![](https://i.imgur.com/YdZKjhY.png)
Some information is duplicated -> **BAD IDEA!!**

#### One to Many
We can fix the above problem by seperating it to the couple tables.
![](https://i.imgur.com/PEr8OCZ.png)

### PRIMARY & FOREIGN KEY
#### PRIMARY KEY
![](https://i.imgur.com/yjhtjkl.png)

#### FOREIGN KEY
![](https://i.imgur.com/OWyqwC6.png)



#### Work with FOREIGN KEY
```sql
CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(50)
);

CREATE TABLE orders(
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE,
    amount DECIMAL(8,2),
    customer_id INT,
    FOREIGN KEY (customers_id) REFERENCES customers(id)
);

INSERT INTO customers (first_name, last_name, email) 
VALUES ('Boy', 'George', 'george@gmail.com'),
       ('George', 'Michael', 'gm@gmail.com'),
       ('David', 'Bowie', 'david@gmail.com'),
       ('Blue', 'Steele', 'blue@gmail.com'),
       ('Bette', 'Davis', 'bette@aol.com');
       
       
INSERT INTO orders (order_date, amount, customer_id)
VALUES ('2016-02-10', 99.99, 1),
       ('2017-11-11', 35.50, 1),
       ('2014-12-12', 800.67, 2),
       ('2015-01-03', 12.50, 2),
       ('1999-04-11', 450.25, 5);
```
### On Delete Cascade
```sql
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE,
    amount DECIMAL(8 , 2 ),
    customer_id INT,
    FOREIGN KEY (customer_id)
        REFERENCES customers (id)
        ON DELETE CASCADE
);
```


## Joins
### Cross Joins

```sql
SELECT * FROM orders WHERE customer_id = 1;
SELECT * FROM orders 
WHERE customer_id = (SELECT id FROM customers WHERE last_name = 'George');
```
### Inner Joins
![](https://i.imgur.com/H13mtve.png)

```sql
SELECT * FROM orders
JOIN customers ON customers.id=customer_id;
```
With GROUP BY

```sql
SELECT first_name, last_name, SUM(amount) as total FROM orders
JOIN customers ON customers.id=customer_id
GROUP BY first_name, last_name
ORDER BY total;
```

### Left Joins
![](https://i.imgur.com/s8gIM0f.png)

```sql
SELECT first_name, last_name, order_date, amount FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id;
```

### Right Joins
```sql
SELECT first_name, last_name, order_date, amount FROM customers
RIGHT JOIN orders ON orders.customer_id = customers.id;
```

### Exercise
![](https://i.imgur.com/FJI3GZp.png)

```sql
SELECT first_name, AVG(IFNULL(grade, 0)),
CASE 
    WHEN AVG(IFNULL(grade, 0)) > 60 THEN 'PASSING'
    ELSE 'FAILING'
END AS passing_status
FROM students
LEFT JOIN papers ON papers.student_id = students.id
GROUP BY first_name;
```

## Many to Many
![](https://i.imgur.com/fZezjt4.png)

### Exercise 1
![](https://i.imgur.com/j1hm2Nk.png)

```sql
SELECT title as unreviews_series FROM reviews
RIGHT JOIN series 
	ON reviews.series_id = series.id
WHERE rating IS NULL;
```

### Exercise 2
![](https://i.imgur.com/BMh6ABs.png)

```sql
SELECT genre, avg(rating) FROM reviews
INNER JOIN series 
	ON reviews.series_id = series.id
GROUP BY genre;
```

### Exercise 3
![](https://i.imgur.com/oJLLAxR.png)

```sql
SELECT first_name, last_name, COUNT(rating) as count, MAX(IFNULL(rating, 0)) as max, 
	MIN(IFNULL(rating, 0)) as min, round(AVG(IFNULL(rating, 0)), 2) as average, 
	CASE
		WHEN  COUNT(rating) > 0 THEN 'active'
        ELSE 'unactive'
    END AS status
FROM reviews
RIGHT JOIN reviewers
	ON reviewers.id = reviews.reviewer_id
GROUP BY first_name, last_name;
```

## Views, Modes & Views
### Views
This function can store a virtual table.
```sql
CREATE VIEW full_reviews AS
SELECT title, released_year, genre, rating, first_name, last_name FROM reviews
JOIN series ON series.id = reviews.series_id
JOIN reviewers ON reviewers.id = reviews.reviewer_id;

SELECT * FROM full_reviews;
```

### GROUP BY HAVING
```sql
SELECT 
    title, 
    AVG(rating),
    COUNT(rating) AS review_count
FROM full_reviews 
GROUP BY title HAVING COUNT(rating) > 1;
```

### WITH ROLLUP
```sql
SELECT title, AVG(rating) FROM full_reviews GROUP BY title WITH ROLLUP;
```



### SQL Modes
```sql
-- To View Modes:
SELECT @@GLOBAL.sql_mode;
SELECT @@SESSION.sql_mode;
 
-- To Set Them:
SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';
```


## Window Functions
Window functions perform aggregate operations on groups of rows, but they produce a result **FOR EACH ROW**.
![](https://i.imgur.com/2KUBwfn.png)

### OVER
```sql
SELECT 
    emp_no, 
    department, 
    salary, 
    (salary) OVER() 
    FROM employees;
```

### PARTITION BY
```sql
SELECT 
    emp_no, 
    department, 
    salary, 
    AVG(salary) OVER(PARTITION BY department) as department_avg, 
    AVG(salary) OVER() AS company_avg 
FROM employees;
```

### ORDER BY
```sql
SELECT 
    emp_no, 
    department, 
    salary, 
    AVG(salary) OVER(PARTITION BY department ORDER BY salary) as department_avg, 
    AVG(salary) OVER() AS company_avg 
FROM employees;
```



### RANKING
```sql
SELECT 
    emp_no, 
    department, 
    salary,
    ROW_NUMBER() OVER(PARTITION BY department ORDER BY SALARY DESC) as dept_row_number,
    RANK() OVER(PARTITION BY department ORDER BY SALARY DESC) as dept_salary_rank,
    RANK() OVER(ORDER BY salary DESC) as overall_rank,
    DENSE_RANK() OVER(ORDER BY salary DESC) as overall_dense_rank,
    ROW_NUMBER() OVER(ORDER BY salary DESC) as overall_num
FROM employees ORDER BY overall_rank;
```

### NTILE
```sql
SELECT 
    emp_no, 
    department, 
    salary,
    NTILE(4) OVER(PARTITION BY department ORDER BY salary DESC) AS dept_salary_quartile,
    NTILE(4) OVER(ORDER BY salary DESC) AS salary_quartile
FROM employees;
```

### FIRST_VALUE
```sql
SELECT 
    emp_no, 
    department, 
    salary,
    FIRST_VALUE(emp_no) OVER(PARTITION BY department ORDER BY salary DESC) as highest_paid_dept,
    FIRST_VALUE(emp_no) OVER(ORDER BY salary DESC) as highest_paid_overall
FROM employees;
```

### LEAD & LAG
```sql
SELECT 
    emp_no, 
    department, 
    salary,
    salary - LAG(salary) OVER(ORDER BY salary DESC) as salary_diff
FROM employees;
 
SELECT 
    emp_no, 
    department, 
    salary,
    salary - LAG(salary) OVER(PARTITION BY department ORDER BY salary DESC) as dept_salary_diff
FROM employees;
```

## Instagram Challenges
### Set Up
```sql
DROP DATABASE IF EXISTS ig_clone;
CREATE DATABASE ig_clone;
USE ig_clone; 

CREATE TABLE users (
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE photos (
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    image_url VARCHAR(255) NOT NULL,
    user_id INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY(user_id) REFERENCES users(id)
);

CREATE TABLE comments (
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    comment_text VARCHAR(255) NOT NULL,
    photo_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY(photo_id) REFERENCES photos(id),
    FOREIGN KEY(user_id) REFERENCES users(id)
);

CREATE TABLE likes (
    user_id INTEGER NOT NULL,
    photo_id INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY(user_id) REFERENCES users(id),
    FOREIGN KEY(photo_id) REFERENCES photos(id),
    PRIMARY KEY(user_id, photo_id)
);

CREATE TABLE follows (
    follower_id INTEGER NOT NULL,
    followee_id INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY(follower_id) REFERENCES users(id),
    FOREIGN KEY(followee_id) REFERENCES users(id),
    PRIMARY KEY(follower_id, followee_id)
);

CREATE TABLE tags (
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  tag_name VARCHAR(255) UNIQUE,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE photo_tags (
    photo_id INTEGER NOT NULL,
    tag_id INTEGER NOT NULL,
    FOREIGN KEY(photo_id) REFERENCES photos(id),
    FOREIGN KEY(tag_id) REFERENCES tags(id),
    PRIMARY KEY(photo_id, tag_id)
);

```

### Challenge 1
We want to reward our users who have been around the longest.
```sql
SELECT 
    username
FROM 
    users
ORDER BY created_at LIMIT 5;
```

### Challenge 2
What day of the week do must users register on?
(We need to figure out when to schedule an ad compaign.)
```sql
SELECT 
    DAYNAME(created_at), 
    COUNT(DAYNAME(created_at))
FROM 
    users 
GROUP BY DAYNAME(created_at)
ORDER BY COUNT(DAYOFWEEK(created_at)) DESC;
```

### Challenge 3
We want to target our inactive users with an email compaign.
(Find the users who never post a photo)
```sql
SELECT 
    username 
FROM 
    photos
RIGHT JOIN users 
ON photos.user_id = users.id
WHERE photos.id IS NULL;
```


### Challenge 4
We're running a new contest to see who can get the most likes on a single photo.
```sql
SELECT 
    image_url, 
    count(*) AS total
FROM 
    photos
INNER JOIN likes ON likes.photo_id = photos.id
GROUP BY photos.id
ORDER BY total DESC;
```

### Challenge 5
How many times does the average user post?
```sql
SELECT (SELECT Count(*) 
        FROM   photos) / (SELECT Count(*) 
                          FROM   users) AS avg; 
```

### Challenge 6
What are the most commonly used hashtags?
```sql
SELECT 
	tag_name, 
    COUNT(*) as total 
FROM  
	tags
INNER JOIN photo_tags ON photo_tags.tag_id = tags.id
GROUP BY tag_name
ORDER BY total DESC
LIMIT 5;
```

### Challenge 7
Find users who have liked every single photo on the site.
```sql
SELECT 
	username, COUNT(*) as num_likes
FROM  
	likes
INNER JOIN users ON users.id = likes.user_id
GROUP BY username
HAVING num_likes = (SELECT Count(*) 
                    FROM   photos); 
```

## Work with NODE
### Connecting Node to MySQL

Install the package.
:::danger
Version error comes up if we install MySQL.
:::
``` bash
npm install mysql2
```
 Connect node to MySQL.
```javascript
var mysql = require('mysql');
 
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',     // your root username
  password : 'password', // your password
  database : 'join_us'   // the name of your db
});

```
Test!!
```javascript
var q = 'SELECT CURTIME() as time, CURDATE() as date, NOW() as now';
connection.query(q, function (error, results, fields) {
  if (error) throw error;
  console.log(results[0].time);
  console.log(results[0].date);
  console.log(results[0].now);
});
```
### Inserting data to MySQL
```javascript
var person = {
    email: faker.internet.email(),
    created_at: faker.date.past()
};
 
var end_result = connection.query('INSERT INTO users SET ?', person, function(err, result) {
  if (err) throw err;
  console.log(result);
});

```
### The Code To INSERT 500 Random Users

```javascript
var mysql = require('mysql2');
var faker = require('faker');
 
 
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  database : 'join_us'
});
 

var data = [];
for(var i = 0; i < 500; i++){
    data.push([
        faker.internet.email(),
        faker.date.past()
    ]);
}
 
 
var q = 'INSERT INTO users (email, created_at) VALUES ?';
 
connection.query(q, [data], function(err, result) {
  console.log(err);
  console.log(result);
});
 
connection.end();
```