SQL Interview Questions! 🔥🚀


Basic SQL Interview Questions:

- What is SQL?

- What are the different types of SQL commands?

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

- What is the difference between SQL and MySQL?

- What is a primary key?

- What is a foreign key?

- What is a unique key?

- What is the difference between primary key and unique key?

- What is the difference between HAVING and WHERE?

- What are constraints in SQL? Name a few.

- What is the difference between CHAR and VARCHAR?

- What is Normalization? What are its types?

- What is Denormalization?

- What is an index in SQL?

- What are the different types of indexes?

- What is the difference between Clustered and Non-clustered indexes?

- What is an alias in SQL?

- What is the difference between DELETE and TRUNCATE?

- What is the difference between TRUNCATE and DROP?

- What is a view in SQL?


-------------------------------------

Intermediate SQL Interview Questions:

What is a self-join?

What is an inner join?

What is the difference between INNER JOIN and OUTER JOIN?

What are the types of OUTER JOIN?

What is a cross join?

What is a Cartesian join?

What is the difference between UNION and UNION ALL?

What is the difference between JOIN and UNION?

What is a stored procedure?

What is a trigger in SQL?

What are the different types of triggers?

What is the difference between HAVING and GROUP BY?

What are subqueries?

What are correlated subqueries?

What is an EXISTS clause in SQL?

What is the difference between EXISTS and IN?

What is a cursor in SQL?

What is the difference between OLTP and OLAP?

What are ACID properties in SQL?

What is normalization? Explain 1NF, 2NF, 3NF, and BCNF.

What is a composite key?

What is a surrogate key?

What is the use of the COALESCE function?

What is the difference between IS
NULL and IS NOT NULL?

What is partitioning in SQL?


-------------------------------------

Advanced SQL Interview Questions:

What are window functions in SQL?

What is CTE (Common Table Expression)?

What is the difference between TEMP TABLE and CTE?

What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?

What is a materialized view?

What is the difference between materialized views and normal views?

What is sharding in SQL?

What is the MERGE statement?

What is the JSON data type in SQL?

What is recursive CTE?

What is the difference between LEFT JOIN and LEFT OUTER JOIN?

How does indexing impact performance?

What is the difference between OLAP and OLTP?

What is ETL (Extract, Transform, Load)?

What are window functions? Explain LEAD, LAG, and NTILE.

What is a pivot table in SQL?

What is Dynamic SQL?

What is a NoSQL database? How is it different from SQL databases?

What is the difference between SQL and PL/SQL?

How to find the N-th highest salary in SQL?


-------------------------------------

Practical SQL Queries:

Find the second highest salary from an Employee table.

Find duplicate records in a table.

Write a SQL query to find the count of employees in each department.

Write a query to find employees who earn more than their managers.

Write a query to fetch the first three characters of a string.

Write a SQL query to swap two columns in a table without using a temporary table.

Write a query to find all employees who joined in the last 6 months.

Write a query to find the most repeated values in a column.

Write a query to delete duplicate rows from a table.

Write a SQL query to find all customers who made more than 5 purchases.



React ♥️ for more content like this 👍

Here you can find essential SQL Interview Resources👇
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Like this post if you need more 👍❤️

Hope it helps :)

📌10 intermediate-level SQL interview questions

1. How would you find the nth highest salary in a table?
2. What is the difference between JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN?
3. How would you calculate cumulative sum in SQL?
4. How do you identify duplicate records in a table?
5. Explain the concept of a window function and give examples.
6. How would you retrieve records between two dates in SQL?
7. What is the difference between UNION and UNION ALL?
8. How can you pivot data in SQL?
9. Explain the use of CASE statements in SQL.
10. How do you use common table expressions (CTEs)?

#sql

Most people learn SQL just enough to pull some data. But if you really understand it, you can analyze massive datasets without touching Excel or Python.

Here are 8 game-changing SQL concepts that will make you a data pro:

👇

1. Stop pulling raw data. Start pulling insights.

The biggest mistake? Running a query that gives you everything and then filtering it later.

Good analysts don’t pull raw data. They shape the data before it even reaches them.

2. “SELECT ” is a rookie move.

Pulling all columns is lazy and slow.

A pro only selects what they need.
✔️ Fewer columns = Faster queries
✔️ Less noise = Clearer insights

The more precise your query, the less time you waste cleaning data.

3. GROUP BY is your best friend.

You don’t need 100,000 rows of transactions. What you need is:
✔️ Sales per region
✔️ Average order size per customer
✔️ Number of signups per month

Grouping turns chaotic data into useful summaries.

4. Joins = Connecting the dots.

Your most important data is split across multiple tables.

Want to know how much each customer spent? You need to join:
✔️ Customer info
✔️ Order history
✔️ Payments

Joins = unlocking hidden insights.

5. Window functions will blow your mind.

They let you:
✔️ Rank customers by total purchases
✔️ Calculate rolling averages
✔️ Compare each row to the overall trend

It’s like pivot tables, but way more powerful.

6. CTEs will save you from spaghetti SQL.

Instead of writing a 50-line nested query, break it into steps.

CTEs (Common Table Expressions) make your SQL:
✔️ Easier to read
✔️ Easier to debug
✔️ Reusable

Good SQL is clean SQL.

7. Indexes = Speed.

If your queries take forever, your database is probably doing unnecessary work.

Indexes help databases find data faster.

If you work with large datasets, this is a game changer.

SQL isn’t just about pulling data. It’s about analyzing, transforming, and optimizing it.

Master these 7 concepts, and you’ll never look at SQL the same way again.

Join us on WhatsApp: https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Preparing for a SQL interview?

Focus on mastering these essential topics:

1. Joins: Get comfortable with inner, left, right, and outer joins.
Knowing when to use what kind of join is important!

2. Window Functions: Understand when to use
ROW_NUMBER, RANK(), DENSE_RANK(), LAG, and LEAD for complex analytical queries.

3. Query Execution Order: Know the sequence from FROM to
ORDER BY. This is crucial for writing efficient, error-free queries.

4. Common Table Expressions (CTEs): Use CTEs to simplify and structure complex queries for better readability.

5. Aggregations & Window Functions: Combine aggregate functions with window functions for in-depth data analysis.

6. Subqueries: Learn how to use subqueries effectively within main SQL statements for complex data manipulations.

7. Handling NULLs: Be adept at managing NULL values to ensure accurate data processing and avoid potential pitfalls.

8. Indexing: Understand how proper indexing can significantly boost query performance.

9. GROUP BY & HAVING: Master grouping data and filtering groups with HAVING to refine your query results.

10. String Manipulation Functions: Get familiar with string functions like CONCAT, SUBSTRING, and REPLACE to handle text data efficiently.

11. Set Operations: Know how to use UNION, INTERSECT, and EXCEPT to combine or compare result sets.

12. Optimizing Queries: Learn techniques to optimize your queries for performance, especially with large datasets.

If we master/ Practice in these topics we can track any SQL interviews..

Like this post if you need more 👍❤️

Hope it helps :)

Want to build your first AI agent?

Join a live hands-on session by GeeksforGeeks & Salesforce for working professionals

- Build with Agent Builder

- Assign real actions

- Get a free certificate of participation

Registeration link:👇
https://gfgcdn.com/tu/V4t/

The Only SQL You Actually Need For Your First Job DataAnalytics

The Learning Trap:
* Complex subqueries
* Advanced CTEs
* Recursive queries
* 100+ tutorials watched
* 0 practical experience

Reality Check:
75% of daily SQL tasks:
* Basic SELECT, FROM, WHERE
* JOINs
* GROUP BY
* ORDER BY
* Simple aggregations
* ROW_NUMBER

Like for detailed explanation ❤️

#sql

If you want to Excel at using the most used database language in the world, learn these powerful SQL features:

• Wildcards (%, _) – Flexible pattern matching
• Window Functions – ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG()
• Common Table Expressions (CTEs) – WITH for better readability
• Recursive Queries – Handle hierarchical data
• STRING Functions – LEFT(), RIGHT(), LEN(), TRIM(), UPPER(), LOWER()
• Date Functions – DATEDIFF(), DATEADD(), FORMAT()
• Pivot & Unpivot – Transform row data into columns
• Aggregate Functions – SUM(), AVG(), COUNT(), MIN(), MAX()
• Joins & Self Joins – Master INNER, LEFT, RIGHT, FULL, SELF JOIN
• Indexing – Speed up queries with CREATE INDEX

Like it if you need a complete tutorial on all these topics! 👍❤️

#sql

📊Here's a breakdown of SQL interview questions covering various topics:

🔺Basic SQL Concepts:
-Differentiate between SQL and NoSQL databases.
-List common data types in SQL.

🔺Querying:
-Retrieve all records from a table named "Customers."
-Contrast SELECT and SELECT DISTINCT.
-Explain the purpose of the WHERE clause.


🔺Joins:
-Describe types of joins (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN).
-Retrieve data from two tables using INNER JOIN.

🔺Aggregate Functions:
-Define aggregate functions and name a few.
-Calculate average, sum, and count of a column in SQL.

🔺Grouping and Filtering:
-Explain the GROUP BY clause and its use.
-Filter SQL query results using the HAVING clause.

🔺Subqueries:
-Define a subquery and provide an example.

🔺Indexes and Optimization:
-Discuss the importance of indexes in a database.
&Optimize a slow-running SQL query.

🔺Normalization and Data Integrity:
-Define database normalization and its significance.
-Enforce data integrity in a SQL database.

🔺Transactions:
-Define a SQL transaction and its purpose.
-Explain ACID properties in database transactions.

🔺Views and Stored Procedures:
-Define a database view and its use.
-Distinguish a stored procedure from a regular SQL query.

🔺Advanced SQL:
-Write a recursive SQL query and explain its use.
-Explain window functions in SQL.

✅👀These questions offer a comprehensive assessment of SQL knowledge, ranging from basics to advanced concepts.

❤️Like if you'd like answers in the next post! 👍

Here are some tricky🧩 SQL interview questions!

1. Find the second-highest salary in a table without using LIMIT or TOP.

2. Write a SQL query to find all employees who earn more than their managers.

3. Find the duplicate rows in a table without using GROUP BY.

4. Write a SQL query to find the top 10% of earners in a table.

5. Find the cumulative sum of a column in a table.

6. Write a SQL query to find all employees who have never taken a leave.

7. Find the difference between the current row and the next row in a table.

8. Write a SQL query to find all departments with more than one employee.

9. Find the maximum value of a column for each group without using GROUP BY.

10. Write a SQL query to find all employees who have taken more than 3 leaves in a month.

These questions are designed to test your SQL skills, including your ability to write efficient queries, think creatively, and solve complex problems.

Here are the answers to these questions:

1. SELECT MAX(salary) FROM table WHERE salary NOT IN (SELECT MAX(salary) FROM table)

2. SELECT e1.* FROM employees e1 JOIN employees e2 ON e1.manager_id = (link unavailable) WHERE e1.salary > e2.salary

3. SELECT * FROM table WHERE rowid IN (SELECT rowid FROM table GROUP BY column HAVING COUNT(*) > 1)

4. SELECT * FROM table WHERE salary > (SELECT PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY salary) FROM table)

5. SELECT column, SUM(column) OVER (ORDER BY rowid) FROM table

6. SELECT * FROM employees WHERE id NOT IN (SELECT employee_id FROM leaves)

7. SELECT *, column - LEAD(column) OVER (ORDER BY rowid) FROM table

8. SELECT department FROM employees GROUP BY department HAVING COUNT(*) > 1

9. SELECT MAX(column) FROM table WHERE column NOT IN (SELECT MAX(column) FROM table GROUP BY group_column)

Here you can find essential SQL Interview Resources👇
https://t.iss.one/mysqldata

Like this post if you need more 👍❤️

Hope it helps :)

Roadmap to master SQL:

📂 *Basic SQL Concepts*
∟📂 Understand Databases & Tables
∟📂 Learn SQL Syntax & Structure
∟📂 Learn Data Types in SQL
∟📂 Learn Basic SELECT Queries
∟📂 Learn WHERE Clause for Filtering Data
∟📂 Learn ORDER BY for Sorting Data

📂 *Advanced SQL Queries*
∟📂 Learn JOINs (INNER, LEFT, RIGHT, FULL, SELF)
∟📂 Learn Aggregation Functions (SUM, AVG, COUNT, MIN, MAX)
∟📂 Learn GROUP BY and HAVING Clauses
∟📂 Learn Subqueries (Nested Queries)
∟📂 Learn UNION and INTERSECT
∟📂 Learn LIKE, IN, and BETWEEN Operators

📂 *Advanced Data Manipulation*
∟📂 Learn Data Manipulation (INSERT, UPDATE, DELETE)
∟📂 Learn Data Constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL)
∟📂 Learn Normalization & Denormalization
∟📂 Learn Transactions & COMMIT/ROLLBACK

📂 *Performance Optimization*
∟📂 Learn Indexing
∟📂 Learn Query Optimization Techniques
∟📂 Learn EXPLAIN Plan

📂 *Common SQL Functions*
∟📂 Learn Date & Time Functions
∟📂 Learn String Functions (CONCAT, SUBSTRING, TRIM, etc.)
∟📂 Learn Mathematical Functions
∟📂 Learn Window Functions (ROW_NUMBER, RANK, PARTITION BY)

📂 *Working with Views and Stored Procedures*
∟📂 Learn Creating and Using Views
∟📂 Learn Creating and Using Stored Procedures
∟📂 Learn Triggers and Functions

📂 *Build Projects*
∟📂 Create Data Analytics Reports using SQL
∟📂 Build a Database from Scratch
∟📂 Work on Data Cleaning and Transformation Projects

📂 ✅ *Apply for Jobs*
∟📂 Apply for Data Analyst Roles
∟📂 Highlight SQL Skills & Projects in Resume

React ❤️ for detailed explanation of each topic

Data Analyst Roadmap: https://t.iss.one/sqlspecialist/1414

Data Analyst Jobs: https://whatsapp.com/channel/0029Vaxjq5a4dTnKNrdeiZ0J

For all resources and cheat sheets, check out our Telegram channel
👇👇
https://t.iss.one/mysqldata

Hope it helps :)

Quick Recap of SQL Concepts

1️⃣ FROM clause: Specifies the tables from which data will be retrieved.
2️⃣ WHERE clause: Filters rows based on specified conditions.
3️⃣ GROUP BY clause: Groups rows that have the same values into summary rows.
4️⃣ HAVING clause: Filters groups based on specified conditions.
5️⃣ SELECT clause: Specifies the columns to be retrieved.
6️⃣ WINDOW functions: Functions that perform calculations across a set of table rows.
7️⃣ AGGREGATE functions: Functions like COUNT, SUM, AVG that perform calculations on a set of values.
8️⃣ UNION / UNION ALL: Combines the result sets of multiple SELECT statements.
9️⃣ ORDER BY clause: Sorts the result set based on specified columns.
🔟 LIMIT / OFFSET (or FETCH / OFFSET in some databases): Controls the number of rows returned and starting point for retrieval.

🔅 Most important SQL commands

SQL Quick Notes to refresh your concepts

If you are trying to transition into the data analytics domain and getting started with SQL, focus on the most useful concept that will help you solve the majority of the problems, and then try to learn the rest of the topics:

👉🏻 Basic Aggregation function:
1️⃣ AVG
2️⃣ COUNT
3️⃣ SUM
4️⃣ MIN
5️⃣ MAX

👉🏻 JOINS
1️⃣ Left
2️⃣ Inner
3️⃣ Self (Important, Practice questions on self join)

👉🏻 Windows Function (Important)
1️⃣ Learn how partitioning works
2️⃣ Learn the different use cases where Ranking/Numbering Functions are used? ( ROW_NUMBER,RANK, DENSE_RANK, NTILE)
3️⃣ Use Cases of LEAD & LAG functions
4️⃣ Use cases of Aggregate window functions

👉🏻 GROUP BY
👉🏻 WHERE vs HAVING
👉🏻 CASE STATEMENT
👉🏻 UNION vs Union ALL
👉🏻 LOGICAL OPERATORS

Other Commonly used functions:
👉🏻 IFNULL
👉🏻 COALESCE
👉🏻 ROUND
👉🏻 Working with Date Functions
1️⃣ EXTRACTING YEAR/MONTH/WEEK/DAY
2️⃣ Calculating date differences

👉🏻CTE
👉🏻Views & Triggers (optional)

Here is an amazing resources to learn & practice SQL: https://bit.ly/3FxxKPz

Share with credits: https://t.iss.one/sqlspecialist

Hope it helps :)

SQL is one of the core languages used in data science, powering everything from quick data retrieval to complex deep dive analysis. Whether you're a seasoned data scientist or just starting out, mastering SQL can boost your ability to analyze data, create robust pipelines, and deliver actionable insights.

Let’s dive into a comprehensive guide on SQL for Data Science!

I have broken it down into three key sections to help you:

𝟭. 𝗦𝗤𝗟 𝗖𝗼𝗻𝗰𝗲𝗽𝘁𝘀:
Get a handle on the essentials -> SELECT statements, filtering, aggregations, joins, window functions, and more.

𝟮. 𝗦𝗤𝗟 𝗶𝗻 𝗗𝗮𝘆-𝘁𝗼-𝗗𝗮𝘆 𝗗𝗮𝘁𝗮 𝗦𝗰𝗶𝗲𝗻𝗰𝗲:
See how SQL fits into the daily data science workflow. From quick data queries and deep-dive analysis to building pipelines and dashboards, SQL is really useful for data scientists, especially for product data scientists.

𝟯. 𝗗𝗮𝘁𝗮 𝗦𝗰𝗶𝗲𝗻𝗰𝗲 𝗦𝗤𝗟 𝗜𝗻𝘁𝗲𝗿𝘃𝗶𝗲𝘄𝘀:
Learn what interviewers look for in terms of technical skills, design and engineering expertise, communication abilities, and the importance of speed and accuracy.

Here you can find essential SQL Interview Resources👇
https://whatsapp.com/channel/0029VanC5rODzgT6TiTGoa1v

Like this post if you need more 👍❤️

Hope it helps :)

#sql

Most Asked SQL Interview Questions at MAANG Companies🔥🔥

Preparing for an SQL Interview at MAANG Companies? Here are some crucial SQL Questions you should be ready to tackle:

1. How do you retrieve all columns from a table?

SELECT * FROM table_name;

2. What SQL statement is used to filter records?

SELECT * FROM table_name
WHERE condition;

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

3. How can you join multiple tables? Describe different types of JOINs.

SELECT columns
FROM table1
JOIN table2 ON table1.column = table2.column
JOIN table3 ON table2.column = table3.column;

Types of JOINs:

1. INNER JOIN: Returns records with matching values in both tables

SELECT * FROM table1
INNER JOIN table2 ON table1.column = table2.column;

2. LEFT JOIN: Returns all records from the left table & matched records from the right table. Unmatched records will have NULL values.

SELECT * FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

3. RIGHT JOIN: Returns all records from the right table & matched records from the left table. Unmatched records will have NULL values.

SELECT * FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

4. FULL JOIN: Returns records when there is a match in either left or right table. Unmatched records will have NULL values.

SELECT * FROM table1
FULL JOIN table2 ON table1.column = table2.column;

4. What is the difference between WHERE & HAVING clauses?

WHERE: Filters records before any groupings are made.

SELECT * FROM table_name
WHERE condition;

HAVING: Filters records after groupings are made.

SELECT column, COUNT(*)
FROM table_name
GROUP BY column
HAVING COUNT(*) > value;

5. How do you calculate average, sum, minimum & maximum values in a column?

Average: SELECT AVG(column_name) FROM table_name;

Sum: SELECT SUM(column_name) FROM table_name;

Minimum: SELECT MIN(column_name) FROM table_name;

Maximum: SELECT MAX(column_name) FROM table_name;

Here you can find essential SQL Interview Resources👇
https://t.iss.one/mysqldata

Like this post if you need more 👍❤️

Hope it helps :)

We have the Key to unlock AI-Powered Data Skills!

We have got some news for College grads & pros:

Level up with PW Skills' Data Analytics & Data Science with Gen AI course!

✅ Real-world projects
✅ Professional instructors
✅ Flexible learning
✅ Job Assistance

Ready for a data career boost? ➡️
Click Here for Data Science with Generative AI Course:

https://shorturl.at/j4lTD

Click Here for Data Analytics Course:
https://shorturl.at/7nrE5

Quick Recap of SQL Concepts

1️⃣ FROM clause: Specifies the tables from which data will be retrieved.
2️⃣ WHERE clause: Filters rows based on specified conditions.
3️⃣ GROUP BY clause: Groups rows that have the same values into summary rows.
4️⃣ HAVING clause: Filters groups based on specified conditions.
5️⃣ SELECT clause: Specifies the columns to be retrieved.
6️⃣ WINDOW functions: Functions that perform calculations across a set of table rows.
7️⃣ AGGREGATE functions: Functions like COUNT, SUM, AVG that perform calculations on a set of values.
8️⃣ UNION / UNION ALL: Combines the result sets of multiple SELECT statements.
9️⃣ ORDER BY clause: Sorts the result set based on specified columns.
🔟 LIMIT / OFFSET (or FETCH / OFFSET in some databases): Controls the number of rows returned and starting point for retrieval.

SQL Essentials for Data Analysts

SQL interview questions with answers

1. How would you find duplicate records in SQL?

You can find duplicate records using the GROUP BY clause along with the HAVING clause. For example:

SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;



2. What are various types of SQL joins?

INNER JOIN: Returns only the rows where there is a match in both tables.

LEFT JOIN (LEFT OUTER JOIN): Returns all records from the left table and matched records from the right table; if no match, returns NULL from the right table.

RIGHT JOIN (RIGHT OUTER JOIN): Returns all records from the right table and matched records from the left table; if no match, returns NULL from the left table.

FULL JOIN (FULL OUTER JOIN): Returns all records when there is a match in either the left or right table. Non-matching rows are filled with NULL.

CROSS JOIN: Returns the Cartesian product of the two tables, i.e., all possible combinations of rows.

SELF JOIN: Joins a table with itself.



3. What is a trigger in SQL?

A trigger is a stored procedure that automatically executes or "fires" when an event (such as an insert, update, or delete) occurs on a table or view. Triggers are used to enforce business rules, validations, and logging.



4. What are different DDL, DML commands in SQL?

DDL (Data Definition Language): Includes commands like CREATE, ALTER, DROP, and TRUNCATE that define or modify database structures.

DML (Data Manipulation Language): Includes commands like INSERT, UPDATE, DELETE, and SELECT that manage data within schema objects.



5. What is the difference between Delete, Drop and Truncate?

DELETE: Removes rows from a table based on a condition. It is a DML command and can be rolled back.

DROP: Deletes a table (or other database object) and all its data. It is a DDL command and cannot be rolled back.

TRUNCATE: Removes all rows from a table, but the table structure remains. It is faster than DELETE and is a DDL command. It cannot be rolled back in many systems.



6. What is the difference between Union and Union all?

UNION: Combines the result sets of two or more queries and removes duplicates.

UNION ALL: Combines the result sets of two or more queries without removing duplicates.



7. Which command gives unique values?

The DISTINCT keyword is used to return unique values in a result set. Example:

SELECT DISTINCT column_name FROM table_name;



8. What is the difference between Where and Having Clause?

WHERE: Filters rows before any groupings are made (used with SELECT, UPDATE, DELETE).

HAVING: Filters groups after the GROUP BY operation (used with GROUP BY).



9. Give the execution order of keywords in SQL?

The typical execution order is:

1. FROM


2. JOIN


3. WHERE


4. GROUP BY


5. HAVING


6. SELECT


7. DISTINCT


8. ORDER BY


9. LIMIT/OFFSET





10. What is the difference between IN and BETWEEN Operator?

IN: Used to check if a value is within a set of values. Example:

SELECT * FROM table WHERE column_name IN (value1, value2, value3);

BETWEEN: Used to filter the result set within a specific range. Example:

SELECT * FROM table WHERE column_name BETWEEN value1 AND value2;



11. What is primary and Foreign key?

Primary Key: A column (or set of columns) in a table that uniquely identifies each row in the table. It cannot have NULL values.

Foreign Key: A column (or set of columns) in one table that refers to the primary key in another table, establishing a relationship between the two tables.



12. What is an aggregate function?

Aggregate functions perform a calculation on a set of values and return a single value. Examples include:

COUNT(): Counts the number of rows.

SUM(): Returns the sum of a numeric column.

AVG(): Returns the average value.

MIN() and MAX(): Return the minimum and maximum values.




13. What is the difference between Rank and Dense Rank?

RANK(): Assigns a rank to each row, with gaps in the ranking where there are ties.

DENSE_RANK(): Similar to RANK(), but does not leave gaps in the ranking when there are ties.

14. List the ACID Properties and explain what they are?

Atomicity: Ensures that a transaction is all-or-nothing. Either all operations are performed, or none are.

Consistency: Ensures that a transaction takes the database from one valid state to another.

Isolation: Ensures that operations of one transaction are isolated from others.

Durability: Ensures that once a transaction is committed, it will remain in the database, even in case of a system crash.



15. What is the difference between % and _ in the LIKE operator?

%: Represents zero or more characters.

_: Represents exactly one character.



16. What does CTE stand for?

CTE stands for Common Table Expression. It is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.



17. What is a database? What is DBMS? What is RDBMS?

Database: A collection of structured data stored and accessed electronically.

DBMS (Database Management System): Software that provides an interface for interacting with databases, allowing for data creation, modification, and querying.

RDBMS (Relational Database Management System): A type of DBMS that stores data in tables with relationships between them (e.g., MySQL, PostgreSQL).



18. What is Alias in SQL?

An Alias is a temporary name given to a table or column for the duration of a query. It makes the query easier to read. Example:

SELECT column_name AS alias_name FROM table_name;



19. What is Normalisation? Describe various forms.

Normalization is the process of organizing a database to reduce redundancy and dependency.

1NF (First Normal Form): Ensures no repeating groups or arrays in a table.

2NF (Second Normal Form): Ensures that all non-key attributes are fully dependent on the primary key.

3NF (Third Normal Form): Ensures that no non-key attribute is transitively dependent on the primary key.

BCNF (Boyce-Codd Normal Form): A stricter version of 3NF.



20. How do you sort the results of a query?

Use the ORDER BY clause to sort the result set. Example:

SELECT * FROM table_name ORDER BY column_name ASC|DESC;



21. Explain the types of Window functions?

ROW_NUMBER(): Assigns a unique row number to each row.

RANK(): Assigns a rank, with gaps in case of ties.

DENSE_RANK(): Assigns a rank without gaps.

NTILE(): Divides the result set into a specified number of buckets.

LEAD() and LAG(): Accesses data from subsequent or previous rows.



22. What is limit and offset?

LIMIT: Restricts the number of rows returned by a query. Example: LIMIT 10.

OFFSET: Skips the specified number of rows before beginning to return rows. Example: OFFSET 5 LIMIT 10.



23. What is candidate key?

A candidate key is a set of one or more columns that can uniquely identify a row in a table. There can be multiple candidate keys in a table, but one is chosen as the primary key.



24. Describe various types of Alter command?

ALTER TABLE: Modifies an existing table (e.g., adding/removing columns, changing data types).

Example: ALTER TABLE table_name ADD column_name datatype;


ALTER COLUMN: Modifies an existing column's properties.

Example: ALTER TABLE table_name ALTER COLUMN column_name SET DATA TYPE new_type;




25. What is Cartesian product?

A Cartesian Product occurs when a query uses a CROSS JOIN. It returns all possible combinations of rows from the two tables, resulting in a multiplication of the number of rows from both tables.

Example:

SELECT * FROM table1 CROSS JOIN table2;


React with ❤️ for more Interview Resources