Category : SQL Server Tutorial For Beginners


Insert statement

In this chapter “Insert statement“, we are going to discuss T-SQL’s Insert statement which is used to add new records in a table. Whenever, we need to add a record in a table, INSERT T-SQL statement is used. We can insert single or multiple records at a time using INSERT statement. INSERT statement can be used in two ways as below:

  1. To insert fixed (hard-code) values
  2. To insert the result of a SELECT statement

Let’s discuss each of them in more detail with the help of example.

To insert fixed (hard-code) values

To insert fixed values in the table, below is the syntax:

Syntax:

INSERT INTO [<schema-name>.]<table-name>

[(column-1, column-2, column-3, …….., column-n)]

VALUES

(value-1, value-2, value-3, …….., value-n)

Where,

INSERT INTO is used to declare a new INSERT statement.

[<schema-name>.] is the name of the schema in which the table exists and is optional if the object exists … More


SELECT Statement

Now that we have been familiar with Create, Alter and Drop – Database and Table, it is time to be familiar with T-SQL statements. Let’s start this chapter with the definition of T-SQL followed by a detailed discussion on T-SQL’s SELECT statement.

Transact SQL (T-SQL)

Transact SQL (T-SQL) is a proprietary extension of SQL (structured Query Language) by Microsoft and Sybase to communicate with Microsoft SQL Server. T-SQL has support for procedural programming using control flow (if-else, Case), loop (while), cursor, recursion and local variable, etc. It has many inbuilt functions to support string operation, date and time processing, mathematical calculation, aggregate processing and many more.

In short, T-SQL is capable to deal all kinds of SQL Server requirements like; creating database, adding tables, creating relationship, constraints, managing users and roles, creating a backup, restore database etc.

T-SQL Statements

T-SQL Statements are the basic constructs of T-SQL Programming … More


Create, Alter and Drop – Database and Table

Now that you are familiar with database concepts and SQL Server Management Studio, it is time to begin our journey with T-SQL programming. In my opinion, to shine as a good T-SQL programmer, one must have a good understanding of T-SQL concepts and its capability.

In this chapter “Create, Alter and Drop – Database and Table”, we are going to learn, how we can create, alter and drop a database and table. We would also explore how to add columns to an existing table, change column definition and delete column from the table.

Below is the abstract of this chapter:

  1. {CREATE | ALTER | DROP} DATABASE – Used to create / alter / delete a database respectively.
  2. {CREATE | ALTER | DROP} TABLE – Used to add / modify / remove table in a database respectively.

 

1 {CREATE | ALTER | DROP} DATABASE STATEMENT

1.1 CREATE DATABASE

We … More


Exploring SQL Server Management Studio

In this SQL Server Tutorial, we have gone through DBMS, RDBMS and SQL ServerTable RelationshipsData IntegrityTypes of keys and Database normalization chapters. Now that we have a good understanding of database concepts, it is time to put our feet in T-SQL.

Before we begin our journey with T-SQL (MS SQL Server programming language) we need to be familiar with SQL Server Management Studio, also known as SSMS, an application software developed by Microsoft, to manage, configure and administer Microsoft SQL Server and all its components like Database Engine, Analysis services, Integration Services, Reporting Services etc. SQL Server Management Studio provides a centralized management platform for all kind of SQL related work. We can create a database, add tables, create relationships, constraints, functions, procedures, views, triggers etc. We can also define authorization on objects like who is authorized on which object and for what.

Lets … More


Database normalization

In previous chapter, “Types of keys“, we had a good discussion on keys and their types in SQL Server. In this chapter “Database normalization”, we are going to discuss database normalization process and normal forms. Lets start our discussion with definition of Normalization.

Normalization

Normalization is a process of minimizing redundant data from database by decomposing the columns of a table into multiple tables. This process increases the number of tables in the database but it minimizes the redundant (duplicate), inaccurate and inconsistent data in database. Decomposed tables are connected using relationships (primary keys and foreign keys) to reduce the insert, update and delete anomalies. During normalization process, database designer decides the actual structure of the tables and their relationships. It is easy to find and fix any design problem at this early stage. During normalization process, we have various normal forms with some conditions. A table … More


Types of keys

In previous chapter, we had a good discussion on “Data Integrity” and in this chapter “Types of Keys”, we are going to discuss Keys and their types in SQL Server. Lets start this topic by defining keys”.

What is Key?

Keys are fields in a table which participate in below activities in RDBMS systems:

  1. To create relationships between two tables.
  2. To maintain uniqueness in a table.
  3. To keep consistent and valid data in database.
  4. Might help in fast data retrieval by facilitating indexes on column(s).

SQL Server supports various types of keys, which are listed below:

  1. Candidate Key
  2. Primary Key
  3. Unique Key
  4. Alternate Key
  5. Composite Key
  6. Super Key
  7. Foreign Key

Before discussing each type in brief, have a look on the below image used as an an example to define types of keys.

Types of keys

Types of keys

Lets discuss each type in detail:

Candidate Key

Candidate key is a … More


Data Integrity

After having a good discussion on “Table Relationships” in previous chapter, we are going to discuss Data Integrity in this chapter. First, we would discuss the term Data Integrity, followed by a discussion on data integrity types, and finally, we would go through an image which summarizes the data integrity concepts in one sight view. Lets start the discussion with the definition first.

Data Integrity

In relational database management systems, data integrity ensures the accuracy, reliability and consistency of the data during any operation like as data manipulation, data retrieval, data storage, backup or restore operation etc. It also guarantees that the recording of data is accurate and as intended. Having any bad or unintended data in the database, is a failure of data integrity. For example, having sales detail in Sales table of a product not available in Product master is a failure of referential integrity.

Below … More


Table Relationships

We have already been familiar with “DBMS, RDBMS and SQL Server” in previous chapter. In this chapter named “Table Relationship”, we are going to understand the relationship between tables. In relational database management systems, tables are used to store every information in the form of columns and rows. Before moving to the topic “Table Relationship”, we would have a look on the definition of Database, Table, Column and Row, as these entities participate in relationships.

Database

Database is an organized collection of data for fast processing on these data. These data are arranged in such form that enables fast retrieval of information and manipulation on these data. In RDBMSs, database organizes its data in relational form only using tables, column and rows. We can define relationships between these tables.

Table

A table is a collection of related data in a structured form using columns and rows within a database. … More


DBMS, RDBMS and SQL Server

Lets start this tutorial with the chapter “DBMS, RDBMS and SQL Server”. Before starting T-SQL part of the SQL Server tutorial, we will learn basic concepts of database systems. In this chapter, we will explore “Database management systems” and its relational extension “Relational database management systems”. We will also have an introduction to Microsoft SQL Server at the end of the chapter. This chapter has necessary images to explain the concepts in pictured way which will help you to understand the topics with ease.

Database management system (DBMS)

Database management system is a software used to define, record, query, update and manage data in a database. DBMS stores data in a structured way (relational, hierarchical, flat files, objects etc), and executes operations requested by various users on these data. It interacts with user, receives commands, and runs it against the database. DBMS can interact with some other applications, if required. … More


SQL Server Tutorial – Prerequisite

To start with this tutorial, you need to download and install SQL Server (Latest version recommended) from Microsoft and to do some hands-on throughout this tutorial and for assignments purposes, you need to download and attach AdventureWorksDatabase also. Below you have some links, which might help you out in these prerequisites.

Prerequisite

Just two things for this “SQL Server Tutorial” and you all set to go with this tutorial.

  1. Install SQL Server – [Recommended – SQL Server 2014 or 2012]
  2. Download and attach “AdventureWorksDatabase”

If you don’t have installed SQL Server yet, no worries, here are the links which may help you.

To download SQL Server 2014;

https://www.microsoft.com/en-us/evalcenter/evaluate-sql-server-2014

For Step by Step installation of SQL Server 2014

http://social.technet.microsoft.com/wiki/contents/articles/23878.installing-sql-server-2014-step-by-step-tutorial.aspx

To download AdventureWorks2014 Database

https://msftdbprodsamples.codeplex.com/releases/view/125550

How to restore “AdventureWorks2014” Database

Download the MS Word file from below link and follow the instructions mentioned in “Install Adventure Works 2014 OLTP database from More