https://www.halvorsen.blog
Structured Query Language
Hans-Petter Halvorsen
Available Online: https://www.halvorsen.blog
,Structured Query Language
Hans-Petter Halvorsen
Copyright © 2017
https://www.halvorsen.blog
,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)
Structured Query Language
Hans-Petter Halvorsen
Available Online: https://www.halvorsen.blog
,Structured Query Language
Hans-Petter Halvorsen
Copyright © 2017
https://www.halvorsen.blog
,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)
