-- create a new database
CREATE DATABASE database_name;

-- see the databases that already exist
SHOW DATABASES;

-- go inside a database before using its tables
USE database_name;

-- delete a database completely
DROP DATABASE database_name;

CREATE DATABASE school;
USE school;

CREATE TABLE table_name(column_name data_type(size),
                        column_name data_type(size),
                        column_name data_type(size));

CREATE TABLE Student(Roll_No int,
                     Stu_Name varchar(40),
                     Address varchar(80));

SHOW TABLES;

-- keep the same column names
CREATE TABLE new_table_name AS
    SELECT column_name, column_name, ...
    FROM existing_table_name WHERE condition;

-- give the new table its own column names
CREATE TABLE new_table_name(new_column_name, new_column_name) AS
    SELECT column_name, column_name, ...
    FROM existing_table_name WHERE condition;

CREATE TABLE accounts_staff AS
    SELECT emp_no, name, salary
    FROM employee WHERE department = 'Accounts';

-- add a new column
ALTER TABLE table_name ADD column_name data_type(size);

-- remove a column
ALTER TABLE table_name DROP COLUMN column_name;

-- change the data type of an existing column
ALTER TABLE table_name MODIFY COLUMN column_name data_type(size);

-- remove the primary key
ALTER TABLE table_name DROP PRIMARY KEY;

-- remove a foreign key
ALTER TABLE table_name DROP FOREIGN KEY column_name;

-- delete the whole table
DROP TABLE table_name;

ALTER TABLE employee ADD email varchar(60);
ALTER TABLE employee MODIFY COLUMN gender varchar(10);
ALTER TABLE Marks DROP FOREIGN KEY Index_No;

CREATE TABLE Student(Roll_No int NOT NULL,
                     Stu_Name varchar(40) NOT NULL,
                     Address varchar(80));

CREATE TABLE Student(Roll_No int UNIQUE,
                     Stu_Name varchar(40) UNIQUE,
                     Address varchar(80));

CREATE TABLE Student(Roll_No int PRIMARY KEY,
                     Stu_Name varchar(40),
                     Address varchar(80));

CREATE TABLE Student(Roll_No int,
                     Stu_Name varchar(40),
                     Address varchar(80),
                     PRIMARY KEY(Roll_No));

CREATE TABLE Marks(Index_No int,
                   Subject varchar(40),
                   Mark varchar(5),
                   FOREIGN KEY(Index_No) REFERENCES Student(Stu_No));

CREATE TABLE Student(Roll_No int CHECK(Roll_No > 1000),
                     Stu_Name varchar(40),
                     Address varchar(80));

CREATE TABLE employee(emp_no int PRIMARY KEY,
                      name varchar(40) NOT NULL,
                      department varchar(30),
                      salary int);

INSERT INTO table_name(column_name1, column_name2, column_name3)
VALUES (DataForColumn1, DataForColumn2, DataForColumn3),
       (DataForColumn1, DataForColumn2, DataForColumn3),
       (DataForColumn1, DataForColumn2, DataForColumn3);

INSERT INTO employee(emp_no, name, department, salary)
VALUES (1, 'Amara', 'Accounts', 15000),
       (2, 'Kumari', 'Sales', 18000),
       (3, 'Fernando', 'Accounts', 22000);

SELECT column_name, column_name, ... FROM table_name;

SELECT name, salary FROM employee;

SELECT * FROM table_name;

SELECT * FROM employee;

SELECT * FROM table_name WHERE condition;
-- or
SELECT column_name1, column_name2, ... FROM table_name WHERE condition;

SELECT * FROM employee WHERE department = 'Accounts';

SELECT * FROM employee
WHERE department = 'Accounts' AND salary > 15000;

SELECT * FROM employee
WHERE department = 'Accounts' OR department = 'Sales';

SELECT * FROM employee WHERE salary BETWEEN 17000 AND 20000;

SELECT * FROM table_name LIMIT number;

SELECT * FROM employee LIMIT 3;

-- total of all salaries
SELECT SUM(salary) FROM employee;                 -- 88000

-- the same thing, but name the answer column "Total"
SELECT SUM(salary) AS Total FROM employee;        -- Total = 88000

-- average salary
SELECT AVG(salary) FROM employee;                 -- 17600

-- highest and lowest salary
SELECT MAX(salary) FROM employee;                 -- 22000
SELECT MIN(salary) FROM employee;                 -- 15000

-- how many rows are in the salary column
SELECT COUNT(salary) FROM employee;               -- 5

-- total of the salaries above 16000 only
SELECT SUM(salary) FROM employee WHERE salary > 16000;   -- 57000

SELECT * FROM table_name ORDER BY column_name;
-- or, written out in full
SELECT * FROM table_name ORDER BY column_name ASC;

SELECT * FROM table_name ORDER BY column_name DESC;

SELECT name, salary FROM employee ORDER BY salary DESC;

SELECT column1, column2, ... FROM table_name GROUP BY column_name;

SELECT department, SUM(salary) AS Total
FROM employee GROUP BY department;

SELECT Index_No, SUM(Mark) AS Total
FROM Marks GROUP BY Index_No;

SELECT DISTINCT column_name1, column_name2, ... FROM table_name;

SELECT DISTINCT department FROM employee;

SELECT column1, column2, ... FROM table_name WHERE column LIKE pattern;

SELECT * FROM employee WHERE name LIKE 'A%';

SELECT * FROM employee WHERE name LIKE '%i';

SELECT * FROM employee WHERE name LIKE '%ma%';

UPDATE table_name SET column1 = value1, column2 = value2, ...
WHERE condition;

UPDATE employee SET name = 'Senaratne' WHERE name = 'Perera';

UPDATE employee SET salary = salary + 3000 WHERE emp_no = 2;

UPDATE employee SET department = 'Management'
WHERE department = 'Accounts';

DELETE FROM table_name WHERE condition;

DELETE FROM employee WHERE emp_no = 1;

DELETE FROM employee WHERE department = 'Accounts';

SELECT table1.column1, column2, table2.column1, column2
FROM table1 INNER JOIN table2
ON table1.common_column_name = table2.common_column_name;

SELECT Student.Stu_No, Student.Stu_Name, Marks.Subject, Marks.Mark
FROM Student INNER JOIN Marks
ON Student.Stu_No = Marks.Index_No;

SELECT table1.column1, column2, table2.column1, column2
FROM left_table LEFT JOIN right_table
ON table1.common_column_name = table2.common_column_name;

SELECT Student.Stu_No, Student.Stu_Name, Marks.Subject, Marks.Mark
FROM Student LEFT JOIN Marks
ON Student.Stu_No = Marks.Index_No;

SELECT table1.column1, column2, table2.column1, column2
FROM left_table RIGHT JOIN right_table
ON table1.common_column_name = table2.common_column_name;

SELECT Student.Stu_No, Student.Stu_Name, Marks.Subject, Marks.Mark
FROM Student RIGHT JOIN Marks
ON Student.Stu_No = Marks.Index_No;

SELECT column_name FROM table1
UNION
SELECT column_name FROM table2;

SELECT column_name FROM table1
UNION ALL
SELECT column_name FROM table2;

SELECT Stu_Name FROM Student
UNION
SELECT name FROM employee;

CREATE DATABASE name;            SHOW DATABASES;
USE name;                        DROP DATABASE name;
SHOW TABLES;                     DROP TABLE name;

CREATE TABLE t(col type(size), col type(size));

ALTER TABLE t ADD col type(size);
ALTER TABLE t DROP COLUMN col;
ALTER TABLE t MODIFY COLUMN col type(size);
ALTER TABLE t DROP PRIMARY KEY;
ALTER TABLE t DROP FOREIGN KEY col;

col int NOT NULL          -- no empty values
col int UNIQUE            -- no repeated values
col int PRIMARY KEY       -- NOT NULL + UNIQUE together
PRIMARY KEY(col)          -- the same, declared at the end
FOREIGN KEY(col) REFERENCES other_table(other_col)
col int CHECK(col > 1000) -- must satisfy a condition

INSERT INTO t(c1, c2) VALUES (v1, v2), (v1, v2);

SELECT * FROM t;
SELECT c1, c2 FROM t WHERE condition;
SELECT * FROM t WHERE c1 = 'x' AND c2 > 10;
SELECT * FROM t WHERE c BETWEEN 10 AND 20;
SELECT * FROM t WHERE c LIKE 'A%';
SELECT DISTINCT c FROM t;
SELECT * FROM t LIMIT 5;
SELECT * FROM t ORDER BY c DESC;
SELECT c, SUM(x) AS Total FROM t GROUP BY c;

UPDATE t SET c1 = v1 WHERE condition;
DELETE FROM t WHERE condition;

SELECT ... FROM a INNER JOIN b ON a.k = b.k;   -- common rows only
SELECT ... FROM a LEFT  JOIN b ON a.k = b.k;   -- all of a, NULLs on the right
SELECT ... FROM a RIGHT JOIN b ON a.k = b.k;   -- all of b, NULLs on the left

SELECT c FROM a UNION     SELECT c FROM b;     -- distinct values
SELECT c FROM a UNION ALL SELECT c FROM b;     -- keeps duplicates