- What is SQL?
- SQL (Structured Query Language) is a domain-specific language used for managing and manipulating relational databases.
- Explain the difference between SQL and NoSQL databases.
- SQL databases are relational databases, while NoSQL databases are non-relational and provide more flexibility in handling unstructured data.
- What are the types of SQL statements?
- SQL statements include SELECT (retrieving data), INSERT (adding data), UPDATE (modifying data), and DELETE (deleting data).
- Define the term “Normalization” in databases.
- Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity.
- Explain ACID properties in the context of database transactions.
- ACID stands for Atomicity, Consistency, Isolation, and Durability, ensuring reliability and consistency in database transactions.
- How do you retrieve unique values from a column?
SELECT DISTINCT column_name FROM table_name;
- Explain the LIKE operator in SQL.
- The LIKE operator is used for pattern matching in a WHERE clause. For example,
SELECT * FROM employees WHERE last_name LIKE 'S%';retrieves names starting with ‘S’.
- The LIKE operator is used for pattern matching in a WHERE clause. For example,
- What is the purpose of the ORDER BY clause?
- ORDER BY is used to sort the result set based on one or more columns. For example,
SELECT * FROM products ORDER BY price DESC;sorts products by price in descending order.
- ORDER BY is used to sort the result set based on one or more columns. For example,
- What is a self-join?
- A self-join is a regular join, but the table is joined with itself. It is useful for hierarchical data.
- Explain the difference between INNER JOIN and LEFT JOIN.
- INNER JOIN returns only the rows where there is a match in both tables, while LEFT JOIN returns all rows from the left table and matching rows from the right table.
- What is a cross join?
- A cross join (or Cartesian join) returns the Cartesian product of two tables, meaning every row from the first table is combined with every row from the second table.
- How do you calculate the average of a column?
SELECT AVG(column_name) FROM table_name;
- Explain the purpose of the GROUP_CONCAT function.
- GROUP_CONCAT is used to concatenate values from multiple rows into a single string, grouped by a specified column.
- What is the difference between GROUP BY and ORDER BY?
- GROUP BY is used to group rows that have the same values in specified columns, while ORDER BY is used to sort the result set.
- What is a correlated subquery?
- A correlated subquery is a subquery that depends on the outer query, using values from the outer query in its WHERE clause.
- How do you use the EXISTS keyword in a subquery?
SELECT * FROM employees WHERE EXISTS (SELECT 1 FROM departments WHERE employees.department_id = departments.department_id);
- Explain the difference between a subquery and a JOIN.
- A subquery is a query nested within another query, while a JOIN is used to combine columns from two or more tables based on related columns.
- How do you update data in a table?
UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;
- Explain the purpose of the ROLLBACK statement.
- ROLLBACK is used to undo changes made during the current transaction if an error occurs.
- What is the CASCADE option in a foreign key constraint?
- CASCADE automatically deletes or updates related records in child tables when the primary key in the parent table is modified or deleted.
- What is an index?
- An index is a data structure that improves the speed of data retrieval operations on a database table.
- How do you create an index on a column?
CREATE INDEX index_name ON table_name (column_name);
- Explain the purpose of the ANALYZE statement.
- ANALYZE is used to update statistics about the distribution of values in a table, helping the query planner make better decisions.
- Define the PRIMARY KEY constraint.
- A PRIMARY KEY constraint uniquely identifies each record in a table and must contain unique and non-null values.
- What is the purpose of the CHECK constraint?
- CHECK ensures that the values in a column meet a specific condition, preventing invalid data entry.
- Explain the difference between UNIQUE and PRIMARY KEY constraints.
- UNIQUE ensures that all values in a column are different, while PRIMARY KEY is a combination of UNIQUE and NOT NULL.
- What is a view in SQL?
- A view is a virtual table based on the result of a SELECT statement, simplifying complex queries.
- How do you create a stored procedure with parameters?
CREATE PROCEDURE procedure_name (IN parameter_name data_type) AS ...
- Explain the purpose of the OUTPUT clause in a stored procedure.
- The OUTPUT clause is used to return values from a stored procedure back to the calling program.
- How do you grant SELECT permission on all tables in a database to a user?
GRANT SELECT ON ALL TABLES IN SCHEMA public TO user_name;
- What is SQL injection, and how can it be prevented?
- SQL injection is a type of attack where malicious SQL statements are inserted. Parameterized queries can prevent it.
- What is a recursive CTE (Common Table Expression)?
- A recursive CTE is a CTE that references itself, typically used for hierarchical data.
- Explain the purpose of the window function ROW_NUMBER().
- ROW_NUMBER() is a window function that assigns a unique number to each row within a partition of a result set.
- What is the purpose of the MERGE statement?
- MERGE is used to perform an insert, update, or delete operation based on a specified condition.
- Describe a situation where you used an index to optimize a slow query.
- Discuss the query, the index implemented, and the impact on performance.
- How would you handle a situation where an SQL query is taking a long time to execute?
- Mention strategies like analyzing the query execution plan and optimizing indexes.
- Explain the importance of data normalization in database design.
- Discuss how normalization reduces redundancy and improves data integrity.
- Share an experience where you had to troubleshoot and fix a data integrity issue.
- Describe the issue, the steps taken to identify the problem, and the resolution.
- How do you approach designing a database schema for a new project?
- Discuss considerations like data types, relationships, and normalization.
- Explain the steps you would take to secure a database against unauthorized access.
- Discuss user permissions, encryption, and other security measures.
- Describe a scenario where you had to implement a complex SQL query involving multiple joins.
- Discuss the tables involved, the purpose of the query, and any challenges faced.
- How do you ensure that a database query is optimized for performance?
- Discuss techniques such as indexing, query optimization, and caching.
- Explain the concept of a deadlock in a database and how you would resolve it.
- Discuss situations where multiple transactions are waiting for each other to release locks.
- Describe a situation where you had to migrate data from one database to another.
- Discuss the tools and techniques used for data migration, ensuring data integrity.
- How do you handle database backups and recovery in case of data loss?
- Discuss backup strategies and the steps involved in recovering from a backup.
- Explain the concept of database normalization and denormalization.
- Discuss the trade-offs between normalized and denormalized database designs.
- Share an experience where you had to work with a large dataset and optimize query performance.
- Discuss strategies such as partitioning, indexing, and query optimization.
- How do you handle transactions in SQL, and what is the purpose of the COMMIT and ROLLBACK statements?
- Discuss the principles of transaction management and how these statements are used.
- Describe a situation where you had to work with a complex database schema.
- Discuss the challenges faced and how you approached designing queries for such a schema.
- How would you ensure data consistency in a distributed database environment?
- Discuss techniques such as distributed transactions and two-phase commit.