What is SQL? Learn SQL and Download Free SQL Book

Introduction

SQL stands for Structured Query Language. SQL is a programming language for relational databases. SQL is used to define and manipulate relational databases. In this article, let's learn what SQL is and how to learn SQL. We will also learn about basic SQL queries. SQL is an open standard language used in relational database management systems (RDBMS) to query, read, update, and delete data. SQL is a common query language for RDBMS. SQL language queries are also known as SQL commands or SQL statements. 

The first implementation of SQL was in the early 1970s in an IBM database, System R.

SQL History

SQL was first introduced at IBM in 1970 by Donald D. Chamberlin and Raymond F. Boyce for Edgar F. Codd's relational model. It became a standard of the American National Standards Institute (ANSI) in 1986. Today, SQL is the most used database programming language in the world and is used in most popular relational databases, including MySQL, SQL Server, Oracle, SQLite, and DB2.

SQL is a cross-platform, static, h2-typed, multi-paradigm, declarative computer programming language. SQL files are stored as .sql files and can be executed from a command line or using various UI tools.

Relational Database Management Systems (RDBMS)

A relational database is a DBMS representing data in a tabular form of rows and columns. A table is a representation of an entity. A table is a combination of columns and rows. Each column in a table represents an attribute of the entity, also known as fields or properties. Each row in a table represents a record, the data associated with an entity. 

Each table column is an attribute (or property) of a Customer entity. Each row of the table represents customer data. The following tabular data represents a Customer entity with attributes including ID, Company Name, Contact Name, Contact Title, Address, City, and so on.

Each table column has a data type representing the data a column can store. For example, the company name or customer name is a varchar type that can store characters, but a zip code is a numeric field and can store numbers only.

Here is a detailed article on RDBMS, What is a Relational Database (RDBMS).

A SQL statement is a code that can be executed independently to generate some results. A SQL statement combines queries that retrieve, update, or delete data based on specific criteria, clauses (the conditions), expressions, and predicates.

The following query is a complete SQL statement with clauses, expressions, and a predicate that selects ContactName and ContactTitle columns from the Customers table where ContactTitle has 'Sales' and the City name is 'London.'

Please note

1. SQL is not a case-sensitive language. That means you can write in any case or combination of them.
2. A complete SQL query can have multiple SQL statements in it.
3. A semicolon is used to separate two SQL statements.

Here is a list of some of the most used SQL commands that are 

• SELECT- Gets data from one or multiple database tables.
• UPDATE- Updates data in a database table.
• DELETE- Deletes data from a database table.
• INSERT INTO- Adds new data into a database table.
• CREATE DATABASE- creates a new database.
• ALTER DATABASE- Modifies an existing database.
• CREATE TABLE- creates a new table.
• ALTER TABLE- Modifies an existing table.
• DROP TABLE- Deletes an existing table.

SQL as Data Query Language

SQL DQL (Data Query Language) command allows us to query and retrieve data from a database. 

SQL SELECT command is a DQL command. The SELECT command combines SELECT, FROM, and WHERE clauses.

A comma-separated list of the column names follows the SELECT clause. The FROM clause is followed by one or more table names, and the WHERE clause is a condition to filter records.

SELECT column1, column2, column3
FROM table_name;
WHERE condition

A simple SELECT command selects all records from the table, Customers. 

SELECT * FROM Customers

The following SELECT command selects three columns from the Customers table in London city. 

SELECT ContactName, ContactTitle, City  FROM Customers  WHERE City='London'

SQL SELECT query with WHERE clause can have many operators to filter records based on conditions. We can also select records in sorted orders from the database. We can also search records between a range. 

SQL as Data Definition Language

SQL DDL (Data Definition Language) commands create and manipulate database objects, including the database itself, tables, and other objects. The most commonly used DDL commands are CREATE, ALTER, and DROP.

Here are examples of CREATE, ALTER, and DROP commands.

CREATE DATABASE command creates a new database.

CREATE DATABASE databasename;  

CREATE TABLE creates a new database table.  

CREATE TABLE tablename (  
column1 datatype,  
column2 datatype,  
column3 datatype,  

);  

​The following CREATE TABLE command creates a new table with three columns.

CREATE TABLE Authors (  
AuthorID int,  
Name varchar(255),  
Book varchar(255),  
Publisher varchar(255)
);  

ALTER TABLE command alters a database table schema. For example, if you want to change a column's data type or add a new column.

ALTER TABLE tablename

ADD columnname datatype;

The following command alters the Authors table by adding a new column. 

ALTER TABLE Authors  
ADD Email varchar(255);  

DROP DATABASE command deletes a database.

DROP DATABASE databasename;

DROP TABLE deletes a database table.

DROP TABLE tablename;

The following command deletes the Authors table.

​DROP TABLE Authors;

SQL as Data Manipulation Language

SQL provides DML (Data Manipulation Language) commands to insert, update, and delete data. Common DML commands are INSERT, UPDATE, and DELETE.

Here are some examples of INSERT INTO, UPDATE, and DELETE commands.

INSERT INTO command inserts new records to a database table.

INSERT INTO TableName (Column1, Column2, Column3)  VALUES (value1, value2, value3);  

The following SQL command inserts one record. 

INSERT INTO Authors (AuthorID, Name, Book, Publisher)  VALUES (1001, 'Mahesh Chand', 'Programming ADO.NET', 'APress');  

UPDATE command updates existing records. The following UPDATE SQL command updates an existing record where AuthorID = 1001. 

UPDATE Authors  
SET AuthorID = 100, Book ='C# 8'  
WHERE AuthorID = 1001  

SQL Update queries can be complex. Here is a detailed tutorial on SQL UPDATE Tutorial.

The DELETE command deletes existing records. The following DELETE command deletes existing records where AuthorID is 100.

DELETE FROM Authors WHERE AuthorID = 100

SQL as Data Control Language

SQL DCL (Data Control Language) commands control access to a database, including privileges on database objects. Common DCL commands are GRANT and REVOKE.

SQL Performance

SQL is the language to access RDBMS; if your SQL statements are not written well, it will affect the performance of your query and hence the application. It is highly recommended to learn how to optimize SQL statements. 

Download free SQL EBooks.

Here are two free eBooks downloads (PDF) on SQL queries for beginners.

Joins in SQL

SQL Joins are used when you need to get data from multiple tables. There are four types of joins, inner, outer, cross, and self. If not programmed correctly, joins could affect an application's performance. Here is a detailed article on Joins in SQL Server. Everything You Need to Know about SQL Joins. 

Summary

This article teaches SQL, its types, and how SQL commands are created and used in relational database management systems.