SQL Tutorial Step by Step

SQL stands for Structured Query Language designed to execute operations like storing, retrieving and manipulating data such as updating records, deleting records, creating and modifying tables, views etc in the relational database.
SQL is only a query language, and not a database. For executing SQL queries, databases need to be installed, such as Oracle, MongoDB, DB2, MySQL, SQL, SQL Server, PostGre, etc.

Some Advantages of SQL

SQL consists mainly of English statements that make it easy to learn and write SQL queries. Create, Drop databases and tables. Create views, stored procedures, and functions in a database. Set permissions on tables, procedures, and views. Access data in relational database management systems. Users can describe, define and manipulate the data. SQL queries are effective in handling large amounts of data. Databases for SQL use established standards accepted by ANSI and ISO whereas Non-SQL databases do not consider any such standards.

SQL Commands:

Now SQL interacts with the relational databases using commands that are classified into different groups and are listed as below

1. Data Definition Language (DDL):

The DDL statements are used to handle the design and storage of database objects by performing alter / modify database or table structure and schema operations. Below are mentioned DDL commands
A.) CREATE:
Used to create database and its objects such as table, index, views, store procedure, function and triggers.
Example Query:
CREATE TABLE tableName
(
ColumnName1 dataType1,
ColumnName2 dataType2,
ColumnName3 dataType3,
.
.
ColumnNamen dataTypen
);
B.) ALTER:
Used to change the structure of the tables in the database by adding, renaming, changing data type, modifying size or dropping a column and adding or dropping various limitations in current table.
Example Query:
ADD: ALTER TABLE tableName add (columnName dataType).
MODIFY: ALTER TABLE tableName modify (columName dataType).
RENAME: ALTER TABLE tableName rename old-columnName to columnName.
DROP: ALTER TABLE tableName drop (columnName).
C.) DROP:
Used to delete table or database as well, drop query completely removes a table from database and will also destroy the table structure.
Syntax :
DROP DATABASE databaseName.
DROP TABLE tableName.
D.) TRUNCATE:
Used to remove all records from a table, including all spaces allocated to the records, but this will not destroy the table’s structure. Truncate command works differently from Delete command, Delete command deletes all rows from a table whereas Truncate re-initializes a table just like a newly created table.
Syntax :
TRUNCATE TABLE tableName.
E.) RENAME: Used to rename a table.
Syntax:
RENAME TABLE oldTableName to newTableName;

2. Data Manipulation Language (DML)

The DML statement deals with manipulation of data and includes the most common SQL statements such as SELECT, INSERT, UPDATE, DELETE etc, and are the basic operations we perform on data such as selecting, inserting, Deleting and updating records in the table.
Also, DML command operations are not permanent in database i.e. Auto-committed and can be rolled back.
Below are the commands listed:
A.) SELECT:
One of the most used SQL queries and used to retrieve data from tables entirely or partially by using the WHERE clause.
Syntax:
SELECT columnName1, columnName2, columnNameN from tableName.
B.) INSERT:
Used to insert data into a table
Syntax:
INSERT into tableName values ​​(data1, data2).
C.) UPDATE:
Used to update existing data in a table for any row or column by using column name and also WHERE clause for any specific conditions.
Syntax:
UPDATE table-name set columnName = value where condition.
D.) DELETE:
Used to delete the row of data from a table and can also be used with a condition to delete a particular row using WHERE clause.
Syntax:
DELETE from tableName.
DELETE from tableName where columnName = value.

3. Data Control Language (DCL):

DCL statements control the level of access that users have on database objects.
E.) GRANT:
Used to allow users access permissions to database.
Example Query:
GRANT create session to username.
GRANT create table to username.
F.) REVOKE:
Used to withdraw user access permissions given using the GRANT command.
Example Query:
REVOKE create table from username.

4. Transaction Control Language (TCL):

TCL deals with transactions within a database and allows you to control and manage transactions to maintain integrity of data within SQL statements.
A. COMMIT:
Used to finalize the changes that correspond to a transaction and can not be reverted after commit.
B.) ROLLBACK:
Used to revert the changes corresponding to a transaction.
C.) SAVEPOINT:
Used to rollback the transaction making points within groups
D.) SET TRANSACTION:
Specifies the characteristics for the transaction
E.) BEGIN Transaction:
Opens a transaction.

CLAUSES

1.) WHERE:
This clause is used to filter the records while fetching the data from a single table or joining with. It is mostly used with Select, Update, and Delete query and will return / update / delete only those records that match the WHERE clause condition. Conditions can be specified using comparisons or logical operators such as “>, <, =, Like, Not” etc.
Example Query:
SELECT columnName1, columnName2, columnNameN from table-name WHERE [condition].
2.) ORDER BY:
This clause is used to sort the results of a SELECT query in ascending or descending order based on one or more columns. By default the sorting is of ascending order. Keywords used for sorting are ASC and DESC.
Example Query:
SELECT * from table-name ORDER BY Column1 [ASC | DESC].
SELECT column-list FROM tableName [WHERE condition]
[ORDER BY column1, column2, .. columnN] [ASC | DESC].
3.) GROUP BY:
As the name suggests, this clause is used to group the results of SELECT query in identical groups. It is also very commonly used with Aggregate functions (Count, MAX, MIN, SUM, AVG) to group the result set of one or more columns. The Group By clause follows the WHERE clause and precedes the ORDER BY clause in the SLECET query.
Example Query:
SELECT column_name, function (column_name)
FROM table_name WHERE condition GROUP BY column_name
4.) HAVING:
This clause is used to filter which group results should appear in final results. The HAVING was added to SQL because WHERE could not be used with aggregate functions.
Example Query:
SELECT column_name, function (column_name)
FROM table_name WHERE column_name condition
GROUP BY column_name HAVING function (column_name) condition
5.) LIKE:
This clause is used in a WHERE clause to search for a specified pattern or used to compare a value to a similar value using wildcard operatorsBelow are the two wild card operators used along with LIKE clause
A.) Percent Sign (%): This represents zero, one or multiple characters.
B.) Underscore Sign (_): Represents a single character.
Example Query:
SELECT * from tableName where columnName like ‘pattern%’.
SELECT * from tableName where columnName like ‘_pattern%’.
6.) TOP:
This clause is used to fetch the top N number of records from a table.
Example Query:
SELECT TOP 3 column_name (s)
FROM table_name WHERE [condition]

FUNCTIONS:

SQL provides many inbuilt functions for performing operations on data and are useful for performing mathematical calculations, string concatenations, sub-strings etc and are divided into two categories.

1.) Aggregate Functions:

These functions calculate a single result from multiple input rows, where the values ​​of multiple rows are grouped together on a certain input criterion to form a single value which is of more significance. Below are the most common aggregate functions available.
A.) AVG ():
This returns an average value after performing calculations over values ​​in a numeric column of any table
Example Query:
SELECT AVG (column_name) from table_name.
B.) COUNT ():
This function returns the number of rows present in tables on the basis of some condition or without condition.
Example Query:
SELECT COUNT (column_name) from table-name.
C.) FIRST ():
This function returns the first value of the specified column.
Example Query:
SELECT FIRST (column_name) from table-name.
D.) LAST ():
This function returns the last value of the specified column.
Example Query:
SELECT LAST (column_name) from table-name.
E.) MAX ():
This function returns the maximum value from the specified column of the table.
Example Query:
SELECT MAX (column_name) from table-name.
F) MIN ():
This function returns the minimum value from the specified column of the table.
Example Query:
SELECT MIN (column_name) from table-name.
G) SUM ():
This function returns the total sum of the values ​​of a numeric column.
Example Query:
SELECT SUM (column_name) from table-name.

2.) SCALAR FUNCTIONS:

These functions return a single value from an input value. Below are some scalar functions mentioned
A.) UCASE:
This is used to convert an input value from lower case to upper case.
Example Query:
SELECT UCASE (column_name) from table-name.
B.) LCASE ():
This was used to convert an input value from uppercase to lower case.
Example Query:
SELECT LCASE (column_name) from table-name.
C.) MID ():
This function is used to extract substrings from the string value of any column in the table.
Example Query:
SELECT MID (column_name, start, length) from table-name.
D.) ROUND ():
This function is used to round off a numeric field to the number of closest integers and basically used on a decimal point.
Example Query:
SELECT ROUND (column_name, decimals) from table-name.
E.) TRIM ():
This function is used to remove the leading or trailing characters of any character string and most commonly used for the removal of whitespaces and is called differently in different databases.
Example Query:
Both Sides Trim: SELECT TRIM (‘Sample’).
Left Side Trim: SELECT LTRIM (‘Sample’).
Right Side Trim: SELECT RTRIM (‘Sample’).
F.) EXTRACT:
This function is used to return only the specified part of date / time, such as year, month, day, hour, minute, seconds.
Example Query:
EXTRACT (unit FROM date) where unit can be anything like microseconds, seconds, minutes, hours, days, weeks, months, etc.
G.) POSITION ():
This function finds a given string within targeting and if found, returns the starting position of the given string as in the target string and if not found returns zero and all values ​​returned will always be numeric.
Example Query:
POSITION (inputString_pattern IN targetString_value)
H.) SUBSTRING:
This function is used to extract a particular portion of the character string on which it is operating
Example Query:
SUBSTRING (targetString_value FROM startPosition [FOR lengthNumber])
I.) CHARACTER_LENGTH: This function is used to return the length of character to numeric for any given string.
Example Query:
CHAR_LENGTH (‘ABCDEFGHIJKLMNOP’).

SQL NULL Functions:

These functions in SQL are used to handle the data where we can have possible NULL values ​​in the table. Below are the functions explained
A.) ISNULL:
This function is used to test whether an expression is NULL or not, if the expression is null the function returns 1 else 0.
B.) COALSCE ():
This function returns the first non-null expression between its arguments and if all the expression evaluates to null then the function returns null itself.
Example Query:
SELECT ISNULL (abxpression1)
COALESCE (“expression 1”, “expressions 2”, …)

SET OPERATORS:

These operators combine the results of two or more SELECT queries and these queries are also known as compound queries. Performing these operations requires the below mentioned parameters to be followed
• Each SELECT statement within UNION compulsory should have the same number of columns.
• The columns used should have the same data type.
• The columns used should also be in same order.

Below are the operators explained.

A.) UNION:
This operator only selects distinct values ​​by default.
Example Query:
SELECT column_name (s) FROM table1
UNION
SELECT column_name (s) FROM table2;
B.) UNION ALL:
The UNIONALL operator selects the duplicate values ​​for all the SELECT statements used
Example Query:
SELECT column_name (s) FROM table1
UNION ALL
SELECT column_name (s) FROM table2;
C.) INTERSECT:
This operator is used to select distinct rows by both the SELECT queries being operated.
Example Query:
SELECT expression1, expression2, … expression_n
FROM tables [WHERE conditions]
INTERSECT
SELECT expression1, expression2, … expression_n
FROM tables [WHERE conditions];
D.) MINUS:
This operator is used to select all distinct rows by irst query and not by second query.
Example Query:
SELECT expression1, expression2, … expression_n
FROM tables [WHERE conditions]
MINUS
SELECT expression1, expression2, … expression_n
FROM tables [WHERE conditions];

JOINS:

The joins in SQL are used to fetch data from two or more tables that are combined to appear as a single set of data and works by combining columns from multiple tables by using values ​​common to these tables. Also, sometimes a table can join with itself as a Self join and is explained as below with other possible joins.
A.) Self Join:
This joins the table with itself as if the table were two tables and temporarily renaming at least one table in the SQL query.
Example Query:
SELECT A.ColumnName AS ColumnName1, B.ColumnName AS ColumnName2, A.ColumnName3
FROM Table A, Table B WHERE A.ColumnName4 <> B.ColumnName4
AND A.ColumnName2 = B.ColumnName2 ORDER BY A.ColumnName2;
B.) Inner Join:
This join also known as EquiJoin selects records that have matching values ​​in both tables by creating a new result set on the combination of column values ​​of two tables based on the column on which join is applied.
Example Query:
SELECT column_name (s) FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name.
C.) Left Join:
This join returns all records from the left table and matching records from the right table and the result is NULL from right side in case there is no matching record.
Example Query:
SELECT column_name (s) FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name.
D.) Right Join:
This joins all the records from right table and matching records from left table while the result is NULL from the left side.
Example Query:
SELECT column_name (s) FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name.
E.) Full Join:
This join returns all the records if there is a match found in any of the table i.e. Left or right table.
Example Query:
SELECT column_name (s) FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name.
F.) Cross Join or Cartesian Product:
This join returns all the rows in all the tables mentioned in query where each row is paired with all the rows in the second table and this happens when there is no relationship defined between two tables.
Example Query:
SELECT column-name-list from table-name1
CROSS JOIN table-name2.

Sub Query:

A Sub-query is a SQL query that is nested inside another SELECT, UPDATE, INSERT, or DELETE queries or any other sub query. A sub query may exist in a SELECT, FROM, WHERE clause. The query containing the sub query is known as Outer query, and the execution of sub query occurs first and the result is passed to the outer query.
Example Query:
Sub Query Inside SELECT:
SELECT columnName1 (SELECT columnName2 FROM table1) FROM table2.
Sub Query Inside WHERE:
SELECT column-names FROM table-name1
WHERE value IN (SELECT column name FROM table-name2 WHERE columnName1 = expression)
Sub Query Inside FROM:
SELECT col1, col2, col3
FROM (SELECT c1 AS col1, c2 AS col2, c3 AS col3 from table1) AS aliasTableName
WHERE col1> expression
Similarly, the sub queries can be used with INSERT, UPDATE, DELETE queries as well.

Leave a Reply

Your email address will not be published. Required fields are marked *