SQL Interview Questions

Kulkarni Prasad

Description

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 AnswersDescriptionThis 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. 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 will you 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 contents1. Database and SQL Basics2. DDMS SQL Statements and Clauses3. SQL Operators, Keywords, and Datatypes4. SQL Operators5. SQL Functions, Wildcards, and Dates6. SQL Joins and CASE Statement7. SQL DDL, DCL, and DTL Statements8. SQL Stored Procedures, Triggers, Views, and Transactions9. SQL Keys, Indexes, Injections, and Constraints10. SSRS, SSIS, SQL Cloud database (Azure), and JSON Support11. New features of SQL 2016, 2017, and 201912. SQL Performance Improvement Tips and Fuzzy Interview QuestionsAbout the authorPrasad 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. He started his journey with Microsoft technologies in 2007 with Visual Basic 6 and SQL 2000, then gradually moved to C#, ASP, ASP.NET, MVC and now .NET Core with SQL 2019.His Blog links: http://prasaddotnettricks.blogspot.com/His LinkedIn Profile: https://in.linkedin.com/in/prasad-kulkarni-389152a5

Sujets

• Informatique

Informations

• Publié par: BPB Publications
• Date de parution: 04 novembre 2019
• Nombre de lectures: 9
• EAN13: 9789388176606
• Langue: English

Extrait

SQL Interview Questions

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

by
Prasad Kulkarni
FIRST EDITION 2019
Copyright © BPB Publications, India
ISBN: 978-93-89328-462
All Rights Reserved. No part of this publication may be reproduced or distributed 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.
LIMITS OF LIABILITY AND DISCLAIMER OF WARRANTY
The information contained in this book is true to correct and the best of author’s & publisher’s knowledge. The author has made every effort to ensure the accuracy of these publications, but 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.
Distributors:
BPB PUBLICATIONS
20, Ansari Road, Darya Ganj
New Delhi-110002
Ph: 23254990/23254991
MICRO MEDIA
Shop No. 5, Mahendra Chambers,
150 DN Rd. Next to Capital Cinema,
V.T. (C.S.T.) Station, MUMBAI-400 001
Ph: 22078296/22078297
DECCAN AGENCIES
4-3-329, Bank Street,
Hyderabad-500195
Ph: 24756967/24756400
BPB BOOK CENTRE
376 Old Lajpat Rai Market,
Delhi-110006
Ph: 23861747
Published by Manish Jain for BPB Publications, 20 Ansari Road, Darya Ganj, New Delhi-110002 and Printed by him at Repro India Ltd, Mumbai
Dedicated to
My loved ones Baba, Aai, Jadi, Mau, Bundu
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, XML, etc. He is very passionate about Microsoft .NET technology. He likes to write articles and blogs on different aspects of SQL and .NET. He also likes to help developers resolve their issues and boost them on Microsoft Technologies.
Prasad has impressive certifications such as Microsoft Certified Professional (MCP), Microsoft Certified Technology Specialist (MCTS), and Agile Scrum Master. Prasad was 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 been awarded for his articles on CodeProject.
He started his journey with Microsoft technologies in 2007 with Visual Basic 6 and SQL 2000. Then, he gradually moved to C#, ASP, ASP.NET, MVC and now .NET Core with SQL 2019.
About the Reviewers
Manavya is a Technical leader by profession, enthusiast about cutting edge technologies. He has completed Master’s in computer science from Pune university and then after working as software professional from last 13 years. He likes to work on Microsoft technologies like SQL, .NET, MVC, jQuery.
Development is his hobby and Coding is passion
Acknowledgement
I would like to thank my mother, Mrs Lata Kulkarni, and my wife Mrs Apoorva, for their support. They deserve to have their name on the cover of this book as much as I do for all their support that made this possible. I would also like to thank my father, Shrikant Kulkarni, and my children, Mau and Manasi, for their love and support to help achieve my goals.
Last but not least, I would like to thank everyone at BPB Publications for giving me this opportunity to publish my book.
– Prasad Kulkarni
Preface
The database is the most essential or a crucial part in any modern technology. It is developed for web-based, desktop/laptop-based or any smart device-based application. We need the database to maintain data security, data storage, data integrity, handling large/huge data, create and maintain relationship between data, fetching of data in a well-organized way, increase the efficiency of the end user, avoid duplication and redundancy of data, and data sorting. For all these activities, we must know about the database concepts. Are you not aware of all these concepts? Don’t worry, this book will give you a complete idea about the SQL database. This book is divided into three parts: Beginner, intermediate, and advanced level.
This book starts with very basic concepts like what is data, database and DBMS. What are the advantages and disadvantages of SQL? Gradually, we move on to concepts of SQL datatypes and keywords.
In the intermediate part, we will learn about different SQL functions, wild cards, and playing with dates. We will also learn about SQL joins, case statement, DDL, DCL, and DTL concepts.
In the advanced part, we will go through SQL stored procedures, triggers, views and transactions, SQL keys, indexes, injection and constraints. SSRS, SSIS, cloud database (Azure), SQL hosting, and JSON Support. We will also learn about the new features of SQL 2016, 2017, CTP-2019, and SQL performance improvement tips. We will finally end with some fuzzy interview questions and answers.
Chapter 1 , Database and SQL Basics, talks about the basics of the database, which includes what is a database, types of databases, why to use it, etc. SQL basic, SQL tables, and SQL advantages.
Chapter 2 , DDMS SQL Statements and Clauses, describes SQL statements that are used to define and manipulate database and data, and the clauses that are used to filter and arrange data. It also describes the different SQL statements and clauses with examples.
Chapter 3 , SQL Operators, Keywords, and Datatypes, talks about the different SQL operators, SQL keywords, and clauses.
Chapter 4 , SQL Operators, discusses SQL operators like comparison, logical, assignment, bitwise, and set operators.
Chapter 5 , SQL Functions, Wildcards, and Dates, describes multiple functions of SQL, including dates function to select, manipulate, and arrange data. Additionally, SQL has a magical search technique where we can search with specific wild cards that match any characters and get the result. This chapter also talks about the different SQL functions, wildcards, and dates.
Chapter 6 , SQL Joins and CASE Statement, talks about how to get records from multiple tables with different selection criteria. It also talks about how we can use SQL Joins on them. It describes the various SQL Joins, their usage and SQL Case statements is used for the conditional executiona.
Chapter 7 , SQL DDL, DCL, and DTL Statements, discusses how to alter table or data structure or to delete any column, table or database. We need to use data definition statements. This chapter also talks about the different SQL DDL, DCL, and DTL statements.
Chapter 8 , SQL Stored Procedures, Triggers, Views, and Transactions, describes how to run some group of SQL statements again and again. It also describes stored procedures, and if you want to automatically fire/execute some group of SQL statements depending on the database changes, you can use Triggers. It talks about SQL views and transactions.
Chapter 9 , SQL Keys, Indexes, Injections, and Constraints, focuses on how to fetch/get or store data to/from a table. SQL uses different keys and in this chapter, we will review all SQL keys ad indexes. Additionally, we will learn about SQL injection and SQL constraints.
Chapter 10 , SSRS, SSIS, SQL Cloud database (Azure), and JSON Support, talks about how SQL can connect to JSON and interchange data. We will learn more about SQL and JSON connection, SQL reporting and integration services. We will also learn how to host your SQL to cloud server (Azure).
Chapter 11 , New features of SQL 2016, 2017, and 2019, walks you through the new features of SQL 2016, 2017, and 2019.
Chapter 12 , Fuzzy Interview Questions and SQL Performance Tips, gives an overview of some interview breaking fuzzy interview questions and SQL performance tips that will help you to boost the SQL query performance.
Errata
We take immense pride in our work at BPB Publications and follow best practices to ensure the accuracy of our content to provide with an indulging reading experience to our subscribers. Our readers are our mirrors, and we use their inputs to reflect and improve upon human errors if any, occurred during the publishing processes involved. To let us maintain the quality and help us reach out to any readers who might be having difficulties due to any unforeseen errors, please write to us at :
[e-mail]
Your support, suggestions and feedbacks are highly appreciated by the BPB Publications’ Family.
Table of Contents
1. Database and SQL Basics
Introduction
Data, database, and DBMS
Usage of database
Different 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 databases
Structured Query Language (SQL)
Data Definition Language (DDL)
Data Manipulation Language (DML)
Data Control Language (DCL)
Transaction Control Language (TCL)
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?)
2. SQL Statements, Keywords, and Datatype
Prerequisite for this chapter
Structure
SQL keywords
SQL datatypes
Numerical SQL datatypes
Exact numeric
Approximate numeric
The string and char SQL datatypes
Character string
Unicode string
Binary string
Date and time SQL datatypes
Date
Time
Datetime
Miscellaneous SQL datatypes
SQL statements
DDL statement
Create
Alter
Drop
Truncate
DML statement
Insert
Update
Delete
Select
Conclusion
Rapid fire questions and answers
Do you know (lights on fact?)
3. SQL Statements and Clauses
Introduction
Structure
Object