Learn T-SQL From Scratch An Easy-to-Follow Guide for Designing, Developing, and Deploying Databases in the SQL Server (Brahmanand Shukla) (z-library.sk, 1lib.sk, z-lib.sk)

(This page has no text content)

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 www.bpbonline.com

FIRST EDITION 2022 Copyright © BPB Publications, India ISBN: 978-93-91392-413 All Rights Reserved. No part of this publication may be reproduced, distributed or transmitted in any form or by any means or stored in a database or retrieval system, without the prior written permission of the publisher with the exception to the program listings which may be entered, stored and executed in a computer system, but they can not be reproduced by the means of publication, photocopy, recording, or by any electronic and mechanical means. LIMITS OF LIABILITY AND DISCLAIMER OF WARRANTY The information contained in this book is true to correct and the best of author’s and publisher’s knowledge. The author has made every effort to ensure the accuracy of these publications, but publisher cannot be held responsible for any loss or damage arising from any information in this book. All trademarks referred to in the book are acknowledged as properties of their respective owners but BPB Publications cannot guarantee the accuracy of this information.

www.bpbonline.com

Forewords SQL Server has evolved over the last 2 decades into one of the most prominent databases for enterprise application development out there. It is never too late to start learning and I wouldn’t miss an opportunity to learn SQL Server Coding from scratch from a book as useful as “Learn T-SQL from Scratch”. I have known Brahmanand Shukla (aka Brahma) when I worked with Ebixcash Financial Technologies as CTO. Brahma worked on some of the toughest and complex SQL Server projects in that stint with me. He is a SQL Server development authority and runs a SQL Server based blog & consultancy, SQL Server Carpenters working with marquee Customers in SQL Server area. The book is by practitioner for a to-be practitioner, by an expert developer for a novice developer. It explains the concepts, and quickly jumps into the code and implementation of it. It does so with simply explained concepts, pictures, code snippets, SSMS snapshots, with a flair for hands on approach. So, anyone aiming to start off quickly with SQL Server development, with no background, will find this book hard to keep down. From simple concepts like tables, indexes, functions, and procedures, it goes to touch upon advanced topics as handling of XML & JSON data, transactions, and error handling. Thanks to people like Brahmanand Shukla, author of this book, who share their practical knowledge with easy journey from basic

to advanced concepts, you can discover that learning T-SQL has never been this easy. — Pradeep Paliwal MTech (Data Science), BITS Piliani MBA (Systems), XIMB BE, VJTI Regardless of the topic, from martial arts, to woodworking, to flying a plane, and certainly to building and maintaining databases, fundamentals matter more than anything else. If you don’t have a good foundation for your building, it will collapse. Same thing goes for working with SQL Server. If you don’t have the fundamentals well understood and properly applied, you, and the organization you’re supporting, will suffer. All of this is why a book like this one is so important. While you’re reading through this book, keep that concept in mind. You need to understand the fundamentals of SQL Server in order to do basic tasks, yes, but also the advanced ones. The structure and layout of the book builds knowledge and doesn’t assume you already understand concepts. You’ll be walked up to topics carefully. You’ll get the fundamentals that are so important, vital in fact. So, study it well and be better at your database development, maintenance, administration, analysis, pretty much anything and everything to do with SQL Server.

—Grant Fritchey If you are looking to get started with T-SQL and SQL Server, this book is for you. This book guides a novice right from understanding basic database concepts, installation, which menu to click on Management studio, T-SQL concepts, and SQL query writing. Gradually, the book moves towards complex topics, such as transactions, XML handling, error handling, CTE, Indexes, stored procedures, views, triggers, and many more concepts. Moreover, Conclusion, Points to remember, and Test questions after every chapter just helps readers to revise and test themselves on where they stand. Kudos to Mr. Brahmanand to squeeze and pack his 15 years expertise in this book. —Shivprasad Koirala

Dedicated to Late. Shri Ramkishor Shukla & Late. Smt. Chandrakali Shukla My grandparents, the root of my existence. & Late. Shri Sitaram Pednekar My beloved uncle, the biggest motivation during my difficult times

About the Author Brahmanand Shukla is a SQL Server Consultant with over 13 years of experience in Software Design, Development and Support. Brahmanand started his journey with SQL Server 2000 in the year 2007, and since then the SQL Server has been his second wife. SQL Server is not the limit for Brahmanand. He has also designed and developed the databases in PostgreSQL, Oracle and Apache Cassandra. He has developed applications using VB 6.0, VB.Net, C#, ASP.Net, and Crystal Report. He has also delivered various cutting edge ETL solutions in SSIS. Brahmanand believes, the database is no different than an ordinary storage such as cupboard. He feels proud to be known as the SQL Server Carpenter. He started blogging on SQL Server way back in 2016 and launched his personal weblog He started his Consulting and Training firm under the same brand in 2020. He is also a community member of SQL Server Central. He can be reached out at Brahmanand.Shukla@SQLServerCarpenter.com

About the Reviewers Suresh Kumar comes with a rich experience of 20+ years in IT industry with specialization in Database technologies. He is an architect with expertise in redesigning legacy systems and performance tuning. He pursued BE in Electronics & Comm. from CR State College of Engineering, Murthal (known as DCRUST now). He started his professional career with Tata Consultancy Services in 2001 and has worked with fortune 500 companies like Hartford Insurance, AcNielsen. He is currently leading the development vertical at Scalability Engineers, Dehradoon. Sandesh Jadav comes with a rich experience of 12+ years in the IT industry. He is an expert full-stack developer skilled in various technologies ranging from ASP.Net, C#, Angular JS, JavaScript, SQL Server, SSIS, SSRS, and Business Objects. He is a full-stack developer but has more inclination towards the T-SQL and SQL Server. Besides, he is also a seasoned Technical Project Manager and has led various complex deliveries. In his current assignment, he is leading several business-critical applications at HDFC ERGO General Insurance. He is equally competent in various businesses of Finance domains such as Asset Management, Wealth Management, Investment Banking, and Insurance. He has worked with esteemed organizations like Financial Technologies, EbixCash.

He is a Computer Science post-graduate from the University of Mumbai. He also holds a Management degree from Wellingkar’s Institute, Mumbai.

Acknowledgement There is always someone behind one's success. I give this credit to Sai Kiran Gangam, my ex-colleague. It was because of his push, I started my blog and finally decided to write this book. I can never forget my other ex-colleagues Vinod Naidu and Saumitra Bhardwaj. They always pushed me and made me explore my capabilities. Vinod is also my best mate and Alexa since 2012. Alexa, because he is the one whom I approach for my questions. Vinod also contributed to this book at various stages. I would not have been able to write this book if my uncle Shri Baldev Prasad Shukla would not have supported my childhood education. I would not have known the ‘S’ of SQL Server, if my mentor and teacher Mr. Sikander Manihar would not have trained me and given me the first chance to work in the IT industry. It was Mr. Sikander who made me realize my potential. I’m indebted to my parents for everything they did for me. They struggled to raise their three kids, including me. I would not have been what I am without their support and blessings.

Life is a roller-coaster ride. I am blessed to have my family and friends who stand true to the aforesaid mentioned Sanskrit shloka. My brother Krishnanand and his wife Pooja, my sister Shivangi, my friends for life Santosh Gupta and Harkesh Sharma. They are witnesses of all my good and bad times. They stood by me always. This book has been written during the biggest pandemic I’ve ever seen. I’ve spent many nights, weekends and holiday on writing this book. It would not have been possible for me to take up the assignment to write this book, and complete it, without the support of my lovely wife Himani and two sons – Shashank and Shreyansh. Finally, I would like to thank BPB Publications for giving me this opportunity to write my first book for them.

Preface Data, isn’t it a fascinating word nowadays? Indeed! You would often hear people talking about it. With increasing data, there is a need of effective method to manage, process and query it. With the increasing digitalization, even the offline processes are being transformed into the digital online processes. It has nearly became impossible to handle such a vast data in the form of excels. The job profile of such data entry operators, and other people purely doing only physical processes, will demand to acquire new skills. Many data analysts who were earlier working on excel for more than 2 decades, are now told to upgrade their skills and learn SQL. Most of these data is now being maintained in the database, and most of them are based out of SQL. Data has opened many new job opportunities. Earlier only the application developers, database developers and database administrators were expected to have the knowledge of the SQL. But the game has changed now. Even the non-technical job profile demands the knowledge of SQL. Set of people feels, this change is taking lot of jobs. But we must understand the things changes along with the time. The best part is – it gives precious opportunities to people who were not earlier exposed to the IT. They can now become the integral part of IT, and can grow by gaining the SQL skills.

Imagine you’ve an excel file which has 10 sheets, each having millions of records. What would be the situation if you are told to perform an analytics over the data available in excel file. Opening such excel file itself is a challenge. It is also prone to crashes, and there is high possibility that you may lose the data, if such a crash happens. But, if you know SQL, the same excel file can be dumped to a relational database. You can write SQL scripts, and can perform various types of analytics. You can also easily combine the multiple datasets with simple SQL commands. You can do slice and dice the way you want, and can complete the data analytics quicker than excels. Such SQL queries can be reused as many times you want. This has truly transformed the way the data used to be dealt. There are multiple SQL based database in existence today. Most of them are relational database. We are talking about one of such relational database called SQL Server. It is a product offering of Microsoft. SQL is of various types. The base SQL is called ANSI SQL, which is a standard. Almost all the relational database supports this. But, most of them have their own flavor of SQL. Because standard can’t be customized. Hence the relational database have come up with their own version of SQL. In the same line T-SQL is the extension of SQL for the SQL Server and Sybase. If you know any kind of SQL, you’ll be able to work on almost all kinds of SQL. There are slight syntax change though which you can learn quickly.

Database opens multiple branches, and obviously opens multiple job opportunities. Here are few job profile which is based on SQL. Since we are talking about T-SQL in this book, so consider SQL Server as the database and T-SQL as SQL language. Business Analyst: They are basically IT people who understand the business well. They are kind of bridge between the Business and IT teams. Business Analysts are now also expected to know SQL to better analyze the data and requirement, before getting it developed. Their knowledge of SQL can also help them to properly plan the data migration, and define the data API’s required to integrate between two different applications. Database Architects: They are the founders of your database. They understand the business requirement, and effectively model your database. They define the structure and flow of objects in your database. They define the tables, columns, their datatypes etc. A best performing database is the outcome of a good data model. If you have one, you should appreciate your database architect. Database Developer: They are the people who develop the database based on the model shared by the Database Architect. They also develop the queries, and other programs as desired by the respective business. Database Administrator: They make sure the database servers and databases are up and running all the times, and obviously in a healthy state. It is also their responsibility to plan the hardware capacity such as Disk, Memory, and CPU etc. based on the data growth and load on the system. They also monitor your databases

and database servers, and tune the performance, if there is any performance hiccups. They also plan the database backups, and help you recover from the unplanned disasters. Data Analyst: They are the people who serve the business on their data requirements. They develop and run the queries as per the requirement and provide the data to the requestor. Data Scientists: They leverage the historical data to come up with the prediction or the various forecasting, based on the business demand. It doesn’t matter which profile you chose amongst mentioned aforesaid. You need to learn T-SQL. You must have now got an idea of why SQL is important and why you should learn it. We’ve tried in this book to keep the contents simple as much possible, so that even a reader who is not from IT and Programming background can learn T-SQL with ease. The overall book has 15 chapters as follows. Each of these chapters are the ladder to reach the goal of learning T-SQL: Chapter 1 (Getting started): You’ll understand the basics of Relation Database, SQL Server and SQL. You’ll also learn about the SSMS which is an IDE for SQL Server. Chapter 2 (Table): You’ll learn about the tables, columns, datatypes and constraints. You’ll be able to create, modify and drop the tables on your own.

Chapter 3 (Index): You’ll understand the indexes. You’ll learn to create and drop them. Chapter 4 (DML): You’ll learn about insert, update, delete and select commands. You’ll be able to create a new records, modify and delete existing records, and read the existing records. After this chapter you’ll be able to read the data from single table. You’ll also be able to filter and sort the result. You’ll also be able to fetch the top N records and perform the paging on the result- set. Chapter 5 (Built-In Functions - Part 1): You’ll learn about various built-in functions. After the end of this chapter, you’ll be able to perform aggregation, and also make use of various string, numeric and date built-in functions. These functions will be useful if you want to do more than just reading the raw data from the table. Chapter 6 (Join, Apply and Subquery): You’ll learn to combine the multiple tables to get a unified result-set. After the end of this chapter, you’ll be able to join multiple tables, and you’ll be able to perform advanced analytics by combining aggregation, sorting, filtering etc. Chapter 7 (Built-In Functions - Part 2): You’ll learn about various advanced built-in function. They are called – Window, Ranking and Analytic functions. After the end of this chapter, you’ll be able to perform complex queries, by combining the knowledge gained through Chapter 2 to

Chapter 8 (Dealing with XML and JSON): At the end of this chapter, you’ll be able to query the semi-structured data in the form of XML and JSON. You’ll be able to convert XML / JSON to Table and vice versa. Chapter 9 (Variables and Control Flow Statements): You’ll learn the basics of programing in this chapter. At the end of this chapter, you’ll be able to declare a variable, assign the value to it, and read the variable value. You’ll also be able to define control flow statements such as IF statement, CASE statement and loop. Chapter 10 (Temporary Tables, CTE and MERGE Statement): In this chapter you’ll learn to break a complex query into multiple smaller queries. You’ll also learn to store and use the intermediate result in temporary tables and table variables. Chapter 11 (Error Handling & Transaction Management): At the end of this chapter, you’ll be able to implement the Error Handling in your T-SQL program. You’ll be able to handle the errors, and implement the action upon an error, including the rollback mechanism. You would not want a half-baked solution. Learnings of this chapter will help you control the transactions in your program, and the way you commit or rollback it, especially in case of errors. Chapter 12 (Data Conversion, Cross-Database and Cross-Server Data Access): After completing this chapter, you’ll be able to

convert data between various types. You’ll also be able to access the data from various databases on the same or different servers. Chapter 13 (Programmability): At the end of this chapter, you’ll be able to package your T-SQL program in the form of programmability objects such as Stored Procedures, Functions, Triggers and Views etc. Each of these programmability objects have specific purpose. You’ll be able to decide when to use what and how. Chapter 14 (Deployment): You’ll learn the various deployment methods, and its pros and cons. At the end of this chapter, you’ll be able to choose the right deployment method for you.

Statistics

Uploader

Learn T-SQL From Scratch An Easy-to-Follow Guide for Designing, Developing, and Deploying Databases in the SQL Server (Brahmanand Shukla) (z-library.sk, 1lib.sk, z-lib.sk)

AI Reading Assistant

Passage locations

Tags

Text Preview (First 20 pages)

Registered users can read the full content for free

Comments 0

Blog & Notes

Recommended for You

Statistics

Uploader

Learn T-SQL From Scratch An Easy-to-Follow Guide for Designing, Developing, and Deploying Databases in the SQL Server (Brahmanand Shukla) (z-library.sk, 1lib.sk, z-lib.sk)

AI Reading Assistant

Passage locations

Tags

Text Preview (First 20 pages)

Registered users can read the full content for free

Comments 0

Reply to Comment

Edit Comment

Blog & Notes

Recommended for You