Table of Contents
1 Introduction to SQL ........................................................................................................... 6
1.1 Data Definition Language (DDL).................................................................................. 8
1.2 Data Manipulation Language (DML) ........................................................................... 8
2 Introduction to SQL Server ................................................................................................ 9
2.1 SQL Server Management Studio ............................................................................... 10
2.1.1 Create a new Database...................................................................................... 11
2.1.2 Queries .............................................................................................................. 12
3 CREATE TABLE ................................................................................................................. 13
3.1 Database Modelling .................................................................................................. 15
3.2 Create Tables using the Designer Tools .................................................................... 17
3.3 SQL Constraints ......................................................................................................... 17
3.3.1 PRIMARY KEY ..................................................................................................... 18
3.3.2 FOREIGN KEY ..................................................................................................... 19
3.3.3 NOT NULL / Required Columns ......................................................................... 22
3.3.4 UNIQUE ............................................................................................................. 23
3.3.5 CHECK ................................................................................................................ 25
3.3.6 DEFAULT ............................................................................................................ 27
3.3.7 AUTO INCREMENT or IDENTITY ......................................................................... 28
3.4 ALTER TABLE ............................................................................................................. 29
4 INSERT INTO .................................................................................................................... 31
5 UPDATE ........................................................................................................................... 33
3
,4 Table of Contents
6 DELETE ............................................................................................................................. 35
7 SELECT ............................................................................................................................. 37
7.1 The ORDER BY Keyword ............................................................................................ 39
7.2 SELECT DISTINCT ....................................................................................................... 40
7.3 The WHERE Clause .................................................................................................... 40
7.3.1 Operators .......................................................................................................... 41
7.3.2 LIKE Operator .................................................................................................... 41
7.3.3 IN Operator........................................................................................................ 42
7.3.4 BETWEEN Operator ........................................................................................... 42
7.4 Wildcards .................................................................................................................. 42
7.5 AND & OR Operators ................................................................................................ 43
7.6 SELECT TOP Clause .................................................................................................... 44
7.7 Alias .......................................................................................................................... 45
7.8 Joins .......................................................................................................................... 45
7.8.1 Different SQL JOINs ........................................................................................... 46
8 SQL Scripts ....................................................................................................................... 48
8.1 Using Comments ....................................................................................................... 48
8.1.1 Single-line comment .......................................................................................... 48
8.1.2 Multiple-line comment ...................................................................................... 48
8.2 Variables ................................................................................................................... 49
8.3 Built-in Global Variables ........................................................................................... 50
8.3.1 @@IDENTITY ..................................................................................................... 50
8.4 Flow Control ............................................................................................................. 51
8.4.1 IF – ELSE ............................................................................................................. 51
8.4.2 WHILE ................................................................................................................ 52
8.4.3 CASE................................................................................................................... 53
Structured Query Language (SQL)
,5 Table of Contents
8.4.4 CURSOR ............................................................................................................. 54
9 Views ............................................................................................................................... 56
9.1 Using the Graphical Designer ................................................................................... 57
10 Stored Procedures ........................................................................................................ 61
10.1 NOCOUNT ON/NOCOUNT OFF .............................................................................. 64
11 Functions ...................................................................................................................... 66
11.1 Built-in Functions .................................................................................................. 66
11.1.1 String Functions ............................................................................................. 66
11.1.2 Date and Time Functions ............................................................................... 67
11.1.3 Mathematics and Statistics Functions ........................................................... 67
11.1.4 AVG() .............................................................................................................. 68
11.1.5 COUNT() ......................................................................................................... 68
11.1.6 The GROUP BY Statement.............................................................................. 69
11.1.7 The HAVING Clause ........................................................................................ 70
11.2 User-defined Functions ......................................................................................... 71
12 Triggers ......................................................................................................................... 72
13 Communication from other Applications ..................................................................... 75
13.1 ODBC ..................................................................................................................... 75
13.2 Microsoft Excel ...................................................................................................... 76
14 References .................................................................................................................... 78
Structured Query Language (SQL)
, 1 Introduction to SQL
SQL (Structured Query Language) is a database computer language designed for managing
data in relational database management systems (RDBMS).
SQL, is a standardized computer language that was originally developed by IBM for querying,
altering and defining relational databases, using declarative statements.
SQL is pronounced /ˌɛs kjuː ˈɛl/ (letter by letter) or /ˈsiːkwəl/ (as a word).
What can SQL do?
• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
6
1 Introduction to SQL ........................................................................................................... 6
1.1 Data Definition Language (DDL).................................................................................. 8
1.2 Data Manipulation Language (DML) ........................................................................... 8
2 Introduction to SQL Server ................................................................................................ 9
2.1 SQL Server Management Studio ............................................................................... 10
2.1.1 Create a new Database...................................................................................... 11
2.1.2 Queries .............................................................................................................. 12
3 CREATE TABLE ................................................................................................................. 13
3.1 Database Modelling .................................................................................................. 15
3.2 Create Tables using the Designer Tools .................................................................... 17
3.3 SQL Constraints ......................................................................................................... 17
3.3.1 PRIMARY KEY ..................................................................................................... 18
3.3.2 FOREIGN KEY ..................................................................................................... 19
3.3.3 NOT NULL / Required Columns ......................................................................... 22
3.3.4 UNIQUE ............................................................................................................. 23
3.3.5 CHECK ................................................................................................................ 25
3.3.6 DEFAULT ............................................................................................................ 27
3.3.7 AUTO INCREMENT or IDENTITY ......................................................................... 28
3.4 ALTER TABLE ............................................................................................................. 29
4 INSERT INTO .................................................................................................................... 31
5 UPDATE ........................................................................................................................... 33
3
,4 Table of Contents
6 DELETE ............................................................................................................................. 35
7 SELECT ............................................................................................................................. 37
7.1 The ORDER BY Keyword ............................................................................................ 39
7.2 SELECT DISTINCT ....................................................................................................... 40
7.3 The WHERE Clause .................................................................................................... 40
7.3.1 Operators .......................................................................................................... 41
7.3.2 LIKE Operator .................................................................................................... 41
7.3.3 IN Operator........................................................................................................ 42
7.3.4 BETWEEN Operator ........................................................................................... 42
7.4 Wildcards .................................................................................................................. 42
7.5 AND & OR Operators ................................................................................................ 43
7.6 SELECT TOP Clause .................................................................................................... 44
7.7 Alias .......................................................................................................................... 45
7.8 Joins .......................................................................................................................... 45
7.8.1 Different SQL JOINs ........................................................................................... 46
8 SQL Scripts ....................................................................................................................... 48
8.1 Using Comments ....................................................................................................... 48
8.1.1 Single-line comment .......................................................................................... 48
8.1.2 Multiple-line comment ...................................................................................... 48
8.2 Variables ................................................................................................................... 49
8.3 Built-in Global Variables ........................................................................................... 50
8.3.1 @@IDENTITY ..................................................................................................... 50
8.4 Flow Control ............................................................................................................. 51
8.4.1 IF – ELSE ............................................................................................................. 51
8.4.2 WHILE ................................................................................................................ 52
8.4.3 CASE................................................................................................................... 53
Structured Query Language (SQL)
,5 Table of Contents
8.4.4 CURSOR ............................................................................................................. 54
9 Views ............................................................................................................................... 56
9.1 Using the Graphical Designer ................................................................................... 57
10 Stored Procedures ........................................................................................................ 61
10.1 NOCOUNT ON/NOCOUNT OFF .............................................................................. 64
11 Functions ...................................................................................................................... 66
11.1 Built-in Functions .................................................................................................. 66
11.1.1 String Functions ............................................................................................. 66
11.1.2 Date and Time Functions ............................................................................... 67
11.1.3 Mathematics and Statistics Functions ........................................................... 67
11.1.4 AVG() .............................................................................................................. 68
11.1.5 COUNT() ......................................................................................................... 68
11.1.6 The GROUP BY Statement.............................................................................. 69
11.1.7 The HAVING Clause ........................................................................................ 70
11.2 User-defined Functions ......................................................................................... 71
12 Triggers ......................................................................................................................... 72
13 Communication from other Applications ..................................................................... 75
13.1 ODBC ..................................................................................................................... 75
13.2 Microsoft Excel ...................................................................................................... 76
14 References .................................................................................................................... 78
Structured Query Language (SQL)
, 1 Introduction to SQL
SQL (Structured Query Language) is a database computer language designed for managing
data in relational database management systems (RDBMS).
SQL, is a standardized computer language that was originally developed by IBM for querying,
altering and defining relational databases, using declarative statements.
SQL is pronounced /ˌɛs kjuː ˈɛl/ (letter by letter) or /ˈsiːkwəl/ (as a word).
What can SQL do?
• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert records in a database
• SQL can update records in a database
• SQL can delete records from a database
6
