fbpx
Enquire Now

The Ultimate Guide to SQL Interview Questions and Answers for 2024

If you’re preparing for an SQL interview or just aiming to strengthen your knowledge, you’re in the right place. In this blog, we’ll cover fundamental SQL interview questions with practical answers and explanations that will boost your confidence. Let’s get started.

What is SQL, and What is its Purpose?

SQL (Structured Query Language) is the standard language used to communicate with relational databases. SQL allows you to query, update, insert, delete, and manage data stored in databases. Its primary purpose is to perform operations on data, ensuring you can extract meaningful insights and manage records.

For example, to fetch all data from a table, you’d use a query like –

SELECT * FROM table_name;

How are NOSQL Database different from SQL Databases ?

A common topic in interviews is the difference between SQL and NoSQL databases.

  • SQL Databases: These are relational and have a structured schema. Think of it as well-organized rows and columns, similar to Excel. SQL is perfect for applications that require complex queries, data integrity, and transactions (like banking).

  • NoSQL Databases: These are non-relational and can handle unstructured or semi-structured data, making them suitable for big data applications or real-time data, where flexibility is key (e.g., social media platforms).

What is a Data Type?

In SQL, a data type specifies what kind of data a column can hold. Here’s a breakdown of common data types:

  • INT: Stores whole numbers (e.g., 100, -45).
  • VARCHAR: Stores variable-length strings (e.g., names, descriptions).
  • DATE: Stores date values (e.g., ‘2024-01-01’).
  • BOOLEAN: Stores TRUE or FALSE values.
  • FLOAT: Stores decimal numbers (e.g., 12.34).

What is the Purpose of the SELECT Statement in SQL?

The SELECT statement retrieves data from a database. It’s the most commonly used SQL query.

SELECT column1, column2 FROM table_name;

Want to see everything in a table? Just use this – 

SELECT * FROM employees;

How to Find the Number of Employees in Each City

SELECT city, COUNT(*) FROM employees GROUPBY city;

What is the difference Between WHERE and HAVING Clauses

  • WHERE: Filters rows before aggregation (use with non-aggregated conditions).
  • HAVING: Filters after aggregation (used with aggregate functions).

SELECT city, COUNT(*) FROM employees WHERE state = ‘CA’ GROUP BY city HAVING COUNT(*) > 5;

What is a JOIN?

A JOIN is used to combine data from two or more tables based on a related column.

What are different types of SQL JOINs

  • INNER JOIN: Returns rows with matching values in both tables.
  • LEFT JOIN: Returns all rows from the left table, and matching rows from the right. Rows without a match will have NULL values.
  • RIGHT JOIN: Opposite of LEFT JOIN, returning all rows from the right.
  • FULL JOIN: Returns all matching rows plus unmatched rows from both tables.

What is the difference between inner join and left join? 

  • INNER JOIN: Returns only rows that have matching values in both tables.
  • LEFT JOIN: Returns all rows from the left table, even if there are no matches in the right table. If no match is found, NULL values are returned for columns from the right table.

How to Join Two Tables in SQL?

To join two tables, for example, employees and departments – 

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

How do we UPDATE data in SQL?

To update records in a table:

UPDATE employees SET salary = 60000 WHERE employee_id = 101;

What is the purpose of ORDER BY Clause?

The ORDER BY clause sorts the result set based on one or more columns, either in ascending (ASC) or descending (DESC) order

SELECT * FROM employees ORDER BY salary DESC;

What is difference Between DROP, DELETE, and TRUNCATE

DROP: Removes the entire table and its data.

DELETE: Removes specific rows from a table.

TRUNCATE: Removes all rows from a table but keeps the table structure intact.

TRUNCATE employees:

How do we take backup and restore database:

To backup a MySQL database:

mysqldump -u username -p database_name > backup.sql;

To restore a MySQL database:

mysql -u username -p database_name < backup.sql;

What is a Primary Key?

A PRIMARY KEY uniquely identifies each record in a table. It ensures that no duplicate rows exist, and each record can be uniquely retrieved.

What is a Foreign Key?

A FOREIGN KEY links records in one table to a PRIMARY KEY in another table. It establishes a relationship between two tables, enforcing referential integrity.

What is an Index?

An INDEX helps speed up the retrieval of rows by creating a quick lookup reference for specific columns. However, indexes can slow down INSERT and UPDATE operations as they need to be updated as well.

Shopping Basket