MS SQL Server

Module 1

Relational Database Management

System (RDBMS)

Disclaimer: This material is protected under copyright act AnalytixLabs ©, 2011-2018. Unauthorized use and/ or duplication of this material or any part of this material
including data, in any form without explicit and written permission from AnalytixLabs is strictly prohibited. Any violation of this copyright will attract legal actions
What is a Server?
A computer, device or program that is dedicated to manage network resources for carrying
out some specific tasks. The purpose of a server is to share data, resources and distribute
work.
DB, DBMS & RDBMS?
Database (DB) – It’s a organized collection of data stored in a standardized format.

Relational Database – A tabular database in which data is stored in tables with relationships
to other tables.

Database Management System (DBMS) – It’s a software platform that hosts or defines a
database on a hardware (computer system). It enables users to store, modify and extract
information/data from the database. Supports a query language (SQL).

Relational Database Management System (RDBMS) – It’s a database management system

based on the relational model.
Different relational databases that exist in market
Database Structure
Database
A database is a organized collection of information in a standardized format.

Database Terminology

• Table – A list of related information presented in a column/row format.

• Row is referred to as Record.
• Column in a table is a category of information referred to as a Field.
Database design
The organization of data according to a database model where database designer determines
what data must be stored and how the data elements interrelate.

A general process…
Step 1: Define the Purpose of the Database
(Requirement Analysis).
Step 2: Gather Data, Organize in tables and
Specify the Primary Keys.
Step 3: Create Relationships among Tables.
Step 4: Refine & Normalize the Design.
Step 5: Database Implementation.
ER Model – Basic Concepts
ER model defines the conceptual view of a database and is considered as a good option for
designing databases. It works around real-world entities and the associations among them.
ER model mainly comprise of
1. Entity – An entity can be a real-world object, a generalized class of people, places or
things for which data is collected, stored and maintained. E.g. Employee, Customer,
Vehicle, House etc.
2. Attributes – These are characteristics of the entities. All the entities have some attributes
or properties that give them their identity.
3. Keys – A field or set of fields in the records that can be used to identify the record.
Types of Attributes
• Simple attribute − Atomic values, which cannot be divided further. e.g. a student's phone number
is an atomic value of 10 digits.

• Composite attribute − Made of more than one simple aBribute. e.g. a student's complete name
may have first_name and last_name.

• Derived attribute − Derived aBributes are the aBributes that do not exist in the physical database,
but their values are derived from other attributes present in the database. e.g. age can be derived
from data_of_birth.

• Single-value attribute − Single-value attributes contain single value e.g. Social_Security_Number.

• Multi-value attribute − MulD-value attributes may contain more than one values. e.g. a person can
have more than one phone number, email_address, etc.
Entity-Set and Keys
Key is an attribute or collection of attributes that uniquely identifies an entity among entity
set. e.g. the email_id of a student makes him/her identifiable among students.
• Super Key − A set of attributes (one or more) that
collectively identifies an entity in an entity set.
Primary Key
• Candidate Key − A minimal super key is called a
candidate key. An entity set may have more than
one candidate key.

• Primary Key − A primary key is one of the Candidate Keys

candidate keys chosen by the database designer
to uniquely identify the entity set.
Primary Key
A primary key is a special relational database table column (or combination of columns)
designated to uniquely identify all table records.
A primary key’s main features are:

• There can be only one primary key in a table.

• It must contain a unique value for each row of data.
• It cannot contain null values.
Foreign Key
A foreign key is a field (or collection of fields) in one table that refers to the PRIMARY KEY in
another table. It is key element in the relational database which helps to establish a link
between multiple tables.

A foreign key’s main features are:

• There can be only one/more foreign keys in a table; which may consist of single or multiple
fields.
• It prevents invalid data from being inserted into the foreign key column, as it has to be one
of the values contained in the table it points to.
• The table containing the foreign key is called the child table, and the table containing the
primary key is called the referenced or parent table.
Relationships
The power of relational database lies in the relationship that can be defined between tables.
A database consisting of independent and unrelated tables serves little purpose.
The types of relationships include:
1. One to One – One entity from entity set A can be associated with at most one entity of entity set
B and vice versa.
2. One to Many – One entity from entity set A can be associated with more than one entities of
entity set B however an entity from entity set B, can be associated with at most one entity.
3. Many to One – More than one entities from entity set A can be associated with at most one
entity of entity set B, however an entity from entity set B can be associated with more than one
entity from entity set A.
4. Many to Many – One entity from A can be associated with more than one entity from B and vice
versa.
Relationships (continued..)

One to One Many to One

Order table Order table

+ +
Return table Customer table

One to Many Many to Many

Customer table Student table

+ +
Order table Subject table
ER Model to Relational Model
ER Model, when conceptualized into diagrams, gives a good overview of entity-relationship,
which is easier to understand. ER diagrams can be mapped to relational schema.
Mapping Entities:
• Create table for each entity.
• Entity's attributes should become fields of tables with their respective data types.
• Declare primary key.
EmployeeID
SSN
Employee_Name

DOB Employee
Address
DepartmentID
Passport_No
Example of physical tables in DB
Customer tables contains basic customer details, and order table has information of the
orders placed by the customers that exist in customer table.
Customer Table Order Table
Example of physical tables in DB
Customer tables contains basic customer details, and order table has information of the
orders placed by the customers that exist in customer table.
Foreign Key
Customer Table Order Table

Primary Key
Introduction to SQL
SQL (Structured Query Language) is a database language designed for managing data in
relational database management systems.
Today almost all RDBMS use SQL as the standard database query language. SQL is used to
perform all types of data operations in RDBMS.
Using SQL one can…
• create new databases
• create new tables in a database
• create views in a database
• insert records in a database
• update records in a database
• delete records from a database
• create stored procedures in a database
• retrieve data from a database (tables/views)
• set permissions on tables, procedures, and views.
• A lot more….
SQL commands are mainly categorized into four categories
1. DDL (Data Definition Language) – SQL commands that can be used to define the
database schema. It deals with descriptions of the database schema and is used to create
and modify the structure of database objects in database.
2. DML (Data Manipulation Language) – The SQL commands that deals with the
manipulation of data present in database belong to DML or Data Manipulation Language
and this includes most of the SQL statements.
3. DCL (Data Control Language) – DCL includes commands such as GRANT and REVOKE
which mainly deals with the rights, permissions and other controls of the database
system.
4. TCL (transaction Control Language) – TCL commands deals with the transaction within
the database.
DDL (Data Definition Language)

The Data Definition Language manages table and index structure. Following are the
statements used in DDL

1. CREATE is used to create the database or its objects (like table, index, function, views,
store procedure and triggers).
2. DROP is used to delete existing objects from the database.
3. ALTER is used to alter the structure of the database.
4. TRUNCATE is used to remove all records from a table, including all spaces allocated for
the records are removed.
5. RENAME is used to rename existing object in the database.
6. COMMENT is used to add comments to the data dictionary.
DML (Data Manipulation Language)

The SQL commands that deals with the manipulation of data present in database belong to
Data Manipulation Language and this includes most of the SQL statements.

1. SELECT is used to retrieve data from the a table.

2. INSERT is used to insert data or new records into a table.
3. UPDATE is used to update existing data within a table.
4. DELETE is used to delete records from a database table.
DCL (Data Control Language)

Data Control Language includes commands which deals with the access management i.e.
rights, permissions and other controls of the database system.

1. GRANT gives user’s access privileges to database.

2. REVOKE withdraw user’s access privileges given by using the GRANT command.
TCL (Transaction Control Language)

TCL commands deals with the transaction within the database.

1. COMMIT commits a Transaction.

2. ROLLBACK rollbacks a transaction in case of any error occurs.
3. SAVEPOINT sets a save point within a transaction.
4. SET TRANSACTION specify characteristics for the transaction.
MS SQL Server
Relational Database Management System (RDBMS) developed by Microsoft. It is a highly
scalable product that can run on anything from a single laptop, to a network of high-powered
cloud servers.
Like all major RBDMS, SQL Server supports ANSI SQL, the standard SQL language.

The newest version is “SQL Server 2017”

We have different editions of SQL Server namely Enterprise, Standard, Web, Business
Intelligence etc. where SQL Server Express is free to download and use
MS SQL Server consists of
• Database Engine is the core MS SQL Server technology that creates and drives relational
databases; it is the core service for storing, processing, and securing data. It doesn’t have
any graphical interface; it is just a service running in the background of your computer
(preferable on the server).

• Management Studio is graphical tool for configuring and viewing the information in the
database. It can be installed on the server or on the client (or both).

and lots of other components…..

SQL Server Management Studio (SSMS)
SSMS is a GUI tool included with SQL Server for configuring, managing, and administering all
components within MS SQL Server. The tool includes both script editors and graphical tools
that work with objects and features of the server.

Version of SQL Server Management Studio is also available for SQL Server Express Edition, for
which it is known as SQL Server Management Studio Express.
SQL Comments

Comments are used to explain sections of SQL statements, or to prevent execution of SQL
statements.
Single Line Comments: Start with --
Any text between -- and the end of the line will be ignored (will not be executed).

Multi-line Comments: Start with /* and end with */

Any text between /* and */ will be ignored.
CREATE DATABASE
Syntax:
CREATE DATABASE databasename;

Example:
CREATE DATABASE dbCustomer;
DROP Database
Syntax:
DROP DATABASE databasename;

Example:
DROP DATABASE dbCustomer;
SELECT a Database

In case multiple databases exists in SQL Schema, then before starting operation on
DB, select a database where all the operations would be performed.

Syntax:
USE databasename;

Example:
USE dbCustomer;
CREATE TABLE

Syntax:
CREATE TABLE tablename (
column1 datatype constraint,
column2 datatype constraint,
.....
columnN datatype constraint,
table constraint (one or more columns)
);
Data types in MS SQL Server (not a comprehensive list)
SQL Constraints

Used to limit the type of data that can go into a table. This ensures the accuracy and
reliability of the data in the table. Constraints can be column level or table level.

The following constraints are commonly used in SQL:

• NOT NULL - Ensures that a column cannot have a NULL value.
• UNIQUE - Ensures that all values in a column are different.
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in
a table.
• FOREIGN KEY - Uniquely identifies a row/record in another table.
Continued..
• CHECK - Ensures that all values in a column satisfies a specific condition.
• DEFAULT - Sets a default value for a column when no value is specified.
• IDENTITY - Value in the field would be auto generated by the system when we insert data in
to the table.
Example
Create a table “CUSTOMER” which has the following columns and data types.

CREATE TABLE CUSTOMER (

CustomerId int IDENTITY(1,1) PRIMARY KEY,
CustomerNumber int NOT NULL UNIQUE,
LastName varchar(50) NOT NULL,
FirstName varchar(50) NOT NULL,
AreaCode int NULL,
Address varchar(50) NULL,
Phone char(10) NULL DEFAULT '0000000000'
);
Best Practice

When creating tables you should consider following these guidelines:

• Tables: Use upper case and singular form in table names – not plural, e.g. CUSTOMER.
Never use spaces inside the table names.
• Columns: Use Pascal notation, e.g. “CustomerId”. Never use spaces inside the column
names.
• Primary Keys: Use Integer and Identity(1,1) columns for Primary Keys. Name primary key
column the same as the table name + Id
ALTER TABLE

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

To add a column in a table, use the following syntax:

ALTER TABLE table_name ADD column_name datatype;

To delete* a column in a table, use the following syntax:

ALTER TABLE table_name DROP COLUMN column_name;

To change the data type of a column in a table, use the following syntax:
ALTER TABLE table_name ALTER COLUMN column_name datatype;

*Some database systems don't allow deleting a column

INSERT INTO

The INSERT INTO statement is used to insert a new row/s in a table

Syntax:
INSERT INTO table_name
VALUES (value1, value2, value3,...)

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

VALUES (value1, value2, value3,...)
Example
INSERT INTO CUSTOMER VALUES ('100', 'Smith', 'John', 12, 'California', '11111111’);

INSERT INTO CUSTOMER

(CustomerNumber, LastName, FirstName, AreaCode, Address, Phone) VALUES
(‘101', 'Smith', 'John’, 14, 'California', '11111111’);

INSERT INTO CUSTOMER

(CustomerNumber, LastName, FirstName) VALUES
(‘102', 'Smith', 'John’);
**You at least need to include all columns that cannot be NULL.
UPDATE

The UPDATE statement is used to update existing records in a table.

Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE
some_column = some_value;

*Notice the WHERE clause in the UPDATE syntax. The WHERE clause specifies which record or records should be updated.
*If you omit the WHERE clause, all records will be updated!
Example
UPDATE CUSTOMER
SET
AreaCode = 46, LastName = ‘Fenn', FirstName = ‘John'
WHERE
CustomerId = 1;

Always include the WHERE clause when using the UPDATE command!
DELETE

The DELETE statement is used to delete the records in a table.

Syntax:
DELETE FROM table_name
WHERE
some_column = some_value;

*Notice the WHERE clause in the DELETE syntax. The WHERE clause specifies which record or records should be deleted.
*If you omit the WHERE clause, all records will be DELETED from a table!
Continued…

It is possible to delete all rows in a table without deleting the table. This means that the table
structure, attributes, and indexes will be intact.
Syntax:
DELETE FROM table_name;

Make sure to do this only when you really mean it! You cannot UNDO this statement!
Example
DELETE FROM CUSTOMER
WHERE
CustomerId = 1003;

Always include the WHERE clause when using the DELETE command!
SELECT

The SELECT statement is used for retrieving rows from the database and enables the
selection of one or many rows or columns from one or many tables in the database.
Syntax:
SELECT
[ ALL | DISTINCT ]
[TOP ( expression ) [PERCENT] [ WITH TIES ] ]
select_list [ INTO new_table ]
[ FROM table_source ] [ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]
Example

SELECT * FROM CUSTOMER;

SELECT CustomerId, LastName, FirstName FROM CUSTOMER;

SELECT * FROM CUSTOMER ORDER BY FirstName, LastName DESC;

SELECT * FROM CUSTOMER WHERE CustomerNumber = '1001’;

SELECT * FROM CUSTOMER

WHERE LastName = 'Smith’ AND (FirstName = 'John’ OR FirstName = 'Smith’);
Contact Us

Visit us on: http://www.analytixlabs.in/

For assistance, email us: [email protected]

Call us we would love to speak with you: (+91) 9555219007