Database Normalization

• First Norm: only singled value attribute
• Second Norm: No Partial Dependency (If proper subset of candidate key determines non-prime attribute, it is called partial dependency.)
• Third Norm: No Transitive Dependency.(When a non-prime attribute depends on other non-prime attributes rather than depending upon the prime attributes or primary key.)

Primary Key, Candidate Key, Foreign Key

• Primary Key vs. Candidate Key
  • Both Primary and Candidate keys can uniquely identify records in a table on the database.
  • Both Primary and Candidate keys have constraints UNIQUE and NOT NULL.
  • An example of Primary key and Candidate key can be ID and SSN number in an Employee table, Since both can identify each employee uniquely they are candidate key and anyone can become the primary key.
• Foreign Key
  • A Foreign key is a column in one table which is the primary key on another table.
  • Foreign key can be duplicate opposite to primary key which is always unique.

Constraints

• NOT NULL - Ensures that a column cannot have a NULL value
• UNIQUE - Ensures that all values in a column are different
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
• FOREIGN KEY - Uniquely identifies a row/record in another table
• CHECK - Ensures that all values in a column satisfies a specific condition
• DEFAULT - Sets a default value for a column when no value is specified
• INDEX - Used to create and retrieve data from the database very quickly

ORDER BY

• ASC/DESC

IN

• The IN operator allows you to specify multiple values in a WHERE clause.
• The IN operator is a shorthand for multiple OR conditions.

BETWEEN

• The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.
• The BETWEEN operator is inclusive: begin and end values are included.

MIN/MAX

• The MIN() function returns the smallest value of the selected column.
• The MAX() function returns the largest value of the selected column.

UNION

• Each SELECT statement within UNION must have the same number of columns
• The columns must also have similar data types
• The columns in each SELECT statement must also be in the same order
• Syntax:
  • Only distinct: SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
  • Allow duplicates: SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;

JOIN

• A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
• Types of JOIN:
  • (INNER) JOIN: Returns records that have matching values in both tables
  • LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table
  • RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table
  • FULL (OUTER) JOIN: Return all records when there is a match in either left or right table
  
  

GROUP BY

• The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

LIMIT and OFFSET
SELECT column FROM table LIMIT 18 OFFSET 8;
Start by reading the query from offset. First you offset by 8, which means you skip the first 8 results of the query. Then you limit by 18. Which means you consider records 9, 10, 11, 12, 13, 14, 15, 16....24, 25, 26 which are a total of 18 records.

Using limit:
• one parameter: number of rows to be returned from the beginning of the result set.
  • SELECT * FROM Data LIMIT 3;: retrieve the first three rows
• two parameter: the first is the offset and the second denotes the count.
  • SELECT * FROM Data LIMIT 1, 2;: retrieve the rows 2-3(inclusive)

IFNULL

• IFNULL(expression, alt_value);
• If expression is NOT NULL, the IFNULL() function returns expression, otherwise, return alt_value.

Basic Operations
Insert:
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
Update:
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
Delete:
DELETE FROM table_name WHERE condition;
Alter:
ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE Customers DROP COLUMN Email; ALTER TABLE table_name MODIFY COLUMN column_name datatype;
NULL value
A field with a NULL value is a field with no value.
How to test if a data is NULL?: use IS NULL

LIKE operator

• %: The percent sign represents zero, one, or multiple characters
• _: The underscore represents a single character

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

Aliases
Alias Column Syntax
SELECT column_name AS alias_name FROM table_name;
Alias Table Syntax
SELECT column_name(s) FROM table_name AS alias_name;

SELECT...INTO

• copies data from one table into a new table.
• Syntax:
  SELECT column1, column2, column3, ...
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

INSERT INTO...SELECT...

• copies data from one table and inserts it into another(existing) table.
• Syntax:
  INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

Stored Procedure

• A stored procedure is a segment of declarative SQL statements stored inside the database catalog.
• A stored procedure can be invoked by triggers
• Syntax:
  DELIMITER //
CREATE PROCEDURE GetAllProducts()
BEGIN
SELECT * FROM products;
END //
DELIMITER ;
  • DELIMITER //: changes the standard delimiter which is semicolon( ; ) to another to pass the stored procedure to the server as a whole.
  • CREATE PROCEDURE: create a new stored procedure.
  • BEGIN...END: body of the stored procedure
• Calling stored procedure: CALL stored_procedure_name();
• Variables:
  • Syntax:
    DECLARE variable_name datatype(size) DEFAULT default_value;
  • DECLARE: specify variable name after DECLARE
  • DEFAULT: assign the variable a default value
  • Example: DECLARE total_sale INT DEFAULT 0;
  • Assigning:
    DECLARE total_count INT DEFAULT 0;
SET total_count = 10;
• Parameters
  • Modes:
    • IN
      • default mode
      • the calling program has to pass an argument to the stored procedure
      • the value of an IN parameter is protected. It means that even the value of the IN parameter is changed inside the stored procedure, its original value is retained after the stored procedure ends.
    • OUT
      • can be changed inside the stored procedure and its new value is passed back to the calling program
    • INOUT
      • combination of IN and OUT parameters
  • Syntax:
    CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255))
CALL GetOfficeByCountry('USA');

Trigger

• Create trigger:
  • 
    CREATE TRIGGER trigger_name trigger_time trigger_event
ON table_name
FOR EACH ROW
BEGIN
...
END;

Query Questions

• Copy a table with/without index
  • With index:
    CREATE TABLE newtable LIKE oldtable;
INSERT newtable SELECT * FROM oldtable;
  • Without index, just structure and data:
    CREATE TABLE tbl_new AS SELECT * FROM tbl_old;