SQL Interview Questions and Answers

CTA

Top 10 Most Frequently Asked SQL Interview Questions

1. What is normalization and what are its types?
2. Explain the types of SQL joins.
3. What is a primary key?
4. Explain the different types of SQL commands.
5. What is the ACID property in a database?
6. What is a “TRIGGER” in SQL?
7. What is the COALESCE function?
8. What are the types of views in SQL?
9. What are views? Give an example.
10. What is the difference between Union and Union All operators?

Watch this video on SQL Interview Questions and Answers:

Basic SQL Interview Questions for Freshers

1. What is a primary key?

Primary keys are unique values used to identify records. Think of this as a unique identification card such as an Aadhar Card, or PAN Card of the data. It cannot have null values and must have a unique value just like your PAN Card number, or Aadhar Number. Only one primary key is allowed in one table (one Aadhar, or PAN Card per person). It can have one or more fields making the primary key a composite key. Now, we will write a query to demonstrate the use of a primary key for the employee table:

 // CREATE TABLE Employee ( ID int NOT NULL, Employee_name varchar(255) NOT NULL, Employee_designation varchar(255), Employee_Age int, PRIMARY KEY (ID) );

2. Explain the different types of SQL commands.

Types of SQL Languages

• DDL: DDL is that part of SQL that defines the data structure of the database in the initial stage when the database is about to be created. It is mainly used to create and restructure database objects. Commands in DDL are:
  • Create table
  • Alter table
  • Drop table
• DML: DML is used to manipulate already existing data in a database, i.e., it helps users to retrieve and manipulate data. It is used to perform operations such as inserting data into the database through the insert command, updating data with the update command, and deleting data from the database through the delete command.
• DCL: DCL is used to control access to the data in the database. DCL commands are normally used to create objects related to user access and to control the distribution of privileges among users. The commands that are used in DCL are Grant and Revoke.
• TCL: TCL is used to control the changes made by DML commands. It also authorizes the statements to assemble in conjunction with logical transactions. The commands that are used in TCL are Commit, Rollback, Savepoint, Begin, and Transaction.

Also, Have a look at SQL Command Cheatsheet.

3. Explain the types of SQL joins.

There are four different types of SQL Joins: (Inner) Join: An inner join is used to retrieve the records that have matching values in tables involved in the join. It combines rows from two tables based on a related column and returns only the matching record. Inner Join is mostly used to join queries.

 SELECT * FROM Table_A JOIN Table_B; SELECT * FROM Table_A INNER JOIN Table_B; 

Left (Outer) Join: The use of left join is to retrieve all the records or rows from the left and the matched ones from the right.

 SELECT * FROM Table_A A LEFT JOIN Table_B B ON A.col = B.col; 

Right (Outer) Join: The use of Right join is to retrieve all the records or rows from the right and the matched ones from the left.

 SELECT * FROM Table_A A RIGHT JOIN Table_B B ON A.col = B.col; 

Full (Outer) Join: The use of Full join is to retrieve the records that have a match either in the left table or the right table.

 SELECT * FROM Table_A A FULL JOIN Table_B B ON A.col = B.col; 

Ace your next SQL interview with our expert-written Sql Join Query Interview Questions.

4. What are the uses of SQL?

SQL databases are your data powerhouse. Using them, you can do the following:

1. Create New Databases: It is like planting seeds for future growth.
2. Insert Data: You can add new information to your database, like filling out forms.
3. Delete Data: Remove outdated information you no longer need. It’s cleaning house.
4. Update Records: Modify existing data to keep it accurate and relevant.
5. Retrieve Data: Fetch relevant data you need; think of it as using a library’s catalogue search.
6. Create/Drop Tables: Build or remove structures to organize your data; imagine setting up or taking down shelves in your garage.
7. Create Functions/Views: Customize how you access your data; this is creating shortcuts or custom views.
8. Convert Data Types: Transform data into a different format; think of changing a recipe from metric to imperial.

5. What is the ACID property in a database?

When we talk about transactions, ACID here stands for Atomicity, Consistency, Isolation and Durability that acts as a checklist to check on reliability.

• Atomicity: It means either the whole transaction succeeds or none of it will do. It is like going to a departmental store and buying a complete range of items if one item is missing, you do not buy anything at all.
• Consistency: It is about how well the data sticks to the rules. Just imagine ensuring your recipe menu does all the steps correctly; in case any step is omitted or done wrongly then you will not serve the dish.
• Isolation: Picture it as managing multiple transactions without interference. For instance, in a busy kitchen where every chef has their own dish; no one will interfere with someone else’s recipe.
• Durability: Look at it as how well the transaction can stand against interruptions. You can think of this as having your message passed through despite harsh weather, once sent always delivered regardless of external factors.

Enroll now in SQL course in Bangalore to learn more in-depth about SQL.

6. What is normalization and what are its types?

Data normalization is like tidying your house by arranging it into a city. This way, every information bit has its own storage, and nothing is dependent on something else except for when it’s necessary. Thus, we reduce chaos and ambiguity. It’s like sort of putting all the clothes into different boxes so that you can easily find them whenever you need them.

There are various types of Normalization which are discussed below:

In the First Normal Form (1NF), each data in your cell should be made up of only indivisible values as if ensuring that every house in your city has a single owner with no shared rooms or multiple keys.

The Second Normal Form (2NF) eliminates partial dependencies so that any non-key attribute is fully functionally dependent on the primary key. This is the same as every house in your city having one owner to avoid shared ownership.

The Third Normal Form (3NF) deals with transitive dependencies which means a non-key attribute must not transitively depend on another non-key attribute. It’s just like making sure house ownership in your city doesn’t rely on owning other things such as another car.

BCNF is a stricter version of 3NF, where every functional dependency X -> Y, X is a super key, thus, all the rules are clear, and it makes the organization smooth. It is like a city plan to make sure that all the houses have a single owner who is the owner of the house himself without any intermediaries.

7. What is the difference between DBMS and RDBMS?

A Database Management System is like a normal paper filing cabinet in that each folder represents a data item. This behaves well when one is keeping a few pieces of information in a simple way. When the data becomes too much or when one wants to get a certain kind of information from the cabinet, it becomes hard to find. This happens because the files are unlinked or are unstructured. 

In the case of Relational Database Management Systems, they are just like modern libraries that put books into tables with unique identification aspects. Therefore, any kind of data can be linked or grouped together so that we can be able to tap into vast amounts of information. Moreover, this helps us to understand the buried relations between the data.

8. What are the applications of SQL?

Think of SQL as a toolbox for streamlining your information resources:

• Data Integration Scripts: Equally, when writing data integration scripts, think of SQL as your personal data librarian. It helps you to combine various databases and sources into one single output.
• Setting and Running Analytical Queries: SQL allows you to question your data like an intelligent detective who can recognize patterns in the information provided.
• Retrieving Subsets of Information: For instance, it’s like a treasure map guiding you to specific pieces of information you want whether for processing transactions or analyzing while working with it.
• Adding, Updating, and Deleting Data: Using SQL is almost like reshaping and cleaning up raw material with some simple commands. With the help of SQL, inserting new records, modifying ones that already exist or removing some irrelevant information is only a matter of seconds.

9. What are the subsets of SQL?

Depending on what you want to do, SQL queries come primarily in four flavours:

DDL (Data Definition Language): This is for defining the database structure.

1. CREATE: to construct databases, tables and indexes.
2. DROP: to drop databases, tables and views.
3. ALTER: change the table’s structure (e.g. adding or dropping columns).

DML (Data Manipulation Language): It is used to manipulate data in the database.

1. INSERT: For adding new records
2. UPDATE: To modify existing records
3. DELETE: Deleting of records
4. SELECT INTO: Copying data from one table into another

DCL (Data Control Language): These control user access rights and permissions.

1. GRANT: Assigns privileges to database objects.
2. REVOKE: Withdraws privileges granted earlier from a certain object in the database.

TCL (Transaction Control Language): Basically, it consists of transaction management and commands that are used in the manipulation of that information within a given transaction.

1. COMMIT: To save changes permanently.
2. ROLLBACK: It undoes changes made during a transaction.
3. SAVEPOINT: This defines a point where the current transaction can be rolled back too in case an error occurs. 
4. SET TRANSACTION: This is used for setting up transaction characteristics. 

Together they ensure efficient management, security and integrity of your database.

10. What is a DEFAULT constraint?

Imagine you’re setting up a game with rules, and default constraints in SQL are like the default settings you establish before you start playing.

Defining Default Values: Think of default constraints as setting the starting point for a game. You establish a default value for a column in a table, so if no other value is specified when you add a new record, it automatically gets this default value.

Example: Let’s say you have a table for employee data, and you want to set a default salary for new employees. You can add a default constraint to the salary column, specifying that if no salary is provided, it defaults to a specific value, like $85,000.

Creating a Default Constraint: It’s like creating a rule for your game. You start by creating a new table for your data. Then, when defining the structure of your table, you add a default constraint to the column where you want to apply the rule. This ensures that every time a new record is added to the table without specifying a value for that column, it automatically follows the default rule you’ve set.

So, default constraints in SQL act as handy rules that ensure consistency in your data, providing a default value unless you specify otherwise. It’s like having a safety net to catch any missing information and keep your database organized.

Also, learn from our blog on MySQL Interview Questions and Answers to crack any Interview.

11. What is a UNIQUE constraint?

Think of unique constraints as a lottery ticket. Just like we have different lottery numbers for every ticket, in the same manner, in the column where the unique constraint is applied, only unique values will be allowed.

A PRIMARY KEY constraint will automatically have a UNIQUE constraint. However, unlike a PRIMARY KEY, multiple UNIQUE constraints are allowed per table.

12. What is an index?

The indexes in a database are like the index in a book. They are the ones to assist you in getting the info at a fast pace. For example, take a library where there is no catalogue, you would have to take a long time to find a book. The index is a tool that allows you to find books faster. Also, in the case of SQL, the index creation is the reason of the faster searches by means of the index which is the roadmap to the relevant data.

Syntax:

CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN)

13. Explain the types of indexes.

Primary Index: Think of it as the main key to a building. It is unique and provides entrance into all rooms. In the same way that a primary index in a database uniquely identifies each row and speeds up searches.

Secondary Index: Imagine this like one departmental index in a noticeably big library. Whereas the primary index leads you to the book, the secondary index helps narrow down your search within specific sections, so you find it quicker.

Clustered Index: Picture this as organizing books on shelves with alphabetical titles. Consequently, it organizes data physically in a table that matches the sequence of the index thereby facilitating easy retrieval just like finding them on shelves well arranged.

Non-Clustered Index: Just like card catalogues, a clustered index sorts out data on the shelf while a non-clustered index gives another lookup for some information that can be narrowed down further for ease in searching.

14. What are entities and relationships?

Visualize a database as a digital world where entities are the characters and relationships are the connections between them. 

Entities: Visualize entities as the characters in a novel, each one representing an individual, a location, or an item that keeps data. For instance, when considering employees, projects, and wages in company databases they appear as separate entities just like in any novel story.

Relationships: Relationships are contacts that help to achieve this interconnection of the characters as it is done in stories. For example, within university information systems, the student entity is linked with the department entity because there is an association among them; it could be representative of various departments attended by students.

So, for example with regards to the database world, you can say that all casts involved are these entities and their relationships complete plot lines creating a narrative out of data.

Intermediate SQL Interview Questions and Answers

15. What are SQL operators?

SQL operators are the special keywords or characters that perform specific operations. They are also used in SQL queries. These operators can be used within the WHERE clause of SQL commands. Based on the specified condition, SQL operators filter the data.

The SQL operators can be categorized into the following types:

• Arithmetic Operators:For mathematical operations on numerical data
  • addition (+)
  • subtraction (-)
  • multiplication (*)
  • division (/)
  • remainder/modulus (%)
• Logical Operators: For evaluating the expressions and returning results in True or False
  • ALL
  • AND
  • ANY
  • ISNULL
  • EXISTS
  • BETWEEN
  • IN
  • LIKE
  • NOT
  • OR
  • UNIQUE
• Comparison Operators: For comparisons of two values and checking whether they are the same or not
  • equal to (=)
  • not equal to (!= or <>)
  • less than (<),
  • greater than (>;)
  • less than or equal to (&<=)
  • greater than or equal to (>=)
  • not less than (!<)
  • not greater than (!>)
• Bitwise Operators: For bit manipulations between two expressions of integer type. It first performs the conversion of integers into binary bits and then applied operators
  • AND (& symbol)
  • OR (|, ^)
  • NOT (~)
• Compound Operators: For operations on a variable before setting the variable’s result to the operation’s result
  • Add equals (+=)
  • subtract equals (-=)
  • multiply equals (*=)
  • divide equals (/=)
  • modulo equals (%=)
• String Operators: For concatenation and pattern matching of strings
  • + (String concatenation)
  • += (String concatenation assignment)
  • % (Wildcard)
  • [] (Character(s) matches)
  • [^] (Character(s) not to match)
  • _ (Wildcard match one character)

16. What do you mean by data integrity?

Data integrity is like a loyal protector, guaranteeing data accuracy and consistency. It is there from the creation to deletion, like a lifelong companion. Integrity constraints are its rules that make sure that data is following the guidelines. It is your faithful friend, guaranteeing your data is correct and dependable.

17. What is a data warehouse?

A data warehouse is a large store of accumulated data, from a wide range of sources, within an organization. The data helps drive business decisions.

18. How would you find the second highest salary from the following table?

Code:

select * from employee
select max(e_salary) from employee 
where e_salary not in (select max(e_salary) 
from employee)

Output:

19. Why is the FLOOR function used in SQL Server?

The FLOOR() function helps to find the largest integer value for a given number, which can be an equal or lesser number.

Are you planning to learn SQL Server? Here is the SQL Certification training. Enroll now!

20. State the differences between clustered and non-clustered indexes

• Clustered Index: It is used to sort the rows of data by their key values. A clustered index is like the contents of a phone book. We can directly open the book on David’s index (for “David, Thompson”) and find information for all Davids right next to each other. Since the data are located next to each other, it helps a lot in fetching the data based on range-based queries. A clustered index is actually related to how the data is stored; only one clustered index is possible per table.
• Non-Clustered Index: It stores data at one location and indexes at another location. The index has pointers that point to the location of the data. As the indexes in a non-clustered index are stored in a different place, there can be many non-clustered indexes for a table.

State the differences between the Clustered and Non-clustered indexesState the differences between the Clustered and Non-clustered indexes

Now, we will see the major differences between clustered and non-clustered indexes:

21. What do you know about CDC in SQL Server?

CDC refers to change data capture. It captures recent INSERT, DELETE, and UPDATE activity applied to SQL Server tables. It records changes to SQL Server tables in a compatible format.

22. What is the difference between SQL and MySQL?

Now Let’s compare the difference between SQL and MySQL.

23. State the differences between SQL and PL/SQL

24. What is the need for group functions in SQL?

Group functions operate on a series of rows and return a single result for each group. COUNT(), MAX(), MIN(), SUM(), AVG(), and VARIANCE() are some of the most widely used group functions.

Enroll now in SQL course in Bangalore to learn more in-depth about SQL.

25. What do you understand about a character manipulation function?

Character manipulation functions are used for the manipulation of character data types.
Some of the character manipulation functions are as follows:

UPPER: It returns the string in uppercase.

Syntax:

UPPER(‘string’)

Example:

SELECT UPPER(‘demo string’) from String;

Output:

DEMO STRING

LOWER: It returns the string in lowercase.

Syntax:

LOWER(‘STRING’)

Example:

SELECT LOWER (‘DEMO STRING’) from String

Output:

demo string

INITCAP: It converts the first letter of the string to uppercase and retains others in lowercase.

Syntax:

Initcap(‘sTRING’)

Example:

SELECT Initcap(‘dATASET’) from String

Output:

Dataset

CONCAT: It is used to concatenate two strings.

Syntax:

CONCAT(‘str1’,’str2’)

Example:

SELECT CONCAT(‘Data’,’Science’) from String

Output:

Data Science

LENGTH: It is used to get the length of a string.

Syntax:

LENGTH(‘String’)

Example:

SELECT LENGTH(‘Hello World’) from String

Output: 11

26. What is AUTO_INCREMENT?

AUTO_INCREMENT is used in SQL to automatically generate a unique number whenever a new record is inserted into a table.

Since the primary key is unique for each record, this primary field is added as the AUTO_INCREMENT field so that it is incremented when a new record is inserted.

The AUTO-INCREMENT value starts at 1 and is incremented by 1 whenever a new record is inserted.

Syntax:

CREATE TABLE Employee(
Employee_id int NOT NULL AUTO-INCREMENT,
Employee_name varchar(255) NOT NULL,
Employee_designation varchar(255)
Age int,
PRIMARY KEY (Employee_id)
)

Check out our Blog on PL/SQL Interview Questions to crack your SQL Interview.

27. What is a “TRIGGER” in SQL?

Triggers are the silent guardians of the database that are automatically fired upon the occurrence of an event such as data insertion, updating, or deletion. They are protective keepers who enforce the rules and do not tolerate any unauthorized changes likely to alter data illegally. The triggers are sentinels with an invisible break that protects the treasure.

The syntax used to generate the trigger function is as follows:

CREATE TRIGGER trigger_name

28. Where are usernames and passwords stored in SQL Server?

In SQL Server, usernames and passwords are stored in the main database in the sysxlogins table.

29. What are the types of relationships in SQL Server databases?

Relationships are developed by interlinking the columns of one table with the column of another table. There are three different types of relationships, which are as follows:

• One-to-one relationship
• Many-to-one relationship
• Many-to-many relationship

30. How can you handle expectations in SQL Server?

TRY and CATCH blocks handle exceptions in SQL Server. Put the SQL statement in the TRY block and write the code in the CATCH block to handle expectations. If there is an error in the code in the TRY block, then the control will automatically move to the CATCH block.

Advanced SQL Interview Questions for Experienced

31. Which command is used to find out the SQL Server version?

The following command is used to identify the version of SQL Server:

Select SERVERPROPERTY('productversion')

32. What is the COALESCE function?

The COALESCE function takes a set of inputs and returns the first non-null value.

Syntax:

COALESCE(val1,val2,val3,……,nth val)

Example:

SELECT COALESCE(NULL, 1, 2, ‘MYSQL’)

Output:

1

33. What do you know about magic tables in SQL Server?

A magic table can be defined as a provisional logical table that is developed by an SQL Server for tasks such as insert, delete, or update (DML) operations. The operations recently performed on the rows are automatically stored in magic tables. Magic tables are not physical tables; they are just temporary internal tables.

34. Explain Inner Join with an example.

Inner Join gives us those records that have matching values in two tables.

Let us assume that we have two tables: Table A and Table B. When we apply Inner Join to these two tables, we will get only records common to both Table A and Table B.

Syntax:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column_x=table2.column_y;

Example:

select * from employee
select * from department

Output:

Now, we will apply Inner Join to both these tables, where the e_dept column in the employee table is equal to the d_name column of the department table.

Syntax:

select employee.e_name, employee.e_dept, department.d_name, department.d_location
from employee inner join department
on
employee.e_dept=department.d_name

Output:

After applying Inner Join, we have only those records where the departments match in both tables. As we can see, the matched departments are Support, Analytics, and Sales.

35. What are the types of views in SQL?

In SQL, the views are classified into four types. They are the following:

• Simple View: It is a view based on a single table and does not have a GROUP BY clause or other features.
• Complex View: It is a view built from several tables and includes a GROUP BY clause as well as functions.
• Inline View: It is a view built on a subquery in the FROM clause, which provides a temporary table and simplifies a complicated query.
• Materialized View: It is a view that saves both the definition and the details. It builds data replicas by physically preserving them.

SQL Interview Questions for 3 Years Experienced

36. How many authentication modes are there in SQL Server? What are they?

Two authentication modes are available in SQL Server. They are as follows:

• Windows Authentication Mode: It allows authentication for Windows but not for SQL Server.
• Mixed Mode: It allows both types of authentication—Windows and SQL Server.

37. What is a function in SQL Server?

A function is an SQL Server database object. It is basically a set of SQL statements that allow input parameters, perform processing, and return results only. A function can only return a single value or table. The ability to insert, update, and delete records in database tables is not available.

38. What is SQL Server Agent?

SQL Server Agent plays an important role in the daily work of SQL Server administrators or DBAs. This is one of the important parts of SQL Server. The aim of the server agent is to easily implement tasks using a scheduler engine that enables the tasks to be performed at scheduled times. SQL Server Agent uses SQL Server to store scheduled management task information.

39. What are views? Give an example.

Views are virtual tables used to limit the tables that we want to display. Views are nothing but the result of an SQL statement that has a name associated with it. Since views are not physically present, they take less space to store.

Let us consider an example. In the following employee table, say we want to perform multiple operations on the records with the gender “Female”. We can create a view-only table for the female employees from the entire employee table.

Now, let us implement it on SQL Server.

This is the employee table:

select * from employee

Now, we will write the syntax for the view.

Syntax:

create view female_employee as select * from employee where e_gender=’Female’
select * from female_employee

Output:

40. State the differences between views and tables.

41. What do you understand by Self Join? Explain using an example

Self Join in SQL is used for joining a table with itself. Here, depending on some conditions, each row of the table is joined with itself and with other rows of the table.

Syntax:

SELECT a.column_name, b.column_name
FROM table a, table b
WHERE condition

Example:

Consider the customer table given below.

We will now join the table using Self Join:

SQL > SELECT a.ID, b.NAME, a.SALARY
FROM CUSTOMERS a, CUSTOMERS b
WHERE a.SALARY < b.SALARY;[/code] Output:

42. What is the difference between Union and Union All operators?

The union operator is used to combine the result set of two or more select statements. For example, the first select statement returns the fish shown in Image A, and the second statement returns the fish shown in Image B. The Union operator will then return the result of the two select statements as shown in Image A U B. If there is a record present in both tables, then we will get only one of them in the final result.

Syntax:

SELECT column_list FROM table1

Union:

SELECT column_list FROM table2

Now, we will execute it in the SQL Server.

These are the two tables in which we will use the Union operator.

select * from student_details1

Union:

select * from student_details2

Output:

The Union All operator gives all the records from both tables including the duplicates.

Let us implement it in the SQL Server.

Syntax:

select * from student_details1

Union All:

select * from student_details2

Output:

43. Can you identify the employee who has the third-highest salary from the given employee table (with salary-related data)?

Consider the following employee table. In the table, Sabid has the third-highest salary (60,000).

Below is a simple query to find out which employee who has the third-highest salary. The functions RANK, DENSE RANK, and ROW NUMBER are used to obtain the increasing integer value by imposing the ORDER BY clause in the SELECT statement, based on the ordering of the rows. The ORDER BY clause is necessary when the RANK, DENSE RANK, or ROW NUMBER functions are used. On the other hand, the PARTITION BY clause is optional.

WITH CTE AS
(
SELECT Name, Salary, RN = ROW_NUMBER() OVER (ORDER BY Salary DESC) FROM EMPLOYEE
)
SELECT Name, Salary FROM CTE WHERE RN =3

44. How would you find the second-highest salary in a table?

There are several ways to find the second highest salary in a table.

1. Using the ORDER BY, LIMIT, and OFFSET Clauses:

SELECT distinct(salary) from table_name ORDER BY salary DESC LIMIT 1 OFFSET 1;

2. Using Subquery:

SELECT MAX(salary) AS salary 
FROM table_name WHERE salary <> (SELECT MAX(salary) 
FROM table_name);


Where,
table_name: your table name
salary: salary column present in your table

45. What is an effective way to prevent SQL injection in your queries?

The effective way to prevent SQL injection attacks is through input validation and parameterized queries, which include prepared statements. The developer can sanitize all the inputs, not only just the web form input, because the application code should never use the input directly.

46. What is the significance of an index in a database, and how do you use it?

Database indexing helps the DBMS find the specific rows in a table very quickly. The most common database index is the B-tree Index. A B-tree index is a situation where the data has few distinct values, such as name, dates or state codes.

47. What is the significance of transactions, and how do you ensure their consistency?

Transactions help to ensure the data consistency and integrity of the data; they also protect against errors by grouping many actions into a single unit.

Transaction funds from one account to another are the best example of ensuring data consistency;  the total value of the funds in both accounts is the same at the start and end of each transaction.

48. How will you optimize a slow-moving SQL query? What are some of the optimization techniques?

We can optimize a slow-moving SQL query by using indexing in the DBMS to find the specific rows in a table very quickly.

There are several optimization techniques:

1. 1. Indexing
   2. Using Distinct
   3. Having and Where clauses
   4. Avoiding correlated subqueries
   5. Limit
   6. Column statistics

SQL Interview Questions for 5 Years Experienced

49. What is wrong with the following SQL query?

SELECT gender, AVG(age) FROM employee WHERE AVG(age)&>30 GROUP BY gender

When this command is executed, it gives the following error:

Msg 147, Level 16, State 1, Line 1

Aggregation may not appear in the WHERE clause unless it is in a subquery contained in the HAVING clause or a select list; the column being aggregated is an outer reference.

Msg 147, Level 16, State 1, Line 1
Invalid column name ‘gender’.

This means that whenever we work with aggregate functions and use the GROUP BY clause, we cannot use the WHERE clause. Therefore, instead of the WHERE clause, we should use the HAVING clause.

When we use the HAVING clause, the GROUP BY clause should come first, followed by the HAVING clause.

select e_gender, avg(e_age) from employee group by e_gender having avg(e_age)>30

Output:

50. What do you know about the stuff() function?

The stuff() function deletes a part of the string and then inserts another part into the string, starting at a specified position.

Syntax:

STUFF(String1, Position, Length, String2)

Here, String1 is the one that will be overwritten. The position indicates the starting location for overwriting the string. Length is the length of the substitute string, and String2 is the string that will overwrite String1.

Example:

select stuff(‘SQL Tutorial’,1,3,’Python’)

This will change ‘SQL Tutorial’ to ‘Python Tutorial’

Output:

Python Tutorial

51. What is a stored procedure? Give an example.

A stored procedure is a prepared SQL code that can be saved and reused. In other words, we can consider a stored procedure to be a function consisting of many SQL statements to access the database system. We can consolidate several SQL statements into a stored procedure and execute them whenever and wherever required.

A stored procedure can be used as a means of modular programming, i.e., we can create a stored procedure once, store it, and call it multiple times as required. This also supports faster execution when compared to executing multiple queries.

Syntax:

CREATE PROCEDURE procedure_name
AS
Sql_statement
GO;
To execute we will use this:
EXEC procedure_name

Example:

We are going to create a stored procedure that will help us extract the age of the employees.

create procedure employee_age
as
select e_age from employee
go

Now, we will execute it.

exec employee_age

Output:

52. What do you understand about a temporary table? Write a query to create a temporary table

A temporary table helps us store and process intermediate results. Temporary tables are created and can be automatically deleted when they are no longer used. They are very useful in places where temporary data needs to be stored.

Syntax:

CREATE TABLE #table_name();
The below query will create a temporary table:
create table #book(b_id int, b_cost int)
Now, we will insert the records.
insert into #book values(1,100)
insert into #book values(2,232)
select * from #book

Output:

53. What is a database cursor? How to use a database cursor?

A database cursor is a control that allows you to navigate around a table’s rows or documents. It can be referred to as a pointer for a row in a set of rows. Cursors are extremely useful for database traversal operations such as extraction, insertion, and elimination.

• After any variable declaration, DECLARE a cursor. A SELECT statement must always be aligned with the cursor declaration.
• To initialize the result set, OPEN statements must be called before fetching the rows from the result table.
• To grab and switch to the next row in the result set, use the FETCH statement.
• To deactivate the cursor, use the CLOSE expression.
• Finally, use the DEALLOCATE clause to uninstall the cursor description and clear all the resources associated with it.

Here is an example SQL cursor:

DECLARE @name VARCHAR(50)
DECLARE db_cursor CURSOR FOR
SELECT name
From myDB.company
WHERE employee_name IN (‘Jay’, ‘Shyam’)
OPEN db_cursor
FETCH next
FROM db_cursor
Into @name
Close db_cursor
DEALLOCATE db_cursor

54. What is the use of the INTERSECT operator?

The INTERSECT operator helps combine two select statements and returns only those records that are common to both select statements. After we get Table A and Table B over here, and if we apply the INTERSECT operator on these two tables, then we will get only those records that are common to the result of the select statements of these two tables.

Syntax:

SELECT column_list FROM table1
INTERSECT
SELECT column_list FROM table2

Now, let us take a look at an example of the INTERSECT operator.

select * from student_details1
select * from student_details1

Output:

select * from student_details1
intersect
select * from student_details2

Output:

55. Describe how to delete duplicate rows using a single statement but without any table creation.

Let us create an employee table where the column names are ID, NAME, DEPARTMENT, and EMAIL. Below are the SQL scripts for generating the sample data:

CREATE TABLE EMPLOYEE
(
ID INT,
NAME Varchar(100),
DEPARTMENT INT,
EMAIL Varchar(100)
)
INSERT INTO EMPLOYEE VALUES (1,'Tarun',101,'[email protected]')
INSERT INTO EMPLOYEE VALUES (2,'Sabid',102,'[email protected]')
INSERT INTO EMPLOYEE VALUES (3,'Adarsh',103,'[email protected]')
INSERT INTO EMPLOYEE VALUES (4,'Vaibhav',104,'[email protected]')
–These are the duplicate rows:
INSERT INTO EMPLOYEE VALUES (5,'Tarun',101,'[email protected]')
INSERT INTO EMPLOYEE VALUES (6,'Sabid',102,'[email protected]')

We can see the duplicate rows in the above table.

DELETE e1 FROM EMPLOYEE e1, EMPLOYEE e2 WHERE e1.name = e2.name AND e1.id > e2.id

The SQL query above will delete the rows, where the name fields are duplicated, and it will retain only those unique rows in which the names are unique and the ID fields are the lowest, i.e., the rows with IDs 5 and 6 are deleted, while the rows with IDs 1 and 2 are retained.

56. Explain database white box testing and black box testing.

The white box testing method mainly deals with the internal structure of a particular database, where users hide specification details. The white box testing method involves the following:

• As the coding error can be detected by testing the white box, it can eliminate internal errors.
• To check for the consistency of the database, it selects the default table values.
• This method verifies the referential integrity rule.
• It helps perform the module testing of database functions, triggers, views, and SQL queries.

The black box testing method generally involves interface testing and database integration. The black box testing method involves the following:

• Mapping details
• Verification of incoming data
• Verification of outgoing data from the other query functions

57. What is Blocking and Troubleshooting?

Blocking: Blocking occurs when one session holds a lock on a specific resource and a second SPID attempts to acquire a conflicting lock type on the same resource.

Troubleshooting: To start the troubleshooting, you first have to define the symptoms. Troubleshooting starts with identifying the biggest CPU resource users. The most common SQL server performance symptoms are CPU, memory, network, and slow-running queries.

58. What is an Optimal Disk Configuration for a server?

Optimal Disk Configuration involves strategically organizing and using storage resources on a server, which helps us achieve the best performance and reliability for a specific workload. The main aim of optimal disk configuration is to minimize bottlenecks and ensure efficient support for the read and write demands of the database.

59. What is a Deadlock or a live Deadlock, and how do you resolve it?

A deadlock is a situation where a set of processes are blocked because each process is holding the resource and waiting for the other resource. A live deadlock is just like a deadlock-like situation where the processes block each other with a repeated state change yet make no progress.

There are several ways to prevent a deadlock or live deadlock situation:

1. Acquired multiple locks for a thread.
2. Abort and restart the process.
3. Timeouts
4. Transaction Rollback

60. What are statistics in SQL, and how do you update them?

Statistics in SQL help us compute the standard statistics, which help us execute the SQL queries more efficiently. The statistics will help us understand the total structure of the data. There are various functions that we can use in statistics, such as MEAN, MAX, MIN, MEDIAN, MODE, and Standard Deviation, and we can also use inferential statistics in SQL, like t-test, f-test, ANOVA, and analytics functions.

Updating statistics for a specific table

1. Go to the menu of the table and choose Definition.
2. Open the Optimizer Statistics tab page.
3. Choose the Update option in the context menu of the Table Statistics field.
4. Define the sample type and size that you want to use to generate the statistics.

61. What is an efficient structure to speed up the table reads?

With the help of Database Indexing we can improve the speed of the table read.

Database indexing helps DBMS find the specific rows in a table very quickly. The most common database index is the B-tree Index.that has few distinct values, such as name, dates, or state codes.

SQL Technical Interview Questions

62. What is the difference between the DELETE and TRUNCATE commands?

• DELETE:This query is used to delete or remove one or more existing tables.
• TRUNCATE:This statement deletes all the data inside a table.

The differences between DELETE and TRUNCATE commands are the following:

• TRUNCATE is a DDL command, and DELETE is a DML command.
• With TRUNCATE, we cannot really execute and trigger, while with DELETE, we can accomplish a trigger.
• If a table is referenced by foreign key constraints, then TRUNCATE will not work. So, if we have a foreign key, we have to use the DELETE command.

The syntax for the DELETE command is as follows:

DELETE FROM table_name
[WHERE condition];

Example:

select * from stu

Output:

delete from stu where s_name=’Bob’

Output:

The syntax for the TRUNCATE command:

TRUNCATE TABLE
Table_name;

Example:

select * from stu1

Output:

truncate table stu1

Output:

This deletes all the records from a table.

63. What is the difference between the DROP and TRUNCATE commands?

If a table is dropped, all things associated with that table are dropped as well. This includes the relationships defined on the table with other tables, access privileges, and grants that the table has, as well as the integrity checks and constraints.

To create and use the table again in its original form, all the elements associated with the table need to be redefined.

However, if a table is truncated, there are no such problems as mentioned above. The table retains its original structure.

64. What are the third-party tools that are used in SQL Server?

The following is the list of third-party tools that are used in SQL Server:

• SQL CHECK
• SQL DOC 2
• SQL Backup 5
• SQL Prompt
• Litespeed 5.0

65. Can we link SQL Server with others?

Yes, SQL Server can be linked with other database systems using various methods. One common method is through the use of linked servers. Linked servers allow SQL Server to establish connections and access data from other database platforms. By configuring appropriate settings and creating the necessary connections, SQL Server can interact with databases such as MySQL, Oracle, PostgreSQL, and more, enabling data integration and querying across multiple systems.

Also, check out the blog on PostgreSQL vs. MySQL.

66. What are some common clauses used with SELECT queries in SQL?

There are many SELECT statement clauses in SQL. Some of the most commonly used clauses with SELECT queries are as follows:

• FROM
  The FROM clause defines the tables and views from which data can be interpreted. The tables and views listed must exist at the time the question is given.
• WHERE
  The WHERE clause defines the parameters that are used to limit the contents of the results table. You can test for basic relationships or for relationships between a column and a series of columns using subselects.
• GROUP BY
  The GROUP BY clause is commonly used for aggregate functions to produce a single outcome row for each set of unique values in a set of columns or expressions.
• ORDER BY
  The ORDER BY clause helps in choosing the columns on which the table’s result should be sorted.
• HAVING
  The HAVING clause filters the results of the GROUP BY clause by using an aggregate function.

67. Explain the difference between OLTP and OLAP.

OLTP: It stands for Online transaction processing, and we can consider it a category of software applications that are efficient for supporting transaction-oriented programs. One of the important attributes of the OLTP system is its potential to maintain consistency. The OLTP system often follows decentralized planning to avoid single points of failure. This system is generally designed for a large audience of end users to perform short transactions. The queries involved in such databases are generally simple, require a fast response time, and, in comparison, return only a few records. So, the number of transactions per second acts as an effective measure for those systems.

OLAP: It stands for online analytical processing, and it is a category of software programs that are identified by a comparatively lower frequency of online transactions. For OLAP systems, the efficiency of computing depends heavily on the response time. Hence, such systems are generally used for data mining or maintaining aggregated historical data, and they are usually used in multidimensional schemas.

68. What is Hybrid OLAP?

Hybrid OLAP (HOLAP) uses a combination of multidimensional data structures and relational database tables to store multidimensional data. The aggregations for a HOLAP partition are stored by analysis services in a multidimensional structure. The facts are stored in a relational database.

69. How can you copy data from one table to another table?

Here, we have our employee table.

We have to copy this data into another table. For this purpose, we can use the INSERT INTO SELECT operator. Before we go ahead and do that, we will have to create another table that will have the same structure as the above-given table.

Syntax:

create table employee_duplicate(
e_id int,
e_name varchar(20),
e_salary int,
e_age int,
e_gender varchar(20)
e_dept varchar(20)
)

For copying the data, we will use the following query:

insert into employee_duplicate select * from employees

Let us take a look at the copied table.

select * from employee_duplicate

Output:

70. What is the difference between BETWEEN and IN operators in SQL?

The BETWEEN operator is employed to identify rows that fall within a specified range of values, encompassing numerical, textual, or date values. It returns the count of values that exist between the two defined boundaries.

On the other hand, the IN operator serves as a condition operator utilized for searching values within a predetermined range. When multiple values are available for selection, the IN operator is utilized.

Check out how to use IN and BETWEEN Operators in SQL with examples.

71. What is the difference between HAVING and WHERE clauses?

The main difference between the ‘HAVING’ and ‘WHERE’ clauses in SQL is that the ‘WHERE’ clause operates on individual rows of data, while the ‘HAVING’ clause is used to filter aggregated data. The ‘WHERE’ clause cannot be used with aggregate functions, whereas the ‘HAVING’ clause specifically filters results based on aggregate conditions.

Let us consider the employee table below.

SELECT Name, Salary FROM Employee WHERE Salary >=50000

SELECT Department, SUM(Salary) AS total FROM Employee GROUP BY Department

SELECT Department, SUM(Salary) AS total FROM Employee GROUP BY Department HAVING SUM(Salary)>70000

72. How can you create empty tables with the same structure as another table?

This can be achieved by fetching the records of one table into a new table using the INTO operator while fixing a WHERE clause to be false for all records. In this way, SQL prepares the new table with a duplicate structure to accept the fetched records. However, there are no records that will get fetched due to the WHERE clause in action. Therefore, nothing is inserted into the new table, thus creating an empty table.

SELECT * INTO Students_copy
FROM Students WHERE 1 = 2;

73. How will you update the prices in a product column by increasing 5% of the prices in each row?

We can update the prices of the product columns by using the UPDATE method, which is part of the DML.

UPDATE table_name SET price = price*1.05;

Where,
table_name: your table name
price: price column present in your table

With this code, we can easily modify the price of each product by 5%.

74. How will you fetch the most recent entries in a database?

We can fetch the most recent entries in a database by using the ORDER BY clause along with the timestamp column in descending order.

SELECT * FROM table_name ORDER BY timestamp_column DESC;

Where,
table_name: your table name
timestamp_column: datetime column present in your table

75. How will you calculate the average price of products in each category?

To calculate the average price of products in each category, we can use the aggregate function (AVG) with the price column and group it by the category column.

SELECT category, AVG(price) as average_price FROM table_name GROUP BY category;

Where,
table_name: table name
category: category column in your table
price: price column in your table

76. How will you calculate the total sales in each category of a product sales table?

To calculate the total sales in each category of a product sales table, we can use the aggregate function (SUM) with the sales amount column and group it by the category column.

SELECT category, SUM(sales_amount) as total_sales FROM sales GROUP BY category;

Where,
sales: table name
category: category column in your table
sales_amount: sales_amount column in your table

77. How will you find the IDs or details where there have been no entries in terms of sales?

To find the IDs or details where there have been no entries in terms of sales, we can use the LEFT JOIN or NOT EXISTS clause.

Assume we have two tables: ‘product’ with product details and ‘sales’ with sales data.

Left Joins:

SELECT p.product_id, p.product_name FROM product p LEFT JOIN sales s on p.product_id = s.product_id WHERE s.product_id is NULL;

Where,
p.product_id: product id in the product table
p.product_name: product name in the product table
s.product_id: product id in the sales table

Here, the WHERE s.product_id is NULL condition helps us filter out the rows where a match in the sales table is not found.

Not Exists:

SELECT p.product_id, p.product_name FROM products p WHERE NOT EXISTS (SELECT 1 FROM sales s WHERE s.product_id = p.product_id );

Where,
p.product_id: product id in the product table
p.product_name: product name in the product table
s.product_id: product id in the sales table

SQL Scenario-Based Interview Questions

78. Suppose there is a database where information about the employees in various verticals is stored. Your task is to find the average salary of each vertical and the highest salary among the lot.

To find the average salary of each vertical and the highest salary among the employees, we can use the group by clause along with the aggregate functions (AVG and MAX).

SELECT vertical, AVG(salary) as average_salary, MAX(salary) as highest_salary FROM employees GROUP BY vertical;

Where,
vertical: column that you want to group
salary: column in the table
employees: table name

79. Given data where store inventory is stored, your task is to find the top 3 products in each category in terms of prices.

To find the top 3 products in each category in terms of price, we can group by clause along with the  aggregate function (MAX) with the price column and set the limit as 3 in descending order.

SELECT category, product_name, MAX(price) as max_price FROM inventory GROUP BY category, product_name ORDER BY category, max_price DESC LIMIT 3;

Where,
category: column name having different categories
product_name: name of the product column
price: column having the price details
inventory: table name

80. Write an SQL query to find the month-on-month sales of a specific product in a store.

To calculate the month-on-month sales of a specific product in a store, we can use a combination of date functions and aggregate functions.

SELECT EXTRACT(YEAR_MONTH FROM sale_date) AS year_month, SUM(quantity_sold) AS total_sales FROM sales WHERE product_id = 'your_product_id' GROUP BY year_month ORDER BY year_month;

Where,
sale_date: date of the sales column
quantity_sold: number of quantity sold column
sales: table name
product_id: Id of the product column
your_product_id: pass the product ID for which you want to check.

81. Suppose in an organization, employees are mapped under managers. Write a SQL query that will fetch you the managers and employees working under them.

To fetch the managers and employees working under them, we can use a self-join to fetch the managers and the employees working under them.

SELECT M.manager_id AS manager_id, M.manager_name AS manager_name, E.employee_id AS employee_id, E.employee_name AS employee_name FROM employees E JOIN employees M ON E.manager_id = M.employee_id ORDER BY M.manager_id, E.employee_id;

Where,
manager_id: manager id column
manager_name: manager name column
employee_id: employee id column
employee_name: employee name column

82. In a store inventory, your task is to fetch the total quantity of the top product purchased by the customers.

To fetch the total quantity of the top product purchased by the customers, we can use a group by clause along with the limit in descending order.

SELECT product_id, SUM(quantity_purchased) AS total_quantity_purchased FROM purchases GROUP BY product_id ORDER BY total_quantity_purchased DESC LIMIT 1;

Where,
product_id: product id column
quantity_purchased: column having the no. of the quantity purchased
purchases: table name

83. Mention different types of replication in SQL Server?

In SQL Server, three different types of replications are available:

• Snapshot replication
• Transactional replication
• Merge replication

Download the SQL Interview Questions PDF to prepare for interviews offline.

SQL Cheat Sheets

Go through the following SQL Cheat Sheets and download their PDF:

• Download the PDF of the SQL Basics Cheat Sheet
• Download the PDF for SQL Constraints, Joins, Set Operators Cheat Sheet
• Download the PDF of SQL Grouping, inbuilt, subquery, views, and temp table Cheat Sheet
• Download the PDF for SQL User Define Function Cheat Sheet
• Download the PDF for SQL Exception Handling Index, Pivot, Transactions Cheat Sheet

Check out this exclusive blog on SQL Cheat Sheet to know more!

SQL Developer Salary on the basis of Experience

SQL Trends in 2024

According to the Bureau of Labor Statistics US, the projected growth in demand for roles like SQL Developers and Database Administrators is expected to rise 8% faster than the average rate from 2022 to 2032.

1. Global Demand: With more than 400,000 active jobs on LinkedIn in the United States alone for SQL and related roles, the tally moves to more than 100,000 jobs for the related roles.
2. Growth Projections: The growth suggested by the Bureau of Labor Statistics might increase and reach double digits in the next several years as the demand for skilled professionals who are proficient in SQL is increasing.

Job Opportunities in SQL

The job opportunities in SQL can be different for various departments; the inclusivity of SQL enhances business solutions and hence is required in most domains and processes. Some of the job opportunities in SQL are as follows:

Roles and Responsibilities of a SQL Developer

A typical job description for a SQL developer is as follows:

The roles and responsibilities of a SQL developer are quite diverse and vary for different departments. Some of the most important roles and responsibilities of a SQL developer are as follows:

1. Database design, development, and solutions: To create, design, and develop database systems and solutions for various processes and requirements.
2. Data security: Maintain data security and manage databases that are monitored for suggested security measures.
3. Data migration from third party resources:  Ability to efficiently retrieve data from third-party resources without hampering data integrity.
4. Documentation: The documentation for the database systems for easier collaboration.
5. Troubleshooting: Troubleshoot databases for optimization and performance.
6. Performance optimization: To be able to optimize the processes and enhance performance in terms of data retrieval, manipulation, etc.
7. Quality assurance: Assuring a seamless delivery of database solutions and enhancing their performance.

Conclusion

SQL is an essential skill in the 21st century, time and again proved by its requirement in every major breakthrough role.Owing to its simplicity and powerful applications, SQL isn’t limited to any specific domain or professionals, you can learn and master SQL for any job role, and help improve the entire workflow. Enroll in SQL Certification training to get an in-depth understanding of SQL workflows or join the path of excellence with SQL Master Program for an enriching learning experience.

If you have any questions, post your queries at intellipaat community space or let us know in the comments, and we’ll get back to you.