1. What is SQL, and what are its primary functions?
SQL (Structured Query Language) is a programming language used to manage and manipulate relational databases. Its primary functions include querying data from databases using SELECT, inserting new records using INSERT, modifying existing records using UPDATE, and deleting records using DELETE.
2. Explain the difference between a database and a table in SQL.
A database is a collection of related data organized in a structured way. It contains one or more tables, along with other database objects. A table, on the other hand, is a fundamental structure in a database, where data is stored in rows and columns.
3. What are the different data types in SQL?
SQL supports various data types, including:
- Numeric data types: INT, FLOAT, DECIMAL, etc.
- Character data types: CHAR, VARCHAR, TEXT, etc.
- Date and time data types: DATE, TIME, DATETIME, etc.
- Boolean data type: BOOLEAN, BIT, etc.
4. What is a primary key, and why is it important?
A primary key is a unique identifier for each row in a table. It ensures that each record in the table can be uniquely identified. It is crucial for maintaining data integrity and for defining relationships between tables using foreign keys.
5. Describe the difference between a clustered and a non-clustered index.
In SQL, a clustered index determines the physical order of data rows in a table. There can be only one clustered index per table, and it organizes the data for faster retrieval based on the indexed column. A non-clustered index is a separate structure that contains a copy of the indexed columns and a pointer to the actual rows. A table can have multiple non-clustered indexes, and they improve query performance without changing the physical order of data.
6. What is a foreign key, and how is it used?
A foreign key is a column or a combination of columns in a table that refers to the primary key of another table. It establishes a relationship between two tables, enforcing referential integrity. It ensures that data in the referencing table corresponds to data in the referenced table.
7. Explain the ACID properties in the context of databases.
ACID stands for Atomicity, Consistency, Isolation, and Durability. These properties ensure that database transactions are processed reliably and accurately. Atomicity ensures that a transaction is treated as a single, indivisible unit of work. Consistency guarantees that the database remains in a valid state before and after each transaction. Isolation ensures that each transaction is isolated from other transactions until it is completed. Durability ensures that once a transaction is committed, its changes are permanent and survive system failures.
8. What is the purpose of normalization in databases?
Normalization is the process of organizing data in a database to eliminate redundancy and improve data integrity. It involves breaking down a large table into smaller tables and defining relationships between them using primary and foreign keys. The goal of normalization is to minimize data duplication and maintain consistency throughout the database.
9. What are the different types of SQL joins?
The main types of SQL joins are:
- INNER JOIN: Returns only the rows with matching values in both tables.
- LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table and the matched rows from the right table.
- RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table and the matched rows from the left table.
- FULL JOIN (or FULL OUTER JOIN): Returns all rows when there is a match in either table.
10. What is the difference between INNER JOIN and OUTER JOIN?
INNER JOIN returns only the rows that have matching values in both tables, effectively intersecting the data. OUTER JOIN, on the other hand, returns all the rows from at least one table and the matched rows from the other table. If there is no match for a row, the result will contain NULL values for columns from the non-matching table.
11. How do you find duplicate records in a table?
To find duplicate records in a table, you can use the GROUP BY clause along with the HAVING clause to filter rows with a count greater than one.
Here’s an example:
SELECT column1, column2, COUNT(*)
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;12. Explain the purpose of the GROUP BY clause in SQL.
The GROUP BY clause is used to group rows with similar values in a specified column(s). It is often used in combination with aggregate functions like SUM, AVG, COUNT, etc. The result of the GROUP BY clause is a summary of data grouped based on the specified column(s).
13. How can you filter aggregated results in SQL?
You can filter aggregated results in SQL using the HAVING clause. While the WHERE clause filters individual rows before aggregation, the HAVING clause filters the results after aggregation. It allows you to specify conditions on aggregated values.
14. What is the use of the HAVING clause in SQL?
The HAVING clause is used to filter the results of the GROUP BY clause based on specified conditions. It works like the WHERE clause but is applied to the grouped data, allowing you to filter aggregated results.
15. What is a subquery, and how is it used in SQL?
A subquery is a query nested inside another query. It is used to retrieve data that will be used as a filter condition or for calculations in the main query. Subqueries are enclosed in parentheses and can appear in the SELECT, FROM, WHERE, and HAVING clauses.
16. How do you use the UNION and UNION ALL operators?
Both UNION and UNION ALL are used to combine the results of two or more SELECT queries into a single result set. The difference is that UNION removes duplicate rows, whereas UNION ALL retains all rows, including duplicates. Syntax:
SELECT column1, column2 FROM table1
UNION [ALL]
SELECT column1, column2 FROM table2;17. How do you add a new column to an existing table?
You can use the ALTER TABLE statement to add a new column to an existing table. Syntax:
ALTER TABLE table_name
ADD new_column datatype;18. How can you remove a column from a table?
To remove a column from a table, you can use the ALTER TABLE statement with the DROP COLUMN clause. Be cautious as dropping a column will permanently delete the data in that column. Syntax:
ALTER TABLE table_name
DROP COLUMN column_name;19. What are triggers, and when are they used?
Triggers are database objects that automatically execute in response to specific events, such as INSERT, UPDATE, DELETE, etc., on a table. They are often used to enforce referential integrity, maintain audit logs, or update related tables when certain actions occur in the database.
Triggers can be “before” or “after” the triggering event.
20. What is a view, and why is it used?
A view is a virtual table created based on the result of a SELECT query. It does not store data itself but provides a convenient way to access and manipulate data from multiple tables. Views are used to simplify complex queries, restrict data access, and ensure data security.
21. Explain the difference between DELETE and TRUNCATE commands.
DELETE and TRUNCATE are used to remove data from a table, but they differ in functionality.
DELETE is a DML (Data Manipulation Language) command that removes rows one by one and can be used with a WHERE clause to specify which rows to delete. It also generates individual row-level triggers if they are defined on the table.
TRUNCATE, on the other hand, is a DDL (Data Definition Language) command that removes all rows from a table in a single operation, and it cannot be used with a WHERE clause. TRUNCATE is generally faster than DELETE because it deallocates the data pages, but it doesn’t trigger row-level triggers.
22. How do you update records in a table using SQL?
To update records in a table, you use the UPDATE statement along with the SET clause to specify the new values and the WHERE clause to filter the records to be updated. Here’s an example:
UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;23. What is the purpose of the COALESCE function in SQL?
The COALESCE function returns the first non-null expression from a list of expressions. It is used to handle NULL values and provide a default value when the first non-null value is encountered. If all expressions are NULL, COALESCE returns NULL. Example:
SELECT COALESCE(column1, column2, 'N/A') AS result
FROM table_name;24. How do you calculate the average of a column in SQL?
You can use the AVG aggregate function to calculate the average of a column in SQL. Here’s an example:
SELECT AVG(column1) AS average
FROM table_name;25. What is the difference between CHAR and VARCHAR data types?
Both CHAR and VARCHAR are used for character data, but they differ in storage and handling. CHAR is a fixed-length data type that stores a specific number of characters, padding with spaces if the value is shorter. VARCHAR is a variable-length data type that stores only the actual characters used, without padding. As a result, VARCHAR is more space-efficient for shorter values.
26. What are Common Table Expressions (CTEs), and when are they used?
Common Table Expressions (CTEs) are temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. They enhance readability and simplify complex queries. CTEs are especially useful when you need to perform recursive queries or reference the same subquery multiple times.
27. How do you find the second-highest salary in a table?
You can use the ORDER BY clause along with the LIMIT or OFFSET clause to find the second-highest salary in a table.
SELECT salary
FROM table_name
ORDER BY salary DESC
LIMIT 1 OFFSET 1;28. Explain the difference between stored procedures and functions.
Stored procedures and functions are both database objects that contain a collection of SQL statements. However, the main difference is that a function returns a single value and is used within SQL expressions, while a stored procedure can perform multiple operations and does not return a value. Functions are often used for calculations, whereas stored procedures are commonly used for complex data manipulations and business logic.
29. How do you handle NULL values in SQL?
You can handle NULL values in SQL using functions like COALESCE and NULLIF. Additionally, you can use the IS NULL or IS NOT NULL operators in the WHERE clause to check for NULL values. Also, you can use the CASE statement to replace NULL values with specific values or handle them conditionally.
30. What is a self-join, and when is it used?
A self-join is a SQL query in which a table is joined with itself. It is used to combine related data that exists in the same table. Self-joins are typically employed when you have a hierarchical structure in a single table and need to link records to their parent or child records within the same table.
31. How do you retrieve the current date and time in SQL?
In SQL, you can use the GETDATE() function (or CURRENT_TIMESTAMP or SYSDATETIME depending on the database system) to retrieve the current date and time.
SELECT GETDATE() AS current_datetime;32. Explain the purpose of the LIKE operator.
The LIKE operator is used in SQL for pattern matching within string values. It is commonly used with the % (matches any sequence of characters) and _ (matches any single character) wildcards. It allows you to search for data based on partial patterns rather than exact matches.
33. How do you use the CASE statement in SQL?
The CASE statement is a conditional expression in SQL that allows you to perform different actions based on specified conditions. It can be used in SELECT, UPDATE, and other statements. Here’s an example:
SELECT column1,
CASE
WHEN condition1 THEN 'Result1'
WHEN condition2 THEN 'Result2'
ELSE 'DefaultResult'
END AS result
FROM table_name;34. What are the differences between single quotes and double quotes in SQL?
In SQL, single quotes (‘) are used to denote string literals, while double quotes (“) are often used for identifier quoting (when allowed by the database system). In most SQL databases, double quotes are used to treat the enclosed text as an identifier, such as a column or table name, whereas single quotes are used for string values.
35. How do you perform a full-text search in SQL?
The method for performing a full-text search varies depending on the database system. In SQL Server, you can use the CONTAINS or FREETEXT functions, while in MySQL, you can use the MATCH AGAINST clause. Full-text search allows you to find words and phrases within text data efficiently.
36. How do you sort the results of a query in descending order?
To sort the results in descending order, you can use the ORDER BY clause along with the DESC keyword.
SELECT column1, column2
FROM table_name
ORDER BY column1 DESC;37. What are user-defined functions (UDFs), and when are they used?
User-defined functions (UDFs) are functions created by users to extend the functionality of the database. They allow you to encapsulate complex logic into a single reusable function. UDFs can be scalar (returning a single value), table-valued (returning a table), or aggregate (performing calculations on sets of rows). They are used to simplify complex queries and promote code reuse.
38. How can you create an index in SQL?
You can create an index on one or more columns of a table using the CREATE INDEX statement. Indexes improve the performance of queries by allowing the database to locate data faster.
Syntax:
CREATE INDEX index_name ON table_name (column1, column2);39. Explain the purpose of the EXISTS keyword.
The EXISTS keyword is used in SQL to check for the existence of rows returned by a subquery. It is often used in combination with the WHERE clause to filter records based on the presence of related data in another table. It returns TRUE if the subquery returns any rows, and FALSE if it is empty.
40 How do you limit the number of rows returned by a query?
You can use the LIMIT or TOP (for SQL Server) clause to restrict the number of rows returned by a query. It is commonly used for pagination or to retrieve a specific number of top results.
SELECT column1, column2
FROM table_name
LIMIT 10; -- or TOP 10 for SQL Server41. What is the use of the DISTINCT keyword in SQL?
The DISTINCT keyword is used in SQL to remove duplicate rows from the result set of a query. It ensures that only unique values are returned in the output. It is often used with the SELECT statement to retrieve distinct values from a column.
42. How do you concatenate strings in SQL?
The method for concatenating strings varies depending on the database system. In SQL Server, you can use the + operator or the CONCAT function. In MySQL, you can use the CONCAT function, and in PostgreSQL, you can use the || operator. Example in SQL Server:
SELECT column1 + ' ' + column2 AS full_name
FROM table_name;43. Explain the difference between UNION and UNION ALL.
Both UNION and UNION ALL are used to combine the results of two or more SELECT queries into a single result set. The main difference is that UNION removes duplicate rows from the result set, whereas UNION ALL retains all rows, including duplicates. As a result, UNION ALL is generally faster than UNION.
44. How do you handle exceptions and errors in SQL?
In SQL, you can use the TRY…CATCH block (in SQL Server) or BEGIN…EXCEPTION block (in Oracle) to handle exceptions and errors. These blocks allow you to catch and handle errors gracefully, providing customized error messages or performing specific actions when an error occurs during the execution of a query or stored procedure.
45. What is the purpose of the ROW_NUMBER() function?
The ROW_NUMBER() function is a window function used to assign a unique sequential number to each row in the result set based on the specified ordering. It is often used with the OVER() clause to perform ranking or pagination operations in SQL.
46. How do you find the nth highest salary in a table?
You can use the ORDER BY clause along with the LIMIT or OFFSET clause to find the nth highest salary in a table. For example, to find the second-highest salary:
SELECT salary
FROM table_name
ORDER BY salary DESC
LIMIT 1 OFFSET 1;47. What is the difference between TRUNCATE and DELETE?
TRUNCATE and DELETE are used to remove data from a table, but they differ in functionality. TRUNCATE is a DDL (Data Definition Language) command that removes all rows from a table in a single operation and resets the identity column (if any). It is faster than DELETE because it deallocates data pages. DELETE, on the other hand, is a DML (Data Manipulation Language) command that removes rows one by one and can be used with a WHERE clause to specify which rows to delete.
48. Explain the concept of a composite key.
A composite key is a key that consists of two or more columns in a table. Together, these columns uniquely identify each row in the table. In other words, the combination of values in the composite key must be unique. Composite keys are used when no single column can uniquely identify rows, and a combination of columns is needed for uniqueness.
49. How do you drop a table in SQL?
To drop a table in SQL, you use the DROP TABLE statement. Be cautious when using this command, as it permanently deletes the table and its data.
DROP TABLE table_name;
50. What is the purpose of the NOLOCK hint in SQL?
Answer: The NOLOCK hint (or READUNCOMMITTED) is used in SQL Server to allow a query to read data from a table even if it is locked by another transaction. It provides a way to access potentially uncommitted data, which can improve query performance in some situations but may lead to “dirty reads” or other concurrency issues. The NOLOCK hint should be used with caution, as it can lead to data inconsistency.