Database Development (Sequence Objects)

Heidi21

SQLServer2019Chapter11slides1.pptx

Home >Computer Science homework help >Database Development (Sequence Objects)

Chapter 11

How create a database and its tables with SQL statements

Murach's SQL Server 2019

C11, Slide 1

Objectives (part 1)

Applied

Given a complete database design, write the SQL DDL statements to create the database, including all tables, relationships, constraints, indexes, and sequences.

Knowledge

Describe how each of these types of constraints restricts the values that can be stored in a table: NOT NULL, PRIMARY KEY, UNIQUE, CHECK, and FOREIGN KEY (or REFERENCES).

Describe the difference between a column-level constraint and a table-level constraint.

Explain how the CASCADE and NO ACTION options differ in enforcing referential integrity on deletes and updates.

Murach's SQL Server 2019

C11, Slide 2

Objectives (part 2)

Describe the use of a sequence.

Name two character sets and four encodings frequently used with SQL Server.

Compare and contrast the UCS-2, UTF-8, and UTF-16 encodings.

Explain what a collation is and how you specify a collation.

Describe the use of a script that contains one or more batches for creating a database.

Murach's SQL Server 2019

C11, Slide 3

DDL statements to create, modify, and delete objects (part 1)

CREATE DATABASE

CREATE TABLE

CREATE INDEX

CREATE SEQUENCE

CREATE FUNCTION

CREATE PROCEDURE

CREATE TRIGGER

CREATE VIEW

ALTER TABLE

ALTER SEQUENCE

ALTER FUNCTION

Murach's SQL Server 2019

C11, Slide 4

DDL statements to create, modify, and delete objects (part 2)

ALTER PROCEDURE

ALTER TRIGGER

ALTER VIEW

DROP DATABASE

DROP TABLE

DROP SEQUENCE

DROP INDEX

DROP FUNCTION

DROP PROCEDURE

DROP TRIGGER

DROP VIEW

Murach's SQL Server 2019

C11, Slide 5

Formatting rules for regular identifiers

The first character of an identifier must be a letter as defined by the Unicode Standard 3.2, an underscore (_), an at sign (@), or a number sign (#).

All characters after the first must be a letter as defined by the Unicode Standard 3.2, a number, an at sign, a dollar sign ($), a number sign, or an underscore.

An identifier can’t be a Transact-SQL reserved keyword.

An identifier can’t contain spaces or special characters other than those already mentioned.

Murach's SQL Server 2019

C11, Slide 6

Valid regular identifiers

Employees

#PaidInvoices

ABC$123

Invoice_Line_Items

@TotalDue

Valid delimited identifiers

[%Increase]

"Invoice Line Items"

[@TotalDue]

Murach's SQL Server 2019

C11, Slide 7

Basic syntax of the CREATE DATABASE statement

CREATE DATABASE database_name

[ON [PRIMARY] (FILENAME = 'file_name')]

[FOR ATTACH]

Create a new database

CREATE DATABASE New_AP;

The response from the system

Commands completed successfully.

Attach an existing database file

CREATE DATABASE Test_AP

ON PRIMARY (FILENAME =

'C:\Murach\SQL Server 2019\Databases\Test_AP.mdf')

FOR ATTACH;

The response from the system

Commands completed successfully.

Murach's SQL Server 2019

C11, Slide 8

Basic syntax of the CREATE TABLE statement

CREATE TABLE table_name

(column_name_1 data_type [column_attributes]

[, column_name_2 data_type [column_attributes]]...

[, table_attributes])

Common column attributes

NULL|NOT NULL

PRIMARY KEY|UNIQUE

IDENTITY

DEFAULT default_value

SPARSE

Murach's SQL Server 2019

C11, Slide 9

Create a table without column attributes

CREATE TABLE Vendors

(VendorID INT,

VendorName VARCHAR(50));

Create a table with column attributes

CREATE TABLE Invoices

(InvoiceID INT PRIMARY KEY IDENTITY,

VendorID INT NOT NULL,

InvoiceDate DATE NULL,

InvoiceTotal MONEY NULL DEFAULT 0);

A column definition that uses the SPARSE attribute

VendorAddress2 VARCHAR(50) SPARSE NULL

Murach's SQL Server 2019

C11, Slide 10

Basic syntax of the CREATE INDEX statement

CREATE [CLUSTERED|NONCLUSTERED] INDEX index_name ON table_name (col_name_1 [ASC|DESC]

[, col_name_2 [ASC|DESC]]...)

[WHERE filter-condition]

Create a nonclustered index on a single column

CREATE INDEX IX_VendorID

ON Invoices (VendorID);

Create a nonclustered index on two columns

CREATE INDEX IX_Invoices

ON Invoices (InvoiceDate DESC, InvoiceTotal);

Note

SQL Server automatically creates a clustered index for a table’s primary key.

Murach's SQL Server 2019

C11, Slide 11

Create a filtered index for a subset of data in a column

CREATE INDEX IX_InvoicesPaymentFilter

ON Invoices (InvoiceDate DESC, InvoiceTotal)

WHERE PaymentDate IS NULL;

Create a filtered index for categories in a column

CREATE INDEX IX_InvoicesDateFilter

ON Invoices (InvoiceDate DESC, InvoiceTotal)

WHERE InvoiceDate > '2020-02-01';

Murach's SQL Server 2019

C11, Slide 12

The snippet picker with a list of object folders

Murach's SQL Server 2019

C11, Slide 13

The snippet picker with the list of snippets for a table

The CREATE TABLE snippet after it’s inserted

Murach's SQL Server 2019

C11, Slide 14

Terms related to database creation

Transaction log file

Attach a database file

Full-table index

Filtered index

Murach's SQL Server 2019

C11, Slide 15

Column-level constraints

Constraint Description

NOT NULL Prevents null values from being stored in the column.

PRIMARY KEY Requires that each row in the table have a unique value in the column. Null values are not allowed.

UNIQUE Requires that each row in the table have a unique value in the column.

CHECK Limits the values for a column.

[FOREIGN KEY] Enforces referential integrity between a column REFERENCES in the new table and a column in a related table.

Murach's SQL Server 2019

C11, Slide 16

Table-level constraints

Constraint Description

PRIMARY KEY Requires that each row in the table have a unique set of values over one or more columns. Null values are not allowed.

UNIQUE Requires that each row in the table have a unique set of values over one or more columns.

CHECK Limits the values for one or more columns.

[FOREIGN KEY] Enforces referential integrity between one or more REFERENCES columns in the new table and one or more columns in the related table.

Murach's SQL Server 2019

C11, Slide 17

Create a table with a two-column primary key constraint

CREATE TABLE InvoiceLineItems1

(InvoiceID INT NOT NULL,

InvoiceSequence SMALLINT NOT NULL,

InvoiceLineItemAmount MONEY NOT NULL,

PRIMARY KEY (InvoiceID, InvoiceSequence));

Murach's SQL Server 2019

C11, Slide 18

Create a table with two column-level check constraints

CREATE TABLE Invoices1

(InvoiceID INT NOT NULL IDENTITY PRIMARY KEY,

InvoiceTotal MONEY NOT NULL CHECK (InvoiceTotal >= 0),

PaymentTotal MONEY NOT NULL DEFAULT 0 CHECK (PaymentTotal >= 0));

The same check constraints coded at the table level

CREATE TABLE Invoices2

(InvoiceID INT NOT NULL IDENTITY PRIMARY KEY,

InvoiceTotal MONEY NOT NULL,

PaymentTotal MONEY NOT NULL DEFAULT 0,

CHECK ((InvoiceTotal >= 0) AND (PaymentTotal >= 0)));

Murach's SQL Server 2019

C11, Slide 19

The syntax of a check constraint

CHECK (condition)

A column-level check constraint

CREATE TABLE Invoices3

(InvoiceID INT NOT NULL IDENTITY PRIMARY KEY,

InvoiceTotal MONEY NOT NULL CHECK (InvoiceTotal > 0));

An INSERT statement that fails due to the check constraint

INSERT Invoices3

VALUES (-100);

The response from the system

The INSERT statement conflicted with the CHECK constraint "CK__Invoices3__Invoi__0BC6C43E". The conflict occurred in database "New_AP", table "dbo.Invoices3", column 'InvoiceTotal'.

The statement has been terminated.

Murach's SQL Server 2019

C11, Slide 20

A table-level check constraint

CREATE TABLE Vendors1

(VendorCode CHAR(6) NOT NULL PRIMARY KEY,

VendorName VARCHAR(50) NOT NULL,

CHECK ((VendorCode LIKE '[A-Z][A-Z][0-9][0-9][0-9][0-9]')

AND (LEFT(VendorCode,2) = LEFT(VendorName,2))));

An INSERT statement that fails due to the check constraint

INSERT Vendors1

VALUES ('Mc4559','Castle Printers, Inc.');

The response from the system

The INSERT statement conflicted with the CHECK constraint "CK__Vendors1__164452B1". The conflict occurred in database "New_AP", table "dbo.Vendors1".

The statement has been terminated.

Murach's SQL Server 2019

C11, Slide 21

The syntax of a column-level foreign key constraint

[FOREIGN KEY] REFERENCES ref_table_name (ref_column_name)

[ON DELETE {CASCADE|NO ACTION}]

[ON UPDATE {CASCADE|NO ACTION}]

The syntax of a table-level foreign key constraint

FOREIGN KEY (column_name_1 [, column_name_2]...)

REFERENCES ref_table_name (ref_column_name_1

[, ref_column_name_2]...)

[ON DELETE {CASCADE|NO ACTION}]

[ON UPDATE {CASCADE|NO ACTION}]

Murach's SQL Server 2019

C11, Slide 22

A column-level foreign key constraint (part 1)

A statement that creates the primary key table

CREATE TABLE Vendors9

(VendorID INT NOT NULL PRIMARY KEY,

VendorName VARCHAR(50) NOT NULL);

A statement that creates the foreign key table

CREATE TABLE Invoices9

(InvoiceID INT NOT NULL PRIMARY KEY,

VendorID INT NOT NULL

REFERENCES Vendors9 (VendorID),

InvoiceTotal MONEY NULL);

Murach's SQL Server 2019

C11, Slide 23

A column-level foreign key constraint (part 2)

An INSERT statement that fails because a related row doesn’t exist

INSERT Invoices9

VALUES (1, 99, 100);

The response from the system

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Invoices9__Vendo__1367E606". The conflict occurred in database "New_AP", table "dbo.Vendors9", column 'VendorID'.

The statement has been terminated.

Murach's SQL Server 2019

C11, Slide 24

Terms related to constraints

Constraint

Column-level constraint

Table-level constraint

Check constraint

Foreign key constraint

Reference constraint

Cascading delete

Cascading update

Murach's SQL Server 2019

C11, Slide 25

The syntax of the DROP INDEX statement

DROP INDEX index_name_1 ON table_name_1 [, index_name_2 ON table_name_2]...

Delete an index from the Invoices table

DROP INDEX IX_Invoices ON Invoices;

Note

You can’t delete an index that’s based on a primary key or unique key constraint. To do that, you have to use the ALTER TABLE statement.

Murach's SQL Server 2019

C11, Slide 26

The syntax of the DROP TABLE statement

DROP TABLE table_name_1 [, table_name_2]...

Delete a table from the current database

DROP TABLE Vendors1;

Qualify the table to be deleted

DROP TABLE New_AP.dbo.Vendors1;

Notes

You can’t delete a table if a foreign key constraint in another table refers to that table.

When you delete a table, all of the data, indexes, triggers, and constraints are deleted. Any views or stored procedures associated with the table must be deleted explicitly.

Murach's SQL Server 2019

C11, Slide 27

The syntax of the DROP DATABASE statement

DROP DATABASE database_name_1 [, database_name_2]...

A statement that deletes a database

DROP DATABASE New_AP;

Murach's SQL Server 2019

C11, Slide 28

The basic syntax of the ALTER TABLE statement

ALTER TABLE table_name [WITH CHECK|WITH NOCHECK]

{ADD new_column_name data_type [column_attributes] |

DROP COLUMN column_name |

ALTER COLUMN column_name new_data_type [NULL|NOT NULL] |

ADD [CONSTRAINT] new_constraint_definition |

DROP [CONSTRAINT] constraint_name}

Murach's SQL Server 2019

C11, Slide 29

Add a new column

ALTER TABLE Vendors

ADD LastTranDate DATE NULL;

Drop a column

ALTER TABLE Vendors

DROP COLUMN LastTranDate;

Add a new check constraint

ALTER TABLE Invoices WITH NOCHECK

ADD CHECK (InvoiceTotal >= 1);

Add a foreign key constraint

ALTER TABLE InvoiceLineItems WITH CHECK

ADD FOREIGN KEY (AccountNo) REFERENCES GLAccounts(AccountNo);

Change the data type of a column

ALTER TABLE InvoiceLineItems

ALTER COLUMN InvoiceLineItemDescription VARCHAR(200);

Murach's SQL Server 2019

C11, Slide 30

The syntax of the CREATE SEQUENCE statement

CREATE SEQUENCE sequence_name

[AS integer_type]

[START WITH starting_integer]

[INCREMENT BY increment_integer]

[{MINVALUE minimum_integer | NO MINVALUE}]

[{MAXVALUE maximum_integer | NO MAXVALUE}]

[{CYCLE|NOCYCLE}]

[{CACHE cache_size|NOCACHE}]

Murach's SQL Server 2019

C11, Slide 31

Create a sequence that starts with 1

CREATE SEQUENCE TestSequence1

START WITH 1;

Specify a starting value and an increment

CREATE SEQUENCE TestSequence2

START WITH 10

INCREMENT BY 10;

Specify all optional parameters

CREATE SEQUENCE TestSequence3

AS int

START WITH 100 INCREMENT BY 10

MINVALUE 0 MAXVALUE 1000000

CYCLE CACHE 10;

Murach's SQL Server 2019

C11, Slide 32

Create a table with a sequence column

CREATE TABLE SequenceTable(

SequenceNo INT,

Description VARCHAR(50));

Insert the next value for a sequence

INSERT INTO SequenceTable

VALUES (NEXT VALUE FOR TestSequence3, 'First inserted row')

INSERT INTO SequenceTable

VALUES (NEXT VALUE FOR TestSequence3,

'Second inserted row');

Get the current value of the sequence

SELECT current_value FROM sys.sequences

WHERE name = 'TestSequence3';

Murach's SQL Server 2019

C11, Slide 33

The syntax of the DROP SEQUENCE statement

DROP SEQUENCE sequence_name1[, sequence_name2]...

A statement that drops a sequence

DROP SEQUENCE TestSequence2;

Murach's SQL Server 2019

C11, Slide 34

The syntax of the ALTER SEQUENCE statement

ALTER SEQUENCE sequence_name

[RESTART [WITH starting_integer]]

[INCREMENT BY increment_integer]

[{MINVALUE minimum_integer | NO MINVALUE}]

[{MAXVALUE maximum_integer | NO MAXVALUE}]

[{CYCLE|NOCYCLE}]

[{CACHE cache_size|NOCACHE}]

A statement that alters a sequence

ALTER SEQUENCE TestSequence1

INCREMENT BY 9

MINVALUE 1 MAXVALUE 999999

CACHE 9

CYCLE;

Murach's SQL Server 2019

C11, Slide 35

Common character encodings

Name Bytes per character

Latin1 1

UCS-2 2

UTF-8 1-4

UTF-16 2 or 4

Murach's SQL Server 2019

C11, Slide 36

Bytes required for Unicode characters

Numeric code range Characters UCS-2 UTF-8 UTF-16

0-127 ASCII 2 1 2

128-2047 European letters and Middle Eastern script 2 2 2

2048-65,535 Korean, Chinese, and Japanese ideographs 2 3 2

65,536-1,114,111 Supplementary N/A 4 4

Murach's SQL Server 2019

C11, Slide 37

Collation sets supported by SQL Server

Windows

SQL Server

Binary

Murach's SQL Server 2019

C11, Slide 38

Some collation options

Option Name

_CS Case Sensitive

_CI Case Insensitive

_AS Accent Sensitive

_AI Accent Insensitive

_BIN Binary (legacy)

_BIN2 Binary (new)

_SC Supplementary

_UTF8 UTF-8

Murach's SQL Server 2019

C11, Slide 39

Collation examples

The default server collation for the English (US) locale

SQL_Latin1_General_CP1_CI_AS

The closest Windows equivalent to this collation

Latin1_General_100_CI_AS

A collation that provides for supplementary characters using UTF-8

Latin1_General_100_CI_AS_SC_UTF8

Murach's SQL Server 2019

C11, Slide 40

How to view the default collation for a server

SELECT CONVERT(varchar, SERVERPROPERTY('collation'));

Murach's SQL Server 2019

C11, Slide 41

How to view all available collations for a server

SELECT * FROM sys.fn_helpcollations();

How to view all collations with a specific name

SELECT * FROM sys.fn_helpcollations() WHERE name LIKE 'Latin1_General_100%';

Murach's SQL Server 2019

C11, Slide 42

How to view the collation for a database

SELECT name, collation_name

FROM sys.databases

WHERE name = 'AP';

How to view the collations for database columns

SELECT sys.tables.name AS TableName,

sys.columns.name AS ColumnName, collation_name

FROM sys.columns inner join sys.tables

ON sys.columns.object_id = sys.tables.object_id;

Murach's SQL Server 2019

C11, Slide 43

The clause used to specify a collation

[COLLATE collation]

How to specify a collation at the database level

For a new database

CREATE DATABASE AR COLLATE Latin1_General_100_CI_AS;

For an existing database

ALTER DATABASE AR COLLATE Latin1_General_100_CI_AS_SC_UTF8;

Murach's SQL Server 2019

C11, Slide 44

How to specify a collation at the column level

For a column in a new table

CREATE TABLE Employees

(

EmployeeID INT PRIMARY KEY,

EmployeeName VARCHAR(50)

COLLATE Latin1_General_100_CI_AS

);

For a column in an existing table

ALTER TABLE Employees

ALTER COLUMN

EmployeeName VARCHAR(200)

COLLATE Latin1_General_100_CI_AS_SC_UTF8;

Murach's SQL Server 2019

C11, Slide 45

How to specify a collation for an expression

SELECT * FROM Employees

ORDER BY EmployeeName COLLATE Latin1_General_100_BIN2;

Murach's SQL Server 2019

C11, Slide 46

A possible error message after switching to UTF-8 or UTF-16

String or binary data would be truncated in table 'Test', column 'Message'. Truncated value: 'Hi! 😀😀😀'.

Murach's SQL Server 2019

C11, Slide 47

Terms related to collations

Character set

Encoding

Unicode

Supplementary characters

Collation

Murach's SQL Server 2019

C11, Slide 48

The SQL script that creates the AP database (part 1)

CREATE DATABASE AP;

USE AP;

CREATE TABLE Terms

(TermsID INT NOT NULL PRIMARY KEY,

TermsDescription VARCHAR(50) NOT NULL,

TermsDueDays SMALLINT NOT NULL);

CREATE TABLE GLAccounts

(AccountNo INT NOT NULL PRIMARY KEY,

AccountDescription VARCHAR(50) NOT NULL);

Murach's SQL Server 2019

C11, Slide 49

The SQL script that creates the AP database (part 2)

CREATE TABLE Vendors

(VendorID INT NOT NULL IDENTITY

PRIMARY KEY,

VendorName VARCHAR(50) NOT NULL,

VendorAddress1 VARCHAR(50) NULL,

VendorAddress2 VARCHAR(50) SPARSE NULL,

VendorCity VARCHAR(50) NOT NULL,

VendorState CHAR(2) NOT NULL,

VendorZipCode VARCHAR(20) NOT NULL,

VendorPhone VARCHAR(50) NULL,

VendorContactLName VARCHAR(50) NULL,

VendorContactFName VARCHAR(50) NULL,

DefaultTermsID INT NOT NULL

REFERENCES Terms(TermsID),

DefaultAccountNo INT NOT NULL

REFERENCES GLAccounts(AccountNo));

Murach's SQL Server 2019

C11, Slide 50

The SQL script that creates the AP database (part 3)

CREATE TABLE Invoices

(InvoiceID INT NOT NULL IDENTITY

PRIMARY KEY,

VendorID INT NOT NULL

REFERENCES Vendors(VendorID),

InvoiceNumber VARCHAR(50) NOT NULL,

InvoiceDate DATE NOT NULL,

InvoiceTotal MONEY NOT NULL,

PaymentTotal MONEY NOT NULL DEFAULT 0,

CreditTotal MONEY NOT NULL DEFAULT 0,

TermsID INT NOT NULL

REFERENCES Terms(TermsID),

InvoiceDueDate DATE NOT NULL,

PaymentDate DATE NULL);

Murach's SQL Server 2019

C11, Slide 51

The SQL script that creates the AP database (part 4)

CREATE TABLE InvoiceLineItems

(InvoiceID INT NOT NULL

REFERENCES Invoices(InvoiceID)

ON DELETE CASCADE,

InvoiceSequence SMALLINT NOT NULL,

AccountNo INT NOT NULL

REFERENCES GLAccounts(AccountNo),

InvoiceLineItemAmount MONEY NOT NULL,

InvoiceLineItemDescription VARCHAR(100) NOT NULL,

PRIMARY KEY (InvoiceID, InvoiceSequence));

Murach's SQL Server 2019

C11, Slide 52

The SQL script that creates the AP database (part 5)

CREATE INDEX IX_Invoices_VendorID

ON Invoices (VendorID);

CREATE INDEX IX_Invoices_TermsID

ON Invoices (TermsID);

CREATE INDEX IX_Vendors_TermsID

ON Vendors (DefaultTermsID);

CREATE INDEX IX_Vendors_AccountNo

ON Vendors (DefaultAccountNo);

CREATE INDEX IX_InvoiceLineItems_AccountNo

ON InvoiceLineItems (AccountNo);

CREATE INDEX IX_VendorName

ON Vendors (VendorName);

CREATE INDEX IX_InvoiceDate

ON Invoices (InvoiceDate DESC);

Murach's SQL Server 2019

C11, Slide 53

Terms related to SQL scripts

Script

Batch

Murach's SQL Server 2019

C11, Slide 54