SQL Tutorial : One Stop Solution to Learn SQL

In today’s market, where around 2.5 Quintillion bytes of data gets generated every day, it is very important to understand how to handle such a humongous amount of data. Well, this is where Structured Query Language or SQL comes into the picture. So, in this article on SQL Tutorial, I will discuss the following important concepts, which are a must to one’s journey on becoming a Database Administrator.

• Introduction To SQL
  1. What is SQL?
  2. Applications of SQL
  3. SQL Data Types
  4. SQL Operators
• Top SQL Commands
  1. CREATE
  2. DROP
  3. ALTER
  4. TRUNCATE
  5. EXPLAIN
  6. INSERT INTO
  7. UPDATE
  8. SELECT
  9. LIKE
  10. GRANT
• Keys in database
• SQL Constraints
• Normalization
• SQL Joins
• Views

SQL Tutorial: Introduction To SQL

What is SQL?

Developed by Donald D.Chamberlin in the 1970s, Structured Query Language or most commonly known as SQL is one of the most popular languages used to manipulate, store, update and retrieve data from a relational database. SQL consists of various commands segregated into 4 categories i.e. DDL, DML, DCL, and TCL to play with data in databases.  Also, relational databases like MySQL Databases, Oracle, MS SQL Server, Sybase etc use SQL to modify data.

Applications of SQL

The applications of SQL are as follows:

• With SQL, you can create and drop tables and databases.
• It allows the users to define and manipulate data in databases.
• SQL allows the users to access, modify, and describe data in RDBMS.
• With SQL, you can set permissions on tables, views, and procedures and grant specific permissions to different users.
• SQL allows you to embed within other languages using SQL libraries and modules.

Now that you know the basics of SQL, next in this SQL tutorial, let us understand what are the different SQL Data Types.

SQL Data Types

SQL Data Types are divided into the following categories:

• Numeric  – The numeric data types allow both signed and unsigned integers. They can be further divided into exact and approximate data types where exact allows the integers in the form of whole numbers and approximate allow floating integers. 
• Character String – This data type allows characters of fixed and variable length. This data type also can be further categorized into Unicode characters, which allow fixed and variable length of Unicode characters.
• Binary – The Binary data types allows data to be stored in the format of binary values, for fixed and variable length.
• Date & Time – This data type allows data to be stored in different formats of date and time.
• Other – This section of data types has data types such as table, XML, cursor, uniqueidentifier, and sql_variant.

If you wish to get a detailed understanding of the different SQL data types, you can refer to detailed guide on SQL Data Types.

SQL Operators

Operators are the constructs which can manipulate the values of the operands. Consider the expression 4 + 6 = 10, here 4 and 6 are operands and + is called operator.

SQL supports the following types of Operators:

• Arithmetic Operators
• Bitwise Operators
• Comparison Operators
• Compound Operators
• Logical Operators

To know the different operators supported by SQL, in an elaborated manner, you can click here. So, now that you know what is SQL and its’ basics, let us understand the top commands or statements in SQL.

SQL Tutorial: Top SQL Commands

SQL consists of various commands or statements to add, modify, delete or update data in the database. In this article on SQL tutorial, we are going to discuss the following statements:

1. 1. CREATE
   2. DROP
   3. ALTER
   4. TRUNCATE
   5. EXPLAIN
   6. INSERT INTO
   7. UPDATE
   8. SELECT
   9. LIKE
   10. GRANT

In this SQL tutorial, I am going to consider the below database as an example, to show you how to write queries using these SQL commands.

CREATE

The CREATE statement is used to create a table, view or a database in the following way:

CREATE DATABASE

Used to create a database.

Syntax

CREATE DATABASE DatabaseName;

Example

 
CREATE DATABASE CustomerInfo; 

CREATE TABLE

This statement is used to create a table.

Syntax

CREATE TABLE TableName (
Column1 data type,
Column2 data type,
....

ColumnN data type
);

Example

 
CREATE TABLE Customers
(
CustomerID int,
CustomerName varchar(255),
PhoneNumber int,
Address varchar(255),
City varchar(255),
Country varchar(255)
);

CREATE VIEW

Used to create a view.

Syntax

CREATE VIEW OR REPLACE ViewName AS
SELECT Column1, Column2, ..., ColumnN
FROM TableName
WHERE Condition;

Example

CREATE VIEW OR REPLACE HydCustomers AS
SELECT CustomerName, PhoneNumber
FROM Customers
WHERE City = "Hyderabad";

Note: Before you start creating a table and entering values, you have to use the database, using the USE statement as [USE CustomersInfo;]

DROP

The DROP statement is used to drop an existing table, view or a database.

DROP DATABASE

Used to drop the database. When you use this statement, complete information present in the database will be lost.

Syntax

DROP DATABASE DatabaseName;

Example

DROP DATABASE CustomerInfo;

DROP TABLE

Used to drop the table. When you use this statement, complete information present in the table will be lost.

Syntax

DROP TABLE TableName;

Example

DROP TABLE Customers;

DROP VIEW

Used to drop the view. When you use this statement, complete information present in the view will be lost.

Syntax

DROP VIEW ViewName;

Example

DROP VIEW HydCustomers;

ALTER

The ALTER statement is used to add, delete or modify constraints or columns in an existing table.

ALTER TABLE

The ALTER statement is used to delete, add, modify columns in an existing table. You can use ALTER TABLE with ADD/ DROP column to either add or drop a column in the table. Apart from this, you can also ALTER/MODIFY a specific column. 

Syntax

ALTER TABLE TableName
ADD ColumnName Data Type;
ALTER TABLE TableName
DROP COLUMN ColumnName;

ALTER TABLE TableName
ALTER COLUMN ColumnName Data Type;

Example

--ADD Column Gender:
 ALTER TABLE Customers
ADD  Gender varchar(255);
 
--DROP Column Gender: 
ALTER TABLE Customers
DROP COLUMN Gender ;

--Add a column DOB and change the data type from Date to Year.
 
ALTER TABLE DOB
ADD DOB date;
 
ALTER TABLE DOB
ALTER DOB year;

TRUNCATE

The TRUNCATE statement is used to delete the information present in the table, but not the table itself. So, once you use this command, your information will be lost, but not the table will still be present in the database.

Syntax

TRUNCATE TABLE TableName;

Example

TRUNCATE Table Customers;

EXPLAIN

The EXPLAIN and DESCRIBE statements are synonyms used to obtain a query execution plan and the information about a table structure respectively. This statement can be used with INSERT , DELETE, SELECT, UPDATE and REPLACE statements.

Syntax

--Syntax for DESCRIBE
DESCRIBE TableName;

--Sample syntax for EXPLAIN
EXPLAIN ANALYZE SELECT * FROM TableName1 JOIN TableName2 ON (TableName1.ColumnName1 = TableName2.ColumnName2);

Example

DESCRIBE Customers;

EXPLAIN ANALYZE SELECT * FROM Customers1 JOIN Orders ON (Customers.CustomerID = Orders.CustomerID);

INSERT INTO

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

Syntax

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

--If you do not want to mention the column names then use the below syntax, but the order of values entered should match the column data types :

INSERT INTO TableName
VALUES (Value1, Value2, Value3, ...);

Example

INSERT INTO Customers(CustomerID, CustomerName, PhoneNumber, Address, City, Country)
VALUES ('06', 'Sanjana', '9654323491', 'Oxford Street House No 10', 'Bengaluru', 'India');
 
INSERT INTO Customers
VALUES ('07', 'Himani','9858018368', 'Nice Road 42', 'Kolkata', 'India');

UPDATE

The UPDATE statement is used to modify the records already present in the table.

Syntax

UPDATE TableName
SET Column1 = Value1, Column2 = Value2, ...
WHERE Condition;

Example

UPDATE Customers
SET CustomerName = 'Aisha', City= 'Kolkata'
WHERE EmployeeID = 2;

SELECT

The SELECT  statement is used to select data from a database and store it in a result table, called the result-set.

Syntax

SELECT Column1, Column2, ...ColumN
FROM TableName;

--(*) is used to select all from the table
SELECT * FROM table_name;

-- To select the number of records to return use:
SELECT TOP 3 * FROM TableName;

Example

SELECT CustomerID, CustomerName
FROM Customers;
 
--(*) is used to select all from the table
SELECT * FROM Customers;
 
-- To select the number of records to return use:
SELECT TOP 3 * FROM Customers;

Apart from this, you can use the SELECT keyword with  DISTINCT, ORDER BY, GROUP BY, HAVING Clause and INTO.

LIKE

This operator is used with a WHERE clause to search for a specified pattern in a column of a table. There are mainly two wildcards that are used in conjunction with the LIKE operator:

• % – It matches 0 or more character.
• _ – It matches exactly one character.

Syntax

SELECT ColumnName(s)
FROM TableName
WHERE ColumnName LIKE pattern;

Example

SELECT * FROM Customers
WHERE CustomerName LIKE 'S%';

GRANT

The GRANT command is used to provide privileges or access on the database and its objects to the users.

Syntax

GRANT PrivilegeName
ON ObjectName
TO {UserName |PUBLIC |RoleName}
[WITH GRANT OPTION];

where,

• PrivilegeName – The privileges/rights/access granted to the user.
• ObjectName – Name of a database object like TABLE/VIEW/STORED PROC.
• UserName – Name of the user who is given the access/rights/privileges.
• PUBLIC – To grant access rights to all users.
• RoleName – The name of a set of privileges grouped together.
• WITH GRANT OPTION – To give the user access to grant other users with rights.

Example

-- To grant SELECT permission to Customers table to admin
GRANT SELECT ON Customers TO admin;

Now that you know the top SQL Commands, let us understand what are the different types of keys used in the database. Well, this concept will help you understand how each table is related to the other table in a relational database management system.

SQL Tutorial: Keys

The following are the 7 types of keys, which can be considered in a database:

• Candidate Key – A set of attributes which can uniquely identify a table can be termed as a Candidate Key. A table can have more than one candidate key, and out of the chosen candidate keys, one key can be chosen as a Primary Key. 
• Super Key – The set of attributes which can uniquely identify a tuple is known as Super Key. So, a candidate key, primary key, and a unique key is a superkey, but vice-versa isn’t true.
• Primary Key – A set of attributes which are used to uniquely identify every tuple is also a primary key.
• Alternate Key – Alternate Keys are the candidate keys, which are not chosen as a Primary key. 
• Unique Key –  The unique key is similar to the primary key, but allows one NULL value in the column.
• Foreign Key – An attribute that can only take the values present as the values of some other attribute, is the foreign key to the attribute to which it refers.
• Composite Key – A composite key is a combination of two or more columns that identify each tuple uniquely.

I hope you have understood the different types of keys in the database, next in this article on SQL tutorial, let us discuss the constraints in the database. Well, SQL constraints are used to increase the accuracy and reliability of data that goes into the database through a table.

SQL Tutorial: Constraints

SQL constraints make sure that there is no violation in terms of a transaction of data if found then the action will be terminated. The main use of the following constraints is to limit the type of data that can go into a table. 

• NOT NULL – This constraint is used to ensure that a column cannot store a NULL value.
• UNIQUE –  The UNIQUE constraint is used to ensure all the values entered in a column or a table are unique.
• CHECK –  This constraint is used to make sure that a column or multiple columns satisfy a specific condition.
• DEFAULT – The DEFAULT constraint is used to set a default value for a column if no value is specified.
• INDEX –  This constraint is used to indexes in the table, through which you can create and retrieve data from the database very quickly.

If you want to know about the following constraints in-depth with syntax and examples, you can refer to other articles on SQL. So, now that you about keys and constraints in the database, next in this article on SQL tutorial, let us take a look an interesting concept Normalization.

SQL Tutorial: Normalization

Normalization is the process of organizing data to avoid duplication and redundancy. There are many successive levels of normalization and those are called normal forms. Also, each consecutive normal form depends on the previous one.  The following are the normal forms present:

To understand the above normal forms, let us consider the following table:

By observing the above table, you can clearly make out the data redundancy and duplication of data. So, let’s normalize this table. To start normalizing the databases, you should always start with the lowest normal form i.e. 1NF and then go to the higher normal forms eventually.

Now, let us see how we can perform the first normal form, for the above table.

First Normal Form(1NF)

To make sure that the database must in 1NF, each table cell should have a single value. So, basically all the records must be unique. The above table will be normalized into 1NF as below:

If you observe in the above table, all the records are unique. But, yet there is a lot of data redundancy and duplication. So, to avoid that, let us normalize database into second normal form.

Second Normal Form(2NF)

To make sure that the database must in 2NF, the database should be 1NF and should also have a single-column primary key. The above table will be normalized into 2NF as below:

If you observe the above tables, each table has a single-column primary key. But there is a lot of data redundancy and duplication of a few columns. So to avoid that, let us normalize database into third normal form.

Third Normal Form(3NF)

To make sure that the database must in 3NF, the database should be in 2NF and must not have any transitive functional dependencies. The above tables will be normalized into 3NF as below:

If you observe the above tables, the database does not have any transitive dependency. So, after this step, we do not have to normalize our database further. But, if you see any anomalies present or more than a single candidate key, then you can go forward with the next higher normal form i.e. BCNF.

Boyce-Codd Normal Form(BCNF)

To make sure that the database must in BCNF, the database must be present in 3NF and the tables must be divided further, to make sure only one candidate key present.

With this, we come to an end to normalization. Now, next in this SQL tutorial, let us discuss an important concept in SQL, which is Joins.

SQL Tutorial: Joins

Joins are used to combine rows from two or more tables, based on a related column between those tables and also on few conditions. There are mainly four types of joins: 

• INNER JOIN: This join returns those records which have matching values in both the tables.
• FULL JOIN: The FULL JOIN returns all those records which either have a match in the left or the right table.
• LEFT JOIN: This join returns records from the left table, and also those records which satisfy the condition from the right table.
• RIGHT JOIN: This join returns records from the right table, and also those records which satisfy the condition from the left table.

So, this was a short description on JOINS, but if you want a detailed description on JOINS with a detailed example, you can refer to my article on SQL JOINS. Next, in this SQL tutorial, let us discuss the last concept for this article i.e. Views.

SQL Tutorial: Views

A view in SQL is a single table, which is derived from other tables. A view contains rows and columns similar to a real table and has fields from one or more tables. Refer to the below image:

To understand how to create and drop a view, you can refer to the CREATE and DROP statements mentioned above. With that, we come to an end to this article on SQL tutorial. I hope you found this article informative. Also, if you are preparing for Database Administrator interviews, and are searching for a comprehensive list of questions, you can refer to our article on SQL Interview Questions.

If you wish to learn more about MySQL and get to know this open-source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.

Got a question for us? Please mention it in the comments section of this SQLTutorial and we will get back to you.