From Clomosy Docs

Revision as of 07:55, 2 January 2023 by ClomosyManager (talk | contribs)

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:

SELECT column1, column2....columnN

FROM   table_name;

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.

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. 

SELECT column1, column2....columnN

FROM   table_name

WHERE  CONDITION;

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. 

SELECT column1, column2....columnN

FROM   table_name

WHERE  CONDITION-1 {AND|OR} CONDITION-2;

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. 

SELECT column1, column2....columnN

FROM   table_name

WHERE  column_name IN (val-1, val-2,...val-N);

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. 

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

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.
Retrieved from "http:///index.php?title=SQL_Language&oldid=146"