dotku
5/1/2016 - 5:07 AM

Database Lesson #3 of 8 - The Structured Query Language (SQL)

Database Lesson #3 of 8 - The Structured Query Language (SQL)

0-README Database Lesson 3.md
content_copyfile_download
  • Rendered
  • Source

Original tutorial you can found out over here: Database Lesson by Dr. Daniel Soper

This gist is my note for the tutorial.

Appendix Get Foreign Key List
content_copyfile_download
# List all foreign key in the system

select *
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE = 'FOREIGN KEY'
DEMO ALTER 1
content_copyfile_download
ALTER TABLE Employee
  ADD CONSTRAINT empPk PRIMARY KEY(empId);
DEMO ALTER 2
content_copyfile_download
ALTER TABLE EmployeeSkill
  ADD CONSTRAINT empSkillPk
    PRIMARY KEY(empId, skillId);
DEMO ALTER 3
content_copyfile_download
# add foreign key constraints to an existing table
ALTER TABLE Employee
  ADD CONSTRAINT empFk FOREIGN KEY(deptId)
    REFERENCES Department(deptId);
DEMO CONSTRAINT
content_copyfile_download
# DROP CONSTRAINT 
ALTER TABLE Employee DROP CONSTRAINT empFk;

# CHECK CONSTRAINT
ALTER TABLE Project
  ADD CONSTRAINT projectCheckDates
    CHECK (startDate < endDate);
DEMO CREATE with CONSTRAINT 3.sql
content_copyfile_download
# If you have duplicate key error check out the solution in
# http://stackoverflow.com/questions/36963260/mysql-duplicate-key-on-create-table

CREATE TABLE EmployeeSkill (
  empId Integer NOT NULL,
  skillId Integer NOT NULL,
  skillLevel Integer NULL,
  CONSTRAINT empSkillPk PRIMARY KEY(empId, skillId),
  CONSTRAINT empFk FOREIGN KEY(empId) REFERENCES Employee(empId),
  CONSTRAINT skillFk FOREIGN KEY(skillId) REFERENCES Skill(skillId)
)
DEMO CREATE with CONSTRAINT 4.sql
content_copyfile_download
CREATE TABLE EmployeeSkill (
  empId Integer NOT NULL,
  skillId Integer NOT NULL,
  skillLevel Integer NULL,
  CONSTRAINT empSkillPk PRIMARY KEY(empId, skillId),
  CONSTRAINT empFk FOREIGN KEY(empId) 
      REFERENCES Employee(empId) ON DELETE CASCADE,
  CONSTRAINT skillFk FOREIGN KEY(skillId) 
      REFERENCES Skill(skillId) ON UPDATE CASCADE
)
DEMO DELETE
content_copyfile_download
DELETE FROM Employee
WHERE empId = 29;
DEMO Function
content_copyfile_download
# COUNT

SELECT COUNT(*)
FROM Employee;

# MIN
# MAX
# SUM
# AVG
# STDEV, 平均差, 用于了解变化幅度
DEMO GROUP
content_copyfile_download
SELECT deptId,
  COUNT(*) AS numberOfEmployees
FROM Employee
GROUP BY deptId;

| deptId | numberOfEmployees |
DEMO GROUP with HAVING
content_copyfile_download
# HAVING is similar to WHERE, but genenrally used by GROUP BY

SELECT salespersonId, salespsersonLastName,
    SUM(saleAmount) AS totalSales
FROM Sales
GROUP BY salepersonId, salepersonLastName,
HAVING SUM(saleAmount) >= 10000;
DEMO INSERT INTO
content_copyfile_download
INSERT INTO Employee (empId, salaryCode, lastName)
  Values(62, 11, 'Halpert');
DEMO JOIN.md
content_copyfile_download
  • Rendered
  • Source
# Dan's Typology of Database Joins

Database Joins: {
  Outer Join: {Left Outer Join, Full Outer Join, Right Outer Join},
  Inner Join
}

# Inner Join
SELECT empName, deptName
FROM Employee AS E, Depratment AS D
WHERE E.deptId = D.deptId

# JOIN ON
SELECT empName, deptName
FROM Employee e INNER JOIN Department d 
  ON e.deptId = d.deptId
WHERE d.deptName NOT LIKE 'Account%';

DEMO LIKE
content_copyfile_download
# (%) wildcard for multiple character
# (_) wildcard for single character

SELECT empName
FROME Employee
WHERE empName LIKE 'Da%';

SELECT empId
FROM Employee
WHERE phone LIKE '123-456-_ _ _ _';
DEMO ORDER BY
content_copyfile_download
SELECT *
FROM Employee
ORDER By empName DESC;

#! change the default order by DESC

ALTER TABLE TableName 
ORDER BY id DESC
DEMO SELECT Queries
content_copyfile_download
SELECT empName
FROM Employee
WHERE empId = 33;

SELECT empId, empName
FROM Employee;

SELECT *
FROM Employee;

# only the unique deptId will return

SELECT DISTINCT deptId
FROM Employee
DEMO SELECT by WHERE
content_copyfile_download
# IN as OR Relation (Union)

SELECT empName
FROM Employee
WHERE deptId IN (4,8,9);

SELECT empName
FROM Employee
WHERE deptId NOT IN (4,8,9);

# Between as Range 

SELECT empName
From Employee
WHERE salaryCode BETWEEN 10 AND 45;
DEMO UPDATE
content_copyfile_download
UPDATE Employee
SET phone = '123-456-7890'
WHERE empId = 29;

UPDATE Employee
SET deptId = 4
WHERE empName Like 'Da%';
DEMO VIEW
content_copyfile_download
CREATE VIEW SalesDepartment AS
SELECT *
FROM Employee
WHERE deptId = (SELECT deptId FROM Department WHERE deptName = 'Sales');
DEMO subquery
content_copyfile_download
# Noncorrelated subquery
# inner query only run once

SELECT empName
FROM Employee
WHERE deptId IN
  (SELECT deptId
  FROM Department
  WHERE deptName LIKE 'Account%');

# Correlated subquery
# inner query run many times
# (!) notice 'e' on the sample below

SELECT empName
FROM Employee e
WHERE empSalary >
  (SELECT AVG(empSalary)
  FROM Employee
  WHERE deptId = e.deptId);