Introduction to Structured Query Language (SQL)

Submitted by heartin on Sat, 02/04/2017 - 19:29

Structured Query Language (SQL) is a special-purpose programming language designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS).

A relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model. In the relational model of a database, all data is represented in terms of tuples, grouped into relations. Relation, tuple, and attribute are represented as table, row, and column, in a relational database. A relational database can be considered as a container for tables and a table is a grid with rows and columns to hold data.

Important points about SQL

SQL lets you communicate with databases to store, manipulate, and retrieve data, and also modify the structure of the database.
Individual statements in SQL are called queries.
SQL is case-INsensitive; however, it is become standard in SQL community to use all capital letters for SQL keywords.
In SQL, extra white spaces like spaces, tabs and carriage returns are stripped out before the SQL statement is processed.
SQL commands can be divided into DDL, DML, DCL and TCL.

DDL, DML, DCL and TCL

SQL commands can be divided into DDL, DML, DCL and TCL:

DDL stands for Data Definition Language, and contain commands for creating, modifying and deleting databases, schemas, tables, columns etc. such as CREATE, ALTER, RENAME, DROP etc.
DML stands for Data Manipulation Language, and these queries are used to create or manipulate the actual data. SQL implements CRUD operations through INSERT, SELECT, UPDATE and DELETE commands that belong to DML. CRUD (create, read, update and delete) operations are the four basic functions of any persistent storage mechanisms.
DCL stands for Data Control Language and contain commands such as GRANT, REVOKE etc.
TCL stands for Transaction Control and contain commands such as COMMIT, SAVEPOINT, ROLLBACK etc.

Creating tables to try out the examples

You need to first install and set up a relational database management system such as MySQL.

Next you need to create a database and use that database.

Create database testdb;

use testdb;

Data in relational data are stored as tables with rows and columns. So first create 2 tables employee and emp, with 2 columns empName and id.

Create table employee(empName varchar(20), id int PRIMARY KEY);

Create table emp(empName varchar(20), id int PRIMARY KEY);

You might also have to use some common sense for some of the examples, like deleting/modifying duplicate data.

CRUD Operations

CRUD stands for Create, Read, Update and Delete.

You create data through INSERT statements, read data through SELECT statements, update data through UPDATE statements and delete data through DELETE statements.

Data in relational data are stored as tables with rows and columns, and all operations are in general performed row-wise.

INSERT

You can use INSERT command to insert data into a table.

INSERT INTO emp values (‘Heartin’,1);

We can use INSERT and SELECT together to insert rows from a query. The column names of the columns returned by the SELECT statement are ignored by SQL in an INSERT SELECT statement. So the names of the returned columns do not need to match the column names of the columns data is going into.

INSERT INTO employee SELECT * from emp;

SELECT

You can use SELECT to retrieve data.

You can specify which columns to retrieve in an SQL SELECT statement or use * for all columns.

You can also specify an SQL expression, a UNIQUE or DISTINCT, or a combination in a SELECT statement.

Below are few different ways in which you can query your data.

SELECT * FROM employee;

SELECT empName, id FROM employee;

SELECT empName, MAX(id) FROM employee;

SELECT empName, CURRENT_DATE FROM employee;

SELECT UNIQUE empName FROM Employees;

SELECT COUNT(DISTINCT lastname) FROM Employees;

You can use the UNIQUE and DISTINCT keywords as a field constraint, in the WHERE clause or in the SELECT statement.

The difference is that UNIQUE treats two NULL rows as still unique, while DISTINCT treats two NULL rows as NOT UNIQUE.
It is better to use DISTINCT as SELECT UNIQUE is considered “old” SQL and your DBMS might not support it.

UPDATE

You use the UPDATE statement to change data after it is stored in the table, optionally using WHERE clause. You can also update data using fetched data from a query.

UPDATE employee set id=id+1000;

UPDATE employee set id=1001 WHERE id=1;

DELETE

You can use DELETE to delete rows from a table.

DELETE from employee WHERE id=1;

Datatypes in SQL

Common data types that we can use in SQL are INT, VARCHAR, FLOAT, DATE and TIME.

We can also have user defined data types based on predefined SQL types.

Constraints In SQL

Different constraints can be used while creating tables in SQL such as UNIQUE, PRIMARY KEY, FOREIGN KEY, NOT NULL, CHECK, DEFAULT etc.

A PRIMARY KEY is a column or group of columns that represents a unique identifier for each row in a table. PRIMARY key cannot have NULL values.
A UNIQUE constraint is similar to PRIMARY key, but you can have more than one UNIQUE constraint per table. Also, UNIQUE constraints can accept NULL, but just once. If the constraint is defined in a combination of fields, then every field can accept NULL and can have some values on them, as long as the combination values is unique.
A FOREIGN KEY in one table points to a PRIMARY KEY in another table; and is used to specify relationship between tables. The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column, because it has to be one of the PK values in the table it points to.
The CHECK constraint lets you constrain values stored in your table.
DEFAULT constraint gives all fields in a default value.