RDBMS is a popular tool for data, and therefore SQL proficiency is required in almost all positions. This SQL Interview Questions tutorial will guide you through the most popular SQL interview questions (Structured Query Language). This tutorial is an excellent resource for learning everything there is to know about SQL, Oracle, MS SQL Server, and MySQL databases. Our tutorial on SQL Interview Questions is a one-stop for improving your interview preparation.
Q. What’s the difference between SQL and MySQL?
SQL: SQL (Structured Query Language) is a standard language based on the English language. The relational database’s core language, SQL, is used to access and manage the database.
MySQL: MySQL is a relational database management system (RDMS), similar to SQL Server, Informix, and others.
Q. What are the various SQL subsets?
• Data Definition Language (DDL): It helps you to do things like CREATE, ALTER, and DELETE objects on the database.
• Data Manipulation Language (DML): This language helps you to control and view data. It helps in the inserting, updating, deleting, and retrieval of data from a database.
• Data Control Language (DCL): This language helps you to manage database access. For example, Grant and revoke access permissions.
Q. What do you mean by database management system (DBMS)? What are the various types of it?
A Database Management System (DBMS) collects and analyzes data by communicating with the user, programs, and database. A database is a collection of data that is organized.
A database management system (DBMS) helps a user to communicate with the database. Data in the database can be updated, retrieved, and removed, and it can be of any kind, including strings, numbers, and images.
There are two types of database management systems:
• Relational Database Management System (RDBMS): Data is organized into relations (tables). MySQL is a good example.
• Database Management System (DBMS): In this, there is no concept of relations.
Q. In SQL, what do you mean by table and field?
A table is a logically ordered array of data in the form of rows and columns. The number of columns in a table is referred to as a field. Consider the following scenario:
Table: Emp
Field: Id, Name, Salary
Q. What are SQL joins?
A JOIN clause is used to join rows from two or more tables together based on a common column. It’s used to join two tables together or get data from them. As shown below, there are four different forms of joins:
• Inner join: The most common form of SQL join is the inner join. It’s used to get all the rows from different tables that satisfy the join condition.
• Left Join: In SQL, a left join returns all rows from the left table but only matching rows from the right table where the join condition is met.
• Right Join: In SQL, a right join returns all rows from the right table but only matching rows from the left table where the join condition is met.
• Full Join: When there is a match in any of the tables, a full join returns all of the data. As a result, all rows from the left-hand side table and all rows from the right-hand side table are returned.
Q. What is the difference between the SQL datatypes CHAR and VARCHAR2?
Character data is stored in Char and Varchar2, but varchar2 is used for variable-length character strings, and Char is used for fixed-length strings.
E.g., Char (10) can only store 10 characters and cannot store any other length string, while varchar2(10) can store any length string, i.e., 6,8,2 in this variable.
Q. What is the concept of a primary key?
• In SQL, a primary key is a column (or group of columns) or a set of columns that uniquely identifies each row in a table.
• Defines a single table row in a particular way.
• Null values are not permitted.
The primary key in the Student table, for example, is S.ID.
Q. What do you mean by constraints?
In SQL, constraints are used to define the table’s data type limit. It can be defined when the table statement is created or changed. The following are some examples of constraints: NOT NULL, CHECK, DEFAULT, UNIQUE, PRIMARY KEY, FOREIGN KEY.
Q. How are DELETE and TRUNCATE statements different?
DELETE: To delete a row in a table, use the Delete command. After using the delete expression, you can rollback info. It’s a command in the DML language. It is more time-consuming than using the truncate method.
TRUNCATE: The word “truncate” refers to the method of deleting all rows from a table. Data is not reversible. A DDL command is what it is. It’s more convenient.
Q. What is a unique key, exactly?
• Defines a single table row in a particular way.
• Each table may have several values.
• Null values are permitted.
Q. In SQL, what is a foreign key?
• By imposing a relation between the data in two tables, a foreign key preserves referential integrity.
• The child table’s foreign key refers to the parent table’s primary key.
• The foreign key restriction avoids behavior that would cause the child and parent tables to lose their connections.
Q. What exactly do you mean when you say “data integrity”?
Data integrity refers to the quality and accuracy of data contained in a database. Integrity constraints are often established to impose business rules on data entered into an application or database.
Q. In SQL, what’s the difference between clustered and non-clustered indexes?
The following are the distinctions between clustered and non-clustered indexes in SQL:
• The clustered index is used for quick data retrieval from the database, and it is quicker than reading from the non-clustered index.
• A clustered index changes the way records are stored in a database since it sorts rows by the clustered index column.
A non-clustered index, on the other hand, does not modify the way data is stored; instead, it produces a different object inside a table that, after searching, points back to the original table rows.
• A table may only have one clustered index but several non-clustered indexes.
Q. How can you write a SQL query to display the current date?
GetDate()
is a built-in function in SQL that lets you get the current timestamp/date.
Q.What exactly do you mean by query optimization?
Query optimization is the process in which a strategy for evaluating a query with the lowest expected cost is defined.
The following are some of the benefits of query optimization:
• The output is delivered faster; a greater number of queries can be processed in less time, and the time and space complexity is reduced.
Q. What exactly do you mean when you say “denormalization”?
Denormalization is a technique for accessing data from a database’s higher to lower forms. It helps database administrators in enhancing the overall efficiency of their infrastructure by incorporating redundancy into a table. It integrates database queries that merge data from multiple tables into a single table to add redundant data to a table.
Q. What is the difference between entities and relationships?
Entities are real-world people, places, and things for which data can be stored in a database. Tables are used to store information about a single class of objects. A customer table, for example, is used to store customer information in a bank database. Each customer’s information is stored in the customer table as a set of attributes.
Relationships are relations or connections between people that have something in common. The customer name, for example, is linked to the customer account number and contact information, which may be stored in the same table. There may also be links between various tables (for example, customer to accounts).
What is an index, exactly?
An index is a performance tuning method for retrieving records from a table more quickly. Since an index generates an entry for each value, retrieving data is quicker.
Q. Describe the various forms of indexes in SQL.
In SQL, there are three types of indexes:
Unique indexed: this index prevents the field from having duplicate values. A unique index can be implemented automatically if a primary key is specified.
Clustered Index: This index reorganizes the physical order of the table and looks for key values. There can only be one clustered index per table.
Non-Clustered Index: A non-clustered index does not change the table’s physical order and keeps the data in a logical order. There can be a lot of non-clustered indexes in a table.
Q. What is normalization, and what are the benefits of doing so?
The process of arranging data in SQL to prevent repetition and redundancy is known as normalization.
The following are some of the benefits:
• Efficient data access.
• More accessibility for Queries.
• Easily locate the information.
• Easier to enforce Protection.
• Allows fast adjustment.
• Avoids redundant and duplicate data.
• More Compact Database.
• Maintain Reliable data after modification.
Q. What is the difference between the commands DROP and TRUNCATE?
The DROP command removes a table from the database, which cannot be undone, while the TRUNCATE command removes all of the rows from the table.
Q. Define the various forms of normalization.
There are several levels of normalization. These are referred to as normal forms. Each subsequent normal form is based on the one before it. In most cases, the first three normal forms are sufficient.
• 1NF (First Normal Form): There are no repeated classes inside rows.
• Second Normal Form (2NF): The primary key as a whole determines the value of a non-key (supporting) column.
• Third Normal Form (3NF): Depends on the primary key, with no non-key (supporting) column values.
Q. In a database, what is the ACID property?
ACID are acronyms for Atomicity, Consistency, Isolation, and Durability. It is used to ensure that data transactions in a database system are processed reliably.
• Atomicity: Atomicity refers to completed or incomplete transactions, where a transaction refers to a single logical data process. It means that if one component of a transaction fails, the whole transaction fails as well, leaving the database state unchanged.
• Consistency: Consistency guarantees that all validation rules are followed. In simple terms, the transaction never leaves the database until it has completed its state.
• Isolation: The primary purpose of isolation is to monitor concurrency.
• Durability: Once a transaction is committed, it will occur regardless of what occurs in the meantime, such as a power outage, a fire, or some other form of mistake.
Q. In SQL, what do you mean by “Trigger”?
Triggers are a type of stored procedure in SQL that is specified to be executed automatically or after data changes. When an insert, update, or other test is run against a particular table, it allows you to run a batch of code.
Q. What are the various forms of SQL operators?
In SQL, there are three operators available: Arithmetic Operators, Logical Operators, and Comparison Operators.
Q. Are NULL values the same as that of zero or a blank space?
A NULL value is not to be confused with a value of zero or a blank space. A NULL value denotes an inaccessible, undefined, reserved, or not applicable value, while a zero denotes a number and a blank space denotes a character.
Q. What’s the difference between a natural join and a cross join?
The natural join is based on all columns in both tables having the same name and data types, while the cross join creates the cross product or Cartesian product of two tables.
Q. In SQL, what is a subquery?
A subquery is a query specified within another query to retrieve data or information from the database. The outer query in a subquery is referred to as the main query, while the inner query is referred to as the subquery. Subqueries are often run first, and the subquery’s answer is then passed on to the main query. It can be nested within any question, like Pick, UPDATE, and OTHER. Any contrast operators, such as >, or =, can be used in a subquery.
Q. What are the various forms of subqueries?
Correlated and Non-Correlated subqueries are the two kinds of the subquery.
Correlated subqueries: These are queries that pick data from a table that the outer query refers to. Since it applies to another table and a table column, it is not considered an independent query.
Non-Correlated subquery: This query is a stand-alone query in which the response of a subquery is used to replace the results of the main query.
Q. What is the purpose of SQL group functions?
Group functions operate on a collection of rows and return a single result for each group. AVG, COUNT, MAX, MIN, SUM, and VARIANCE are some of the most widely used group functions.
Q. What is a relationship, and what are the forms of relationships?
The term “relationship” or “link” refers to a connection between two or more individuals. The connection between the tables in a database is known as a relationship. There are a variety of relations, including:
• One-on-one interaction.
• Relationship between one and many.
• One-to-Many Relationship.
• Relationship that is self-referential.
Q. When inserting data, how do you insert NULL values in a column?
In SQL, NULL values can be inserted using the following methods:
• Inferentially, by omitting a column from the list of columns.
• Use the NULL keyword in the VALUES clause directly.
Q. What are the benefits of using SQL functions?
The following are some examples of SQL functions:
• Calculate data
• Alter individual data objects
• Manipulate result
• Format dates and numbers
• Convert data types
Q. Why is it important to use a MERGE statement?
This statement allows you to update or insert data into a table on a conditional basis. If a row already exists, it performs an UPDATE; otherwise, it performs an INSERT.
Q. Can you explain what a recursive stored procedure is?
A recursive stored procedure calls itself before it hits a certain boundary state. This recursive function or process allows programmers to reuse the same code set n times.
Q. What does SQL’s CLAUSE stand for?
By providing a condition to the query, the SQL clause helps to restrict the result collection. A clause helps in the filtering of rows from a large number of documents.
Q. What’s the difference between a ‘HAVING’ and a ‘WHERE’ CLAUSE?
Only the SELECT statement can use the HAVING Clause. It’s most often used in a GROUP BY Clause, but getting functions like a WHERE Clause is when it’s not. WHERE Clause is added to each row until they are part of the GROUP BY function while Having Clause is only used for the GROUP BY function in a query.
Q. What are the different ways that Dynamic SQL can be used?
The following are some examples of how dynamic SQL can be used:
• Create a parameterized query.
• Using the EXEC command.
• Using the sp execute SQL feature.
Q. What are the different forms of constraints?
Constraints represent a column and are used to implement data entity and consistency. A constraint can be divided into two levels:
• constraint at the column level
• constraint at the table level
Q. What is the best way to get common records from two tables?
INTERSECT can be used to retrieve common records from two tables. Consider the following scenario:
1. Select empid from emp INTERSECT Select empid from the employee.
Q. What are some SQL manipulation functions?
In SQL, there are three manipulation functions:
• LOWER: This function returns a lowercase string. It takes a string as an argument and converts it to a lower case before returning it. The syntax is as follows: LOWER('string')
• UPPER: This function returns an uppercase string. It takes a string as an argument and converts it to uppercase before returning it.
The syntax is as follows: UPPER('string')
• INITCAP: This function returns a string with the first letter in capital letters and the rest in lowercase letters.
The syntax is as follows: INITCAP('string')
Q. What are the various types of set operators in SQL?
Union, Intersect, and Minus operators are some of the available set operators.
Q What is an ALIAS command, exactly?
In SQL, the ALIAS command is used to assign a name to a table or a column. This alias name may be used to describe a specific table or column in the WHERE clause.
Q. What is the difference between aggregate and scalar functions?
Aggregate functions return a single value after evaluating a mathematical equation. These measurements are made using data from a table’s columns. E.g., max()
and count()
are calculated with numeric in mind.
Based on the input value, scalar functions return a single value. UCASE()
and NOW()
, for example, are calculated with string in mind.
Q. What is the primary distinction between SQL and PL/SQL?
PL/SQL is Oracle’s “Procedural Language” SQL, which allows you to write a complete program (loops, variables, etc.) to accomplish various operations such as selects/inserts/updates/deletes.
Q. What is a view?
A view is a virtual table that represents a subset of the data in a table. It takes up less space to store since there are no views. The data from one or more tables may be combined in a view, depending on the relationship.
Q. What is the purpose of Views?
A logical snapshot based on a table or another perspective is referred to as a view. It’s used for a variety of reasons:
• Limiting data access.
• Simplifying complex questions.
• Data independence is ensured.
• Providing multiple perspectives on the same results.
Q. What is a Stored Procedure, exactly?
A Stored Procedure is a database access feature that consists of several SQL statements. Several SQL statements are consolidated into a stored procedure, which can be executed whenever and wherever required, saving time and avoiding rewriting code.
Q. What are some of the benefits and drawbacks of using a stored procedure?
Advantages include:
A Stored Procedure can be used as modular programming, which means that it can be created once, stored, and called multiple times as needed.
It allows for quicker execution. It also decreases network traffic while also enhancing data protection.
Negative aspects:
The only drawback to a Stored Procedure is that it can only be performed in the database and consumes more memory on the database server.
Q. What are all the different kinds of user-defined functions?
User-defined functions are divided into three categories:
• Table-valued functions
• Scalar functions
• Valued functions with numerous statements
A scalar returns the unit, and the return clause is described by variant. Tables are returned by the other two types of specified functions.
Q. What exactly do you mean when you say “collation”?
A collection of rules that specify how data can be sorted and compared is referred to as collation. Character data is sorted according to rules that describe the proper character series and options for determining case sensitivity, character width, and other parameters.
Q What are the various kinds of Collation Sensitivity?
The various forms of collation sensitivity are as follows:
• Case Sensitivity: A and a, B and b, C and c, D and d, E and e, F and e, G and e
• Kana Sensitivity: Kana characters from Japan.
• Width Sensitivity: single-byte and double-byte characters.
• Sensitivity of accents.
Q. What are the differences between local and global variables?
Variables at the local level: These variables may be used outside of the function or exist within it. Any other feature does not use or apply to these variables.
Variables at a global level: These are the variables that can be accessed at any point during the program. When that function is named, no global variables can be generated.
Q. What does SQL Auto Increment mean?
When a new record is inserted into the table, the auto increment keyword enables the user to produce a unique number.
When using SQL’s PRIMARY KEY
, this keyword is typically needed.
In Oracle, the AUTOINCREMENT
keyword can be used, and in SQL SERVER
, the IDENTITY
keyword can be used.
Q What is a Data warehouse, exactly?
A data warehouse is a central repository of data that has been collected from various sources of data. These data are then consolidated, converted, and made available for online processing and mining. Data Marts are a subset of data found in warehouses.
Q. What are the various SQL Server authentication modes? What can be done about it?
To adjust the authentication mode in SQL Server, follow the steps below:
• To run SQL Enterprise Manager from the Microsoft SQL Server program group, go to Start > Programs > Microsoft SQL Server
and select SQL Enterprise Manager.
• Then, from the Tools menu, choose the server.
• Select the Security page from the SQL Server Configuration Properties menu.