SQL – A beginner’s guide

Comments in SQL

There are two ways in which you can comment in SQL, i.e. either the Single-Line Comments or the Multi-Line Comments.

Single-Line Comments

The single line comment starts with two hyphens (–). So, any text mentioned after (–), till the end of a single line will be ignored by the compiler.

Multi-Line Comments 

The Multi-line comments start with /* and end with */. So, any text mentioned between /* and */ will be ignored by the compiler.

SQL Commands: Data Definition Language Commands (DDL)

This section of the article will give you an insight into the commands through which you can define your database. The commands are as follows

• • CREATE
  • DROP
  • TRUNCATE
  • ALTER
  • BACKUP DATABASE

CREATE

This statement is used to create a table or a database.

The ‘CREATE DATABASE’ Statement

As the name suggests, this statement is used to create a database.

Syntax

CREATE DATABASE DatabaseName;

 

The ‘CREATE TABLE’ Statement

This statement is used to create a table.

Syntax

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

The ‘CREATE TABLE AS’ Statement

Syntax

CREATE TABLE NewTableName ASSELECT Column1, column2,…, ColumnNFROM ExistingTableNameWHERE ….;

 

DROP

This statement is used to drop an existing table or a database.

The ‘DROP DATABASE’ Statement

This statement is used to drop an existing database. When you use this statement, complete information present in the database will be lost.

Syntax

DROP DATABASE DatabaseName;

The ‘DROP TABLE’ Statement

This statement is used to drop an existing table. When you use this statement, complete information present in the table will be lost.

Syntax

DROP TABLE TableName;

 

TRUNCATE

This command is used to delete the information present in the table but does not delete the table. So, once you use this command, your information will be lost, but not the table.

Syntax

TRUNCATE TABLE TableName;

 

ALTER

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

The ‘ALTER TABLE’ Statement

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

The ‘ALTER TABLE’ Statement with ADD/DROP COLUMN

You can use the ALTER TABLE statement with ADD/DROP Column command according to your need. If you wish to add a column, then you will use the ADD command, and if you wish to delete a column, then you will use the DROP COLUMN command.

Syntax

ALTER TABLE TableNameADD ColumnName Datatype; ALTER TABLE TableNameDROP COLUMN ColumnName;

The ‘ALTER TABLE’ Statement with ALTER/MODIFY COLUMN

This statement is used to change the datatype of an existing column in a table.

Syntax

ALTER TABLE TableNameALTER COLUMN ColumnName Datatype;

BACKUP DATABASE

This statement is used to create a full backup of an existing database.

Syntax

BACKUP DATABASE DatabaseNameTO DISK = ‘filepath’;

You can also use a differential back up. This type of back up only backs up the parts of the database, which have changed since the last complete backup of the database.

Syntax

BACKUP DATABASE DatabaseNameTO DISK = ‘filepath’WITH DIFFERENTIAL;

SQL Commands: Different Types Of Keys In Database

There are mainly 7 types of Keys, that can be considered in a database. I am going to consider the below tables to explain to you the various keys.

• 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.

SQL Commands: Constraints Used In Database

Constraints are used in a database to specify the rules for data in a table. The following are the different types of constraints:

• NOT NULL
• UNIQUE
• CHECK
• DEFAULT
• INDEX

NOT NULL

This constraint ensures that a column cannot have a NULL value.

UNIQUE

This constraint ensures that all the values in a column are unique.

CHECK

This constraint ensures that all the values in a column satisfy a specific condition.

DEFAULT

This constraint consists of a set of default values for a column when no value is specified.

INDEX

This constraint is used to create indexes in the table, through which you can create and retrieve data from the database very quickly.

Syntax

–Create an Index where duplicate values are allowedCREATE INDEX IndexNameON TableName (Column1, Column2, …ColumnN); –Create an Index where duplicate values are not allowedCREATE UNIQUE INDEX IndexNameON TableName (Column1, Column2, …ColumnN);

SQL Commands: Data Manipulation Language Commands (DML)

This section of the article will give you an insight into the commands through which you can manipulate the database. The commands are as follows

• • USE
  • INSERT INTO
  • UPDATE
  • DELETE
  • SELECT

Apart from these commands, there are also other manipulative operators/functions such as:

• • Operators
  • Aggregate Functions
  • NULL Functions
  • Aliases & Case Statement

USE

The USE statement is used to select the database on which you want to perform operations.

Syntax

USE DatabaseName;

INSERT INTO

This statement is used to insert new records into the table.

Syntax

INSERT INTO TableName (Column1, Column2, Column3, …,ColumnN)VALUES (value1, value2, value3, …); –If you don’t want to mention the column names then use the below syntax INSERT INTO TableNameVALUES (Value1, Value2, Value3, …);

UPDATE

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

Syntax

UPDATE TableNameSET Column1 = Value1, Column2 = Value2, …WHERE Condition;

 

DELETE

This statement is used to delete the existing records in a table.

Syntax

DELETE FROM TableName WHERE Condition;

SELECT

This statement is used to select data from a database and the data returned is stored in a result table, called the result-set.

Syntax

SELECT Column1, Column2, …ColumNFROM TableName; –(*) is used to select all from the tableSELECT * FROM table_name; — To select the number of records to return use:SELECT TOP 3 * FROM TableName;

Apart from just using the SELECT keyword individually, you can use the following keywords with the SELECT statement:

• • DISTINCT
  • ORDER BY
  • GROUP BY
  • HAVING Clause
  • INTO

The ‘SELECT DISTINCT’ Statement

This statement is used to return only different values.

Syntax

SELECT DISTINCT Column1, Column2, …ColumnNFROM TableName;

 

The ‘ORDER BY’ Statement

The ‘ORDER BY’ statement is used to sort the required results in ascending or descending order. The results are sorted in ascending order by default. Yet, if you wish to get the required results in descending order, you have to use the DESC keyword.

Syntax

SELECT Column1, Column2, …ColumnNFROM TableNameORDER BY Column1, Column2, … ASC|DESC;

The ‘GROUP BY’ Statement

This ‘GROUP BY’ statement is used with the aggregate functions to group the result-set by one or more columns.

Syntax

SELECT Column1, Column2,…, ColumnNFROM TableNameWHERE ConditionGROUP BY ColumnName(s)ORDER BY ColumnName(s);

The ‘HAVING’ Clause

The ‘HAVING’ clause is used in SQL because the WHERE keyword cannot be used everywhere.

Syntax

SELECT ColumnName(s)FROM TableNameWHERE ConditionGROUP BY ColumnName(s)HAVING ConditionORDER BY ColumnName(s);

The ‘SELECT INTO’ Statement

The ‘SELECT INTO’ statement is used to copy data from one table to another.

Syntax

SELECT *INTO NewTable [IN ExternalDB]FROM OldTableWHERE Condition;

Now, as I mentioned before, let us move onto our next section in this article on SQL Commands, i.e. the Operators.

Operators in SQL

The different set of operators available in SQL are as follows:

Let us look into each one of them, one by one.

Arithmetic Operators

Operator	Description
%	Modulous [A % B]
/	Division [A / B]
*	Multiplication [A * B]
–	Subtraction  [A – B]
+	Addition [A + B]

Bitwise Operators

Operator	Description
^	Bitwise Exclusive OR (XOR) [A ^ B]
|	Bitwise OR [A | B]
&	Bitwise AND [A & B]

Comparison Operators

Operator	Description
< >	Not Equal to [A < > B]
<=	Less than or equal to [A <= B]
>=	Greater than or equal to [A >= B]
<	Less than [A < B]
>	Greater than [A > B]
=	Equal to [A = B]

Compound Operators

Operator	Description
|*=	Bitwise OR equals [A |*= B]
^-=	Bitwise Exclusive equals [A ^-= B]
&=	Bitwise AND equals [A &= B]
%=	Modulo equals [A %= B]
/=	Divide equals [A /= B]
*=	Multiply equals [A*= B]
-=	Subtract equals [A-= B]
+=	Add equals [A+= B]

Logical Operators

The Logical operators present in SQL are as follows:

• • AND
  • OR
  • NOT
  • BETWEEN
  • LIKE
  • IN
  • EXISTS
  • ALL
  • ANY

AND Operator

This operator is used to filter records that rely on more than one condition. This operator displays the records, which satisfy all the conditions separated by AND, and give the output TRUE.

Syntax

SELECT Column1, Column2, …, ColumnNFROM TableNameWHERE Condition1 AND Condition2 AND Condition3 …;

OR Operator

This operator displays all those records which satisfy any of the conditions separated by OR and give the output TRUE.

Syntax

SELECT Column1, Column2, …, ColumnNFROM TableNameWHERE Condition1 OR Condition2 OR Condition3 …;

NOT Operator

The NOT operator is used, when you want to display the records which do not satisfy a condition.

Syntax

SELECT Column1, Column2, …, ColumnNFROM TableNameWHERE NOT Condition;

BETWEEN Operator

The BETWEEN operator is used, when you want to select values within a given range. Since this is an inclusive operator, both the starting and ending values are considered.

Syntax

SELECT ColumnName(s)FROM TableNameWHERE ColumnName BETWEEN Value1 AND Value2;

 

LIKE Operator

The LIKE operator is used in 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 TableNameWHERE ColumnName LIKE pattern;

Refer to the following table for the various patterns that you can mention with the LIKE operator.

Like Operator Condition	Description
WHERE CustomerName LIKE ‘v%	Finds any values that start with “v”
WHERE CustomerName LIKE ‘%v’	Finds any values that end with “v”
WHERE CustomerName LIKE ‘%and%’	Finds any values that have “and” in any position
WHERE CustomerName LIKE ‘_q%’	Finds any values that have “q” in the second position.
WHERE CustomerName LIKE ‘u_%_%’	Finds any values that start with “u” and are at least 3 characters in length
WHERE ContactName LIKE ‘m%a’	Finds any values that start with “m” and end with “a”

IN Operator

This operator is used for multiple OR conditions. This allows you to specify multiple values in a WHERE clause.

Syntax

SELECT ColumnName(s)FROM TableNameWHERE ColumnName IN (Value1,Value2…);

EXISTS Operator

The EXISTS operator is used to test if a record exists or not.

Syntax

SELECT ColumnName(s)FROM TableNameWHERE EXISTS(SELECT ColumnName FROM TableName WHERE condition);

ALL Operator

The ALL operator is used with a WHERE or HAVING clause and returns TRUE if all of the subquery values meet the condition.

Syntax

SELECT ColumnName(s)FROM TableNameWHERE ColumnName operator ALL(SELECT ColumnName FROM TableName WHERE condition);

ANY Operator

Similar to the ALL operator, the ANY operator is also used with a WHERE or HAVING clause and returns true if any of the subquery values meet the condition.

Syntax

SELECT ColumnName(s)FROM TableNameWHERE ColumnName operator ANY(SELECT ColumnName FROM TableName WHERE condition);

Next, in this article on SQL Commands, let us look into the various Aggregate Functions provided in SQL.

Aggregate Functions

This section of the article will include the following functions:

• • MIN()
  • MAX()
  • COUNT()
  • SUM()
  • AVG()

MIN() Function

The MIN function returns the smallest value of the selected column in a table.

Syntax

SELECT MIN(ColumnName)FROM TableNameWHERE Condition;

MAX() Function

The MAX function returns the largest value of the selected column in a table.

Syntax

SELECT MAX(ColumnName)FROM TableNameWHERE Condition;

COUNT() Function

The COUNT function returns the number of rows which match the specified criteria.

Syntax

SELECT COUNT(ColumnName)FROM TableNameWHERE Condition;

SUM() Function

The SUM function returns the total sum of a numeric column that you choose.

Syntax

SELECT SUM(ColumnName)FROM TableNameWHERE Condition;

AVG() Function

The AVG function returns the average value of a numeric column that you choose.

Syntax

SELECT AVG(ColumnName)FROM TableNameWHERE Condition;

NULL Functions

The NULL functions are those functions which let you return an alternative value if an expression is NULL. In the SQL Server, the function is ISNULL().

Aliases & Case Statement

In this section of this article on SQL Commands, you will go through the Aliases and Case statement one after the other.

Aliases

Aliases are used to give a column/table a temporary name and only exists for a duration of the query.

Syntax

–Alias Column Syntax SELECT ColumnName AS AliasNameFROM TableName; –Alias Table Syntax SELECT ColumnName(s)FROM TableName AS AliasName;

Case Statement

This statement goes through all the conditions and returns a value when the first condition is met. So, if no conditions are TRUE, it returns the value in the ELSE clause. Also, if no conditions are true and there is no ELSE part, then it returns NULL.

Syntax

CASEWHEN Condition1 THEN Result1WHEN Condition2 THEN Result2WHEN ConditionN THEN ResultNELSE ResultEND;

Now, that I have told you a lot about DML commands in this article on SQL Commands, let me just tell you in short about Nested Queries, Joins, Set Operations, and Dates & Auto Increment.

SQL Commands: Nested Queries

Nested queries are those queries which have an outer query and inner subquery. So, basically, the subquery is a query which is nested within another query such as SELECT, INSERT, UPDATE or DELETE.

SQL Commands: Joins

JOINS are used to combine rows from two or more tables, based on a related column between those tables. The following are the types of joins:

• INNER JOIN: This join returns those records which have matching values in both the tables.
• FULL JOIN: This 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.

INNER JOIN

Syntax

SELECT ColumnName(s)FROM Table1INNER JOIN Table2 ON Table1.ColumnName = Table2.ColumnName;

FULL JOIN

Syntax

SELECT ColumnName(s)FROM Table1FULL OUTER JOIN Table2 ON Table1.ColumnName = Table2.ColumnName;

LEFT JOIN

Syntax

SELECT ColumnName(s)FROM Table1LEFT JOIN Table2 ON Table1.ColumnName = Table2.ColumnName;

RIGHT JOIN

Syntax

SELECT ColumnName(s)FROM Table1RIGHT JOIN Table2 ON Table1.ColumnName = Table2.ColumnName;

SQL Commands: Set Operations

There are mainly three set operations: UNION, INTERSECT, EXCEPT. You can refer to the image below to understand the set operations in SQL.

UNION

This operator is used to combine the result-set of two or more SELECT statements.

Syntax

SELECT ColumnName(s) FROM Table1UNIONSELECT ColumnName(s) FROM Table2;

INTERSECT

This clause used to combine two SELECT statements and return the intersection of the data-sets of both the SELECT statements.

Syntax

SELECT Column1 , Column2 ….FROM TableNameWHERE Condition INTERSECT SELECT Column1 , Column2 ….FROM TableNameWHERE Condition

EXCEPT

This operator returns those tuples that are returned by the first SELECT operation, and are not returned by the second SELECT operation.

Syntax

SELECT ColumnNameFROM TableName EXCEPT SELECT ColumnNameFROM TableName;

Next, in this article, let us look into the date functions and auto-increment fields.

SQL Commands: Dates & Auto Increment

In this section of this article, I will explain to you how to use the Date functions and also the Auto-Increment fields.

Dates

The following data types are present in a SQL Server to store a date or a date/time value in a database.

Data Type	Format
DATE	YYYY-MM-DD
DATETIME	YYYY-MM-DD HH:MI:SS
SMALLDATETIME	YYYY-MM-DD HH:MI:SS
TIMESTAMP	A Unique Number

Auto Increment

This field generates a unique number automatically when a new record is inserted into a table. The MS SQL Server uses the IDENTITY keyword for this feature.

SQL Commands: Data Control Language Commands (DCL)

This section of the article will give you an insight into the commands which are used to enforce database security in multiple user database environments. The commands are as follows:

• • GRANT
  • REVOKE

GRANT

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

Syntax

GRANT PrivilegeNameON ObjectNameTO {UserName |PUBLIC |RoleName}[WITH GRANT OPTION];

where,

• PrivilegeName – Is the privilege/right/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.

REVOKE

This command is used to withdraw the user’s access privileges given by using the GRANT command.

Syntax

REVOKE PrivilegeName ON ObjectName FROM {UserName |PUBLIC |RoleName}

Now, next in this article on SQL Commands, I will discuss Views, Stored Procedures, and Triggers.

SQL Commands: Views

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

The ‘CREATE VIEW’ statement

This statement is used to create a view, from a table.

Syntax

CREATE VIEW ViewName ASSELECT Column1, Column2, …, ColumnNFROM TableNameWHERE Condition;

The ‘CREATE OR REPLACE VIEW’ statement

This statement is used to update a view.

Syntax

CREATE VIEW OR REPLACE ViewName ASSELECT Column1, Column2, …, ColumnNFROM TableNameWHERE Condition;

The ‘DROP VIEW’ statement

This statement is used to delete a view.

Syntax

DROP VIEW ViewName;

SQL Commands: Stored Procedures

A code which you can save and reuse it again is known as StoredProcedures.

Syntax

CREATE PROCEDURE ProcedureNameASSQLStatementGO;

EXEC ProcedureName;

SQL Commands: Triggers

Triggers are a set of  SQL statements which are stored in the database catalog. These statements are executed whenever an event associated with a table occurs. So, a trigger can be invoked either BEFORE or AFTER the data is changed by INSERT, UPDATE or DELETE statement. Refer to the image below.

Syntax

CREATE TRIGGER [TriggerName][BEFORE | AFTER]{INSERT | UPDATE | DELETE}on [TableName][FOR EACH ROW]

SQL Commands: Transaction Control Language Commands (TCL)

This section of the article will give you an insight into the commands which are used to manage transactions in the database. The commands are as follows:

• • COMMIT
  • ROLLBACK
  • SAVEPOINT

COMMIT

This command is used to save the transaction into the database.

Syntax

COMMIT;

ROLLBACK

This command is used to restore the database to the last committed state.

Syntax

ROLLBACK;

NOTE: When you use ROLLBACK with SAVEPOINT, then you can directly jump to a savepoint in an ongoing transaction. Syntax: ROLLBACK TO SavepointName;

SAVEPOINT

This command is used to temporarily save a transaction.  So if you wish to rollback to any point, then you can save that point as a ‘SAVEPOINT’.

Syntax

SAVEPOINT SAVEPOINTNAME;