- Types of data
- What is SQL?
- History of SQL
- Why we need SQL?
- Applications of SQL
- What is the SQL server?
- Top SQL interview questions
In this article, we are going to discuss the SQL server must know questions for an interview. To start with, let’s take a glance over some SQL basics.
Types of data:
In this digital age, data is growing exponentially. The growth is tremendous that we humans have started studying and teaching subjects like Big Data Analysis. Machine learning techniques are applied to vast amounts of data to gain insights about the hidden patterns. These insights help businesses make difficult and profitable decisions. Using Big data techniques has become trendy in the marketing and advertising section.
All this data can be categories in three categories:
- Structured data: This data has a predefined schema (in layman language ‘a blueprint’) which defines what type of data will be stored in the store.
- Semi-Structured Data: This data has some structure to it but is not defined completely.
- Unstructured data: Most of the data generated digitally is unstructured data. It is hard to work with but is the most valuable data when it comes to discovering hidden patterns.
In this article, we will talk about the Structured Query Language also known as SQL. SQL is implemented in relational databases. These come under the category of structured data.
In the relational database, the data is stored in the form of a table. Relational databases are datasets where the tables are connected with each other with some relation. For example, as table customers can have a field name favorite product_ID, which could also be present in table Product_information.
What is SQL?
SQL (Structured Query Language) is a query language used to retrieve and manage relational databases. SQL is primarily used for structured datasets, which maintains ACID properties.
Atomicity: The transaction is either completely done or not done at all. The status is always either 0 or 1, there is no grey area. This property of relational databases is used to prevent confusion during transactions. For example: if you transfer money and due to some issue the transaction is left incomplete there has to be a refund of the money cut from your account. Thus the transaction has to be canceled. Without Atomicity the money cut will not be returned as the incomplete transaction will be considered valid.
Consistency: The data in the dataset should be the same all over the dataset copies. The same query in copies of the same dataset should provide the same answer. The update methods should update all the copies of the dataset stored in the system.
Isolation: The state of the transaction is invisible to other transactions, which means the transaction is isolated.
Durability: The data should be durable, it should not be volatile that changes continuously, the data should persist in the dataset as long as the data is needed.
The relational database maintains all the ACID properties.
History of SQL:
In the 1970s two researchers of IBM, Raymond Boyce and Donald Chamberlin developed the structured query language. It was then known as SEQUEL. The creation of SQL was inspired after Edgar Frank Todd’s paper on relational databases was published.
The idea of SQL was conceived when there was a need for a language to retrieve and manipulate data. Oracle in 1979 commercially released a different version of SQL.
Why do we need SQL?
When dealing with Big Data, manually handling data is not feasible. A global common language is required to ease the task of querying and handling the data. SQL was created for this task. Imagine examining every record for a certain query manually. When the records are in thousands it is not humanly possible to scan every record. Human error ratios is also more than machines.
To retrieve, manipulate and store data in a relational database as a type of query language was required. SQL is a standard language for this purpose. When the database is large, instead of manually handling the data some type of systematic way is required to control the data. SQL gives a systematic approach to the handling of data. The language has syntax and semantics making it useful globally.
Applications of SQL:
SQL is one of the most popular query languages due to its easy syntax and semantics. There are numerous applications of SQL such as:
- SQL helps in accessing data in large datasets (Relational Database Management System)
- SQL defined the data stored thus describing the stored data.
- SQL provides a wide range of functions that can be used to manipulate data.
- SQL can delete, create tables. We can also drop tables using SQL queries.
- SQL provides a wide range of constraint keywords that help in the selection of specific data that is data relevant to the person querying.
- SQL can help users set permissions over data to ensure security.
What is SQL Server?
SQL Server was designed by Microsoft as a relational database management system. It was designed to compete with MySQL developed by Oracle.
SQL Server supports standard Structured Query Language (ANSI SQL), but it has its version of SQL called T-SQL (Transact-SQL). T-SQL is a Microsoft developed language and it can implement facilities like exception handling etc. The interface tool for SQL Server is SQL Server Management Studio (SSMS), it supports 32 and 64 bit systems.
TOP SQL Server interview questions
1. Differentiate MySQL and SQL Server
MySQL is an open-source while SQL Server is commercial. MySQL has C and C++ as implementation languages while SQL Server only has C++. SQL Server supports C#, PHP, Python, Ruby, R, Visual Basic, Java, etc and MySQL supports c++, C#, Java, PHP, Perl, Python, Ruby, Tcl, Delphi, D, etc.
2. What are the different modes of SQL Server and how can you start those modes?
There are three modes of SQL Server given below
- Single-user Mode (-m):
sqlcmd –m –d master –S sample–c –U sa –P *******
- DAC (-A):
sqlcmd –A –d master –S sample –c –U sa –P *******
ALTER DATABASE sample_db SET EMERGENCY
3. What is the difference between Primary Key and Unique Key in Relational Database Management System?
The primary key is the identity index of the table. It cannot have repeated values and it also does not support NULL values.
Unique Key uniquely identifies a coulmn it allows NULL values.
4. What are the Candidate key, Alternate key, and Composite key?
Candidate key: Candidate key can uniquely define a row in the database.
Alternate key: If the database has multiple candidate keys, one is selected to be the primary key while others become alternate keys.
Composite key: More than one key is used to uniquely identify the row.
5. What are the authentication modes in SQL Server?
SQL Server has two authentication modes – Windows Mode and Mixed Mode
6. Which key should one use to encrypt SQL Server database?
AES (Advanced Encryption Standard) should be used to secure the SQL Server database. The key length decides the complexity. Large keys are preferred but they increase computation time so the key should be selected properly.
7. How to identify active and passive nodes?
Cluster administrator can check the current owner of the SQL Server, every other node is passive and the current node is active.
8. What is the SQL profiler?
The administrator has to monitor all the events happening in the database. SQL profiler is used to allow administrators to monitor and save data regarding each event for further analysis.
9. Global temporary table and Local Table, differentiate.
The local tables are only visible when there is a connection open to them after the connection is closed the tables are deleted
CREATE TABLE #<tablename>
Global temporary tables are visible to every user, but when the connection that created them is closed they are deleted.
CREATE TABLE ##<tablename>
10. What is a subquery and what are its types?
A subquery is a type of query which can be nested inside the main query. A subquery is always written on the right-hand side of the main query inside parentheses and it does not support the order by clause. Subqueries can be nested inside each other.
Types of a subquery: Single-row subquery, Multiple row subquery, and Multiple column subquery.
- Single-row subquery returns a single row to the main query.
- Multiple row subquery returns multiple rows to the main query.
- Multiple column subquery returns multiple columns to the main query.
11. Database normalisation/ What is database normalisation/Types of data normalisation?
Database normalization is a bottom-up technique which done to organize data in the relational database and reduce redundancy. Normalization reduces the interdependencies of datasets. It usually involves partitioning large tables into smaller ones.
Types of Normalisation:
- First Normal Form (1NF)
- Second Normal Form (2NF)
- Third Normal Form (3NF)
- Boyce-Codd Normal Form (BCNF)
- 4th Normal Form
- 5th Normal Form
- 6th Normal Form
12. What is the SQL Server agent?
SQL Server works with the scheduler engine and makes sure all the day to day tasks are being performed at scheduled date and time.
13. Trigger and types of triggers.
When insert/update/delete commands are fired on a table triggers are used to execute a batch of tables. Triggers are triggered automatically when the data in the database is modified.
There are four types of triggers a: Insert, Delete, Update and Instead of
14. What is Bulkcopy?
It is a tool used to copy and load a large amount of data from the tables to the SQL Server.
15. What is a stored procedure?
A stored procedure is a precompiled set of statements/queries. It is a collection of T-SQL statements. They are used because their pre-compiled form saves computation time.
16. What is the purpose of the Intent lock in SQL Server?
There are two types of intent locks Shared(S) and Exclusive(X). These locks are applied to the resources. They prevent unnecessary modification of a higher-level resource which can invalidate lower-level resource thus maintaining consistency.
17. Main steps of data modeling
There are two main steps of data modeling, Logical and Physical.
The logical part contains Planning of the model, analysis of the model and designing the model.
The physical part contains Designing of the model, Implementation of the model and maintenance of the model once it is in the market.
18. Difference between Dynamic procedure and a stored procedure.
The dynamic procedure is compiled during runtime and it takes more computation time to execute while the stored procedure is precompiled.
19. Where does SQL server stores stored procedures?
Stored Procedures are stored in a table named Sys.SQL_Modules. The script is stored in Sys.SQL_Modules. The procedure names are saved in Sys. Procedures.
20. What are Magic Tables in SQL Server?
Magic tables are tables that act as a buffer to the data when operations like Insert, Delete and update are fired. These tables are used in triggers.
21. What are cursors? Types of cursors.
To process the result set of a query row by row cursors are used.
There are four types of cursors:
- Static – It copies the data into a table name tempdb and this copy is temporary.
- Dynamic – All the changes by the cursor are reflected in the base table.
- Forward-only – Cursor is only able to fetch the records sequentially.
- Keyset-driven – It is the Keyset used to uniquely define a row in tempdb.
22. What is a Join? Explain Different Types of Joins.
Joins are used to describe the relationships among different tables. How are they related to how they are dependent on each other. Joins let us merge data without redundancy.
Types of joins:
- INNER JOIN
- OUTER JOIN
- CROSS JOIN
- OUTER JOIN
Joins help in decreasing the redundancy and maintaining consistency.
23. Explain Referential integrity
Referential integrity is a kind of integrity which maintains consistency all over the related tables. It ensures that for every foreign key value there is a primary key value. It ensures the consistency of data over related tables.
24. What are the types of functions in SQL Server?
SQL Server has two types of functions given below