SQL: 50 Questions and Answers
1. What is SQL?
SQL stands for Structured Query Language. It is a programming language used for managing and manipulating relational databases.
2. What are the different types of SQL statements?
The different types of SQL statements are:
- SELECT: Retrieves data from a database
- INSERT: Inserts new data into a database
- UPDATE: Modifies existing data in a database
- DELETE: Deletes data from a database
3. What is a primary key?
A primary key is a unique identifier for a record in a database table. It ensures that each record can be uniquely identified and helps in maintaining data integrity.
4. What is a foreign key?
A foreign key is a field in a database table that refers to the primary key of another table. It establishes a relationship between the two tables.
5. What is normalization in SQL?
Normalization is the process of organizing data in a database to eliminate redundancy and improve data integrity. It involves splitting a table into multiple tables and defining relationships between them.
6. What is a join in SQL?
A join is used to combine rows from two or more tables based on a related column between them. It allows retrieving data from multiple tables in a single query.
7. What are the different types of joins?
The different types of joins are:
- INNER JOIN: Returns only the matching rows between two tables
- LEFT JOIN: Returns all the rows from the left table and the matching rows from the right table
- RIGHT JOIN: Returns all the rows from the right table and the matching rows from the left table
- FULL JOIN: Returns all the rows from both tables, including unmatched rows
8. What is a view in SQL?
A view is a virtual table based on the result of a SQL query. It does not store any data itself but provides an alternative way to access and manipulate data from one or more tables.
9. What is a stored procedure?
A stored procedure is a set of SQL statements that are stored in the database and can be executed as a single unit. It allows for code reusability and improves performance.
10. What is a trigger in SQL?
A trigger is a set of SQL statements that are automatically executed in response to a specific event, such as an insert, update, or delete operation on a table. It is used to enforce business rules or perform additional actions.
11. What is the difference between UNION and UNION ALL?
UNION combines the result sets of two or more SELECT statements and removes duplicate rows, while UNION ALL combines the result sets without removing duplicates.
12. What is the difference between CHAR and VARCHAR?
CHAR is a fixed-length data type that stores a fixed amount of characters, while VARCHAR is a variable-length data type that stores a varying amount of characters.
13. What is the difference between WHERE and HAVING?
WHERE is used to filter rows before they are grouped or aggregated, while HAVING is used to filter rows after they have been grouped or aggregated.
14. What is the difference between a clustered and non-clustered index?
A clustered index determines the physical order of data in a table, while a non-clustered index is a separate structure that points to the physical location of data.
15. What is the difference between a primary key and a unique key?
A primary key is a unique identifier for a record in a table and cannot contain null values, while a unique key ensures that the values in a column are unique, but null values are allowed.
16. What is the difference between a database and a schema?
A database is a collection of related data, while a schema is a logical container within a database that organizes and separates objects such as tables, views, and procedures.
17. What is the difference between a transaction and a query?
A transaction is a sequence of SQL statements that are executed as a single unit, either all succeeding or all failing, while a query is a single SQL statement that retrieves or manipulates data.
18. What is the ACID property in SQL?
The ACID property stands for Atomicity, Consistency, Isolation, and Durability. It ensures that database transactions are processed reliably and consistently.
19. What is the difference between a primary key and a foreign key?
A primary key is used to uniquely identify a record in a table, while a foreign key is used to establish a relationship between two tables.
20. What is the purpose of the COMMIT statement?
The COMMIT statement is used to save all the changes made in a transaction to the database. It makes the changes permanent and releases any locks held by the transaction.
21. What is the purpose of the ROLLBACK statement?
The ROLLBACK statement is used to undo all the changes made in a transaction and restore the database to its previous state. It cancels the transaction and releases any locks held by it.
22. What is the difference between a left outer join and a right outer join?
A left outer join returns all the rows from the left table and the matching rows from the right table, while a right outer join returns all the rows from the right table and the matching rows from the left table.
23. What is the difference between a subquery and a join?
A subquery is a query nested within another query, while a join combines rows from two or more tables based on a related column between them.
24. What is the purpose of the GROUP BY clause?
The GROUP BY clause is used to group rows with similar values in a column. It is often used in conjunction with aggregate functions to perform calculations on grouped data.
25. What is the purpose of the ORDER BY clause?
The ORDER BY clause is used to sort the result set of a query based on one or more columns. It can sort the data in ascending or descending order.
26. What is the purpose of the LIKE operator?
The LIKE operator is used to search for a specified pattern in a column. It is often used with wildcard characters such as % (matches any number of characters) and _ (matches a single character).
27. What is the purpose of the IN operator?
The IN operator is used to specify multiple values in a WHERE clause. It allows for the selection of rows that match any of the specified values.
28. What is the purpose of the BETWEEN operator?
The BETWEEN operator is used to select values within a range. It allows for the selection of rows that have values between two specified values, inclusive.
29. What is the purpose of the EXISTS operator?
The EXISTS operator is used to check if a subquery returns any rows. It is often used in conjunction with the WHERE clause to filter rows based on the result of the subquery.
30. What is the purpose of the COUNT function?
The COUNT function is used to count the number of rows that match a specified condition in a SELECT statement. It can be used with or without the GROUP BY clause.
31. What is the purpose of the SUM function?
The SUM function is used to calculate the sum of values in a column. It is often used with the GROUP BY clause to calculate the sum for each group.
32. What is the purpose of the AVG function?
The AVG function is used to calculate the average of values in a column. It is often used with the GROUP BY clause to calculate the average for each group.
33. What is the purpose of the MAX function?
The MAX function is used to find the maximum value in a column. It is often used with the GROUP BY clause to find the maximum for each group.
34. What is the purpose of the MIN function?
The MIN function is used to find the minimum value in a column. It is often used with the GROUP BY clause to find the minimum for each group.
35. What is the purpose of the UPPER function?
The UPPER function is used to convert a string to uppercase. It can be used in a SELECT statement to format the output.
36. What is the purpose of the LOWER function?
The LOWER function is used to convert a string to lowercase. It can be used in a SELECT statement to format the output.
37. What is the purpose of the DISTINCT keyword?
The DISTINCT keyword is used to eliminate duplicate rows from the result set of a query. It returns only unique values.
38. What is the purpose of the AS keyword?
The AS keyword is used to give a column or table alias in a SELECT statement. It allows for a more meaningful or concise name to be used in the result set.
39. What is the purpose of the NULL value?
The NULL value represents the absence of a value in a column. It is different from zero or an empty string and is often used to indicate missing or unknown data.
40. What is the purpose of the CASE statement?
The CASE statement is used to perform conditional logic in a SQL statement. It allows for different actions to be taken based on specified conditions.
41. What is the purpose of the COALESCE function?
The COALESCE function is used to return the first non-null value in a list. It is often used to replace null values with a default value.
42. What is the purpose of the TOP keyword?
The TOP keyword is used to limit the number of rows returned by a query. It is often used with the ORDER BY clause to retrieve the top N rows based on a specified criteria.
43. What is the purpose of the TRUNCATE TABLE statement?
The TRUNCATE TABLE statement is used to remove all rows from a table. It is faster than the DELETE statement as it does not generate any transaction logs.
44. What is the purpose of the INDEX statement?
The INDEX statement is used to create an index on one or more columns of a table. It improves the performance of queries by allowing for faster data retrieval.
45. What is the purpose of the GRANT statement?
The GRANT statement is used to give specific privileges or permissions to a user or role in a database. It allows for fine-grained control over who can access or modify data.
46. What is the purpose of the REVOKE statement?
The REVOKE statement is used to remove specific privileges or permissions from a user or role in a database. It cancels the previously granted privileges.
47. What is the purpose of the CREATE TABLE statement?
The CREATE TABLE statement is used to create a new table in a database. It defines the structure of the table, including column names, data types, and constraints.
48. What is the purpose of the ALTER TABLE statement?
The ALTER TABLE statement is used to modify an existing table in a database. It allows for adding, modifying, or dropping columns, as well as changing data types or constraints.
49. What is the purpose of the DROP TABLE statement?
The DROP TABLE statement is used to remove an existing table from a database. It permanently deletes the table and all its data.
50. What is the purpose of the TRIGGER statement?
The TRIGGER statement is used to define a trigger in a database. It specifies the event that will activate the trigger and the actions that will be performed in response to the event.