Create Database sql content

sql-commands.sql

CREATE SCHEMA movies_db_schema CHARATER SET = utf8;
CREATE SCHEMA IF NOT EXISTS movies_db_schema;
CREATE DATABASE IF NOT EXISTS movies_db_database;

-- Create

USE "movies_db_database";

CREATE TABLE actors (name VARCHAR(50) NOT NULL);
CREATE TABLE movies (title VARCHAR(200) NOT NULL, year INTEGER NULL);
CREATE TABLE products (name VARCHAR(100), description TEXT, stock_count INTEGER);

SHOW ENGINES;

CREATE TABLE test ENGINE InnoDB;

INSERT INTO movies VALUES ("Avatar", 2009);
INSERT INTO movies (year, title) VALUES (2009, "Avatar"), (NULL, "Avatar 2");
INSERT INTO movies SET title = "Back to the Future", year = 1985;

UPDATE movies SET year = 2015 WHERE title = "Avatar 2";

SET SQL_SAFE_UPDATES = 0; -- allow to set year in the future

-- Read

SELECT movies.year FROM movies;
SELECT year, title FROM movies;
SELECT * FROM movies;

SELECT * FROM movies WHERE year = 1999;
SELECT * FROM movies WHERE year >= 1999;
SELECT * FROM movies WHERE year != 1999;

SELECT * FROM movies WHERE year = 1999 AND title = "The Matrix";
SELECT * FROM movies WHERE year = 1998 OR year = 1998;
SELECT * FROM movies WHERE year BETWEEN 1998 AND 2000;
SELECT * FROM movies WHERE title LIKE "%godfather%";

SELECT * FROM movies ORDER BY year DESC;
SELECT * FROM movies ORDER BY year ASC, title ASC;

SELECT * FROM movies LIMIT 10 OFFSET 10;
SELECT * FROM movies LIMIT 20, 10;

SELECT * FROM movies WHERE year IS NULL;
SELECT * FROM movies WHERE year IS NOT NULL;

-- Update

UPDATE movies SET year = 2016, title = "Avatar Reloaded" WHERE title = "Avatar 2";

-- Delete

DELETE FROM movies WHERE title = "Avatar Reloaded" AND year = 2016;

-- Rename table

RENAME TABLE movies TO movies_table;
RENAME TABLE movies TO movies_table, actors TO actors_table;

DROP TABLE IF EXISTS movies, actors, reviews; -- remove the table
DROP SCHEMA IF EXISTS movies_db_2;
TRUNCATE movie_table; -- delete table data without remove the table

-- Modify

ALTER TABLE movies ADD COLUMN genre VARCHAR(100);
ALTER TABLE actors ADD (birthday VARCHAR(100), death DATE);
ALTER TABLE t_movies ADD COLUMN pk_id INTEGER AUTO_INCREMENT PRIMARY KEY FIRST;

ALTER TABLE actors CHANGE COLUMN birthday birth_place VARCHAR(100);
ALTER TABLE movies CHANGE COLUMN year year_released YEAR;

ALTER TABLE movies DROP year_released;

-- JOIN TABLE

CREATE TABLE genres (id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL UNIQUE KEY);

INSERT INTO genres (name) VALUES ("Sci Fi"); -- added (id: 1)
INSERT INTO genres (name) VALUES ("Sci Fi"); -- error, duplicate key

ALTER TABLE movies ADD COLUMN id INTEGER AUTO_INCREMENT PRIMARY KEY FIRST; -- add a column in first place

ALTER TABLE movies ADD COLUMN genre_id INTEGER NULL, ADD CONSTRAINT FOREIGN KEY (genre_id) REFERENCES genres(id); -- create a column to refecenes to a column of genre table

UPDATE movies SET genre_id = 1 WHERE id = 8 or id = 9; -- add Sci Fi genre to movies (base on id)

-- now we need to join data:

SELECT * FROM movies JOIN genres ON movies.genre_id = genres.id; -- return the join of these two table based on genre id
SELECT * FROM movies INNER JOIN genres ON movies.genre_id = genres.id; -- return the subset of the common data
SELECT * FROM movies LEFT OUTER JOIN genres ON movies.genre_id = genres.id; -- return the keep movies data after the join
SELECT * FROM movies RIGHT OUTER JOIN genres ON movies.genre_id = genres.id; -- return the keep genres data after the join
SELECT movies.title, genre.name FROM movies LEFT OUTER JOIN genres ON movies.genre_id=genres.id; -- return the keep only column join for these tables ("Alliasing")

-- return :
-- | title	|	name	|
-- | -----	|	------	|
-- | Alien	|	Sci Fi	|
-- | Aliens	|	Sci Fi	|

SELECT movies.title AS movie_title genre.name AS genre_name
	FROM movies LEFT OUTER JOIN genres
	ON movies.genre_id = genres.id
	WHERE genres.name IS NOT NULL;
	-- return the keep only column join for these tables ("Alliasing") and rename the column to genre_name etc.


-- CALCULATING

SELECT COUNT(id) AS reviews_count FORM reviews WHERE movie_id = 1; -- reviews_count: 43...

SELECT MIN(score) AS minimum_score FORM reviews WHERE movie_id = 1; -- minimum_score: 1...
SELECT MAX(score) AS maximum_score FORM reviews WHERE movie_id = 1; -- maximum_score: 5...
SELECT MIN(score) AS minimum_score, MAX(score) AS maximum_score FORM reviews WHERE movie_id = 1; -- minimum_score: 1 & maximum_score: 5...

SELECT SUM(score) AS total_score WHERE movie_id = 1; -- total_score: 122...

SELECT SUM(score) / COUNT(score) AS average_score FORM reviews WHERE movie_id = 1; -- average_score: 2.8372...
SELECT AVG(score) AS average_score FORM reviews WHERE movie_id = 1; -- average_score: 2.8372...

SELECT AVG(score) AS average FROM reviews GROUP BY movie_id; -- Group all reviews by "movie_id" and get the average "score" and alias it as "average".

-- in place of showing 1 result for all movies, that show all separated results base on movie_id
SELECT movie_id,
MIN(score) AS minimum_score,
MAX(score) AS maximum_score,
AVG(score) AS average_score
FROM reviews GROUP BY movie_id;

-- the same with movie name from movie_id in review. And that don't show null result because of join (inner)
SELECT movie_id,
MIN(score) AS minimum_score,
MAX(score) AS maximum_score,
AVG(score) AS average_score
FROM reviews JOIN reviews
ON movies.id = reviews.movie_id
GROUP BY movie_id;

-- here if the result is null -> set to 0
-- IFNULL() take two arguments : the value to check, the value to set in case of null
SELECT movie_id,
MIN(score) AS minimum_score,
MAX(score) AS maximum_score,
IFNULL(AVG(score), 0) AS average_score
FROM reviews LEFT OUTER JOIN reviews
ON movies.id = reviews.movie_id
GROUP BY movie_id;

-- filter result
SELECT movie_id,
MIN(score) AS minimum_score,
MAX(score) AS maximum_score,
IFNULL(AVG(score), 0) AS average_score
FROM reviews LEFT OUTER JOIN reviews
ON movies.id = reviews.movie_id
WHERE year_released > 2000
GROUP BY movie_id HAVING average_score > 3;

-- Like before, select the average "score" as "average", setting to 0 if null, by grouping the "movie_id" from the "reviews" table. Also, do an outer join on the "movies" table with its "id" column and display the movie "title" before the "average". Finally, filter out any "average" score over 2.
SELECT title, IFNULL(AVG(score),0) AS average FROM movies LEFT OUTER JOIN reviews ON movies.id = reviews.movie_id GROUP BY movie_id HAVING average <=2;

-- STRING modification
SELECT first_name,
UPPER(last_name), LOWER(email), LENGTH(username) AS username_length
FROM users HAVING username_length < 19;

SELECT CONCAT(first_name, " ", UPPER(last_name)) AS full_name FROM users;
SELECT CONCAT(first_name," (", (username),")") AS display_name FROM users;
SELECT CONCAT( SUBSTRING( first_name, 1, 1), ". ", UPPER(last_name) ) AS name FROM users;

SELECT SUBSTRING(LOWER(email), 1, 10,  "...") AS partial_email FROM users;

SELECT TRIM('    bar   '); -- -> 'bar'
SELECT TRIM( LEADING 'x' FROM 'xxxxbarxxxx') -- -> 'barxxxx'
SELECT TRIM( BOTH 'x' FROM 'xxxbarxxx') -- -> 'bar'
SELECT TRIM( TRAILING 'xyz' FROM 'barxxyz') -- -> 'barx'

--DATE & TIME FUNCTIONS
DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y'); -- -> Sunday October 2009


-- SHOW REQUEST DETAILS
EXPLAIN SELECT * FROM users WHERE last_name="Charkley";

-- CREATE INDEX
CREATE INDEX last_name_idx ON users(last_name); -- -> that optimize performance one row in place of all

-- CREATE USERS
GRANT SELECT ON databasename.* TO user1@'%' INDENTIFIED BY 'passwordhere'; -- read only
GRANT SELECT, INSERT, UPDATE, DELETE ON databasename.* TO user2@'%' INDETIFIED BY 'passwordtwo'; -- CRUD access
GRANT ALTER, CREATE, DROP ON databasename.* TO user3@'%' INDETIFIED BY 'passwordthree'; -- DDL access

FLUSH PRIVILEGES; -- reset and reload all acces/privileges to the databases