All you need to know about mysql - Database

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

1 INSTALLATION

sudo mysql -u root -p
PASSWORD: yourpassword

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

CREATE DATABASE db_name;

SHOW DATABASES;

DROP DATABASE db_name;

USE e_com;

SELECT database();

• 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

CREATE TABLE dogs(
    name VARCHAR(100),
    breed VARCHAR(100),
    age INT
    );

SHOW TABLES;

SHOW COLUMNS FROM dogs;

DESC dogs;

DROP TABLE dogs;

SELECT database(); shows selected database.

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

Insert multiple data

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

NOT NULL

all the values for headers needs to be defined explicitly.

CREATE TABLE dogs(
    name VARCHAR(100) NOT NULL,
    breed VARCHAR(100) NOT NULL,
    age INT NOT NULL
    );

DEFAULT values

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

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

SELECT * FROM dog3;

1 row in set (0.00 sec)

INSERT INTO dog3 (age) VALUES(10);

SELECT * FROM dog3;

2 rows in set (0.00 sec)

key

Unique Identifier

CREATE TABLE dog4(
  dog_id INT NOT NULL,
	name VARCHAR(100),
	age INT,
	PRIMARY KEY(dog_id)
);

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);

SELECT * FROM dog4

3 rows in set (0.00 sec)

AUTO INCREMENT

CREATE TABLE dog5(
	dog_id INT NOT NULL AUTO_INCREMENT,
	name VARCHAR(100),
	age INT,
	PRIMARY KEY(dog_id)
);

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

SELECT * FROM dog5;

3 rows in set (0.01 sec)

EXERCISE:

CREATE DATABASE BEX;

USE BEX;

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)
    );

DESC class;

6 rows in set (0.01 sec)

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

SELECT * FROM class;

1 row in set (0.00 sec)

3 CRUD

3.1 Data preparing section

CREATE DATABASE demo_dog;

CREATE TABLE dogs( dog_id INT NOT NULL AUTO_INCREMENT,
		name VARCHAR(100), breed VARCHAR(100),
		age INT,
		PRIMARY KEY(dog_id)
);

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);

DESC dogs;

4 rows in set (0.00 sec)

3.2 SELECT

SELECT * FROM dogs;

5 rows in set (0.00 sec)

SELECT * FROM tables_name; COMMAND + EXPRESSION + ADRESS

SELECT dog_id,name,breed FROM dogs;

5 rows in set (0.00 sec)

3.3 WHERE

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

1 row in set (0.00 sec)

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

1 row in set (0.00 sec)

3.3 ALIASES

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

SELECT dog_id AS ID FROM dogs;

5 rows in set (0.00 sec)

SELECT name AS dog_name FROM dogs;

5 rows in set (0.00 sec)

3.4 UPDATE

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

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

SELECT * FROM dogs;

5 rows in set (0.00 sec)

3.5 DELETE

DELETE FROM dogs WHERE age = 5

SELECT * FROM dogs;

4 rows in set (0.00 sec)

DELETE tables contents

DELETE FROM shoes;

4 All About String

CREATE DATABASE stationery_books;

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()

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

With Aliases

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

4.2 CONCAT_WS()

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

4.3 SUBSTRING()

Indexing starts from 1.

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

1 row in set (0.00 sec)

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

1 row in set (0.00 sec)

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

4 rows in set (0.00 sec)

4.4 CONCAT(SUBSTRING(), ..)

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

4.5 REPLACE()

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

1 row in set (0.00 sec)

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

4 rows in set (0.00 sec)

4.6 REVERSE()

SELECT REVERESE('HELLO')

1 row in set (0.00 sec)

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

4 rows in set (0.00 sec)

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

3 rows in set (0.00 sec)

4.7 UPPER(), LOWER()

SELECT UPPER('sudip bhujel');

1 row in set (0.00 sec)

SELECT LOWER('SUDIP BHUJEL');

1 row in set (0.00 sec)

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

4 rows in set (0.00 sec)

4.8 EXERCISE

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

1 row in set (0.00 sec)

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

9 rows in set (0.00 sec)

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

9 rows in set (0.00 sec)

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

10 rows in set (0.00 sec)

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

10 rows in set (0.01 sec)

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;

10 rows in set (0.01 sec)

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;

10 rows in set (0.00 sec)

5 Advanced Selection

5.1 DINSTINCT()

SELECT DISTINCT(title) FROM books;

19 rows in set (0.00 sec)

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

5 rows in set (0.00 sec)

5.2 ORDERBY

SELECT DISTINCT author_lname FROM books ORDER BY author_lname;

12 rows in set (0.00 sec)

SELECT DISTINCT author_lname FROM books ORDER BY author_lname DESC;

12 rows in set (0.00 sec)

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

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

35 rows in set (0.00 sec)

5.3 LIMIT

LIMIT is used with DISTINCT and ORDER BY.

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

3 rows in set (0.00 sec)

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

3 rows in set (0.01 sec)

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

LIMIT starting_point, number_of_results;

6 rows in set (0.00 sec)

5.4 LIKE and WILD cards

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

wild card = '%da%'

10 rows in set (0.00 sec)

5.5 Exercise

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

3 rows in set (0.01 sec)

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;

16 rows in set (0.00 sec)

6 Aggregate Function

6.1 COUNT()

SELECT COUNT(*) FROM books;

1 row in set (0.00 sec)

SELECT COUNT(DISTINCT(author_fname)) FROM books;

1 row in set (0.00 sec)

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

1 row in set (0.00 sec)

6.2 GROUPBY()

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

12 rows in set (0.00 sec)

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

14 rows in set (0.00 sec)

6.3 MIN and MAX

SELECT MIN(released_year) FROM books;

1 row in set (0.00 sec)

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

1 row in set (0.00 sec)

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

1 row in set (0.00 sec)

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

12 rows in set (0.00 sec)

6.4 SUM()

SELECT SUM(pages) FROM books;

1 row in set (0.00 sec)

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

12 rows in set (0.00 sec)

6.5 AVG()

SELECT AVG(pages) FROM books;

1 row in set (0.00 sec)

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

14 rows in set (0.00 sec)

6.6 EXERCISE

How many books are published in each year?

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

14 rows in set (0.00 sec)

Find the average pages published by each author?

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

12 rows in set (0.00 sec)

Find the lazy author?

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

1 row in set (0.00 sec)

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

1 row in set (0.00 sec)

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

12 rows in set (0.00 sec)

7 Diving Deep Into Data

7.1 REFERENCE GUIDE TO DATETIME FUNCTIONS

Date and Time Functions

7.2 Date and Time

Date: yyyy-mm-dd

Time: hh:mm:ss

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

--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;

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

CURDATE(), CURTIME(), NOW()

SELECT * FROM attendance;

3 rows in set (0.00 sec)

7.2.1 Formatting

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

3 rows in set (0.00 sec)

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

3 rows in set (0.00 sec)

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

3 rows in set (0.00 sec)

7.2.2 DATEDIFF

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

3 rows in set (0.00 sec)

7.2.3 DATE_ADD()

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

3 rows in set (0.00 sec)

7.2.4 TIMESTAMP()

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

DESC observation;

2 rows in set (0.00 sec)

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');

SELECT * FROM observation;

3 rows in set (0.00 sec)

8 Logical Operator

8.1 NOTEQUAL

! =

SELECT title FROM books WHERE released_year != 2005;

17 rows in set (0.00 sec)

8.2 NOT LIKE

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

16 rows in set (0.00 sec)

8.3 GREATER THAN

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

4 rows in set (0.00 sec)

8.4 LOGICAL AND

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

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

3 rows in set (0.00 sec)

8.5 LOGICAL OR

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

16 rows in set (0.00 sec)

8.6 BETWEEN and NOT BEETWEEN

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

10 rows in set (0.00 sec)

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

9 rows in set (0.00 sec)

8.7 IN and NOT IN

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

3 rows in set (0.00 sec)

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

16 rows in set (0.00 sec)

8.8 CASE statement

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;

19 rows in set (0.00 sec)

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;

19 rows in set (0.00 sec)

9 One to Many RELATIONSHIPS

CREATE DATABASE one2many;

USE one2many;

CREATE TABLE customers(

    id INT AUTO_INCREMENT PRIMARY KEY,

    fname VARCHAR(100),

    lname VARCHAR(100),

    email VARCHAR(100)

);

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)

);

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');

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

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

8 rows in set (0.00 sec)

9.1.2 EXPLICIT JOIN

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

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

8 rows in set (0.00 sec)

9.1.3 LEFT JOIN

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

13 rows in set (0.00 sec)

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

8 rows in set (0.00 sec)

9.2 EXERCISE

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

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

SELECT * FROM scientists;

4 rows in set (0.00 sec)

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.

DESC publications;

3 rows in set (0.00 sec)

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);

SELECT * FROM publications;

4 rows in set (0.01 sec)

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

4 rows in set (0.00 sec)

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

6 rows in set (0.00 sec)

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

6 rows in set (0.00 sec)

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;

--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;

6 rows in set (0.00 sec)

10 Many To Many

10.1 CODE: SCHEMA UDEMY REVIEW APP

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);

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

47 rows in set (0.00 sec)

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

14 rows in set (0.00 sec)

MISTAKE

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

658 rows in set (0.00 sec)

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

98 rows in set (0.00 sec)

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

49 rows in set (0.00 sec)

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

12 rows in set (0.01 sec)

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

6 rows in set (0.00 sec)

Two inner JOIN

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

47 rows in set (0.00 sec)

11 linkedin Clone

CREATE DATABASE linkedin_db;
USE linkedin_db;

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

INSERT INTO users(username) VALUES
    ('Sudip'),
    ('Bharat'),
    ('Sundar');

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)
  );

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

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)
	);

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);

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)
  );

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

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)

);

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

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

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)
    );

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)

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

SELECT * FROM users ORDER BY created_at ASC LIMIT 3;

3 rows in set (0.00 sec)

Most recent users

SELECT * FROM users ORDER BY created_at DESC LIMIT 3;

3 rows in set (0.00 sec)

Best day and Worst day for POST

SELECT

DAYNAME(created_at) AS DAY,

COUNT(*) AS register_days

FROM users

GROUP BY DAY

ORDER BY register_days DESC

LIMIT 2;

2 rows in set (0.01 sec)

Find InActive USERS

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

26 rows in set (0.01 sec)

Famous 4 tags

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;

4 rows in set (0.00 sec)