SQL

IN operator is used to reduce use of multiple OR conditions by specifying multiple values in WHERE clause. Syntax of IN operator in SQL – SELECT(ColumnName(s)) FROM <TableName> WHERE<ColumnName> IN (value1, value2…valueN) ;    Example of IN operator in SQL- SELECT * FROM Customers WHERE City IN (‘Berlin’,’London’,’Madrid’);  –It will select all the customers from any of the cities Berlin, London and…

BETWEEN operator fetches the records from the table within the range specified in the query. Between Operator includes the starting and ending values of the specified range. Syntax of BETWEEN in SQL- SELECT(ColumnName(s)) FROM <TableName> WHERE<ColumnName> BETWEEN value1 AND value2;   Example of BETWEEN in SQL- SELECT * FROM Products WHERE Price BETWEEN 18 AND 22;  –It will select all the products with a price between…

BACKUP DATABASE is used to create full backup of an existing SQL SERVER database. Syntax of BACKUP DATABASE in SQL – BACKUP DATABASE <database_name> TO DISK = ‘filepath’; Example of BACKUP DATABASE in SQL- BACKUP DATABASE  Northwind TO DISK = ‘C:\backups\Northwind.bak’; –This SQL statement will create a full back up of the existng database “Northwind” to C: drive. BACKUP DATABASE…

CONSTRAINTS are used to create rules for data in a table to limit the type of data that can be stored into a table, Constraints can be created when the table is created with the CREATE TABLE statement , or after the table is created with the ALTER TABLE statement in SQL. Syntax of CONSTRAINTS in…

JOIN clause combine rows from two or more tables, based on a common field between them. There are five types of JOIN, defined by ANSI standard SQL– Sr. JOIN TYPES USAGE 1 INNER JOIN Returns records that have matching values in both tables 2 LEFT OUTER JOIN Returns all records from left table and matching records from right table…

INNER JOIN clause select all the records from both the tables when there are the values matching between the specified columns. Syntax of INNER JOIN in SQL- SELECT <column_name(s)> FROM <table1> INNER JOIN<table2> ON <table1.column_name=table2.column_name> WHERE<condition>; Example of INNER JOIN in SQL- SELECTOrders.OrderID, Customers.CustomerName FROMOrders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID; –Records in the Orders table that don’t have matches in…

LEFT JOIN/LEFT OUTER JOIN clause select all the records from left table(table1) and the matching records from the right table(table2). Syntax of LEFT JOIN in SQL- SELECT <column_name(s)> FROM <table1> LEFT JOIN<table2> ON <table1.column_name=table2.column_name> WHERE<condition>; Example of LEFT JOIN in SQL- SELECT Customers.CustomerName, Orders.OrderID FROMCustomers LEFT JOIN Orders ONOrders.CustomerID = Customers.CustomerID; LEFT JOIN returns all records from the left table (Customers),…

RIGHT JOIN/RIGHT OUTER JOIN clause select all the records from right table(table2) and the matching records from the left table(table1). Syntax of RIGHT JOIN in SQL- SELECT <column_name(s)> FROM <table1> RIGHT JOIN<table2> ON <table1.column_name=table2.column_name>WHERE<condition>; Example of RIGHT JOIN in SQL- SELECT Customers.CustomerName, Orders.OrderID FROMCustomers RIGHT JOIN Orders ONOrders.CustomerID = Customers.CustomerID; RIGHT JOIN returns all records from the right table (Orders), even…

FULL JOIN/FULL OUTER JOIN clause returns all the records when there is a match in left(table1) or right(table2) Syntax of FULL OUTER JOIN in SQL- SELECT <column_name(s)> FROM <table1> FULL OUTER JOIN<table2> ON <table1.column_name=table2.column_name> WHERE<condition>; Example of FULL OUTER JOIN in SQL- SELECT Customers.CustomerName, Orders.OrderID FROMCustomers FULL OUTER JOIN Orders ONOrders.CustomerID = Customers.CustomerID; FULL OUTER JOIN returns all records from both…

SELF JOIN is a regular join used to join the table with itself. Syntax of SELF JOIN in SQL- SELECT <column_name(s)> FROM <table1> t1, <table1>t2 WHERE<condition>; Example of SELF JOIN in SQL- SELECT A.CustomerName AS Customer1, B.CustomerName AS Customer2, A.CityFROM Customers A, Customers BWHERE A.CustomerID <> B.CustomerIDAND A.City = B. City; — SELF JOIN query customers that are from the same city.

GROUP BY clause in SQL statements is used to group the result-set by one or more columns that have the same values. It is often used with aggregate functions like SUM, AVG, MIN, MAX & COUNT. GROUP BY clause is placed after WHERE clause. Syntax of GROUP BY clause in SQL- SELECT <column_name(s)> FROM <table_name> WHERE<condition> GROUP BY<column_name(s)>;…

HAVING statement is used to filter the groups in result-set based on condition. HAVING clause is used after the GROUP BY clause. HAVING clause can include SQL aggregate functions in a query. Syntax of HAVING Clause in SQL- SELECT <column_name(s)> FROM <table_name> WHERE<condition> GROUP BY<column_name(s)> HAVING <condition>; Example of HAVING Clause in SQL- SELECT COUNT (CustomerID), Country FROM Customers GROUP BY Country…

EXISTS  is a boolean operator which returns TRUE if the subquery returns single or multiple records else it gives a false result when no records are returned. The EXISTS operator when detects the first true event, it automatically terminates for further processing.  Syntax of EXISTS operator in SQL- SELECT <column_name(s)> FROM <table_name> WHERE EXISTS (SELECT <column_name(s)> FROM <table_name> WHERE <condition>); Example of EXISTS operator in SQL-…

ANY and ALL operator is used to perform a comparison between a single column value and a range of other values. Both return a boolean value as a result. ANY returns TRUE if any of the subquery values meet the condition and ALL returns TRUE if all of the subquery values meet the condition. Syntax…

SELECT INTO statement is used to copy data from one table into a new table. Syntax of SELECT INTO in SQL- SELECT [* | <column_name(s)>] INTO <newtable> [IN <external_database>]FROM <Oldtable_name> WHERE <condition>; Example of SELECT INTO in SQL- SELECT * INTO CustomersBackup FROM Customers; –This SQL statement will create a copy of the Customers table. SELECT CustomerName, ContactName INTO CustomersBackup FROM Customers; –This SQL statement will copy only a few…

CASE statement is used to return the value if the specified condition evaluates to True, else it returns the value of the ELSE part. When there is no ELSE block and no condition evaluates to True, it returns a NULL value. Syntax of CASE in SQL- CASE WHEN<condition1> THEN <result1>……. WHEN<conditionN> THEN<resultN> ELSE<result> END; Example of CASE in SQL-It will returns…

ISNULL(), SQL Server function returns an alternative value when an expression is NULL. Syntax of ISNULL() in SQL- ISNULL(<column_name>,<alternative_value>) Example of ISNULL() in SQL Server query- SELECT ProductName, (UnitsInStock + ISNULL(UnitsOnOrder,0) FROM Products; –It will return an alternative value ‘0’ when an expression is NULL

Stored Procedure in SQL is a group of SQL Statements stored in the database for performing a particular task. Syntax of stored procedure in SQL- CREATE PROCEDURE <Procedure_Name> AS <SQL_statements> GO; Syntax to execute stored procedure in SQL- EXEC Procedure_Name; Syntax of Stored Procedure with Parameter in SQL- CREATE PROCEDURE <Procedure_Name> @<Parameter_Name1> <datatype (size)>…….. @<Parameter_NameN> <datatype (size)> AS <SQL_statements> GO; Syntax to execute…

A VIEW in SQL is a virtual table showing up-to-date result-set of SQL statements.Unlike a real table, a view does not exist in the database as astored set of data values. Instead, the rows and columns of data visible through the view arethe query results produced by the query that defines the view. HOW TO CREATE VIEW…

The CHECK constraint in SQL is used to limit the value that can be placed in a column or table. Example of CHECK constraint in SQL server- CREATE TABLE Students (ID INT NOT NULL, FirstName VARCHAR(200),Fees INT CHECK (Fees>0)); Naming of CHECK constraint in SQL server during creation of table- CREATE TABLE Students (ID INT NOT NULL, FirstName VARCHAR(200), Fees INT , Age INT , CONSTRAINT chk CHECK (Fees>0 AND Age>21)); …