Learn T-SQL From Scratch

An Easy-to-Follow Guide for Designing, Developing, and Deploying Databases in the SQL Server and Writing T-SQL Queries Efficiently

Brahmanand Shukla

• Publisher: BPB Publications
• Year: 2021
• ISBN:
  9789391392413
• Topic: Computer Science
• Subtopic: Microsoft Programming
• Index: Computer Science

Advance your career as an SQL Server developer and DBA

Key Features
? Cutting-edge coverage from community experts to learn T-SQL programming.
? Detailed explanation of concepts and techniques for easy understanding.
? Numerous practical demonstrations of T-SQL querying and programming applications.

Description
This book will teach you the fundamentals of SQL, SQL Server, databases, and how to write queries and programs using T-SQL. After reading this book, you will be able to create, modify, and delete databases, tables, and indexes. You can practice querying the data and running complex analytics on it. You will also be able to add, delete, and modify procedures, user-defined functions, triggers, and views.The journey of learning T-SQL with this book begins with an understanding of SQL and database fundamentals. You'll explore the SQL Server Management Studio (SSMS) used for developing and managing SQL Server databases. You'll then learn how to use DDL statements to create, modify and delete tables and indexes. Gradually, you'll be able to query in T-SQL using DML statements, joins, and various built-in functions. Successively, you'll learn XML and JSON data processing, and by the time you'll reach the end of this book, you will learn to program in SQL Server and various strategies to deploy your databases and programs.Throughout the book, you'll learn through simple examples and straightforward explanations, diagrams, and numerous real-world use-cases.

What you will learn
? Concise understanding of relational databases and the SQL Server.
? Learn how to create database tables and indexes using T-SQL.
? Learn to add, modify, and delete records.
? Practice how to slice and dice data by running smart T-SQL queries.
? Perform advanced analytical analysis using various functions.
? Discover Error Handling and Transaction Management.
? Administer XML and JSON handling with T-SQL.
? Practice different deployment modes for T-SQL objects.

Who this book is for
If you want to know how to design, develop, and maintain SQL Server databases and run sophisticated T-SQL queries without much hassle, this book is for you. Readers with a basic understanding of programming would have an advantage.

Table of Contents
1. Getting started
2. Tables
3. Index
4. DML
5. Built-In Functions - Part 1
6. Join, Apply, and Subquery
7. Built-In Functions - Part 2
8. Dealing with XML and JSON
9. Variables and Control Flow Statements
10. Temporary Tables, CTE, and MERGE Statement
11. Error Handling and Transaction Management
12. Data Conversion, Cross Database, and Cross-Server Data Access
13. Programmability
14. Deployment

CHAPTER 1

Getting Started

This chapter will provide you with an overview of the fundamentals of relational database and SQL Server. You will understand the overview of the physical architecture of SQL Server. You will also understand the various versions and editions of SQL Server.

This chapter will also talk about of what are SQL, T-SQL, and various T-SQL commands. Most importantly, you will learn about the SSMS, which is an Integrated Development Environment (IDE), used for developing and managing the SQL Server database and writing T-SQL queries.

This chapter will act as a founding stone in the process of learning T-SQL.

Structure

In this chapter, we will cover the following topics:

• Database
• DBMS
• RDBMS
• ACID
• T-SQL
• SQL Server physical architecture
• SQL Server version and editions
• SQL Server download and installation
• SQL Server Management Studio (SSMS)

Objectives

After reading this chapter, the readers will be able to:

• Understand the fundamentals of the relational database and SQL Server.
• Learn about SSMS and its various features.
• Apprehend the concepts, terminologies, and SSMS.

Database

We will not talk about a lot of theories on database. However, it is important to know what does a database mean?

You would have heard words such as data and database many a times.

Data in simpler terms is information, which can be in various forms. Data could be information written on a piece of paper. It could be a file (of any type). It could be sound/video recording stored in a file. It could be educational records, medical records, legal records, employment records, and any other records you can think of.

Data is everywhere and everything is data.

As we all know, a cupboard is a storage unit, which we use to store our stuff. In the same way, a database is the storage unit to store data.

There are various types of databases in existence. Even Excel files, text files, and so on are databases. A folder is also a database that stores the files.

I remember my college days. We used to have floppy disks. Floppy disks had limited storage capacity in KBs. The maximum capacity of the floppy disk was 1.44 MB. Then, CDs came in the picture with a higher storage in 100 MB. CDs were replaced by DVDs, which had a storage capacity in GB. DVDs were later replaced by pen drives. Today, we can store 100 GB of data in a tiny pen drive.

The purpose of sharing this experience is to make you understand how data is growing day by day.

The world today is digital. Digital technologies and the Internet have truly made the world a family. We are connected with each other, irrespective of wherever we are with Internet. Recently, we were attacked by the worst pandemic of the recent times called COVID-19.

We could handle the situation in such a worst pandemic only because of technology. People are working from home. Companies are closing their offices and making their employees work from home permanently.

We can avail almost any service from our mobile phone, without stepping out of our home.

Just imagine the amount of data that is being produced today! You would have also experienced that Google, Facebook, and so on are selling ads to its consumers based on their search history, buying patterns, and so on. Amazon is suggesting the products based on the customers’ buying choices and patterns. We are able to predict the monsoon, track the storms, and take precautionary measures to save precious lives.

This would not have been possible without data. Obviously, data just stored is nothing, unless mined to make purposeful information out of it.

With such an increase in the adaptation of digital technologies and a drastic increase in the volume of data day by day, there was a need to have specialized databases to store, retrieve, and make meaningful analytics out of available data.

That is why databases were evolved further in DBMSs. We’ll understand about DBMSs in the upcoming topics.

Table, row, and column

Have you seen an Excel file? It contains multiple sheets. Each sheet is a table. A sheet has multiple columns which are named as A, B, C, D, …, and so on and rows 1, 2, 3, …, and so on. The number of rows supported generally depends on the version of Excel.

To summarize, .xls or xlsx file is a database that contains multiple tables in the form of sheets and each table is a collection of rows and columns. The following screenshot shows the Excel file:

Figure 1.1: Table, row, and column in excel

From figure 1.1, we understand that:

• Book1 is a database.
• Sheet1, Sheet2, and Sheet3 are tables.
• A, B, C, and so on are columns and 1, 2, 3, and so on are rows.

Similarly, a database stores the data in the form of a table which is composed of rows and columns. The simple definition of a database and table could be a database is a collection of tables. A table is a collection of rows and columns.

Excel is not RDBMS.

DBMS

Databases were further evolved into Database Management Systems (DBMSs). DBMS is a packaged database solution with built-in handling to interact with both the database and user. These solutions allow users to connect to the remote database. Most of them have their own language. These languages are used to store the data in the database and to retrieve the stored data and perform analytics over it.

There are various kinds of DBMSs such RDBMS, DDBMS, and so on. However, in this book, we will focus only on RDBMS.

RDBMS

RDBMS stands for Relational Database Management System. These are specialized databases built to store data with proper relationships called relations.

We will understand the relations along with few other important concepts using an example of a well-known two-wheeler manufacturer Royal Enfield.

De-normalization

The following table shows the sample data of purchase orders. PurchaseOrder is used for recording the information of all the purchases. It has various attributes as can be seen in the following table. We have taken the sample data set. The actual data set can have many more attributes.

You’ll also observe that few attributes such as CustomerName, CustomerAddress, CustomerMobile, and ProductName are repeated for each order. The customer John has three orders and each order has exactly the same customer information. This kind of dataset is called as de-normalized data.

The process of including all the required attributes in the same table is called de-normalization. As you can see in the following table, we have all the attributes of the purchase order in a single table:

Table 1.1: De-normali...