80+ Frequently Asked SQL Interview Questions and Answers [2024]

Structured Query Language (SQL) is the standard programming language used by database administrators and data analysts to query databases. It is widely used in the programming languages of websites, apps, and other platforms.

It is used to access and manipulate data in a database, including creating and modifying tables and columns and querying the data using SQL commands.

This post will undoubtedly give you some insights if you’re looking for questions and answers to use while you prepare for a SQL interview. In a job interview for a data engineer, data analyst, database administrator, etc., the following questions should be expected.

What are the basic SQL commands?

Some of the most important SQL Commands are:

• SELECT – extracts data from a database.
• CREATE TABLE – creates a new table.
• DELETE – deletes data from a database.
• INSERT INTO – inserts new data into a database.
• ALTER DATABASE – modifies a database.
• CREATE DATABASE – creates a new database.
• UPDATE – updates data in a database.

What are the four important SQL statements?

Main SQL statements are divided into these categories:

• Data Definition Language (DDL) Statements
• Data Manipulation Language (DML) Statements
• Data Control Language (DCL) Statements
• Transaction Control Language (TCL) Statements

What is the meaning of the primary key in SQL?

A primary key is a column (or set of columns) that allows each row in a database to be uniquely identified. SQL databases place a lot of importance on primary keys. They provide each row in a database table with a unique ID. A primary key can consist of one or more fields, and there can only be one primary key per table.

What are SQL data types?

A data type is an attribute that describes the kind of data an object can store, such as binary strings, numeric data, character data, financial data, date and time data, and so on.

In SQL, data types are categorized into the following groups:

• Exact numerics
• Approximate numerics
• Date and time
• Character strings
• Unicode character strings
• Binary strings

Where are user names and passwords stored in SQL Server?

User names and passwords are stored in the SQL table’s sys.server principals and sys.sql logins, respectively. Passwords are not stored in normal text.

What is SQL injection?

SQL injection attacks are one of the most common types of cyber-attacks today. They allow malicious attackers to access data by manipulating an application’s database. This can result in a wide range of negative consequences, from financial losses to the loss of sensitive data. The best way to protect against SQL injection attacks is to prevent them from happening in the first place.

What is a trigger in SQL and its types?

A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server. Triggers are used to evaluate data before or after data modification using DDL and DML statements.

There are three types of triggers – LOGON, DDL, and DML.

• LOGON triggers: These triggers are fired when a user initiates a Logon event.
• DDL triggers are triggered whenever a DDL command such as CREATE, ALTER, or DROP is issued.
• DML Triggers: These are triggered whenever a DML command modifies data. Comparable to INSERT, UPDATE, and DELETE

How would you distinguish between single-row and multiple-row functions?

A single row in a table can be affected by single row functions at once. They execute a row and then just give back one result. It is well known that length and case conversions are single-row functions.

A table’s rows can be affected by many row functions simultaneously. They are also known as group functions, executing multiple rows before returning a single output.

What is Database Normalization, and what are its main four types in SQL?

Database Normalization is a process through which data is organized for faster accessibility and to reduce data redundancy. A database’s columns and tables are organized during normalization to make sure that any dependencies are correctly upheld by database integrity constraints.

The four types of database normalization processes are as follows:

• First Normal Form (1 NF)
• Second Normal Form (2 NF)
• Third Normal Form (3 NF)
• Boyce Codd Normal Form or Fourth Normal Form (BCNF or 4 NF)

What are indexes and constraints in SQL?

Indexes are one of the most important concepts in SQL. They allow us to quickly find specific rows of data in large databases. They also help us enforce constraints, which are rules that tell the database how to behave when certain conditions are met.

Indexes are used to improve the performance of queries by speeding up the searching of data in tables. They are also used to improve the readability of data in tables.

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

What are the commonly used SQL constraints?

Commonly used SQL constraints are:

• CREATE INDEX: This command ensures that indexes are created for tables so that data retrieval is made simpler.
• FOREIGN KEY: A table’s foreign key must connect tables with similar attributes.
• DEFAULT: If no value is supplied for the fields of a column, it supplies a default value for Those fields.
• UNIQUE: This specifies that each value in a column must be distinct.
• PRIMARY KEY: A table’s primary key must identify each row.
• NOT NULL:  This condition ensures that NULL values are not accepted by columns.
• CHECK: It ensures that every column field complies with a predetermined requirement.

Is NULL equal to 0 in SQL?

A NULL value in SQL denotes a value that is unavailable or assigned. A space (‘ ‘) or zero (0) are not equivalent to the value NULL. You cannot compare the NULL value to any other value using comparison operators like “=” or”>” since it cannot be equal to or unequal to any other value.

How to prevent SQL injection attacks?

Along with constant scanning and penetration testing, security methods like input validation, sanitization, prepared statements, and parameterized SQL queries are crucial for preventing SQL injection attacks. Additionally, quick defenses like a firewall would help to safeguard the SQL database.

What is Dynamic SQL, and when can you use it?

Dynamic SQL is a feature you can use to modify your queries at run time based on one or more criteria, including the current date, the current time, or any other criteria you choose.

You can use dynamic SQL to change your queries in meaningful ways and can help you achieve your goals or simply enhance your programming skills. Whether you are creating a new app, improving a database, or adding data warehouse functionality to your production system, you can use Dynamic SQL to do it all.

What are the different types of Keys in SQL?

Primary Key: Each row or record in a database table is uniquely identified by a field called Primary Key. The unique value must be in the primary key. Primary key fields cannot contain NULL values. There can be only one primary key per table, consisting of one or more fields.

Foreign Key:  The field or group of fields in a table that relates to the primary key of another table is called a foreign key. Tables with primary keys are called parent tables, while tables with foreign keys are called child tables.

Super key: A super key is a key or set of keys that help identify entries in a table. Although not all attributes are required to identify a record, a super key can have one or more of them.

Candidate key: A Candidate key is a subset of super keys that can be used to identify records in a database based on one or more attributes. Unlike Superkey, all the features of a candidate key must be useful for identifying records.

Composite key: A composite key is a combination of two or more columns in a table that is used to identify rows in a table. A composite key is a primary key with some other attribute or column.

What are the types of indexes in SQL?

The following are types of indexes in SQL

• Hash index
• memory-optimized Non-clustered
• Clustered index
• Non-clustered index
• Unique index
• Column Store index
• Index with included columns
• Index on computed columns
• Filtered index
• Spatial index
• XML index
• Full-Text index

What do you mean by buffer pool and mention its benefits?

In SQL, a buffer pool is also referred to as a buffer cache. A buffer pool can be used by all resources to store their cached data pages. When setting up a SQL Server instance, the buffer pool’s size can be specified. The size of a buffer pool determines how many pages it can hold.

The following are the benefits of a buffer pool:

• Improvements in I/O performance
• Transaction throughput growth
• I/O latency reduction
• Improvement reading performance

What do you mean by dependency and mention the different dependencies?

When one object is referenced by name in a SQL statement kept in another object, a dependency between the two objects is established. The term “referred entity” refers to an object that appears in a SQL expression, whereas the term “referencing entity” refers to an object that contains a SQL expression.

The following are the different types of dependencies in SQL.

• Functional dependency
• Fully-functional dependency
• Multivalued dependency
• Transitive dependency
• Partial dependency

What are SQL joins, and what are the most popular SQL joins?

Joins are a fundamental part of data management in SQL. They allow two or more tables to be joined together to create a single table that can be used to analyze data. They also provide a way to filter results based on criteria in a single table, which can be useful when trying to find specific rows or columns in a large table.

There are four main types of JOINs in SQL: INNER JOIN, OUTER JOIN, CROSS JOIN, and SELF JOIN.

What are the set operators in SQL?

Data from one or more tables of the same type can be combined using the set operator. The SQL set and SQL join operators are similar, but there are some key differences. SQL set operators collect records from different queries, whereas SQL joins combine columns from different tables. Compound SQL queries are those that include set operations.

What is DBMS? 

In simple terms, a DBMS is a software system that facilitates communication between end-users and a database. As a result, it enables us to store, retrieve, define, and manage data within a database.

What is RDBMS? 

To understand RDBMS, you must first grasp the Relational Databases concept. A relational database is a structured method for storing and managing data using tables, where data is organized into rows and columns for easy retrieval and analysis.

Now that the functionality is precise, let’s define RDBMS. An RDBMS is considered a software program that allows users to manage, update, and create relational databases. Some popular RDBMSs in the market today include MySQL, Microsoft SQL Server, Oracle Database, etc.

What is PostgreSQL? 

PostgreSQL is a robust open-source database system that enhances SQL with advanced features for secure and scalable data management. 

Discuss the difference between SQL and MySQL. 

Unlike MySQL, PostgreSQL is an object-relational database that supports advanced data types like objects, arrays, and XML. This makes it more intuitive and versatile for developers. Below is the tabular form to make it more clear.

S.No	MySQL	PostgreSQL
1	Popular	Advanced
2	Relational	Object-Relational
3	MySQL Workbench	pgAdmin
4	Limited Index Types	Supports Various Indexes
5	Limited MVCC	Full MVCC Support
6	Simple Operations	Complex Operation
7	OS Threads	OS Processes
8	Reliable & Fast	Slower & Complex
9	Basic Data Types	Advanced Data Types

Discuss the difference between NoSQL and SQL.

There are various differences between NoSQL and SQL in terms of type, language, structure, support, etc. The use cases also differ, as SQL is a good choice whenever you need to work with related data, while NoSQL excels when dealing with large or changing data sets. Below is a tabular representation of their differences that might help you gain clarity regarding SQL and NoSQL.

S. No	SQL	NoSQL
1	Uses structures SQL language	Employs dynamic schema for unstructured data
2	Follows ACID Properties	Follows CAP Theorem
3	Vertically scalable	Horizontally scalable
4	Good for related data, structures queries	Ideal for large or changing data sets, flexible data models
5	Relational Databases (RDBMS)	Non-relational/distributed databases
6	Table-based	Various structures (e.g., key-value pairs, documents, graphs)
7	Strong vendor and consultant support	Limited expert support, often relies on community

Discuss the difference between SQL and PL/SQL.

SQL and PL/SQL differ in their database manipulation approaches. SQL is a declarative language for relational databases, while PL/SQL is a procedural language using SQL for database operations.

PL/SQL includes variables and control structures and can execute multiple operations at once, reducing network traffic. SQL is limited to single operations. PL/SQL offers high-speed data processing, which SQL lacks.

Discuss the differences between OLTP and OLAP.

OLAP is designed for analytical tasks, sourcing historical data from multiple databases and using a data warehouse. It’s slower with large data volumes but doesn’t require frequent updates. In contrast, OLTP is for daily business tasks, managing current operational data with a standard DBMS. It’s faster for essential operations and quick data updates initiated by users.

OLAP is subject-oriented, while OLTP is application-oriented. OLAP targets top-level executives, focusing on customers, while OLTP caters to clerks, forex workers, and managers with a market-centric approach.

What is the select statement? 

The SQL SELECT statement retrieves data from a database, allowing us to specify which columns to fetch and apply criteria for data selection. The result is stored in a result set, and we can define column headings for clarity. This statement is one of the first and last clauses evaluated by the database server to determine the potential columns for the final result set.

For example, consider a “Products” table. If we want to retrieve the names and prices of products with a price greater than $50, we can use the following SQL query:

SELECT ProductName, Price
FROM Products
WHERE Price > 50;

In this example, we specify the columns “ProductName” and “Price” in the SELECT clause. The WHERE clause sets the criteria for selecting only those records where the price is greater than $50. The result set will include the names and prices of products that meet this condition.

How to use a DISTINCT statement? 

SQL DISTINCT is a powerful tool for removing duplicates in query results. It pairs with SELECT to extract unique values from specific columns or tables. Key points include:

• It eliminates duplicates.
• It can work with aggregate functions.
• Operates on a single column.
• Syntax: SELECT DISTINCT expressions FROM tables [WHERE conditions];
• NULL values are treated as distinct.

What is CLAUSE in SQL? 

In SQL, you’ll encounter a range of clauses designed to handle substantial data volumes efficiently. These clauses act as built-in functions, receiving conditional expressions involving column names or related terms.

These expressions guide the clauses to calculate results, allowing you to filter and analyze extensive data stored within databases. So, within the realm of SQL, these clauses are your trusty companions, helping you sift through and make sense of your data effortlessly.

Mention some clauses used with select. 

Below are some common clauses used with the SELECT statement in SQL:

• WHERE: Filters rows based on a given criteria.
• ORDER BY: Sorts the result set.
• GROUP BY: Groups rows with similar values.
• HAVING: Filters grouped rows.
• LIMIT (or TOP): Limits the number of rows returned.
• DISTINCT: Retrieves unique values.
• JOIN: Combines rows from multiple tables.
• UNION (and UNION ALL): Combines results from multiple queries.
• CASE: Enables conditional logic within a query.

What is the default ordering of data using the ORDER BY clause? 

The default ordering of data using the ORDER BY clause is like arranging things from the smallest to the largest by default. Imagine you have a list of numbers: 1, 4, 2, 7, 3. When you use ORDER BY without specifying any direction, it will arrange them like this:

1, 2, 3, 4, 7

So, it goes from the smallest (1) to the largest (7) values. If you want to reverse this and start with the largest first, you can use the DESC keyword like this:

SELECT * FROM Table
ORDER BY ColumnName DESC;

So the list would be:

7, 4, 3, 2, 1

You have control over how the data is ordered with the ORDER BY clause, making it easy to sort your data in different ways.

Mention the different types of relationships in SQL 

Imagine you have a data model with tables, and these tables are connected or related to each other. You’ll often see lines connecting them. These lines represent the types of relations between the tables.

There are three main types of relations:

🔷 One-to-Many: This is the most common type. Imagine you have a list of customers, and each customer is associated with a city. One city can have many customers, but each customer belongs to only one city.

🔷 Many-to-Many: This type is used when both tables can have multiple entries on both sides. For example, think about employees making calls to customers. One employee can call many customers, and one customer can receive calls from many employees. When you have “many” on both sides, it’s a many-to-many relation. To handle this, you often need a new table in between.

🔷 One-to-One: This is rarely used but has its place. Imagine you want to store employee data and their valid identity cards. Each employee has only one valid identity card, and each identity card belongs to only one employee. It’s a one-to-one relation. You can store the identity card details in a separate table and relate them using a foreign key.

So, when you’re designing a database, understanding these relation types helps you decide how to structure your tables and their connections effectively. It’s like figuring out how puzzle pieces fit together to represent your real-world data accurately.

What are Union and Intersect? 

UNION: Think of UNION as a way to stack results on top of each other. It combines data from multiple queries into a single list. If you have two lists of items and want to put them together while removing duplicates, you can use UNION.

Example: Imagine you have a list of favorite fruits from one friend and another list from a different friend. UNION helps you create a single list of unique fruits by combining both lists

SELECT fruit FROM friend1_fruits
UNION
SELECT fruit FROM friend2_fruits;

INTERSECT: INTERSECT is like a detective tool for finding common elements. It compares two lists and shows you only the items that appear in both lists. If you have two sets of data and want to see what they have in common, you can use INTERSECT.

Example: Imagine you have a list of books your friend read and a list of books you read. INTERSECT helps you find the books you both read.

SELECT book FROM your_books
INTERSECT
SELECT book FROM friend_books;

These operations are like puzzle pieces, helping you combine or find common elements in your data.

Discuss the difference between cross join and natural join. 

A Natural Join is like combining two tables that have something in common, like students’ names in a class roster and their test scores in another table. Both tables need to have the same type of information (like names in both tables) to use a Natural Join.

Suppose you have two tables: “Students” and “TestScores,” and you want to perform a Natural Join based on the common attribute “student_id.”

Table: Students

Student ID	Student Name	Student Age
1	Alice	18
2	Bob	19
3	Charlie	18

Table: TestScores

Student ID	Student Name	Score
1	Math	95
2	Science	88
3	Math	91

Now, let’s perform a Natural Join in SQL:

SELECT * FROM Students
NATURAL JOIN TestScores;

Output:

Student ID	Name	Age	Subject	Score
1	Alice	18	Math	95
2	Bob	19	Science	88
3	Charlie	18	Math	91

In this example, the Natural Join combines the “Students” and “TestScores” tables based on the common attribute “student_id.” The resulting table includes all attributes from both tables but only one copy of the “student_id” column, as it’s the common attribute.

While in A Cross Join, also known as a Cartesian Join, is like combining every row from one table with every row from another table. It doesn’t rely on any specific condition or common attribute; it just pairs every row from the first table with every row from the second table.

Table: Colors

Color

Red

Green

Blue

Table: Sizes

Size

Small

Medium

Large

Now, let’s perform a Cross Join in SQL:

SELECT * FROM Colors
CROSS JOIN Sizes;

Output:

Color	Size
Red	Small
Red	Medium
Red	Large
Green	Small
Green	Medium
Green	Large
Blue	Small
Blue	Medium
Blue	Large

In this example, the Cross Join combines every color from the “Colors” table with every size from the “Sizes” table, resulting in all possible combinations of colors and sizes. It doesn’t consider any specific relationship or condition between the two tables, making it different from other types of joins that rely on matching criteria.

How do you remove duplicate rows in SQL? 

When working with SQL databases, you may encounter duplicate rows in your tables. This can happen even if you follow best practices like using primary keys and indexes. This article will discuss different methods to remove duplicate rows from a SQL table.

1. Using GROUP BY and HAVING Clause

• Identify duplicate rows using the GROUP BY clause.
• Use COUNT to check for duplicate occurrences.
• Delete duplicates by keeping only one instance of each.

2. Using Common Table Expressions (CTE)

• Utilize the ROW_NUMBER() function in a CTE.
• Partition data is based on specific columns.
• Delete duplicates where the row number is greater than 1.

3. Using the RANK Function

• Use RANK() with the PARTITION BY clause.
• Assign unique IDs to rows regardless of duplicates.
• Delete rows with ranks greater than 1.

4. Using SSIS Package

• SQL Server Integration Services (SSIS) can automate duplicate removal.
• Sort data using a Sort Operator in SSIS.
• Enable the “Remove rows with duplicate sort values” option.
• Store the cleaned data in a destination table.

It’s important to exercise caution when applying these methods to production data. Always test them in a safe environment before using them on live data.

What are some SQL functions used for manipulating letter cases? 

SQL provides several functions that allow you to manipulate letter cases in strings. Some common SQL functions for manipulating letter cases include:

UPPER(): This function converts all characters in a string to uppercase.

SELECT UPPER('Hello World') AS UppercaseResult;

Output: "HELLO WORLD"

LOWER(): This function converts all characters in a string to lowercase.

SELECT LOWER('Hello World') AS LowercaseResult;

Output: "hello world"

INITCAP(): This function capitalizes the first letter of each word in a string.

SELECT INITCAP('hello world') AS InitCapResult;

Output: "Hello World"

These functions are useful for formatting and presenting text data in a consistent and readable manner in SQL queries and reports.

What is a UNIQUE Key? 

SQL’s Unique Key ensures that a table’s column has only different values and no repeats. It’s a bit like a Primary Key but allows for one “blank” value (like empty space). This can be handy when you want things to be mostly unique but not super strict. You can also use Unique Keys to connect with other tables. And guess what? You can have more than one Unique Key on different columns in a table at the same time.

What is a Foreign Key? 

A FOREIGN KEY connects one table to another by referencing the PRIMARY KEY of the second table. The first table is called the child table, and the second is the parent table. It’s like the child table is borrowing information from the parent table.

What are tokens? 

In SQL, tokens are the fundamental units that make up SQL statements. These include keywords (e.g., SELECT), identifiers (e.g., table names), literals (e.g., numbers), operators (e.g., +), punctuation (e.g., commas), and comments. Tokens are used to structure and define SQL queries and commands.

What is BLOB in MYSQL? 

In MySQL, a BLOB (Binary Large Object) is a data type for storing binary data like images or files. For example, you can create a table to store images with columns for image names and their binary data. This allows you to insert, retrieve, and manage binary data in your database.

Here’s a simplified example code for creating a MySQL table to store images using the BLOB data type and inserting an image into it:

-- Create a table to store images
CREATE TABLE Images (
    image_id INT AUTO_INCREMENT PRIMARY KEY,
    image_name VARCHAR(255),
    image_data BLOB
);

-- Insert an image into the table
INSERT INTO Images (image_name, image_data) VALUES ('example.jpg', 'binary_data_here');

In this code:

• We create a table named “Images” with columns for the image’s unique ID (image_id), the image’s name (image_name), and the binary image data (image_data) stored as a BLOB.

• We insert an image named ‘example.jpg’ into the “Images” table. However, in practice, you would replace ‘binary_data_here’ with the actual binary data of the image, which can be loaded from a file using the LOAD_FILE() function or provided programmatically.

This code demonstrates the basic structure for creating a BLOB column in a MySQL table and inserting an image into it.

Discuss the difference between CHAR and VARCHAR. 

Char and Varchar are data types in SQL used for storing character strings, but they have distinct characteristics:

Char(n) Datatype:

• Stores fixed-length character strings.
• Requires a specified length (n) for each stored value.
• Utilizes a static memory location.
• Each character takes 1 byte of storage.
• Suitable when the string length is known and consistent.

Varchar(n) Datatype:

• Stores variable-length character strings.
• Allows for different string lengths up to a maximum specified length (n).
• Uses dynamic memory allocation.
• Requires extra bytes (typically 2) to store length information.
• Appropriate when string lengths vary.

Differences:

• Char is for fixed-length strings, while Varchar is for variable-length strings.
• Char uses static memory, whereas Varchar uses dynamic memory.
• Char requires 1 byte per character, while Varchar uses 1 byte per character plus additional bytes for length.
• Char suits situations with consistent string lengths, while Varchar is flexible for varying lengths.

In summary, Char is like a rigid box for your strings, always the same size, while Varchar is like an elastic band, accommodating different lengths as needed.

Discuss the difference between the ‘BETWEEN’ and ‘IN’ condition operators.

In SQL, we have two handy operators: BETWEEN and IN. They help us compare values in distinct ways. The BETWEEN operator comes in handy when we want to check if a value falls within a specific range. It’s like saying, “Is this number between 5 and 10?”

On the other hand, the IN operator is like having a checklist. We use it to see if a value is on our list of accepted values. For instance, we can ask, “Is this name in our list of approved names?” These operators are like our tools for selecting data in a database, ensuring that we get the right information that falls within the ranges we specify or matches the values we’ve listed.

Discuss the difference between IN and EXISTS.

In SQL, the IN operator compares a value to a list or subquery, typically used in the WHERE clause for known value sets. For instance, it helps filter products by CategoryID within a predefined list.

On the other hand, the EXISTS operator checks for row existence returned by a subquery. It returns true if the subquery produces any rows, making it useful for verifying specific data or table relationships in complex scenarios, often with correlated subqueries.

In short, IN compares values to fixed sets, while EXISTS checks row existence, especially in intricate subquery and table relationship cases.

Discuss the difference between DROP and TRUNCATE commands. 

Here, DROP eliminates the table entirely, while TRUNCATE keeps the table structure but removes its data. Here’s a tabular representation of the differences between the DROP and TRUNCATE commands in SQL:

Aspect	DROP Command	TRUNCATE Command
Purpose	Removes the entire table	Removes all data from the table
Structure Impact	Drops both data and table schema	Retains the table structure
Rollback	Cannot be rolled back	It can be rolled back within a transaction
Speed	Slower due to table structure operations	Faster as it only deals with data
Usage Scenario	When you want to completely get rid of a table	When you want to remove data but keep the table for future use
Example	DROP TABLE TableName;	TRUNCATE TABLE TableName;

Discuss the difference between DELETE and TRUNCATE statements.

DELETE is used for selectively removing specific rows while considering conditions, while TRUNCATE quickly removes all rows but retains the table structure. Both can be rolled back within a transaction, but TRUNCATE resets identity columns, and DELETE does not reset them by default. Below is a concise comparison between the DELETE and TRUNCATE statements in SQL:

Aspect	DELETE Statement	TRUNCATE Statement
Purpose	Removes specific rows from a table based on a condition.	It can be rolled back within a transaction.
Rollback	It can be rolled back within a transaction.	Removes all rows from a table but retains the table structure.
Speed	It is slower, especially for large datasets, as it logs individual row deletions and triggers any associated triggers.	Faster, as it deallocates data pages without logging each row deletion.
Reset Identity	Identity columns (auto-increment) are not reset by default; you need to manually reset them.	Resets identity columns (auto-increment) to the seed value.
Conditions	Allows specifying conditions to selectively delete rows.	It does not allow conditions; it removes all rows in one go.
Example	DELETE FROM TableName WHERE Condition;	TRUNCATE TABLE TableName;

Discuss the difference between NOW() and CURRENT_DATE(). 

The NOW() function serves the purpose of retrieving the current date and time, presenting it in the ‘YYYY-MM_DD HH:MM:SS’ format. In contrast, the CURRENT_DATE() function specifically fetches the date of the current day, formatted as ‘YYYY-MM_DD’. Both of these functions are employed to provide precise details of the current date and time, serving as valuable tools for time-related data in SQL.

Discuss the difference between the RANK() and DENSE_RANK() functions.

In SQL, the RANK() and DENSE_RANK() functions are used for ranking rows within result sets, but they differ in how they handle tied or identical values. The RANK() function assigns a unique rank to each row, but when multiple rows have the same values and share the same rank, it leaves gaps in the rank values. 

For instance, if two rows have a rank of 2, the next row would be assigned a rank of 4. On the other hand, the DENSE_RANK() function also assigns a unique rank to each row, but crucially, when multiple rows share the same values and rank, it does not leave gaps. This means that if two rows have a rank of 2, the next row is consecutively ranked as 3. 

So, the choice between RANK() and DENSE_RANK() depends on whether you want gaps in rank values or not when dealing with tied rows in your SQL result sets.

Discuss the difference between COALESCE() & ISNULL(). 

COALESCE() is a more versatile and widely supported function for handling null values in SQL, while ISNULL() is specific to SQL Server and serves a similar purpose but with limited portability.

Key Differences:

• COALESCE() is ANSI-standard SQL and can handle multiple expressions, whereas ISNULL() is SQL Server-specific and works with only two arguments.

• COALESCE() returns the first non-null value from a list of expressions, while ISNULL() replaces a null value with a specified replacement value.

• COALESCE() is more portable across different database systems, while ISNULL() is limited to SQL Server.

Discuss the difference between NVL and NVL2 functions.

In SQL, the NVL() function helps convert a null value into an actual value. This can be useful for various data types like dates, characters, and numbers, but it’s essential that the data types of expr1 and expr2 match. The syntax is simple: NVL(expr1, expr2), where expr1 is the source value that might be null, and expr2 is the value we want to use to replace the null.

For example, in a query that calculates annual salaries, we can use NVL() to handle commission percentages. If commission_pct is null, we substitute it with 0.

SELECT salary, NVL(commission_pct, 0),

    (salary*12) + (salary*12*NVL(commission_pct, 0)) AS annual_salary FROM employees;

Another useful function is NVL2(), which evaluates the first expression. If it’s not null, it returns the second expression; otherwise, it returns the third expression. The argument expr1 can have any data type.

For instance, in a query that calculates income, we can use NVL2() to determine whether to include commission in the calculation.

SELECT last_name, salary, commission_pct,

NVL2(commission_pct, 'SAL+COMM', 'SAL') AS income FROM employees;

These functions are handy for dealing with null values and customizing data handling in SQL queries.

Discuss the difference between the WHERE and HAVING clauses.

WHERE filters individual rows based on column values, while HAVING filters grouped results based on aggregate functions. They serve different purposes in SQL queries, with WHERE being used for row-level filtering and HAVING for group-level filtering.

Key Differences:

• WHERE is used to filter individual rows before grouping, while HAVING is used to filter groups of rows after grouping.

• WHERE is applied to the source data, whereas HAVING is applied to the results of aggregate functions.

• WHERE is typically used with SELECT, UPDATE, and DELETE statements, while HAVING is commonly used with SELECT statements involving GROUP BY.

• WHERE conditions are based on individual column values while HAVING conditions involve aggregate functions.

How do you create empty tables? 

To create an empty table in SQL, you can use the “CREATE TABLE” command. You specify the table’s name and define its columns along with their data types. However, you don’t insert any actual data into the table when creating it. It’s like setting up an empty structure to hold data in the future. This way, you have a table ready to go, but it starts with no information in it.

Can you explain the steps to create a temporary table? 

Creating a temporary table in SQL is quite straightforward:

• Step 1 – Connect: First, ensure you’re connected to your database.

• Step 2 – Define Structure: Use the CREATE TABLE command to outline your temporary table’s structure. Specify its name, columns, and data types.

• Step 3 – Optional Data: If needed, insert data using INSERT INTO. This step is for adding specific information.

• Step 4 – Utilize the Table: Now, you can use your temporary table in SQL queries during your current database session.

• Step 5 – Cleanup (Optional): If you wish, you can remove the temporary table when you’re done using it. Employ DROP TEMPORARY TABLE to do this.

Temporary tables are handy for holding data temporarily within your database session.

Which types of tables are commonly used in MySQL, and how do they serve different purposes? 

In MySQL, there are several types of tables commonly used to serve different purposes:

1. InnoDB Tables: It supports transactions, foreign keys, and row-level locking. This makes it suitable for applications requiring data integrity, such as e-commerce platforms and content management systems.

2. MyISAM Tables: MyISAM is an older storage engine that’s still used in some scenarios. It’s known for its simplicity and speed, making it suitable for read-heavy applications like blogs or forums. However, it doesn’t support transactions or foreign keys.

3. MEMORY Tables: It is ideal for caching purposes or for holding temporary data that doesn’t need to be persisted. However, data is lost when the server restarts.

4. ARCHIVE Tables: ARCHIVE tables are used for storing large amounts of data efficiently. They are well-suited for applications that require historical or archived data, as they use minimal disk space. However, they have limited functionality compared to other storage engines.

5. CSV Tables: It is useful for importing and exporting data in CSV format and can be used for data exchange between MySQL and other applications.

6. Federated Tables: Federated tables allow you to access data from a remote MySQL server. They are handy for distributing database scenarios or when you need to combine data from multiple sources into one query.

How can you select unique records from a table? 

To get unique records from a table in SQL, you can use the DISTINCT keyword with a SELECT statement. This keyword ensures that only different values are shown for the chosen columns. For instance, if you want to see a list of unique names from a “Customers” table, you’d write:

SELECT DISTINCT FirstName, LastName
FROM Customers;

This query retrieves distinct combinations of first and last names from the “Customers” table, excluding duplicates.

What are partitioned tables? 

Partitioned tables in SQL are like breaking a big table into smaller pieces based on specific values in a chosen column, known as the partition key. This helps manage large datasets and speeds up queries. Each piece is called a partition and works like its own mini-table, making it easy to handle and search for data. 

People often do this to organize data by time, numbers, or locations. Different databases support partitioning, but the details can vary. For instance, you could split a “Sales” table by “OrderDate” into monthly sections, simplifying dealing with lots of sales data.

How to copy tables in SQL? 

To duplicate or “clone” a table’s contents in SQL, you can use various methods:

1. CREATE TABLE … AS SELECT:

Create a new table and copy data from an existing one:

CREATE TABLE new_table AS SELECT * FROM original_table;

Be cautious with large tables; it can be slow and resource-intensive. The new table inherits some basics but not indexes or auto-increment settings.

2. CREATE TABLE… LIKE:

To replicate the structure of an existing table, use:

CREATE TABLE new_table LIKE original_table;

This makes a new table with the same structure but no data.

3. INSERT … SELECT:

For copying data between tables, you can do the following:

INSERT INTO new_table SELECT * FROM original_table;

These methods give you control over duplicating tables in SQL, letting you choose what to copy and how.

How to delete a table in SQL? 

To delete a table in SQL, you can use the DROP TABLE statement followed by the table name you want to delete. Here’s the basic syntax:

DROP TABLE table_name;

Replace table_name with the name of the table you wish to delete.

Here’s an example:

DROP TABLE Customers;

In this example, the “Customers” table would be deleted, and all data within it would be lost.

How can you delete a database in PostgreSQL? 

To delete a database in PostgreSQL, you can use the DROP DATABASE command. However, you need to be careful when executing this command because it permanently removes the entire database and all its associated objects, including tables, views, functions, and data. 

How can I change the data type of an existing column in SQL, and what steps are involved? 

To modify the data type of an existing column in SQL, you can follow a series of steps. First, connect to your database using your preferred database management tool. Once connected, examine the current data type of the target column using commands like DESCRIBE or SHOW COLUMNS. 

Next, employ the ALTER TABLE statement, specifying the table name, column name, and the desired new data type. After making the alteration, reconfirm the change’s success by checking the column’s data type once more. Depending on the new data type, you may need to update the column’s data accordingly to match the new type. 

Finally, it’s crucial to thoroughly test any queries or applications that utilize this modified column to ensure they function correctly with the adjusted data type. This process should be approached with care and thorough testing to prevent data loss or unforeseen issues, especially in production environments.

What are SQL comments?

SQL comments are like notes you can add within your SQL code to explain things or provide information. These comments don’t get executed when you run your SQL queries; instead, they’re there to help you and others understand the code better. 

There are two types: single-line comments, which start with two hyphens (–), and multi-line comments, enclosed between /* and */. Single-line comments are great for short explanations, while multi-line comments let you write longer notes. 

SQL comments are handy for documenting code, clarifying complex queries, and making your code more understandable to yourself and fellow developers, especially in larger projects.

What are Nested Triggers? 

Nested triggers in SQL databases are like triggers within triggers. When a trigger is set off by an action, and that trigger, in turn, causes another trigger to activate, it’s called a nested trigger. 

For example, Trigger A might be triggered by an action, and if it does something that makes Trigger B fire, you have a nested trigger situation. These nested triggers can make your database actions complex and harder to manage. It’s essential to be cautious when using them and to understand how they interact, as they can lead to unexpected results or performance problems if not handled carefully.

What is denormalization? 

Denormalization in SQL simplifies database tables by introducing redundancy. Unlike normalization, which minimizes duplication, denormalization intentionally adds redundancy. It’s done to speed up data retrieval by reducing the need for complex joins. While it can improve query performance, it may lead to data inconsistencies and increased storage needs. So, use denormalization judiciously to balance performance and data integrity.

What is a View? 

In SQL, a view is like a virtual table created by a query. It’s not a real table with data, but it acts like one when you query the database. Views have a few handy tricks up their sleeves:

• Simplified Queries: You can use views to make complex queries easier to read and use.

• Data Security: They help keep your data safe by letting you control who sees what. You can limit the columns and rows visible to different users.

• Data Flexibility: Even if you change the actual tables, views stay the same as long as you keep the same column names.

• Performance Boost: In some cases, views can make your queries run faster by doing some calculations in advance.

In a nutshell, views are like SQL’s way of making your life easier when dealing with databases.

What is a Stored Procedure? 

A stored procedure is like a pre-saved set of SQL commands that you can use again and again. Instead of rewriting the same SQL code, you save it as a stored procedure and execute it whenever you need.

The cool part is that you can give it some values (parameters), and the stored procedure can do different things based on those values. It’s like having a handy SQL toolbox!

What is a cursor? 

In SQL, a cursor is like a virtual hand for handling data row by row. It helps you work through a list of data, performing actions on each item individually. Cursors are useful when you need precise control over data operations. 

There are two types: implicit, created automatically, and explicit, which you manage yourself for more complex tasks. Cursors offer fine-grained data manipulation.

What is a User-defined function? 

In SQL, a User-Defined Function (UDF) is a custom tool you create for specific tasks. It’s like your own mini-program in SQL. UDFs take input, do something with it, and give you results. There are two types: Scalar Functions (for single results) and Table-Valued Functions (for data tables). UDFs keep your SQL code neat and reusable.

What are Group Functions? 

In SQL, Group Functions (also known as Aggregate Functions) are special functions used to perform operations on groups of rows and return a single result for each group. Instead of working on individual rows, these functions operate on sets of rows and provide a summary or aggregation of the data within each group.

What are scalar functions? 

Scalar functions in SQL are like mini-programs that work on individual pieces of data. They take one value, do something with it, and give you a result. For instance, they can change text to uppercase, find the length of a word, or round a number. These functions are handy for tasks like data cleaning, where you need to tweak data within a single row of a table.

What are character manipulation functions? 

Character manipulation functions in SQL are a group of functions used to perform various operations on character or text data. These functions allow you to manipulate and transform text values within your SQL queries.

How many Aggregate functions are available in SQL? 

Aggregate functions in SQL provide insights into the data within a database. For example, AVG calculates the average value of a column in a database. There are five primary aggregate functions: MIN (finds the minimum), MAX (finds the maximum), COUNT (counts the number of rows), SUM (adds up the values), and AVG (calculates the average). These functions offer valuable data summaries and insights when querying databases.

What is a unique constraint? 

A unique constraint in SQL ensures that a specific column or group of columns doesn’t have any duplicate values. It’s like a rule that says, “Each value must be unique.” This is commonly used for things like employee IDs or email addresses to prevent duplicates and maintain data integrity.

What is a subquery? 

A subquery in SQL is like a query within a query. It helps you ask specific questions within a larger question. This nested query is used to filter or calculate data in a structured way.

What is an SQL server? 

An SQL server is a software application that manages and stores databases. It’s like a powerful librarian for data. SQL servers are designed to handle tasks like storing, retrieving, and organizing information efficiently. 

They use the SQL language to communicate with databases, making it easy for users and applications to interact with the stored data. SQL servers are widely used in various industries to manage and secure valuable information.

How to start an SQL Server? 

Starting SQL Server depends on your operating system and preferences. On Windows, you can simply click the Start button, search for “SQL Server,” and open it. Alternatively, press “Windows + R,” type “services.msc,” locate SQL Server in the list, right-click it, and select “Start.” 

If you prefer a more organized approach, you can use SQL Server Configuration Manager to manage and start SQL Server instances. For SQL Server Express, a lightweight version, you can open Command Prompt, navigate to its installation folder, and run “sqlservr.exe” to initiate it.

How to restore the database in SQL Server? 

Restoring a database involves taking data from a backup and applying any logged transactions to it. Essentially, it’s the process of turning a backup file back into a usable database.

You can perform database restoration using two methods:

Method 1 – T-SQL: This involves using SQL commands to restore a database. Here’s an example:

Restore database TestDB from disk = 'D:\TestDB_Full.bak' with replace

In this command, “TestDB” is the name of the database you want to restore, and ‘D:\TestDB_Full.bak’ is the location of the backup file. If you’re creating a new database or need to specify new data and log file paths, you can use the “MOVE” option like this:

RESTORE DATABASE TestDB FROM DISK = 'D:\TestDB_Full.bak' 

WITH MOVE 'TestDB' TO 'D:\Data\TestDB.mdf', 

MOVE 'TestDB_Log' TO 'D:\Data\TestDB_Log.ldf'

Method 2 – SSMS (SQL Server Management Studio): In this method, you use SQL Server Management Studio (SSMS) for the restoration process:

• Connect to your database instance in SSMS.
• Right-click on the “Databases” folder.
• Select “Restore database.”
• Choose the backup file and configure options.
• Click “OK” to initiate the restore process.

These methods allow you to recover a database from a backup efficiently.

What is schema in SQL Server? 

In SQL Server, a schema is like a virtual folder that holds different types of information, such as tables and functions. It helps keep things organized, manage who can access what, and prevent naming conflicts. For instance, you can have a “Sales” folder for all sales-related stuff and an “HR” folder for HR-related things. It’s a way to tidy up and control your database world.

What is Database Black Box Testing? 

Database black box testing is like testing a vending machine without knowing how it’s built inside. Testers interact with the database without peeking into its inner workings. They focus on checking if the database does what it’s supposed to do, like providing the right answers when asked questions. 

Testers don’t need to know how the database is designed; they just ensure it functions correctly, keeps data safe, and handles different situations, like errors or security checks. It’s a way to ensure the database works well from the outside, as a user would expect.

What is Data Integrity? 

Data integrity in databases means keeping data accurate, complete, and secure. It ensures that information is correct, not missing, and consistent across the database. Reliable data is free from errors and safe from unauthorized changes. It follows the rules and stays protected. Maintaining relationships between data is important, too. 

Keeping track of data changes is part of data integrity. It’s crucial for making good decisions and building trust. Problems with data integrity can lead to errors and security issues for organizations.

What is a grant command? 

In SQL, the GRANT command is used to give specific privileges or permissions to database users or roles. These privileges can include the ability to perform various actions on database objects like tables, views, or procedures. The GRANT command allows administrators to control who can access, modify, or manage specific parts of a database.

For example, you can use the GRANT command to give a user permission to select data from a table, update records, or even create new tables. It’s a way to define the level of access and control that different users or roles have within a database system.

In essence, the GRANT command helps ensure that users can perform only the actions they are authorized to do, which is a fundamental aspect of database security and access control.

What is COMMIT? 

In SQL, COMMIT is like saving changes to a database. It makes them permanent, unlike ROLLBACK, which undoes changes.

Discuss differences between commit and checkpoint.

A COMMIT finalizes the current transaction in the current session, ensuring the consistency of the data modified during that transaction. Meanwhile, a checkpoint writes all committed changes to disk up to a specific SCN (System Change Number) stored in the control file and datafile headers, ensuring the overall consistency of the database.

Conclusion

I hope these SQL interview questions offer you a good picture of what to expect from an interview perspective. More exposure to the SQL environment is necessary to crack the SQL interview; this can be attained by studying SQL in-depth. In order to evaluate key SQL principles, you must also practice SQL query interview questions and keep reading.

If you are looking for SQL query clarification, please visit the SQL cheat sheet to get more insights about SQL statements.

To gain hands-on experience by executing SQL queries, check out these SQL practice platforms.