SQL SERVER 2008 – FUNCTIONS In SQL SERVER 2008 – Group Functions

Operators: An operator is a symbol specifying an action that is performed on one or more expressions. The lists the operator categories that SQL Server supports:

-Arithmetic Operators

-Assignment Operator

-Comparison Operators

-Logical Operators

-Concatenation Operator

 

Arithmetic Operators: Arithmetic operators perform mathematical operations on two expressions of one or more of the data types of the numeric data type category. Those are:

+          –           Addition

–           –           Subtraction

*          –           Multiplication

/           –           Division

%         –           Modulo

 

Assignment Operators: The equal sign (=) is the only assignment operator.

 

Comparison Operators: Comparison operators test whether two expressions are the same. Comparison operators can be used on all expressions except expressions of the text, ntext, or image data types. Those are:

=          –           Equal to

>          –           Greater than

<          –           Less than

>=        –           Greater than or equal to

<=        –           Less than or equal to

<>        –           not equal to

!=         –           not equal to

!<         –           not less than

!>         –           not greater than

 

Logical Operators: Logical operators test for the truth of some condition. Logical operators, like comparison operators, return a Boolean value of TRUE or FALSE.

Those are:

  • ALL                TRUE if all of a set of comparisons are TRUE
  • AND               TRUE if both Boolean expressions are TRUE
  • ANY               TRUE if any one of a set of comparisons are TRUE
  • BETWEEN     TRUE if the operand is within a range
  • EXISTS          TRUE if a subquery contains any rows
  • IN                    TRUE if the operand is equal to one of a list of expressions.
  • LIKE               TRUE if the operand matches a pattern
  • NOT                Reverses the value of any other Boolean operator
  • OR                  TRUE if either Boolean expression is TRUE
  • SOME             TRUE is some of a set of comparisons are TRUE

 

String Concatenation Operator: The plus sign (+) is the string concatenation operator that enables string concatenation.

 

-WAQ             to find the details of employees whose job is CLERK.

Sol: SELECT * FROM EMP WHERE JOB=’CLERK’

 

-WAQ to find the details of all employees except SALESMAN.

Sol: SELECT * FROM EMP WHERE JOB != ‘SALESMAN’

(OR)

Sol: SELECT * FROM EMP WHERE JOB <> ‘SALESMAN’

 

-WAQ to find the details of employees who are earning more than 3000

Sol: SELECT * FROM EMP WHERE SAL>3000

 

-WAQ to find the details of employees who are earning less than 2500

Sol: SELECT * FROM EMP WHERE SAL<2500

 

-WAQ to find the details of employees who are earning with in a range of 2500 and 4000

Sol: SELECT * FROM EMP WHERE SAL>=2500 AND SAL<=4000

Sol: SELECT * FROM EMP WHERE SAL BETWEEN 2500 AND 4000

 

-Between operator is used for specifying with a range of values to test.

 

-WAQ to find the details of employees who are earning less than 1500 as well as more than 3500

Sol: SELECT * FROM EMP WHERE SAL<1500 OR SAL>3500

Sol: SELECT * FROM EMP WHERE SAL NOT BETWEEN 1500 AND 3500

 

-WAQ to find the details of employees whose jobs are CLERK, MANAGER AND SALESMAN

Sol: SELECT * FROM EMP WHERE JOB=’CLERK’ OR JOB=’MANAGER’ OR JOB=’SALESMAN’

Sol: SELECT * FROM EMP WHERE JOB IN (’CLERK’, ’MANAGER’, ’SALESMAN’)

 

-In Operator Determines whether a specified value matches any value in the list.

 

-WAQ to find the details of all employees except PRESIDENT AND MANAGER.

Sol: SELECT * FROM EMP WHERE JOB != ’MANAGER’ AND JOB != ’PRESIDENT’

Sol: SELECT * FROM EMP WHERE JOB NOT IN (’MANAGER’, ’PRESIDENT’)

 

-WAQ to find the details of employees who name starts with character S.

Sol: SELECT * FROM EMP WHERE ENAME LIKE ‘S%’

 

-Like Operator determines whether a specific character string matches a specified pattern. A pattern can include regular characters and wildcard characters. During pattern matching, regular characters must exactly match the characters specified in the character string. However, wildcard characters can be matched with arbitrary fragments of the character string. Using wildcard characters makes the LIKE operator more flexible than using the = and != string comparison operators.

 

% – it represents any string of zero or more characters.

 

-WAQ to find the details of employees whose name contains M in it.

Sol: SELECT * FROM EMP WHERE ENAME LIKE ‘%M%’

 

-WAQ to find the details of employees whose name is SMITH, when the spelling of the name is not known exactly as SMITH OR SMYTH.

Sol: SELECT * FROM EMP WHERE ENAME LIKE ‘SM_TH’

OR

Sol: SELECT * FROM EMP WHERE SOUNDEX(ENAME)=SOUNDEX(‘SMYTH’)

 

_(underscore) – it represents any single character.

 

WAQ to find the details of employees whose name starts with a characters between A to S.

Sol: SELECT * FROM EMP WHERE ENAME LIKE ‘[A-S]%’

 

[ ] – it represents any single character within the specified range ([a-f]) or set ([abcdef]).

 

WAQ to find the details of employees whose name starts with any of the character “ABCDE”.

Sol: SELECT * FROM EMP WHERE ENAME LIKE ‘[ABCDE]%’

 

WAQ to find the details of employees whose name starts with a characters not between A to S.

Sol: SELECT * FROM EMP WHERE ENAME LIKE ‘[^A-S]%’

 

WAQ to find the details of employees whose name starts with characters apart from “ABCDE”

Sol: SELECT * FROM EMP WHERE ENAME LIKE ‘[^ABCDE]%’

OR

Sol: SELECT * FROM EMP WHERE ENAME NOT LIKE ‘[ABCDE]%’

 

WAQ to find the details of employees whose job is CLERK and earning 3000.

Sol: SELECT * FROM EMP WHERE JOB=’CLERK’ AND SAL=3000

 

WAQ to find the details of employees whose job is MANAGER as well as earning more than 3000.

Sol: SELECT * FROM EMP WHERE JOB=’MANAGER’ OR SAL>3000

 

WAQ to find the details of employees whose salary is not equal to 3000.

Sol: SELECT * FROM EMP WHERE NOT SAL=3000

 

Set Operators: Combines the results of two or more queries into a single result set.

 

The following are basic rules for combining the result sets of two queries by using SET Operators:

  • The number and the order of the columns must be the same in all queries.
  • The data types must be compatible.

 

UNION: Combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union.

SELECT JOB FROM EMP WHERE DEPTNO=10

UNION

SELECT JOB FROM EMP WHERE DEPTNO=30

 

UNION ALL: These is same as UNION but in this case duplicates will not be eliminated.

SELECT JOB FROM EMP WHERE DEPTNO=10

UNION ALL

SELECT JOB FROM EMP WHERE DEPTNO=30

 

INTERSECT: Returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.

SELECT JOB FROM EMP WHERE DEPTNO=10

INTERSECT

SELECT JOB FROM EMP WHERE DEPTNO=30

 

EXCEPT: Returns any distinct values from the query to the left of the EXCEPT operand that are not also returned from the right query.

SELECT JOB FROM EMP WHERE DEPTNO=10

EXCEPT

SELECT JOB FROM EMP WHERE DEPTNO=30

 

CLAUSES: SQL Server provides with the following clauses that can be used in the SELECT statements:

  • WHERE
  • GROUP BY
  • HAVING
  • ORDER BY

 

The complete syntax of the SELECT statement looks as following:

SELECT <select_list>  FROM <tname>

[ WHERE search_condition ]

[ GROUP BY group_by_expression ]

[ HAVING search_condition ]

[ ORDER BY order_expression [ ASC | DESC ] ]

 

WHERE Clause: The WHERE clause is a filter that defines the conditions each row in the source tables must meet to qualify for the SELECT. Only rows that meet the conditions contribute data to the result set. Data from rows that do not meet the conditions is not used.

SELECT * FROM EMP WHERE JOB=’MANAGER’

SELECT * FROM EMP WHERE DEPTNO=20

 

GROUP BY Clause: The GROUP BY clause partitions the result set into groups based on the values in the columns of the group_by_list. For example, the Emp table has 3 values in Deptno column. A GROUP BY Deptno clause partitions the result set into 3 groups, one for each value of Deptno.

 

WAQ to find the highest salaries for each department.

Sol: SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO

 

WAQ to find the highest salaries for each job.

Sol: SELECT JOB, MAX(SAL) FROM EMP GROUP BY JOB

 

WAQ to find the highest salaries for each department in it for each job.

Sol: SELECT DEPTNO, JOB, MAX(SAL) FROM EMP GROUP BY DEPTNO, JOB

 

Note: While using the GROUP By clause the select_list of the query should contain only the following:

-Group Functions or Aggregate Functions

-Columns used in the Group By Clause

-Constants.

 

WAQ to find the number of employees working for each department.

Sol: SELECT DEPTNO, COUNT(*)  FROM EMP GROUP BY DEPTNO

 

WAQ to find the number of employees working for each department only if the number is greater than 3.

Sol: SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO HAVING COUNT(*)>3

 

HAVING Clause: The HAVING clause is an additional filter that is applied to the result set. Logically, the HAVING clause filters rows from the intermediate result set built from applying any FROM, WHERE, or GROUP BY clauses in the SELECT statement. HAVING clauses are typically used with a GROUP BY clause.

 

WAQ to find the number of Clerk’s working for each department.

Sol: SELECT DEPTNO, COUNT(*) FROM EMP WHERE JOB=’CLERK’ GROUP BY DEPTNO

 

WAQ to find the number of Clerk’s working for each department only if the count is greater than 1.

Sol: SELECT DEPTNO, COUNT(*) FROM EMP WHERE JOB=’CLERK’ GROUP BY DEPTNO HAVING COUNT(*)>1

 

ORDER BY order_list[ ASC | DESC ]

The ORDER BY clause defines the order in which the rows in the result set are sorted. order_list specifies the result set columns that make up the sort list. The ASC and DESC keywords are used to specify if the rows are sorted in an ascending or descending sequence.

SELECT * FROM EMP ORDER BY SAL

SELECT * FROM EMP ORDER BY SAL DESC

SELECT * FROM EMP ORDER BY SAL, COMM

 

SUBQUERY: A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed. In this case first the inner query executes and basing upon the result generated by it the outer query executes to generate the final output.

 

WAQ to find the details of employees earning the highest salary.

Sol: SELECT * FROM EMP WHERE SAL=

(SELECT MAX(SAL) FROM EMP)

 

WAQ to find the details of employees earning the second highest salary.

Sol: SELECT * FROM EMP WHERE SAL=

(SELECT MAX(SAL) FROM EMP WHERE SAL<

(SELECT MAX(SAL) FROM EMP))

 

WAQ to find the details of employees working in sales department.

Sol: SELECT * FROM EMP WHERE DEPTNO=

(SELECT DEPTNO FROM DEPT WHERE DNAME=’SALES’)

 

 

 

WAQ to find the details of employees working in Mumbai.

Sol: SELECT * FROM EMP WHERE DEPTNO=

(SELECT DEPTNO FROM DEPT WHERE LOC=’MUMBAI’)

 

WAQ to find the details of employees who are earning more than the highest salary of deptno 30

Sol: SELECT * FROM EMP WHERE SAL>

(SELECT MAX(SAL) FROM EMP WHERE DEPTNO=30)

OR

SELECT * FROM EMP WHERE SAL>

ALL (SELECT SAL FROM EMP WHERE DEPTNO=30)

 

-In this case we can use the ALL operator which will compare an expression with set of values, where the expression has to satisfy the condition with all the values.

 

WAQ to find the details of employees who are earning less than the lowest salary of deptno 20

Sol: SELECT * FROM EMP WHERE SAL<

(SELECT MIN(SAL) FROM EMP WHERE DEPTNO=20)

OR

SELECT * FROM EMP WHERE SAL<

ALL(SELECT SAL FROM EMP WHERE DEPTNO=20)

 

WAQ to find the details of employees who are earning less than the highest salary of deptno 10

Sol: SELECT * FROM EMP WHERE SAL<

(SELECT MAX(SAL) FROM EMP WHERE DEPTNO=10)

OR

SELECT * FROM EMP WHERE SAL<

ANY(SELECT SAL FROM EMP WHERE DEPTNO=10)

 

-In the place of ANY we can use SOME operator also.

– In this case we can use the ANY/SOME operatorS which will compare an expression with set of values, where the expression has to satisfy the condition with at least a single value.

 

WAQ to find the details of employees who are earning the highest salary in each department.

SELECT * FROM EMP WHERE SAL IN

(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO)

 

WAQ to find the details of seniors in each department.

SELECT * FROM EMP WHERE HIREDATE IN

(SELECT MIN(HIREDATE) FROM EMP GROUP BY DEPTNO)

 

Correlated Subqueries: Many queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query.

 

WAQ to find the details of employees earning the highest salary.

Sol: SELECT * FROM EMP E WHERE 0=

(SELECT COUNT(DISTINCT SAL) FROM EMP WHERE SAL>E.SAL)

 

WAQ to find the details of employees earning the second highest salary.

Sol: SELECT * FROM EMP E WHERE 1=

(SELECT COUNT(DISTINCT SAL) FROM EMP WHERE SAL>E.SAL)

 

-In this case if we want the n th highest salary we need to substitute n-1 value in the where condition of the outer query.

 

WAQ to find the details of departments in which employees are working.

Subquery:                    SELECT * FROM DEPT WHERE DEPTNO IN

(SELECT DISTINCT DEPTNO FROM EMP)

Correlated Subquery: SELECT * FROM DEPT WHERE EXISTS

(SELECT DEPTNO FROM EMP WHERE

EMP.DEPTNO=DEPT.DEPTNO)

 

-EXISTS is an operator which is used to specifies a subquery to test for the existence of rows.

 

WAQ to find the details of departments in which employees are not working.

Subquery:                    SELECT * FROM DEPT WHERE DEPTNO NOT IN

(SELECT DISTINCT DEPTNO FROM EMP)

Correlated Subquery: SELECT * FROM DEPT WHERE NOT EXISTS

(SELECT DEPTNO FROM EMP WHERE

EMP.DEPTNO=DEPT.DEPTNO)

 

WAQ to find the details of employees who have subordinates under them.

Subquery:                    SELECT * FROM EMP WHERE EMPNO IN(

SELECT DISTINCT MGR FROM EMP)

Correlated Subquery: SELECT * FROM EMP E WHERE EXISTS (

SELECT * FROM EMP M WHERE E.EMPNO=M.MGR)

 

WAQ to find the details of employees who doesn’t have any subordinates under them.

Subquery:                    SELECT * FROM EMP WHERE EMPNO NOT IN(

SELECT DISTINCT MGR FROM EMP)

Correlated Subquery: SELECT * FROM EMP E WHERE NOT EXISTS (

SELECT * FROM EMP M WHERE E.EMPNO=M.MGR)

Reference : Dilip Kumar Jena ( https://sqlexplore.wordpress.com )

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s