SQL Questions and Answers for Viva

Answer-1: SQL (Structured Query Language) is a standard language used for managing and manipulating relational databases.

Answer-2: SQL statements are categorized as DDL, DML, DCL, and TCL.

Answer-3: DELETE removes rows selectively and can be rolled back. TRUNCATE removes all rows and cannot be rolled back.

Answer-4: A primary key uniquely identifies each row in a table and does not allow null values.

Answer-5: A foreign key is a column that establishes a relationship between two tables by referencing a primary key in another table.

Answer-6: WHERE filters rows before grouping; HAVING filters after grouping.

Answer-7: A join combines rows from two or more tables based on a related column.

Answer-8: Types include INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and CROSS JOIN.

Answer-9: INNER JOIN returns matching rows; OUTER JOIN returns all rows, with nulls for unmatched rows.

Answer-10: Normalization organizes data to reduce redundancy and improve integrity through normal forms.

Answer-11: Normal forms include 1NF, 2NF, 3NF, BCNF, and higher forms, each with stricter rules to remove redundancy.

Answer-12: A unique key ensures all values in a column or combination of columns are unique, but allows one null value.

Answer-13: CHAR is fixed-length, while VARCHAR is variable-length, using only as much space as needed.

Answer-14: An index improves query performance by allowing faster data retrieval.

Answer-15: Types include clustered, non-clustered, unique, and full-text indexes.

Answer-16: A view is a virtual table based on the result of an SQL query, used for abstraction and security.

Answer-17: Yes, if the view is created from a single table without aggregations or complex joins.

Answer-18: A stored procedure is a precompiled set of SQL statements that can be executed with parameters.

Answer-19: A trigger is a stored procedure that automatically executes in response to specific database events.

Answer-20: A cursor is a database object used to retrieve, manipulate, and iterate through query results row by row.

Answer-21: A subquery is a query nested inside another query to perform intermediate calculations or filters.

Answer-22: A correlated subquery depends on the outer query, while a non-correlated subquery is independent.

Answer-23: GROUP BY groups rows with the same values into summary rows, often used with aggregate functions.

Answer-24: Aggregate functions perform calculations on multiple rows, e.g., SUM, AVG, COUNT, MIN, MAX.

Answer-25: ORDER BY sorts query results in ascending (ASC) or descending (DESC) order.

Answer-26: LIMIT restricts the number of rows returned by a query, supported by most databases.

Answer-27: UNION removes duplicate rows, while UNION ALL includes duplicates.

Answer-28: A transaction is a sequence of operations performed as a single logical unit of work.

Answer-29: ACID ensures database reliability through Atomicity, Consistency, Isolation, and Durability.

Answer-30: ROLLBACK undoes changes made by a transaction if it hasn't been committed.

Answer-31: COMMIT saves changes made by a transaction permanently.

Answer-32: SAVEPOINT sets a point in a transaction to which you can later roll back.

Answer-33: A data warehouse is a centralized repository for storing and analyzing large amounts of data.

Answer-34: OLTP is for transaction processing; OLAP is for analytical querying and reporting.

Answer-35: DROP removes the table entirely; DELETE removes data but retains the table structure.

Answer-36: CASE is used for conditional logic within SQL queries.

Answer-37: SQL injection is a security vulnerability where attackers manipulate SQL queries to access data.

Answer-38: Use parameterized queries, stored procedures, and input validation to prevent SQL injection.

Answer-39: RANK allows gaps, DENSE_RANK avoids gaps, and ROW_NUMBER assigns unique sequential numbers.

Answer-40: A TEMP table stores data temporarily for the duration of a session or transaction.

Answer-41: A self-join joins a table to itself to compare rows within the same table.

Answer-42: A CTE is a temporary result set defined within a query using the WITH keyword.

Answer-43: A materialized view is a precomputed, stored query result that improves performance for complex queries.

Answer-44: NULL represents a missing or undefined value in a database column.

Answer-45: IS NULL checks for null values; = NULL is invalid because null is not equal to anything.

Answer-46: Optimize queries by indexing, avoiding SELECT *, and analyzing execution plans.

Answer-47: It provides the execution plan of a query, helping identify performance bottlenecks.

Answer-48: Window functions perform calculations across a set of rows related to the current row. Examples: OVER, RANK.

Answer-49: FULL OUTER JOIN includes all rows with nulls for unmatched rows; CROSS JOIN creates a Cartesian product.

Answer-50: Use foreign keys with ON DELETE or ON UPDATE constraints to enforce referential integrity.