Top 60+ SQL Interview Questions That You Should Know in 2021
Introduction
SQL or Structured Query Language is one of the most popular terms in the Database Management System. And with Data becoming the modern age fuel, SQL remains a central part of Data Analytics. From an organization’s perspective, SQL professionals are in demand from small, medium, or large, corporate and MNCs. The interview and selection process varies from one organization to another.
However, the SQL fundamental remains the same. Mainly, the following SQL interview questions for freshers can help them be ready for their careers ahead. We have also put together a dedicated section at the end – SQL interview questions for experienced professionals. You will also find some tricky questions on SQL server interview questions for practical learning.
Here is a list of top SQL interview questions for freshers and experienced professionals that one should know about to succeed in 2021.
- How would you define a Database?
A Database is a collection of organized information or data in a digital form. It is administered by the Database Manage System (DMBS) to control and manage the information. This makes it easy for the information to be accessible, modified, updated, or deleted. SQL is one of the most widely used database languages for managing information and querying data.
- Explain the differences between clustered and non-clustered indexes in SQL.
As two of the essential parts of the indexes in SQL, there are several differences between the clustered and non-clustered indexes.
- Numbers: In a table, there can be only a single clustered index while it can have as many as 999 non-clustered indexes.
- Order: Clustered index changes the order of the data by sorting while non-clustered indexes do not sort data.
- On the reading aspect, clustered indexes are easier to read than the non-clustered data with the same table.
- Represent: Clustered indexes show data based on key values, whereas non-clustered follow a completely separate object inside the table.
- How would you define differences between TRUNCATE and DROP commands?
The TRUNCATE command in SQL, once executed, can remove all rows from a given table. And on completion, the process cannot be rolled back.
Whereas the DROP command in SQL removes the table entirely from a given database, and the operation cannot be rolled back either.
- Explain the main differences between the Natural Join and Cross Join.
The main difference in the results of Natural Join and Cross Join. Natural Join uses all columns by the same data types and name from both the given tables, while Cross Join gets the Cross product or Cartesian product from two tables.
- What are the main differences between IN and BETWEEN condition operators?
BETWEEN operator fetches the rows from a given range of rows while IN operator plays a crucial role in checking the specific values in a given row. Here is a short code example of the two.
Example of IN Operator:
SELECT * FROM students where ROLL_NO IN (7,13,23);
Example of BETWEEN Operator:
SELECT * FROM Students where ROLL_NO BETWEEN 20 AND 58;
- Explain the main difference between the WHERE clause and the HAVING clause.
Both WHERE and HAVING clauses are used for restricting the return of rows in a given SELECT query.
WHERE clause is applied to filter rows before the grouping process is done, while the HAVING clause takes place after the grouping process takes place.
- Explain the difference in terms of SQL and PL/SQL.
- SQL stands for Structured Query Language that allows to write and execute single commands while PL/SQL comes from Oracle as ‘Procedural Language SQL’ where you can write, run, and execute full-scale operations or programs with loops, variables, functions, etc. to perform at a higher level.
- SQL represents the source of data for getting specific results, while PL/SQL acts as a platform for showing SQL operations or data.
- With SQL, you can work with a single query only, while PL/SQL can execute a large range of blocks.
- You can embed SQL Statements inside the PL/SQL. While you cannot do the same for the PL/SQL statements, as there is no support in SQL to understand any keywords or programming language.
- In SQL, what is the difference between NULL value, zero, and blank space?
A NULL value has a specific role with major differences from zero and blank space. The NULL value represents a value as unassigned, unknown, unavailable, or not applicable. While Zero represents a particular number, and a blank space is valued as a character only.
- Define the ways one can insert NULL values inside a column for inserting data.
There are two ways you deal with NULL values in a column.
- First, using implicit by removing a column from the column list.
- Second, using explicit by declaring NULL keywords in the particular VALUES clause.
- Define the SET operators in SQL.
SQL queries that include SET operations are known as Compound Queries. Union, Intersect, and Minus operators are the example of SET operators in SQL.
SQL Query Interview Questions for Experienced Professionals
- Write a query to create an empty table from a given particular existing table.
You can create a new empty table from an existing table by using the following query:
Select * into peoplecopy from people where 1=2
Here, you can copy people’s tables from an existing table using the same structure that’s not using or copying any rows.
- Explain the authentication modes in SQL Server and their change procedure.
There are two authentication modes with Windows Mode and Mixed Mode. Here are the steps to make a change in the authentication of the SQL Server:
- First of all, start the SQL Enterprise Manager. You can do so by clicking Start > Programs > Microsoft SQL Server and then further clicking the SQL Enterprise Manager to start the SQL Enterprise Manager from the server.
- Now, move further by selecting the Server from the Tools menu.
- Now, select the specific SQL Server Configuration to come at Properties and then move on to the Security Page.
- How would you write the SQL query to fetch the current date?
In SQL, we have a built-in function with a particular as GetDate() that on execution fetches the current date/timestamp, respectively.
- How can you fetch a query to display all records from SQL?
Here, you can use the SELECT command to get all records from SQL.
SELECT * FROM [Customers]
Number of total records = 4
| CustomerID | CustomerName | Address |
| 1 | Alex Noon | 457, Z Villa |
| 2 | Baskin Robbin | 123, B block |
| 3 | Curtis Rock | 12, Section A |
| 4 | Zed Mistry | 345, 1st Floor S-Area |
- Which operator is used in query for pattern making in SQL?
In SQL, LIKE Operator is the main operator for pattern making using % and _ characters.
% (Percentage – that matches zero or another character)
_ (Underscore that matches single or particular character)
Example includes:
Select * from People where peoplename like ‘a%’
Select * from People where peoplename like ‘mit_’
SQL scenario-based interview questions
- Write a command to select the unique records from a given table in SQL?
Unique records in a table can help you find the values that exist only once in a particular table.
Select DISTINCT peopleID from People
Here, you can see that DISTINCT commands help the user find unique values or IDs from the table People.
- Write a command to fetch or find alternate records from a given table in SQL.
Alternate records are both even and odd row numbers present in the table. You can use the following query or command to get the specific alternate records from the database, respectively:
- For odd numbers:
Select peopleId from (Select rowno, peopleId from people) where mod(rowno,2)=1
- For even numbers:
Select peopleId from (Select rowno, peopleId from student) where mod(rowno,2)=0
- Write different ways to get a count of records in a given table in SQL.
There are many ways to count the given number of records for a particular table in SQL. Here are the commands:
SELECT * FROM table2
SELECT COUNT(*) FROM table2
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table2) AND indid < 3
- Write a SQL query to fetch the list of employees starting with B from a given table of employees.
Most often, we have to search for specific people from a long list of employees. You can use the following query to get the right result:
SELECT * FROM Table_name WHERE EmpName like ‘B%’
- Write down the command for fetching the first 4 characters of the string.
Here are the two ways to get the desired first 4 characters of the given string:
Select SUBSTRING(PeopleName,1,4) as peoplename from people
Select LEFT(PeopleName,5) as peoplename from people
- Write a SQL query for creating a new table with the following fields or structure: empId, name, salary, department, char(4) varchar(10) float(15).
Here is a SQL query to create SQL table:
create table employee(empid char(4), name varchar(10), salary float(15), department.field varchar(15));
- Write a query with the following structure:
+—————–+————–+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+—————–+————–+——+—–+———+——-+
| DEPARTMENT_ID | decimal(5,0) | NO | PRI | 0 | |
| DEPARTMENT_NAME | varchar(25) | NO | | NULL | |
| MANAGER_ID | decimal(4,0) | NO | PRI | 0 | |
| LOCATION_ID | decimal(3,0) | YES | | NULL | |
+—————–+————–+——+—–+———+——-+
Now create a table with the following things:
– employee_id, first_name, last_name, email, phone_number hire_date, job_id, salary, commission, manager_id and department_id
– No duplicates exist in employee_id
– Department and Manager ID follow key insertion with unique combinations.
Here is a SQL query to fetch the above table:
CREATE TABLE IF NOT EXISTS employees (
EMPLOYEE_ID decimal(5,0) NOT NULL PRIMARY KEY,
FIRST_NAME varchar(15) DEFAULT NULL,
LAST_NAME varchar(20) NOT NULL,
EMAIL varchar(30) NOT NULL,
PHONE_NUMBER varchar(30) DEFAULT NULL,
HIRE_DATE date NOT NULL,
JOB_ID varchar(15) NOT NULL,
SALARY decimal(10,4) DEFAULT NULL,
COMMISSION_PCT decimal(2,2) DEFAULT NULL,
MANAGER_ID decimal(4,0) DEFAULT NULL,
DEPARTMENT_ID decimal(5,0) DEFAULT NULL,
FOREIGN KEY(DEPARTMENT_ID,MANAGER_ID)
REFERENCES departments(DEPARTMENT_ID,MANAGER_ID)
)ENGINE=InnoDB;
Conclusion
These SQL important interview questions provide in-depth learning of this language. As an individual, you must understand that both theory and practical learning go hand-in-hand. This list starts with basic SQL interview questions, then goes on to advanced SQL interview questions, covering preparation for all levels of professionals.
SQL remains one of the essential parts of modern-day Data Science, and these questions are paramount in your preparations for SQL interview questions for experienced professionals for securing top positions in MNCs.
You should read this list of SQL interview questions for testers, developers, data analyst professionals, software engineers, software architects, cloud-native professionals, and database experts of all kinds.
Jigsaw Academy is one of the top online learning platforms for modern professionals to prepare for their careers smartly. Interested applicants can apply for their Integrated Program in Business Analytics and enhance their career opportunities. This course is also available online and certified under the collaboration of the Indian Institute of Management, Indore, with Jigsaw Academy.
Also, Read
- Explain the terms DBMS, and RDBMS.
Database Management System (DBMS) presents a method for building, managing, and maintaining databases, so that information or data is saved in tables (rows and columns) inside the database. DBMS acts as an interface for giving database access to the user. Overall, DBMS allows the swift movement of data access and data retrieval easier.
Relational Database Management System (RDBMS) also uses the same tables and common fields to save data. But in addition, it provides relational operators to make changes and get desired results.
- How would you define SQL in general terms?
Structured Query Language (SQL) is an industry-standard query language for managing data inside relational databases. You can create, modify, update, and delete databases using SQL queries.
- By whom and when was the SQL launched?
Although the initial version of SQL was developed by Donald D. Chamberlin and Raymond F. Boyce at IBM based on Edgar F. Codd models. It became the ISO standards (International for Standardization) in 1986 and American National Standards Institute (ANSI) subsequently in 1987. Since then, newer SQL versions have been coming in, the most recent one in 2016.
- Explain the different uses of SQL in general.
The following are the main uses of SQL in general:
- SQL gives the ability to users to create new databases
- To create new tables
- Build new views in the databases
- Able to insert information or data in the database
- Able to update records inside the database
- Able to execute queries inside the database to fetch specific information
- Able to delete records in the database
- To perform complex queries to get desired information from databases
- What are tables and fields in SQL?
In SQL, information is procured or saved in tables and fields by rows and columns. In the database, a table has a limited number of columns but can have any number of rows. A column represents the vertical positions, and rows represent the horizontal data put in to match with the column value.
| Employee ID | FirstName | LastName |
| 1 | Martha | Stewart |
| 2 | Cherry | King |
| 3 | Lewis | Kante |
| 4 | Harris | Peter |
- What are different SQL keys?
Keys present a specific set of attributes that define the relations between a given row and table. Thus, it allows users to build a relation between two tables. Keys provide a way to combine values from one table to another and get more desired results. Generally, there are eight types of keys present in DBMS based on different attributes. These are Super Key, Primary Key, Candidate Key, Alternate Key, Foreign Key, Composite Key, Compound Key, and Surrogate Key.
- What is a Primary Key, Unique Key, and Foreign Key in SQL?
In SQL, a Primary Key defines a collection or combination of fields that come from unique rows. Primary Keys present a special case scenario for a Unique Key with a NOT NULL constraint, i.e., values inside the Primary Keys can’t be NULL.
- Explain the different subsets of SQL.
There are three different subsets present in SQL; they are defined as:
- Data Definition Language (DDL): DDL gives you access to perform the basic operations for a database that includes CREATE, ALTER, and DELETE information.
- Data Manipulation Language (DML): DML gives you access to perform data manipulation inside the database that includes inserting, updating, deleting, and retrieving data, respectively.
- Data Control Language (DCL): DCL gives you access to control and manage data precisely, which includes Grant and Revoke user permissions.
- What are the main differences between SQL and MySQL?
| SQL | MySQL |
| SQL stands for Structured Query Language. | MySQL – ‘My’ represents the name of the founder and his daughter, while SQL stands for ‘Structured Query Language’ only. |
| SQL is the industry-standard language for working with relational databases. | MySQL is a Relational Database Management System (RDBMS). |
| SQL’s main function lies in managing and accessing databases. | MySQL uses SQL to manage databases in their RDBMS. An example includes Microsoft SQL, Informix, etc. |
SQL Joins Interview Questions
- What is a Join in SQL?
SQL Join provides a way to connect information from two or more tables. So, users can merge or retrieve data from multiple table fields to get preferred results. In Join, the use of keys plays a crucial role in fetching precise information from the database.
- Explain the different types of Join in SQL.
There are four types of SQL Join in general.
- Inner Join: In this type of SQL Join, you fetch values from two tables with at least one matching row. SQL Inner Join is among the most common types.
- Full Join: In this type of SQL Join, you can fetch all values from both tables. So, all values from the tables are shown in the result.
- Left Join: In this type of SQL Join, the left side of the table gets preference with matching rows from the right side of the table, as the conditions of Joins are satisfied. So, values from the left tables are shown with matching row values from the right.
- Right Join: In this type of SQL Join, the right table gets the preference with matching rows from the left side that matches the Join conditions. So, all values from the right table are shown with matching rows from the table’s left side.
- How does Join work in DBMS/SQL Server?
Joins offer a way to get information from two tables and bring collective results for further processing. There are different types and combinations of Joins that you can use to get any desired result.
- Which Join is the fastest in SQL?
In SQL, there is no specific faster Join. But in some cases, one Join may perform and fetch results quicker than another Join. And Joins will always fetch results better than subquery in general.
- What is Self join, and when is it used in SQL?
Self Join is a unique scenario and offers a highly useful clause that allows a table to compare with itself. So, you can compare one value of a column to the second column within a table. Self Joins are used to change a hierarchical structure into a flat structure.
- What are Views in SQL?
In SQL, Views represent a virtual table showing subsets of data present in the table. As Views don’t take a particular position, they take even less space to store. A View can have values or data from one or two tables based on a specific relationship. Views represent a searchable object and are easily findable by using a query to search.
- What is Index in SQL?
An Index is a performance-enhancing method that allows quick fetching of information from the database or a table. The Index has a unique value that cannot be duplicated. So, these Index values are easier to fetch.
- Explain the different types of Indexes in SQL.
There are three types of Indexes in general.
- Unique Index: For building a Unique Index, you can’t have duplicate values in the column. And if a Primary Key is present and defined, then a Unique Index can be automatically applied simultaneously.
- Clustered Index: Clustered Index defines the physical order of the table in the database. Every table can only have one Clustered Index and is created automatically on generating a Primary Key.
- Non-Clustered Index: Non-Clustered Index does not make any change in the physical order of the table in a database. The main reason for creating Non-Clustered Indexes is to search the data. Non-Clustered Indexes are created for multiple Join conditions, including filters for queries. In a particular table in a database, there can be as many as 999 Non-Clustered Indexes.
- What is a Query in SQL?
A Query represents a set of instructions written in a way to get specific results. You can use single or multiple queries to get precise information from the database in a table.
- What are Relationships and Entities in SQL?
Entities represent a physical person, object, or a particular thing that can be seamlessly stored in an electronic database. For instance, a database with a list of customers and their specific information for age, salary, duration, etc.
Relationships/Relations define the links between entities that are connected. For instance, a customer name has a unique identity as customer ID, contact information, etc.
- Define a SubQuery in SQL.
A SubQuery represents a query defined inside the main query. The outer layer is termed as the main query, while the inner query is termed as SubQuery. In SQL, SubQuery is executed first, and the result is then carried forward to the original query.
- Explain the different types of SubQueries in SQL.
There are two general types of SubQueries defined in SQL:
- Correlated SubQuery is the query that is present in the specified table from the outer query. These are not independent queries and depend on the information or data from the outer query, respectively.
- Non-Correlated SubQuery can be termed as an independent query. As the information is executed, their result is then simultaneously added to the main query.
- What is a trigger in SQL?
A database trigger is a specific program or code that executes an automatic response to a particular event in a view or a table. These triggers play an essential role in maintaining database integrity. When a new customer record is added to a given database, simultaneously related columns, such as the ID, name, phone number, notes, etc., are created.
- What is a Constraint in SQL? Explain the different levels of Constraint in SQL.
Constraints in SQL define the specific rules and regulations for limiting the data type on a column inside the table. Constraints help you add relevant data to the table and stop users from adding irrelevant information to the database. There are two broad levels of Constraints in SQL that are represented as:
- Column Level Constraint
- Table Level Constraint
Some of these Constraints are UNIQUE, Primary Key, Foreign Key, Default, Not NULL, CHECK, etc.
- Define Data Integrity in SQL.
Data Integrity presents a method or way to keep the data accurate and consistent inside the database. You must define the integrity constraints to get business data accurate while submitting data or information in the database.
- Explain local and global variables with their differences in SQL.
Local variables are the ones that exist or are used within the functions only. These local variables don’t work or can’t be called in other functions, as they are not known. However, variables work when that specific function is called upon.
Global variables are the ones that exist or can be used across the program. A global variable, once created, can never be used in any function.
- Define Auto Increment in SQL.
Auto Increment field defines a unique number automatically generated whenever a new record is inserted in the database or the table. Most often, this represents a Primary Key field that is created when each new record is inserted in the table.
- What is a Data Warehouse in SQL?
Data Warehouse is one large set of information or data and represents a central repository that collects data from all sources of information of any kind. In Data Warehouse, data is consolidated, manipulated, transformed, and processed further for processing and mining. Here, you may find data subsets termed as Data Marts.
- Explain user-defined functions and their types.
User-defined functions represent the functions that allow specific logic to be used whenever and wherever required. So, you don’t need to write that same logic again and again.
There are three types of user-defined functions in SQL.
- Scalar Functions
- Inline Table Value Functions
- Multi Statement Value Functions
- What is a Collation in SQL?
A Collation in SQL represents the specific set of rules and regulations for sorting and comparing character data. For instance, character width, case sensitivity, etc.
The four types of Collation sensitivity are:
- Case Sensitivity as A and a and Z and z
- Accent Sensitivity as English, French
- Width Sensitivity Single or Double Byte character
- Kana Sensitivity as Japanese Kana character
- Define stored procedure in SQL.
Stored procedures are the collection of functions containing multiple SQL statements for accessing data in a specific form. SQL has several stored procedures that you can write once and store to run them whenever required repeatedly.
- Explain the main advantages and disadvantages of the stored procedure in SQL.
Stored procedure advantage lies in its modular nature of programming, i.e., you can create these once, save, and call them as per your preference. This results in quicker execution, lowering network traffic, and keeping a check on the security aspects.
One of the main disadvantages of stored procedures is they run or execute in the database by consuming a lot of memory in the database server.
- Define CLAUSE in SQL.
SQL CLAUSE provides a way to limit the query by adding conditions and getting more specific results. Thus, acting as a filter to the whole result. For instance, WHERE, HAVING, etc.
- What are Normalization and Denormalization in SQL?
Normalization is a process of lowering dependencies and redundancies by organizing tables and fields in a database. Here, the Normalization is done with the main focus to add, modify, or delete fields to collect them into one table.
Denormalization is a process for accessing data from higher to lower forms in a database. Denormalization introduces redundancy in the table by adding values from the related tables in the database.
- Explain types of Normalization in SQL.
Normalization comes in multiple types, termed normal forms, with each having a dependency on the other.
- First Normal Form (1NF): Starts with the creation of tables for the data and then columns unique identification. Thus, removing all the duplicate columns from the database.
- Second Normal Form (2NF): Starts with matching all requirements of the first normal form and then creating columns depending on the Primary Key.
- Third Normal Form (3NF): Matching with all the 2NF requirements and creating new columns for relationships using Primary Keys only.
- Fourth Normal Form (4NF): Matching all the 3NF requirements and avoiding all the multivalued dependencies.
- What is a cursor in SQL?
A cursor provides a way to control the return on rows in SQL statements, such as updating records in a particular row in singleton. A cursor is quite useful for traversing functions, for instance, addition, removal, and retrieval of information or values from the database.
- Define the ACID property in a given database in SQL.
ACID stands for Atomicity, Consistency, Isolation, and Durability. Every database has ACID properties to make it more reliable. Here each logical operation within data is termed a transaction.
- Atomicity: In this stage, every transaction acts as all or nothing. So, even if a single part of the transaction fails, then the whole transaction fails with the database’s status unchanged.
- Consistency: Makes sure the transaction is done as per all the validation rules.
- Isolation: Defines the concurrency protocol, making sure the property execution is not met on all conditions.
- Durability: Once a transaction is committed, the state will remain the same even if any crashes, errors, or power interruptions occur.
- Explain Operators of Union, Minus, and Intersect Commands in SQL.
- Union: Union Operator provides a way to collect or combine values of the two tables and then remove duplicates from the respective table.
- Minus: Minus Operator prefers the first query while taking matching records from the first and second query, respectively. So, in the result, we get matching records from the first and second queries along with the rest of the rows present in the first query.
- Intersect: Intersect Operator, when used, displays the overall rows from both the queries.
- Define Online Transaction Processing (OLTP) in general.
Online Transaction Processing (OLTP) is responsible for all transactions being carried out with respect to entering data, retrieving data, and then processing data. Overall, OLTP keeps the data procedure efficient and easy to manage. For instance, financial transactions in a banking institution.
- Define ALIAS command in SQL.
ALIAS command is used to give a secondary name to identify any column or the table. The below code refers to Ex as Alias name for exam table and st as student table, respectively.
| Select st.StudentID, Ex.Result from student st, Exam as Ex where st.studentID = Ex. StudentID |
Complex SQL Interview Questions
- What are the main differences between TRUNCATE and DELETE statements in SQL?
| S No. | TRUNCATE | DELETE |
| 1 | TRUNCATE is part of the DDL command subset. | DELETE is part of the DML command subset. |
| 2 | In TRUNCATE, the main role is to remove or delete all the rows from a particular table. | In DELETE, the main role is to delete a particular or single row in a given table. |
| 3 | You cannot use rollback once you perform the TRUNCATE command in SQL. | You can roll back after executing the DELETE command in SQL. |
| 4 | You cannot use the WHERE clause with the TRUNCATE command. | You are allowed to use the WHERE clause with the DELETE combination. |
| 5 | TRUNCATE statements work significantly higher than the DELETE command. | DELETE command works at a slower speed when compared with TRUNCATE statements in SQL. |
- Explain the differences between clustered and non-clustered indexes in SQL.
As two of the essential parts of the indexes in SQL, there are several differences between the clustered and non-clustered indexes.
- Numbers: In a table, there can be only a single clustered index while it can have as many as 999 non-clustered indexes.
- Order: Clustered index changes the order of the data by sorting while non-clustered indexes do not sort data.
- On the reading aspect, clustered indexes are easier to read than the non-clustered data with the same table.
- Represent: Clustered indexes show data based on key values, whereas non-clustered follow a completely separate object inside the table.
- How would you define differences between TRUNCATE and DROP commands?
The TRUNCATE command in SQL, once executed, can remove all rows from a given table. And on completion, the process cannot be rolled back.
Whereas the DROP command in SQL removes the table entirely from a given database, and the operation cannot be rolled back either.
- Explain the main differences between the Natural Join and Cross Join.
The main difference in the results of Natural Join and Cross Join. Natural Join uses all columns by the same data types and name from both the given tables, while Cross Join gets the Cross product or Cartesian product from two tables.
- What are the main differences between IN and BETWEEN condition operators?
BETWEEN operator fetches the rows from a given range of rows while IN operator plays a crucial role in checking the specific values in a given row. Here is a short code example of the two.
Example of IN Operator:
SELECT * FROM students where ROLL_NO IN (7,13,23);
Example of BETWEEN Operator:
SELECT * FROM Students where ROLL_NO BETWEEN 20 AND 58;
- Explain the main difference between the WHERE clause and the HAVING clause.
Both WHERE and HAVING clauses are used for restricting the return of rows in a given SELECT query.
WHERE clause is applied to filter rows before the grouping process is done, while the HAVING clause takes place after the grouping process takes place.
- Explain the difference in terms of SQL and PL/SQL.
- SQL stands for Structured Query Language that allows to write and execute single commands while PL/SQL comes from Oracle as ‘Procedural Language SQL’ where you can write, run, and execute full-scale operations or programs with loops, variables, functions, etc. to perform at a higher level.
- SQL represents the source of data for getting specific results, while PL/SQL acts as a platform for showing SQL operations or data.
- With SQL, you can work with a single query only, while PL/SQL can execute a large range of blocks.
- You can embed SQL Statements inside the PL/SQL. While you cannot do the same for the PL/SQL statements, as there is no support in SQL to understand any keywords or programming language.
- In SQL, what is the difference between NULL value, zero, and blank space?
A NULL value has a specific role with major differences from zero and blank space. The NULL value represents a value as unassigned, unknown, unavailable, or not applicable. While Zero represents a particular number, and a blank space is valued as a character only.
- Define the ways one can insert NULL values inside a column for inserting data.
There are two ways you deal with NULL values in a column.
- First, using implicit by removing a column from the column list.
- Second, using explicit by declaring NULL keywords in the particular VALUES clause.
- Define the SET operators in SQL.
SQL queries that include SET operations are known as Compound Queries. Union, Intersect, and Minus operators are the example of SET operators in SQL.
SQL Query Interview Questions for Experienced Professionals
- Write a query to create an empty table from a given particular existing table.
You can create a new empty table from an existing table by using the following query:
Select * into peoplecopy from people where 1=2
Here, you can copy people’s tables from an existing table using the same structure that’s not using or copying any rows.
- Explain the authentication modes in SQL Server and their change procedure.
There are two authentication modes with Windows Mode and Mixed Mode. Here are the steps to make a change in the authentication of the SQL Server:
- First of all, start the SQL Enterprise Manager. You can do so by clicking Start > Programs > Microsoft SQL Server and then further clicking the SQL Enterprise Manager to start the SQL Enterprise Manager from the server.
- Now, move further by selecting the Server from the Tools menu.
- Now, select the specific SQL Server Configuration to come at Properties and then move on to the Security Page.
- How would you write the SQL query to fetch the current date?
In SQL, we have a built-in function with a particular as GetDate() that on execution fetches the current date/timestamp, respectively.
- How can you fetch a query to display all records from SQL?
Here, you can use the SELECT command to get all records from SQL.
SELECT * FROM [Customers]
Number of total records = 4
| CustomerID | CustomerName | Address |
| 1 | Alex Noon | 457, Z Villa |
| 2 | Baskin Robbin | 123, B block |
| 3 | Curtis Rock | 12, Section A |
| 4 | Zed Mistry | 345, 1st Floor S-Area |
- Which operator is used in query for pattern making in SQL?
In SQL, LIKE Operator is the main operator for pattern making using % and _ characters.
% (Percentage – that matches zero or another character)
_ (Underscore that matches single or particular character)
Example includes:
Select * from People where peoplename like ‘a%’
Select * from People where peoplename like ‘mit_’
SQL scenario-based interview questions
- Write a command to select the unique records from a given table in SQL?
Unique records in a table can help you find the values that exist only once in a particular table.
Select DISTINCT peopleID from People
Here, you can see that DISTINCT commands help the user find unique values or IDs from the table People.
- Write a command to fetch or find alternate records from a given table in SQL.
Alternate records are both even and odd row numbers present in the table. You can use the following query or command to get the specific alternate records from the database, respectively:
- For odd numbers:
Select peopleId from (Select rowno, peopleId from people) where mod(rowno,2)=1
- For even numbers:
Select peopleId from (Select rowno, peopleId from student) where mod(rowno,2)=0
- Write different ways to get a count of records in a given table in SQL.
There are many ways to count the given number of records for a particular table in SQL. Here are the commands:
SELECT * FROM table2
SELECT COUNT(*) FROM table2
SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table2) AND indid < 3
- Write a SQL query to fetch the list of employees starting with B from a given table of employees.
Most often, we have to search for specific people from a long list of employees. You can use the following query to get the right result:
SELECT * FROM Table_name WHERE EmpName like ‘B%’
- Write down the command for fetching the first 4 characters of the string.
Here are the two ways to get the desired first 4 characters of the given string:
Select SUBSTRING(PeopleName,1,4) as peoplename from people
Select LEFT(PeopleName,5) as peoplename from people
- Write a SQL query for creating a new table with the following fields or structure: empId, name, salary, department, char(4) varchar(10) float(15).
Here is a SQL query to create SQL table:
create table employee(empid char(4), name varchar(10), salary float(15), department.field varchar(15));
- Write a query with the following structure:
+—————–+————–+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+—————–+————–+——+—–+———+——-+
| DEPARTMENT_ID | decimal(5,0) | NO | PRI | 0 | |
| DEPARTMENT_NAME | varchar(25) | NO | | NULL | |
| MANAGER_ID | decimal(4,0) | NO | PRI | 0 | |
| LOCATION_ID | decimal(3,0) | YES | | NULL | |
+—————–+————–+——+—–+———+——-+
Now create a table with the following things:
– employee_id, first_name, last_name, email, phone_number hire_date, job_id, salary, commission, manager_id and department_id
– No duplicates exist in employee_id
– Department and Manager ID follow key insertion with unique combinations.
Here is a SQL query to fetch the above table:
CREATE TABLE IF NOT EXISTS employees (
EMPLOYEE_ID decimal(5,0) NOT NULL PRIMARY KEY,
FIRST_NAME varchar(15) DEFAULT NULL,
LAST_NAME varchar(20) NOT NULL,
EMAIL varchar(30) NOT NULL,
PHONE_NUMBER varchar(30) DEFAULT NULL,
HIRE_DATE date NOT NULL,
JOB_ID varchar(15) NOT NULL,
SALARY decimal(10,4) DEFAULT NULL,
COMMISSION_PCT decimal(2,2) DEFAULT NULL,
MANAGER_ID decimal(4,0) DEFAULT NULL,
DEPARTMENT_ID decimal(5,0) DEFAULT NULL,
FOREIGN KEY(DEPARTMENT_ID,MANAGER_ID)
REFERENCES departments(DEPARTMENT_ID,MANAGER_ID)
)ENGINE=InnoDB;
Conclusion
These SQL important interview questions provide in-depth learning of this language. As an individual, you must understand that both theory and practical learning go hand-in-hand. This list starts with basic SQL interview questions, then goes on to advanced SQL interview questions, covering preparation for all levels of professionals.
SQL remains one of the essential parts of modern-day Data Science, and these questions are paramount in your preparations for SQL interview questions for experienced professionals for securing top positions in MNCs.
You should read this list of SQL interview questions for testers, developers, data analyst professionals, software engineers, software architects, cloud-native professionals, and database experts of all kinds.
Jigsaw Academy is one of the top online learning platforms for modern professionals to prepare for their careers smartly. Interested applicants can apply for their Integrated Program in Business Analytics and enhance their career opportunities. This course is also available online and certified under the collaboration of the Indian Institute of Management, Indore, with Jigsaw Academy.
Also, Read
basic sql interview questions
sql interview questions
sql interview questions and answers
sql interview questions for experienced
sql interview questions for experienced professionals
sql interview questions for freshers
sql joins interview questions
sql query interview questions
sql server interview questions
