DataBase Info

Sql Server material 20

2012-05-08T02:39:00.001-07:00

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)

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)

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)

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

2012-05-08T02:37:00.000-07:00

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_listspecifies 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

2012-05-08T02:29:00.000-07:00

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

2012-05-08T02:27:00.000-07:00

-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’

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]%’

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

2012-05-08T02:22:00.000-07:00

-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

2012-05-05T02:01:00.000-07:00

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

2012-05-05T01:58:00.001-07:00

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

2012-05-05T01:55:00.000-07:00

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

…………………………

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

…………………………

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

2012-05-05T01:52:00.000-07:00

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 smalldatetimedata 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

2012-05-05T00:45:00.000-07:00

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

2012-05-04T02:17:00.001-07:00

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 >>>

Sql Server material 9

2012-05-04T02:14:00.000-07:00

FUNCTIONS: SQL Server 2005 provides built-in functions that can be used to perform certain operations. Functions can be used or included in the following:

- The select list of a query that uses a SELECT statement to return a value.

- A WHERE clauses search condition of a SELECT statement to limit the rows that qualify for the query.

Syntax for executing a function:

SELECT <Fun Name> ( [ <expressions> ] )

-The expression can be a constant values or a name of a column.

Functions can be classified into 2 types:

-Single Row Functions

-Group Functions

A single row function executes once for each row that is present in the table where as group functions take multiple rows into consideration and returns a single value as output.

Single Row Function Categories:

-Mathematical Functions

-String Functions

-Date and Time Functions

-System Functions

Mathematical Functions: These functions perform a calculation, usually based on input values that are provided as arguments, and return a numeric value; they take “n” as input where n is a numeric expression.

ABS (n): A mathematical function that returns the absolute (positive) value of the specified numeric expression.

Select ABS(10) Ouput: 10

Select ABS(-10) Ouput: 10

CEILING (n): Returns the smallest integer greater than, or equal to, the specified numeric expression.

SELECT CEILING(15.6) OUTPUT: 16

SELECT CEILING(15.6) OUTPUT: -15

CEILING (n): Returns the largest integer less than or equal to the specified numeric expression.

SELECT FLOOR(15.6) OUTPUT: 15

SELECT FLOOR(15.6) OUTPUT: -16

LOG (n): Returns the natural logarithm of the specified expression, i.e. base-e

SELECT LOG(10) OUTPUT: 2.30258509299405

LOG10 (n): Returns base-10 logarithm of the specified expression, i.e. base e

SELECT LOG10(10) OUTPUT: 1

PI(): Returns the constant value of PI.

SELECT PI() OUTPUT: 3.14159265358979

<<<Previous Next >>>

Sql Server material 8

2012-05-04T02:11:00.000-07:00

Alter Command: After creating a table if we want to make any modifications to the structure of the table we use the Alter Command. Using alter command we can perform the following:

-Increase/Decrease the width of a column.

-Change the data type of a column.

-Change Null to Not Null and Not Null to Null

-Add a new column to the table.

-Drop an existing column from the table.

-Add a constraint to a column of the table.

-Drop an existing constraint present on a column from the table.

-To perform the first 3 operations the syntax is:

ALTER TABLE <TNAME> ALTER COLUMN <COLNAME> <DTYPE> [WIDTH] [NULL | NOT NULL]

First create a table as following:

CREATE TABLE Students (SNO int, Sname varchar(50), Class int)

Increasing the width of a column:

ALTER TABLE Students ALTER COLUMN Sname varchar(100)

Decreasing the width of a column:

ALTER TABLE Students ALTER COLUMN Sname varchar(25)

Changing the data type of the column:

ALTER TABLE Students ALTER COLUMN Sname nvarchar(25)

Adding a Not Null Constraint:

ALTER TABLE Students ALTER COLUMN Sname nvarchar(25) Not Null

Removing a Not Null Constraint:

ALTER TABLE Students ALTER COLUMN Sname nvarchar(25) Null

Syntax to add a new column:

ALTER TABLE <TNAME> ADD <COLNAME> <DTYPE> [<WIDTH>]

[ [CONSTRAINT <CONS NAME>] <CONS TYPE> ]

Adding a column to the table with out any constraint:

ALTER TABLE Students ADD Fees Money

Adding a column to the table with a constraint:

ALTER TABLE Students ADD Sid int Constraint Sid_UQ UNIQUE

Syntax to drop an existing column:

ALTER TABLE <TNAME> DROP COLUMN <COLNAME>

Dropping the Sid Column:

ALTER TABLE Students DROP COLUMN Sid

Syntax to Add a Constraint:

ALTER TABLE <TNAME> ADD [ CONSTRAINT <CON NAME> ] <CONS TYPE> (COLLIST)

Adding a check constraint on the Fees column:

ALTER TABLE Students ADD Constraint Fees_CK Check (Fees>1500)

Adding a primary key constraint on the Sno column:

ALTER TABLE Students ALTER COLUMN SNO INT NOT NULL

ALTER TABLE Students ADD Constraint Sno_PK Primary Key(SNO)

Syntax to Drop a Constraint:

ALTER TABLE <TNAME> DROP CONSTRAINT <CONS NAME>

Dropping the check constraint present on the Fees column:

ALTER TABLE Students DROP Constraint Fees_CK

Drop Command: If we want to destroy the existing tables present in the database we use the Drop Command.

Syntax: DROP TABLE <TNAME>

Dropping the Students Table:

DROP TABLE Students

Truncate Command: Removes all rows from a table. TRUNCATE TABLE is functionally the same as the DELETE statement with no WHERE clause specified.

Syntax: TRUNCATE TABLE <TNAME>

Truncating the EMP Table:

TRUNCATE TABLE Students

The difference between Truncate and Delete is:

Truncate table is faster in execution.
Truncate will reset the identity function if present on the table to initial value again which will not happen in delete.

<<< Previous Next >>>

Sql Server material 7

2012-05-04T02:06:00.000-07:00

Foreign Key Constraint:it is a column or combination of columns that is used to establish and enforce a link between the data in two tables. In a foreign key reference, a link is created between two tables when the column(s) in a table reference the column(s) that hold the primary key of other table, which becomes a foreign key in the first table.

For example, the Dept.Deptno table below has a link to the Emp.Deptnotable because there is a logical relationship between Dept table and Emptable. The Deptno column in the Emp table matches the primary key column of the Dept table. The Deptno column in the Emptable is the foreign key to the Dept table. In this case the value that is going to be inserted into the Deptno column of the Emp table should be present in the Deptno column of the Dept table or should be a null values.

- In this case the Dept table is called as Parent table and Emp table is called as Child table.

- Dept.Deptno is called as Reference Key column on which either Primary Key Constraint or Unique Constraint has to be imposed.

- Emp.Deptno is called as Foreign Key column on which the Foreign Key Constraint has to be imposed, with this only the link gets established between the 2 tables.

Create Table Dept (Deptno int Constraint Deptno_Pk Primary Key, DName varchar(50), Loc varchar(50))

1. Insert into Dept values (10, 'Marketing', 'Mumbai')

2. Insert into Dept values (20, 'Sales', 'Chennai')

3. Insert into Dept values (30, 'Finance', 'Delhi')

4. Insert into Dept values (40, 'Production', 'Kolkota')

-Creating Foreign Key Constraint in column level:

Create table Emp (Empno int, Ename varchar(100), Job varchar(100), Mgr int, HireDate datetime, Sal Money, Comm Money, Deptno int Constraint Deptno_Ref References Dept (Deptno))

-Creating Foreign Key Constraint in column level: while defining constraint in table level we need to explicitly use the Foreign Key clause to specify the Foreign Key Column:

Create table Emp (Empno int, Ename varchar(100), Job varchar(100), Mgr int, HireDate datetime, Sal Money, Comm Money, Deptno int,

Constraint Deptno_Ref Foreign Key (Deptno) References Dept (Deptno))

-Now when we try to insert values into the Emp table the Deptno what we give should be only the 4 values (10, 20, 30, 40) present in the dept table or a null value, if we try to insert any other value the insert statement fails.

1. Insert into Emp Values (1001, 'Suresh', 'President', NULL, '01/01/78', 5000, NULL, 10)

2. Insert into Emp Values (1002, 'Ramesh', 'Manager', 1001, '01/01/78', 4000, NULL, 20)

3. Insert into Emp Values (1003, 'Ravi', 'Manager', 1001, '01/01/78', 3500, NULL, 30)

4. Insert into Emp Values (1004, 'Vijay', 'Manager', 1001, '01/01/78', 4000, NULL, 40)

5. Insert into Emp Values (1005, 'Ajay', 'Salesman', 1003, '02/04/79', 3000, NULL, 50)

-In this case the first 4 statements gets executed but last statement fails because the Deptno given is not present in the dept table.

The Foreign Key constraint enforces referential integrity by guaranteeing that changes cannot be made to data in the primary key table if those changes invalidate the link to data in the foreign key table. If an attempt is made to delete the row in a primary key table or to change a primary key value, the action will fail when the deleted or changed primary key value corresponds to a value in the FOREIGN KEY constraint of another table. To successfully change or delete a row in a FOREIGN KEY constraint, you must first either delete the foreign key data in the foreign key table or change the foreign key data in the foreign key table, which links the foreign key to different primary key data.

By using cascading referential integrity constraints, you can define the actions that the SQL Server 2005 takes when a user tries to delete or update a key value in the master table to which existing foreign keys point.

The REFERENCES clauses of the CREATE TABLE statements support the ON DELETE and ON UPDATE clauses:

-ON DELETE <NO ACTION | CASCADE | SET NULL | SET DEFAULT>

-ON UPDATE <NO ACTION | CASCADE | SET NULL | SET DEFAULT>

NO ACTION is the default if ON DELETE or ON UPDATE is not specified.

ON DELETE NO ACTION: Specifies that if an attempt is made to delete a key value in the master table, which is referenced by foreign keys in other tables, an error is raised and the DELETE statement will not execute.

ON UPDATE NO ACTION: Specifies that if an attempt is made to update a key value in the master table, which is referenced by foreign keys in other tables, an error is raised and the UPDATE statement will not execute.

When NO ACTION is specified we can delete or update or delete rows in the master table if they are referenced by child table rows, but we can perform those operations when we use CASCADE, SET NULL AND SET DEFAULT CLAUSES:

ON DELETE CASCADE: Specifies that if an attempt is made to delete a key value in the master table, which is referenced by foreign keys in other tables, all rows that contain those foreign keys in child table are also deleted.

ON UPDATE CASCADE: Specifies that if an attempt is made to update a key value in the master table, which is referenced by foreign keys in other tables, all the foreign key values will also be updated to the new value specified for the key.

ON DELETE SET NULL: Specifies that if an attempt is made to delete a key value in the master table, which is referenced by foreign keys in other tables, all rows that contain those foreign keys in child table are set to NULL, provided the foreign key column allows NULL values into it.

ON UPDATE SET NULL: Specifies that if an attempt is made to update a key value in the master table, which is referenced by foreign keys in other tables, all rows that contain those foreign keys in child table are set to NULL, provided the foreign key column allows NULL values into it.

ON DELETE SET DEFAULT: Specifies that if an attempt is made to delete a key value in the master table, which is referenced by foreign keys in other tables, all rows that contain those foreign keys in child table are set to default value. All foreign key columns of the target table must have a default definition for this constraint to execute. If there is no explicit default value set, NULL becomes the implicit default value of the column.

ON UPDATE SET DEFAULT: Specifies that if an attempt is made to update a key value in the master table, which is referenced by foreign keys in other tables, all rows that contain those foreign keys in child table are set to default value. All foreign key columns of the target table must have a default definition for this constraint to execute. If there is no explicit default value set, NULL becomes the implicit default value of the column.

We can use any of the rules beside the column as following:

Deptno int Constraint Deptno_Ref References Dept (Deptno) on delete cascade on update cascade

In the same way you can use any rule there and also not mandatory to specify both the delete and update rule, we can use any rule what we require.

<<< Previous Next >>>

Sql Server material 6

2012-05-04T02:02:00.006-07:00

Primary Key Constraint.

-While Creating a primary key constraint we need to keep this in mind i.e. a table can contain only a single primary key present on it which can be present on a single column or multiple columns also.

Creating Primary Key in column level:

CREATE TABLE Bank(Custid int Constraint Cusid_PK Primary Key, Cname varchar(50), Bal decimal(7,2) Not Null)

Creating Primary Key in table level:

CREATE TABLE Bank(Custid int, Cname varchar(50), Bal decimal(7,2) Not Null, Constraint Cusid_PK Primary Key(Custid))

Creating a Composite Primary Key in table level:

CREATE TABLE BankDetails(CityCode varchar(10), BranchCode varchar(10), Constraint CC_BC_PK Primary Key(CityCode, BranchCode))

Check Constraint: If we want to check the values present in a column to be according to a specified value we use this constraint.

-If we want to restrict the Bal in the bank table should be some specified values then we can use the constraint as following:

CHECK (Bal>=1000) –> Checking Bal should be greater than equal to 1000

CHECK (Bal BETWEEN 1000 AND 9999) -> Checking Bal should be within the range of 1000 and 9999

CHECK (BAL IN (3000, 5000, 7000)) -> Checking the Bal Should be within any of the three values only

Creating Check Constraint in column level:

CREATE TABLE Bank(Custid int, Cname varchar(50), Bal decimal(7,2) Constraint Bal_CK Check (Bal>=1000))

Creating Check Constraint in table level:

CREATE TABLE Bank(Custid int, Cname varchar(50), Bal decimal(7,2), Constraint Bal_CK Check(Bal BETWEEN 1000 AND 9999))

Default Value: The default value for any column if a not null constraint is not present on it is “NULL”, which can be changed by using the Default Clause while creating the table as following:

CREATE TABLE Bank(Custid int, Cname varchar(50), Bal decimal(7,2) Default 1000)

-In the above case if have not specified any value to the Bal column while inserting then it takes 1000 as default

INSERT INTO Bank (Custid, Cname) VALUES (101, ‘Ravi’)

Identity Function: Generally for any column if we want to insert only unique values then we can hand over the task to the identity function, so that it takes the responsibility of inserting a unique value in to the column as following:

Colname <dtype> [width] Identity [(Seed, Incr)]

Seed – It is the starting value for the identity function.

Incr – It is the difference between to subsequent values generated by the function.

-Both of them are optional, if not specified 1 and 1 are taken as values.

-When we use the identity function on a column we cannot explicitly insert any values into the column using the insert statement.

-A table can have only one identity column present in it.

-Generally we use this on Primary Key Columns.

CREATE TABLE Bank(Custid int identity(101, 1), Cname varchar(50), Bal decimal(7,2))

-In this case when we insert rows into the table then it automatically generates a identity value starting from 101.

INSERT INTO Bank (Cname, Bal) VALUES (‘Raju’, 3500)

<<< Previous Next >>>

Sql Server material 5

2012-05-03T01:51:00.000-07:00

Constraints: used to enforce the integrity of the data in the columns, SQL Server 2005 provides the following mechanisms to enforce the integrity of the data in column:

-Not Null

-Unique

-Primary Key

-Check

-Default

-Foreign Key

Not Null: If it is imposed on a column that column will not allow Null Values into it; this can be imposed on any no of columns.

-CREATE TABLE <table_name>(

column_name1 <dtype> [width] [Not Null],

………………….

column_namen <dtype> [width] [Not Null])

Recreating the bank table with Not Null Constraint on it:

CREATE TABLE Bank(Custid int Not Null, Cname varchar(50), Bal decimal(7,2) Not Null)

After creating this if we try to insert a null value into the Custid or Bal columns it will restrict us:

INSERT INTO Bank VALUES (NULL, ‘RAJU’, 3500)

INSERT INTO Bank (CUSTID, CNAME) VALUES (101, ‘RAVI’)

The drawback with Not Null Constraint is even if it restricts null values it will not restrict duplicate values, if they has to be restricted we use the Unique Consraints.

Unique: If it is imposed on a column or columns they will not allow duplicate Values into it.

Note: Unique, Primary Key, Check and Foreign Key Constraints can be imposed in two different ways:

-Column Level Definition

-Table Level Definition

Column Level Definition: In this case the constraint definition is immediately followed after the column definition. The syntax is:

-CREATE TABLE <table_name>(

column_name1 <dtype> [width] [ [Constraint <Name>] <Type> ],

………………….

column_namen <dtype> [width] [ [Constraint <Name>] <Type> ],

Recreating the bank table with Unique Constraint on it:

CREATE TABLE Bank(Custid int Unique, Cname varchar(50), Bal decimal(7,2) Not Null)

After creating this if we try to insert a duplicate value into the Custid column it will restrict us:

INSERT INTO Bank VALUES (101, ‘RAJU’, 3500)

INSERT INTO Bank VALUES (101, ‘RAVI’, 4500)

Note: Internally Unique, Primary Key, Check and Foreign Key Constraints are identified by using some unique name which has to be given by us or else the system will give a name, so when we violate these constraints it will show the name of the constraint in the error message, by seeing which we require to identify on which column we are getting the problem, but if the table is not created by us or we don’t remember the structure of the table we cannot identify. So it is advised to give a name to the constraint so that when it violates the error message shows the name of the constraint using which we can easily identify where the violation has been done.

While giving a name to the Constraint they follow some conventions like:

<ColumnName_ConstraintType>

<TableName_ColumnName_ConstraintType>

Recreating the bank table with Unique Constraint by giving a name to it:

CREATE TABLE Bank(Custid int Constraint Cusid_UQ Unique, Cname varchar(50), Bal decimal(7,2) Not Null)

Table Level Definition: In this case the constraint definition is immediately followed after the column definition. The syntax is:

-CREATE TABLE <table_name>(

column_name1 <dtype> [width],

………………….

column_namen <dtype> [width],

[ [Constraint <Name>] <Type> (<Collist>)],

…………………….)

Note: A Not Null Constraint Cannot be defined table level.

CREATE TABLE Bank(Custid int, Cname varchar(50), Bal decimal(7,2) Not Null, Constraint Cusid_UQ Unique(Custid))

-In this case because the constraint is defined in the end of all the columns it cannot understand to which column the constraint depends so we need to specify the column name beside the constraint for identification.

-There will not be any difference in execution whether the constraint is defined in table level or column level.

-When we define a constraint in table level we can define composite constraint i.e. a single constraint on multiple columns.

CREATE TABLE BankDetails(CityCode varchar(10), BranchCode varchar(10), Constraint CC_BC_UQ Unique(CityCode, BranchCode))

INSERT INTO BankDetails Values(‘C1’, ‘B1’)

INSERT INTO BankDetails Values(‘C1’, ‘B2’)

INSERT INTO BankDetails Values(‘C1’, ‘B3’)

INSERT INTO BankDetails Values(‘C2’, ‘B1’)

INSERT INTO BankDetails Values(‘C2’, ‘B2’)

INSERT INTO BankDetails Values(‘C2’, ‘B3’)

-In this case all the statements are valid because a composite unique constraint checks the uniqueness on the combination of columns, but not on a single column.

The drawback with Unique Constraint is even if it restricts duplicate values it will allow a single null value in to the column. If we want to restricted duplicate values as well as null values we need to use Primary Key Constraint.

<<< Previous Next >>>

Sql Server material 4

2012-05-03T01:48:00.001-07:00

Updating data present in the tables: if we want to Update the data existing in the table we use

Update Statement:

Syntax:

-UPDATE <TNAME> SET <CNAME>=<VALUE> [, …..] [CONDITIONS]

Note: We can modify a single column or multiple columns using the update statement all the rows that satisfy the condition gets affected.

-UPDATE BANK SET CNAME=’RAMESH’ WHERE CUSTID=104

-UPDATE BANK SET CNAME=’RAJESH’, BAL=3000 WHERE CUSTID=105

Deleting data present in the tables: if we want to delete rows of data present in the table we use

Delete Statement:

Syntax:

-DELETE FROM <TNAME> [CONDITIONS]

-DELETE FROM BANK WHERE CUSTID=105

-DELETE FROM BANK

<<< Previous Next >>>

Sql Server material 3

2012-05-03T01:47:00.000-07:00

Populating Data into Tables: after the table gets created to populate the data into it we use

Insert Statement:

Syntax for Insert statement:

-INSERT INTO <table_name> [(col1, col2, ……..coln)]

VALUES (val1, val2, …….valn)

Examples:

INSERT INTO BANK VALUES (101, ‘VENKAT’, 4500)

-In this case we need to provide the values for all the columns in the same order they are present in the table.

-String and Date values have to be enclosed in single quotes.

INSERT INTO BANK (CUSTID, CNAME, BAL) VALUES (102, ‘SUBASH’, 5600)

-This statement is same as above statement.

-If we want to change the order of columns while inserting:

INSERT INTO BANK (CNAME, CUSTID, BAL) VALUES (‘SURESH’, 103, 6500)

-If we want to insert data only into required columns then:

INSERT INTO BANK (CUSTID, BAL) VALUES (104, 3600)

-In this case the columns into which values are not supplied are filled with Null value.

-We can also insert Null’s explicitly into the column in the following way:

INSERT INTO BANK VALUES (105, NULL, 5400)

Retrieving the data from Tables: if we want to retrieve the information from the table use

Select Statement:

Basic Syntax for Select statement:

SELECT < * | COLLIST > FROM <TNAME> [CONDITIONS]

- ‘*’ Represents all the columns of the table in the same order.

- COLLIST is used for specifying the required no of columns and in required order.

- CONDITIONS are optional which can be used for retrieving the required rows.

-SELECT * FROM BANK

-SELECT CUSTID, CNAME, BAL FROM BANK

-SELECT CNAME, BAL, CUSTID FROM BANK

-SELECT CUSTID AS ACCNO, CNAME, BAL FROM BANK

-We can specify an alias name for any required column while retrieving known as Column Alias.

-If we want to retrieve required rows then we use a conditional statement where:

-SELECT * FROM BANK WHERE CUSTID=104

-SELECT CUSTID, BAL FROM BANK WHERE CNAME=’SURESH’

-SQL Server does not have any case restrictions while writing the conditions.

Handling Null Values: The value NULL means the data value for the column is unknown or not available, so we cannot use equality condition while getting the data based on null values.

SELECT * FROM EMP WHERE CNAME=NULL

-The above statement will not get any result because no 2 null values can be compared so to get the data based on Null values we should use the IS NULL operator as following:

SELECT * FROM EMP WHERE CNAME IS NULL

<<< Previous Next >>>

Sql Server material 2

2012-05-03T01:44:00.000-07:00

Database:

SQL Server manages the objects in a container known as Database, where we can have multiple databases present in it, each database when created creates 2 files internally those or .mdf and .ldf file.

Syntax for creating a database:

-CREATE DATABASE <db_name>

-Database names must be unique within an instance of SQL Server.

-Any Object name in sqlserver can be of 1 through 128 characters

Tables:

-It is the object, which will store the information in the database in the form of rows and columns.

Syntax for creating a Table:

-CREATE TABLE <table_name>(

column_name1 <dtype> [width],

………………….

column_namen <dtype> [width])

-Table names must be unique within the database.

-Column names must be unique within the table.

-Every table can have maximum of 1024 and minimum of 1 column.

-CREATE TABLE Bank(Custid int, Cname varchar(50), Bal decimal(7,2))

<<< Previous Next >>>

Sql Server material 1

2012-05-03T01:42:00.000-07:00

Data Types

In a Database, each column, local variable, expression, and parameter has a related data type. A data type is an attribute that specifies the type of data that the object can hold: integer data, character data, monetary data, data and time data, binary strings, and so on.

Integer Types: To hold the Integer values it provides with tinyint, smallint, int and bigintdata types with sizes 1, 2, 4 and 8 bytes respectively.

Boolean Type: To hold the Boolean values it provides with bit data type that can take a value of 1, 0, or NULL.

Note: The string values TRUE and FALSE can be converted to bit values: TRUE is converted to 1 and FALSE is converted to 0.

Decimal Types: To hold the decimal values it provides with the following types:

-decimal[ (p[ , s] )] and numeric[ (p[ , s] )]

p (precision)

The maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.

s (scale)

The maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0.

Storage sizes of Decimal and Numeric types vary, based on the precision.

Precision	Storage bytes
1 – 9	5
10-19	9
20-28	13
29-38	17

Note: numeric is functionally equivalent to decimal.

-float [ ( n ) ] and real

-Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly. Where n is the number of bits that are used to store the mantissa of the float number in scientific notation and, therefore, dictates the precision and storage size. If n is specified, it must be a value between 1 and 53. The default value of n is 53.

n value	Precision	Storage size
1-24	7 digits	4 bytes
25-53	15 digits	8 bytes

Monetary or Currency Types: To hold the Currency values it provides with the following types which takes a scale of 4 by default:

money	-922,337,203,685,477.5808 to 922,337,203,685,477.5807	8 bytes
smallmoney	- 214,748.3648 to 214,748.3647	4 bytes

Date and Time Values: To hold the Date and Time values of a day it provides with the following types:

Data type	Range	Accuracy
datetime	January 1, 1753, through December 31, 9999	3.33 milliseconds
smalldatetime	January 1, 1900, through June 6, 2079	1 minute

Values with the datetime data type are stored internally by the Microsoft SQL Server 2005 Database Engine as two 4-byte integers. The first 4 bytes store the number of days before or after the base date: January 1, 1900. The base date is the system reference date. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.
The smalldatetime data type stores dates and times of day with less precision than datetime. The Database Engine stores smalldatetimevalues as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight.
String Values: To hold the string values it provides with the following types:

char [ ( n ) ]

Fixed-length, non-Unicode character data with a length of n bytes. n must be a value from 1 through 8,000. The storage size is n bytes.

varchar [ ( n | max) ]

Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. maxindicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes.

text

It was equal to varchar(max) this data type will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work use varchar(max) instead.

Unicode Data types for storing Multilingual Characters are nchar, nvarchar and ntext where n stands for national.

nchar [ ( n ) ]

Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. The storage size is two times n bytes.

nvarchar [ ( n | max) ]

Variable-length Unicode character data. n can be a value from 1 through 4,000. maxindicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes.

ntext

It was equal to nvarchar(max) this data type will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work use nvarchar(max) instead.

Binary Values: To hold the binary values likes images, audio clips and video clips we use the following types.

binary [ ( n ) ]

Fixed-length binary data with a length of n bytes, where n is a value from 1 through 8,000. The storage size is n bytes.

varbinary [ ( n | max) ]

Variable-length binary data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of the data entered + 2 bytes.

Image

It was equal to varbinary(max) this data type will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work use varbinary(max) instead.

Use char, nchar, binary when the sizes of the column data entries are consistent.
Use varchar, nvarchar, varbinary when the sizes of the column data entries vary considerably.
Use varchar(max), nvarchar(max), varbinary(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.

Other Types: Apart from the above it provides some additional types like -

timestamp: Is a data type that exposes automatically generated, unique binary numbers within a database. The storage size is 8 bytes. You can use the timestamp column of a row to easily determine whether any value in the row has changed since the last time it was read. If any change is made to the row, the timestamp value is updated. If no change is made to the row, the timestamp value is the same as when it was previously read.

Uniqueidentifier: Is a 16-byte GUID which is initialized by using the newid() function or converting a string constant in the form of xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx which is used to guarantee that rows are uniquely identified across multiple copies of the table.

Xml: Is the data type that stores XML data. You can store xml instances in a column, or a variable of xmltype. The stored representation of xml data type instances cannot exceed 2 gigabytes (GB) in size.

Next >>>

Convert between different numbering systems ,binary, octal, decimal and hex

2012-05-02T06:38:00.000-07:00

set serveroutput on

CREATE OR REPLACE PACKAGE dbms_numsystem AS

   function bin2dec (binval in char  ) RETURN number;

   function dec2bin (N      in number) RETURN varchar2;

   function oct2dec (octval in char  ) RETURN number;

   function dec2oct (N      in number) RETURN varchar2;

   function hex2dec (hexval in char  ) RETURN number;

   function dec2hex (N      in number) RETURN varchar2;

END dbms_numsystem;

show errors

CREATE OR REPLACE PACKAGE BODY dbms_numsystem AS

FUNCTION bin2dec (binval in char) RETURN number IS

  i                 number;

  digits            number;

  result            number := 0;

  current_digit     char(1);

  current_digit_dec number;

BEGIN

  digits := length(binval);

  for i in 1..digits loop

     current_digit := SUBSTR(binval, i, 1);

     current_digit_dec := to_number(current_digit);

     result := (result * 2) + current_digit_dec;

  end loop;

  return result;

END bin2dec;

FUNCTION dec2bin (N in number) RETURN varchar2 IS

  binval varchar2(64);

  N2     number := N;

BEGIN

  while ( N2 > 0 ) loop

     binval := mod(N2, 2) || binval;

     N2 := trunc( N2 / 2 );

  end loop;

  return binval;

END dec2bin;

FUNCTION oct2dec (octval in char) RETURN number IS

  i                 number;

  digits            number;

  result            number := 0;

  current_digit     char(1);

  current_digit_dec number;

BEGIN

  digits := length(octval);

  for i in 1..digits loop

     current_digit := SUBSTR(octval, i, 1);

     current_digit_dec := to_number(current_digit);

     result := (result * 8) + current_digit_dec;

  end loop;

  return result;

END oct2dec;

FUNCTION dec2oct (N in number) RETURN varchar2 IS

  octval varchar2(64);

  N2     number := N;

BEGIN

  while ( N2 > 0 ) loop

     octval := mod(N2, 8) || octval;

     N2 := trunc( N2 / 8 );

  end loop;

  return octval;

END dec2oct;

FUNCTION hex2dec (hexval in char) RETURN number IS

  i                 number;

  digits            number;

  result            number := 0;

  current_digit     char(1);

  current_digit_dec number;

BEGIN

  digits := length(hexval);

  for i in 1..digits loop

     current_digit := SUBSTR(hexval, i, 1);

     if current_digit in ('A','B','C','D','E','F') then

        current_digit_dec := ascii(current_digit) - ascii('A') + 10;

     else

        current_digit_dec := to_number(current_digit);

     end if;

     result := (result * 16) + current_digit_dec;

  end loop;

  return result;

END hex2dec;

FUNCTION dec2hex (N in number) RETURN varchar2 IS

  hexval varchar2(64);

  N2     number := N;

  digit  number;

  hexdigit  char;

BEGIN

  while ( N2 > 0 ) loop

     digit := mod(N2, 16);

     if digit > 9 then

        hexdigit := chr(ascii('A') + digit - 10);

     else

        hexdigit := to_char(digit);

     end if;

     hexval := hexdigit || hexval;

     N2 := trunc( N2 / 16 );

  end loop;

  return hexval;

END dec2hex;

END dbms_numsystem;

show errors

-- Examples:

select dbms_numsystem.dec2bin(22)      from dual;

select dbms_numsystem.bin2dec('10110') from dual;

select dbms_numsystem.dec2oct(44978)   from dual;

select dbms_numsystem.oct2dec(127662)  from dual;

select dbms_numsystem.dec2hex(44978)   from dual;

select dbms_numsystem.hex2dec('AFB2')  from dual;

Random number or string generator package

2012-05-02T06:36:00.001-07:00

create or replace package random

is

   procedure srand(new_seed in number);

   procedure get_rand(r OUT number);

   procedure get_rand_max(r OUT number, n IN number);

   function  rand return number;

   function  rand_max(n IN number) return number;

   function  rand_string(ssiz IN number) return varchar2;

   function  smaller(x IN number, y IN number) return number;

   pragma restrict_references(rand, WNDS);

   pragma restrict_references(rand_max, WNDS);

   pragma restrict_references(random, WNDS, RNPS);

   pragma restrict_references(rand_string, WNDS);

   pragma restrict_references(smaller, WNDS);

end random;

create or replace package body random

is

   multiplier   constant number := 22695477;

   increment    constant number := 1;

   "2^32"       constant number := 2 ** 32;

   "2^16"       constant number := 2 ** 16;

   "0x7fff"     constant number := 32767;

   Seed         number          := 1;

   function  smaller(x IN number, y IN number) return number is

   begin

        if x <= y then

            return x;

        else

            return y;

        end if;

   end smaller;

   function rand_string(ssiz IN number) return varchar2 is

     i      number;

     m      number;

     c      char;

     result varchar2(2000) := '';

   begin

        m := smaller(ssiz,2000);

        for i in 1..m loop

            c := substr('abcdefghijklmnopqrstuvwxyz0123456789',rand_max(36),1);

            result := result || c;

        end loop;

        return result;

   end rand_string;

   procedure srand(new_seed in number) is

   begin

     Seed := new_seed;

   end srand;

   function rand return number is

   begin

     Seed := mod(multiplier * Seed + increment, "2^32");

     return bitand(Seed/"2^16", "0x7fff");

   end rand;

   procedure get_rand(r OUT number) is

   begin

     r := rand;

   end get_rand;

   function rand_max(n IN number) return number is

   begin

     return mod(rand, n) + 1;

   end rand_max;

   procedure get_rand_max(r OUT number, n IN number) is

   begin

     r := rand_max(n);

   end get_rand_max;

begin

   select userenv('SESSIONID')

   into   Seed

   from   dual;

end random;

-- Some examples:

select random.rand_max(10) from dual;

select random.rand_max(10) from dual;

select random.rand_string(20) from dual;

select random.rand_string(20) from dual;

Function to test for Leap Years

2012-05-02T06:35:00.000-07:00

CREATE OR REPLACE FUNCTION isLeapYear(i_year NUMBER) RETURN boolean AS

BEGIN

  -- A year is a leap year if it is evenly divisible by 4

  -- but not if it's evenly divisible by 100

  -- unless it's also evenly divisible by 400

   IF mod(i_year, 400) = 0 OR ( mod(i_year, 4) = 0 AND mod(i_year, 100) != 0) THEN

      return TRUE;

   ELSE

      return FALSE;

   END IF;

END;

show errors

-- Let's test it

SET SERVEROUTPUT ON

BEGIN

  IF isLeapYear(2004) THEN

     dbms_output.put_line('Yes, it is a leap year');

  ELSE

     dbms_output.put_line('No, it is not a leap year');

  END IF;

END;

Print the ASCII table

2012-05-02T06:34:00.000-07:00

set serveroutput on size 10240

declare

   i number;

   j number;

   k number;

begin

   for i in 2..15 loop

       for j in 1..16 loop

           k:=i*16+j;

           dbms_output.put((to_char(k,'000'))||':'||chr(k)||'  ');

           if k mod 8 = 0 then

              dbms_output.put_line('');

           end if;

       end loop;

   end loop;

end;

show errors

Pass result sets ,REF CURSOR, between procedures and functions

2012-05-02T06:32:00.000-07:00

set serveroutput on

-- Define TYPES package separately to be available to all programming

-- environments...

CREATE OR REPLACE PACKAGE types AS

   TYPE cursortyp is REF CURSOR;   -- use weak form

END;

-- Create test package to demonstrate passing result sets...

CREATE OR REPLACE PACKAGE test_ref_cursor AS

   PROCEDURE main;

   FUNCTION  get_cursor_ref(typ NUMBER) RETURN types.cursortyp;

   PROCEDURE process_cursor(cur types.cursortyp);

END;

show errors

CREATE OR REPLACE PACKAGE BODY test_ref_cursor AS

  -- Main program entry point

  PROCEDURE main IS

  BEGIN

    process_cursor( get_cursor_ref(1) );

    process_cursor( get_cursor_ref(2) );

  END;

  -- Get and return a CURSOR REF/ Result Set

  FUNCTION get_cursor_ref(typ NUMBER) RETURN types.cursortyp IS

    cur  types.cursortyp;

  BEGIN

    if typ = 1 THEN

      OPEN cur FOR SELECT * FROM emp  WHERE ROWNUM < 5;

    ELSE

      OPEN cur FOR SELECT * FROM dept WHERE ROWNUM < 5;

    END IF;

    RETURN cur;

  END;

  -- Process rows for an EMP or DEPT cursor

  PROCEDURE process_cursor(cur types.cursortyp) IS

    empRec  emp%ROWTYPE;

    deptRec dept%ROWTYPE;

  BEGIN

    LOOP

      FETCH cur INTO empRec;    -- Maybe it was an EMP cursor, try to fetch...

      EXIT WHEN cur%NOTFOUND;

      dbms_output.put_line('EMP ROW: '||empRec.ename);

    END LOOP;

  EXCEPTION

    WHEN ROWTYPE_MISMATCH THEN  -- OK, so it was't EMP, let's try DEPT.

       LOOP

         FETCH cur INTO deptRec;

         EXIT WHEN cur%NOTFOUND;

         dbms_output.put_line('DEPT ROW: '||deptRec.dname);

       END LOOP;

  END;

END;

show errors

EXEC test_ref_cursor.main;