SQL

SQL – Overview

Contents

Introduction  1

SQL as a Declarative Language  1

SQL as a Definition Language  2

CREATE  2

ALTER  3

TRUNCATE  3

DROP  4

SQL as a Query Language  5

SELECT  5

SQL as a Manipulation Language  6

INSERT  6

UPDATE  6

DELETE  7

Foreign Key  7

Joins  8

Inner Join  9

Outer Join  9

Cross Join  10

Self Join  11

.

.

.

Introduction

.

We will look at the most important SQL concepts and statements with examples performed on a MySQL database

SQL as a Declarative Language

.

SQL as a Definition Language

.

The Data Definition Language (DDL) refers to SQL statements that are used to define and modify the database schema.

CREATE

The CREATE command is used to create the database schema and its objects like index, views, stored procedures, functions and triggers.

1. Create Database

The following script creates a new database ‘EMPLOYEES_DB’:

Syntax: CREATE DATABASE <database-name> . CREATE DATABASE EMPLOYEES_DB; .

.

2. Create Table

The following script creates a new table ‘EMPLOYEES’ with given column specifications in ‘EMPLOYEES_DB’ database:

Syntax: CREATE TABLE <database-name>.<table-name> ( column-name datatype ); . CREATE TABLE EMPLOYEES_DB.EMPLOYEES( ID INT PRIMARY KEY, FIRST_NAME VARCHAR(50), LAST_NAME VARCHAR(50), DEPARTMENT VARCHAR(100) ); .

.

ALTER

The ALTER command is used to modify an existing table.

1. Add a new Column

The following script adds a new column ‘PHONE’ in the existing ‘EMPLOYEES’ table:

Syntax: ALTER TABLE <table-name> ADD <column-name> <datatype>; . ALTER TABLE employees ADD PHONE BIGINT; .

.

2. Modify a Column

The following script modifies the size of an existing column ‘DEPARTMENT’ in ‘EMPLOYEES’ table from 100 to 50:

Syntax: ALTER TABLE <table-name> MODIFY COLUMN <column-name> <datatype>; . ALTER TABLE employees MODIFY COLUMN DEPARTMENT VARCHAR(50); .

.

TRUNCATE

The TRUNCATE command is used to delete all the records from a table.

Suppose we already have some records in the EMPLOYEES table.

The following script deletes all the records from the table:

Syntax: TRUNCATE <table-name>; . TRUNCATE TABLE employees; .

.

DROP

The DROP command is used to delete the database or its objects.

1. Drop a Column

The following script deletes the column PHONE from the EMPLOYEES table:

Syntax: ALTER TABLE <table-name> DROP COLUMN <column-name>; . ALTER TABLE employees DROP COLUMN PHONE; .

.

2. Drop a Table

The following script deletes the EMPLOYEES table:

Syntax: DROP TABLE <table-name>; . DROP TABLE employees; .

.

3. Drop a Database

The following script deletes the EMPLOYEES_DB database:

Syntax: DROP DATABASE <database-name>; . DROP DATABASE employees_db; .

.

.

SQL as a Query Language

.

The Data Query Language (DQL) refers to SQL statements that are used to fetch data from the database schema based on queries.

SELECT

The SELECT statement is used to fetch data from the tables.

Suppose we already have some records in the EMPLOYEES table.

.

1. Fetch all the data

The following script fetches all records and fields from the EMPLOYEES table:

Syntax: SELECT * FROM <database-name>.<table-name>; . SELECT * FROM employees_db.employees; .

.

2. Fetch selected columns

The following fetches only the FIRST_NAME and DEPARTMENT for all the records in the EMPLOYEES table:

Syntax: SELECT <column-1>,<column-2> FROM <database-name>.<table-name>; . SELECT FIRST_NAME, DEPARTMENT FROM employees_db.employees; .

.

3. Fetch data based on condition

The following script fetches the FIRST_NAME and LAST_NAME of the employees whose DEPARTMENT is ‘Admin’:

Syntax: SELECT <column-1>,<column-2> FROM <database-name>.<table-name> WHERE <condition>; . SELECT FIRST_NAME, LAST_NAME FROM employees_db.employees WHERE DEPARTMENT = “Admin”; .

.

SQL as a Manipulation Language

.

The Data Manipulation Language (DML) refers to SQL statements that are used to manipulate data in the database.

INSERT

The INSERT statement is used to insert new records into a table.

Suppose we already have two records in the table. The following script inserts records of two new employee records into the EMPLOYEES table:

Syntax: INSERT INTO <database-name>.<table-name>(<column-1>,<column-2>..) VALUES(<column-1-value>,<column-2-value>); . INSERT INTO `employees_db`.`employees` (`ID`, `FIRST_NAME`, `LAST_NAME`, `DEPARTMENT`, `PHONE`) VALUES (‘3‘, ‘Amit‘, ‘S’, ‘Finance’, ‘9999‘); . INSERT INTO `employees_db`.`employees` (`ID`, `FIRST_NAME`, `LAST_NAME`, `DEPARTMENT`, `PHONE`) VALUES (‘4‘, ‘Jack‘, ‘D‘, ‘Admin’, ‘7777‘); .

.

UPDATE

The UPDATE statement is used to update an existing record in a table.

The following script updates the PHONE of an employee in the EMPLOYEES table:

Syntax: UPDATE <database-name.<table-name> SET <column-1> = <new-value1>,<column-2>=<new-value2> WHERE <condition> . UPDATE `employees_db`.`employees` SET PHONE = 3333 WHERE FIRST_NAME = “Ankit“; .

.

DELETE

The DELETE statement is used to delete records from a table.

The following script deletes a record from the EMPLOYEES table:

Syntax: DELETE FROM <database-name.<table-name> WHERE <condition> . DELETE FROM `employees_db`.`employees` WHERE FIRST_NAME = “Ankit“; .

.

Foreign Key

.

Suppose we already have an EMPLOYEES table created in the previous section. Now we create a new table EMPLOYEE_ASSETS that contains a foreign key reference to EMPLOYEES table:

CREATE TABLE EMPLOYEES_DB.EMPLOYEE_ASSETS( ID INT PRIMARY KEY, LAPTOP_ID VARCHAR(50), LAPTOP_DESCRIPTION VARCHAR(50), EMPLOYEE_ID INT, CONSTRAINT fk_employee FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEES(ID) );

Suppose the EMPLOYEES table contains following records:

Now EMPLOYEE_ID column of the EMPLOYEE_ASSETS table must contain a value from the ID column of the EMPLOYEES table. Otherwise the record will not be inserted:

. Executing: INSERT INTO `employees_db`.`employee_assets` (`ID`, `LAPTOP_ID`, `LAPTOP_DESCRIPTION`, `EMPLOYEE_ID`) VALUES (‘1’, ‘1234’, ‘dell 111′, ’12’); . Operation failed: There was an error while applying the SQL script to the database. ERROR 1452: 1452: Cannot add or update a child row: a foreign key constraint fails (`employees_db`.`employee_assets`, CONSTRAINT `fk_employee` FOREIGN KEY (`EMPLOYEE_ID`) REFERENCES `employees` (`ID`)) SQL Statement: INSERT INTO `employees_db`.`employee_assets` (`ID`, `LAPTOP_ID`, `LAPTOP_DESCRIPTION`, `EMPLOYEE_ID`) VALUES (‘1’, ‘1234’, ‘dell 111′, ’12’); . Executing: INSERT INTO `employees_db`.`employee_assets` (`ID`, `LAPTOP_ID`, `LAPTOP_DESCRIPTION`, `EMPLOYEE_ID`) VALUES (‘1’, ‘hp123’, ‘hp laptop’, ‘1’); .

.

Joins

.

SQL Joins are used to fetch data from multiple tables by combining them based on their relationship.

.

Suppose we have the two tables EMPOYEES and EMPLOYEE_ASSETS, where EMPLOYEE_ASSETS.EMPLOYEE_ID is a foreign key referencing EMPLOYEES.ID:

.

EMPLOYEES

.

EMPLOYEE_ASSETS

.

Inner Join

.

The INNER JOIN (or JOIN) returns rows from both the tables where the join condition is satisfied.

The following script fetches employees that have a child record in EMPLOYEE_ASSETS table:

SELECT * FROM EMPLOYEES INNER JOIN EMPLOYEE_ASSETS ON EMPLOYEES.ID = EMPLOYEE_ASSETS.ID; .

.

Outer Join

.

The Outer Join returns the matching rows as well as the unmatched rows from the tables.

1. Left Outer Join

The LEFT OUTER JOIN (or LEFT JOIN) returns the matching rows and the unmatched rows from the left table in the join clause:

SELECT * FROM EMPLOYEES LEFT OUTER JOIN EMPLOYEE_ASSETS ON EMPLOYEES.ID = EMPLOYEE_ASSETS.ID; .

.

2. Right Outer Join

The RIGHT OUTER JOIN (or RIGHT JOIN) returns the matching rows and the unmatched rows from the right table in the join clause:

SELECT * FROM EMPLOYEES RIGHT OUTER JOIN EMPLOYEE_ASSETS ON EMPLOYEES.ID = EMPLOYEE_ASSETS.ID; .

.

Cross Join

.

The CROSS JOIN returns all the possible combinations of the rows from both the tables:

SELECT * FROM EMPLOYEES CROSS JOIN EMPLOYEE_ASSETS; .

.

.

Self Join

.

The SELF JOIN joins a table with itself.

Suppose we have another column MANAGER_ID in the EMPLOYEES table that contains the ID of another record in the same table:

The following script returns the name of employee and its manager from the above table:

SELECT e1.FIRST_NAME as EMPLOYEE, e2.FIRST_NAME as MANAGER FROM employees e1, employees e2 WHERE e1.MANAGER_ID = e2.ID; .

.