SQL Basic

-- delete a table
DROP TABLE person;

-- create a table
CREATE TABLE person (
id INT(11),
number INT(11),
name VARCHAR(255),
birthday DATE
);

-- insert values
INSERT INTO person VALUES(1, 18, "CECE", "1993-01-23");
INSERT INTO person VALUES(2, 20, "SHAN", "1995-05-15");
INSERT INTO person VALUES(3, 33, "JIN", "1983-07-25");
INSERT INTO person VALUES(4, 12, "XUE", "1973-11-23");


DROP TABLE course;

CREATE TABLE course (
id INT(11),
name VARCHAR(255),
courses VARCHAR(255)

);

INSERT INTO course VALUES(1, "CECE", "COMPUTE");
INSERT INTO course VALUES(2, "SHAN", "math");
INSERT INTO course VALUES(3, "JIN", "english");


-- add some constraints when creating the table

CREATE TABLE fulltable (
sid INT(11) UNIQUE,
name VARCHAR(255) NOT NULL,
age INT(11) CHECK(age>15)
);


-- alter table operations
ALTER TABLE person add GENDER CHAR(255);
ALTER TABLE person change name names CHAR(255);
-- clear all the contents in the table
TRUNCATE TABLE person;
-- delete some rows
DELETE FROM person WHERE id = 1;

-- select
SELECT * FROM person;
SELECT id, number FROM person;
-- drop the duplicate values
SELECT DISTINCT id FROM person;
-- where condition
SELECT id, name, number FROM person WHERE number > 15;
-- and or used in where
SELECT id, name, number FROM person WHERE number > 15 AND id <2;
-- when we are going to look for multiple values
SELECT id, name, number FROM person WHERE id IN (1, 2, 3);
-- between
SELECT id, name, number FROM person WHERE id BETWEEN 1 AND 2;
-- LIKE
SELECT id, name, number FROM person WHERE name LIKE '%CE';
-- sort
SELECT id, name, number FROM person ORDER BY number ASC;
SELECT id, name, number FROM person ORDER BY number DESC;
-- if we have multiple columns to be sorted
SELECT id, name, number FROM person ORDER BY number DESC, id ASC;
-- some math functions
SELECT SUM(number) FROM person;
SELECT COUNT(id) FROM person;
-- not include the duplicate value
SELECT COUNT(DISTINCT id) FROM person;
-- if we need to calculate the sum with the same name
SELECT name, SUM(number) FROM person GROUP BY name;
-- use the value got by the functions, use HAVING
SELECT name, SUM(number) FROM person GROUP BY name HAVING SUM(number);
-- ALIAS for columns or table
-- like after getting the sum(number), change the name into sum
SELECT name, SUM(number) "sum" FROM person GROUP BY name;

-- table join
-- left join, also called inner join, only select the same values
SELECT person.id, person.name, course.courses, sum(person.number) "sum"
FROM person, course
WHERE person.id = course.id
GROUP BY person.id;
-- concatenate 
SELECT CONCAT(person.name, course.courses)
FROM person, course
WHERE person.id = 1;
-- substr
SELECT SUBSTR(name, 2, 2)
FROM person
WHERE name = 'CECE';

-- TRIM delete the space in the begining and the end
SELECT TRIM('    sample    ');
-- LTRIM, RTRIM
SELECT LTRIM('   sample    ');
SELECT RTRIM('    sample    ');

-- UNION ======= OR
-- UNION merge two sql results WITHOUT THE duplicate
SELECT id FROM person 
UNION
SELECT id FROM course;
-- UNION merge two sql results with THE duplicate
SELECT id FROM person 
UNION ALL
SELECT id FROM course;
-- INTERSECT ======= AND
-- in SQL serves, it can be
-- SELECT id FROM person 
--  INTERSECT
-- SELECT id FROM course;
-- MINUS:only output the different values in the two sql results
-- SELECT id FROM person 
-- MINUS
-- SELECT id FROM course;
-- But in MySQL, there are no INTERSECT AND MINUS, BELOWS it is an example from the internet

-- intersect
SELECT id, nickname, playNum, COUNT(*)
FROM (SELECT id, nickname, playNum
    FROM t1
    UNION ALL
    SELECT id, nickname, playNum
    FROM t2
    ) a
GROUP BY id, nickname, playNum
HAVING COUNT(*) > 1;
-- minus
SELECT t1.id, t1.nickname, t1.playNum
FROM t1 LEFT JOIN t2 ON t1.id = t2.id
WHERE t1.nickname != t2.nickname
    OR t1.playNum != t2.playNum;

-- CASE STUDY
-- if-then in SQL
SELECT name, CASE name
	WHEN 'CECE' THEN number*2
	ELSE number
	END
"new_number"
FROM person

-- RANK
-- MEDIUM
-- PERCENTAGE