Informatico

Microsoft SQL Server 2008
Course 2778A
Data Types:














varchar(50)
nvarchar(50) --> for Unicode
char() -->specific limit
Text --> no limit, all kinds of Text
varchar(max) --> no limit
date
time
binary --> for Images
int --> numbers 1,2,3,4,5,6,7,8,9
big int --> 8 bytes
tiny int --> 3 numbers Ex: 123, 835, 367
decimal(-,-) Ex: (5,2) = 5 numbers atall, 2 numbers after the sign
money --> for money Ex: (10,4) 10 numbers at all, 4 numbers after the sign

Note: Put n before any data type to support Unicode Ex: nvarchar (30)

SQL Languages
DDL - Data Definition Language:
CREATE, ALTER, DROP, TRUNCATE
TRUNCATE: delete all rows in the table and format it, it is faster than DELETE and
also when you enter new data in the table afterTRUNCATE it'll start ID from 1, not
like DELETE which starts the new data after the Last ID deleted .
CREATE DATABASE Lab203
CREATE TABLE Instructors(Ins_ID int primary key identity,
Ins_Name nvarchar(200), Ins_Phone varchar(14), Ins_DOB date)
ALTER TABLE Instructors ADD Ins_Title nvarchar(50)
ALTER TABLE Instructors ALTER COLUMN Ins_Mobile nvarchar(50)

DML- Data Manipulation Language -->INSERT, DELETE, UPDATE, SELECT
Course 2778 - Writing Queries Using Microsoft SQL Server 2008 Transact-SQL - 1
MCIT Scholarship 2010-11 – Database Development and Administration – YAT Heliopolis - 203

DCL - Data Control Language --> GRANT, REVOKE
TCL - Transactional Control Language --> COMMIT, ROLLBACK, SAVEPOINT
DQL - Documentum/Data Query Language --> SELECT

Operators:
ArithmeticOperators:
+-/*%
* and / have much priority than + and 5+8-10/2+4*6

5%2=1
Level 1: / * %
Level 2: + Assignment Operators:
=
Comparison Operators:

Course 2778 - Writing Queries Using Microsoft SQL Server 2008 Transact-SQL - 2
MCIT Scholarship 2010-11 – Database Development and Administration – YAT Heliopolis - 203

, , >=, for showing the time now (system clock)

Control of flow:BEGIN..END, BREAK, CONTINUE, GOTO, IF...ELSE, RETURN, TRY...CATCH, WAITFOR, WHILE

Variables:


Ex1:
DECLARE @X int
SET @X=5
DECLARE @Y nvarchar(5)
SET @Y='Ahmed'
IF @Y='Ahmed'
BEGIN
SELECT @X+5
END



Ex2:

Course 2778 - Writing Queries Using Microsoft SQL Server 2008 Transact-SQL - 3
MCIT Scholarship 2010-11 – Database Development and Administration – YAT Heliopolis - 203DECLARE @Degree int
SET @Degree=100
IF @Degree>110
BEGIN
SELECT'Pass'
END
ELSE
BEGIN
SELECT'Fail'
END

Note: dbo
SELECT*From dbo.Instructors

--> dbo= database owner

WHERE
UPDATE Instructors
SET Ins_Sal=Ins_Sal+100 WHERE Ins_ID=1
SELECT*From Instructors
WHERE Ins_Sal=1600
SELECT*From Instructors
WHERE Ins_Sal IN(1,2,3)

Note: WHERE doesn't come with INSERT

NOT BETWEEN, NOTIN , !=, !<

NULL
SELECT * From Instructors
WHERE Ins_Phone is NULL
NOT NULL
SELECT*From Instructors WHERE Ins_Phone is NOT NULL

IS NULL
SELECT Ins_ID, Ins_Name, Ins_Phone, Ins_DOB, Ins_Sal,
Ins_Dept,

Course 2778 - Writing Queries Using Microsoft SQL Server 2008 Transact-SQL - 4
MCIT Scholarship 2010-11 – Database Development and Administration – YAT Heliopolis - 203

ISNULL(Ins_Main, N'Not Determine Yet') AS Ins_Main From
Instructors
OR (for Arabic write N)
SELECT Ins_ID, Ins_Name, Ins_Phone, Ins_DOB, Ins_Sal,
Ins_Dept,
ISNULL (Ins_Main, N'‫ )'بعد يحدد لم‬AS Ins_Main From Instructors

NULL IF
SELECT Ins_Name, NULLIF(Ins_Sal,1000) From dbo.Instructors

COALESCE --> Returns the first Not Null Expression among its arguments
SELECT COAlESCE(Ins_Phone,Ins_Mobile)From Instructors

DISTINCT --> Returns unrepeated data from table
Ex: 1000,1000,1200,1200,1400 will appear like this: 1000,1200,1400
SELECT DISTINCT Ins_Sal From Instructors

GROUP BY --> grouping data
SELECT SUM(Ins_Sal)As Salary From Instructors GROUPBY Ins_Main

ROLLUP
SELECT Ins_Dept, Ins_Main,MAX(Ins_Sal)AS Salary From
Instructors
GROUP BY ROLLUP (Ins_Dept, Ins_Main)

CUBE...