--- title: SQL syntax description: SQL tutorial from freeCodeCamp image: https://encrypted-tbn0.gstatic.com/images?q=tbn:ANd9GcRxmIv9kch-FoeU43XS6MQhPS2WoirHq-FVMw&usqp=CAU tags: sql --- # SQL syntax {%hackmd @themes/dracula %} > Included here are some notes I jotted down while watching [this SQL tutorial](https://www.youtube.com/watch?v=HXV3zeQKqGY&t=1396s) from [freeCodeCamp](https://www.freecodecamp.org/). Kudos to freeCodeCamp, which offers the best free resources to self-taught developers. - [Haowen Jiang](https://howard-haowen.rohan.tw/) [toc] ## Comments ```sql= -- This is a comment in SQL! ``` ## Data types ```sql= -- integers INT -- 5 digits in total, 2 digits after the decimal point DECIMAL(5, 2) -- max string len = 3 VARCHAR(3) -- binary data BLOB -- 2022-06-06 DATE -- 2022-06-06 22:22:22 TIMESTAMP ``` ## Creating tables - Create a table ```sql= CREATE TABLE student ( student_id INT AUTO_INCREMENT, name VARCHAR(20), major VARCHAR(20) DEFAULT 'undecided', PRIMARY KEY(student_id) ); ``` - Describe a table ```sql= DESCRIBE student; ``` - Delete a table ```sql= DROP TABLE student; ``` - Create a column in a table ```sql= ALTER TABLE student ADD gpa DECIMAL(3,2); ``` - Delete a column from a table ```sql= ALTER TABLE student DROP COLUMN gpa; ``` ## Inserting data - Insert a row ```sql= INSERT INTO student VALUES( 1, 'Jack', 'Biology' ); ``` ## Updating and deleting data - Update all instances of one value with another under a condition ```sql= UPDATE student SET major = 'Bio' WHERE major = 'Biology'; ``` - Update all instances of two values with one under a condition ```sql= UPDATE student SET major = 'Biochemistry' WHERE major = 'Bio' OR major = 'Chemistry'; ``` - Update multiple columns of a row under a condition ```sql= UPDATE student SET name = 'Tom', major = 'undecided' WHERE student_id = 1; ``` - Delete a specific row ```sql= DELETE FROM student WHERE student_id = 5; ``` ## Basic queries - Select all columns ```sql= SELECT * FROM student; ``` - Select specific columns ```sql= SELECT name, major FROM student; ``` - Select specific columns (another way) ```sql= SELECT student.name, student.major FROM student; ``` - Order by a column in ascending order (by default) ```sql= SELECT name, major FROM student ORDER BY name; ``` - Order by a column in descending order ```sql= SELECT name, major FROM student ORDER BY name DESC; ``` - Order by multiple columns ```sql= SELECT * FROM student ORDER BY name, student_id; ``` - Limit the results by a number ```sql= SELECT * FROM student LIMIT 5; ``` - Filter the results by a comparison operator ```sql= SELECT * FROM student -- = equal to -- <> NOT equal to WHERE major = 'Biology'; ``` - Filter the results by comparing against a list ```sql= SELECT * FROM student WHERE name IN ('Kate', 'Mike'); ``` - Give an alias to a column ```sql= SELECT first_name AS forename FROM employee ``` - Select distinct values in a column ```sql= SELECT DISTINCT last_name FROM employee ``` - Summary formula ```sql= SELECT column1 as alias, DISTINCT column2 FROM any_table WHERE codition1='text' AND condition2>5 ORDER BY column3 ASC, column4 DESC LIMIT 5; ``` ## Built-in functions ### The schema for the company database ![](https://www.mikedane.com/databases/sql/company-database.png) ### COUNT() - Count the number of rows in a column ```sql= SELECT COUNT(emp_id) FROM employee; ``` - Count the number of female employees born after 1970 ```sql= SELECT COUNT(emp_id) WHERE sex = 'F' AND birth_date > '1970-01-01' FROM employee; ``` - Count the number of male and female employees ```sql= SELECT sex, COUNT(sex) FROM employee GROUP BY sex; ``` ### AVG() - Find the average salary of male employees ```sql= SELECT AVE(salary) FROM employee WHERE sex = 'M'; ``` ### SUM() - Find the total salary of employees ```sql= SELECT SUM(salary) FROM employee; ``` - Find the total sales of each employee ```sql= SELECT emp_id, SUM(total_sales) -- The works_with table has three columns: -- emp_id, client_id, and total_sales. FROM works_with GROUP BY emp_id; ``` ## Wildcards - `%`: for any number of characters, equivalent to `*` in Regex - `_`: for a single character, equivalent to `.` in Regex - Find any employee whose last name ends with `son` ```sql= SELECT emp_id, first_name, last_name FROM employee WHERE last_name LIKE '%son'; ``` - Find any employee born in September ```sql= SELECT emp_id, first_name, last_name FROM employee WHERE birth_date LIKE '____-09%'; ``` ## Union - Two constraints with `UNION`: - The number of columns has to match. - The data types have to match. - Find a list of all client names and supplier names ```sql= SELECT client_name AS client_supplier FROM client UNION SELECT supplier_name FROM supplier; ``` ## Join ### Inner join - Find all branches and the names of their managers, and include only employees that manage a branch ```sql= SELECT employee.emp_id, employee.first_name, branch.branch_name FROM employee JOIN branch ON employee.emp_id = branch.mgr_id; ``` ### Left join - Find all branches and the names of their managers, and include all employees ```sql= SELECT employee.emp_id, employee.first_name, branch.branch_name FROM employee LEFT JOIN branch ON employee.emp_id = branch.mgr_id; ``` ### Right join - Find all branches and the names of their managers, and include all branches ```sql= SELECT employee.emp_id, employee.first_name, branch.branch_name FROM employee RIGHT JOIN branch ON employee.emp_id = branch.mgr_id; ``` ## Nested queries - Find names of all employees who have sold over 30K to a single client ```sql= -- Start from the inner querry -- Then embed it to IN() in the outer query SELECT employee.first_name, employee.last_name FROM employee WHERE employee.emp_id IN ( SELECT works_with.emp_id FROM works_with WHERE works_with.total_sales > 3000 ) ``` ## Trigger - Create a trigger in the SQL terminal ```shell= # change the default delimiter from ; to $$ DELIMITER $$ # create a trigger CREATE TRIGGER my_trigger BEFORE INSERT ON employee FOR EACH ROW BEGIN INSERT INTO trigger_test VALUES('added new employee') END$$ # change the delimiter back to the default DELIMITER ; ``` - What the trigger above does is that whenever a new row is inserted into the `employee` table, a fixed message will be inserted into the `trigger_test` table. - Another trigger, but with more conditions ```shell= # change the default delimiter from ; to $$ DELIMITER $$ # create a trigger CREATE TRIGGER my_trigger2 BEFORE INSERT ON employee FOR EACH ROW BEGIN IF NEW.sex 'M' THEN INSERT INTO trigger_test VALUES('added male employee'); ELSEIF NEW.sex 'F' THEN INSERT INTO trigger_test VALUES('added female employee'); ELSE INSERT INTO trigger_test VALUES('added other employee'); END IF; END$$ # change the delimiter back to the default DELIMITER ; ``` - What the trigger above does is that whenever a new row is inserted into the `employee` table, a different message will be inserted into the `trigger_test` table depending on the sex of the newly inserted employee. - Delete a trigger in the SQL terminal ```shell= DROP TRIGGER my_trigger ``` ## Entity relationship diagrams ![](https://www.mikedane.com/databases/sql/company-erd.png) - Entity: e.g. `student` - Weak entity: an entity that cannot be uniquely identified by its attributes alone, e.g. `exam`, which is not independent from `class` - Attribute: features about an entity, e.g. `student_id`, `gpa` - Composite attribute: attribute that consists of multiple attributes, e.g. `name` consisting of `last_name` and `first_name` - Multi-valued attribute: attribute that has multiple values, e.g. `clubs`, whose values might be `guitar`, `dancing`, etc. - Derived attribute: attribute that can be derived from other attributes, e.g. `has_honors`, which can be derived from `gpa` - Relationship: a relationship between two entities, eg. `student` holds the relationship `takes` with `class`. - Partial participation: e.g. only some students take a specific class - Total participation: e.g. all classes are taken by at least a student - Relationship attribute: attribute about a relationship, e.g. `grade` in the `takes` relationship between `student` annd `class` - Relationship cardinality: - one-to-one: 1:1 - one-to-many: 1:N - many-to-one: N:1 - many-to-many: N:M - Identifying relationship: a relationship that serves to uniquely identify a weak entity, e.g. `has` between `class`, a normal entity, and `exam`, a weak entity ## Postscript :::info Not until I finished writing the notes above did I find [this page](https://www.mikedane.com/databases/sql/), where Mike Dane, the instructor, already created a nice-looking page for all the SQL syntax covered in this tutorial. :::