Sql Server material 20

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)

<<< Previous                                                                                                 Will Come...

Sql Server material 19

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

<<< Previous                                                                                                                           Next >>>

Sql Server material 18

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


<<< Previous                                                                                                             Next >>>

Sql Server material 17

-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

<<< Previous                                                                                                                           Next >>>

Sql Server material 16

-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

<<< Previous                                                                                                                        Next >>>

Sql Server material 15

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.

<<< Previous                                                                                                                         Next >>>

Sql Server material 14

Set Operators:

COUNT(expression): Returns the number of items in a group.

            SELECT COUNT(*) FROM EMP

            SELECT COUNT(*) FROM EMP WHERE DEPTNO=20

            SELECT COUNT(COMM) FROM EMP

COUNT_BIG(expression): COUNT_BIG works like the COUNT function. The only difference between the two functions is their return values. COUNT_BIG always returns a bigint data type value. COUNT always returns an int data type value.

            SELECT COUNT_BIG(*) FROM EMP

SUM(expression): Returns the sum of all the values. SUM can be used with numeric columns only. Null values are ignored

            SELECT SUM(SAL) FROM EMP

AVG(expression): Returns the average of the values in a group. Null values are ignored.

            SELECT AVG(SAL) FROM EMP

MAX(expression): Returns the maximum value in the expression.

            SELECT MAX(SAL) FROM EMP

MIN(expression): Returns the minimum value in the expression.

            SELECT MIN(SAL) FROM EMP

STDEV(expression): Returns the statistical standard deviation of all values in the specified expression.

            SELECT STDEV(SAL) FROM EMP

VAR(expression): Returns the statistical variance of all values in the specified expression.

            SELECT VAR(SAL) FROM EMP

<<< Previous                                                                                                                             Next >>>

Sql Server material 13

System Functions:

ISNUMERIC( expression ): Determines whether an expression is a valid numeric type. If it is numeric it returns 1 else return 0.

            SELECT ISNUMERIC(100)                                     OUTPUT: 1

            SELECT ISNUMERIC(‘100’)                                   OUTPUT: 1

            SELECT ISNUMERIC(‘100A’)                                OUTPUT: 0

ISDATE (expression): Determines whether an input expression is a valid date or not. If it is a valid date it returns 1 else return 0. Valid date in the sense the expression, which is present in mm/dd/yy format.

            SELECT ISDATE('12/21/98')                                    OUTPUT: 1

            SELECT ISDATE('21/12/98')                                    OUTPUT: 0

ISNULL (expression1, expression2): if expression1 is null then it returns expression2.

SELECT ISNULL(100, 200)                                     OUTPUT: 100

SELECT ISNULL(NULL, 200)                                OUTPUT: 200

SELECT EMPNO, ENAME, SAL, COMM, SAL + COMM AS [TOTAL SAL] FROM EMP

-In above case if any of the value in the comm. Is null it returns null in the Total Sal because any arithmetic operations performed on a null value results to null only at this time the statement has to be written as following:

SELECT EMPNO, ENAME, SAL, COMM, SAL + ISNULL(COMM, 0) AS [TOTAL SAL] FROM EMP

COALESCE (expression1, expression2, …… expression n):  Returns the first not null expression in the list of expressions given, similar to isnull but we can give multiple values here.

            SELECT COALESCE(NULL, 100, NULL, 200)     OUTPUT: 100

SELECT EMPNO, ENAME, SAL, COMM, SAL + COALESCE(COMM, 0) AS [TOTAL SAL] FROM EMP

DATALENGTH (expression) : Returns the number of bytes used to represent any expression.

            SELECT DATALENGTH(100)                                OUTPUT: 4

            SELECT DATALENGTH(‘HELLO’)                      OUTPUT: 5

HOST_NAME(): Returns the name of the workstation.

            SELECT HOST_NAME()

IDENT_CURRENT('table_name'): Returns the last identity value generated for a specified table by the identity function.

            SELECT IDENT_CURRENT(‘BANK’)

IDENT_SEED('table_name'): Returns the seed value that was specified when the identity function in a table was created.

            SELECT IDENT_SEED(‘BANK’)

IDENT_INCR('table_name'): Returns the increment value that was specified when the identity function in a table was created.

            SELECT IDENT_INCR(‘BANK’)

NEWID( ): Creates a unique value of type uniqueidentifier.

            SELECT NEWID()

NULLIF(expression1, expression2): Returns the first expression if the two expressions are not equivalent. If the expressions are equivalent, returns a null value.

            SELECT NULLIF(100, 200)                                     OUTPUT: 100

            SELECT NULLIF(100, 100)                                     OUTPUT: NULL

ROWCOUNT_BIG(): Returns the number of rows affected by the last statement executed. If we use this after a select statement it will return us the number of rows the select statement has returned.

            SELECT * FROM EMP

            SELECT ROWCOUNT_BIG FROM EMP

APP_NAME(): Returns the name of the application from where the statement is executed.

            SELECT APP_NAME()

CASE: Evaluates a list of conditions and returns one of multiple possible result expressions. It has two formats:

-The simple CASE function compares an expression to a set of simple expressions to determine the result.

-The searched CASE function evaluates a set of Boolean expressions to determine the result.

- Both formats support an optional ELSE argument.

CASE <expression>

                        WHEN when_expression THEN result_expression

                        WHEN when_expression THEN result_expression

…………………………       

ELSE else_result_expression

END

-In this case if the expression matches with any of the when_expression it returns the corresponding result_expression, if it does not match with any then it returns else_result_exression.

            SELECT EMPNO, ENAME, SAL, JOB,

(CASE JOB

WHEN ‘PRESIDENT’ THEN ‘BIG BOSS’

WHEN ‘MANAGER’ THEN ‘BOSS’

WHEN ‘ANALYST’ THEN ‘SCIENTIST’

ELSE ‘EMPLOYEE’

END) AS COMMENTS FROM EMP

            SELECT EMPNO, ENAME, JOB, SAL,

            (CASE SIGN(SAL-3000)

            WHEN 1 THEN ‘ABOVE TARGET’

            WHEN 0 THEN ‘ON TARGET’

            WHEN –1 THEN ‘BELOW TARGET’

            END) AS COMMENTS FROM EMP

-The above statement can be written in one more way also by using the second format of the CASE function.

CASE

WHEN condition THEN result_expression

WHEN condition THEN result_expression

 …………………………      

                        ELSE else_result_expression

END

            SELECT EMPNO, ENAME, JOB, SAL,

            (CASE

            WHEN SAL>3000 THEN ‘ABOVE TARGET’

            WHEN SAL=3000 THEN ‘ON TARGET’

            WHEN SAL<3000 THEN ‘BELOW TARGET’

            END) AS COMMENTS FROM EMP

<<< Previous                                                                                                                              Next >>>

Sql Server material 12

Date and Time Functions: The following functions perform an operation on a date and time input value and return a string, numeric, or date and time value.

GETDATE(): Returns the current date and time of the server in SQL Server standard internal format.

            SELECT GETDATE()

DAY(date): Returns an integer representing the DAY of the specified date, which has to be specified in standard SQL Server date format ‘mm/dd/yy’.

            SELECT DAY(GETDATE())

            SELECT DAY(‘10/24/78’)                                        OUTPUT: 24

MONTH(date): Returns an integer representing the MONTH of the specified date, which has to be specified in standard SQL Server date format ‘mm/dd/yy’.

            SELECT MONTH(GETDATE())

            SELECT MONTH(‘10/24/78’)                                   OUTPUT: 10

YEAR(date): Returns an integer representing the YEAR of the specified date, which has to be specified in standard SQL Server date format ‘mm/dd/yy’.

            SELECT YEAR(GETDATE())

            SELECT YEAR(‘10/24/78’)                                      OUTPUT: 1978

DATENAME(datepart, date): Returns a character string representing the specified datepart of the specified date, datepart is the parameter that specifies the part of the date to return. The following table lists dateparts and abbreviations recognized by Sql Server:

Datepart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw
hour	hh
minute	mi, n
second	ss, s
millisecond	ms

SELECT DATENAME(mm, ‘10/24/78’)                   OUTPUT: October

SELECT DATENAME(dd, ‘10/24/78’)                    OUTPUT: 10

DATEPART(datepart, date): This is same as DATENAME function but the only difference is weekday (dw) of DATEPART function returns a number that corresponds to the day of the week, for example: Sunday = 1, Saturday = 7, where as in the case of DATENAME returns the value in string format that is Sunday, Monday, … Saturday.

DATEADD(datepart, number, date): Returns a new datetime value based on adding an interval to the specified date, datepart is the value that has to be added and number is the interval.

            SELECT DATEADD(dd, 30, GETDATE()) –Adds 30 days to GETDATE().

            SELECT DATEADD(mm, 16, GETDATE()) –Adds 16 months to GETDATE().

DATEDIFF(datepart, startdate, enddate): Returns the difference between the start and end dates in the give datepart format.

            SELECT DATEDIFF(yy, ‘10/24/78’, GETDATE())

GETUTCDATE()-Returns the datetime value representing the current UTC time (Coordinated Universal Time or Greenwich Mean Time).

            SELECT GETUTCDATE()

Conversion Functions: Explicitly converts an expression of one data type to another. We has two conversion functions CAST and CONVERT, both provide similar functionality.

Syntax for CAST:

CAST ( expression AS data_type [ (length ) ])

            SELECT CAST(10.6496 AS INT)                             OUTPUT: 10

SELECT CAST(10.3496847 AS money)                   OUTPUT: 10.3497

Syntax for CONVERT:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

            SELECT CONVERT(INT, 10.6496)                         OUTPUT: 10

            SELECT CONVERT(VARCHAR(50), GETDATE())

Style is an optional parameter that can be used to specify a date format used to convert datetime or smalldatetime data to character. When style is NULL, the result returned is also NULL. Style can be used as following:

            SELECT CONVERT(VARCHAR(50), GETDATE(), 101)

            SELECT CONVERT(VARCHAR(50), GETDATE(), 102)

-Each style will give the output of the date in a different format the default style it uses is 100. The style values can be ranging between 100-114, 120, 121, 126, 127, 130 and 131 or 0 to 8, 10, 11, 12 and 14 in this case century part will not returned.

            SELECT CONVERT(VARCHAR(50), GETDATE(), 1)

<<< Previous                                                                                                                        Next >>>

Sql Server material 11

String Functions: These functions perform an operation on a string input value and return a string or numeric value.

ASCII(s): Returns the ASCII code value of the leftmost character of the expression.

            ASCII(‘A’)                                                     OUTPUT: 65

            ASCII(‘BCD’)                                                            OUTPUT: 66

CHAR(n): Converts the given ASCII code to a character.

            CHAR(97)                                                      OUTPUT: a

NCHAR(n): Returns the Unicode character with the specified integer code ranging between 0 to 65, 535, as defined by the Unicode standard.

            CHAR(300)                                                    OUTPUT: Ĭ

CHARINDEX(search exp, string exp [ , start_location ] ): Returns the starting position of the search exp in the string exp which can also be a column name.

            CHARINDEX(‘O’, ‘HELLO WORLD’)                  OUTPUT: 5

-In this case it returns 5 as output because it starts its search from the beginning of the string, we can change it by using the start location optional parameter.

            CHARINDEX(‘O’, ‘HELLO WORLD’, 6)              OUTPUT: 8

-WAQ to get the details of employees whose name contains the character ‘M’ in it.

Sol: SELECT * FROM EMP WHERE CHARINDEX(‘M’, ENAME)>0

LEFT(s, n): Returns the left part of the string with the specified number of characters.

            SELECT LEFT(‘HELLO’, 3)                                                OUTPUT: HEL

-WAQ to get the details of employees whose name contains the first 2 characters as ‘VE’.

Sol: SELECT * FROM EMP WHERE LEFT(ENAME, 2)=’VE’

RIGHT(s, n): Returns the right part of the string with the specified number of characters.

            SELECT RIGHT(‘HELLO’, 3)                                 OUTPUT: LLO

-WAQ to get the details of employees whose name ends with characters ‘TT’.

Sol: SELECT * FROM EMP WHERE RIGHT(ENAME, 2)=’TT’

SUBSTRING(s, start, length): Returns a part of a string from string s starting from start position, where length is the no of chars to be picked.

            SELECT SUBSTRING(‘HELLO’, 1, 3)                   OUTPUT: HEL

            SELECT SUBSTRING(‘HELLO’, 3, 3)                   OUTPUT: LLO

            SELECT SUBSTRING(‘HELLO’, 2, 3)                   OUTPUT: ELL

-WAQ to get the details of employees whose names 3^rd and 4^th characters are ‘TI’.

Sol: SELECT * FROM EMP WHERE RIGHT(LEFT(ENAME, 4), 2)=’TI’

Sol: SELECT * FROM EMP WHERE SUBSTRING(ENAME, 3, 2)=’TI’

LEN(s): Returns the number of characters of the specified string expression, excluding trailing blanks.

            SELECT LEN(‘HELLO’)                                          OUTPUT: 5

            SELECT LEN(‘   HELLO’)                                       OUTPUT: 8

-WAQ to get the details of employees whose names was 5 characters in length

Sol: SELECT * FROM EMP WHERE LEN(ENAME)=5

            SELECT LEN(‘HELLO   ‘)                                       OUTPUT: 5

LOWER(s): Returns a character expression after converting the given character data to lowercase.

            SELECT LOWER(‘Hello’)                                        OUTPUT: hello

UPPER(s): Returns a character expression after converting the given character data to uppercase.

            SELECT UPPER(‘Hello’)                                          OUTPUT: HELLO

LTRIM(s): Returns a character expression after it removes leading blanks.

            SELECT LEN(LTRIM(‘   HELLO’))                                    OUTPUT: 5   

            SELECT 'HELLO ' + LTRIM(' WORLD')                OUTPUT: HELLO WORLD

RTRIM(s): Returns a character expression after it removes trailing blanks.

            SELECT RTRIM('HELLO ') + ' WORLD'               OUTPUT: HELLO WORLD

REPLACE(s1, s2, s3): Replaces all occurrences of the s2 in s1 with s3.

            SELECT REPLACE(‘HELLO’, ‘L’, ‘X’)                 OUTPUT: HEXXO

REPLICATE(s, n): Repeats the expression ‘s’ for specified ‘n’ number of times.

            SELECT REPLICATE(‘HEL’, 2)                             OUTPUT: HELHEL

REVERSE(s): Returns the reverse of the given string ‘s’.

            SELECT REVERSE(‘HELLO’)                                OUTPUT: OLLEH

SOUNDEX(s): Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings. SOUNDEX converts an alphanumeric string to a four-character code to find similar-sounding words or names. The first character of the code is the first character of strings and the second through fourth characters of the code are numbers.

            SELECT SOUNDEX ('Smith'), SOUNDEX ('Smyth')

-Generally we use then when we perform comparison of words, which are sounded in the same way but have different spelling like color & colour. Suppose in a table the ename of a person is smith we will get the result even if the statement is written as following:

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

DIFFERENCE(S1, S2): Returns an integer value that indicates the difference between the SOUNDEX values of two character expressions. The return value ranges from 0 through 4: 0 indicates weak or no similarity, and 4 indicates strong similarity or the same values.

SELECT SOUNDEX(‘SMITH’), SOUNDEX('SMYTH'),

DIFFERENCE('SMITH','SMYTH')

           

SPACE(n): Returns a string with specified ‘n’ number of repeated spaces.

            SELECT ‘HELLO’ + SPACE(1) + ‘WORLD’         OUTPUT: HELLO WORLD

STUFF(s, start, length, replace_str): Replaces specified length of characters from specified starting point with replace_str in the string ‘s’

            SELECT STUFF(‘ABXXCDXX’, 3, 3, ‘YY’)         OUTPUT: ABYYDXX

<<< Previous                                                                                                                          Next >>>

Sql Server material 10

POWER(n, m): Returns the value of the specified expression n to the specified power m.

            SELECT POWER(10, 3)                                OUTPUT: 1000

RAND ( [SEED] ): Returns a random float value from 0 through 1.

-          SEED: Is an integer expression that gives the seed value. If seed is not specified, the Database Engine assigns a seed value at random. For a specified seed value, the result returned is always the same.

SELECT RAND()      -Each time we execute we get a random value.

SELECT RAND(100)            -Each time we execute we get the same value.                      

ROUND ( n , length [ ,function ] ): Returns a numeric expression, rounded to the specified length or precision.

            SELECT ROUND(156.567, 2)                      OUTPUT: 156.57

            SELECT ROUND(156.567, 1)                      OUTPUT: 156.6

            SELECT ROUND(156.567, 0)                      OUTPUT: 157

-If the seed is positive rounding will be done after the decimal, if it is negative rounding will be done before the decimal:

            SELECT ROUND(156.567, -1)                     OUTPUT: 160

            SELECT ROUND(156.567, -2)                     OUTPUT: 200

-If we specify the optional parameter function that is an integer value we can decide to truncate the value or round the value. If it is 0 (default) rounds the value and value greater than 0 truncates the value.

            SELECT ROUND(156.567, 2, 1)                  OUTPUT: 156.56

            SELECT ROUND(156.567, -2, 1)                 OUTPUT: 100

SIGN(n):  Returns the positive (+1), zero (0), or negative (-1) sign of the specified expression.

-          If n<0 it returns -1

-          If n=0 it returns 0

-          If n>0 it returns 1

SELECT SIGN(-100)                                     OUTPUT: -1

SELECT SIGN(0)                                          OUTPUT: 0

SELECT SIGN(100)                                      OUTPUT: 1

SQRT(n): Returns the square root of the specified expression.

            SELECT SQRT(81)                                        OUTPUT: 9

SELECT SQRT(30)                                        OUTPUT: 5.47722557505166

SQUARE(n): Returns the square of the specified expression.

            SELECT SQUARE(35)                                 OUTPUT: 1225

-Apart from the above it provides with trigonometric function like COS, COT, SIN, TAN, ACOS, ASIN, ATAN for which we need to provide the degrees.

<<< Previous                                                                                                                          Next >>>