28-02-2021 Lecture

Create Schema

Create Table

Data Type

Primary Key

Insert into

SQL Server CREATE DATABASE

Summary: in this tutorial, you will learn how to create a new database in SQL
Server using CREATE DATABASE statement or SQL Server Management Studio.

Creating a new database using the CREATE DATABASE statement

CREATE DATABASE database_name;

In this syntax, you specify the name of the database after the CREATE
DATABASE keyword.

The following statement creates a new database named TestDb:

CREATE DATABASE TestDb;

Creating a new database using SQL Server Management

Studio
First, right-click the Database and choose New Database… menu item.
Second, enter the name of the database e.g., SampleDb and click
the OK button.

Third, view the newly created database from the Object Explorer:

SQL Server DROP DATABASE

To remove an existing database from a SQL Server instance, you use the DROP
DATABASE statement.

The DROP DATABASE statement allows you to delete one or more databases with
the following syntax:

DROP DATABASE [ IF EXISTS ]

database_name

[,database_name2,...];

The following example uses the DROP DATABASE statement to delete

the TestDb database:

DROP DATABASE IF EXISTS TestDb;

Using the SQL Server Management Studio to drop a

database
First, right-click on the database name that you want to delete and
choose Delete menu item:

Second, uncheck the Delete backup and restore history information for
databases check box, check the Close existing connections check box,
and click the OK button to delete the database

Third, verify that the database has been dropped from the Object Explorer.
SQL BACKUP DATABASE for SQL Server

The SQL BACKUP DATABASE Statement

The BACKUP DATABASE statement is used in SQL Server to create a full back up
of an existing SQL database.

Syntax
BACKUP DATABASE databasename
TO DISK = 'filepath';

The SQL BACKUP WITH DIFFERENTIAL

Statement
A differential back up only backs up the parts of the database that have
changed since the last full database backup.

Syntax
BACKUP DATABASE databasename
TO DISK = 'filepath'
WITH DIFFERENTIAL;

BACKUP DATABASE Example

The following SQL statement creates a full back up of the existing database
"testDB" to the D disk:

Example
BACKUP DATABASE testDB
TO DISK = 'D:\backups\testDB.bak';

Tip: Always back up the database to a different drive than the actual database.
Then, if you get a disk crash, you will not lose your backup file along with the
database.
BACKUP WITH DIFFERENTIAL Example
The following SQL statement creates a differential back up of the database
"testDB":

Example
BACKUP DATABASE testDB
TO DISK = 'D:\backups\testDB.bak'
WITH DIFFERENTIAL;

Tip: A differential back up reduces the back up time (since only the changes are
backed up).

SQL Server CREATE TABLE

Introduction to the SQL Server CREATE TABLE statement
Tables are used to store data in the database. Tables are uniquely named
within a database and schema. Each table contains one or more columns. And
each column has an associated data type that defines the kind of data it can
store e.g., numbers, strings, or temporal data.

The SQL CREATE TABLE Statement

The CREATE TABLE statement is used to create a new table in a database.

Syntax
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
The column parameters specify the names of the columns of the table.

The datatype parameter specifies the type of data the column can hold (e.g.
varchar, integer, date, etc.).

Tip: For an overview of the available data types, go to our complete Data Types
Reference.

SQL CREATE TABLE Example

The following example creates a table called "Persons" that contains five
columns: PersonID, LastName, FirstName, Address, and City:

Example
CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

The PersonID column is of type int and will hold an integer.

The LastName, FirstName, Address, and City columns are of type varchar and
will hold characters, and the maximum length for these fields is 255 characters.

The empty "Persons" table will now look like this:

PersonID LastName FirstName Address City

Create Table Using Another Table

A copy of an existing table can also be created using CREATE TABLE.

The new table gets the same column definitions. All columns or specific columns
can be selected.

If you create a new table using an existing table, the new table will be filled
with the existing values from the old table.

Syntax
CREATE TABLE new_table_name AS
SELECT column1, column2,...
FROM existing_table_name
WHERE ....;

The following SQL creates a new table called "TestTables" (which is a copy of
the "Customers" table):

Example
CREATE TABLE TestTable AS
SELECT customername, contactname
FROM customers;

The SQL DROP TABLE Statement

The DROP TABLE statement is used to drop an existing table in a database.

Syntax
DROP TABLE table_name;

Note: Be careful before dropping a table. Deleting a table will result in loss of
complete information stored in the table!
SQL DROP TABLE Example
The following SQL statement drops the existing table "Shippers":

Example
DROP TABLE Shippers;

SQL TRUNCATE TABLE

The TRUNCATE TABLE statement is used to delete the data inside a table, but
not the table itself.

Syntax
TRUNCATE TABLE table_name;

SQL INSERT INTO Statement

he SQL INSERT INTO Statement
The INSERT INTO statement is used to insert new records in a table.

INSERT INTO Syntax

It is possible to write the INSERT INTO statement in two ways.

The first way specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);

If you are adding values for all the columns of the table, you do not need to
specify the column names in the SQL query. However, make sure the order of
the values is in the same order as the columns in the table. The INSERT INTO
syntax would be as follows:

INSERT INTO table_name

VALUES (value1, value2, value3, ...);

Demo Database
Below is a selection from the "Customers" table in the Northwind sample
database:

CustomerI CustomerName ContactName Address City PostalCode Country

D

89 White Clover Karl Jablonski 305 - 14th Ave. S. Seattle 98128 USA
Markets Suite 3B

90 Wilman Kala Matti Keskuskatu 45 Helsinki 21240 Finland

Karttunen

91 Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland

INSERT INTO Example

The following SQL statement inserts a new record in the "Customers" table:
Example
INSERT INTO Customers (CustomerName, ContactName, Address, City,
PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen
21', 'Stavanger', '4006', 'Norway');

Try it Yourself »

he selection from the "Customers" table will now look like this:

CustomerI CustomerName ContactName Address City PostalCode Country

D

89 White Clover Karl Jablonski 305 - 14th Ave. S. Seattle 98128 USA
Markets Suite 3B

90 Wilman Kala Matti Keskuskatu 45 Helsinki 21240 Finland

Karttunen

91 Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland

92 Cardinal Tom B. Skagen 21 Stavange 4006 Norway

Erichsen r

Did you notice that we did not insert any number into the CustomerID
field?
The CustomerID column is an auto-increment field and will be generated
automatically when a new record is inserted into the table.
Insert Data Only in Specified Columns
It is also possible to only insert data in specific columns.

The following SQL statement will insert a new record, but only insert data in the
"CustomerName", "City", and "Country" columns (CustomerID will be updated
automatically):

Example
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');

The selection from the "Customers" table will now look like this:

CustomerID CustomerName ContactName Address City PostalCode Country

89 White Clover Karl Jablonski 305 - 14th Ave. Seattle 98128 USA
Markets S. Suite 3B

90 Wilman Kala Matti Keskuskatu 45 Helsinki 21240 Finland

Karttunen

91 Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland

92 Cardinal null null Stavanger null Norway

SQL Server Data Types
SQL Server data types Overview
In SQL Server, a column, variable, and parameter holds a value that associated
with a type, or also known as a data type. A data type is an attribute that
specifies the type of data that these objects can store. It can be an integer,
character string, monetary, date and time, and so on.

SQL Server provides a list of data types that define all types of data that you
can use e.g., defining a column or declaring a variable.

Exact numeric data types

Exact numeric data types store exact numbers such as integer, decimal, or
monetary amount.

 The bit store one of three values 0, 1, and NULL

 The int, bigint, smallint, and tinyint data types store integer data.
 The decimal and numeric data types store numbers that have fixed
precision and scale. Note that decimal and numeric are synonyms.
 The following table illustrates the characteristics of the exact numeric
data types:

SQL Server Data Types

String data types:

Data type Description Max size Storage

char(n) Fixed width character 8,000 characters Defined width
string

varchar(n) Variable width character 8,000 characters 2 bytes + number of

string chars

varchar(max) Variable width character 1,073,741,824 2 bytes + number of

string characters chars

text Variable width character 2GB of text data 4 bytes + number of

string chars

nchar Fixed width Unicode 4,000 characters Defined width x 2

string

nvarchar Variable width Unicode 4,000 characters

string

nvarchar(max) Variable width Unicode 536,870,912

string characters

ntext Variable width Unicode 2GB of text data

string

binary(n) Fixed width binary string 8,000 bytes

 varbinary Variable width binary 8,000 bytes
string

varbinary(max) Variable width binary 2GB

string

image Variable width binary 2GB

string

Numeric data types:

Data type Description Storage

bit Integer that can be 0, 1, or NULL

tinyint Allows whole numbers from 0 to 255 1 byte

smallint Allows whole numbers between -32,768 and 32,767 2 bytes

int Allows whole numbers between -2,147,483,648 and 2,147,483,647 4 bytes

bigint Allows whole numbers between -9,223,372,036,854,775,808 and 8 bytes

9,223,372,036,854,775,807
 5-17 bytes
decimal(p,s) Fixed precision and scale numbers.

Allows numbers from -10^38 +1 to 10^38 –1.

The p parameter indicates the maximum total number of digits that can
be stored (both to the left and to the right of the decimal point). p must
be a value from 1 to 38. Default is 18.

The s parameter indicates the maximum number of digits stored to the

right of the decimal point. s must be a value from 0 to p. Default value
is 0

numeric(p,s) Fixed precision and scale numbers. 5-17 bytes

Allows numbers from -10^38 +1 to 10^38 –1.

The p parameter indicates the maximum total number of digits that can
be stored (both to the left and to the right of the decimal point). p must
be a value from 1 to 38. Default is 18.

The s parameter indicates the maximum number of digits stored to the

right of the decimal point. s must be a value from 0 to p. Default value
is 0

smallmoney Monetary data from -214,748.3648 to 214,748.3647 4 bytes

money Monetary data from -922,337,203,685,477.5808 to 8 bytes

922,337,203,685,477.5807

float(n) Floating precision number data from -1.79E + 308 to 1.79E + 308. 4 or 8 bytes

The n parameter indicates whether the field should hold 4 or 8 bytes.

float(24) holds a 4-byte field and float(53) holds an 8-byte field.
Default value of n is 53.

real Floating precision number data from -3.40E + 38 to 3.40E + 38 4 bytes

Date and Time data types:

Data type Description Storage

datetime From January 1, 1753 to December 31, 9999 with an accuracy of 3.33 8 bytes
milliseconds

datetime2 From January 1, 0001 to December 31, 9999 with an accuracy of 100 6-8 bytes
nanoseconds

smalldatetime From January 1, 1900 to June 6, 2079 with an accuracy of 1 minute 4 bytes

date Store a date only. From January 1, 0001 to December 31, 9999 3 bytes

time Store a time only to an accuracy of 100 nanoseconds 3-5 bytes

datetimeoffset The same as datetime2 with the addition of a time zone offset 8-10 bytes

timestamp Stores a unique number that gets updated every time a row gets created
or modified. The timestamp value is based upon an internal clock and does
not correspond to real time. Each table may have only one timestamp
variable

Data Type Lower limit Upper limit Memory

bigint −2^63 (−9,223,372, 036,854,775,808) 2^63−1 (−9,223,372, 036,854,775,807) 8 bytes
Data Type Lower limit Upper limit Memory
int −2^31 (−2,147, 483,648) 2^31−1 (−2,147, 483,647) 4 bytes
smallint −2^15 (−32,767) 2^15 (−32,768) 2 bytes
tinyint 0 255 1 byte
bit 0 1 1
byte/8bit
column
decimal −10^38+1 10^381−1 5 to 17
bytes
numeric −10^38+1 10^381−1 5 to 17
bytes
money −922,337, 203, 685,477.5808 +922,337, 203, 685,477.5807 8 bytes
smallmoney −214,478.3648 +214,478.3647 4 bytes

Approximate numeric data types

The approximate numeric data type stores floating point numeric data. They
are often used in scientific calculations.

Data Type Lower limit Upper limit Memory Precision

float(n) −1.79E+308 1.79E+308 Depends on the value of n 7 Digit

real −3.40E+38 3.40E+38 4 bytes 15 Digit

Date & Time data types

The date and time data types store data and time data, and the date time
offset.

Data Type Storage size Accuracy Lower Range Upper Range

datetime 8 bytes Rounded to increments of .000, .003, .007 1753-01-01 9999-12-31

Data Type Storage size Accuracy Lower Range Upper Range

smalldatetime 4 bytes, fixed 1 minute 1900-01-01 2079-06-06

date 3 bytes, fixed 1 day 0001-01-01 9999-12-31

time 5 bytes 100 nanoseconds 00:00:00.0000000 23:59:59.9999999

datetimeoffset 10 bytes 100 nanoseconds 0001-01-01 9999-12-31

datetime2 6 bytes 100 nanoseconds 0001-01-01 9999-12-31

If you develop a new application, you should use

the time, date, datetime2 and datetimeoffset data types. Because these
types align with the SQL Standard and more portable. In addition,
the time, datetime2 and datetimeoffset have more seconds precision
and datetimeoffset supports time zone.

Character strings data types

Character strings data types allow you to store either fixed-length (char) or
variable-length data (varchar). The text data type can store non-Unicode data
in the code page of the server.

CHAR(size) A FIXED length string (can contain letters, numbers, and special
characters). The size parameter specifies the column length in
characters - can be from 0 to 255. Default is 1

VARCHAR(size) A VARIABLE length string (can contain letters, numbers, and

special characters). The size parameter specifies the maximum
column length in characters - can be from 0 to 65535
Data Type Lower limit Upper limit Memory

char 0 chars 8000 chars n bytes

varchar 0 chars 8000 chars n bytes + 2 bytes

varchar (max) 0 chars 2^31 chars n bytes + 2 bytes

text 0 chars 2,147,483,647 chars n bytes + 4 bytes

SQL Server PRIMARY KEY

Introduction to SQL Server PRIMARY KEY constraint
A primary key is a column or a group of columns that uniquely identifies each
row in a table. You create a primary key for a table by using the PRIMARY
KEY constraint.

If the primary key consists of only one column, you can define use PRIMARY
KEY constraint as a column constraint:

CREATE TABLE table_name (

pk_column data_type PRIMARY KEY,
...
);