SQL Language

SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in a relational database.

SQL is the standard language for Relational Database System. All the Relational Database Management Systems (RDMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres and SQL Server use SQL as their standard database language.
SQL is widely popular because it offers the following advantages:

• Allows users to access data in the relational database management systems.

• Allows users to describe the data.

• Allows users to define the data in a database and manipulate that data.

• Allows to embed within other languages using SQL modules, libraries & pre-compilers.

• Allows users to create and drop databases and tables.

• Allows users to create view, stored procedure, functions in a database.

• Allows users to set permissions on tables, procedures and views.

When you are executing an SQL command for any RDBMS, the system determines the best way to carry out your request and SQL engine figures out how to interpret the task.
There are various components included in this process.
These components are:

• Query Dispatcher
• Optimization Engines
• Classic Query Engine
• SQL Query Engine, etc.

A classic query engine handles all the non-SQL queries, but a SQL query engine won't handle logical files.
The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE and DROP. These commands can be classified into the following groups based on their nature:br>

DDL - Data Definition Language

Command	Description
CREATE	Creates a new table, a view of a table, or other object in the database.
ALTER	Modifies an existing database object, such as a table.
DROP	Deletes an entire table, a view of a table or other objects in the database.

DML - Data Manipulation Language

Command	Description
SELECT	Retrieves certain records from one or more tables.
INSERT	Creates a record.
UPDATE	Modifies records.
DELETE	Deletes records.

SQL-Syntax

SQL is followed by a unique set of rules and guidelines called Syntax. This tutorial gives you a quick start with SQL by listing all the basic SQL Syntax. All the SQL statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW and all the statements end with a semicolon (;). The most important point to be noted here is that SQL is case insensitive, which means SELECT and select have same meaning in SQL statements.

All the examples given in this tutorial have been tested with a Clomosy server.

SQL SELECT Statement

---

Retrieves rows from the database and enables the selection of one or many rows or columns from one or many tables in SQL Server. The full syntax of the SELECT statement is complex, but the main clauses can be summarized as:
If we write "SELECT * FROM tableName" , it returns all the columns in the desired table.
Syntax:

SELECT column1, column2, ...., columnN FROM   table_name;

Sample:

SQL DISTINCT Statement

---

The Distinct statement allows the repetitive data in certain columns of the table to be retrieved as one. This operation allows to bring the data in the desired condition in cases where there is too much data in the table.
Syntax:

SELECT DISTINCT column1, column2....columnN FROM table_name;

SQL WHERE Clause

---

Defines the condition to be met for the rows to be returned. There is no limit to the number of predicates that can be included in a search condition.
Syntax:

SELECT column1, column2....columnN FROM  table_name WHERE  CONDITION;

Sample:
In the Products table, fetch those with productPrice 700.

SQL AND/OR Clause

---

AND: Combines two Boolean expressions and returns TRUE when both expressions are TRUE. When more than one logical operator is used in a statement, the AND operators are evaluated first. You can change the order of evaluation by using parentheses.
OR: Combines two conditions. When more than one logical operator is used in a statement, OR operators are evaluated after AND operators. However, you can change the order of evaluation by using parentheses. OR returns TRUE when either of the conditions is TRUE.
Syntax:

SELECT column1, column2....columnN FROM table_name WHERE  CONDITION-1 {AND|OR} CONDITION-2;

Sample:
We said to fetch the productName iPhone 11 or productPrice 700 from all the data in the Products table. He brought those who met both of these conditions. If we used the "and" operator, it would return empty.

SQL IN Clause

---

The IN operator allows you to specify multiple values in a WHERE clause. The IN operator is a shorthand for multiple OR conditions.
Syntax:

SELECT column1, column2....columnN FROM   table_name WHERE  column_name IN (val-1, val-2,...val-N);

Sample:
Bring the ones with the price 7000 or 5539.

SQL BETWEEN Clause

---

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included.
Syntax:

SELECT column1, column2....columnN FROM table_name WHERE  column_name BETWEEN val-1 AND val-2;

SQL LIKE Clause

---

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator:

• The percent sign (%) represents zero, one, or multiple characters
• The underscore sign (_) represents one, single character
  

Syntax:

SELECT column1, column2....columnN FROM table_name WHERE column_name LIKE { PATTERN };

Here are some examples showing different LIKE operators with '%' and '_' wildcards:

LIKE Operator	Description
WHERE CustomerName LIKE 'a%'	Finds any values that start with "a"
WHERE CustomerName LIKE '%a'	Finds any values that end with "a"
WHERE CustomerName LIKE '%or%'	Finds any values that have "or" in any position
WHERE CustomerName LIKE '_r%'	Finds any values that have "r" in the second position
WHERE CustomerName LIKE 'a_%'	Finds any values that start with "a" and are at least 2 characters in length
WHERE CustomerName LIKE 'a__%'	Finds any values that start with "a" and are at least 3 characters in length
WHERE ContactName LIKE 'a%o'	Finds any values that start with "a" and ends with "o"

SQL ORDER BY Clause

---

The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some databases sort the query results in an ascending order by default.

• The ASC command is used to sort the data returned in ascending order.
• The DESC command is used to sort the returned data in descending order.
  

Syntax:

SELECT column1, column2....columnN FROM table_name WHERE CONDITION ORDER BY column_name {ASC|DESC};

SQL GROUP BY Clause

---

The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups. This GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.
Syntax:

SELECT SUM(column_name) FROM table_name WHERE CONDITION GROUP BY column_name;

SQL - Operators

An operator is a reserved word or a character used primarily in an SQL statement's WHERE clause to perform operation(s), such as comparisons and arithmetic operations. These Operators are used to specify conditions in an SQL statement and to serve as conjunctions for multiple conditions in a statement.

• Arithmetic operators
• Comparison operators
• Logical operators

SQL Arithmetic Operators

---

Assume 'variable a' holds 10 and 'variable b' holds 20, then:

Operator	Description	Example
+ (Addition)	Adds values on either side of the operator.	a + b will give 30
- (Subtraction)	Subtracts right hand operand from left hand operand.	a - b will give -10
* (Multiplication)	Multiplies values on either side of the operator.	a * b will give 200
/ (Division)	Divides left hand operand by right hand operand.	b / a will give 2
% (Modulus)	Divides left hand operand by right hand operand and returns remainder.	b % a will give 0

SQL Comparison Operators

---

Assume 'variable a' holds 10 and 'variable b' holds 20, then:

Operator	Description	Example
=	Checks if the values of two operands are equal or not, if yes then condition becomes true.	(a = b) is not true.
!=	Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.	(a != b) is true.
<>	Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.	(a <> b) is true.
>	Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true.	(a > b) is not true.
<	Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true.	(a < b) is true.
>=	Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true.	(a >= b) is not true.
<=	Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true.	(a <= b) is true.
!<	Checks if the value of left operand is not less than the value of right operand, if yes then condition becomes true.	(a !< b) is false.
!>	Checks if the value of left operand is not greater than the value of right operand, if yes then condition becomes true.	(a !> b) is true.

Language Elements

Control-of-Flow

---

BEGIN...END

---

Encloses a series of Transact-SQL statements so that a group of Transact-SQL statements can be executed. BEGIN and END are control-of-flow language keywords.
Syntax:

BEGIN 

   { sql_statement | statement_block }   

END

BREAK

---

BREAK exits the current WHILE loop. If the current WHILE loop is nested inside another, BREAK exits only the current loop, and control is given to the next statement in the outer loop. BREAK is usually inside an IF statement.
Syntax:

BEGIN
   -- statement
   WHILE Boolean_expression2
   BEGIN
       IF condition
           BREAK;
   END
END

CONTINUE

---

Restarts a WHILE loop. Any statements after the CONTINUE keyword are ignored. CONTINUE is frequently, but not always, opened by an IF test.

ELSE(IF...ELSE)

---

Applies conditions for the execution of a statement. SQL statement (sql_statement) following the Boolean_expressionis executed if the Boolean_expression evaluates to TRUE. The optional ELSE keyword is an alternate SQL statement that is executed when Boolean_expression evaluates to FALSE or NULL.
Syntax:

IF Boolean_expression   
    { sql_statement | statement_block }   
[ ELSE   
    { sql_statement | statement_block } ]

GOTO

---

Alters the flow of execution to a label. The SQL statement or statements that follow GOTO are skipped and processing continues at the label. GOTO statements and labels can be used anywhere within a procedure, batch, or statement block. GOTO statements can be nested.
Syntax:

Define the label:   
label:   
Alter the execution:  
GOTO label

RETURN

---

Exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch, or statement block. Statements that follow RETURN are not executed.
Syntax:

RETURN [ integer_expression ] 

TRY...CATCH

---

Implements error handling for Transact-SQL that is similar to the exception handling in the Microsoft Visual C# and Microsoft Visual C++ languages. A group of Transact-SQL statements can be enclosed in a TRY block. If an error occurs in the TRY block, control is usually passed to another group of statements that is enclosed in a CATCH block.
Syntax:

BEGIN TRY  
    { sql_statement | statement_block }  
END TRY  
BEGIN CATCH  
    [ { sql_statement | statement_block } ]  
END CATCH  
[ ; ]

Expressions

---

Is a combination of symbols and operators that the SQL Server Database Engine evaluates to obtain a single data value. Simple expressions can be a single constant, variable, column, or scalar function. Operators can be used to join two or more simple expressions into a complex expression.

CASE

---

Evaluates a list of conditions and returns one of multiple possible result expressions.
The CASE expression has two formats:

• The simple CASE expression compares an expression to a set of simple expressions to determine the result.

• The searched CASE expression evaluates a set of Boolean expressions to determine the result.

Both formats support an optional ELSE argument.
CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in statements such as SELECT, UPDATE, DELETE and SET, and in clauses such as select_list, IN, WHERE, ORDER BY, and HAVING. Syntax:

--Simple CASE expression:   
CASE input_expression   
    WHEN when_expression THEN result_expression [ ...n ]   
    [ ELSE else_result_expression ]   
END 
  
--Searched CASE expression:  
CASE  
    WHEN Boolean_expression THEN result_expression [ ...n ]   
    [ ELSE else_result_expression ]   
END

NULLIF

---

Returns a null value if the two specified expressions are equal. For example, SELECT NULLIF(4,4) AS Same, NULLIF(5,7) AS Different; returns NULL for the first column (4 and 4) because the two input values are the same. The second column returns the first value (5) because the two input values are different.
Syntax:

NULLIF ( expression , expression )

Functions

Date & Time

---

DATEADD

---

This function adds a number (a signed integer) to a datepart of an input date, and returns a modified date/time value. For example, you can use this function to find the date that is 7000 minutes from today: number = 7000, datepart = minute, date = today.
Syntax:

DATEADD (datepart , number , date )

datepart;
The part of date to which DATEADD adds an integer number. This table lists all valid datepart arguments.

datepart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

number;
An expression that can resolve to an int that DATEADD adds to a datepart of date. DATEADD accepts user-defined variable values for number. DATEADD will truncate a specified number value that has a decimal fraction. It will not round the number value in this situation.
date;
An expression that can resolve to one of the following values:

• date
• datetime
• datetimeoffset
• datetime2
• smalldatetime
• time

For date, DATEADD will accept a column expression, expression, string literal, or user-defined variable. A string literal value must resolve to a datetime. Use four-digit years to avoid ambiguity issues.

DATEDIFF

---

This function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate.
Syntax:

DATEDIFF ( datepart , startdate , enddate ) 

DAY

---

This function returns an integer that represents the day (day of the month) of the specified date.
Syntax:

DAY ( date )

GETDATE

---

Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running.
Syntax:

GETDATE()

MONTH

---

Returns an integer that represents the month of the specified date.
Syntax:

MONTH ( date )

SYSDATETIME

---

Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running.
Syntax:

SYSDATETIME ( ) 

YEAR

---

Returns an integer that represents the year of the specified date.
Syntax:

YEAR( date )

ISNULL

---

Replaces NULL with the specified replacement value.
Syntax:

ISNULL ( check_expression , replacement_value )

check_expression;
The expression is to be checked for NULL. check_expression can be of any type.

replacement_value ;
Is the expression to be returned if check_expression is NULL. replacement_value must be of a type that is implicitly convertible to the type of check_expression.

Click if you want to access more information.