MIS 431

1
MIS 431

• Chapter 2.
• Elements ofTransact-SQL

2
Topics

• Data Definition Language (DDL)
• Data Manipulation Language (DML)
• Data Control Language (DCL)
• Extensions of Transact-SQL

3
Data Definition Language

• To create and manage databases and database
  objects
• CREATE , DROP , ALTER
• DATABASE
• TABLE
• PROCEDURE
• FUNCTION
• TRIGGER
• VIEW
• DEFAULT
• INDEX
• RULE
• STATISTICS
• and others

4
Names and Identifiers

• Fully-qualified name
• servername.databasename.owner.objectname
• Regular identifier
• First character is either a letter, _at_, , or _
• Does not contain any spaces
• Not a T-SQL reserved word
• If it violates the above, enclose in brackets

5
Data Manipulation Language

• Does the CRUD
• INSERT INTO table (field, field) VALUES (value,
  value)
• UPDATE table SET field valueWHERE condition
• SELECT field, field FROM tableWHERE condition
• DELETE table WHERE condition

6
Data Control Language

• To manage security
• GRANT , DENY , REVOKE
• Permissions on objects GRANT permission ON
  object TO user
• Permissions on statementsGRANT statement TO user

7
Permissions on Database Objects
REFERENCES to create a foreign key to a table
8
Data Types

• Limited character
• CHAR
• VARCHAR
• NCHAR
• NVARCHAR
• Unlimited character
• TEXT
• NTEXT
• Binary
• BINARY
• VARBINARY
• Binary large objects
• IMAGE
• Integer
• BIGINT
• INT
• SMALLINT
• TINYINT
• BIT

• Approximate numeric
• REAL
• FLOAT
• Exact numeric
• DECIMAL
• NUMERIC
• Date and time
• DATETIME
• SMALLDATETIME
• Currency
• MONEY
• SMALLMONEY
• Other
• UNIQUEIDENTIFIER
• TABLE
• SQL_VARIANT
• TIMESTAMP or ROWVERSION
• CURSOR

9
Extensions

• Variables
• DECLARE statement
• Variable name begins with _at_
• Operators
• Assignment
• Arithmetic - /
• Comparison ltgt lt gt lt gt
• Logical AND, OR, NOT, BETWEEN, IN, LIKE

10
Extensions (continued)

• Using the special logical operators
• BETWEEN x AND yx must be less than y
• IN (list) list can be a SELECT statement
• LIKE 'pattern' using wildcards , _, ,

11
Extensions (continued)

• Control of flow
• IF ELSE
• Use BEGIN and END if more than one line
• RETURN
• WAITFOR
• TIME
• DELAY
• WHILE
• BREAK unconditional exit
• CONTINUE restart loop
• GOTO

12
Extensions (continued)

• Comments
• -- inline comment
• /multiline comment/