SQL Interview Questions - Fullstackadda

Here is a list of possible interview questions of SQL, along with answers to help you prepare for your interview:

1. What is SQL and why is it used?

SQL stands for Structured Query Language and is used to manage and manipulate relational databases.

2. What are the main commands in SQL?

The main commands in SQL are SELECT, INSERT, UPDATE, DELETE, and CREATE.

3. How do you select specific columns in a table?

You can select specific columns in a table by using the SELECT statement followed by the column name, for example:

SELECT column1, column2 FROM table_name;

4. How do you select distinct values from a column?

You can select distinct values from a column by using the SELECT DISTINCT statement followed by the column name, for example:

SELECT DISTINCT column_name FROM table_name;

5. How do you sort a query by a specific column?

You can sort a query by a specific column by using the ORDER BY statement followed by the column name, for example:

SELECT column1, column2 FROM table_name 
ORDER BY column1;

6. How do you limit the number of rows returned in a query?

You can limit the number of rows returned in a query by using the LIMIT statement followed by a number, for example:

SELECT column1, column2 FROM table_name 
LIMIT 10;

7. How do you join multiple tables in a query?

You can join multiple tables in a query by using the JOIN statement followed by the table name and the join condition, for example

SELECT * FROM table1 JOIN table2 ON table1.column_name = table2.column_name;

8. How do you update a specific record in a table?

You can update a specific record in a table by using the UPDATE statement followed by the table name, the SET keyword, and the new values, for example

UPDATE table_name SET column1 = new_value WHERE column2 = specific_value;

9. How do you delete a specific record from a table?

You can delete a specific record from a table by using the DELETE statement followed by the table name and the WHERE clause, for example:

DELETE FROM table_name
WHERE
column_name = specific_value;

10. How do you create a new table in a database?

You can create a new table in a database by using the CREATE TABLE statement followed by the table name, column names, and data types, for example

CREATE TABLE table_name (column1 data_type, column2 data_type, column3 data_type);

11. How do you create a primary key for a table?

You can create a primary key for a table by using the PRIMARY KEY constraint, for example:

CREATE TABLE table_name (id INT PRIMARY KEY, column1 VARCHAR(255), column2 VARCHAR(255));

12. How do you create a foreign key for a table?

You can create a foreign key for a table by using the FOREIGN KEY constraint, for example

CREATE TABLE table2 (id INT, FOREIGN KEY (id) REFERENCES table1(id));

13. How do you create an index in a table?

You can create an index in a table by using the CREATE INDEX statement followed by the index name, the table name, and the column name, for example:

CREATE INDEX index_name ON table_name (column_name);

14. What is a primary key in SQL?

A primary key is a column or set of columns in a table that uniquely identifies each row in the table. Primary keys are used to establish relationships between tables and enforce data integrity.

15. What is the difference between a DELETE and TRUNCATE statement in SQL?

A DELETE statement is used to delete specific rows from a table, while a TRUNCATE statement is used to delete all data from a table, effectively resetting the table to its initial state.

16. How can you use the GROUP BY clause in SQL?

The GROUP BY clause is used to group the rows in a SELECT statement based on one or more columns. The basic syntax is:

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column

17. How can you drop a table in SQL?

You can use the DROP TABLE statement to delete a table and all of its data permanently. The basic syntax is:

DROP TABLE table_name;

18. What is a transaction in SQL?

A transaction is a sequence of SQL statements that are treated as a single unit of work. All statements in a transaction are committed (made permanent) or rolled back (undone) together.

19. How can you create a table in SQL?

You can use the CREATE TABLE statement to create a new table in a database. The basic syntax is:

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    ...
);

20. How can you add a new column to a table in SQL?

You can use the ALTER TABLE statement to add a new column to an existing table. The basic syntax is:

ALTER TABLE table_name
ADD column_name datatype constraint;

21.How can you sort the results of a query in SQL?

You can use the ORDER BY keyword to sort the results of a query in ascending or descending order. The basic syntax is:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

22. What is a view in SQL?

A view is a virtual table that is based on the result of a SELECT statement. Views can be used to simplify complex queries or to limit access to specific columns in a table.

23. How can you create a stored procedure in SQL?

You can use the CREATE PROCEDURE statement to create a new stored procedure in a database. The basic syntax is:

CREATE PROCEDURE procedure_name
AS
BEGIN
    --SQL statements
END;

24.How can you use the BETWEEN operator in SQL?

The BETWEEN operator is used to filter results based on a range of values. The basic syntax is:

SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

25. How can you use the LIKE operator in SQL?

The LIKE operator is used to search for a specific pattern in a column. The basic syntax is:

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE 'pattern';

26. How can you use the LIMIT clause in SQL?

The LIMIT clause is used to limit the number of rows returned in a query. The basic syntax is:

SELECT column1, column2, ...
FROM table_name
LIMIT number_of_rows;

27. How can you use the NOT IN operator in SQL?

The NOT IN operator is used to exclude specific values from the results of a query. The basic syntax is:

SELECT column1, column2, ...
FROM table_name
WHERE column_name NOT IN (value1, value2, ...);

28. How can you use the IS NULL operator in SQL?

The IS NULL operator is used to test for null values in a column. The basic syntax is:

SELECT column1, column2, ...
FROM table_name
WHERE column_name IS NULL;

29. How can you use the UNION operator in SQL?

The UNION operator is used to combine the results of two or more SELECT statements into a single result set. The basic syntax is:

SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

30. How can you use the AVG() function in SQL?

The AVG() function is used to calculate the average value of a numeric column. The basic syntax is:

SELECT AVG(column_name) FROM table_name;

31. What is the difference between a group by and a having clause?

A group by clause is used to group the results of a query by one or more columns, while a having clause is used to filter the results of a group by based on some condition.

32. What is a foreign key and how is it used?

A foreign key is a column or combination of columns that references a primary key in another table. It is used to establish a relationship between two tables, ensuring data integrity and enforcing referential integrity constraints.

33.What is a trigger and how is it used?

A trigger is a special type of stored procedure that automatically executes in response to certain events, such as inserting, updating, or deleting data in a table. It is used to enforce business rules, audit changes, or automate complex tasks.

34.What is a self-join and how is it used?

A self-join is a join where a table is joined with itself using a common column. It is used to retrieve related data from the same table, such as finding all employees who have the same manager.

35. What is a stored function and how is it used?

A stored function is a pre-written block of code that returns a single value. It is used to perform calculations, manipulate data, or validate inputs.

36. What is a transaction log and how is it used?

A transaction log is a record of all changes made to a database, including inserts, updates, and deletes. It is used to recover data in case of a system failure or user error.

37. What is a sub-select and how is it used?

A sub-select is a query nested within another query, used to retrieve data for a specific purpose, such as filtering or joining data.

38. What is the difference between a left outer join and a right outer join?

A left outer join returns all rows from the left table and matching rows from the right table, while a right outer join returns all rows from the right table and matching rows from the left table.

39. What is the purpose of an index and how is it used?

An index is a data structure used to speed up data retrieval by allowing the database engine to quickly locate specific data based on the values in one or more columns. It is used to improve query performance and reduce data access time.

40. What is a common table expression (CTE) and how is it used?

A common table expression is a temporary result set defined within a SQL statement. It is used to simplify complex queries, improve readability, and reuse code.

41. What is a foreign key and how is it used?

42. What is a trigger and how is it used?

43. What is a self-join and how is it used?

A self-join is a join where a table is joined with itself using a common column. It is used to retrieve related data from the same table, such as finding all employees who have the same manager.

44. What is a stored function and how is it used?

A stored function is a pre-written block of code that returns a single value. It is used to perform calculations, manipulate data, or validate inputs.

45. What is a transaction log and how is it used?

A transaction log is a record of all changes made to a database, including inserts, updates, and deletes. It is used to recover data in case of a system failure or user error.

46. What is a sub-select and how is it used?

A sub-select is a query nested within another query, used to retrieve data for a specific purpose, such as filtering or joining data.

47. What is the difference between a left outer join and a right outer join?

A left outer join returns all rows from the left table and matching rows from the right table, while a right outer join returns all rows from the right table and matching rows from the left table.

48. What is the purpose of an index and how is it used?

49. What is a common table expression (CTE) and how is it used?

A common table expression is a temporary result set defined within a SQL statement. It is used to simplify complex queries, improve readability, and reuse code.

50. What is the difference between a clustered and a non-clustered index?

A clustered index determines the physical order of data in a table based on the values of the indexed columns, while a non-clustered index contains a separate data structure that maps the indexed columns to the corresponding rows in the table.

51. What is a materialized view and how is it used?

A materialized view is a precomputed result set that is stored on disk for fast data retrieval. It is used to speed up complex queries and reduce response time for data analysis.

52. What is a data warehouse and how is it different from a traditional database?

A data warehouse is a centralized repository that stores large amounts of historical and current data for analysis and decision-making. It is optimized for read-intensive workloads and data analysis, while a traditional database is optimized for transactional processing.

53. What is a correlated subquery and how is it used?

A correlated subquery is a subquery that references a column from the outer query. It is used to retrieve data based on the values in the outer query, such as finding the most recent order for each customer.

54. What is a full outer join and how is it used?

A full outer join returns all rows from both tables being joined, along with matching rows from each table where available. It is used to combine data from two tables where not all rows have a matching value.

55. What is a constraint and how is it used?

A constraint is a rule that limits the type or value of data that can be inserted, updated, or deleted in a table. It is used to enforce data integrity and prevent data inconsistencies.

56. What is a pivot table and how is it used?

A pivot table is a summary table that groups and aggregates data based on one or more dimensions and measures. It is used to simplify complex data analysis and make it easier to understand and visualize.

57. What is a recursive common table expression (CTE) and how is it used?

A recursive CTE is a type of CTE that references itself in the definition. It is used to perform recursive calculations or traverse hierarchical data structures, such as finding all descendants of a parent node in a tree.

58. What is the purpose of the SQL injection attack and how can it be prevented?

A SQL injection attack is a type of security exploit that allows an attacker to execute malicious SQL code in a database by manipulating input data. It can be prevented by using parameterized queries, input validation, and access controls.

59. What is a database trigger and how is it used?

A database trigger is a special type of stored procedure that automatically executes in response to certain events, such as inserting, updating, or deleting data in a table. It is used to enforce business rules, audit changes, or automate complex tasks.

60. What is normalization and why is it important in database design?

Normalization is the process of organizing data in a database to minimize redundancy and improve data integrity. It is important in database design because it helps to reduce data anomalies, improve data consistency, and simplify data maintenance.

61. What is a view and how is it used?

A view is a virtual table that is derived from one or more tables or other views in a database. It is used to simplify complex queries, limit data access, and provide a level of abstraction between the user and the underlying data.

62. What is a cursor and how is it used?

A cursor is a database object used to traverse a result set row by row and perform operations on each row. It is used to perform tasks that cannot be accomplished with a single SQL statement, such as looping through a result set and performing calculations or updates.

63. What is a transaction and how is it used?

A transaction is a series of database operations that are treated as a single, atomic unit of work. It is used to ensure data consistency and integrity, and to provide a way to rollback changes in case of errors or exceptions.

64. What is a correlated subquery and how is it used?

65. What is a recursive common table expression (CTE) and how is it used?

66. What is a database index and how is it used?

A database index is a data structure used to speed up data retrieval by allowing the database engine to quickly locate specific data based on the values in one or more columns. It is used to improve query performance and reduce data access time.

67. What is a correlated subquery and how is it used?

68. What is a transaction log and how is it used?

A transaction log is a record of all changes made to a database, including inserts, updates, and deletes. It is used to recover data in case of a system failure or user error.

69. What is the difference between a left join and a right join?

A left join returns all rows from the left table and matching rows from the right table, while a right join returns all rows from the right table and matching rows from the left table.