All you need to know about mysql - Database

2020-08-01 7083 words 34 minutes

/mysql-database-tutorials/featured-image.jpg

Contents

Discover and Learn CRUD (Create, Read, Update, Delete) operation and the concept behind database.

1 INSTALLATION

1
2
sudo mysql -u root -p
PASSWORD: yourpassword

1
2
3
4
5
6
use mysql;
UPDATE user SET plugin='mysql_native_password' WHERE user='root'
UPDATE mysql.user set authentication_string=PASSWORD('yourpassword') where user='root';
FLUSH PRIVILEGES;
quit;

2 Create Database

1
CREATE DATABASE db_name;

1
SHOW DATABASES;

1
DROP DATABASE db_name;

1
USE e_com;

1
SELECT database();

Info

Tables are used to hold the data.
Actual information of data is in ROW.
COLUMN holds headers.
Numeric datatype, String datatype, Date datatype
INT : Whole number
varchar() : String or Text

2.1 Create table

1
2
3
4
5
CREATE TABLE dogs(
    name VARCHAR(100),
    breed VARCHAR(100),
    age INT
    );

1
SHOW TABLES;

1
SHOW COLUMNS FROM dogs;

1
DESC dogs;

1
DROP TABLE dogs;

Info

SELECT database(); shows selected database.

1
INSERT INTO dogs(name, age) VALUES('John', 5);

Insert multiple data

1
INSERT INTO dogs(name, age) VALUES('John', 5), ('pop', 6);

NOT NULL

Info

all the values for headers needs to be defined explicitly.

1
2
3
4
5
CREATE TABLE dogs(
    name VARCHAR(100) NOT NULL,
    breed VARCHAR(100) NOT NULL,
    age INT NOT NULL
    );

DEFAULT values

1
CREATE TABLE dog3( name VARCHAR(100) DEFAULT 'DEFALUT_DOG3', AGE INT DEFAULT 99);

1
INSERT INTO dog3 (name) VALUES('Puppy');

1
SELECT * FROM dog3;

name	AGE
Puppy	99

1 row in set (0.00 sec)

1
INSERT INTO dog3 (age) VALUES(10);

1
SELECT * FROM dog3;

name	AGE
Puppy	99
DEFALUT_DOG3	10

2 rows in set (0.00 sec)

key

Info

Unique Identifier

1
2
3
4
5
6
CREATE TABLE dog4(
  dog_id INT NOT NULL,
	name VARCHAR(100),
	age INT,
	PRIMARY KEY(dog_id)
);

1
2
3
INSERT INTO dog4(dog_id, name, age) VALUES(1, 'D4', 5);
INSERT INTO dog4(dog_id, name, age) VALUES(2, 'D4', 5);
INSERT INTO dog4(dog_id, name, age) VALUES(3, 'D4', 5);

1
SELECT * FROM dog4

dog_id	name	age
1	D4	5
2	D4	5
3	D4	5

3 rows in set (0.00 sec)

Info

AUTO INCREMENT

1
2
3
4
5
6
CREATE TABLE dog5(
	dog_id INT NOT NULL AUTO_INCREMENT,
	name VARCHAR(100),
	age INT,
	PRIMARY KEY(dog_id)
);

1
INSERT INTO dog5(name, age) VALUES('A', 6), ('B', 8), ('C', 10);

1
SELECT * FROM dog5;

dog_id	name	age
1	A	6
2	B	8
3	C	10

3 rows in set (0.01 sec)

EXERCISE:

1
CREATE DATABASE BEX;

1
USE BEX;

1
2
3
4
5
6
7
8
9
CREATE TABLE class(
    ID INT NOT NULL AUTO_INCREMENT,
    last_name VARCHAR(100),
    first_name VARCHAR(100),
    middle_name VARCHAR(100),
    age INT NOT NULL,
    Marks INT NOT NULL
		PRIMARY KEY(ID)
    );

1
DESC class;

Field	Type	Null	Key	Default	Extra
ID	int(11)	NO	PRI	NULL	auto_increment
last_name	varchar(100)	YES		NULL
first_name	varchar(100)	YES		NULL
middle_name	varchar(100)	YES		NULL
age	int(11)	NO		NULL
Marks	int(11)	NO		NULL

6 rows in set (0.01 sec)

1
INSERT INTO class(last_name, first_name, middle_name, age, Marks) VALUES('BHUJEL', 'SUDIP', '', 21, 89);

1
SELECT * FROM class;

ID	last_name	first_name	middle_name	age	Marks
1	BHUJEL	SUDIP		21	89

1 row in set (0.00 sec)

3 CRUD

3.1 Data preparing section

1
CREATE DATABASE demo_dog;

1
2
3
4
5
CREATE TABLE dogs( dog_id INT NOT NULL AUTO_INCREMENT,
		name VARCHAR(100), breed VARCHAR(100),
		age INT,
		PRIMARY KEY(dog_id)
);

1
2
3
4
5
6
INSERT INTO dogs(name, breed, age)
VALUES('Name 1', 'Breed 1', 1),
		('Name 2', 'Breed 2', 2),
		('Name 3', 'Breed 3', 3),
		('Name 4', 'Breed 4', 4),
		('Name 5', 'Breed 5', 5);

1
DESC dogs;

Field	Type	Null	Key	Default	Extra
dog_id	int(11)	NO	PRI	NULL	auto_increment
name	varchar(100)	YES		NULL
breed	varchar(100)	YES		NULL
age	int(11)	YES		NULL

4 rows in set (0.00 sec)

3.2 SELECT

1
SELECT * FROM dogs;

dog_id	name	breed	age
1	Name 1	Breed 1	1
2	Name 2	Breed 2	2
3	Name 3	Breed 3	3
4	Name 4	Breed 4	4
5	Name 5	Breed 5	5

5 rows in set (0.00 sec)

Info

SELECT * FROM tables_name; COMMAND + EXPRESSION + ADRESS

1
SELECT dog_id,name,breed FROM dogs;

dog_id	name	breed
1	Name 1	Breed 1
2	Name 2	Breed 2
3	Name 3	Breed 3
4	Name 4	Breed 4
5	Name 5	Breed 5

5 rows in set (0.00 sec)

3.3 WHERE

1
SELECT dog_id,name,breed FROM dogs WHERE age = 4;

dog_id	name	breed
4	Name 4	Breed 4

1 row in set (0.00 sec)

1
SELECT dog_id,name,breed FROM dogs WHERE name = 'Name 5';

dog_id	name	breed
5	Name 5	Breed 5

1 row in set (0.00 sec)

3.3 ALIASES

Info

Display with Different header, header_name of Table is not changed.

1
SELECT dog_id AS ID FROM dogs;

ID
1
2
3
4
5

5 rows in set (0.00 sec)

1
SELECT name AS dog_name FROM dogs;

dog_name
Name 1
Name 2
Name 3
Name 4
Name 5

5 rows in set (0.00 sec)

3.4 UPDATE

Info

ACTION + dogs (table_name) + SET_this_breed where to look.

1
UPDATE dogs SET breed = 'Updated_breed_1' WHERE breed = 'Breed 1';

1
SELECT * FROM dogs;

dog_id	name	breed	age
1	Name 1	Updated_breed_1	1
2	Name 2	Breed 2	2
3	Name 3	Breed 3	3
4	Name 4	Breed 4	4
5	Name 5	Breed 5	5

5 rows in set (0.00 sec)

3.5 DELETE

1
DELETE FROM dogs WHERE age = 5

1
SELECT * FROM dogs;

dog_id	name	breed	age
1	Upadated_name_1	Updated_breed_1	1
2	Name 2	Breed 2	2
3	Name 3	Breed 3	3
4	Name 4	Breed 4	4

4 rows in set (0.00 sec)

DELETE tables contents

1
DELETE FROM shoes;

4 All About String

1
CREATE DATABASE stationery_books;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
USE stationery_books;

CREATE TABLE books
	(
		book_id INT NOT NULL AUTO_INCREMENT,
        title VARCHAR(100),
        author_fname VARCHAR(100),
        author_lname VARCHAR(100),
        released_year INT,
        stock_quantity INT,
        pages INT,
        PRIMARY KEY(book_id)
	);

INSERT INTO books (title, author_fname, author_lname, released_year, stock_quantity, pages)
VALUES
('The Namesake', 'Jhumpa', 'Lahiri', 2003, 32, 291),
('Norse Mythology', 'Neil', 'Gaiman', 2016, 43, 304),
('American Gods', 'Neil', 'Gaiman', 2001, 12, 465),
('Interpreter of Maladies', 'Jhumpa', 'Lahiri', 1996, 97, 198),
('A Hologram for the King: A Novel', 'Dave', 'Eggers', 2012, 154, 352),
('The Circle', 'Dave', 'Eggers', 2013, 26, 504),
('The Amazing Adventures of Kavalier & Clay', 'Michael', 'Chabon', 2000, 68, 634),
('Just Kids', 'Patti', 'Smith', 2010, 55, 304),
('A Heartbreaking Work of Staggering Genius', 'Dave', 'Eggers', 2001, 104, 437),
('Caraline',  'Neil', 'Gaiman', 2003, 100, 208),
('What We Talk About When We Talk About Love: Stories ',  'Raymond', 'Carver', 1981, 23, 176),
('Where I\'m Calling From: Selected Stories',  'Raymond', 'Carver', 1989, 12, 526),
('White Noise', 'Done', 'DeLillo', 1985, 49, 320),
('Cannery Row',  'John', 'Steinbeck', 1945, 95, 181),
('Oblivion: Stories',  'David', 'Foster Wallace', 2004, 172, 329),
('Consider the Lobster',  'David', 'Foster Wallace', 2005, 92, 343);

4.1 CONCAT()

1
SELECT CONCAT(author_fname, ' ', author_lname) AS 'Full Name' FROM books;

With Aliases

1
SELECT author_fname AS First, author_lname AS Second, CONCAT(author_fname, ' ', author_lname) AS 'Full Name' FROM books;

4.2 CONCAT_WS()

1
SELECT CONCAT_WS(' ', author_fname, author_lname) AS 'Full Name' FROM books;

4.3 SUBSTRING()

Indexing starts from 1.

1
SELECT SUBSTRING('HELLO WORLD', 1, 4);

SUBSTRING(‘HELLO WORLD’, 1, 4)
HELL

1 row in set (0.00 sec)

1
SELECT SUBSTRING('HELLO WORLD', -2);

SUBSTRING(‘HELLO WORLD’, -2)
LD

1 row in set (0.00 sec)

1
SELECT SUBSTRING(title, 2) FROM books WHERE book_id < 5;

SUBSTRING(title, 2)
he Namesake
orse Mythology
merican Gods
nterpreter of Maladies

4 rows in set (0.00 sec)

4.4 CONCAT(SUBSTRING(), ..)

1
SELECT CONCAT(SUBSTRING(author_fname, 3), '_', title) FROM books WHERE book_id < 6;

4.5 REPLACE()

1
SELECT REPLACE('HELLO GUYS', 'HELLO', 'BUY');

REPLACE(‘HELLO GUYS’, ‘HELLO’, ‘BUY’)
BUY GUYS

1 row in set (0.00 sec)

1
SELECT REPLACE(title, 'T', '9') FROM books WHERE book_id < 5;

REPLACE(title, ‘T’, ‘9’)
9he Namesake
Norse Mythology
American Gods
Interpreter of Maladies

4 rows in set (0.00 sec)

4.6 REVERSE()

1
SELECT REVERESE('HELLO')

REVERSE(‘HELLO’)
OLLEH

1 row in set (0.00 sec)

1
SELECT REVERSE(title) FROM books WHERE book_id < 5;

REVERSE(title)
ekasemaN ehT
ygolohtyM esroN
sdoG naciremA
seidalaM fo reterpretnI

4 rows in set (0.00 sec)

1
SELECT CHAR_LENGTH(title) FROM books WHERE book_id<4;

CHAR_LENGTH(title)
12
15
13

3 rows in set (0.00 sec)

4.7 UPPER(), LOWER()

1
SELECT UPPER('sudip bhujel');

UPPER(‘sudip bhujel’)
SUDIP BHUJEL

1 row in set (0.00 sec)

1
SELECT LOWER('SUDIP BHUJEL');

LOWER(‘SUDIP BHUJEL’)
sudip bhujel

1 row in set (0.00 sec)

1
SELECT UPPER(author_lname) FROM books WHERE book_id < 5;

UPPER(author_lname)
LAHIRI
GAIMAN
GAIMAN
LAHIRI

4 rows in set (0.00 sec)

4.8 EXERCISE

1
SELECT REVERSE(UPPER('Hello world'));

REVERSE(UPPER(‘Hello world’))
DLROW OLLEH

1 row in set (0.00 sec)

1
SELECT REVERSE(author_fname) AS 'First', REVERSE(author_lname) AS 'LAST' FROM books WHERE book_id< 10;

First	LAST
apmuhJ	irihaL
lieN	namiaG
lieN	namiaG
apmuhJ	irihaL
evaD	sreggE
evaD	sreggE
leahciM	nobahC
ittaP	htimS
evaD	sreggE

9 rows in set (0.00 sec)

1
SELECT UPPER(CONCAT(author_fname,' ', author_lname)) AS 'NAME' FROM books WHERE book_id< 10;

NAME
JHUMPA LAHIRI
NEIL GAIMAN
NEIL GAIMAN
JHUMPA LAHIRI
DAVE EGGERS
DAVE EGGERS
MICHAEL CHABON
PATTI SMITH
DAVE EGGERS

9 rows in set (0.00 sec)

1
SELECT title AS 'Title', CHAR_LENGTH(title) AS 'Char length' FROM books WHERE book_id < 11;

Title	Char length
The Namesake	12
Norse Mythology	15
American Gods	13
Interpreter of Maladies	23
A Hologram for the King: A Novel	32
The Circle	10
The Amazing Adventures of Kavalier & Clay	41
Just Kids	9
A Heartbreaking Work of Staggering Genius	41
Caraline	8

10 rows in set (0.00 sec)

1
SELECT CONCAT(author_fname, ' ', author_lname) AS 'Full Name', title AS 'Title', stock_quantity AS 'Quantity' FROM books WHERE book_id < 11;

Full Name	Title	Quantity
Jhumpa Lahiri	The Namesake	32
Neil Gaiman	Norse Mythology	43
Neil Gaiman	American Gods	12
Jhumpa Lahiri	Interpreter of Maladies	97
Dave Eggers	A Hologram for the King: A Novel	154
Dave Eggers	The Circle	26
Michael Chabon	The Amazing Adventures of Kavalier & Clay	68
Patti Smith	Just Kids	55
Dave Eggers	A Heartbreaking Work of Staggering Genius	104
Neil Gaiman	Caraline	100

10 rows in set (0.01 sec)

1
SELECT CONCAT(author_fname, ' ', author_lname) AS 'Full Name', SUBSTRING(title, 1, 6) AS 'Title', stock_quantity AS 'Quantity' FROM books WHERE book_id < 11;

Full Name	Title	Quantity
Jhumpa Lahiri	The Na	32
Neil Gaiman	Norse	43
Neil Gaiman	Americ	12
Jhumpa Lahiri	Interp	97
Dave Eggers	A Holo	154
Dave Eggers	The Ci	26
Michael Chabon	The Am	68
Patti Smith	Just K	55
Dave Eggers	A Hear	104
Neil Gaiman	Carali	100

10 rows in set (0.01 sec)

1
SELECT CONCAT(author_fname, ' ', author_lname) AS 'Full Name', SUBSTRING(title, 1, 6) AS 'Title', CONCAT(stock_quantity, ' in stock') AS 'Quantity' FROM books WHERE book_id < 11;

Full Name	Title	Quantity
Jhumpa Lahiri	The Na	32 in stock
Neil Gaiman	Norse	43 in stock
Neil Gaiman	Americ	12 in stock
Jhumpa Lahiri	Interp	97 in stock
Dave Eggers	A Holo	154 in stock
Dave Eggers	The Ci	26 in stock
Michael Chabon	The Am	68 in stock
Patti Smith	Just K	55 in stock
Dave Eggers	A Hear	104 in stock
Neil Gaiman	Carali	100 in stock

10 rows in set (0.00 sec)

5 Advanced Selection

5.1 DINSTINCT()

1
SELECT DISTINCT(title) FROM books;

title
The Namesake
Norse Mythology
American Gods
Interpreter of Maladies
A Hologram for the King: A Novel
…
Fake_Book3

19 rows in set (0.00 sec)

1
SELECT DISTINCT author_lname, author_fname FROM books WHERE book_id < 11;

author_lname	author_fname
Lahiri	Jhumpa
Gaiman	Neil
Eggers	Dave
Chabon	Michael
Smith	Patti

5 rows in set (0.00 sec)

5.2 ORDERBY

1
SELECT DISTINCT author_lname FROM books ORDER BY author_lname;

author_lname
Author1
Author2
Author3
Carver
Chabon
DeLillo
Eggers
Foster Wallace
Gaiman
Lahiri
Smith
Steinbeck

12 rows in set (0.00 sec)

1
SELECT DISTINCT author_lname FROM books ORDER BY author_lname DESC;

author_lname
Steinbeck
Smith
Lahiri
Gaiman
Foster Wallace
Eggers
DeLillo
Chabon
Carver
Author3
Author2
Author1

12 rows in set (0.00 sec)

1
SELECT title, author_lname, released_year FROM books ORDER BY 3 DESC;

1
SELECT title, author_lname, released_year FROM books ORDER BY released_year DESC;

title	author_lname	released_year
Norse Mythology	Gaiman	2016
Norse Mythology	Gaiman	2016
The Circle	Eggers	2013
The Circle	Eggers	2013
A Hologram for the King: A Novel	Eggers	2012
…	…	…
Fake_Book1	Author1	1945

35 rows in set (0.00 sec)

5.3 LIMIT

LIMIT is used with DISTINCT and ORDER BY.

1
SELECT title, released_year FROM books ORDER BY released_year DESC LIMIT 3;

title	released_year
Norse Mythology	2016
Norse Mythology	2016
The Circle	2013

3 rows in set (0.00 sec)

1
SELECT title, released_year FROM books ORDER BY released_year ASC LIMIT 3;

title	released_year
Cannery Row	1945
Fake_Book1	1945
Cannery Row	1945

3 rows in set (0.01 sec)

1
SELECT title, released_year FROM books ORDER BY released_year LIMIT 4,6;

LIMIT starting_point, number_of_results;

title	released_year
What We Talk About When We Talk About Love: Stories	1981
White Noise	1985
White Noise	1985
Where I’m Calling From: Selected Stories	1989
Where I’m Calling From: Selected Stories	1989
Interpreter of Maladies	1996

6 rows in set (0.00 sec)

5.4 LIKE and WILD cards

1
SELECT title, author_fname FROM books WHERE author_fname LIKE '%da%';

wild card = ‘%da%’

title	author_fname
A Hologram for the King: A Novel	Dave
The Circle	Dave
A Heartbreaking Work of Staggering Genius	Dave
Oblivion: Stories	David
Consider the Lobster	David
A Hologram for the King: A Novel	Dave
The Circle	Dave
A Heartbreaking Work of Staggering Genius	Dave
Oblivion: Stories	David
Consider the Lobster	David

10 rows in set (0.00 sec)

5.5 Exercise

1
SELECT CONCAT(title, ' ', 'has ', pages, ' pages.') AS 'Title/Overview' FROM books ORDER BY pages DESC LIMIT 3;

Title/Overview
The Amazing Adventures of Kavalier & Clay has 634 pages.
The Amazing Adventures of Kavalier & Clay has 634 pages.
Where I’m Calling From: Selected Stories has 526 pages.

3 rows in set (0.01 sec)

1
SELECT DISTINCT released_year AS 'Year', UPPER(CONCAT(author_fname, ' ', author_lname)) AS 'Author Name', CONCAT(UPPER(author_fname), ' ', UPPER(author_lname), ' has ', stock_quantity, ' books in Stock, PUB YEAR: ', released_year) AS 'Summary' FROM books ORDER BY released_year;

Year	Author Name	Summary
1945	JOHN STEINBECK	JOHN STEINBECK has 95 books in Stock, PUB YEAR: 1945
1981	RAYMOND CARVER	RAYMOND CARVER has 23 books in Stock, PUB YEAR: 1981
1985	DONE DELILLO	DONE DELILLO has 49 books in Stock, PUB YEAR: 1985
1989	RAYMOND CARVER	RAYMOND CARVER has 12 books in Stock, PUB YEAR: 1989
1996	JHUMPA LAHIRI	JHUMPA LAHIRI has 97 books in Stock, PUB YEAR: 1996
…	…	…
2016	NEIL GAIMAN	NEIL GAIMAN has 43 books in Stock, PUB YEAR: 2016

16 rows in set (0.00 sec)

6 Aggregate Function

6.1 COUNT()

1
SELECT COUNT(*) FROM books;

COUNT(*)
19

1 row in set (0.00 sec)

1
SELECT COUNT(DISTINCT(author_fname)) FROM books;

COUNT(DISTINCT(author_fname))
12

1 row in set (0.00 sec)

1
SELECT COUNT(title) FROM books WHERE title LIKE '%The%';

COUNT(title)
5

1 row in set (0.00 sec)

6.2 GROUPBY()

1
SELECT author_lname, COUNT(*) FROM books GROUP BY author_lname ORDER BY COUNT(*) DESC;

author_lname	COUNT(*)
Eggers	3
Gaiman	3
Lahiri	2
Carver	2
Foster Wallace	2
DeLillo	1
Smith	1
Author3	1
Author1	1
Steinbeck	1
Chabon	1
Author2	1

12 rows in set (0.00 sec)

1
SELECT released_year, COUNT(*) FROM books GROUP BY released_year ORDER BY COUNT(*) ASC;

released_year	COUNT(*)
1985	1
2016	1
1996	1
2012	1
2013	1
2000	1
2010	1
1981	1
1989	1
1945	2
2004	2
2005	2
2003	2
2001	2

14 rows in set (0.00 sec)

6.3 MIN and MAX

1
SELECT MIN(released_year) FROM books;

MIN(released_year)
1945

1 row in set (0.00 sec)

1
SELECT title, pages FROM books WHERE pages = (SELECT MAX(pages) FROM books);

title	pages
The Amazing Adventures of Kavalier & Clay	634

1 row in set (0.00 sec)

1
SELECT title, pages FROM books ORDER BY pages DESC LIMIT 1;

title	pages
The Amazing Adventures of Kavalier & Clay	634

1 row in set (0.00 sec)

1
SELECT author_fname, author_lname, MIN(released_year) FROM books GROUP BY author_fname, author_lname;

author_fname	author_lname	MIN(released_year)
Dave	Eggers	2001
David	Foster Wallace	2004
Done	DeLillo	1985
Fake1	Author1	1945
Fake2	Author2	2004
Fake3	Author3	2005
Jhumpa	Lahiri	1996
John	Steinbeck	1945
Michael	Chabon	2000
Neil	Gaiman	2001
Patti	Smith	2010
Raymond	Carver	1981

12 rows in set (0.00 sec)

6.4 SUM()

1
SELECT SUM(pages) FROM books;

SUM(pages)
6172
1 row in set (0.00 sec)

1
SELECT author_fname, author_lname, SUM(pages) FROM books GROUP BY author_fname, author_lname ORDER BY SUM(pages) ASC;

author_fname	author_lname	SUM(pages)
Fake1	Author1	100
John	Steinbeck	181
Fake2	Author2	200
Fake3	Author3	300
Patti	Smith	304
Done	DeLillo	320
Jhumpa	Lahiri	489
Michael	Chabon	634
David	Foster Wallace	672
Raymond	Carver	702
Neil	Gaiman	977
Dave	Eggers	1293

12 rows in set (0.00 sec)

6.5 AVG()

1
SELECT AVG(pages) FROM books;

AVG(pages)
324.8421

1 row in set (0.00 sec)

1
SELECT released_year, AVG(pages) FROM books GROUP BY released_year;

released_year	AVG(pages)
1945	140.5000
1981	176.0000
1985	320.0000
1989	526.0000
1996	198.0000
2000	634.0000
2001	451.0000
2003	249.5000
2004	264.5000
2005	321.5000
2010	304.0000
2012	352.0000
2013	504.0000
2016	304.0000

14 rows in set (0.00 sec)

6.6 EXERCISE

Question

How many books are published in each year?

1
SELECT released_year AS 'PUB YEAR', COUNT(*) FROM books GROUP BY released_year;

PUB YEAR	COUNT(*)
1945	2
1981	1
1985	1
1989	1
1996	1
2000	1
2001	2
2003	2
2004	2
2005	2
2010	1
2012	1
2013	1
2016	1

14 rows in set (0.00 sec)

Question

Find the average pages published by each author?

1
SELECT author_fname, author_lname, AVG(pages) FROM books GROUP BY author_fname, author_lname;

author_fname	author_lname	AVG(pages)
Dave	Eggers	431.0000
David	Foster Wallace	336.0000
Done	DeLillo	320.0000
Fake1	Author1	100.0000
Fake2	Author2	200.0000
Fake3	Author3	300.0000
Jhumpa	Lahiri	244.5000
John	Steinbeck	181.0000
Michael	Chabon	634.0000
Neil	Gaiman	325.6667
Patti	Smith	304.0000
Raymond	Carver	351.0000

12 rows in set (0.00 sec)

Question

Find the lazy author?

1
SELECT author_fname, author_lname, AVG(pages) FROM books GROUP BY author_fname, author_lname ORDER BY AVG(pages) ASC LIMIT 1;

author_fname	author_lname	AVG(pages)
Fake1	Author1	100.0000

1 row in set (0.00 sec)

1
SELECT CONCAT(author_fname,' ', author_lname) FROM books WHERE pages = (SELECT MIN(pages) FROM books);

CONCAT(author_fname,’ ‘, author_lname)
Fake1 Author1

1 row in set (0.00 sec)

1
SELECT author_fname, author_lname, COUNT(*) AS 'Book Published', AVG(pages) FROM books GROUP BY author_fname, author_lname;

author_fname	author_lname	Book Published	AVG(pages)
Dave	Eggers	3	431.0000
David	Foster Wallace	2	336.0000
Done	DeLillo	1	320.0000
Fake1	Author1	1	100.0000
Fake2	Author2	1	200.0000
Fake3	Author3	1	300.0000
Jhumpa	Lahiri	2	244.5000
John	Steinbeck	1	181.0000
Michael	Chabon	1	634.0000
Neil	Gaiman	3	325.6667
Patti	Smith	1	304.0000
Raymond	Carver	2	351.0000

12 rows in set (0.00 sec)

7 Diving Deep Into Data

7.1 REFERENCE GUIDE TO DATETIME FUNCTIONS

Date and Time Functions

Name	Description
ADDDATE()	Add time values (intervals) to a date value
ADDTIME()	Add time
CONVERT_TZ()	Convert from one time zone to another
CURDATE()	Return the current date
CURRENT_DATE()	CURRENT_DATE Synonyms for CURDATE()
CURRENT_TIME()	CURRENT_TIME Synonyms for CURTIME()
CURRENT_TIMESTAMP()	CURRENT_TIMESTAMP Synonyms for NOW()
CURTIME()	Return the current time
DATE()	Extract the date part of a date or datetime expression
DATE_ADD()	Add time values (intervals) to a date value
DATE_FORMAT()	Format date as specified
DATE_SUB()	Subtract a time value (interval) from a date
DATEDIFF()	Subtract two dates
DAY()	Synonym for DAYOFMONTH()
DAYNAME()	Return the name of the weekday
DAYOFMONTH()	Return the day of the month (0-31)
DAYOFWEEK()	Return the weekday index of the argument
DAYOFYEAR()	Return the day of the year (1-366)
EXTRACT()	Extract part of a date
FROM_DAYS()	Convert a day number to a date
FROM_UNIXTIME()	Format Unix timestamp as a date
GET_FORMAT()	Return a date format string
HOUR()	Extract the hour
LAST_DAY	Return the last day of the month for the argument
LOCALTIME()	LOCALTIME Synonym for NOW()
LOCALTIMESTAMP	LOCALTIMESTAMP() Synonym for NOW()
MAKEDATE()	Create a date from the year and day of year
MAKETIME()	Create time from hour, minute, second
MICROSECOND()	Return the microseconds from argument
MINUTE()	Return the minute from the argument
MONTH()	Return the month from the date passed
MONTHNAME()	Return the name of the month
NOW()	Return the current date and time
PERIOD_ADD()	Add a period to a year-month
PERIOD_DIFF()	Return the number of months between periods
QUARTER()	Return the quarter from a date argument
SEC_TO_TIME()	Converts seconds to ‘hh:mm:ss’ format
SECOND()	Return the second (0-59)
STR_TO_DATE()	Convert a string to a date
SUBDATE()	Synonym for DATE_SUB() when invoked with three arguments
SUBTIME()	Subtract times
SYSDATE()	Return the time at which the function executes
TIME()	Extract the time portion of the expression passed
TIME_FORMAT()	Format as time
TIME_TO_SEC()	Return the argument converted to seconds
TIMEDIFF()	Subtract time
TIMESTAMP()	With a single argument, this function returns the date or datetime expression; with two arguments, the sum of the arguments
TIMESTAMPADD()	Add an interval to a datetime expression
TIMESTAMPDIFF()	Subtract an interval from a datetime expression
TO_DAYS()	Return the date argument converted to days
TO_SECONDS()	Return the date or datetime argument converted to seconds since Year 0
UNIX_TIMESTAMP()	Return a Unix timestamp
UTC_DATE()	Return the current UTC date
UTC_TIME()	Return the current UTC time
UTC_TIMESTAMP()	Return the current UTC date and time
WEEK()	Return the week number
WEEKDAY()	Return the weekday index
WEEKOFYEAR()	Return the calendar week of the date (1-53)
YEAR()	Return the year
YEARWEEK()	Return the year and week

7.2 Date and Time

Date: yyyy-mm-dd

Time: hh:mm:ss

DATETIME: yyyy-mm-dd hh:mm:ss

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
--DATETIME

CREATE TABLE attendance (name VARCHAR(100), entrydate DATE, entrytime TIME, entryDT DATETIME);

INSERT INTO attendance (name, entrydate, entrytime, entryDT)

VALUES('Lazy Worker', '2020-06-11', '08:07:35', '2020-06-11 08:07:35');

INSERT INTO attendance (name, entrydate, entrytime, entryDT)

VALUES('Sleepy Head', '2020-09-11', '10:30:35', '2020-06-11 10:30:35');

SELECT * FROM attendance;

1
INSERT INTO attendance (name, entrydate, entrytime, entryDT) VALUES('Late Commer', CURDATE(), CURTIME(), NOW());

CURDATE(), CURTIME(), NOW()

1
SELECT * FROM attendance;

name	entrydate	entrytime	entryDT
Lazy Worker	2020-06-11	08:07:35	2020-06-11 08:07:35
Sleepy Head	2020-09-11	10:30:35	2020-06-11 10:30:35
Late Commer	2019-10-11	11:27:31	2019-10-11 11:27:31

3 rows in set (0.00 sec)

7.2.1 Formatting

1
SELECT name, entrydate, DAY(entrydate), DAYOFYEAR(entrydate), DAYNAME(entrydate), DAYOFWEEK(entrydate) FROM attendance;

name	entrydate	DAY(entrydate)	DAYOFYEAR(entrydate)	DAYNAME(entrydate)	DAYOFWEEK(entrydate)
Lazy Worker	2020-06-11	11	163	Thursday	5
Sleepy Head	2020-09-11	11	255	Friday	6
Late Commer	2019-10-11	11	284	Friday	6

3 rows in set (0.00 sec)

1
SELECT DATE_FORMAT(entrydate,'%M/%D/%Y') FROM attendance;

DATE_FORMAT(entrydate,'%M/%D/%Y’)
June/11th/2020
September/11th/2020
October/11th/2019

3 rows in set (0.00 sec)

1
SELECT DATE_FORMAT(entrydate,'%m/%d/%y') FROM attendance;

DATE_FORMAT(entrydate,'%m/%d/%y’)
06/11/20
09/11/20
10/11/19
3 rows in set (0.00 sec)

7.2.2 DATEDIFF

1
SELECT name, entryDT, DATEDIFF(NOW(), entryDT) FROM attendance;

name	entryDT	DATEDIFF(NOW(), entryDT)
Lazy Worker	2020-06-11 08:07:35	-244
Sleepy Head	2020-06-11 10:30:35	-244
Late Commer	2019-10-11 11:27:31	0

3 rows in set (0.00 sec)

7.2.3 DATE_ADD()

1
SELECT name, entryDT, DATE_ADD(entryDT, INTERVAL 1 MONTH) FROM attendance;

name	entryDT	DATE_ADD(entryDT, INTERVAL 1 MONTH)
Lazy Worker	2020-06-11 08:07:35	2020-07-11 08:07:35
Sleepy Head	2020-06-11 10:30:35	2020-07-11 10:30:35
Late Commer	2019-10-11 11:27:31	2019-11-11 11:27:31

3 rows in set (0.00 sec)

7.2.4 TIMESTAMP()

1
CREATE TABLE observation(content VARCHAR(100), created_at TIMESTAMP DEFAULT NOW());

1
DESC observation;

Field	Type	Null	Key	Default	Extra
content	varchar(100)	YES		NULL
created_at	timestamp	NO		CURRENT_TIMESTAMP

2 rows in set (0.00 sec)

1
2
3
4
5
INSERT INTO observation(content) VALUES('Hi, I arrived before 7 Oclock today');

INSERT INTO observation(content) VALUES('Hi, I arrived before 6 Oclock today in the Morning');

INSERT INTO observation(content) VALUES('Hi, I arrived before 8.30 Oclock today');

1
SELECT * FROM observation;

content	created_at
Hi, I arrived before 7 Oclock today	2019-10-11 13:59:29
Hi, I arrived before 6 Oclock today in the Morning	2019-10-11 13:59:39
Hi, I arrived before 8.30 Oclock today	2019-10-11 13:59:49

3 rows in set (0.00 sec)

8 Logical Operator

8.1 NOTEQUAL

! =

1
SELECT title FROM books WHERE released_year != 2005;

title
The Namesake
Norse Mythology
American Gods
Interpreter of Maladies
…
Fake_Book2

17 rows in set (0.00 sec)

8.2 NOT LIKE

1
SELECT title FROM books WHERE title NOT LIKE '%FAKE%';

title
The Namesake
Norse Mythology
American Gods
Interpreter of Maladies
…
Oblivion: Stories
Consider the Lobster

16 rows in set (0.00 sec)

8.3 GREATER THAN

1
SELECT title, released_year FROM books WHERE released_year >= 2010;

title	released_year
Norse Mythology	2016
A Hologram for the King: A Novel	2012
The Circle	2013
Just Kids	2010

4 rows in set (0.00 sec)

8.4 LOGICAL AND

1
SELECT title, released_year, stock_quantity FROM books WHERE released_year >= 2010 && stock_quantity <= 100;

1
SELECT title, released_year, stock_quantity FROM books WHERE released_year >= 2010 AND stock_quantity <= 100;

title	released_year	stock_quantity
Norse Mythology	2016	43
The Circle	2013	26
Just Kids	2010	55

3 rows in set (0.00 sec)

8.5 LOGICAL OR

1
SELECT title, released_year, stock_quantity FROM books WHERE released_year >= 2010 || stock_quantity <= 100;

title	released_year	stock_quantity
The Namesake	2003	32
Norse Mythology	2016	43
American Gods	2001	12
Interpreter of Maladies	1996	97
…	…	…
Fake_Book3	2005	92

16 rows in set (0.00 sec)

8.6 BETWEEN and NOT BEETWEEN

1
SELECT title, released_year, stock_quantity FROM books WHERE released_year BETWEEN 2000 AND 2010;

title	released_year	stock_quantity
The Namesake	2003	32
American Gods	2001	12
The Amazing Adventures of Kavalier & Clay	2000	68
Just Kids	2010	55
A Heartbreaking Work of Staggering Genius	2001	104
Caraline	2003	100
Oblivion: Stories	2004	172
Consider the Lobster	2005	92
Fake_Book2	2004	172
Fake_Book3	2005	92

10 rows in set (0.00 sec)

1
SELECT title, released_year, stock_quantity FROM books WHERE released_year NOT BETWEEN 2000 AND 2010;

title	released_year	stock_quantity
Norse Mythology	2016	43
Interpreter of Maladies	1996	97
A Hologram for the King: A Novel	2012	154
The Circle	2013	26
What We Talk About When We Talk About Love: Stories	1981	23
Where I’m Calling From: Selected Stories	1989	12
White Noise	1985	49
Cannery Row	1945	95
Fake_Book1	1945	95

9 rows in set (0.00 sec)

8.7 IN and NOT IN

1
SELECT title, released_year FROM books WHERE released_year IN (2000, 2004, 2008);

title	released_year
The Amazing Adventures of Kavalier & Clay	2000
Oblivion: Stories	2004
Fake_Book2	2004

3 rows in set (0.00 sec)

1
SELECT title, released_year FROM books WHERE released_year NOT IN (2000, 2004, 2008);

title	released_year
The Namesake	2003
Norse Mythology	2016
American Gods	2001
Interpreter of Maladies	1996
A Hologram for the King: A Novel	2012
…	…
Fake_Book3	2005

16 rows in set (0.00 sec)

8.8 CASE statement

1
2
3
4
5
6
7
8
SELECT title,
				released_year,
				CASE
						WHEN released_year BETWEEN 1945 AND 2000 THEN 'OLD'
						WHEN released_year BETWEEN 2000 AND 2010 THEN 'Not So OLD'
						ELSE 'MODERN'
						END AS 'Book Era'
FROM books;

title	released_year	Book Era
The Namesake	2003	Not So OLD
Norse Mythology	2016	MODERN
American Gods	2001	Not So OLD
Interpreter of Maladies	1996	OLD
A Hologram for the King: A Novel	2012	MODERN
…	…	…
Fake_Book3	2005	Not So OLD

19 rows in set (0.00 sec)

1
2
3
4
5
6
7
SELECT title,
				pages,
				CASE WHEN pages BETWEEN 0 AND 200 THEN 'SHORT'
				WHEN pages BETWEEN 200 AND 400 THEN 'MEDIUM'
				ELSE 'LENGTHY'
				END AS 'Note'
FROM books;

title	pages	Note
The Namesake	291	MEDIUM
Norse Mythology	304	MEDIUM
American Gods	465	LENGTHY
Interpreter of Maladies	198	SHORT
A Hologram for the King: A Novel	352	MEDIUM
…	…	…
Fake_Book3	300	MEDIUM

19 rows in set (0.00 sec)

9 One to Many RELATIONSHIPS

1
CREATE DATABASE one2many;

1
USE one2many;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
CREATE TABLE customers(

    id INT AUTO_INCREMENT PRIMARY KEY,

    fname VARCHAR(100),

    lname VARCHAR(100),

    email VARCHAR(100)

);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
CREATE TABLE orders(

    id INT AUTO_INCREMENT PRIMARY KEY,

    order_date DATE,

    amount DECIMAL(8,2),

    customer_id INT,

    FOREIGN KEY(customer_id) REFERENCES customers(id)

);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
INSERT INTO customers (fname, lname, email)

VALUES ('fname1', 'lname1', 'flname1@gmail.com'),

       ('fname2', 'lname2', 'flname2@gmail.com'),

       ('fname3', 'lname3', 'flname3@gmail.com'),

       ('fname4', 'lname4', 'flname4@gmail.com'),

       ('fname5', 'lname5', 'flname5@gmail.com'),

       ('fname6', 'lname6', 'flname6@gmail.com'),

       ('fname7', 'lname7', 'flname7@gmail.com'),

       ('fname8', 'lname8', 'flname8@gmail.com');

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
INSERT INTO orders (order_date, amount, customer_id)

VALUES ('2019/07/10', 85.99, 2),

       ('2019/11/15', 99.50, 3),

       ('2018/12/18', 30.67, 3),

       ('2017/04/23', 12.50, 2),

       ('1995/05/21', 25.25, 5),

('2018/12/28', 30.67, 3),

       ('2017/04/13', 12.50, 2),

       ('2000/05/31', 25.25, 5);

9.1 JOIN

9.1.1 IMPLICIT JOIN

1
SELECT * FROM customers, orders WHERE customers.id = orders.customer_id;

id	fname	lname	email	id	order_date	amount	customer_id
2	fname2	lname2	flname2@gmail.com	1	2019-07-10	85.99	2
3	fname3	lname3	flname3@gmail.com	2	2019-11-15	99.50	3
3	fname3	lname3	flname3@gmail.com	3	2018-12-18	30.67	3
2	fname2	lname2	flname2@gmail.com	4	2017-04-23	12.50	2
5	fname5	lname5	flname5@gmail.com	5	1995-05-21	25.25	5
3	fname3	lname3	flname3@gmail.com	6	2018-12-28	30.67	3
2	fname2	lname2	flname2@gmail.com	7	2017-04-13	12.50	2
5	fname5	lname5	flname5@gmail.com	8	2000-05-31	25.25	5

8 rows in set (0.00 sec)

9.1.2 EXPLICIT JOIN

1
2
3
4
SELECT fname, lname, order_date, amount, customer_id
    FROM customers
    JOIN orders
    ON customers.id = orders.customer_id;

1
SELECT fname, lname, order_date, amount, customer_id FROM customers JOIN orders ON [customers.id](http://customers.id/) = orders.customer_id;

fname	lname	order_date	amount	customer_id
fname2	lname2	2019-07-10	85.99	2
fname3	lname3	2019-11-15	99.50	3
fname3	lname3	2018-12-18	30.67	3
fname2	lname2	2017-04-23	12.50	2
fname5	lname5	1995-05-21	25.25	5
fname3	lname3	2018-12-28	30.67	3
fname2	lname2	2017-04-13	12.50	2
fname5	lname5	2000-05-31	25.25	5

8 rows in set (0.00 sec)

9.1.3 LEFT JOIN

1
SELECT fname, lname, order_date, amount, customer_id FROM customers LEFT JOIN orders ON customers.id = orders.customer_id;

fname	lname	order_date	amount	customer_id
fname2	lname2	2019-07-10	85.99	2
fname3	lname3	2019-11-15	99.50	3
fname3	lname3	2018-12-18	30.67	3
fname2	lname2	2017-04-23	12.50	2
fname5	lname5	1995-05-21	25.25	5
fname3	lname3	2018-12-28	30.67	3
fname2	lname2	2017-04-13	12.50	2
fname5	lname5	2000-05-31	25.25	5
fname1	lname1	NULL	NULL	NULL
fname4	lname4	NULL	NULL	NULL
fname6	lname6	NULL	NULL	NULL
fname7	lname7	NULL	NULL	NULL
fname8	lname8	NULL	NULL	NULL

13 rows in set (0.00 sec)

1
SELECT * FROM customers RIGHT JOIN orders ON customers.id = orders.customer_id;

id	fname	lname	email	id	order_date	amount	customer_id
2	fname2	lname2	flname2@gmail.com	1	2019-07-10	85.99	2
3	fname3	lname3	flname3@gmail.com	2	2019-11-15	99.50	3
3	fname3	lname3	flname3@gmail.com	3	2018-12-18	30.67	3
2	fname2	lname2	flname2@gmail.com	4	2017-04-23	12.50	2
5	fname5	lname5	flname5@gmail.com	5	1995-05-21	25.25	5
3	fname3	lname3	flname3@gmail.com	6	2018-12-28	30.67	3
2	fname2	lname2	flname2@gmail.com	7	2017-04-13	12.50	2
5	fname5	lname5	flname5@gmail.com	8	2000-05-31	25.25	5

8 rows in set (0.00 sec)

9.2 EXERCISE

1
CREATE TABLE scientists( id INT AUTO_INCREMENT, name VARCHAR(100), PRIMARY KEY(id));

1
INSERT INTO scientists(name) VALUES ('Scientist 1'), ('Scientist 2'), ('Scientist 3'), ('Scientist 4');

1
SELECT * FROM scientists;

| id | name | | —— | | 1 | Scientist 1 | | 2 | Scientist 2 | | 3 | Scientist 3 | | 4 | Scientist 4 | 4 rows in set (0.00 sec)

1
CREATE TABLE publications( title VARCHAR(100), impact_factor DECIMAL(6,3), scientist_id INT,  FOREIGN KEY (scientist_id) REFERENCES scientists(id) ON DELETE CASCADE);

ON DELETE CASCADE will allow to delete linked DATA.

1
DESC publications;

Field	Type	Null	Key	Default
title	varchar(100)	YES		NULL
impact_factor	decimal(6,3)	YES		NULL
scientist_id	int(11)	YES	MUL	NULL

3 rows in set (0.00 sec)

1
INSERT INTO publications (scientist_id, title, impact_factor) VALUES (1, 'Journal 1', 2.3), (1, 'Journal 2', 32.5), (2, 'Journal 3', 5.5), (2, 'Journal 4', 8.9);

1
SELECT * FROM publications;

title	impact_factor	scientist_id
Journal 1	2.300	1
Journal 2	32.500	1
Journal 3	5.500	2
Journal 4	8.900	2

4 rows in set (0.01 sec)

1
SELECT name, title, impact_factor FROM publications LEFT JOIN scientists ON scientists.id = publications.scientist_id;

name	title	impact_factor
Scientist 1	Journal 1	2.300
Scientist 1	Journal 2	32.500
Scientist 2	Journal 3	5.500
Scientist 2	Journal 4	8.900

4 rows in set (0.00 sec)

1
SELECT name, title, impact_factor FROM scientists LEFT JOIN publications ON scientists.id = publications.scientist_id;

name	title	impact_factor
Scientist 1	Journal 1	2.300
Scientist 1	Journal 2	32.500
Scientist 2	Journal 3	5.500
Scientist 2	Journal 4	8.900
Scientist 3	NULL	NULL
Scientist 4	NULL	NULL

6 rows in set (0.00 sec)

1
SELECT name, IFNULL(title, 'MISSING'), IFNULL(impact_factor, 0) FROM scientists LEFT JOIN publications ON scientists.id = publications.scientist_id

name	IFNULL(title, ‘MISSING’)	IFNULL(impact_factor, 0)
Scientist 1	Journal 1	2.300
Scientist 1	Journal 2	32.500
Scientist 2	Journal 3	5.500
Scientist 2	Journal 4	8.900
Scientist 3	MISSING	0.000
Scientist 4	MISSING	0.000

6 rows in set (0.00 sec)

1
SELECT name, IFNULL(title, 'MISSING'), IFNULL(impact_factor, 0), CASE WHEN IFNULL(impaor, 0) > 6 THEN 'Excellent' WHEN IFNULL(impact_factor,0) BETWEEN 2 AND 6 THEN 'Medium' ELSE 'You are Fired' END AS 'REMARKS' FROM scientists LEFT JOIN publications ON scientists.id = publications.scientist_id;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
--Format
SELECT name,
			IFNULL(title, 'MISSING'),
			IFNULL(impact_factor, 0),
			CASE
					WHEN IFNULL(impaor, 0) > 6 THEN 'Excellent'
					WHEN IFNULL(impact_factor,0) BETWEEN 2 AND 6 THEN 'Medium'
					ELSE 'You are Fired'
END AS 'REMARKS'
FROM scientists LEFT JOIN publications ON scientists.id = publications.scientist_id;

name	IFNULL(title, ‘MISSING’)	IFNULL(impact_factor, 0)	REMARKS
Scientist 1	Journal 1	2.300	Medium
Scientist 1	Journal 2	32.500	Excellent
Scientist 2	Journal 3	5.500	Medium
Scientist 2	Journal 4	8.900	Excellent
Scientist 3	MISSING	0.000	You are Fired
Scientist 4	MISSING	0.000	You are Fired

6 rows in set (0.00 sec)

10 Many To Many

10.1 CODE: SCHEMA UDEMY REVIEW APP

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
CREATE TABLE students (

    id INT AUTO_INCREMENT PRIMARY KEY,

    name VARCHAR(100)

);

-- CREATING THE course TABLE

CREATE TABLE course(

    id INT AUTO_INCREMENT PRIMARY KEY,

    title VARCHAR(100),

    released_year YEAR(4),

    topic VARCHAR(100)

);

-- CREATING THE REVIEWS TABLE

CREATE TABLE reviews (

    id INT AUTO_INCREMENT PRIMARY KEY,

    rating DECIMAL(2,1),

    course_id INT,

    student_id INT,

    FOREIGN KEY(course_id) REFERENCES course(id),

    FOREIGN KEY(student_id) REFERENCES students(id)

);

-- INSERTING A BUNCH OF DATA

INSERT INTO course (title, released_year, topic) VALUES

    ('mySQL', 2019, 'Databases'),

    ('Learn Python', 2018, 'Programming'),

    ("Web Scraping Course", 2018, 'DataMining'),

    ('Learn Django', 2014, 'Web Development'),

    ("K_Means Course", 2017, 'Machine Learning'),

    ('Digital Marketing', 2000, 'Sales'),

    ("Learn React", 2014, 'Web Development'),

    ('Full ML Course', 1999, 'Machine Learning'),

    ('Python for Beginners', 1973, 'Programming'),

    ('Learn Scrapy', 2014, 'Datamining'),

    ('Learn mySQL with SQL', 2000, 'Databases'),

    ('Learn SEO', 2007, 'Digital Marketing'),

    ('Natural Language Processing', 1989, 'Machine Learning'),

    ('Back End Web Development with Django', 2016, 'Web Development');

INSERT INTO students (name) VALUES

    ('Chuck Norris'),

    ('Brad Pitt'),

    ('Angelina Julie'),

    ('Van Dam'),

    ('Rock'),

    ('Shahrukh Khan'),

    ('Marlin Mory');

INSERT INTO reviews(course_id, student_id, rating) VALUES

    (1,1,5.0),(1,2,4.5),(1,3,4.5),(1,4,4.7),(1,5,4.9),

    (2,1,3.1),(2,4,3.0),(2,3,4.0),(2,6,4.4),(2,5,5.0),

    (3,1,3.0),(3,6,3.5),(3,4,4.0),(3,3,4.1),(3,5,5.0),

    (4,1,3.5),(4,3,2.8),(4,4,3.3),(4,2,2.6),(4,5,5.5),

    (5,1,3.5),(5,3,2.0),(5,4,5.1),(5,2,2.3),(5,5,4.9),

    (6,2,3.5),(6,3,3.8),(6,4,4.8),(6,2,4.4),(6,5,4.1),

    (7,2,3.1),(7,5,9.7),

    (8,4,4.5),(8,2,5.0),(8,6,1.8),(8,5,1.3),

    (9,2,4.5),(9,3,5.0),(9,4,2.8),(9,6,1.3),(9,5,4.5),

    (10,5,5.0),

    (13,3,2.0),(13,4,2.2),

    (14,2,3.5),(14,3,3.9),(14,4,2.9);

1
SELECT title, rating FROM course JOIN reviews ON course.id = reviews.course_id;

title	rating
mySQL	5.0
mySQL	4.5
mySQL	4.5
mySQL	4.7
mySQL	4.9
…	…
Back End Web Development with Django	2.9

47 rows in set (0.00 sec)

1
SELECT title, AVG(rating) FROM course JOIN reviews ON course.id = reviews.id GROUP BY title ORDER BY AVG(rating) ASC;

title	AVG(rating)
Learn React	3.00000
Learn mySQL with SQL	3.00000
Digital Marketing	3.10000
Learn SEO	3.50000
Full ML Course	4.00000
Natural Language Processing	4.00000
Back End Web Development with Django	4.10000
Python for Beginners	4.40000
Learn Python	4.50000
Web Scraping Course	4.50000
Learn Django	4.70000
K_Means Course	4.90000
Learn Scrapy	5.00000
mySQL	5.00000

14 rows in set (0.00 sec)

MISTAKE

1
2
-- TECHNICALLY MISTAKE ???
SELECT title, name, rating FROM course, students JOIN reviews ON students.id = reviews.student_id;

title	name	rating
mySQL	Chuck Norris	5.0
mySQL	Chuck Norris	3.1
mySQL	Chuck Norris	3.0
…	…	…
Back End Web Development with Django	Shahrukh Khan	1.3

658 rows in set (0.00 sec)

1
SELECT title, name, rating FROM course, students JOIN reviews ON students.id = reviews.id;

title	name	rating
mySQL	Chuck Norris	5.0
mySQL	Brad Pitt	4.5
mySQL	Angelina Julie	4.5
…	…	…
Back End Web Development with Django	Marlin Mory	3.0

98 rows in set (0.00 sec)

1
SELECT title, IFNULL(rating, 0) FROM course LEFT JOIN reviews ON course.id = reviews.course_id;

title	IFNULL(rating, 0)
mySQL	5.0
mySQL	4.5
mySQL	4.5
…	…
Back End Web Development with Django	2.9

49 rows in set (0.00 sec)

1
SELECT title, AVG(rating) FROM course JOIN reviews ON course.id = reviews.course_id GROUP BY title ORDER BY AVG(rating) DESC;

title	AVG(rating)
Learn React	6.40000
Learn Scrapy	5.00000
mySQL	4.72000
Digital Marketing	4.12000
Web Scraping Course	3.92000
Learn Python	3.90000
Python for Beginners	3.62000
K_Means Course	3.56000
Learn Django	3.54000
Back End Web Development with Django	3.43333
Full ML Course	3.15000
Natural Language Processing	2.10000

12 rows in set (0.01 sec)

1
SELECT topic, AVG(rating) FROM course JOIN reviews ON course.id = reviews.course_id GROUP BY topic ORDER BY AVG(rating) DESC;

topic	AVG(rating)
Databases	4.72000
Sales	4.12000
DataMining	4.10000
Web Development	4.08000
Programming	3.76000
Machine Learning	3.14545

6 rows in set (0.00 sec)

Two inner JOIN

1
SELECT name, title, topic, rating FROM students JOIN course ON course.id = students.id JOIN reviews ON reviews.student_id = students.id;

name	title	topic	rating
Chuck Norris	mySQL	Databases	5.0
Chuck Norris	mySQL	Databases	3.1
Chuck Norris	mySQL	Databases	3.0
…	…	…	…
Shahrukh Khan	Digital Marketing	Sales	1.3

47 rows in set (0.00 sec)

11 linkedin Clone

1
2
CREATE DATABASE linkedin_db;
USE linkedin_db;

1
2
3
4
5
CREATE TABLE users(
		id INT AUTO_INCREMENT PRIMARY KEY,
		username VARCHAR(255) UNIQUE NOT NULL,
		created_at TIMESTAMP DEFAULT NOW()
		);

1
2
3
4
INSERT INTO users(username) VALUES
    ('Sudip'),
    ('Bharat'),
    ('Sundar');

1
2
3
4
5
6
7
CREATE TABLE media(
  id INT AUTO_INCREMENT PRIMARY KEY,
  url VARCHAR(255) UNIQUE NOT NULL,
  user_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT NOW(),
  FOREIGN KEY(user_id) REFERENCES users(id)
  );

1
2
3
4
INSERT INTO media(url, user_id) VALUES
  ('/img1', 1),
  ('/img2', 2),
  ('/img3', 3);

1
2
3
4
5
6
7
8
9
CREATE TABLE comments(
	id INT AUTO_INCREMENT PRIMARY KEY,
	comment_text VARCHAR(255) NOT NULL,
	media_id INT NOT NULL,
	user_id INT NOT NULL,
	created_at TIMESTAMP DEFAULT NOW(),
	FOREIGN KEY (media_id) REFERENCES media(id),
	FOREIGN KEY (user_id) REFERENCES users(id)
	);

1
2
3
4
INSERT INTO comments (comment_text, user_id, media_id) VALUES
  ('Congrats on you New Job', 1, 2),
  ('I just got Hired as Data Engineer', 2, 3),
  ('Now I can write SQL Quarries', 2, 1);

1
2
3
4
5
6
7
8
CREATE TABLE likes(
  user_id INT NOT NULL,
  media_id INT NOT NULL,
  created_at TIMESTAMP DEFAULT NOW(),
  FOREIGN KEY(user_id) REFERENCES users(id),
  FOREIGN KEY(media_id) REFERENCES media(id),
  PRIMARY KEY(user_id, media_id)
  );

1
2
3
4
5
INSERT INTO likes(user_id, media_id) VALUES
  (1, 2),
  (2, 2),
  (3, 1),
  (1, 3);

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
CREATE TABLE connections (

  follower_id INTEGER NOT NULL,

  following_id INTEGER NOT NULL,

  created_at TIMESTAMP DEFAULT NOW(),

  FOREIGN KEY(follower_id) REFERENCES users(id),

  FOREIGN KEY(following_id) REFERENCES users(id),

  PRIMARY KEY(follower_id, following_id)

);

1
2
3
4
5
INSERT INTO connections(follower_id, following_id) VALUES
  (1, 2),
  (2, 1),
  (3, 1),
  (1, 3);

1
2
3
4
5
CREATE TABLE tags(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT NOW()
    );

1
2
3
4
5
6
7
CREATE TABLE media_tags(
    media_id INT NOT NULL,
    tag_id INT NOT NULL,
    FOREIGN KEY(media_id) REFERENCES media(id),
    FOREIGN KEY(tag_id) REFERENCES tags(id),
    PRIMARY KEY(media_id, tag_id)
    );

1
2
3
4
5
6
7
CREATE TABLE tagged_media(
		media_id INT NOT NULL,
		tag_id INT NOT NULL,
		FOREIGN KEY(media_id) REFERENCES media(id),
		FOREIGN KEY(tag_id) REFERENCES tags(id),
		PRIMARY KEY(media_id, tag_id)
		);

12 Real Data (linkedin)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
DROP DATABASE IF EXISTS linkedin_db;
CREATE DATABASE linkedin_db;
USE linkedin_db;

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

CREATE TABLE media (
    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,
    media_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY(media_id) REFERENCES media(id),
    FOREIGN KEY(user_id) REFERENCES users(id)
);

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

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

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

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

You can find databases here!

Oldest users

1
SELECT * FROM users ORDER BY created_at ASC LIMIT 3;

id	username	created_at
80	Darby_Herzog	2016-05-06 00:14:21
67	Emilio_Bernier52	2016-05-06 13:04:30
63	Elenor88	2016-05-08 01:30:41

3 rows in set (0.00 sec)

Most recent users

1
SELECT * FROM users ORDER BY created_at DESC LIMIT 3;

id	username	created_at
11	Justina.Gaylord27	2017-05-04 16:32:16
6	Travon.Waters	2017-04-30 13:26:14
85	Milford_Gleichner42	2017-04-30 07:50:51

3 rows in set (0.00 sec)

Best day and Worst day for POST

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
SELECT

DAYNAME(created_at) AS DAY,

COUNT(*) AS register_days

FROM users

GROUP BY DAY

ORDER BY register_days DESC

LIMIT 2;

DAY	Registered Days
Thursday	16
Sunday	16

2 rows in set (0.01 sec)

Find InActive USERS

1
SELECT username, image_url FROM users LEFT JOIN media ON users.id = media.user_id WHERE media.id IS NULL;

username	image_url
Aniya_Hackett	NULL
Bartholome.Bernhard	NULL
Bethany20	NULL
…	…
Tierra.Trantow	NULL

26 rows in set (0.01 sec)

Famous 4 tags

1
2
3
4
5
6
SELECT tag_name, COUNT(*) AS 'Famous Tag'
FROM tags
JOIN media_tags ON media_tags.tag_id = tags.id
GROUP BY tag_id
ORDER BY COUNT(*) DESC
LIMIT 4;

tag_name	Famous Tag
smile	59
beach	42
party	39
fun	38

4 rows in set (0.00 sec)