TRIGGERS in SQL
TRIGGERS: Triggers are one of the database events, which performs their own operation when user performs any INSERT, UPDATE, DELETE operations on a specific table.
OR
Trigger is a type of stored procedure that implicitly executed when user performs DML operation on the table. It will not accept any parameters.
Types of Triggers
1)AFTER Trigger
2)INSTEAD OF Trigger
3)DDL Triggers
After Triggers:
These are the triggers, which performs their own operation after performing insert, delete, and update operations on a specific table.
Syntax:
CREATE TRIGGER TRIGGERNAME ON TABLE NAME
FOR/ AFTER {INSERT,/ UPDATE/ DELETE}
AS
BEGIN
SQL STATEMENT.
END
INSERT TRIGGER:
This trigger fires when user performs insert operation on the table. When user insert a record into the table the temporary table called inserted is created the newly inserted record is also stored in inserted table temporarily
DELETE TRIGGER:
This trigger fires when user performs delete operation on the table. When user delete a record from the table the temporary table called deleted is created the deleted record is also stored in deleted table temporarily
UPDATE TRIGGER:
This trigger fires when user performs update operation on the table. When user update a record into the table the temporary tables called inserted and deleted are created the new values placed into inserted table and old values will be placed in deleted table temporarily
Insert trigger example in SQL?
CREATE TRIGGER T1 ON DEPT FOR INSERT
AS
BEGIN
INSERT INTO DEPT1 SELECT * FROM INSERTED
END
The above trigger fires after performing INSERT operation on DEPT table. It will inserts the newly inserted records into DEPT1
Delete trigger example in SQL?
CREATE TRIGGER T2 ON DEPT FOR DELETE
AS
BEGIN
INSERT INTO DEPT2 SELECT * FROM DELETED
END
The above trigger fires after performing DELETE operation on DEPT table. It will inserts the deleted records into DEPT2
Update trigger example in SQL?
CREATE TRIGGER T3 ON DEPT FOR UPDATE
AS
BEGIN
INSERT INTO DEPTO SELECT * FROM DELETED
INSERT INTO DEPTN SELECT * FROM INSERTED
END
The above trigger fires after performing UPDATE operation on DEPT table. It will
Inserts the newly modified records into DEPTN and inserts old values into DEPTO.
Instead of Triggers:
These are the triggers, which performs their operations instead of performing user specified operations.
Syntax:
CREATE TRIGGER TRIGGERNAME ON TABLE NAME
INSTEAD OF {INSERT,/ UPDATE/ DELETE}
AS
BEGIN
SQL STATEMENT.
END
Instead of trigger example in SQL?
CREATE TRIGGER T4 ON DEPT INSTEAD OF INSERT,UPDATE, DELETE
AS
BEGIN
PRINT ‘THESE OPERATIONS ARE NOT ALLOWED’
END
The above trigger fires automatically and shows a message THESE OPERATIONS ARE NOT ALLOWED, when user try to perform INSERT, UPDATE, DELETE operations on DEPT table.
SP_HELPTRIGGER:
This stored procedure is used to display the list of triggers which been placed on a specific table.
Syntax:
SP_HELPTRIGGER TABLENAME
SP_HELPTRIGGER DEPT
DISPLAYING THE CODE OF TRIGGER:
Syntax:
SP_HELPTEXT ‘TRIGGER_NAME’
SP_HELPTEXT ‘T1’
DDL TRIGGERS (2005 Triggers):
These triggers are fired when user performs DDL operations in the database and these triggers belong to database. It means we can define triggers on the current database.
Syntax:
CREATE TRIGGER TRIGGERNAME ON DATABASE
FOR/ AFTER {DROP,/ALTER/ CREATE}
AS
BEGIN
SQL STATEMENT.
END
USE PUBS
CREATE TRIGGER DROP_TRG ON DATABASE FOR DROP-TABLE
AS
BEGIN
PRINT ‘TABLE DROPPED’
ROLLBACK
END
E.g.:
USE PUBS
CREATE TRIGGER DROP_TRG ON DATABASE INSTEAD OF DROP-TABLE
AS
BEGIN
PRINT ‘U CANNOT DROP THE TABLE’
ROLLBACK
END
VIEWS in SQL
A View is nothing but an image table or virtual table, which is created for a base table. A view can be created by taking all values from the base table or by taking only selected values from base table. There are two types views available in SQL Server.
- Simple Views
- Complex Views
Note: If we perform any modifications in base table, then those modifications automatically effected in view and vice-versa.
1.Simple Views:
Creating View by taking only one single base table.
Syntax:
CREATE VIEW VIEWNAME [WITH ENCRYPTION]
AS SELECT * FROM TABLENAME [WHERE CONDITION]
[WITH CHECK OPTION]
Crete view example in SQL?
CREATE VIEW V1 AS SELECT * FROM EMP
INSERT INTO V1 VALUES (55,’RAVI’, 10000,10)
The above insert statement inserts the values into base table EMP as well as into view V1.
CREATE VIEW V2 AS SELECT * FROM EMP WHERE DEPTNO=10
INSERT INTO V2 VALUES (66,’BABBU’, 25000,10)
The above insert statement inserts the values into base table EMP as well as into view
V2.
INSERT INTO V2 VALUES (77,’AMAR’, 15000, 20)
The above insert statement inserts the values into only base table EMP but not into view
V2 because according to the definition of V2 user supplied values are invalid values. It means invalid values are inserting into base table EMP. To stop this kind of operations we have to create the view with ‘WITH CHECK OPTION’.
CREATE VIEW V3 AS SELECT * FROM EMP WHERE DEPTNO=10
WITH CHECK OPTION
INSERT INTO V3 VALUES (88,’TEJA’, 25000,20)
The above insert statement cannot inserts the values into base table EMP as well as into view V3.
SP_HELPTEXT:
This stored procedure is used to display the definition of a specific view.
Syntax:
SP_HELPTEXT VIEWNAME
SP_HELPTEXT V1
Output: CREATE VIEW V1 AS SELECT * FROM EMP
WITH ENCRYPTION:
Once we create any view with ‘WITH ENCRYPTION’ then we cannot find the definition of that particular view using SP_HELPTEXT stored procedure because this encryption option hides the definition.
View with encryption example in SQL?
CREATE VIEW V4 WITH ENCRYPTION
AS SELECT * FROM EMP WHERE DEPTNO=20
SP_HELPTEXT V4
Output :The text for object v4 is encrypted
To decrypt the definition of view V4 we have to follow the below approach
- Replace CREATE with ALTER
- Remove WITH ENCRYPTION keyword
- Select the query and press F5.
Alter view example in SQL?
ALTER VIEW V4
AS SELECT * FROM EMP WHERE DEPTNO=20
SP_HELPTEXT V4
CREATE VIEW V4
AS SELECT * FROM EMP WHERE DEPTNO=20
2.Complex Views:
Creating View by taking multiple base tables.
CREATE VIEW EMP_DEPT_VIEW
AS SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO
Syntax To Create view based on another views:
SQL SERVER enables users to create views based on another view. We can create view based on another view up to 32 levels
Syntax:
CREATE VIEW VIEWNAME [WITH ENCRYPTION]
AS SELECT * FROM VIEWNAME [WHERE CONDITION]
[WITH CHECK OPTION]
CREATE VIEW V5 AS SELECT * FROM V1 WHERE DEPTNO=10
Syntax To Drop the Views:
DROP VIEW VIEWNAME […N]
E.g.: DROP VIEW V1, V2, V3, V4, V5
INDEXES in SQL
Indexes in SQL server is similar to index in text book.. Indexes are used to improve the performance of queries.
INDEXES ARE GENERALLY CREATED FOR FOLLOWING COLUMNS
Primary key column
Foreign key column: frequently used in join conditions.
Column which are frequently used in where clause
Columns, which are used to retrieve the data in sorting order.
INDEXED CANNOT BE CREATED FOR FOLLOWING COLUMNS:
The columns which are not used frequently used in where clause.
Columns containing the duplicate and null values
Columns containing images, binary information, and text information.
TYPES OF INDEXES
1)CLUSTERED INDEX
2)NON-CLUSTERED INDEX
CLUSTERED INDEX:
only one clustered index is allowed per table. The order of values in a table order of values in index is also same. When cluster index is created on table data is arranged in ascending order cluster index will occupy 5% of the table.
Syntax:
CREATE [UNIQUE] CLUSTERED INDEX INDEXNAME ON TABLENAME (COLUMN)
Clustered Index example in SQL?
CREATE CLUSTERED INDEX CI ON EMP (EMPNO)
Note: if we want to maintain unique values in clustered/non clustered indexed column then specify UNIQUE keyword along with CLUSTERED INDEX/NONCLUSTERD INDEX
NONCLUSTERED INDEX:
It is the default index created by the server the physical order of the data in the table is different from the order of the values in index.
Max no. Of non-clustered indexed allowed for table is 249
Syntax:
CREATE [UNIQUE] NONCLUSTERED INDEX INDEXNAME
ON TABLENAME ( COLUMN1,…)
Non Clustered Index example in SQL?
CREATE NONCLUSTERED INDEX NCI ON EMP (ENAME, SAL)
CREATE UNIQUE NONCLUSTERED INDEX UI ON DEPT (DNAME)
COMPOSITE INDEX:
If a Unique NonClustered index is created on more than one column then that concept is called composite index.
Unique NonClustered index example in SQL?
CREATE UNIQUE NONCLUSTERED INDEX COI ON DEPT (DEPTNO, DNAME)
DEPTNO DNAME
10 SALES
20 HR
30 IR
10 HR (Accepted)
20 SALES (Accepted)
30 IR (Repeated, Not accepted)
SP_HELPINDEX:
This stored procedure is used to display the list of indexes, which have been placed on different columns of a specific table.
E.g.: SP_HELPINDEX EMP
Syntax to drop the index:
DROP INDEX TABLENAME.INDEX.NAME
DROP INDEX DEPT.UI
CURSORS in SQL
Cursor is a logical area, which is used to retrieve a particular nth record. Selecting a particular nth record is not possible through physical area (Table). In such situations one logical area (Cursor) we can create and then we can select a particular nth record. Cursors are used to store transaction information temporarily.
Types of Cursors
1.STATIC CURSOR
2.DYNAMIC CURSOR
3.KEYSET CURSOR
4.FORWARD_ONLY CURSOR
DEPT
DEPTNO DNAME LOC
10 SALES HYD
20 HR CHE
30 IR BAN
40 A/C MUM
Syntax to declare the Cursor:
DECLARE CURSORNAME CURSOR CURSORTYPE
FOR SELECT * FROM TABLENAME
Syntax to open the Cursor:
OPEN CURSORNAME
Syntax to fetch the records from Cursor:
FETCH {FIRST/NEXT/PRIOR/LAST/ABSOLUTE N/RELATIVE N}
FROM CURSORNAME
Syntax to close the Cursor:
CLOSE CURSORNAME
Syntax to de allocates the Cursor:
DEALLOCATE CURSORNAME
FIRST: Fetches first record from the cursor
NEXT: Fetches next record from the current position of the cursor
PRIOR: Fetches previous record from the current position of the cursor
LAST: Fetches last record from the cursor
ABSOLUTE N: Fetches nth record from the top of the cursor if n is positive fetches the nth record from bottom of the cursor if n is negative. Where n is an integer
RELATIVE N: Fetches nth next record from current position of the cursor if n is positive fetches nth previous record from the current position of the cursor if n is negative where n is an integer.
1. STATIC CURSOR:
This is the logical area in which dynamic updations not possible. If we want those updations in logical area we close the cursor and we need to reopen it.
Static Cursor example in SQL?
DECLARE SC CURSOR STATIC FOR SELECT * FROM DEPT
OPEN SC
FETCH FIRST FROM SC
10 SALES HYD
UPDATE DEPT SET DEPTNO=15 WHERE DEPTNO=10
FETCH FIRST FROM SC
10 SALES HYD
CLOSE SC
OPEN SC
FETCH FIRST FROM SC
15 SALES HYD
FETCH NEXT FROM SC
20 HR CHE
FETCH PRIOR FROM SC
15 SALES HYD
FETCH LAST FROMSC
40 A/C MUM
FETCH ABSOLUTE 2 FROM SC
20 HR CHE
FETCH ABSOLUTE –2 FROM SC
30 IR BAN
FETCH FIRST FROM SC
15 SALES HYD
FETCH RELATIVE 2 FROM SC
30 IR BAN
FETCH RELATIVE –2 FROM SC
15 SALES HYD
CLOSE SC
DEALLOCATE SC
2. DYNAMIC CURSOR:
This is the logical area in which dynamic updations possible. We need to close and reopen the cursor for the modified values
Dynamic Cursor example in SQL?
DECLARE DC CURSOR DYNAMIC FOR SELECT * FROM DEPT
OPEN SC
FETCH FIRST FROM DC
15 SALES HYD
UPDATE DEPT SET DEPTNO=10 WHERE DEPTNO=15
FETCH FIRST FROM DC
10 SALES HYD
CLOSE DC
DEALLOCATE DC
Note: ABSOLUTE N will not be supported by the dynamic cursor because dynamic updations are possible.
KEYSET CURSOR:
This is the logical area, which is useful only when there is a primary key in the table. This logical area holds only Primary key column values. Based on the key column values in the logical area (Cursor) the rest column values are coming from physical area (Table).
KeySet Cursor example in SQL?
DECLARE KC CURSOR KEYSET FOR SELECT * FROM DEPT
OPEN KC
FETCH FIRST FROM KC
10 SALES HYD
UPDATE DEPT SET LOC=’SRNAGAR’ WHERE LOC=’HYD’
FETCH FIRST FROM KC
10 SALES SRNAGAR
UPDATE DEPT SET DEPTNO=15 WHERE DEPTNO=10
FETCH FIRST FROM KC
0 NULL NULL
CLOSE KC
OPEN KC
FETCH FIRST FROM KC
15 SALES HYD
CLOSE KC
DEALLOCATE KC
Note: Dynamic updations are not possible on key column of the keyset cursor, we have to close the cursor and we need to reopen it for the modified values.
FORWARD_ONLY CURSOR:
This is the most unused logical area because it supports only NEXT operation.
Farward only Cursor example in SQL?
DECLARE FC CURSOR FORWARD_ONLY FOR SELECT * FROM DEPT
OPEN FC
FETCH FIRST FROM FC
Error Message
FETCH NEXT FROM FC
15 SALES HYD
CLOSE SC
DEALLOCATE FC
NOTE:If the cursor does not found any value in its searching process then it will display the corresponding column values.
BATCH TO FETCH MULTIPLE RECORDS FROM STATIC CURSOR
Step1: DECLARE SC CURSOR STATIC FOR SELECT * FROM DEPT
Step2:
DECLARE @N INT
OPEN SC
SET @N=2
WHILE (@N<=4)
BEGIN
FETCH ABSOLUTE @N FROM SC
SET @N=@N+1
END
Step3: CLOSE SC
DEALLOCATE SC
OUTPUT:
DEPTNO DNAME LOC
20 HR CHE
DEPTNO DNAME LOC
30 IR BAN
DEPTNO DNAME LOC
40 A/C MUM
T-SQL
TRANSACT STRUCTURED QUERY LANGUAGE
This is a 4th generated intermediate language between user and SQL Server. Whenever user wants to interact with SQL Server, he has to interact with SQL Server through T-SQL. It includes the following Sub Languages
- DDL (Data Definition Language)
- DML (Data Manipulation Language)
- DCL (Data Control Language)
- TCL (Transaction Control Language)
Things to Observe:
- While writing the Queries using T-SQL in SQL Server Management Studio we need not to follow any particular case. Because T-SQL is case insensitive language.
- After writing the Query, we need to select that query using either mouse or keyboard.
- Now Press F5 ( Execute Key).
- Then the results are displayed in a separate window called Result window or Result Pane.
- Use Ctrl+R to Hide/Show the Result window or Result Pane.
- Use F8 for Object Explorer
Note:
- SQL SERVER can handle nearly 32767 Databases
- Each Database can handle nearly 2 billion Database Objects.
- Each Table can handle nearly 1024 columns
- Each Table can handle nearly 1 million Rows.
SPECIAL OPERATORS IN SQL SERVER
IN
NOT IN
BETWEEN
NOTBETWEEN
LIKE
NOT LIKE
IS NULL
1. IN operator:
It is used for comparing multiple values.
SELECT * FROM EMP WHERE ENAME IN (‘RAM’,’RAJ’)
SELECT * FROM EMP WHERE EMPNO IN (11, 22, 33)
2. NOT IN Operator:
It is just opposite to in. it display all the values in table, other than values specified in list
SELECT * FROM EMP WHERE JOB NOT IN (‘RAM’,’RAJ’)
Other than ‘RAM’, ‘RAJ’ details will be displayed
3. BETWEEN Operator:
it will display the values specified in the range including the given range. It is also known as INCLUSIVE OPERATOR.
SELECT * FROM EMP WHERE SAL BETWEEN 2000 AND 4000
It will display all the employees details who are getting salary more than or equal to 2000 and less than or equal to 4000.
It includes the range specified, so it is called inclusive operator.
4. NOT BETWEEN Operator:
It is opposite to ‘between’ operator it will display the values other than range specified.
SELECT * FROM EMP WHERE SAL NOT BETWEEN 2000 AND 4000.
Note: NOT BETWEEN operator will not include the range specified, so it is called exclusive operator.
5. LIKE Operator:
It is used to search for pattern in characters. it associated with two characters
_ (UNDERSCORE): For single character
%(PERCENTILE): For Multiple characters
[] – Any single character within the specified range.
[^] – Any single character not within the range [^a_f] [^abcdef]
Write a query to display the names begin with ‘s’ in sql?
SELECT ENAME FROM EMP WHERE ENAME LIKE ‘S%’
ENAME
SMITH
SCOTT
Write a query to to display names end with ‘s’ in sql?
SELECT ENAME FROM EMP WHERE ENAME LIKE ‘%S’
ENAME
JAMES
JONES
Write a query to display names
containing ‘a’ as second character in sql?
SELECT ENAME LIKE ‘_A%’
Write a query to
display name containing 5 characters in sql?
SELECT ENAME FROM EMP WHERE ENAME LIKE ’-----‘
OR
SELECT ENAME FROM EMP WHERE LEN (ENAME)=5
6. NOT LIKE Operator:
Opposite to like operator.
7. IS NULL Operator:
It is used for comparing NULL values
Write a query to
display all employees who are working in null department in sql?
SELECT * FROM EMP WHERE DEPTNO. IS NULL
8. IS NOT NULL:
Opposite to IS NULL operator
Write a query to
display all employees who are not working under null department in sql?
SELECT * FROM EMP WHERE DEPTNO. IS NOT NULL
SUB-QUERIES
A Sub Query is also a query, which is defined under a main query .It always sends values to its nearest main query. Server first sub query first based on the sub query value main query will get executed.
If a sub query send single value to its nearest main query then that sub query is called
Single-Valued-Sub query
If a sub query sends multiple values to its nearest main query then that sub query is called
Multi-Valued-Sub query
Note: If a sub query sends multiple values to its nearest main query then we have to use IN operator between Main query and Sub query.
EMPNO ENAME SAL DEPTNO
11 RAM 15000.0000 10
22 RAJ 20000.0000 20
33 ABI 10000.0000 10
44 DP 5000.0000 20
55 NAAG 5000.0000 10
Syntax:
SELECT * FROM TABLENAME
WHERE COLUMNNAME = (SELECT STATEMENT WHERE CONDITION)
Write a query to
illustrate nested query in sql?
SELECT * FROM EMP
WHERE DEPTNO= (SELECT DEPTNO FROM EMP WHERE ENAME=’RAM’)
Write a query to display employee details, whose salary is
greater than highest salary of 10th department in sql?
SELECT * FROM EMP
WHERE SAL> (SELECT MAX (SAL) FROM EMP WHERE DEPTNO=10)
Write a query to display employee details, whose
salary is greater than average salary of RAM department in sql?
SELECT * FROM EMP
WHERE SAL> (SELECT AVG (SAL) FROM EMP WHERE DEPTNO=
SELECT DEPTNO FROM EMP WHERE ENAME=’RAM’)
FUNCTIONS
A function is nothing but a sub program or a module which can written for achieving a particular task
OR
A function is a readymade formula, which takes values as input and returns values as output.
OR
A function is a pre defined program segment that carries specific and well-defined task
.
CLASSIFICATIONS OF FUNCTIONS:
1. SCALAR OR SINGLE-VALUED FUNCTIONS
2. GROUP OR AGGREGATE FUNCTIONS
SCALAR VALUED FUNCTIONS:
These functions takes single value as input and returns single value as output
CLASSIFICATION OF SCALAR VALUED FUNCTIONS:
MATHEMATICAL FUNCTIONS
STRING FUNCTIONS
DATE& TIME FUNCTIONS
MATHEMATICAL FUNCTIONS in SQL:
ABSOLUTE: It return the absolute
value of n
SELECT ABS(-10.65)
10.65 |
SELECT ABS(18.69)
18.67 |
POWER (M, N): It return the m power n
value
SELECT POWER(3,2)
9 |
SELECT POWER(6,2)
36 |
SQRT(N): It return the square
root value of n
SELECT SQRT (4)
SQUARE (N): It return the square
value of n
SELECT SQUARE (9)
81 |
SELECT SQUARE (5)
25 |
ROUND (M, N):
It will round the value
of m to nearest whole number of it will around..
SELECT ROUND (15.143)
15 |
SELECT ROUND (16.513)
16 |
SELECT ROUND (16.816)
17 |
SELECT ROUND (21.132,1)
21.1 |
SELECT ROUND (25.143)
25 |
CEILING: It (ceiling) returns the smallest integer greater than ‘n’
SELECT CEILING (15.13)
16 |
FLOOR (N):
SELECT FLOOR (15.13)
15 |
STRING FUNCTIONS in SQL:
ASCII (CH): It returns the ascii value of given character.
SELECT ASCII (‘A’)
65 |
SELECT ASCII (‘a’)
97 |
SELECT ASCII (‘0’)
48 |
SELECT ASCII (‘’)
32 |
CHAR (N): It returns the character
for given ascii value
SELECT CHAR (65)
A |
SELECT CHAR (98)
b |
LOWER (STRING): It converts the upper
case letters in string into lower cases
SELECT LOWER (‘NRSTT’)
nrstt |
UPPER (STRING): it converts the
lower cases into upper cases
SELECT UPPER (‘nrstt’)
NRSTT |
SELECT ENAME, LOWER (ENAME) LOWER_ENAMES FROM EMP
ENAME LOWER-ENAME
SMITH SMITH
ALLEN ALLEN
LEN (STRING): it converts the lower
cases into upper cases
SELECT LEN (‘SQLSERVER’)
9 |
SELECT LEN(‘SQL SERVER’)
10 |
SPACE IS ALSO A CHARACTER
SELECT ENAME, LEN (‘ENAME’) LENGTH FROM EMP
ENAME LENGTH
SMITH 5
ALLEN 5
MILLER 6
REVERSE (STRING): it will reverse the
given string
SELECT REVERSE (‘SQLSERVER’)
REVRESLQS
SELECT ENAME, REVERSE (ENAME) REV_ENAME FROM EMP
ENAME REV_ENAME
SMITH HTIMS
ALLEN NELLA
MILLER RELLIM
REPLACE (STRING, ‘SEARCHING STRING’, ‘REPLACING STRING’)
it will replace a sub
string in a string with given string
SELECT REPLACE (‘WRONG’, ‘WR’, ‘R’)
RONG |
Ã
SELECT REPLACE (‘JACK AND JUE’, ‘J’, ‘BI’)
BLACK AND BLUE |
SUB STRING (‘STRING’, ‘STARTING POSITION’, NO OF CHARACTERS REQUIRED);
it will entract a
substring flow main string
SELECT SUBSTRING (‘ORACLE’, 1, 3’)
ORA |
SELECT SUBSTRING (‘ORACLE’, 2, 2)
RA |
SELECT ENAME, SUBSTRING (ENAME,1,2) SUB_ENAME FROM EMP
ENAME SUB_ENAME
SMITH SM
ALLEN AL
DATE FUNCTIONS in SQL
GET DATE ():
SELECT GETDATE ()
2005-4-5: 4:30.15.416
DATE PART ABBREVIATION
YEAR YY
QUARTER QQ
MONTH MM
WEEK WW
DAY DD
HOUR HH
MINUTE MM
SIZE SS
MILLISEC MS
DATE ADD (DATE PART, NUMBER, DATE):
it returns the date
according to date part.
SELECT DATE (YY,1,’2005-3-15’)
2006-3-15 |
SELECT DATEADD (MM,1,’1998-4-18’)
1998-5-18 |
à SELECT DATE ADD (DAY,1,’1999-04-26’)
1999-04-27 |
DATE DIFF (DATEPART, DATE 1, DATE 2): it returns the
difference between the dates according to the date part
SELECT DATEDIFF (YEAR,'1999-11-5','2006-11-15')
7 |
DATEPART(PART,DATE):it returns the part of the given date
SELECT DATEPART(DAY,’2007-09-13’)
OUTPUT: 13
DATENAME(PART,DATE): it returns the name of
the given string
SELECT DATENAME(MONTH,’2007-10-31’)
OUTPUT: OCTOBER
AGGRIGATE OR GROUP FUNCTIONS in SQL:
These functions takes multiple values as input and returns single value as output, these includes
MAX ( )
MIN ( )
SUM ( )
AVG ( )
COUNT ( )
COUNT_BIG ( )
EMPNO ENAME SAL DEPTNO
11 RAM 15000.0000 10
22 RAJ 20000.0000 20
33 ABI 10000.0000 10
44 DP 5000.0000 20
55 NAAG 5000.0000 10
Based on the above table we will do some examples for Aggregate functions
SELECT MAX (SAL) AS HISAL FROM EMP
HISAL
20000
SELECT MAX (SAL) AS HISAL, MIN (SAL) AS LOSAL FROM EMP
HISAL LOSAL
20000 5000
SELECT MAX (SAL) AS HISAL, MIN (SAL) AS LOSAL,
SUM (SAL) AS TOTSAL, AVG (SAL) AS AVGSAL FROM EMP
HISAL LOSAL TOTSAL AVGSAL
20000 5000 55000 11000
E.g.: SELECT MAX (SAL) AS HISAL, MIN (SAL) AS LOSAL,
SUM (SAL) AS TOTSAL, AVG (SAL) AS AVGSAL FROM EMP WHERE DEPTNO=10
HISAL LOSAL TOTSAL AVGSAL
15000 5000 30000 10000
SELECT MAX (SAL) AS HISAL, MIN (SAL) AS LOSAL,
SUM (SAL) AS TOTSAL, AVG (SAL) AS AVGSAL FROM EMP WHERE DEPTNO=20
HISAL LOSAL TOTSAL AVGSAL
20000 5000 25000 12500
SET OPERARTORS in SQL
Set operators in SQL Server are used to combine the output of multiple queries.
When ever we want to combine the output of multiple queries we have to identify three factors.
- Whether the multiple queries contains equal number of columns or not?
- If they are equal again we have to identify whether their data types are equal or not?
- We have to identify whether the output column name coming from first query or not?
EMP: DEPT:
EMPNO ENAME SAL DEPTNO DEPTNO DNAME LOC
11 RAM 15000 10 10 SALES HYD
22 RAJ 20000 20 20 HR CHE
33 ABI 10000 10 30 IR BAN
44 DP 5000 20
44 NAAG 5000 NULL
In SQL Server there existed four types of Set
1)UNION ALL
2)UNION
3)INTERSECT
4)EXCEPT
1)UNION ALL: It combines the output of multiple queries including duplicate values.
Syntax: SELECT COLUMN1, COLUMN2… FROM TABLE1
UNION ALL
SELECT COLUMN1, COLUMN2… FROM TABLE2
Union all example in sql?
SELECT EMPNO FROM EMP
UNION ALL
SELECT DEPTNO FROM DEPT
EM PNO
11
22
33
44
44
10
20
30
2)UNION: It combines the output of multiple queries with out considering duplicate values, mean time it arranges output data in ascending order.
Syntax: SELECT COLUMN1, COLUMN2… FROM TABLE1
UNION
SELECT COLUMN1, COLUMN2… FROM TABLE2
Union example in sql?
SELECT EMPNO FROM EMP
UNION
SELECT DEPTNO FROM DEPT
EMPNO
10
11
20
22
30
33
44
3)INTERSECT: It selects the common values from given set of queries.
Syntax: SELECT COLUMN1, COLUMN2… FROM TABLE1
INTERSECT
SELECT COLUMN1, COLUMN2… FROM TABLE2
SELECT DEPTNO FROM EMP
INTERSECT
SELECT DEPTNO FROM DEPT
Intersect example in sql?
DEPTNO
10
20
4)EXCEPT: It selects particular values from the first query which are not available in second query..
Syntax: SELECT COLUMN1, COLUMN2… FROM TABLE1
EXCEPT
SELECT COLUMN1, COLUMN2… FROM TABLE2
Except example in sql?
SELECT DEPTNO FROM EMP
EXCEPT
SELECT DEPTNO FROM DEPT
DEPTNO
NULL
Thanks for visiting this blog. How is the content?. Your comment is great gift to my work. Cheers.
No comments:
Post a Comment