Database — Structured Query Language (SQL) (Part 8)

Leverage The Power of The Structured Query Language (SQL).

Omar Elgabry

Published in

OmarElgabry's Blog

20 min readSep 15, 2016

Wish you already came along the last part Database — Normalization (Part 7)

The Structured Query Language (SQL) — pronounced S-Q-L — is used for creating, querying, updating and manipulating data in relational databases.

There’s a notable difference between how SQL is implemented in each database management system. It’s important to keep that in mind while following along over here.

After learning standard SQL, you’ll be able to use any of them, but you still need to learn the specific implementation of SQL for the database management system you are using.

SQL Syntax

A SQL statement begins with a keyword, like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE and ends with a semicolon “;”.

SELECT column1, column2, ..., columnN FROM table_name;

Case Sensitivity

SQL is not case sensitive, but, most developers write the SQL keywords all uppercase, because it makes these statements easier to read.

White Space

The line breaks also don’t matter. SQL is not sensitive to white space. You can split a SQL statement into multiple lines for readability.

Semicolon

Officially, a complete SQL statement should be ended with a semicolon, though, many DBMS just don’t care.

Comments

You can write comments in two ways; either using the c-style comment, or using two hyphen characters (with no space between them) followed by a space and the comment text.

/* 
  this is a
  multi-line comment
*/
SELECT * FROM Employee; -- this is a single line comment

Operators

An operator is a reserved word used to perform an operation like combining between expressions, comparisons, or arithmetic operations.

Arithmetic: +, -, *, /, and %.
Comparison: =, !=, >, <, ≥, ≤, <>, …etc.
Logical: AND, OR, NOT, …etc.
and many others.

The SQL language can be divided into data definition language (DDL), data manipulation language (DML), and data control language. We’ll get into everyone of them. So, let’s get started.

Data Manipulation Language (DML)

The data manipulation language is used to create, read, update, and delete data from a table. They are grouped together under the acronym CRUD.

The DML operations are the majority of SQL that you’ll write. So, it’s worthwhile to take a deeper look at them.

SELECT

The SELECT statement is used to retrieve data. The results may come from one or more table, or even expressions.

SELECT ‘Hello World’;
SELECT 1 + 2;

Now, let’s select some rows from a table in the database.

The format is to specify the columns you want separated by commas, and then the FROM clause followed by the table name.

SELECT firstName, lastName FROM Employee;

The columns are displayed in the ordered we specified in the SELECT statement.

In order to return all the columns in a table, you can use the asterisk.

SELECT * FROM Employee;

WHERE Clause

You can get data that only meet a specific condition, instead of getting the entire table. The WHERE clause is used to filter the result data.

For example, we can select only employees who have salary > 10,000.

SELECT * FROM Employee WHERE salary > 10000;

Or, those who have last name equals to ‘Fuller’.

SELECT * FROM Employee WHERE lastName = 'Fuller';

In SQL, a string must be in a single quotes, not the double quotes.

You can combine conditions by using the logical operators AND & OR.

SELECT * FROM Employee 
WHERE salary > 10000 AND lastName = 'Fuller';

Rows are returned only if the boolean expression in the WHERE clause returned TRUE for each row.

Comparing NULL

If some employees’ last name are given NULL instead of real values, those employees won’t be included in the result unless you use IS (vice-versa is IS NOT) operator in the WHERE clause to compare to NULL (missing data).

SELECT * FROM Employee 
WHERE lastName = 'Fuller' OR lastName IS NULL;

We can’t use the equal sign with NULL when comparing column’s value. Instead, use the operators; IS NULL, and, IS NOT NULL. Keep in mind that NULL is not a value, it’s a state of no value, not zero, not empty string.

Aliases

SQL aliases are used to give a temporary name to a table, or a column in your result set using the AS operator.

SELECT lastName AS lname FROM Employee;   -- for column
SELECT * FROM Employee AS emp;            -- for table

The AS operator can be useful when:

There are more than one table involved in a query.
Functions are used in the query.
Column names are big or not very readable.
Two or more columns are combined together.

LIMIT & OFFSET Clauses

We can limit the number of rows in the result by using the SELECT TOP clause. But, not all of the database systems support this clause. For example, MySQL equivalent is called LIMIT clause.

SELECT * FROM Employee LIMIT 5;

MySQL also provides a way to specify an offset; the number of rows to skip, by using an OFFSET clause (after the LIMIT clause).

SELECT * FROM Employee LIMIT 5 OFFSET 4;

ORDER BY Clause

SQL does not guarantee that data is returned in any particular order unless you specify an order. This can be done using ORDER BY clause to return the data ordered by one or more columns.

SELECT * FROM Employee ORDER BY salary;

The default order is in ascending order. If you want them in descending, we can just put DESC (or ASC for ascending — default) after ORDER BY clause.

SELECT * FROM Employee ORDER BY salary DESC;

We can also order by more than one column. It will sort them by the first column, department in this case, and then for each department, it will sort them by last name.

SELECT * FROM Employee ORDER BY deptName, lastName;

Sure, you can use the descending order for department instead of the default.

SELECT * FROM Employee ORDER BY deptName DESC, lastName;

DISTINCT

We can select only distinct values of a column using SELECT DISTINCT statement. The following query will return all unique values for last name in the employee table.

SELECT DISTINCT lastName FROM Employee;

The DBMS will actually sort the data, and move out the duplicates.

Using more than one column will result in having duplicates, but, the whole row of columns’ values will be distinct.

SELECT DISTINCT lastName, firstName FROM Employee;

LIKE Operator

You can filter the columns based on a specified text. For example, we can select the employees who have “California” some where in the address.

The LIKE operator is used in the WHERE clause to match a specific pattern in a column value.

SELECT * FROM Employee WHERE address LIKE '%California%';

The percent sign “%” is a wildcard that means zero or more characters. A wildcard character can be used to substitute for other character(s) in a string. All wildcards can be found here.

So, we can also match only those employees where their address starts with ‘California’ by removing the percent sign at the beginning of the string.

SELECT * FROM Employee WHERE address LIKE 'California%';

Other wildcards like the underscore “_” can substitute a single character.

The next query will return all the employees who have the following format in their address: Any letter in the first position, letter ‘a’ in the second position and anything after it.

SELECT * FROM Employee WHERE address LIKE '_a%';

Regular expressions are also used to match a particular pattern. They are much more powerful and complex than LIKE patterns. You need to look closely on how your database system implements regular expressions.

IN Operator

The IN operator used to match a set of possible values in the WHERE clause.

SELECT * FROM Employee 
WHERE lastName IN ('Maria', 'Thomas', 'Fuller');

Likewise, use NOT IN to select the values that don’t match a set of values.

SELECT * FROM Employee 
WHERE lastName NOT IN ('Maria', 'Thomas', 'Fuller');

BETWEEN Operator

The BETWEEN operator is used to select values within a range.

SELECT * FROM Employee
WHERE salary BETWEEN 2000 AND 3000;

Likewise, NOT BETWEEN selects the values that are outside the range.

SELECT * FROM Employee
WHERE salary NOT BETWEEN 2000 AND 3000;

The behavior of BETWEEN operator is different from one database management system to another. So, check how it’s being treated by your database system.

Functions

SQL built-in functions are used to perform specific operations on data. A list of all the functions can be found here.

— Aggregate

Those functions aggregates the rows, resulting in one value (unless we use GROUP BY clause which we will cover here).

-- COUNT
-- Counts the number of rows.
SELECT COUNT(*) FROM Employee;-- SUM
-- The sum of the salary of all the employee.
SELECT SUM(salary) FROM Employee;-- MIN & MAX
-- Selects the min or max salary.
SELECT MAX(salary) FROM Employee;

— Numeric

SQL numeric functions are used for numeric manipulation and mathematical calculations.

-- ABS
-- Returns the absolute value of a number.
SELECT ABS(salary) FROM Employee;-- POWER
-- Finds the value of 1st arg raised to the power of 2nd arg.
SELECT POWER(salary, 2) FROM Employee;-- ROUND
-- Rounds a number to the nearest integer.
-- If 2nd arg is passed, then round to 2nd arg decimal places.
SELECT ROUND(salary, 2) FROM Employee;

— String

SQL string functions are used to process and manipulate a string.

-- LENGTH
-- The length of a column of a string value.
SELECT LENGTH(address) FROM Employee;-- SUBSTR
-- Selects a part of the string (1-indexed)
SELECT SUBSTR(address, start, len) FROM Employee;-- TRIM, LTRIM, RTRIM
-- Removes the leading spaces either from both sides or one side.
SELECT TRIM(address) FROM Employee;-- UPPER & LOWER
-- Converts characters to upper or lower case.
SELECT UPPER(address) FROM Employee;

The standard SQL doesn’t include LENGTH or SUBSTR, but, most DBMS do. The names might be different from one database system to another, but, this is generally how they work.

— Stored Functions

A stored function (also called a user-defined function) is a function stored in the database, and can be called same as any built-in functions.

The implementation can vary from one database system to another, but, to get a quick idea on they’re implemented, here is an example in MySQL.

-- Create a function
CREATE FUNCTION sayHello (name CHAR(30))
RETURNS CHAR(50) DETERMINISTIC
  RETURN CONCAT('Hello, ', name, '!')
;-- Use the function
SELECT sayHello(lastName) FORM Employee;

A function is considered “deterministic” if it always produces the same result for the same input parameters, and “not deterministic” otherwise.

GROUP BY Clause

This clause is used (usually in conjunction with the aggregate functions) to group the resulting rows by one or more columns.

We can get the number of employees grouped by their last name.

SELECT lastName, COUNT(*) 
FROM Employee 
GROUP BY lastName;

HAVING Clause

The HAVING clause was added to SQL because the WHERE clause couldn’t be used with aggregate functions.

The following query will find if there are more than 30 employee with the last name ‘Fuller’ or ‘Walter’.

SELECT lastName, COUNT(*) 
FROM Employee
WHERE lastName = 'Fuller' OR lastName = 'Walter'
GROUP BY lastName
HAVING COUNT(*) > 30;

JOIN

We have been selecting on a specific tables, but, what if we have tables that have some kind of relationship, whether 1-M, or M-M, and we want to connected those tables, and retrieve matching values.

We don’t want to be limited to selecting from one table, but, to be able to select from two different tables or even three or more and the query we’re going to use is to JOIN our tables together.

— INNER JOIN

For example, in 1-M relationship, let’s say we have a customer table, that can place one or many or no orders, where every order is placed by one customer. We can get the information of the customers and the orders placed by each customer.

We join the two tables by matching the foreign key in the orders table, and the primary key of the customers.

SELECT Customer.ID, Customer.name, Order.number
FROM Customer INNER JOIN Order
ON Customer.ID = Order.CustomerID

You need to use the dot ‘.’ sign to refer to a specific table as we did here. This is useful to avoid any conflict.
Even thought the default kind of JOIN is an INNER JOIN (you can use the word JOIN instead of INNER JOIN), but, you should use INNER JOIN instead.

— LEFT & RIGHT OUTER JOIN

What we’ve done is called an INNER JOIN, meaning, only bring back the rows where there is a match in both tables.

Sometimes you might want to start involving these other rows that don’t exactly match. We are interested in where they match but we still want to get the results where they don’t.

So, we would use the word LEFT OUTER JOIN or RIGHT OUTER JOIN. You are typically saying one of these tables takes precedence over the other; Using the LEFT will also show customers that didn’t have any orders, while using RIGHT will also show all the orders that didn’t placed by any customer.

SELECT Customer.ID, Customer.name, Order.number
FROM Customer LEFT OUTER JOIN Order
ON Customer.ID = Order.CustomerID

We will get NULL for the values that doesn’t exist, as a result of retrieve rows that doesn’t match.

— FULL OUTER JOIN

There is also a FULL OUTER JOIN which does both. It says to include all rows from both tables in the result, matching them whenever possible, but, returning rows with NULL when there’s no match between them.

UNION Operator

The UNION operator combines the result of two or more SELECT statements. There are some considerations to keep in mind when using UNION:

Each SELECT statement in UNION must have same number of columns.
The columns must also have similar data types.
The columns in each SELECT statement must be in the same order.

The following SQL statement selects all the different birth dates from the employee and depdendet tables.

SELECT bdate As 'birthDate' FROM Employee
UNION
SELECT birth_date As 'birthDate' FROM Dependent;

The column names in the result are usually equal to the column names in the first SELECT statement unless you give each similar two columns a specific alias using the AS operator.

The UNION operator selects only distinct values by default. To allow duplicate values, use the ALL keyword with UNION.

SELECT bdate As 'birthDate' FROM Employee
UNION ALL
SELECT birth_date As 'birthDate' FROM Dependent;

Subselects

A subselect is a nested SELECT statement. And, since the result of SELECT statement is actually a table, so, it can be used as a data source for another SELECT statement. Here are some examples on using subselect.

-- Inside WHERE ... IN
SELECT * FROM album 
WHERE album.id IN (
  SELECT DISTINCT album_id FROM track WHERE duration <= 90
);-- Inside WHERE ... IN & Using JOIN
SELECT album.title, album.artist, t.title, t.number, t.duration
FROM album 
INNER JOIN track AS t
ON t.album_id = album.id
WHERE album.id IN (
  SELECT DISTINCT album_id FROM track WHERE duration <= 90
);-- Inside JOIN
SELECT album.title, album.artist, t.title, t.number, t.duration
FROM album
INNER JOIN ( 
  SELECT album_id, title, track_number As number, duration
  FROM track 
  WHERE duration <= 90
) AS t
ON t.album_id = album.id
ORDER BY album.title, t.number;

Every subselect (nested SELECT statement) must have an alias if you are going to refer to it, or select column(s) of the resulting table from the subselect.

If you’re going to use the result of a SELECT statement more than one time, you probably want to create a view. A view is a temporary table, stored in the database and it’s created as the following.

-- CREATE
CREATE VIEW trackView AS
  SELECT id, album_id, title, track_number, duration FROM track;-- SELECT
-- We can use SELECT statement on a view same way on a normal table.
SELECT * FROM trackView;-- DROP
-- It doesn't affect the underlying table.
DROP VIEW trackView;

INSERT

The INSERT INTO statement is used to add one or more row to a table.

INSERT INTO Employee (lastName, firstName) 
VALUES ('Jason', 'Albert'), ('Walter', 'Savitch'), ('Christopher', 'John');

If a column is not assigned values, the default value for the column is used.

We can also insert all column values at once.

INSERT INTO Employee VALUES ('Jason', 'Albert', 14054, '123 Statham Street, California');

The number of columns and values must be the same, and the inserted values must match the columns’ order and type.

You can use the results of a SELECT statement in place of the VALUES clause. The SELECT statement will execute the query and return the result.

INSERT INTO backup (lastName, firstName)       -- specific columns
SELECT lastName, firstName FROM Employee;INSERT INTO backup SELECT * FROM Employee;     -- all columns

Similarly, you can use the SELECT INTO statement to achieve the same task.

SELECT * INTO newtable [IN externaldb] FROM table;

UPDATE

The UPDATE statement is used to change data in existing rows in a table. The new values are assigned using the SET keyword.

UPDATE Employee 
SET salary = 5000, address = '77 Winchester Lane, California'
WHERE SSN = 5123;

Usually, you’ll use the WHERE clause to update(or delete) specific row(s). A good practice is to select first the rows you want to modify, make sure they are the expected rows and then perform update(or delete).

We can also assign a column to NULL instead of a real value.

UPDATE Employee SET address = NULL WHERE SSN = 5123;

If you want to assign a column value to NULL, use the equal sign.

DELETE

The DELETE statement is used to delete one or more rows from a table. The format is DELETE FROM, then a table name, and usually a condition.

DELETE FROM Employee WHERE firstName = 'Henry';

To delete all the rows from your table, you don’t need to use WHERE clause.

DELETE FROM Employee;

You need to take care with your delete statements. DBMS won’t confirm back if you really want to delete those rows or not!. So, make sure you’re selecting the rows you expect to be deleted (or updated).

Data Definition Language (DDL)

The data definition language is used to create and alter the structure of the database itself, like creating a table, it’s columns, define and modify the primary and foreign keys, define indexes, stored procedures, delete columns, tables, and even the entire database.

The main operations of DDL are CREATE, ALTER, DROP. So, let’s take look.

CREATE

Database

The CREATE DATABASE statement is used to create a database.

CREATE DATABASE db_name;

Trying to create a database (or a table) that’s already exist will result in an error unless you use IF NOT EXISTS (vice-versa is IF EXIST).

CREATE DATABASE IF NOT EXISTS db_name;

Now, In order to use a specific database, we have to type the USE keyword followed by the database name. Now, anything we write after, will be relative to that database.

USE db_name;
SELECT * FROM table_name;

Table

The CREATE TABLE statement is used to create a table in a database.

Now, If you want to create a new table, you then specify the name of the columns, their datatype, and any other constrains inside the parenthesis.

As an example, we’ll created an employee table as the following:

A SSN and salary are integer values.
The first name, last name, and address are VARCHAR, which means they are character strings, with variable length of characters.
The birth date is of type DATE, which means it stores year, month, and day.

CREATE TABLE Employee (
  SSN INTEGER,
  firstName VARCHAR(45),
  lastName VARCHAR(45),
  salary INTEGER,
  address VARCHAR(255),
  bdate DATE
);

The definition for each column is separated by a comma, except the last one.

— Data Types

A data type defines a set of possible values a column can contain.

The data types that are available in one database management system are much different than the data types available on another database system.

MySQL for example, supports three fundamental types of data:

Numeric types are used to represent numerical values.

Integer like INTEGER.
Fixed Point like DECIMAL and NUMERIC.
Floating Point like FLOAT and DOUBLE.

2. String types are used for representing both text and non-text strings.

Fixed Length like CHAR.
Variable Length like VARCHAR.
Binary Strings like BINARY and VARBINARY.
Large Object Storage (BLOB and TEXT) like BLOB and TEXT.

3. The date and time types are used to represent temporal values.

MySQL has five date and time types; DATE, TIME, DATETIME, TIMESTAMP, and YEAR.

A complete reference of all the data types available in MS Access, MySQL, and SQL Server, can be found here.

— Auto Increment

The auto-increment feature allows a sequence of unique numbers to be generated for a column’s value.

The implementation varies across different database systems. In MySQL, It uses AUTO_INCREMENT keyword on an integer or floating-point column.

CREATE TABLE Employee (
  SSN INTEGER AUTO_INCREMENT
);

So, when inserting, no need to insert the auto-increment column values.

— Table Constrains

As you’re defining your table you may want to define specific rules and behaviors for some of your columns. This is done by using constraints. These constrains can’t be violated, and if you tried, your DBMS will complain.

The constraints can be specified when creating or updating a table. In SQL, we have the following 6 constraints:

— — 1. NOT NULL

Indicates that a column can’t have NULL. It can’t be empty. It must have a value.

— — 2. UNIQUE

Ensures the values of a column (or combination of columns) are unique.

A column with more than NULL doesn’t violates the UNIQUE constrain, because NULL is not a value.

— — 3. PRIMARY KEY

A combination of a NOT NULL and UNIQUE; It ensures that a column (or combination of columns) have a unique values, and aren’t NULL.

You can have many unique columns per table, but only one primary key is allowed.

— — 4. FOREIGN KEY

Ensures a foreign key can’t have a value that’s not in the primary key it points to, but, it can have NULL (if allowed) just fine.

What if we tried to insert a value in the foreign key that’s not in the primary key?
— It will be refused, and It’s not allowed, and you’ll get a constrain violation.

What if we tried to delete or update a primary key that’s being referenced by a foreign key?
— It won’t be allowed by default, and you’ll get a constrain violation.

But, there are some other options that might be available by your database system like:

Cascade: Cascade down and delete or update all referencing rows.
Set NULL: Set the foreign key column to NULL.
No Action: Refuse the delete or update operation (the default in most database management systems).

— — 5. CHECK

Ensures that the value in a column meets a specific condition.

— — 6. DEFAULT

Specifies a default value for a column.

What if we when we insert a row, we didn’t enter the value of a column?
— It will add the default value instead.

What if when we insert a row, we didn’t insert a value to a NOT NULL column, given it’s default value is NULL?
— Probably, your database system will add the closet value to NULL, for example, zero.

The implementation of these constrains can vary from one database system to another. As an example, here’s the syntax in MySQL.

CREATE TABLE Departement (
    number INTEGER NOT NULL AUTO_INCREMENT,   
    name VARCHAR(45) NOT NULL,
    UNIQUE KEY (name),
    PRIMARY KEY (number)
);CREATE TABLE Employee (
  SSN INTEGER NOT NULL,
  firstName VARCHAR(45) NOT NULL,
  lastName VARCHAR(45) NOT NULL,
  salary INTEGER DEFAULT 0,
  address VARCHAR(255),
  bdate DATE NOT NULL,
  deptNumber INTEGER DEFAULT NULL,
  CHECK (SSN > 0),
  PRIMARY KEY (SSN),
  FOREIGN KEY (deptNumber) REFERENCES Departement (number)
);

In most DBMS, if a column is defined as a primary key, no need to add the NOT NULL constrain, since PRIMARY KEY constrain enforces NOT NULL constrain (in addition to the UNIQUE constrain).
MySQL ignores the CHECK constraint. An alternative solution is to use a trigger.

The UNIQUE and PRIMARY KEY constrains can be defined on a combination of columns instead of one column.

CREATE TABLE Employee (
  SSN INTEGER NOT NULL,
  lastName VARCHAR(45) NOT NULL,
  PRIMARY KEY (SSN, lastName)
);

In the example above, there is only one primary key made up of two columns; meaning the value of SSN and last name together represent the primary key.

— Options

There are some options that might be available by your database system when creating a table (and also for database). For example, If possible, you can specify the database engine, character set and collation.

Charset is a set of symbols and their encoding, while collation is a set of rules for comparing the characters in a character set.

In MySQL, you can specify the database character set and collation.

CREATE DATABASE db_name CHARACTER SET utf8 COLLATE utf8_general_ci;

And for a table, you can specify the engine after the parenthesis, along with table character set and collation.

CREATE TABLE Employee (
    /* columns definition goes here */
) ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_general_ci;

The database character set and collation are used as default values for table definitions if the table character set and collation are not specified.

ALTER

Database

The ALTER DATABASE statement is used to modify an existing database. Here’s an example in MySQL to update the character set and collation.

ALTER DATABASE Employee CHARACTER SET utf8 COLLATE utf8_general_ci;

Table

Sometimes, you may need to change the definition of a table after it’s already been defined, and even filled up with data, like add, modify, or delete existing columns. You can do this with the ALTER TABLE statement.

-- To add a new column.
ALTER TABLE Employee ADD email VARCHAR(255) ;-- To add a new column after a specific existing column.
ALTER TABLE Employee ADD email VARCHAR(255) AFTER SSN;-- To add a new column at the beginning.
ALTER TABLE Employee ADD email VARCHAR(255) FIRST;/* New columns are filled with NULL by default. 
 You can specify a different default value. */
ALTER TABLE Employee ADD email VARCHAR(255) DEFAULT '';/* To modify an existing column 
 Database systems vary in the implementations of modifying a column in a table. Here is the implementation of MySQL. */
ALTER TABLE Employee MODIFY COLUMN email VARCHAR(45) NOT NULL;-- To remove a column.
ALTER TABLE Employee DROP email;

The ALTER TABLE statement should be used with care and caution. You need to be careful to track and update any code that may depend on the existing table structure before you change it.

— Table Constrains

To add, or delete table constrains (as part of the table definition), the syntax is slightly different between different constrains, and, it varies across different database management systems. Here’s the implementation in MySQL.

-- UNIQUE
ALTER TABLE tbl_name 
  ADD CONSTRAINT constrain_name UNIQUE (col_name);ALTER TABLE tbl_name DROP INDEX constrain_name;-- PRIMARY KEY
ALTER TABLE tbl_name 
  ADD CONSTRAINT constrain_name PRIMARY KEY (col_name);ALTER TABLE tbl_name DROP PRIMARY KEY;-- FOREIGN KEY
ALTER TABLE tbl_name 
   ADD CONSTRAINT constrain_name FOREIGN KEY (col_name) 
   REFERENCES parent_tbl_name (parent_prim_key);ALTER TABLE tbl_name DROP FOREIGN KEY constrain_name;-- CHECK
-- Remember? CHECK constrain is already ignored by MySQL.-- DEFAULT
ALTER TABLE tbl_name ALTER col_name SET DEFAULT 'default_value';
ALTER TABLE tbl_name MODIFY col_name DEFAULT 'default_value';ALTER TABLE tbl_name ALTER col_name DROP DEFAULT;

DROP

Database

The DROP DATABASE statement is used to delete a database.

DROP DATABASE Employee;

Likewise in creating a database, trying to delete a database (or a table) that doesn’t exit, will result in an error unless you use IF EXISTS.

DROP DATABASE IF EXISTS Employee;

Table

The DROP TABLE statement is used to delete a table.

DROP TABLE Employee;

Triggers

An operation that’s automatically performed when a specific event occurs.

The trigger is mostly used for maintaining the integrity of the information on the database. For example, when a new row is inserted to the employees table, new records should also be created in the tables of the taxes, vacations and salaries. Triggers maybe also used to prevent any change on rows.

Triggers can fall under DML & DDL. A DDL trigger executes in response to a change to the structure of a database (CREATE, ALTER, DROP). A DML trigger executes in response to a change in data (INSERT, UPDATE, DELETE).

How triggers are implemented and used vary from one database system to another. But, To give a general idea on how they work, we’ll use MySQL.

The following trigger updates the last order column of a customer in customer table whenever a new order is made by that customer.

CREATE TRIGGER newOrder AFTER INSERT ON Order
  FOR EACH ROW
  UPDATE Customer SET lastOrder = NEW.id 
  WHERE  Customer.id = NEW.CustomerID
;

The column values of the inserted row can be accessed through the NEW keyword.

You can also add multiple statements inside a trigger. For example, you can as well record the actions (placing an order) made by a customer in the log table.

DELIMITER //
CREATE TRIGGER newOrder AFTER INSERT ON Order
  FOR EACH ROW
  BEGIN
    UPDATE Customer SET lastOrder = NEW.id 
      WHERE  Customer.id = NEW.CustomerID;
    INSERT INTO Log (event, tableName, tableID, doneBy) 
      VALUES ('New Order', 'Order', NEW.id, Customer.id);
  END
//
DELIMITER;

To add multiple statements, you need to enclose them with BEGIN … END block, and change the statement delimiter (terminator) from “;” to “//”.

Finally, to delete a trigger, use the DROP TRIGGER statement.

DROP TRIGGER IF EXISTS newOrder;

Data Control Language (DCL)

It’s worth mentioning that there are other operations in SQL that are typically lumped under the idea of controlling the access to data stored in a database.

This is where you can either “grant” or “revoke” permissions for people in the database. It’s two main statements are GRANT and REVOKE.

Most of your time will be spent on manipulating data, some of the time defining the actual database itself, and a little bit of the time controlling who has access to different parts of it.

Wrapping Up

We’ve showed some of the basic SQL syntax, and how can you use it to create, read, update, and delete data, and also modify the table structure.

You can take this further and start search for extra information if you need to. Along the road, you should look at your database system documentation.

There are two more topics we need to cover before ending this series of tutorials. Next is “Indexing, Transactions & Stored Procedures”.

Database — Structured Query Language (SQL) (Part 8)

Leverage The Power of The Structured Query Language (SQL).

SQL Syntax

Case Sensitivity

White Space

Semicolon

Comments

Operators

Data Manipulation Language (DML)

SELECT

WHERE Clause

Comparing NULL

Aliases

LIMIT & OFFSET Clauses

ORDER BY Clause

DISTINCT

LIKE Operator

IN Operator

BETWEEN Operator

Functions

— Aggregate

— Numeric

— String

— Stored Functions

GROUP BY Clause

HAVING Clause

JOIN

— INNER JOIN

— LEFT & RIGHT OUTER JOIN

— FULL OUTER JOIN

UNION Operator

Subselects

INSERT

UPDATE

DELETE

Data Definition Language (DDL)

CREATE

Database

Table

— Data Types

— Auto Increment

— Table Constrains

— — 1. NOT NULL

— — 2. UNIQUE

— — 3. PRIMARY KEY

— — 4. FOREIGN KEY

— — 5. CHECK

— — 6. DEFAULT

— Options

ALTER

Database

Table

— Table Constrains

DROP

Database

Table

Triggers

Data Control Language (DCL)

Wrapping Up

Written by Omar Elgabry