sql server lab_2

Adavnced Database Programming 1

MS SQL Server MS SQL Server 20052005

Lab # 2 : SQL: Guide to the Language (Components, Data types, Functions) Installing Practicing Example


SQL Basic Objects

1.1. Constants or Literal Values:Constants or Literal Values: AlphanumericalAlphanumerical Numeric constant:Numeric constant: Integer, Fixed-Point, Float-

Point Values Hexadecimal:Hexadecimal:

Used to represent nonprintable characters Each Starts with “0x” followed by even number of

characters or numbers. EX. 0x53514C0D


SQL Basic Objects

1.1. Constants or Literal Values:Constants or Literal Values: Characters string: Characters string:

enclosed in single (preferred) OR double quotation To print single quotation in a statement add a single

quotation to form Consecutive single quotations . EX. ‘You Can’’t agree’

Double quotation allows :Double quotation allows : Delimited Identifiers: reserved keywords used as

identifiers by using double quotation (helps to protect you from using names that could be introduced as reserved keywords in the SQL future), their name can begin with Or Contain any character

Spaces in the names of database objects


Setting Delimited Identifier Usage Option SET QUOTED_IDENTIFIER {ON, OFF} IF :

ON: Quotations can be used for delimiting Identifiers Quotations can’t be used for delimiting Strings

OFF: Quotations can’t be used for delimiting Identifiers Quotations can be used for delimiting Strings


SQL Basic Objects

2.2. Delimiters:Delimiters: ’ “ [ ] & ….

3.3. Comments:Comments: /*/* Several Comment Liens Here */*/ ---- the Reminder of the Current Line is Comment

4.4. Reserved KeywordsReserved Keywords Can’t be used as object names


SQL Basic Objects

5.5. Identifiers:Identifiers: Used to identify DB Objects such as: tables &

Indices Names can be created with:

up to 128 characters: containing letters, numerals, _, @, #, $

Each name must start with a letter or one of the following characters (_, @, #, $)

## at the beginning of table or stored procedure name denotes a temporary object

@@ at the beginning of name denotes a Variable


Data Types

NumericNumeric Data Types Numbers StringString Data Types set of characters Date and/or TimeDate and/or Time Data Types Derived DataDerived Data Types Derived from simple

data types MiscellaneousMiscellaneous Data Types Data Types that

doesn’t belong to other Data Types User-DefinedUser-Defined Data Types


Numeric Data Types (10 Types)Data TypeData Type ExplanationExplanation

INTINT integer(4 Bytes)

SMALLINTSMALLINT integer(2 Bytes)

TINYINTTINYINT Non-Negative Integer (0255)(1 Byte)

BIGINTBIGINT integer(8 Bytes)

DECIMAL(p,[s])DECIMAL(p,[s]) P=precison(Total no of digits with S)

S=Scale(No. of Decimal Point Digits)

REALREAL Floating-Point Values

MONEY-MONEY- SMALLMONEYSMALLMONEY

Decimal(8 Bytes), Rounded to 4 digits after the decimal point - (4 Bytes)

Float[(p)]Float[(p)] P<25(4 Bytes), P>=25(8 Bytes)


String Data Types (6 Types)Data TypeData Type ExplanationExplanation

CHAR[(n)]CHAR[(n)] OR CHARACTER [(n)]CHARACTER [(n)]

String of n Characters, n Maximum value=800, if n is omitted n=1

VARCHAR [(n)]VARCHAR [(n)] String of (0<n<=8000) Characters

NCHAR [(n)]NCHAR [(n)] Fixed-length Unicode Char data(each char stored in 2 Byte), MAX=4000

NVARCHAR [(n)]NVARCHAR [(n)] Varying-length Unicode Char data (each char stored in 2 Byte), MAX=4000

TEXT [(n)]TEXT [(n)] Fixed Length String up to 2GB(each char stored in 1 Byte)

NTEXT [(n)]NTEXT [(n)] Large character data, MAX Bytes=230

(each char stored in 2 Byte)


Binary Data Types (4 Types)Data TypeData Type ExplanationExplanation

BINARY [(n)]BINARY [(n)] A bit String of fixed length with exactly n bytes (0<n<=8000)

VARBINARY [(n)]VARBINARY [(n)] A bit String of Variable length with exactly n bytes (0<n<=8000)

IMAGE [(n)]IMAGE [(n)] A bit String of fixed length with Unlimted values (231 Bytes)

Can contain(audio/video, modules

BitBit Boolean Data type(True, False, NULL)

TEXT/ImageTEXT/Image Contain any image type With Text


Maximizing Data Types with MAX Where MAX=2Where MAX=23030 VARCHAR(MAX) NVARCHAR(MAX) VARBINARY(MAX)


Date & Time Data Types (2 Types)Data TypeData Type ExplanationExplanation

DATETIMEDATETIME Date & Time Stored in (4 Bytes)

SMALLDATETIMESMALLDATETIME Date & Time Stored in (2 Bytes)

Date ( MM DD YYYY )

EX. “January 1 2007” OR ‘January 1 2007’

‘1959 MAY 28’

Using SET DATEFORMAT dmy

Time ( hh:mm AM or PM)

EX. 8:45 AM, 4 pm


Derived Data Types (2 Types)Data TypeData Type ExplanationExplanation

TIMESTAMPTIMESTAMP Maintains a current value for each DB which increments whenever any row with a timestamp column is inserted or updated

Used to determine the relative time when rows where last changed

SYSNAMESYSNAME Name of DB objects in the system catalog


Miscellaneous Data Types

Cursor:Cursor: Used in stored procedure UNIQUEIDENTIFIER:UNIQUEIDENTIFIER: for Data Replication SQL_VARIANT:SQL_VARIANT: Store Values of Different Data

Types TABLE:TABLE:

Store Rows with several & different values SQL Server Restrict its usage in: Local Variable &

User-Defined Functions XML:XML: Store XML Document in a DB


Predicates

A logical condition being applied to rows in a table with Values of : True, False, unknown or Not applicable All Relational Operators BETWEENBETWEEN Operator ININ Operator LIKELIKE Operator NULLNULL Operator ALLALL and ANYANY Operator EXISTSEXISTS function


Aggregate Functions

Applied to a group of data values from different rows, and returns a SINGLE value AVG:AVG: Applied on numeric values Only MAX,MIN:MAX,MIN: Applied on numeric, String, Date/Time

values SUM:SUM: Applied on numeric values COUNTCOUNT: Counts number of non-null data values

in a column (only Function not applied to a Column, its applied to rows)

COUNT_BIG:COUNT_BIG: Returns same as COUNT but in BIGINT data type


Scalar Functions

Used to construct Scalar expressions (operates on a single or list of values) Numeric Functions Date Functions String Functions Text/Image Functions System Functions


Scalar Functions: Numeric FunctionsFunctionFunction ExplanationExplanation FunctionFunction ExplanationExplanation

LOG (n)LOG (n) Logarithm of n EXP(n)EXP(n) =en

ACOS(n)ACOS(n) Arc cosine,Float

COS(n)COS(n) Cosine,Float

ASIN(n)ASIN(n) Arc sine,Float SIN(n)SIN(n) sine,Float

ATAN(n)ATAN(n) Arc tangent,Float

Tan(n)Tan(n) tangent,Float

CEILING(n)CEILING(n) Smallest integer value greater or equal to n

Floor(n)Floor(n) Largest integer value less or equal to n

DEGREES(n)DEGREES(n) Converts radians to degrees

Radians(Radians(n)n)

Converts degrees to radians


Scalar Functions: Numeric FunctionsFunctionFunction ExplanationExplanation FunctionFunction ExplanationExplanation

Power (x,n)Power (x,n) =x n Sign(n)Sign(n) Return -1 for negative & +1 for positive

PI()PI() Return (3.14) ABS(n)ABS(n) Absolute Value

RANDRAND Returns random between 0 - 1 (float)

SQUARESQUARE(n)(n)

SQRT(n)SQRT(n)

=n2

=√n


Scalar Functions: Date FunctionsFunctionFunction ExplanationExplanation

GETDATE()GETDATE() Returns system Date & Time

DATEPART(DATEPART(item,date)item,date)

Returns specified item of a dateEx. Select datepart(weekday,’01.01.2005’)=1

DATENAMEDATENAME(item,date)(item,date)

Returns specified item of a date as a character string, Ex. Select datename(weekday,’01.01.2005’)=Saturday

DATEDIFFDATEDIFF

(item,dat1,(item,dat1,

dat2)dat2)

Returns the difference between dat1 & dat2 in integer Num of item

Ex. Select datediff(year,birthday,getdate())

DATEADD(itDATEADD(item,number,em,number,date)date)

Adds the number of units itemto date

Ex. Select dateadd(day,3,birthdate) AS Age from Employee


Scalar Functions: String FunctionsFunctionFunction ExplanationExplanation

ASCII (ch)ASCII (ch) Equivalent ASCII code of ch

CHAR(integer)CHAR(integer) Equivalent Character of ASCII integer

LOWER(z)LOWER(z) Convert all uppercase to lowercase

Ex. Select lower(“BIG”)=big

UPPER(Z)UPPER(Z) Convert all lowercase to uppercase

Ex. Select upper(“big”)=BIG

RIGHT(z,length)RIGHT(z,length) Returns length characters from z

Ex. Select right(“notebook”,4)=book


Scalar Functions: Text/Image Functions PATINDEX (%pattern%,expr)

Returns an integer value specifying the position of the string pattern in expr

Ex. Select patindex(%gs%,’longstring’)=4


Scalar Operators

~ NOT & AND | OR ^ XOR


Installing SQL Server 2005 . . . Installing Default Server Installing Multi other Instances(Named

Instances)

sql server lab_2

Documents