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 […]

Window function is a function which uses values from one or multiple rows to return a value for each row. The term Window describes the set of rows in the database on which the function will operate using an OVER() clause. The main advantage of using Window functions over regular aggregate functions is- Window functions […]

CTE or Common Table Expression, is a named temporary result set that we can define and reference within the execution scope of single SELECT, INSERT, UPDATE, DELETE, MERGE or CREATE VIEW statement.A CTE is similar to a view, but it is defined within the execution of a single statement and lasts only for the duration […]

PIVOT and UNPIVOT are relational operators. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. And PIVOT runs aggregations where they’re required on any remaining column values that are wanted in the final output. UNPIVOT carries out the opposite operation to PIVOT […]

A situation where finally block will not be executed in Java- Finally block will not be executed whenever jvm shutdowns. If we use system.exit(0) in try statementfinally block if present will not be executed.

Importance of finally over return statement in Java- finally block is more important than return statement when both are present in a program. For example, if there is any return statement present inside try or catch block , and finally block is also present firstfinally statement will be executed and then return statement will be […]