Advanced SQL Syntax

Relational Database Creation

First, create a table without foreign key. Then have another table with.


CREATE TABLE member (
emp_id INT PRIMARY KEY,
name VARCHAR(25),
family_id INT, 
/*Family id -- a foreign key pointing to family table*/
);

CREATE TABLE family (
family_id INT PRIMARY KEY,
family_name VARCHAR(25),
parent_id INT,
FOREIGN KEY(parent_id) REFERENCES member(emp_id) ON DELETE SET NULL
/*parent_id points back to member table*/
);

Then alter the first table, to make foreign keys. Because another table is created to provide client.


ALTER TABLE member
ADD FOREIGN KEY(family_id)
REFERENCES family(family_id)
ON DELETE SET NULL;

For two primary keys as foreign keys, you need references as, ON DELETE CASCADE

Insert into RD

First, insert first table, with NULL foreign keys' values. For the second table, all values are provided.


INSERT INTO member VALUES(1,'Leo',NULL)
INSERT INTO family VALUES(1,'MICHAEL',1)

Update the first table by filling in the missing foreign key value.


UPDATE member
SET branch_id = 1
WHERE emp_id = 1;

This is because the primary key in family is not created yet.

Advanced queries

Display column name as another name.


SELECT name AS forename FROM member;

Display distinct values of column


SELECT DISTINCT family_id FROM member;

Use function to operate, like counting. Others like AVG, SUM.


SELECT COUNT(emp_id) FROM member;

Group by a feature. The second attribute selected should be the group by attribute.


SELECT COUNT(family_id),family_id 
FROM member
GROUP BY family_id;

Similar to Regular Expression, % can represent any number of characters, _ is any one character.


SELECT * FROM member
WHERE name LIKE 'L%';

Union like stacking up


SELECT name AS stack_names
FROM member
UNION
SELECT family_name 
FROM family;
/*2 columns from tables will stack up as one*/

Join (Left or Right as well)


SELECT member.emp_id,member.name,family.family_name,family.parent_id
FROM member
JOIN family
ON member.emp_id = family.parent_id;

Nested Queries


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 > 30000;
);

Trigger (Follow-up)

Entity Relationship Diagram (ERD)

Very Long Queries


select t1.AddressID,t1.FullAddress,t1.PricePaid,t2.AddressID,t2.FullAddress,t2.EPCType,t2.AddressID,t2.FloorArea from flat2db.landregistrypricepaid as t1
inner join flat2db.epc as t2 on t1.AddressID = t2.AddressID 
where t1.PostCode like 'NW2 %' and t2.PostCode like 'NW2 %' and t2.EPCType = 'DOMESTIC'
limit 10;

Having multiple record


SELECT column_name
  FROM table_name
 GROUP BY column_name
HAVING COUNT(column_name) = 1;