What is SQL?

SQL (Structured Query Language) is a standard command language which is used for the purpose of storage, manipulation, and retrieval of data from relational databases.

Let us see how SQL can be used in database systems like MySQL, SQL Server, etc.

Operations performed with SQL

Below are some of the basic operations that could be performed with SQL

• Execution of queries against a database
• Retrieval of data from a database
• Insertion of records in a database
• Updating records in a database
• Deleting records from a database
• Creation of a new database
• Creation of new tables in a database
• Creation of stored procedures in a database
• Creation of views in a database
• Setting permissions on tables, views, and procedures
• Embedding within other languages using SQL libraries

SQL Commands supported by different versions of SQL

SQL is basically an ANSI/ISO standard, however, SQL language has different versions. In order to be compliant with the ANSI standard, all the versions support the most commonly used SQL commands (SELECT, UPDATE, DELETE, INSERT, WHERE) similarly.

In addition to SQL standards, there are self-proprietary extensions of most SQL databases.

SQL Data Types

Numeric:

Both signed and unsigned integers are allowed with the numeric data type. This numeric category is further divided into exact and approximate data types, with the exact data type allowing integers in the form of whole numbers, and approximate data type allowing floating integers.

Character String:

Character strings of fixed and variable length are allowed with this data type. Further categorization of this data type into Unicode characters allows the fixed and variable length of Unicode characters.

Binary:

With this, data is stored in the form of binary values.

Date & Time:

With this, data is stored in different date and time formats.

Other:

This has data types like table, XML, sql_variant, unique identifier.

SQL Operators

Operators are symbols that tell the compiler to perform operations like arithmetic, logical, comparison etc.

Operators supported by SQL are:

• Arithmetic Operators (+, -, *, /, %)
• Bitwise Operators (|, &, ^)
• Comparison Operators (<>, =, <=, >=, <, >)
• Compound Operators (|*=, ^.=, &=, %=, /=, *=, .=, +=)
• Logical Operators (AND, OR, NOT, BETWEEN, LIKE, IN, EXISTS, ALL, ANY, IS NULL, UNIQUE)

RDBMS

Relational Database management System is the basis for SQL and all its modern database systems like MySQL, MS SQL Server, etc. Data in RDBMS is stored in the form of a table, which is the collection of related data values in rows and columns.

Every table has a field or column which is used to maintain specific information about every table record or row.

Commonly used SQL Commands and Syntax:

A database generally has one or more tables. Each table has its own name.

SQL statements are not case-sensitive.

In some database systems, a semi-colon is required at the end of each SQL statement.

Let us consider below table named as ‘Customers’ to understand the SQL Commands and their syntax in SQL server:

CREATE Command

This command is used for the creation of a table, view, or database.

Syntax:

Database Creation:

CREATE DATABASE DatabaseName;

Table Creation:

CREATE TABLE TableName
( Column1 DataType,
  Column2 DataType,
  ………..
  ColumnN DataType);

So let’s see how we created the above table using the CREATE command:

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

View Creation:

A view is a virtual table that derives its data from one or more columns of the table and it is based on the result-set of a SQL statement.

CREATE VIEW or REPLACE ViewName as
SELECT Column1, Column2, ……., ColumnN
From TableName
WHERE condition;

DROP Command

This command is used to completely drop a table, view, or database.

Syntax:

DROP DATABASE DatabaseName;
(or)DROP TABLE TableName;
(or)DROP VIEW ViewName;

ALTER Command

This command is used to modify, add or delete columns in an existing table.

Syntax:

ALTER TABLE TableName
ADD ColumnName DataType;
or
ALTER TABLE TableName
DROP Column ColumnName;
(or)
ALTER TABLE TableName
ALTER Column ColumnName DataType;

For instance, let’s say we wish to add another column say ‘Gender’ to the ‘Customers’ table, then we can use the ALTER command as follows:

ALTER TABLE Customers
ADD Gender varchar (255);

Now if we wish to delete this new column from the ‘Customers’ table, then ALTER command can be used as follows:

ALTER TABLE Customers
DROP Column Gender;

TRUNCATE Command

This command is used to delete one or more columns from an existing table. With this, the information present in the table will be lost, but the table will still be present in the database.

Syntax:

TRUNCATE TABLE TableName;

INSERT INTO Command

This command is used to insert new records in a table.

Syntax:

INSERT INTO TableName (Column1, Column2, ……., ColumnN)
VALUES (VALUE1, VALUE2, ……………);

NOTE: Mentioning the column names is not mandatory in the above syntax

Let us say we wish to add one more data record of a customer to the ‘Customers’ table, then we use the INSERT INTO command as follows:

INSERT INTO Customers
VALUES (‘6’, ‘Rohit’, ’9654231734’, ‘Blair Road 34’, ‘Kolkata’, ‘India’);

UPDATE Command

This command is used to modify the existing records in a table.

Syntax:

UPDATE TableName
SET Column1= Value1, Column2= Value 2
WHERE condition;

NOTE: In this, the WHERE clause is used to limit the result set and extract records that fulfill a particular condition.

For instance, let’s say that the phone number of the customer whose customer id is 2, has been changed and we wish to update this in the ‘Customers’ table. To do this, we use the UPDATE command as follows:

UPDATE Customers
SET Number= ‘9825431672’
WHERE CustomerID= ‘2’;

SELECT Command

This command is used to select data from a table

Syntax:

SELECT Column1, Column2, …………, ColumnN
from TableName;

If we wish to select all the records from a table, then we use the SELECT statement as follows:

SELECT * from TableName;

A SELECT statement can be used with clauses like WHERE, DISTINCT, ORDER BY, GROUP BY, HAVING, INTO. We will study these clauses in the latter part of the tutorial.

DELETE Command

This command is used to delete records from a table.

Syntax:

DELETE from TableName
WHERE condition;

NOTE: When used without the WHERE clause, it deletes all the records.

LIKE Command

This command is used with a WHERE clause when a specific pattern is to be searched in a column. There are two wildcards used with the LIKE command to search the pattern:

• %: This is used to match 0 or more character
• _: This is used to match exactly one character

Syntax:

SELECT ColumnName from TableName
WHERE ColumnName LIKE pattern;

SELECT DISTINCT Command

This statement is used to return distinct or unique values only. There are often many duplicate values in a column and we wish to list the unique values. To do this, we use the SELECT DISTINCT statement as follows:

SELECT DISTINCT Column1, Column2, ……………
from TableName;

Suppose we wish to select the distinct cities from the ‘Customers’ table, so we write the below statement to have this result:

SELECT DISTINCT City
from Customers;

SQL ORDER BY Clause

This is used for sorting in ascending or descending order. The data is sorted in ascending order by default. In order to sort in descending order, we can use the DESC keyword.

Syntax:

SELECT Column1, Column2, …….
from TableName
ORDER BY Column1, Column2, ……. ;

SQL GROUP BY Clause

This is used to group rows that have identical values and are generally used with aggregate functions like COUNT, MAX, MIN, SUM, AVG.

NOTE: GROUP BY clause is used with the SELECT statement. GROUP BY clause is placed after the WHERE clause and before the ORDER BY clause in a SQL query.

Syntax:

SELECT ColumnName from TableName
WHERE condition
GROUP BY ColumnName
ORDER BY ColumnName;

For instance in the ‘Customers’ table, if we wish to display number of customers in each city, then we can the GROUP BY clause as below:

SELECT COUNT(CustomerID) as NoOfCust, City
from Customers
GROUP BY City;

This will give the below output:

SQL And, Or, Not Operators

The WHERE clause can be used with any of the operators: AND, OR, NOT.

The AND and OR operators are the logical operators that are used to filter records on the basis of more than one condition.

AND will display records when all the mentioned conditions are true, while OR will show records when any of the mentioned condition is true.

NOT operator will show records when the mentioned condition is not true.

The syntax for AND/OR:

SELECT Column1, Column2, …..
from TableName
WHERE condition1 AND/OR condition2 ………….;

For instance, let’s say we wish to display those records from the ‘Customers’ table where the country is ‘India’ and the city is ‘Mumbai’:

SELECT * from Customers                                          
WHERE Country= ‘India’ AND City= ‘Mumbai’;

Now let’s say we wish to display those records from the ‘Customers’ table where the city is ‘Mumbai’ or ‘Pune’:

SELECT * from Customers                                             
WHERE City= ‘Pune’ OR City= ‘Mumbai’;

The syntax for NOT:

SELECT Column1, Column2, …..
from TableName
WHERE NOT condition;

For instance, if we wish to display those records from the ‘Customers’ table where the city is not ‘DELHI’:

SELECT * from Customers                                             
WHERE NOT City= ‘DELHI’;

SQL Keys

• Primary Key: This is used to uniquely identify each row in a table
• Unique Key: This is used to uniquely identify a single row in a table, and can hold a NULL value in a column
• Foreign Key: This is used to link two tables, and it refers to the Primary key of another table
• Candidate KEY: This can uniquely identify a table. There can be more than one candidate key in a table, and one of these can be chosen as the Primary key.
• Super Key: This can uniquely identify a row. So primary key, candidate key, and unique key are all super keys.
• Alternate Key: These are those candidate keys that are not chosen as the primary key.
• Composite Key: This is a combination of two or more columns which uniquely identify each row

The most commonly used keys are PRIMARY Key and Foreign key. Let us consider the below example to understand these keys better:

We have a ‘Customers’ table and an ‘Orders’ table like below:

So from the above two tables, we can see that ‘CustomerID’ in the ‘Orders’ table points to ‘CustomerID’ in the ‘Customers’ table.

‘CustomerID’ in the ‘Orders’ table is a foreign key of the ‘Orders’ table, and the primary key of the ‘Customers’ table.

SQL Joins

In SQL, when rows from two or more tables are to be combined based on a common column between the tables then we use joins.

The four joins used in SQL are:

• Inner Join: This join is used to return all the records that have matching values in both tables
• Full Join: This join is used to return all the records that have matching values in either of the tables
• Left Join: This join is used to return all records from the left table and matching records from the right table. In case of no match, the result set will have NULL in the right table.
• Right, Join: This join is used to return all records from the right table and matching records from the left table. In case of no match, the result set will have NULL in the left table.

Syntax:

Let us consider the below two tables: ‘Customers’ table and ‘Orders’ table, that have a common column ‘CustomerID’, and see what result-set we will get with each type of join:

SELECT ColumnName(s)
from TableName1
LEFT JOIN (or any join) TableName2
on TableName1.ColumnName=TableName2.ColumnName;

If we wish to select all orders along with their customer information, then we use inner join as below:

SELECT OrderID, CustomerName
from Orders
INNER JOIN Customers
on Orders.CustomerID=Customers.CustomerID;

If we wish to select all customer information along with any orders they might have, then we use left join or right join as below:

SELECT OrderID, CustomerName
from Orders
RIGHT JOIN Customers
on Orders.CustomerID=Customers.CustomerID;

SELECT OrderID, CustomerName
from Orders
RIGHT JOIN Customers
on Orders.CustomerID=Customers.CustomerID;

If we wish to select all customers and all orders, then we use full join as below:

SELECT OrderID, CustomerName
from Customers
FULL JOIN Orders
on Orders.CustomerID=Customers.CustomerID;

SQL Views

A view is a virtual table which is produced with the result-set of a SQL query. It contains rows and columns like an actual table and has fields from multiple tables.

For instance, we have the ‘Customers’ table and we wish to have a view which shows customers from the city ‘Delhi’ only. So we execute the below query to do so:

CREATE VIEW [Delhi Customers] AS
SELECT CustomerName, Number
from Customers
WHERE City= “Delhi”;

This will create the view named as ‘Delhi Customers’, and to view result-set from this view, the following query can be executed:

SELECT * from [Delhi Customers];

Concat Function

In SQL we have a ‘CONCAT’ function that is used to join the two strings to develop a single string or to Concatenate strings together. The CONCAT function is capable of joining 255 strings to make them into one. When you give non-character strings as values, then the CONCAT() function will implicitly convert the given values into strings and concatenate them.

Concat Function Syntax:

CONCAT(string1, string2, ...., string_n)

E.g: Add two strings together: To perform this function lets take two strings and concatenate them, which are “Besant” and “Technologies”. Below mentioned is the simple process for Concatenating the two strings,

SELECT CONCAT('Softpro', 'India');

Now hit the Run SQL button to get the result.

Result: 

Softpro India

To separate two strings in SQL we use “CONCAT_WS” syntax.  Let’s take the above strings as an example to

Example :

CONCAT_WS("-" , "Softpro", "India");

Output:

Softpro-India

Concatenation Parameters:

We have three concatenation parameters which are

• CONCAT Parameters: This considers string values as the main parameter and concatenates separated by a comma.
• Addition Operator Parameters — You need strings separated by a comma that needs to be concatenated.
• CONCAT_WS Parameters – This parameter separates two strings.

How to use table values for concatenation?

Let’s consider the values presented in the below table for concatenation

Now let’s concatenate the first and last name:

SELECT first_name,last_name,
CONCAT (first_name,' ',last_name)full_name
FROM N
ORDER BY full_name

Using CONCAT With Null Values

When the value is NULL the CONCAT function uses empty for concatenation.

SELECT first_name,last_name,phone,
CONCAT(first_name,' ',last_name,phone)full_name
FROM N
ORDER BY full_name

Order by Clause

The order by clause allows you to sort the results from the data whether the data may be in ascending order or in descending order and the data may be from one or multiple columns. There are some databases whose query function is set to sort the data in ascending order by default. In syntax, you need to specify the column name by which you want to sort after the Order By clause followed by DESC or ASC. The ASC keyword represents Ascending and DESC keyword Descending.

Syntax

Below mentioned one is the basic Syntax for the ORDER BY clause.

SELECT column-list 
FROM table_name 
[WHERE condition] 
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

In the ORDER BY clause, you are allowed to use multiple columns. But make sure that the column you are sorting must be there in the column-list.

AS you know already how the order by In SQL works, let us consider a few examples here. Let’s consider the below table to get knowledge of operations in the ORDER BY Clause. You need a CREATE table statement in SQL to create tables.

ORDER BY clause on one column

When you want to sort the data of a specific column, all you need to do is simply mention the name of the clause after the ORDER BY clause in SQL.

Example:

Now let’s consider a scenario here to write a query to select students from the “Students” table. Sorted by the “City” column.

Code: 

SELECT * FROM Students
ORDER BY City;

Upon the execution of the above code, you will get the below result.

ORDER BY DESC

If you wish to sort the data in descending order, all you need to do is mentioning the keyword DESC after the ORDER BY clause in SQL.

Example:

Let’s consider an example where you are required to write a query to select students from the “students” table and sort by the “CITY” column in descending order.

Code: 

SELECT * FROM Students
ORDER BY City DESC;

Upon the execution of the above code, you will get the result as below.

ORDER BY on several columns

Sometimes you need to sort the data from many columns, in that case, you need to specify the particular columns after the ORDER by a clause in SQL.

Example: 

Let’s consider a situation, where you need to write a query to by selecting the “Students” table, sorted by the “First Name” column and “City” column.

Code:

SELECT * FROM Students
ORDER BY City, FirstName;

Based on the query that you have written above, you will get the result which shows you order by City, but sometimes the city name may be the same for two elements, in that case, they are ordered by their FIrstName.  Upon the execution of the above code, you will get the output as below.

You can also sort students in different ways such as sort by descending order for “Firstname” and ascending order for “City”.  To execute this situation you need the below code.

Code: 

SELECT * FROM Students
ORDER BY City ASC, FirstName DESC;

Upon the execution of the above code, you will get the output as below.

With this, we have come to the end of the Order by clause concept in SQL, hope you may have found this helpful for you.

Manipulation function in SQL

In SQL we have three different Manipulation functions which are LOWER, UPPER, and INITCAP. Each function plays a specific role in making modifications to the text in the SQL database. Let’s look at each case manipulation:

Lower

This function converts a string into lowercase. It takes input as an argument and converts the string into the lower case. It returns a fixed-length string when the incoming string is fixed. LOWER will never modify characters that are not letters, because the case is irrelevant for special characters and numbers, such as modules (%) or a dollar sign ($).

Syntax:

 LOWER(‘string’)

Example:

Input1:

SELECT LOWER(BESANT TECHNOLOGIES') FROM DUAL;

Output1:

 Softpro India  

Input2:

SELECT LOWER('Softpro@123') FROM DUAL;

Output2:

Softpro@123

UPPER

This function converts a string into Uppercase. It takes input as an argument and converts the string into the Uppercase. UPPER will never modify characters that are not letters, because the case is irrelevant for special characters and numbers, such as modules (%) or a dollar sign ($).

Syntax:

UPPER(‘string’)

Example:

Input1:

SELECT UPPER('SoftproIndia') FROM DUAL;

Output1:

SoftproIndia

Input2:

SELECT UPPER('SoftproIndia@123') FROM DUAL;

Output2:

SoftproIndia@123

INITCAP

This function returns you the text which comes with the first letter uppercase and remaining letters in lowercase. The only criteria are that the words in the string must be divided by either space or _ or #.

Syntax:

INITCAP(‘string’)

Input1:

 SELECT INITCAP('Softpro India is a best online training company') FROM DUAL;

Output1:

Softpro India is a best online training company.

Input2:

SELECT INITCAP('PRACTICE_MAKES_MAN_PERFECT') FROM DUAL;

Output2:

Practice_Makes_Man_Perfect. 

Date and Time in SQL

There are many Important Date and time-related functions available through SQL. When working with the database the format of the table should be matched with the data that you input in order to insert. Below mentioned are the data types available in SQL server DATE, which allow the user for storing a data/time value in a database:

• SQL DATE –  YYYY-MM-DD
• SMALLDATETIME – YYYY-MM-DD HH:MI: SS
• DATETIME –  YYYY-MM-DD HH:MI: SS

Now let’s consider an example of how to use Date and Time in SQL.

Consider we are having the following “Order Table”

Here we would like to select the Order date of 2018-12-12

To execute the above request we  use ‘SELECT’ statement

SELECT * FROM Orders WHERE OrderDate=’2018-12-12′

Now let’s assume where your table contains time along with the date and looks like below.

Here if we use the same ‘SELECT’ statement as we did above:

SELECT * FROM Orders WHERE OrderDate='2018-12-12'

Output:

No result!

Reason: the query is written only for dates but not for the time portion.

Tip: if you want to have simple queries do not mix your time components with dates.

Creating a table in SQL:

Sometimes you are required to create new tables in SQL for the purpose of storing particular data. In such scenarios, The CREATE TABLE is a statement given to the database system to explain that you want to create a new table in a database. The data type parameter will explain which type of data the column should hold (e.g.Integer, varchar, date, etc.).

Syntax: 

CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);

Example Query:

Let’s consider the below query to create a Student table with three columns such as Name, Roll_No, and Subject.

CREATE TABLE Students
(
NAME varchar(35),
ROLL_NO int(2),
SUBJECT varchar(35),
);

When you execute the code you will get a table with a student name like below.

The NAME and SUBJECT fields are of varchar type and these fields can now store a maximum of 35 characters.  When it comes to the ROLL_NO field it is an integer type and can store the number of size 2.

Case Statement in SQL:

In SQL CASE is the advanced version of IF…ELSE statement. When it comes to IF…ELSE the maximum number of conditions allowed is one, whereas the CASE allows users to apply various conditions to perform various sets of actions in SQL. The SQL comes with the two different types of CASE statements which are:

• Simple CASE
• Searched CASE

Syntax 

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;

Let’s consider an example here: 

Now let’s write code to modify the elements presented in this table. If the department name is ‘EC’ it gets changed to ‘Electronics and Communication’ and if it is ‘CS’ it gets changed to ‘Computer Science’, and when it is ‘HCS’ it gets changes to’ Humanities and Social Sciences’.

It can be done with the help of the ‘Case Statement’.

Sample Query:

The variable considered here is the department_name which is entered in the SQL code.

CASE department_name
WHEN 'EC'
THEN UPDATE Faculty SET
department='Electronic Communication';
WHEN 'HCS'
THEN UPDATE Faculty SET
department='Humanities and Social Sciences';
ELSE UPDATE Faculty SET
department='Computer Sciences';
END CASE

Output:

The name of each department gets changed from the short form to full form.