SQL Operators

SQL Arithmetic Operators

Operator	Description	Example
+	Add	Try it
-	Subtract	Try it
*	Multiply	Try it
/	Divide	Try it
%	Modulo	Try it

---

SQL Bitwise Operators

Operator	Description
&	Bitwise AND
|	Bitwise OR
^	Bitwise exclusive OR

---

SQL Comparison Operators

Operator	Description	Example
=	Equal to	Try it
>	Greater than	Try it
<	Less than	Try it
>=	Greater than or equal to	Try it
<=	Less than or equal to	Try it
<>	Not equal to	Try it

SQL Compound Operators

Operator	Description
+=	Add equals
-=	Subtract equals
*=	Multiply equals
/=	Divide equals
%=	Modulo equals
&=	Bitwise AND equals
^-=	Bitwise exclusive equals
|*=	Bitwise OR equals

---

SQL Logical Operators

Operator	Description	Example
ALL	TRUE if all of the subquery values meet the condition	Try it
AND	TRUE if all the conditions separated by AND is TRUE	Try it
ANY	TRUE if any of the subquery values meet the condition	Try it
BETWEEN	TRUE if the operand is within the range of comparisons	Try it
EXISTS	TRUE if the subquery returns one or more records	Try it
IN	TRUE if the operand is equal to one of a list of expressions	Try it
LIKE	TRUE if the operand matches a pattern	Try it
NOT	Displays a record if the condition(s) is NOT TRUE	Try it
OR	TRUE if any of the conditions separated by OR is TRUE	Try it
SOME	TRUE if any of the subquery values meet the condition	Try it

Read More

SQL Operators

SQL Arithmetic Operators

Operator	Description	Example
+	Add	Try it
-	Subtract	Try it
*	Multiply	Try it
/	Divide	Try it
%	Modulo	Try it

---

SQL Bitwise Operators

Operator	Description
&	Bitwise AND
|	Bitwise OR
^	Bitwise exclusive OR

---

SQL Comparison Operators

Operator	Description	Example
=	Equal to	Try it
>	Greater than	Try it
<	Less than	Try it
>=	Greater than or equal to	Try it
<=	Less than or equal to	Try it
<>	Not equal to	Try it

SQL Compound Operators

Operator	Description
+=	Add equals
-=	Subtract equals
*=	Multiply equals
/=	Divide equals
%=	Modulo equals
&=	Bitwise AND equals
^-=	Bitwise exclusive equals
|*=	Bitwise OR equals

---

SQL Logical Operators

Operator	Description	Example
ALL	TRUE if all of the subquery values meet the condition	Try it
AND	TRUE if all the conditions separated by AND is TRUE	Try it
ANY	TRUE if any of the subquery values meet the condition	Try it
BETWEEN	TRUE if the operand is within the range of comparisons	Try it
EXISTS	TRUE if the subquery returns one or more records	Try it
IN	TRUE if the operand is equal to one of a list of expressions	Try it
LIKE	TRUE if the operand matches a pattern	Try it
NOT	Displays a record if the condition(s) is NOT TRUE	Try it
OR	TRUE if any of the conditions separated by OR is TRUE	Try it
SOME	TRUE if any of the subquery values meet the condition	Try it

Read More

SQL Comments

SQL Comments

Comments are used to explain sections of SQL statements, or to prevent execution of SQL statements.

Note: The examples in this chapter will not work in Firefox and Microsoft Edge!

Comments are not supported in Microsoft Access databases. Firefox and Microsoft Edge are using Microsoft Access database in our examples.

---

Single Line Comments

Single line comments start with --.

Any text between -- and the end of the line will be ignored (will not be executed).

The following example uses a single-line comment as an explanation:

Example

--Select all:
SELECT * FROM Customers;

Try it Yourself »

The following example uses a single-line comment to ignore the end of a line:

Example

SELECT * FROM Customers -- WHERE City='Berlin';

Try it Yourself »

The following example uses a single-line comment to ignore a statement:

Example

--SELECT * FROM Customers;
SELECT * FROM Products;

Try it Yourself »

Multi-line Comments

Multi-line comments start with /* and end with */.

Any text between /* and */ will be ignored.

The following example uses a multi-line comment as an explanation:

Example

/*Select all the columns
of all the records
in the Customers table:*/
SELECT * FROM Customers;

Try it Yourself »

The following example uses a multi-line comment to ignore many statements:

Example

/*SELECT * FROM Customers;
SELECT * FROM Products;
SELECT * FROM Orders;
SELECT * FROM Categories;*/
SELECT * FROM Suppliers;

Try it Yourself »

To ignore just a part of a statement, also use the /* */ comment.

The following example uses a comment to ignore part of a line:

Example

SELECT CustomerName, /*City,*/ Country FROM Customers;

Try it Yourself »

The following example uses a comment to ignore part of a statement:

Example

SELECT * FROM Customers WHERE (CustomerName LIKE 'L%'
OR CustomerName LIKE 'R%' /*OR CustomerName LIKE 'S%'
OR CustomerName LIKE 'T%'*/ OR CustomerName LIKE 'W%')
AND Country='USA'
ORDER BY CustomerName;

Try it Yourself »

Read More

SQL Comments

SQL Comments

Comments are used to explain sections of SQL statements, or to prevent execution of SQL statements.

Note: The examples in this chapter will not work in Firefox and Microsoft Edge!

Comments are not supported in Microsoft Access databases. Firefox and Microsoft Edge are using Microsoft Access database in our examples.

---

Single Line Comments

Single line comments start with --.

Any text between -- and the end of the line will be ignored (will not be executed).

The following example uses a single-line comment as an explanation:

Example

--Select all:
SELECT * FROM Customers;

Try it Yourself »

The following example uses a single-line comment to ignore the end of a line:

Example

SELECT * FROM Customers -- WHERE City='Berlin';

Try it Yourself »

The following example uses a single-line comment to ignore a statement:

Example

--SELECT * FROM Customers;
SELECT * FROM Products;

Try it Yourself »

Multi-line Comments

Multi-line comments start with /* and end with */.

Any text between /* and */ will be ignored.

The following example uses a multi-line comment as an explanation:

Example

/*Select all the columns
of all the records
in the Customers table:*/
SELECT * FROM Customers;

Try it Yourself »

The following example uses a multi-line comment to ignore many statements:

Example

/*SELECT * FROM Customers;
SELECT * FROM Products;
SELECT * FROM Orders;
SELECT * FROM Categories;*/
SELECT * FROM Suppliers;

Try it Yourself »

To ignore just a part of a statement, also use the /* */ comment.

The following example uses a comment to ignore part of a line:

Example

SELECT CustomerName, /*City,*/ Country FROM Customers;

Try it Yourself »

The following example uses a comment to ignore part of a statement:

Example

SELECT * FROM Customers WHERE (CustomerName LIKE 'L%'
OR CustomerName LIKE 'R%' /*OR CustomerName LIKE 'S%'
OR CustomerName LIKE 'T%'*/ OR CustomerName LIKE 'W%')
AND Country='USA'
ORDER BY CustomerName;

Try it Yourself »

Read More

SQL Stored Procedures for SQL Server

What is a Stored Procedure?

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again.

So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it.

You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

Stored Procedure Syntax

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

Execute a Stored Procedure

EXEC procedure_name;

---

Demo Database

Below is a selection from the "Customers" table in the Northwind sample database:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1	Alfreds Futterkiste	Maria Anders	Obere Str. 57	Berlin	12209	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Avda. de la Constitución 2222	México D.F.	05021	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mataderos 2312	México D.F.	05023	Mexico
4	Around the Horn	Thomas Hardy	120 Hanover Sq.	London	WA1 1DP	UK
5	Berglunds snabbköp	Christina Berglund	Berguvsvägen 8	Luleå	S-958 22	Sweden

---

Stored Procedure Example

The following SQL statement creates a stored procedure named "SelectAllCustomers" that selects all records from the "Customers" table:

Example

CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers
GO;

Execute the stored procedure above as follows:

Example

EXEC SelectAllCustomers;

Stored Procedure With One Parameter

The following SQL statement creates a stored procedure that selects Customers from a particular City from the "Customers" table:

Example

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30)
AS
SELECT * FROM Customers WHERE City = @City
GO;

Execute the stored procedure above as follows:

Example

EXEC SelectAllCustomers @City = 'London';

---

Stored Procedure With Multiple Parameters

Setting up multiple parameters is very easy. Just list each parameter and the data type separated by a comma as shown below.

The following SQL statement creates a stored procedure that selects Customers from a particular City with a particular PostalCode from the "Customers" table:

Example

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)
AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;

Execute the stored procedure above as follows:

Example

EXEC SelectAllCustomers @City = 'London', @PostalCode = 'WA1 1DP';

Read More