1. What is Python and why is it used in data analytics?

Python is a high-level programming language known for its simplicity and readability. It is widely
used in data analytics because it offers powerful libraries such as NumPy, Pandas, and Matplotlib,
which help in data manipulation, analysis, and visualization. Python also supports automation and
integrates well with databases and big data tools.

2. What are the different data types available in Python?

Python provides several built-in data types including integers (int), floating-point numbers (float),
strings (str), booleans (bool), lists, tuples, sets, and dictionaries. Each data type serves a specific
purpose and helps in storing and manipulating data efficiently.

3. What is the difference between list and tuple?

A list is a mutable data type, meaning its elements can be changed after creation, whereas a tuple
is immutable, meaning its elements cannot be modified once defined. Lists use square brackets [],
while tuples use parentheses ().

4. Explain mutable and immutable data types.

Mutable data types can be modified after their creation, such as lists and dictionaries. Immutable
data types cannot be changed once created, such as strings and tuples. This distinction is
important for memory management and data integrity.

5. What is indexing in Python?

Indexing is the process of accessing elements from a sequence using their position. Python uses
zero-based indexing, meaning the first element is at index 0.

6. What is slicing and how is it used?

Slicing is used to extract a subset of elements from a sequence like a list or string. It is done using
the syntax [start:end:step].

7. What is negative indexing?

Negative indexing allows accessing elements from the end of a sequence. For example, -1 refers
to the last element.

8. What is the difference between is and ==?

'==' checks whether two values are equal, while 'is' checks whether two variables refer to the same
object in memory.

9. What happens when you access an index out of range?

It raises an IndexError, indicating that the index does not exist in the sequence.

10. How do you reverse a string in Python?

A string can be reversed using slicing: s[::-1].

11. What is list comprehension?

List comprehension is a concise and efficient way to create lists in Python using a single line of
code. It combines a loop and optional conditions into one readable expression. It is widely used in
data analytics to transform and filter data quickly.

Example:
squares = [x**2 for x in range(5)]
12. Why is list comprehension preferred over loops?

List comprehension is preferred because it reduces the number of lines of code, improves
readability, and is generally faster than traditional loops. It is especially useful when working with
large datasets in data analytics.

13. What is a dictionary in Python?

A dictionary is a data structure that stores data in key-value pairs. Each key is unique and is used
to access its corresponding value. Dictionaries are useful for storing structured data such as
records.

Example:
data = {"name": "Suprika", "age": 22}

14. What is the difference between list, set, and tuple?

A list is an ordered and mutable collection that allows duplicate values.

A tuple is ordered but immutable, meaning it cannot be changed after creation.
A set is an unordered collection that does not allow duplicate elements.

15. What are Python functions?

Functions are reusable blocks of code designed to perform a specific task. They help in organizing
code, improving readability, and reducing repetition. Functions are essential in data processing and
analysis.

16. What is the difference between arguments and parameters?

Parameters are variables defined in a function declaration, while arguments are the actual values
passed to the function when it is called.

17. What are default arguments?

Default arguments are parameters that have predefined values. If no value is provided during the
function call, the default value is used.

Example:
def greet(name="Guest"):

18. What are *args and **kwargs?

*args allows a function to accept multiple positional arguments.

**kwargs allows a function to accept multiple keyword arguments.
They are useful when the number of inputs is not fixed.
19. What is a lambda function?

A lambda function is an anonymous function defined using the lambda keyword. It is used for short,
simple operations and does not require a formal function definition.

Example:
add = lambda a, b: a + b

20. What is the difference between return and print?

The return statement sends a value back to the function caller and is used in further processing.
The print statement only displays output on the screen and does not return any value.

21. What is exception handling?

Exception handling is a method used to handle runtime errors in a program. It prevents the
program from crashing and ensures smooth execution using try and except blocks.

22. What is try, except, and finally?

The try block contains code that may cause an error.

The except block handles the error if it occurs.
The finally block always executes, regardless of whether an error occurs or not.

23. What is the difference between syntax error and runtime error?

A syntax error occurs when the code violates Python rules and cannot be executed.
A runtime error occurs during execution, even if the syntax is correct.

24. What is file handling in Python?

File handling is used to read, write, and manipulate files. It is important in data analytics for
handling datasets stored in files such as text or CSV files.

25. How do you read a CSV file in Python?

A CSV file can be read using the Pandas library. It loads the data into a DataFrame for analysis.

Example:
import pandas as pd
df = pd.read_csv("[Link]")
26. What is NumPy?

NumPy (Numerical Python) is a powerful library used for numerical computations in Python. It
provides support for arrays, matrices, and mathematical functions. It is widely used in data
analytics for handling large datasets efficiently.

27. Why is NumPy faster than Python lists?

NumPy is faster because:

 It uses optimized C code internally

 It stores data in contiguous memory

 It supports vectorized operations

This makes it highly efficient for large-scale numerical computations.

28. What is a NumPy array?

A NumPy array is a multi-dimensional array that stores elements of the same data type. It is faster
and more memory-efficient than Python lists.

Example:

import numpy as np

arr = [Link]([1, 2, 3])

29. What is Pandas?

Pandas is a Python library used for data manipulation and analysis. It provides powerful data
structures like Series and DataFrame, which are essential for handling structured data.

30. What is a DataFrame?

A DataFrame is a two-dimensional, tabular data structure with rows and columns, similar to an
Excel sheet or SQL table.

31. How do you create a DataFrame?

A DataFrame can be created using dictionaries, lists, or external files.

Example:

import pandas as pd

data = {"Name": ["A", "B"], "Age": [20, 25]}

df = [Link](data)

32. What is the difference between loc and iloc?

 loc → label-based indexing

 iloc → position-based indexing

Example:
[Link][0] vs [Link][0]

33. How do you filter rows in Pandas?

You can filter rows using conditions.

Example:

df[df["Age"] > 20]

34. How do you handle missing values in Pandas?

Missing values can be handled by:

 Removing them (dropna())

 Filling them (fillna())

 Replacing with mean/median

35. What is groupby in Pandas?

The groupby() function is used to group data based on a column and perform aggregation like sum,
mean, or count.

36. What is apply function in Pandas?

The apply() function is used to apply a custom function to rows or columns of a DataFrame.

37. How do you sort a DataFrame?

You can sort using:

df.sort_values(by="Age")
38. How do you find unique values in a column?

df["column_name"].unique()

39. What is data cleaning?

Data cleaning is the process of removing errors, duplicates, and inconsistencies from data to
improve its quality.

40. What is data preprocessing?

Data preprocessing involves transforming raw data into a format suitable for analysis. It includes
cleaning, normalization, and encoding.

41. What is exploratory data analysis (EDA)?

EDA is the process of analyzing datasets to summarize their main characteristics using statistics
and visualizations before applying models.

42. What is the use of describe() function?

The describe() function provides summary statistics such as mean, median, standard deviation,
min, and max.

43. What is the use of head() and tail()?

 head() → shows first 5 rows

 tail() → shows last 5 rows

Useful for quickly inspecting data.

44. What is type casting?

Type casting is converting one data type into another.

Example:
int("10")

45. What is None in Python?

None represents the absence of a value. It is commonly used to indicate missing or undefined data.
46. What is a module?

A module is a file containing Python code (functions, variables) that can be imported and reused.

47. What is a package?

A package is a collection of multiple modules organized in directories.

48. How do you import modules?

Modules can be imported using:

import math

from math import sqrt

49. What is name == 'main'?

It is used to check whether a Python file is being run directly or imported as a module.

50. Explain a real-world use case of Python in data analytics.

Python is used in real-world applications like sales analysis. For example, a company can use
Pandas to analyze customer purchase data, identify trends, and make business decisions such as
improving product sales or targeting specific customers.

51. Write a program to add two numbers using a function.

def add(a, b):

return a + b

result = add(10, 5)

print("Sum:", result)

Explanation:
A function add() takes two numbers as input and returns their sum.

52. Write a program to check whether a number is even or odd.

num = int(input("Enter a number: "))

if num % 2 == 0:

print("Even")

else:

print("Odd")

Explanation:
If a number is divisible by 2, it is even; otherwise, it is odd.

53. Write a program to find the maximum of three numbers.

a, b, c = 10, 25, 15

maximum = max(a, b, c)

print("Maximum:", maximum)

Explanation:
The built-in max() function returns the largest value.

54. Write a program to calculate factorial of a number.

num = 5

fact = 1

for i in range(1, num + 1):

fact *= i

print("Factorial:", fact)

Explanation:
Factorial is the product of all numbers from 1 to n.

55. Write a program to reverse a string.

text = "python"

reversed_text = text[::-1]

print("Reversed:", reversed_text)
Explanation:
Slicing [::-1] reverses the string.

56. Write a program to check if a string is palindrome.

text = "madam"

if text == text[::-1]:

print("Palindrome")

else:

print("Not Palindrome")

Explanation:
A palindrome reads the same forward and backward.

57. Write a program to count vowels in a string.

text = "hello world"

vowels = "aeiou"

count = 0

for char in text:

if char in vowels:

count += 1

print("Vowel count:", count)

Explanation:
We loop through the string and count vowels.

58. Write a program to remove duplicates from a list.

lst = [1, 2, 2, 3, 4, 4]

unique_list = list(set(lst))

print("Without duplicates:", unique_list)

Explanation:
Set removes duplicate elements automatically.

59. Write a program to find sum of elements in a list.

lst = [1, 2, 3, 4, 5]

total = sum(lst)

print("Sum:", total)

Explanation:
The built-in sum() function calculates total.

60. Write a program to find max and min in a list.

lst = [10, 20, 5, 40]

print("Maximum:", max(lst))

print("Minimum:", min(lst))

Explanation:
max() and min() return highest and lowest values.

61. Write a program to count frequency of elements in a list.

Code:

lst = [1, 2, 2, 3, 3, 3, 4]

freq = {}

for item in lst:

if item in freq:

freq[item] += 1

else:

freq[item] = 1
print(freq)

Explanation:
This program uses a dictionary to count how many times each element appears in the list.

62. Write a program to sort a list.

Code:

lst = [5, 2, 9, 1]

[Link]()

print(lst)

Explanation:
The sort() method arranges elements in ascending order. You can use reverse=True for
descending order.

63. Write a program to find common elements between two lists.

Code:

list1 = [1, 2, 3, 4]

list2 = [3, 4, 5, 6]

common = list(set(list1) & set(list2))

print(common)

Explanation:
Sets are used to find the intersection of two lists efficiently.

64. Write a program to merge two lists.

Code:

list1 = [1, 2]

list2 = [3, 4]

merged = list1 + list2

print(merged)
Explanation:
Lists can be merged using the + operator.

65. Write a program to create dictionary from two lists.

Code:

keys = ["a", "b", "c"]

values = [1, 2, 3]

result = dict(zip(keys, values))

print(result)

Explanation:
The zip() function pairs elements from both lists to create a dictionary.

66. Write a program to sort dictionary by values.

Code:

data = {"a": 3, "b": 1, "c": 2}

sorted_data = dict(sorted([Link](), key=lambda x: x[1]))

print(sorted_data)

Explanation:
The dictionary is sorted based on values using sorted() and a lambda function.

67. Write a program to find key with maximum value in dictionary.

Code:

data = {"a": 10, "b": 25, "c": 15}

max_key = max(data, key=[Link])

print(max_key)

Explanation:
The max() function returns the key with the highest value.
68. Write a program to count words in a string.

Code:

text = "Python is easy and powerful"

words = [Link]()

count = len(words)

print(count)

Explanation:
The split() function separates words, and len() counts them.

69. Write a program to remove punctuation from string.

Code:

import string

text = "Hello, world!"

clean_text = ""

for char in text:

if char not in [Link]:

clean_text += char

print(clean_text)

Explanation:
This removes punctuation using the [Link] constant.

70. Write a program to generate Fibonacci series.

Code:

n = 10
a, b = 0, 1

for _ in range(n):

print(a, end=" ")

a, b = b, a + b

Explanation:
Each number is the sum of the previous two numbers, starting from 0 and 1.

71. Write a function using *args to calculate sum.

def calculate_sum(*args):

total = 0

for num in args:

total += num

return total

print(calculate_sum(1, 2, 3, 4))

Explanation:
*args allows passing multiple values. These values are stored as a tuple and summed using a loop.

72. Write a function using default arguments.

def greet(name="Guest"):

return f"Hello, {name}"

print(greet())

print(greet("Suprika"))

Explanation:
If no argument is passed, the default value "Guest" is used.

73. Write a function using **kwargs.

def display_info(**kwargs):

for key, value in [Link]():

 print(key, ":", value)

display_info(name="Suprika", age=22)

Explanation:
**kwargs allows passing multiple key-value arguments as a dictionary.

74. Write a program to demonstrate mutable default argument issue.

def add_item(item, my_list=[]):

my_list.append(item)

return my_list

print(add_item(1))

print(add_item(2)) # Unexpected behavior

Correct Approach:

def add_item(item, my_list=None):

if my_list is None:

my_list = []

my_list.append(item)

return my_list

Explanation:
Default mutable arguments retain values across function calls, which can cause unexpected
results.

75. Write a program to handle ZeroDivisionError.

try:

num = int(input("Enter numerator: "))

den = int(input("Enter denominator: "))

result = num / den

print("Result:", result)

except ZeroDivisionError:

print("Cannot divide by zero")

Explanation:
The program safely handles division by zero using exception handling.

76. Write a program to read a text file.

with open("[Link]", "r") as file:

content = [Link]()

print(content)

Explanation:
The with statement ensures the file is properly closed after reading.

77. Write a program to read CSV using Pandas.

import pandas as pd

df = pd.read_csv("[Link]")

print(df)

Explanation:
Reads CSV data into a DataFrame for analysis.

78. Write a program to display first 5 rows of DataFrame.

import pandas as pd

df = pd.read_csv("[Link]")

print([Link]())

Explanation:
head() shows the first 5 rows, useful for previewing data.

79. Write a program to find shape of DataFrame.

import pandas as pd

df = pd.read_csv("[Link]")

print([Link])
Explanation:
Returns number of rows and columns as a tuple.

80. Write a program to filter rows based on condition.

import pandas as pd

df = pd.read_csv("[Link]")

filtered = df[df["Age"] > 25]

print(filtered)

Explanation:
Filters rows where Age is greater than 25.

81. Write a program to detect missing values

import pandas as pd

data = {"A": [1, 2, None], "B": [4, None, 6]}

df = [Link](data)

print([Link]())

Explanation:
The isnull() function detects missing values (NaN) and returns True where values are missing.

82. Write a program to fill missing values with mean

import pandas as pd

data = {"A": [1, 2, None], "B": [4, None, 6]}

df = [Link](data)

df["A"].fillna(df["A"].mean(), inplace=True)

df["B"].fillna(df["B"].mean(), inplace=True)
print(df)

Explanation:
Missing values are replaced using the mean of each column, which helps maintain data
consistency.

83. Write a program using groupby and aggregation

import pandas as pd

data = {"Department": ["HR", "IT", "HR", "IT"],

"Salary": [30000, 50000, 35000, 60000]}

df = [Link](data)

result = [Link]("Department")["Salary"].mean()

print(result)

Explanation:
groupby() groups data by department, and mean() calculates average salary for each group.

84. Write a program to sort DataFrame by column

import pandas as pd

data = {"Name": ["A", "B", "C"], "Age": [25, 20, 30]}

df = [Link](data)

df_sorted = df.sort_values(by="Age")

print(df_sorted)

Explanation:
sort_values() sorts the DataFrame based on the specified column.

85. Write a program to rename DataFrame columns

import pandas as pd

data = {"A": [1, 2], "B": [3, 4]}

df = [Link](data)

[Link](columns={"A": "Age", "B": "Marks"}, inplace=True)

print(df)

Explanation:
The rename() function changes column names for better readability.

86. Write a program to drop duplicate rows

import pandas as pd

data = {"A": [1, 2, 2, 3], "B": [4, 5, 5, 6]}

df = [Link](data)

df = df.drop_duplicates()

print(df)

Explanation:
drop_duplicates() removes duplicate rows from the DataFrame.

87. Write a program to change column data type

import pandas as pd

data = {"A": ["1", "2", "3"]}

df = [Link](data)

df["A"] = df["A"].astype(int)
print([Link])

Explanation:
astype() converts the data type of a column (here from string to integer).

88. Write a program to add a new column in DataFrame

import pandas as pd

data = {"A": [1, 2, 3], "B": [4, 5, 6]}

df = [Link](data)

df["C"] = df["A"] + df["B"]

print(df)

Explanation:
A new column is created by performing operations on existing columns.

89. Write a program to find unique values in a column

import pandas as pd

data = {"A": [1, 2, 2, 3, 3, 3]}

df = [Link](data)

print(df["A"].unique())

Explanation:
unique() returns distinct values from a column.

90. Write a program to count category frequency

import pandas as pd

data = {"Category": ["A", "B", "A", "C", "B", "A"]}

df = [Link](data)

print(df["Category"].value_counts())

Explanation:
value_counts() counts the frequency of each category in a column.

91. Write a program to create a NumPy array.

import numpy as np

arr = [Link]([1, 2, 3, 4, 5])

print(arr)

92. Write a program to find mean of NumPy array.

import numpy as np

arr = [Link]([10, 20, 30, 40])

mean_value = [Link](arr)

print("Mean:", mean_value)

93. Write a program for element-wise array operation.

import numpy as np

arr1 = [Link]([1, 2, 3])

arr2 = [Link]([4, 5, 6])

result = arr1 + arr2 # element-wise addition

print(result)

94. Write a program to reshape NumPy array.

import numpy as np

arr = [Link]([1, 2, 3, 4, 5, 6])

reshaped = [Link](2, 3)

print(reshaped)

95. Write a program to find max and min in NumPy array.

import numpy as np

arr = [Link]([5, 10, 2, 8])

print("Max:", [Link](arr))

print("Min:", [Link](arr))

96. Write a program to generate random numbers using NumPy.

import numpy as np

random_numbers = [Link](5) # 5 random numbers between 0 and 1

print(random_numbers)

97. Write a program using lambda function.

add = lambda a, b: a + b

print(add(3, 5))

98. Write a program to flatten nested list.

nested_list = [[1, 2], [3, 4], [5, 6]]

flat_list = [item for sublist in nested_list for item in sublist]

print(flat_list)

99. Write a program to check all elements unique in list.

lst = [1, 2, 3, 4, 5]

if len(lst) == len(set(lst)):

print("All elements are unique")

else:

print("Duplicates found")

100. Write a simple data analytics workflow program.

import pandas as pd

# Step 1: Load data

df = pd.read_csv("[Link]")

# Step 2: View data

print([Link]())

# Step 3: Handle missing values

df = [Link]()

# Step 4: Basic analysis

print([Link]())

# Step 5: Filter data

filtered = df[df["Age"] > 25]

# Step 6: Save processed data

filtered.to_csv("processed_data.csv", index=False)

print("Data analysis completed successfully!")

SQL INTERVIEW QUESTIONS WITH ANSWERS

1. What is SQL and why is it used?

SQL (Structured Query Language) is a programming language used to interact with
databases. It is used to store, retrieve, update, and manage data efficiently. SQL is widely
used in data analytics to extract insights from structured data stored in databases.

2. What are the different types of SQL commands?

SQL commands are mainly divided into categories such as DDL (Data Definition
Language), DML (Data Manipulation Language), DCL (Data Control Language), and TCL
(Transaction Control Language). Each type is used for a specific purpose like creating
tables, inserting data, or managing transactions.

3. What is the difference between DDL, DML, DCL, and TCL?

DDL is used to define database structure (CREATE, ALTER, DROP).
DML is used to manipulate data (INSERT, UPDATE, DELETE).
DCL is used for access control (GRANT, REVOKE).
TCL is used to manage transactions (COMMIT, ROLLBACK).

4. What is a database?
A database is an organized collection of data that is stored electronically. It allows users to
easily access, manage, and update data. Databases are essential in applications like
banking, e-commerce, and data analytics.

5. What is a table in SQL?

A table is a structured format used to store data in rows and columns. Each row represents
a record, and each column represents a field or attribute. Tables are the basic building
blocks of a database.

6. What is a primary key?

A primary key is a column or a set of columns that uniquely identifies each record in a
table. It cannot contain NULL values and must be unique for every row. It ensures data
integrity.

7. What is a foreign key?

A foreign key is a column that creates a relationship between two tables. It refers to the
primary key of another table. It helps maintain referential integrity between related tables.

8. What is the difference between primary key and unique key?

A primary key uniquely identifies each record and does not allow NULL values.
A unique key also ensures uniqueness but can allow one NULL value (depending on the
database system).

9. What is a composite key?

A composite key is a combination of two or more columns used together to uniquely
identify a record. It is used when a single column is not sufficient to ensure uniqueness.

10. What is normalization?

Normalization is the process of organizing data in a database to reduce redundancy and
improve data integrity. It divides large tables into smaller related tables and establishes
relationships between them.

11. What are the different normal forms?

Normal forms are rules used in database normalization to reduce redundancy. The main types are First
Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF). Each level removes
specific types of dependency and duplication. Higher normal forms improve data consistency and integrity.

12. What is denormalization?

Denormalization is the process of combining tables to improve read performance. It reduces the number of
joins required in queries. Although it increases redundancy, it is useful in data warehouses and reporting
systems where fast retrieval is important.
13. What is the difference between WHERE and HAVING clause?

The WHERE clause is used to filter rows before grouping, while the HAVING clause is used to filter grouped
data after aggregation. WHERE works with individual rows, whereas HAVING works with aggregate
functions like SUM or COUNT.

14. What is the difference between DELETE, TRUNCATE, and DROP?

DELETE removes specific rows and can be rolled back.

TRUNCATE removes all rows quickly and cannot be rolled back in most cases.
DROP deletes the entire table structure along with its data permanently.

15. What is the difference between CHAR and VARCHAR?

CHAR is a fixed-length data type, meaning it always stores the defined number of characters. VARCHAR is
variable-length and stores only the actual data. VARCHAR is more memory-efficient and commonly used in
real-world applications.

16. What is NULL value in SQL?

NULL represents a missing or unknown value in a database. It is not equal to zero or an empty string. Special
conditions like IS NULL or IS NOT NULL are used to check NULL values.

17. What is the use of DISTINCT keyword?

The DISTINCT keyword is used to remove duplicate values from the result set. It ensures that only unique
values are returned in the output. It is commonly used in data analysis to identify unique entries.

18. What are aggregate functions?

Aggregate functions perform calculations on multiple rows and return a single result. Common functions
include COUNT(), SUM(), AVG(), MIN(), and MAX(). They are widely used in reporting and data analysis.

19. What is GROUP BY clause?

The GROUP BY clause is used to group rows that have the same values in specified columns. It is often used
with aggregate functions to perform calculations on grouped data, such as total sales per department.

20. What is ORDER BY clause?

The ORDER BY clause is used to sort the result set in ascending or descending order. By default, it sorts data
in ascending order. It helps in organizing query results for better readability.

21. What is a JOIN?

A JOIN is used to combine data from two or more tables based on a related column between them. It helps
retrieve meaningful information by connecting tables using common fields such as primary and foreign keys.
Joins are widely used in data analytics for relational data analysis.

22. What are the different types of joins?

The main types of joins are INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN. Each join
type determines how data from multiple tables is combined based on matching and non-matching records.

23. What is INNER JOIN?

INNER JOIN returns only the records that have matching values in both tables. If there is no match, the row
is not included in the result. It is the most commonly used join in SQL.

24. What is LEFT JOIN?

LEFT JOIN returns all records from the left table and the matching records from the right table. If there is no
match, NULL values are returned for columns from the right table.

25. What is RIGHT JOIN?

RIGHT JOIN returns all records from the right table and the matching records from the left table. If there is
no match, NULL values are returned for columns from the left table.

26. What is FULL OUTER JOIN?

FULL OUTER JOIN returns all records from both tables. If there is no match, NULL values are returned for
the missing side. It combines the results of both LEFT JOIN and RIGHT JOIN.

27. What is a self join?

A self join is a join where a table is joined with itself. It is useful when comparing rows within the same
table, such as finding employees and their managers.

28. What is a cross join?

A cross join returns the Cartesian product of two tables, meaning every row from the first table is combined
with every row from the second table. It can result in a large number of rows.

29. What is a subquery?

A subquery is a query nested inside another SQL query. It is used to perform operations that depend on the
result of another query. Subqueries can be used in SELECT, WHERE, or FROM clauses.

30. What is the difference between subquery and join?

A join combines data from multiple tables into a single result set.
A subquery is a query inside another query used to filter or compute results.
Joins are generally faster for large datasets, while subqueries are useful for simpler or nested operations.

31. What is a correlated subquery?

A correlated subquery is a subquery that depends on the outer query for its values. It is executed once for
each row processed by the outer query. This makes it slower than normal subqueries but useful for row-wise
comparisons.

32. What is an index?

An index is a database object used to improve the speed of data retrieval operations. It works like an index in
a book, helping the database find data quickly without scanning the entire table.

33. Why are indexes used?

Indexes are used to improve query performance and reduce the time taken to retrieve data. They are
especially useful for large tables where searching without an index would be slow.

34. What is a view?

A view is a virtual table created based on the result of a SQL query. It does not store data itself but displays
data from one or more tables. Views are used for security and simplifying complex queries.

35. What is the difference between view and table?

A table physically stores data in the database, while a view is a virtual representation of data. Changes in the
base table are reflected in the view automatically.

36. What is a stored procedure?

A stored procedure is a precompiled collection of SQL statements stored in the database. It can be executed
whenever needed and helps improve performance and reusability of code.

37. What is a function in SQL?

A function is a reusable SQL block that performs a specific task and returns a value. It can be used in queries
to process and transform data.

38. What is the difference between function and stored procedure?

A function must return a value and can be used inside queries, while a stored procedure may or may not
return a value and is executed independently. Functions are mainly used for calculations, whereas procedures
handle operations.
39. What is ACID property?

ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure reliable
transaction processing in a database and maintain data integrity even in case of failures.

40. What is a transaction?

A transaction is a sequence of SQL operations executed as a single unit. It ensures that either all operations
are completed successfully or none are applied, maintaining database consistency.

41. What is COMMIT and ROLLBACK?

COMMIT is used to permanently save all changes made during a transaction in the database. Once
committed, the changes cannot be undone.
ROLLBACK is used to undo changes made during a transaction before they are committed. It helps in
maintaining data integrity in case of errors.

42. What is the use of CASE statement?

The CASE statement is used to apply conditional logic in SQL queries, similar to if-else conditions in
programming. It allows you to return different values based on specific conditions.

Example use: Categorizing data such as “High”, “Medium”, or “Low” salary.

43. What is the difference between UNION and UNION ALL?

UNION combines results from multiple queries and removes duplicate records.
UNION ALL also combines results but includes all duplicates, making it faster than UNION.

44. What is a constraint?

A constraint is a rule applied to a table column to ensure data accuracy and integrity. It restricts the type of
data that can be inserted into the table.

45. What are the different types of constraints?

Common constraints include:

 PRIMARY KEY

 FOREIGN KEY

 UNIQUE

 NOT NULL

 CHECK

 DEFAULT
These constraints help maintain consistency and validity of data.

46. What is default constraint?

A DEFAULT constraint assigns a default value to a column when no value is provided during insertion. It
ensures that the column always has a value.

47. What is NOT NULL constraint?

The NOT NULL constraint ensures that a column cannot have NULL (empty) values. It is used when a field
must always contain data.

48. What is referential integrity?

Referential integrity ensures that relationships between tables remain consistent. It ensures that a foreign key
value always refers to a valid primary key in another table.

49. What is schema?

A schema is the logical structure of a database that defines how data is organized. It includes tables,
relationships, constraints, and other elements.

50. Explain a real-time use case of SQL in data analytics.

In a real-world scenario, SQL is used to analyze business data such as sales or customer behavior. For
example, a company can use SQL queries to find top-selling products, customer purchase trends, and
monthly revenue, helping management make data-driven decisions.

51. Write a query to display all records from a table.

This query is used to fetch all data from a table without any filtering condition.

SELECT * FROM table_name;

52. Write a query to select specific columns from a table.

This query is used when only required columns need to be retrieved instead of all columns.

SELECT column1, column2 FROM table_name;

53. Write a query to remove duplicate rows from a table.

The DISTINCT keyword is used to remove duplicate records and return only unique values.

SELECT DISTINCT column_name FROM table_name;

54. Write a query to filter records using WHERE clause.

The WHERE clause is used to filter records based on a specific condition.

SELECT * FROM table_name

WHERE condition;

55. Write a query to find unique values in a column.

This query returns only unique values from a specific column.

SELECT DISTINCT column_name FROM table_name;

56. Write a query to sort records in ascending order.

The ORDER BY clause is used to sort data in ascending order (default).

SELECT * FROM table_name

ORDER BY column_name ASC;

57. Write a query to sort records in descending order.

This query sorts the records in descending order.

SELECT * FROM table_name

ORDER BY column_name DESC;

58. Write a query to count number of rows in a table.

The COUNT() function is used to count total rows in a table.

SELECT COUNT(*) FROM table_name;

59. Write a query to find maximum and minimum values in a column.

Aggregate functions MAX() and MIN() are used to find highest and lowest values.

SELECT MAX(column_name), MIN(column_name)

FROM table_name;

60. Write a query to find average value of a column.

The AVG() function calculates the average value of a numeric column.

SELECT AVG(column_name)

FROM table_name;
61. Write a query to use GROUP BY clause.

The GROUP BY clause is used to group rows that have the same values in specified columns. It is often used
with aggregate functions like COUNT, SUM, or AVG.

SELECT department, COUNT(*) AS total_employees

FROM employees

GROUP BY department;

62. Write a query to use HAVING clause.

The HAVING clause is used to filter grouped data after applying GROUP BY. It works with aggregate
functions.

SELECT department, COUNT(*) AS total_employees

FROM employees

GROUP BY department

HAVING COUNT(*) > 5;

63. Write a query to find total salary department-wise.

This query calculates the total salary for each department using GROUP BY and SUM function.

SELECT department, SUM(salary) AS total_salary

FROM employees

GROUP BY department;

64. Write a query to join two tables using INNER JOIN.

INNER JOIN returns only matching records from both tables.

SELECT [Link], d.department_name

FROM employees e

INNER JOIN departments d

ON e.dept_id = d.dept_id;

65. Write a query to join two tables using LEFT JOIN.

LEFT JOIN returns all records from the left table and matching records from the right table.

SELECT [Link], d.department_name

FROM employees e

LEFT JOIN departments d

ON e.dept_id = d.dept_id;
66. Write a query to join two tables using RIGHT JOIN.

RIGHT JOIN returns all records from the right table and matching records from the left table.

SELECT [Link], d.department_name

FROM employees e

RIGHT JOIN departments d

ON e.dept_id = d.dept_id;

67. Write a query to join two tables using FULL OUTER JOIN.

FULL OUTER JOIN returns all records when there is a match in either left or right table.

SELECT [Link], d.department_name

FROM employees e

FULL OUTER JOIN departments d

ON e.dept_id = d.dept_id;

68. Write a query to find records present in both tables.

This can be achieved using INNER JOIN to get common records.

SELECT e.*

FROM employees e

INNER JOIN managers m

ON e.emp_id = m.emp_id;

69. Write a query to find records present in one table but not in another.

This can be done using LEFT JOIN with NULL condition.

SELECT e.*

FROM employees e

LEFT JOIN managers m

ON e.emp_id = m.emp_id

WHERE m.emp_id IS NULL;

70. Write a query to use subquery in SELECT clause.

A subquery inside SELECT is used to fetch additional calculated values.

SELECT name,
 salary,

(SELECT AVG(salary) FROM employees) AS avg_salary

FROM employees;

71. Write a query to find second highest salary.

SELECT MAX(salary) AS second_highest

FROM employees

WHERE salary < (SELECT MAX(salary) FROM employees);

Explanation:
This query first finds the highest salary using a subquery. Then it retrieves the maximum salary that is less
than the highest, which gives the second highest salary.

72. Write a query to find nth highest salary.

SELECT salary

FROM employees

ORDER BY salary DESC

LIMIT 1 OFFSET n-1;

Explanation:
This query sorts salaries in descending order and skips the first (n-1) records to fetch the nth highest salary.
Replace n with the desired rank.

73. Write a query to find employees with salary greater than average salary.

SELECT *

FROM employees

WHERE salary > (SELECT AVG(salary) FROM employees);

Explanation:
This query uses a subquery to calculate the average salary and then filters employees whose salary is greater
than the average.

74. Write a query using CASE statement.

SELECT name, salary,

CASE

WHEN salary > 50000 THEN 'High'

WHEN salary BETWEEN 30000 AND 50000 THEN 'Medium'

ELSE 'Low'
END AS salary_category

FROM employees;

Explanation:
The CASE statement works like an if-else condition. It categorizes employees based on their salary range.

75. Write a query to replace NULL values.

SELECT name, COALESCE(salary, 0) AS salary

FROM employees;

Explanation:
The COALESCE function replaces NULL values with a specified value (here, 0). It ensures no missing
values during analysis.

76. Write a query to find duplicate records.

SELECT name, COUNT(*)

FROM employees

GROUP BY name

HAVING COUNT(*) > 1;

Explanation:
This query groups records by name and counts occurrences. If the count is greater than 1, it means duplicates
exist.

77. Write a query to delete duplicate records.

DELETE FROM employees

WHERE id NOT IN (

SELECT MIN(id)

FROM employees

GROUP BY name

);

Explanation:
This query keeps only one record (minimum id) for each group and deletes the rest, effectively removing
duplicates.

78. Write a query to create a table.

CREATE TABLE employees (

id INT PRIMARY KEY,

 name VARCHAR(50),

salary INT

);

Explanation:
This query creates a new table named employees with columns id, name, and salary, and sets id as the
primary key.

79. Write a query to alter a table and add a column.

ALTER TABLE employees

ADD department VARCHAR(50);

Explanation:
This query modifies the existing table structure by adding a new column called department.

80. Write a query to drop a column from a table.

ALTER TABLE employees

DROP COLUMN department;

Explanation:
This query removes the specified column from the table permanently.

81. Write a query to rename a column.

To rename a column, we use the ALTER TABLE statement along with RENAME COLUMN. This helps
modify the structure of an existing table without affecting the data.

ALTER TABLE employees

RENAME COLUMN old_name TO new_name;

82. Write a query to insert records into a table.

The INSERT INTO statement is used to add new records into a table. You must provide values for all
required columns or specify column names.

INSERT INTO employees (id, name, salary)

VALUES (1, 'John', 50000);

83. Write a query to update records in a table.

The UPDATE statement is used to modify existing records in a table. It is usually combined with the
WHERE clause to update specific rows.

UPDATE employees
SET salary = 60000

WHERE id = 1;

84. Write a query to delete records from a table.

The DELETE statement removes specific records from a table. It is important to use a WHERE clause to
avoid deleting all records.

DELETE FROM employees

WHERE id = 1;

85. Write a query to truncate a table.

TRUNCATE is used to remove all records from a table quickly. It is faster than DELETE and cannot be
rolled back in most cases.

TRUNCATE TABLE employees;

86. Write a query to create a primary key.

A primary key is created to uniquely identify each record in a table. It ensures that no duplicate or NULL
values exist in that column.

CREATE TABLE employees (

id INT PRIMARY KEY,

name VARCHAR(50)

);

87. Write a query to create a foreign key.

A foreign key is used to establish a relationship between two tables. It references the primary key of another
table.

CREATE TABLE orders (

order_id INT,

emp_id INT,

FOREIGN KEY (emp_id) REFERENCES employees(id)

);

88. Write a query to create an index.

An index is created to improve the speed of data retrieval operations. It works like an index in a book.

CREATE INDEX idx_name

ON employees(name);

89. Write a query to create a view.

A view is a virtual table created from a SELECT query. It helps simplify complex queries and improves
security.

CREATE VIEW employee_view AS

SELECT name, salary

FROM employees;

90. Write a query to use DISTINCT keyword.

The DISTINCT keyword is used to remove duplicate values and return only unique records from a column.

SELECT DISTINCT department

FROM employees;

91. Write a query to use BETWEEN operator.

The BETWEEN operator is used to filter records within a specific range. It includes both the starting and
ending values.

SELECT *

FROM employees

WHERE salary BETWEEN 30000 AND 60000;

92. Write a query to use IN operator.

The IN operator is used to match values against a list of multiple values. It simplifies multiple OR conditions.

SELECT *

FROM employees

WHERE department IN ('HR', 'IT', 'Finance');

93. Write a query to use LIKE operator.

The LIKE operator is used for pattern matching in text data using wildcards such as % (any number of
characters) and _ (single character).

SELECT *

FROM employees

WHERE name LIKE 'A%';

94. Write a query to fetch top 5 records.

To fetch top records, we use LIMIT (MySQL/PostgreSQL) or TOP (SQL Server).

SELECT *

FROM employees

LIMIT 5;

95. Write a query to find records between two dates.

This query retrieves records within a specific date range using BETWEEN.

SELECT *

FROM orders

WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

96. Write a query to get current date.

This query returns the current system date.

SELECT CURRENT_DATE;

97. Write a query to calculate total rows using COUNT.

The COUNT function is used to count the number of rows in a table.

SELECT COUNT(*) AS total_rows

FROM employees;

98. Write a query to use UNION.

UNION is used to combine results of two queries and removes duplicate rows.

SELECT name FROM employees

UNION

SELECT name FROM managers;

99. Write a query to use UNION ALL.

UNION ALL combines results but includes duplicate rows.

SELECT name FROM employees

UNION ALL

SELECT name FROM managers;

100. Write a query to perform transaction using COMMIT and ROLLBACK.

Transactions ensure data consistency. COMMIT saves changes, while ROLLBACK undoes them.

BEGIN;

UPDATE employees

SET salary = salary + 5000

WHERE id = 1;

-- If everything is correct

COMMIT;

-- If there is an error

ROLLBACK;