MySQL Cheat Sheet

booking.sql

-- Simulate creating booking.com database
SHOW DATABASES;
CREATE DATABASE booking_company;
USE booking_company;
SELECT DATABASE ();
SHOW CREATE DATABASE booking_company;

-- create guests table
CREATE TABLE guests (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR (255),
reservation_date DATE, -- YYYY-MM-DD
country_code CHAR(3),
PRIMARY KEY (id)
);

-- Insert columns in guests table
INSERT INTO guests (name, email, reservation_date, country_code)
VALUES ('John', '[email protected]', '2022-07-15' , 'CA');

INSERT INTO 
	guests (name, email, reservation_date, country_code)
VALUES 
	('Brigitte', '[email protected]', '2022-02-10' , 'FR'),
	('Tarek', '[email protected]', '2022-07-15' , 'EG'),
	('Ajit', '[email protected]', '2022-02-15' , 'IN'),
	('Mariusz', '[email protected]', '2022-01-10' , 'PL');

SELECT * FROM guests;
SELECT * FROM guests LIMIT 2;
SELECT name, email FROM guests;
SELECT id AS 'guest_id', name AS 'guest_name'
FROM guests;
-- SELECT * FROM guests ORDER BY name ASC; 
SELECT * FROM guests ORDER BY name DESC;
SELECT * FROM guests ORDER BY email ASC;

-- returning any name starting with T letter
SELECt * From guests
WHERE email LIKE "j%";

SELECt * From guests
WHERE name LIKE "%tt%";

-- Operators AND / OR

SELECT * FROM guests
WHERE name LIKE '%ek%' OR country_code = 'PL';

SELECT * FROM guests
WHERE name LIKE '%tt%' AND country_code = 'FR';


-- Create reservations table
CREATE TABLE reservations (
id INT NOT NULL AUTO_INCREMENT,
room_number INT NOT NULL,
room_type VARCHAR(255) NOT NULL,
guest_id INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY(guest_id) REFERENCES guests(id)
);

USE booking_company;
ALTER TABLE reservations
RENAME COLUMN room_type TO roomType;

ALTER TABLE reservations
RENAME COLUMN roomType TO room_type;

-- ALTER TABLE reservations
-- DROP COLUMN room_type;

INSERT INTO 
reservations (room_number, room_type, guest_id)
VALUES
(01, 'smoking', 1),
(014, 'non-smoking', 2),
(021, 'non-smoking', 3),
(043, 'smoking', 4),
(037, 'non-smoking', 5);

SELECT * FROM reservations;
SELECT room_type FROM reservations; 
SELECT DISTINCT room_type FROM reservations;

-- Updating the column
UPDATE reservations
SET room_type = 'smoking'
WHERE id = 2;

SELECT * FROM reservations;
-- TRUNCATE table reservations; -- destroys all data in your DB

SELECT * FROM reservations
WHERE room_type = 'smoking';

SELECT * FROM reservations
WHERE room_number < 21; -- options for < > = operators

SELECT * FROM reservations
WHERE room_number BETWEEN 14 AND 21;

DELETE FROM reservations WHERE id = 5;

-- Connecting (Joining) tables
-- ---------------------------
SELECT * FROM reservations;
SELECT * FROM guests;

-- Inner Join : returns records that have matching values in both tables
SELECT * FROM reservations
INNER JOIN guests ON guests.id = reservations.guest_id;

-- Left Join
SELECT * FROM reservations
LEFT JOIN guests ON guests.id = reservations.guest_id;

SELECT * FROM guests
LEFT JOIN reservations ON guests.id = reservations.guest_id;

SELECT * FROM reservations
RIGHT JOIN guests ON guests.id = reservations.guest_id;


CREATE DATABASE football_team;

USE football_team;

CREATE TABLE players (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
joining_date DATE NULL,
PRIMARY KEY (id)
);

CREATE TABLE coaches (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
joining_date DATE NULL,
player_id INT NOT NULL, 
PRIMARY KEY (id),
FOREIGN KEY (player_id) REFERENCES players(id)
);

INSERT INTO players (name, joining_date)
VALUES ('ronaldo', '1993-02-15'),
('rivaldo', '1993-04-11'),
('carlos', '1991-01-15'),('romario', '1994-01-01');

SELECT * FROM players;

INSERT INTO coaches (name, joining_date, player_id)
VALUES ('C. Pasconi', '1990-01-15', 2),
('C. Roni', '1989-06-03', 4),
('C. Pasconi', '1990-01-15', 3),('C. Mino', '1988-04-01', 1);

SELECT * FROM coaches;

-- UNION statement
SELECT id, name, joining_date FROM players
UNION
SELECT id, name, joining_date FROM coaches;

-- Aggregate methods
-- COUNT(), MAX(), MIN(), SUM(), AVG()
SELECT AVG (joining_date) FROM coaches;

-- GROUP BY Statement
SELECT DATABASE();
SELECT name FROM players WHERE id = 1;
SELECT name, COUNT(joining_date) FROM players GROUP BY joining_date;

USE booking_company;

SELECT name, country_code, COUNT(id) FROM guests
GROUP BY id
ORDER BY name DESC;

db.sql

-- Database-Level
DROP DATABASE databaseName                 -- Delete the database (irrecoverable!)
DROP DATABASE IF EXISTS databaseName       -- Delete if it exists
CREATE DATABASE databaseName               -- Create a new database
CREATE DATABASE IF NOT EXISTS databaseName -- Create only if it does not exists
SHOW DATABASES                             -- Show all the databases in this server
USE databaseName                           -- Set the default (current) database
SELECT DATABASE()                          -- Show the default database
SHOW CREATE DATABASE databaseName          -- Show the CREATE DATABASE statement
 
-- Table-Level
DROP TABLE [IF EXISTS] tableName, ...
CREATE TABLE [IF NOT EXISTS] tableName (
   columnName columnType columnAttribute, ...
   PRIMARY KEY(columnName),
   FOREIGN KEY (columnNmae) REFERENCES tableName (columnNmae)
)
SHOW TABLES                -- Show all the tables in the default database
DESCRIBE|DESC tableName    -- Describe the details for a table
ALTER TABLE tableName ...  -- Modify a table, e.g., ADD COLUMN and DROP COLUMN
ALTER TABLE tableName ADD columnDefinition
ALTER TABLE tableName DROP columnName
ALTER TABLE tableName ADD FOREIGN KEY (columnNmae) REFERENCES tableName (columnNmae)
ALTER TABLE tableName DROP FOREIGN KEY constraintName
SHOW CREATE TABLE tableName        -- Show the CREATE TABLE statement for this tableName
 
-- Row-Level
INSERT INTO tableName 
   VALUES (column1Value, column2Value,...)               -- Insert on all Columns
INSERT INTO tableName 
   VALUES (column1Value, column2Value,...), ...          -- Insert multiple rows
INSERT INTO tableName (column1Name, ..., columnNName)
   VALUES (column1Value, ..., columnNValue)              -- Insert on selected Columns
DELETE FROM tableName WHERE criteria
UPDATE tableName SET columnName = expr, ... WHERE criteria
SELECT * | column1Name AS alias1, ..., columnNName AS aliasN 
   FROM tableName
   WHERE criteria
   GROUP BY columnName
   ORDER BY columnName ASC|DESC, ...
   HAVING groupConstraints
   LIMIT count | offset count
 
-- Others
SHOW WARNINGS;   -- Show the warnings of the previous statement

Add MySQL commands for the YT tutorial