Saraswat World

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)); …

The NOT NULL constraint in SQL is used to disallow the acceptance of NULL value in a column(s). A column, by default, can hold NULL values. Example of NOT NULL constraint in SQL server- CREATE TABLE Students (   ID INT NOT NULL,   FirstName VARCHAR(200) NOT NULL,   Age INT); NOT NULL constraint creation on existing table- ALTER TABLE Students ALTER COLUMN Age INT…

The UNIQUE constraint in SQL is used to ensure that all values in a column are unique or different. Example of UNIQUE constraint in SQL server- CREATE TABLE Students (ID INT NOT NULL UNIQUE, FirstName VARCHAR(200),Fees INT); Naming of UNIQUE constraint in SQL server during creation of table- CREATE TABLE Students (ID INT NOT NULL, FirstName VARCHAR(200) NOT NULL, Fees INT , Age INT , CONSTRAINT unk UNIQUE (ID, FirstName));  –constraint named as unk…

The aliases in SQL are used to give temporary name to a table or a column in a table. An alias is created with the AS keyword. Syntax of alias in SQL for the column- SELECT <column_name1> AS <alias_name1>, ….     <column_nameN> AS <alias_nameN> FROM <table_name>; Syntax of alias in SQL for table- SELECT<column_name(s)> FROM <table_name> AS <alias_name>; Example of alias in SQL for column- SELECT CustomerName AS Customer, PostalCode AS [Postal Code]…

The DEFAULT constraint in SQL is used to set a default value for a column. Example of DEFAULT constraint creation in SQL server- CREATE TABLE Students ( ID INT NOT NULL UNIQUE, FirstName VARCHAR(200), Fees INT, City VARCHAR(200) DEFAULT ‘Delhi’ ); Example to create DEFAULT constraint on existing table in SQL server- ALTER TABLE Students ADD  CONSTRAINT d_city DEFAULT ‘Delhi’ FOR City; Example to DROP a UNIQUE constraint in SQL server –…

The CREATE INDEX statement in SQL is used to create indexes in tables. Indexes are used for faster retrieval of records which speed up queries. Syntax of CREATE INDEX in SQL server. It allow duplicate values CREATE INDEX <index_name> ON <table_name> (column_name1..column_nameN); Syntax of CREATE UNIQUE INDEX in SQL server. It don’t allow duplicate values CREATE UNIQUE INDEX <index_name> ON <table_name> (column_name1..column_nameN); Example of…

UNION operator is used to select distinct values from the combination of the result-set of two or more SELECT statements, provided that the data type and the number of fields, and the order of fields must be the same for every SELECT statement. To allow duplicate values in the result set, use UNION ALL. Syntax of UNION…