SQL Data Science Interview Questions 2024
SQL Data Science Interview Questions 2024
1.Explain the difference between SQL and MySQL.
2.What is an RDBMS (Relational Database Management System) & how it
works ?
3.Differentiate between a database and a DBMS.
4.Describe the concept of data independence in DBMS
5.Differentiate between DBMS and RDBMS
6.Is Mysql platform dependent?
7.What is structured and unstructured data ?
8.Query to create a database .
What are the data types in mysql?
9.Differentiate between char and Varchar ?
10.What is the default length of int ?
11.How to create a Table
12.How to Insert the data in a specific column in a table ?
13.How do you insert the data into specific column in a table
14.What is approximate size of a varchar datatype in organization ?
15.How does the size defines length of the column ?
16.What is normalization in mysql ?
17.What types of data we can handle in mysql ?
18.What were the data sources available in your organization?
20.How is the hostname generated
Command types and DateTime
1.How do you retrieve the current date and time in MySQL?
2.How can you add or subtract days, months, or years from a date in MySQL?
3.How do you find the last day of the month in MySQL?
4.What is the approach to handling time zones in MySQL to ensure consistency
across different regions?
5.When working with large datasets, what strategies can you employ to
optimize queries involving date and time operations for efficient execution?
6.How do you extract data for the past 36 hours in MySQL?
7.Can you explain the differences among the DELETE, DROP, and TRUNCATE
statements in SQL?
8.What techniques can you use to optimize Data Manipulation Language (DML)
statements for improved performance in a database?
9.Is it possible to rollback an executed query in MySQL?
10.What are savepoints in MySQL and how are they used?
11.How can transactions be rolled back in MySQL?
12.How do you create a user in MySQL?
13.What steps are involved in granting access to a specific user in MySQL?
Operators
1.What is the purpose of the LIKE operator in SQL, and how is it used?
2.Explain the difference between the UNION and UNION ALL operators in SQL. When
would you use each?
3.How does the DISTINCT operator work in SQL, and when might you use it in a query?
4.Describe the functionality of the BETWEEN operator in SQL. Provide an example of
how it can be used in a query.
5.What is the purpose of the IN operator in SQL? Provide a scenario where using the IN
operator would be beneficial.
6.Select departments whose numbers are between 1001 and 1002 without using the
BETWEEN operator
7.Select departments whose numbers are between 1001 and 1002 without using the
BETWEEN operator
8.Retrieve male employees located in Bangalore or Mumbai.
9.Explain the significance of the IS NULL and IS NOT NULL operators in SQL. When
would you use them in a query?
Key Constrains
1.What are constraints in a database, and why are they important?
2.Explain the difference between primary key and unique key constraints.
3.How does a foreign key constraint ensure referential integrity in a database?
4.Can you name and explain some common types of constraints supported by most
relational database management systems?
5.When would you use a CHECK constraint? Can you provide an example?
6.What is the purpose of a NOT NULL constraint, and when should it be used?
7.How does adding a constraint affect database performance?
8.Discuss the role of constraints in maintaining data integrity.
9.What are the advantages and disadvantages of using constraints in database design?
10.Can constraints be disabled or dropped once they are defined? What are the
implications of doing so?
11.Can we add multiple primary keys in single table
12.To join 2 or more tables do we need primary and foreign key relation ?
Aggregate Functions
1.How do MAX and MIN functions work on VARCHAR type of data in MySQL?
2.Name some commonly used aggregate functions in MySQL.
3.Explain the difference between COUNT(*) and COUNT(column_name) in MySQL.
4.What is the difference between MIN() and MAX() functions?
5.How do you use the GROUP BY clause in conjunction with aggregate functions?
6.Explain the HAVING clause and its use with aggregate functions.
7.What is the purpose of the DISTINCT keyword with aggregate functions in MySQL?
8.How do you handle NULL values with aggregate functions in MySQL?
9.What are the limitations of using aggregate functions in MySQL?
10.Can you nest aggregate functions within each other in MySQL? If yes, provide an
example
11.How do you order the results of an aggregate function in MySQL?
12.Explain the difference between the WHERE clause and the HAVING clause when
filtering results with aggregate functions.
13.Write a query to count the number of employees who are living in Pune from the
employee table.
14.Write a query to count the number of employees whose salary is greater than 10 LPA
Clauses
â—‹What is the WHERE clause in MySQL and how is it used?
â—‹ Explain the difference between the WHERE and HAVING clauses in MySQL.
â—‹ How does the ORDER BY clause work in MySQL?
â—‹ How does the ORDER BY clause works with varchar type of data ?
â—‹ What is the purpose of the LIMIT clause in MySQL?
â—‹ Explain the GROUP BY clause in MySQL and provide an example.
â—‹ What does the JOIN clause do in MySQL? Provide examples of different types of
JOINs.
â—‹ What is the purpose of the ON clause in a JOIN statement in MySQL?
â—‹ What is the purpose of the USING clause in MySQL JOINs?
â—‹ Explain the functionality of the DISTINCT keyword in MySQL and how it relates to the
SELECT clause.
â—‹ What does the AS keyword do in MySQL and how is it used?
â—‹ Explain the functionality of the LIKE clause in MySQL and provide examples of its
usage.
â—‹ How does the IN clause work in MySQL and what is its purpose?
â—‹ What is the purpose of the EXISTS clause in MySQL and how is it used?
â—‹ How does the OFFSET clause work in MySQL and what is its relationship with the
LIMIT clause?
â—‹ Select count emp from a each city who have salary is more than 50k by location
â—‹ Select total emp present in dept whose salary is greater than 50k
â—‹ Name of department in which no of employee more than 2
â—‹ Name of department whose avg(salary) is greater than 50000;
â—‹ Write a query to find the employees whose salary is greater than the average salary of
their department.
â—‹ Write a query to retrieve the historical changes in employee salaries over time.
â—‹ Implement a query to rank employees based on their salary within each department.
â—‹ Calculate the moving average of employee salaries over a rolling 3-month period.
â—‹ retrieve the data of the top 3 salary earners from each department while including all
other columns in the result set
â—‹ Utilize window frame clauses to compute moving aggregates, considering only the
last 3 months of data for each employee.
â—‹ Write a query to find the top 3 highest-paid employees in each department,
considering ties.
â—‹ Write a query to calculate the number of employees who joined each year, grouped by
their gender.
â—‹ Implement a query to find the maximum salary for employees with more than 5 years
of experience.
â—‹ find the difference in salary between each employee and the previous employee in
terms of salary:
â—‹ difference in salary between each employee and the next employee in terms of salary:
â—‹ How do LIMIT and OFFSET affect query performance, especially when dealing with
large datasets? Can you suggest alternative strategies to implement pagination
efficiently?
â—‹ In scenarios where pagination is required frequently, what optimization techniques
can be applied to improve query performance and minimize resource consumption?
â—‹ Discuss the performance challenges associated with using large offsets in queries
with OFFSET. How can these challenges be mitigated?
â—‹ How to find second third max salary in two possible ways
