From Clomosy Docs
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.
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. |
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:
SELECT * FROM tableName
Syntax:
SELECT column1, column2, ...., columnN FROM table_name;
Sample:
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;
Sample:
SELECT DISTINCT City FROM Customers;
WHERE
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.
SELECT * FROM Products WHERE productPrice= '700';
AND/OR
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.
SELECT * FROM Products WHERE productPrice= '7000' OR productname = 'iPhone 11';
IN
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.
SELECT * FROM Products WHERE productPrice IN (7000,5539);
BETWEEN
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;
Sample:
Bring the ones with prices between 4000-7000.
SELECT * FROM Products WHERE productPrice BETWEEN 4000 AND 7000;
LIKE
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" |
Sample:
In this example, we have performed the operation to fetch all the data that starts with the word Samsung in "productName". Other than that, you can try the data in other ways.
SELECT * FROM Products WHERE productName LIKE 'Samsung%';
ORDER BY
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};
Sample:
Sort the prices in descending order by saying DESC.
SELECT * FROM Products ORDER BY productPrice DESC;
GROUP BY
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.
The GROUP BY statement is used to divide the table or tables queried together into groups. Its general usage is as follows. The GROUP BY statement is used when calculating by groups, not sorting results like ORDER BY.
These grouped records;
- To find out how many (COUNT)
- Average value of prices within the group (AVG)
- Total (SUM) value
- We can perform operations such as the most expensive (MAX) and the cheapest (MIN) within groups.
Functions such as COUNT, AVG, SUM, MAX, MIN used to perform these operations are called AGGRAGATE functions and are usually used with the GROUP BY statement.
NOTE: When grouping, field names written in select must be included in group by. We cannot perform SELECT * while grouping. However, we can perform calculations in the form of SELECT COUNT(*).
Syntax:
SELECT SUM(column_name) FROM table_name WHERE CONDITION GROUP BY column_name;
Sample:
SELECT SUM(productPrice) as Price FROM Products GROUP BY productPrice;
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
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 |
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.
Syntax:
BEGIN
-- statement
WHILE Boolean_expression2
BEGIN
IF condition
CONTINUE;
END
END
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.
Notice: Click if you want to access more information.