07

SQL – MS SQL Server

 FIT

Outline

• SQL Data Definition and Data Types

• Data Definition Language
• Data Manipulation Language
• Specifying Constraints in SQL
• Schema Change Statements in SQL
• Database in SQL Server

• Ref: Chapter 6

2 Design and Manage Database

 FIT

Basic SQL

• SQL language
• Considered one of the major reasons for the commercial success of
relational databases
• SQL
• The origin of SQL is relational predicate calculus called tuple calculus which
was proposed initially as the language SQUARE.
• SQL Actually comes from the word “SEQUEL” which was the original term
used in the paper: “SEQUEL TO SQUARE” by Chamberlin and Boyce. IBM
could not copyright that term, so they abbreviated to SQL and copyrighted
the term SQL.
• Now popularly known as “Structured Query Language”.
• SQL is an informal or practical rendering of the relational data model with
syntax

3 Design and Manage Database

 FIT

DDL – DML

• DDL – Data Definition Language

• SQL commands: create, modify, and delete database structures
such as tables, indexes, and views.
• DML – Data Manipulation Language
• SQL commands: insert, update, and delete data within a database.

4 Design and Manage Database

 FIT

SQL Data Definition, Data Types, Standards

• Terminology:
• Table, row, and column used for relational model terms relation, tuple, and
attribute
• CREATE statement
• Main SQL command for data definition
• The language has features for :
• Data definition, Data Manipulation, Transaction control (Transact-SQL, Ch. 20),
• Indexing (Ch.17),
• Security specification (Grant and Revoke- see Ch.30),
• Active databases (Ch.26),
• Multi-media (Ch.26),
• Distributed databases (Ch.23) etc.

5 Design and Manage Database

 FIT

SQL Standards

• SQL has gone through many standards: starting with SQL-86 or

SQL 1.A. SQL-92 is referred to as SQL-2.
• Later standards (from SQL-1999) are divided into core
specification and specialized extensions. The extensions are
implemented for different applications – such as data mining,
data warehousing, multimedia etc.
• SQL-2006 added XML features (Ch. 13); In 2008 they added
Object-oriented features (Ch. 12).
• SQL-3 is the current standard which started with SQL-1999. It is
not fully implemented in any RDBMS.

6 Design and Manage Database

 FIT

Schema and Catalog Concepts in SQL

• We cover the basic standard SQL syntax – there are variations in

existing RDBMS systems
• SQL schema
• Identified by a schema name
• Includes an authorization identifier and descriptors for
each element
• Schema elements include
• Tables, constraints, views, domains, and other constructs
• Each statement in SQL ends with a semicolon

7 Design and Manage Database

 FIT

• CREATE SCHEMA statement

• CREATE SCHEMA COMPANY AUTHORIZATION ‘Jsmith’;
• Catalog
• Named collection of schemas in an SQL environment
• SQL also has the concept of a cluster of catalogs.

8 Design and Manage Database

 FIT

Attribute Data Types and Domains in SQL

• Basic data types

• Numeric data types
• Integer numbers: INTEGER, INT, and SMALLINT
• Floating-point (real) numbers: FLOAT or REAL, and DOUBLE
PRECISION
• Character-string data types
• Fixed length: CHAR(n), CHARACTER(n)
• Varying length: VARCHAR(n), CHAR VARYING(n), CHARACTER
VARYING(n)

9 Design and Manage Database

 FIT

• Bit-string data types

• Fixed length: BIT(n)
• Varying length: BIT VARYING(n)
• Boolean data type
• Values of TRUE or FALSE or NULL
• DATE data type
• Ten positions
• Components are YEAR, MONTH, and DAY in the form YYYY-MM-DD
• Multiple mapping functions available in RDBMSs to change date
formats

10 Design and Manage Database

 FIT

• Additional data types

• Timestamp data type
Includes the DATE and TIME fields
• Plus a minimum of six positions for decimal fractions of seconds
• Optional WITH TIME ZONE qualifier
• INTERVAL data type
• Specifies a relative value that can be used to increment or decrement
an absolute value of a date, time, or timestamp
• DATE, TIME, Timestamp, INTERVAL data types can be cast or
converted to string formats for comparison.

11 Design and Manage Database

 FIT

• Domain
• Name used with the attribute specification
• Makes it easier to change the data type for a domain that is used by
numerous attributes
• Improves schema readability
• Example:
• CREATE DOMAIN SSN_TYPE AS CHAR(9);
• TYPE
• User Defined Types (UDTs) are supported for object-oriented
applications. (See Ch.12) Uses the command: CREATE TYPE

12 Design and Manage Database

 FIT

Specifying Constraints in SQL

Basic constraints:
• Relational Model has 3 basic constraint types that are supported
in SQL:
• Key constraint: A primary key value cannot be duplicated
• Entity Integrity Constraint: A primary key value cannot be null
• Referential integrity constraints : The “foreign key “ must have a
value that is already present as a primary key, or may be null.

13 Design and Manage Database

 FIT

• Other Restrictions on attribute domains:

• Default value of an attribute
• DEFAULT <value>
• NULL is not permitted for a particular attribute (NOT NULL)
• CHECK clause
• Dnumber INT NOT NULL CHECK (Dnumber > 0 AND Dnumber < 21);

14 Design and Manage Database

 FIT

Specifying Key and Referential Integrity Constraints

• PRIMARY KEY clause

• Specifies one or more attributes that make up the primary key of a
relation
• Dnumber INT PRIMARY KEY;
• UNIQUE clause
• Specifies alternate (secondary) keys (called CANDIDATE keys in the
relational model).
• Dname VARCHAR(15) UNIQUE;

15 Design and Manage Database

 FIT

• FOREIGN KEY clause

• Default operation: reject update on violation
• Attach referential triggered action clause
• Options include SET NULL, CASCADE, and SET DEFAULT
• Action taken by the DBMS for SET NULL or SET DEFAULT is the same
for both ON DELETE and ON UPDATE
• CASCADE option suitable for “relationship” relations

16 Design and Manage Database

 FIT

Giving Names to Constraints

• Using the Keyword CONSTRAINT

• Name a constraint
• Useful for later altering

17 Design and Manage Database

 FIT

The CREATE TABLE Command in SQL

• Specifying a new relation

• Provide name of table
• Specify attributes, their types and initial constraints
• Can optionally specify schema:
• CREATE TABLE [Link] ...
or
• CREATE TABLE EMPLOYEE ...

18 Design and Manage Database

 FIT

• Base tables (base relations)

• Relation and its tuples are actually created and stored as a file by
the DBMS
• Virtual relations (views)
• Created through the CREATE VIEW statement. Do not correspond
to any physical file.

19 Design and Manage Database

 FIT

• Example

20 Design and Manage Database

 FIT

Specifying Constraints on Tuples Using CHECK

• Additional Constraints on individual tuples within a relation are also

possible using CHECK
• CHECK clauses at the end of a CREATE TABLE statement
• Apply to each tuple individually
• CHECK (Dept_create_date <= Mgr_start_date);

21 Design and Manage Database

 FIT

INSERT, DELETE, and UPDATE Statements in SQL

• Three commands used to modify the database:

• INSERT, DELETE, and UPDATE

• INSERT typically inserts a tuple (row) in a relation (table)

• UPDATE may update a number of tuples (rows) in a relation
(table) that satisfy the condition
• DELETE may also update a number of tuples (rows) in a relation
(table) that satisfy the condition

22 Design and Manage Database

 FIT

INSERT

• In its simplest form, it is used to add one or more tuples to a

relation
• Attribute values should be listed in the same order as the
attributes were specified in the CREATE TABLE command
• Constraints on data types are observed automatically
• Any integrity constraints as a part of the DDL specification are
enforced

23 Design and Manage Database

 FIT

• Specify the relation name and a list of values for the tuple. All
values including nulls are supplied.
• U1:
INSERT INTO EMPLOYEE
VALUES ( ‘Richard’, ‘K’, ‘Marini’, ‘653298653’, ‘1962-12-
30’, ‘98 Oak Forest, Katy, TX’, ‘M’, 37000, ‘653298653’, 4
);
• U1A:
INSERT INTO EMPLOYEE (Fname, Lname, Dno, Ssn) VALUES
(‘Richard’, ‘Marini’, 4, ‘653298653’);

24 Design and Manage Database

 FIT

• The variation below inserts multiple tuples where a new table is

loaded values from the result of a query.
• U3A:
CREATE TABLE WORKS_ON_INFO
(Emp_name VARCHAR(15),
Proj_name VARCHAR(15),
Hours_per_week DECIMAL(3,1));

• U3B:
NSERT INTO WORKS_ON_INFO ( Emp_name, Proj_name,
Hours_per_week )
SELECT [Link], [Link], [Link]
FROM PROJECT P, WORKS_ON W, EMPLOYEE E WHERE
[Link] = [Link] AND [Link] = [Link];

25 Design and Manage Database

 FIT

Bulk loading of tables

• Another variation of INSERT is used for bulk-loading of several

tuples into tables
• A new table TNEW can be created with the same attributes as T
and using LIKE and DATA in the syntax, it can be loaded with
entire data.
• Example:
CREATE TABLE D5EMPS LIKE EMPLOYEE
(SELECT E.*
FROM EMPLOYEE AS E
WHERE [Link]=5)
WITH DATA;

26 Design and Manage Database

 FIT

DELETE

• Removes tuples from a relation

• Includes a WHERE-clause to select the tuples to be deleted
• Referential integrity should be enforced
• Tuples are deleted from only one table at a time (unless
CASCADE is specified on a referential integrity constraint)
• A missing WHERE-clause specifies that all tuples in the relation
are to be deleted; the table then becomes an empty table
• The number of tuples deleted depends on the number of
tuples in the relation that satisfy the WHERE-clause

27 Design and Manage Database

 FIT

• Removes tuples from a relation

• Includes a WHERE clause to select the tuples to be deleted. The
number of tuples deleted will vary.
• U4A:
DELETE FROM EMPLOYEE WHERE Lname = ‘Brown’;
• U4B:
DELETE FROM EMPLOYEE WHERE Ssn = ‘123456789’;
• U4C:
DELETE FROM EMPLOYEE WHERE Dno = 5;
• U4D:
DELETE FROM EMPLOYEE;

28 Design and Manage Database

 • UPDATE FIT

• Used to modify attribute values of one or more selected tuples

• A WHERE-clause selects the tuples to be modified
• An additional SET-clause specifies the attributes to be modified
and their new values
• Each command modifies tuples in the same relation
• Referential integrity specified as part of DDL specification is
enforced

29 Design and Manage Database

 FIT

• Example: Change the location and controlling department

number of project number 10 to 'Bellaire' and 5, respectively

U5: UPDATE PROJECT

SET PLOCATION = 'Bellaire',
DNUM = 5
WHERE PNUMBER=10;

30 Design and Manage Database

 FIT

• Example: Give all employees in the 'Research' department a 10%

raise in salary.
U6: UPDATE EMPLOYEE
SET SALARY = SALARY *1.1
WHERE DNO IN (SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME='Research’);

31 Design and Manage Database

 FIT

SQL Server

• Microsoft SQL Server is a proprietary relational database

management system developed by Microsoft. As a database
server, it is a software product with the primary function of
storing and retrieving data as requested by other software
applications—which may run either on the same computer or on
another computer across a network (including the Internet)

32 Design and Manage Database

 FIT

SQL Server history

• 1987: Sysbase SQL Server

• 1988: Microsoft, Sybase, and Aston-Tate port SQL Server to OS/2.
• 1989: Microsoft, Sybase, and Aston-Tate release SQL Server 1.0 for
OS/2.
• 1990: SQL Server 1.1 is released with support for Windows 3.0 clients.
• 1992: Microsoft SQL Server 4.2 for 16-bit OS/2 1.3 is released.
• 1993: Microsoft and Sybase release version 4.2 of SQL Server for
Windows NT.
• 1995: MS SQL Server version 6.0.
• 1996: MS SQL Server version 6.5.
• 1998: MS SQL Server version 7.0.

33 Design and Manage Database

 FIT

• …
• 2000: MS SQL Server 2000.
• Nov 2005: MS SQL Server 2005.
• Aug 2008: MS SQL Server 2008.
• Apr 2010: MS SQL Server 2008 R2
• Mar 2012: MS SQL Server 2012
• Apr 2014: MS SQL Server 2014
• Feb 2016: MS SQL Server 2016
• Oct 2017: MS SQL Server 2017
• Nov 2019: MS SQL Server 2019
• Nov 2022: MS SQL Server 2022

34 Design and Manage Database

 FIT

SQL Server Architecture

35 Design and Manage Database

 FIT

• Database Architecture

36 Design and Manage Database

 FIT

SQL Server features

• Ease of installation, deployment, and use

• SQL Server includes a set of administrative and development tools that
improve your ability to install, deploy, manage, and use SQL Server across
several sites.
• Scalability
• The same database engine can be used across platforms ranging from laptop
computers running Microsoft Windows 95 to large, multiprocessor servers
running Microsoft Windows NT, Enterprise Edition.
• Data warehousing
• SQL Server includes tools for extracting and analyzing summary data for online
analytical processing (OLAP). SQL Server also includes tools for visually
designing databases and analyzing data using English-based questions.
• System integration with other server software
• SQL Server integrates with e-mail, the Internet, and Windows.
Ref: [Link]
37 of-sql-server-2022?view=sql-server-ver16
Design and Manage Database
 FIT

• SQL Server – OS

Ref.: [Link]

38 Design and Manage Database

 FIT

• SQL Server Editions

SQL Server SQL Server SQL Server SQL Server
2008 2017 2019 2022
Core editions Enterprise Enterprise Enterprise
Enterprise Standard Standard Standard
Standard Web Web Express
Specialized Developer Developer
editions Express Express
Workgroup editions editions
Web
Express

39 Design and Manage Database

 FIT

SQL Server Services

40 Design and Manage Database

 FIT

• SQL Server – Application

41 Design and Manage Database

 FIT

SQL Server – Data types

• Integer
Name Bytes Range
bigint 8 -263 (-9,223,372,036,854,775,808) to
263 - 1 (9,223,372,036,854,775,807)
int 4 -231 (-2,147,483,648) to
231 - 1 (2,147,483,647)
smallint 2 -215 (-32,768) to 215 - 1 (32,767)
tinyint 1 0 to 255

42 Design and Manage Database

 FIT

• Exact numeric
Name Bytes Range
decimal[p[,s]] 5 – 17 - 1038 +1 to 1038 - 1.
numeric[p[,s]] 5 – 17 - 1038 +1 to 1038 - 1.

p (precision)
The maximum total number of decimal digits that can be stored, both to the left and
to the right of the decimal point. The precision must be a value from 1 through the
maximum precision of 38. The default precision is 18.
s (scale)
The maximum number of decimal digits that can be stored to the right of the
decimal point. Scale must be a value from 0 through p. Scale can be specified only
if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum
storage sizes vary, based on the precision.

43 Design and Manage Database

 FIT

• Appropriate numeric
Name Bytes Range
Float[(n)] n - 1.79E+308 to -2.23E-308,
0 and 2.23E-308 to 1.79E+308
real 4 - 3.40E + 38 to -1.18E - 38,
0 and 1.18E - 38 to 3.40E + 38
• Monetary
Name Bytes Range
Money 8 -922,337,203,685,477.5808 to
922,337,203,685,477.5807
smallmoney 4 - 214,748.3648 to
214,748.3647
44 Design and Manage Database
 FIT

• Date and Time

Name Bytes Range
datetime 8 January 1, 1753, to
December 31, 9999
smalldatetime 4 January 1, 1900, to
June 6, 2079

45 Design and Manage Database

 FIT

• Characters
Name Bytes Comments
char[(n)] 0-8000 non-Unicode
varchar[(n)] 0-8000 non-Unicode
varchar(max) 0-2 GB non-Unicode, 16 bytes pointer on row,
preferred over text data type
text 0-2 GB non-Unicode, 16 bytes pointer or in row,
obsolete, varchar(max) preferred
nchar[(n)] 0-8000 max 4000 unicode characters
nvarchar[(n)] 0-8000 max 4000 unicode characters
nvarchar(max) 0-2 GB 16 bytes pointer or in row, preferred over ntext
data type
ntext 0-2 GB 16 bytes pointer, obsolete, nvarchar(max)
46 Design and Manage Database
prefered
 FIT

• Binary
Name Bytes Comments
binary[(n)] 0-8000
varbinary[(n)] 0-8000
varbinary(max) 0-2 GB 16 bytes pointer or in row, preferred over
image data type

47 Design and Manage Database

 FIT

• Image
Name Bytes Comments
Image 0-2GB 16 bytes pointer, obsolete, varbinary(max)
preferred
• Global identifier
Name Bytes Comments
uniqueidentifier 16

• XML
Name Bytes Comments
xml 0-2GB 16 bytes pointer

48 Design and Manage Database

 FIT

Create Database

• Syntax:
Create Database <DB_Name> On
(Name <Logical_File_Name>,
Filename = <OS_File_Name.mdf>,
Size = <size> [ KB | MB | GB | TB ],
Maxsize = <size> [ KB | MB | GB | TB ] | UNLIMITED,
Filegrowth = growth_increment [ KB | MB | GB | TB | % ])
Log on
(Name <Logical_File_Name>,
Filename = <OS_File_Name.ldf>,
Size = <size> [ KB | MB | GB | TB ],
Maxsize = <size> [ KB | MB | GB | TB ] | UNLIMITED,
Filegrowth = growth_increment [ KB | MB | GB | TB | % ]);

49 Design and Manage Database

 FIT

• Example: Creating Company database

CREATE DATABASE Company ON PRIMARY
( NAME = 'Company',
FILENAME = ‘D:\DATA\[Link]' ,
SIZE =3072KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
LOG ON
( NAME = 'Company_log',
FILENAME = ‘D:\DATA\Company_log.ldf' ,
SIZE = 1024KB ,
MAXSIZE = 2048KB ,
FILEGROWTH = 10%);
GO

50 Design and Manage Database

 FIT

Delete Database

• Syntax:
Drop Database <DB_Name>, …;

• Example:
Drop Database Company;
Go

51 Design and Manage Database

 FIT

Create table

• Syntax
Create table <tabName>
(<FieldName> <DataType (length)> Null| Not Null
[<FieldName> <DataType (length)> …);

• Example
CREATE TABLE Department(
DName varchar(15) NOT NULL,
DNumber numeric(4, 0) NOT NULL,
MgrSsn char(9) NULL,
MgrStartdate datetime NULL);
GO

52 Design and Manage Database

 FIT

53 Design and Manage Database

 FIT

Alter table syntax

• Primary key | Unique key

Alter table <tabName>
Add Constraint <consName> Primary Key (<FieldName>);

• Example:
Alter Table Department
Add Constraint pk_Dept Primary Key (DNumber);
GO

Alter Table Department

Add Constraint uk_Dept Unique Key (DName);
GO

54 Design and Manage Database

 FIT

• Foreign key
Alter table <tabName>
Add Constraint <consName> Foreign Key (<FieldName>)
References <tabName>(FieldName);

• Example:
Alter Table Department
Add Constraint fk_DepMgrSSN Foreign Key (MgrSSN)
Refrences Employee(SSN);
GO

55 Design and Manage Database

 FIT

Drop table

• Syntax
Drop Table <tabName>;

• Example
Drop Table Department;
Go

56 Design and Manage Database

 FIT

Insert

• Syntax:
Insert [Into] <tabName>(<fieldName1>, <fieldName2>, …)
Values(<value1>, <value2>, … );
Value: constant, expression, Null

• Example:
Insert Into Department(Dname, Dnumnbe, MrgSSN, MgrStartDate)
Values(‘Research’, 5, Null, ‘22-May-1988’);
Go
Or
Insert Into Department
Values(‘Research’, 5, Null, ‘22-May-1988’);
Go

57 Design and Manage Database

 FIT

58 Design and Manage Database

 FIT

59 Design and Manage Database

 FIT

Update

• Syntax
Update <tabName>
Set <fieldName> = <Value>;
[Where <condition>];

• Example:
Update Department
Set MgrSSN = ‘333445555’
Where DNumber = 5;
Go

60 Design and Manage Database

 FIT

Delete

• Syntax
Delete From <tabName>
[Where <condition>];

• Example
Delete From Department
Where Dnumber = 5;
Go
Or
Delete From Department;
Go

61 Design and Manage Database

 FIT

62 Design and Manage Database