SQL Interview Questions

A complete question bank to crack your ANN SQL interview with real-time examples

Prasad Kulkarni

• Publisher: BPB Publications
• Year: 2019
• ISBN:
  9789389328462
• Topic: Computer Science
• Subtopic: Programming in JavaScript
• Index: Computer Science

Let us break the SQL interview with the help of SQL Server interview questions. Key Features

• Database Basic Concepts
• SQL Fundamentals
• DDMS, SQL Statements, and Clauses
• SQL Operators, Datatypes, and Keywords
• SQL Functions, Wildcards and Dates
• SQL Joins and CASE Statement
• SQL DDL, DCL, and DTL Statements
• SQL Stored procedures, Triggers, Views, and Transactions
• SQL Keys, Indexes, Injection, and Constraints
• SSRS, SSIS, SQL Cloud database (Azure), and JSON Support
• New features of SQL 2016, 2017, and 2019
• SQL Performance Improvement Tips
• Fuzzy Interview Questions and Answers

Description
This book gives you a complete idea about the SQL database. It starts from a very basic concept like what is a database, its usage, types, creation, and data storage, security, sorting, and searching for a stored procedure. This book is a complete set of interview breaking questions and answers with live examples and plenty of screenshots. This book takes you on a journey to mastering the SQL database, including SQL datatypes, functions, triggers, and stored procedures. This book also covers the latest and new features of SQL 2016, 2017 and 2019 CTP with examples.In the beginner section, we start with very basic concepts like what is a database, why to use a database, different types of database types, what is SQL, its usages, advantage and disadvantages, SQL datatypes, its different operators and how to use them with samples.In the intermediate section, we will learn about the different SQL functions, SQL Joins (used to fetch values from multiple SQL tables) and SQL DDL, DCL, and DTL commands. (About the last chapters) This is the advanced section of the book where we have provided an explanation of the SQL stored procedure, triggers and SQL view concepts, additionally, we have covered SQL core concepts like keys, indexes, injections and constraints. We have also introduced cutting-edge concepts like SSRS, SSIS, SQL Cloud database (Azure), JSON Support and a list of the new features of SQL 2016, 2017, CTP-2019 with SQL performance improvement tips. Finally, we have ended the book with a series of random SQL questions and answers. What you will learn
After reading this book, you will be able to understand SQL database concepts, handle core database activities like data security, searching, migration, and sorting. You will be able to handle the database transactions, use different SQL datatypes, functions, triggers, and stored procedures to save and retrieve data from the database. You will also be able to understand advanced SQL concepts like SQL reporting services, integration services, cloud database and new features from the latest SQL versions like 2016, 2017, and 2019. Who this book is for
This book is built in such a way that it is useful for all categories such as technical or non-technical readers. This book is perfect. If you are a fresher and you want to learn about SQL, or if you are a teacher and you want to spread SQL knowledge, this book is very helpful. If you want to crack the database interview or if you are working as a DBA and you want to upgrade your knowledge, or if you are backend developer, database tester, performance optimizer, or if your role is that of a database admin, SQL developer, data analyst, mobile app developer or if you are working on core SQL concepts, this book is just right for you.This book is very useful as it contains many simple real-time scenarios for each concept. All functionalities are explained with real SQL screenshots and database records. Table of Contents
1. Database and SQL Basics
2. DDMS SQL Statements and Clauses
3. SQL Operators, Keywords, and Datatypes
4. SQL Operators
5. SQL Functions, Wildcards, and Dates
6. SQL Joins and CASE Statement
7. SQL DDL, DCL, and DTL Statements
8. SQL Stored Procedures, Triggers, Views, and Transactions
9. SQL Keys, Indexes, Injections, and Constraints
10. SSRS, SSIS, SQL Cloud database (Azure), and JSON Support
11. New features of SQL 2016, 2017, and 2019
12. SQL Performance Improvement Tips and Fuzzy Interview Questions About the Author
Prasad Kulkarni is a Microsoft MVP reconnect, Technical leader, Author, Agile Scrum Master and Blogger. He has 13 years of core experience in Microsoft technologies such as SQL, ASP.NET, MVC, ASP.NET Core, VB.NET, SQL server, word Automation, Office development etc. and other technologies such as HTML, CSS, jQuery, JavaScript, Bootstrap, and XML etc. He is very passionate about Microsoft.NET technology. He likes to write articles and blogs on different aspects of SQL stuff and.NET, also like to help developers resolve their issues and boost them on Microsoft Technologies.Prasad has impressive certifications as Microsoft Certified Professional (MCP), Microsoft Certified Technology Specialist (MCTS) and Agile Scrum Master, Prasad was also awarded the most valuable member at dotnetspider, most popular curator, most active curator, and featured curator at Microsoft Curah, and editor at dotnetspider, he has awarded for his articles on codeproject. LinkedIn Profile: https://in.linkedin.com/in/prasad-kulkarni-389152a5

CHAPTER 1

Database and SQL Basics

Introduction

Welcome to the world of database. This chapter is a gateway to the database world, where you will learn what is database, what is DBMS, types of database, their usage, advantages, disadvantages, SQL Query execution plan, SQL table structure and how to play with them. Databases have been in use widely since last many decades (early 60’s). Databases are not only used to store data but to also help us make it more secure and manageable. By encryption and standard hashing techniques, databases are becoming more powerful to fight against data theft and cyber-attacks.

To follow this chapter, you do not need any prior knowledge of database (Yes, the only thing you should have is interest in learning database). The best thing about this chapter, rather this book, is that we have amazing sections like Rapid fire questions and answers and Do you know (lights on fact?) which will give you a different experience of subject knowledge as well as quick interview questions and answers for rapid revision.

After studying this chapter, you will learn the following points:

• Data, database, and DBMS
• Usage of database
• What are the different types of DBMS?
• Structured Query Language (SQL)
• Usage of SQL
• SQL Query execution plan
• SQL tables
• Advantages and disadvantages of SQL
• Rapid fire questions and answers
• Conclusion
• Do you know (lights on fact?)

So, let’s begin.

Data, database, and DBMS

First let’s understand what is data? Now, data is a simple object, it may be a living or a non-living thing. The following example will clear your doubts.

Let’s take an example, your name, address, blood group is data that is related to you (or we can say it represents you), so the data can be in any form, like text, image, files, binary, date, number, and may more.

Now we can move to the concept of database.

As the name suggests, database is the base of data. It’s a collection of data or arrangement of data at a central point, and it is arranged in such a manner that it can be easily managed. Earlier, data-base was maintained in the form of dossier (number of papers, hard-copies) but with time, we have gradually moved to a digitalized world where we store our data in electronic forms, and this is what the data-base in current world.

To manage (fetch, add, remove, or alter) all these stored data, we need some system. This is where Database management system (DBMS) is introduced. DBMS is a system which helps you define, modify, remove, and retrieve data. It also monitors data, apply security policy, observe performance, maintain data integrity, and more. DBMS mainly comprises of software that acts as a bridge between the user and the database. DBMS also provides a way to figure out a way as how data is going to be stored in a database and which structure to follow in doing so.

Now let’s walk through the features of database:

• Well organized data: Due to this feature, the data base concept is widely used; database keeps your data well organized.
• Security: Due to encryption like technique, database keeps your data secure and hidden from attackers.
• ACID: Database provides atomic, consistent, isolated and durable data transaction.
• Relation between the different entities and data across multiple database tables.
• Due to tabular representation and data storing technique and it is very easier to store and fetch data in database.

Usage of database

To answer that question, Database keeps the data well-organized so that it can be controlled easily.

We need database for the following things:

• The first and the very basic answer is we need a database to store data; database is a centralized place where we can find our data.
• As the data is centralized, it is easy to manage (add, remove, or alter),
• To enforce data security.
• To maintain large/huge data.
• To maintain data integrity. (Data integrity is nothing but consistency and accuracy of data).
• To simplify data fetching.
• To create and maintain relationship between data (this is a very important function of database).
• To increase the efficiency of the end user.
• To avoid duplication and redundancy.

Different types of DBMS

Database management system (DBMS) divides databases into different types. So, let’s see the different types of DBMS.

The database management system is not new, and over the years DBMS has improved a lot in its processes, and the way it handles data. Here are the types of DBMS.

• Navigational DBMS (Hierarchical DBMS)
• Network DBMS
• Relational DBMS (RDBMS)
• Object-oriented Database Management (OODBMS)
• Object-relational database management system (ORDBMS)
• No-SQL Database

Let’s trace them one by one in the following sections.

Navigational DBMS (Hierarchical DBMS)

Hierarchical database was invented in the 1960’s and it is a simple form of data storage. This database uses tree-like structure to store data. The structure looks simple but is difficult to manage as larger tree has many branches which make it more complex to handle large amount of data. Imagine a big tree with a lot of branches, can you count the number of branches or trace any specific branch easily? The answer is a NO. The perfect example of Hierarchical DBMS is Windows registry. Have you ever seen Windows registry? It has a lot of branch-like structure with roots and child combination. These databases are popular for their rapid data access, as each root is defined through a specific parent. In one to one relationship child and parent is only one, so the relationship is weak than that of one-to-many or many-to-many. Data is mostly linked in this database depending upon the how the linking flexibility data is grouped.

We can say IMS (Information Management System was developed by IBM), Windows registry and the RDM Mobile are examples of a hierarchical database.

We can define its structure as follows:

Figure 1.1: Logical data storing structure of Hierarchical DBMS

Network DBMS

This is a part of navigational database as we are navigating from one node to another and so on. We have seen in Hierarchical DBMS that it is difficult to maintain too many relations, and to overcome this limitation, Network DBMS was introduced in the late 1960’s. In this structure, any node can connect to any other node. So, each record has multiple parent and child (basically this forms a graph-like structure rather than a tree). We can define its structure as follows:

Figure 1.2: Logical data storing structure of Network DBMS

Relational DBMS (RDBMS)

This database management system brought a big revolution in the database world, where binding of data relation through tabular data format was made possible. This database management system uses tabular structure to store data (combination of rows and columns form a table which is then used to store data). The column has specific datatype to store data. This database does not directly have too many relationships, but it forms specific datatypes and integrity keys that can support any number of relationships. The concept of Relational database was founded in early 1970’s but it was truly implemented by dBase database in the 1980’s, when it had arranged data with relational mappings for the first time. Relational databases take up the minimum memory as they are storing only the linkages to related database instead of the whole table. These databases use Query language to manage data, see the below example to understand this:

I have 2 tables, one is Emp records and the other is Emp salary records. Here I am going to show the relation between two tables.

In the above table we can see only Emp No column from table A is linked to table B with the help of key (we will learn about this concept in coming chapters), so to fetch column Emp Name and Sal from table A and table B, I can use Emp No as linking. It also helps me to minimize column redundancy/duplication.

This is the most popular and most-used database management system. The popular example of this database management is Microsoft SQL, MySQL, Oracle, DB2, among others.

Object-oriented database management (OODBMS)

This DBMS treats each data value as a separate object and query on the database accordingly. This DBMS is developed by blending database abilities and programming abilities together. Here, a developer can program a database and develop software directly as both database and programming language uses the same representation model. Gbase, Wakanda, Realm are some of the popular examples of object-oriented database. These databases are also known as object databases.

Object-relational database management system (ORDBMS)

This is the next generation of Relational database management system where Relational database and Object database are mixed to get advantages of both objects and relation mapping. In this database management system, all object related concepts (like abstract, classes, inheritance, encapsulation) can be directly supported in query language. Today’s popular databases like MS-SQL, DB2, Oracle, and MySQL support these DBMS. These databases are well known for following ACID property (where A-Atomicity, C-Consistency, I-Isolation, D-Durability).

No-SQL databases

These databases are often called as Non-SQL databases, means no query language support is available to these databases, in short, and these databases are not using tabular structure to store data. No-SQL uses key-value pair, document, and graph-like data structure to store data, which is quite different from relational databases. No SQL follows CAP property where...