SQL Interview Questions & Answers Guide
Uploaded by
rutujakalaskar15SQL Interview Questions & Answers Guide
Uploaded by
rutujakalaskar15Common questions
Powered by AIStored procedures are sets of SQL statements saved in the database that can be executed repeatedly, reducing redundancy and improving efficiency by encapsulating complex operations. They enhance security, as users can execute complex logic without direct access to data, and minimize network traffic, as procedures are executed within the database server .
Joins are used to retrieve related data from multiple tables. Types include INNER JOIN, which returns only matching records from both tables; LEFT JOIN, which returns all records from the left table and the matched records from the right table; RIGHT JOIN, which returns all records from the right table and matched records from the left table; and FULL JOIN, which returns all records when there is a match in either left or right table records .
DELETE is used to remove specific rows and can be rolled back, making it suitable for operations where selective record deletion and transaction safety are important. TRUNCATE removes all rows without the option to roll back, so it is ideal for quickly clearing a table when transaction support is not necessary .
Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. 1NF removes duplicate columns; 2NF ensures all attributes are dependent on the primary key; 3NF removes transitive dependencies, and BCNF is a stronger version of 3NF that further reduces redundancy by ensuring that every determinant is a candidate key .
A primary key is a unique identifier for a row in a table and does not allow NULL values, ensuring that each row is distinct. 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. This enforces referential integrity and links related data across tables .
UNION removes duplicate records, making it useful when duplicate elimination is necessary for accurate data representation. UNION ALL includes all records, even duplicates, which is beneficial when duplicates carry relevant information or when performance is a priority since it is faster as no deduplication step is needed .
The ACID properties ensure reliable transaction processing by tackling crucial areas: Atomicity guarantees that transactions are all-or-nothing, preventing partial updates; Consistency ensures data remains in a valid state; Isolation allows transactions to operate independently, thus preventing concurrent transaction interferences; Durability ensures that once a transaction is committed, it will survive system failures, maintaining data integrity .
SQL commands are divided into five categories: DDL (Data Definition Language) includes operations like CREATE, ALTER, DROP to define database structures; DML (Data Manipulation Language) covers INSERT, UPDATE, DELETE to modify data; DQL (Data Query Language) uses SELECT to query data; TCL (Transaction Control Language) manages transactions with COMMIT, ROLLBACK; and DCL (Data Control Language) handles permissions with GRANT, REVOKE .
SQL (Structured Query Language) is a language used to communicate with databases. It helps in storing, retrieving, updating, and deleting data efficiently .
A view is a virtual table created from the result of a SQL query. It simplifies complex queries, improves security by restricting user access to a subset of data, and can encapsulate business logic. However, views can become complex to manage if overly nested and may not support direct manipulation of data, which could limit their flexibility .