Basic SQL Queries
1. What is SQL?
SQL stands for Structured Query Language, a standardized programming language used to manage relational databases and perform various operations on the data in them. SQL can be used for tasks such as querying data, updating records, and managing database structures.
2. What are the different types of SQL commands?
SQL commands are categorized into several types:
- DDL (Data Definition Language): Commands like CREATE, ALTER, and DROP that define the database structure.
- DML (Data Manipulation Language): Commands such as SELECT, INSERT, UPDATE, and DELETE that manipulate data.
- DCL (Data Control Language): Commands like GRANT and REVOKE that control access to data.
- TCL (Transaction Control Language): Commands such as COMMIT and ROLLBACK that manage transactions.
3. What is the difference between a primary key and a foreign key?
- Primary Key: A unique identifier for a record in a table. It must contain unique values and cannot contain NULLs.
- Foreign Key: A field in one table that uniquely identifies a row in another table. It establishes a relationship between the two tables.
4. Write a SQL query to select all columns from a table named "employees".
```sql
SELECT FROM employees;
```
5. What is a JOIN? Explain different types of JOINS.
A JOIN is used to combine rows from two or more tables based on a related column. The different types of JOINS include:
- INNER JOIN: Returns records with matching values in both tables.
- LEFT JOIN (or LEFT OUTER JOIN): Returns all records from the left table, and the matched records from the right table. If there is no match, NULL values are returned.
- RIGHT JOIN (or RIGHT OUTER JOIN): Returns all records from the right table, and the matched records from the left table. If there is no match, NULL values are returned.
- FULL JOIN (or FULL OUTER JOIN): Returns all records when there is a match in either left or right table records.
Intermediate SQL Queries
6. How can you retrieve unique values from a column in a table?
To retrieve unique values from a column, you can use the DISTINCT keyword. Here's an example:
```sql
SELECT DISTINCT column_name FROM table_name;
```
7. Write a SQL query to find the second highest salary from the "employees" table.
One way to find the second highest salary is to use the following query:
```sql
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);
```
8. What are aggregate functions in SQL? Provide examples.
Aggregate functions perform a calculation on a set of values and return a single value. Common aggregate functions include:
- COUNT(): Counts the number of rows.
- SUM(): Calculates the total sum of a numeric column.
- AVG(): Computes the average of a numeric column.
- MIN(): Finds the smallest value in a column.
- MAX(): Finds the largest value in a column.
Example usage:
```sql
SELECT COUNT() FROM employees;
SELECT AVG(salary) FROM employees;
```
9. Explain the GROUP BY clause and provide an example.
The GROUP BY clause groups rows that have the same values in specified columns into summary rows. It is often used with aggregate functions.
Example:
```sql
SELECT department, COUNT() FROM employees GROUP BY department;
```
This query counts the number of employees in each department.
10. What is a subquery? Provide an example.
A subquery is a query nested inside another SQL query. It is used to perform operations that require multiple steps.
Example:
```sql
SELECT employee_name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);
```
This query retrieves the names of employees whose salary is above the average salary.
Advanced SQL Queries
11. What is normalization? Why is it important?
Normalization is the process of organizing data in a database to minimize redundancy and dependency. It involves dividing a database into tables and defining relationships between them. Normalization is important because it:
- Reduces data redundancy.
- Improves data integrity.
- Simplifies data maintenance.
12. Explain the concept of indexing in SQL.
Indexing is a database optimization technique that enhances the speed of data retrieval operations on a database table. An index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional space and slower writes.
Types of indexes include:
- Unique Index: Ensures that all values in the indexed column are unique.
- Composite Index: An index on multiple columns.
- Full-text Index: Used for full-text searches.
13. Write a SQL query to find duplicate records in a table.
To find duplicate records based on a specific column, you can use the following query:
```sql
SELECT column_name, COUNT() FROM table_name GROUP BY column_name HAVING COUNT() > 1;
```
14. What is a view in SQL? How is it different from a table?
A view is a virtual table that is based on the result set of a SQL query. It contains rows and columns, just like a regular table, but it does not store the data itself. Instead, it provides a way to represent data from one or more tables.
Differences between a view and a table:
- A table stores data physically, while a view does not.
- A view can provide a simplified representation of complex queries.
15. How can you delete records from a table based on a condition?
To delete records from a table based on a condition, you can use the DELETE statement with a WHERE clause. For example:
```sql
DELETE FROM employees WHERE department = 'Sales';
```
This query deletes all employees from the Sales department.
Conclusion
Preparing for SQL queries interview questions requires a solid understanding of both fundamental and advanced concepts in SQL. Familiarity with various SQL commands, functions, and best practices will not only help you answer interview questions effectively but also enable you to perform efficiently in real-world database management scenarios. Practice using different queries and scenarios to build your confidence and improve your SQL skills.
Frequently Asked Questions
What is the difference between INNER JOIN and LEFT JOIN in SQL?
INNER JOIN returns only the rows that have matching values in both tables, while LEFT JOIN returns all rows from the left table and the matched rows from the right table. If there is no match, NULL values are returned for columns from the right table.
How do you retrieve unique records from a table in SQL?
You can retrieve unique records by using the DISTINCT keyword in your SELECT statement. For example: SELECT DISTINCT column_name FROM table_name;
What is a subquery in SQL?
A subquery is a query nested inside another query. It can be used to provide a value for the outer query or to filter results. Subqueries can be found in SELECT, INSERT, UPDATE, or DELETE statements.
Explain the purpose of the GROUP BY clause in SQL.
The GROUP BY clause is used to arrange identical data into groups. It is often used with aggregate functions like COUNT, SUM, AVG, etc., to perform operations on each group of data.
What is the difference between a primary key and a foreign key in SQL?
A primary key is a column or a set of columns that uniquely identifies each row in a table. A foreign key, on the other hand, is a column that creates a relationship between two tables by referring to the primary key of another table.
