Advanced SQL 1

What is SQL?

SQL (Structured Query Language) is a standard programming language used to communicate with relational databases for creating, reading, updating, and deleting data, and for managing database schema and security.

What is a database?

A database is an organized collection of data, typically structured in tables with rows and columns, managed by a Database Management System (DBMS) for efficient storage, retrieval, and manipulation.

What are the main types of SQL commands?

DDL (Data Definition Language - CREATE, ALTER, DROP, TRUNCATE), DML (Data Manipulation Language - SELECT, INSERT, UPDATE, DELETE), DCL (Data Control Language - GRANT, REVOKE), and TCL (Transaction Control Language - COMMIT, ROLLBACK, SAVEPOINT).

What is the difference between CHAR and VARCHAR2 data types?

CHAR is fixed-length, padded with spaces if not fully used. VARCHAR2 is variable-length, storing only the actual data, saving space.

What is a primary key?

A primary key is a unique identifier for each record in a table, ensuring no two rows have the same value and disallowing NULL values.

What is a foreign key?

A foreign key is a column (or set of columns) in one table that refers to the primary key in another table, establishing and enforcing relationships and data integrity.

What is the purpose of the DEFAULT constraint?

The DEFAULT constraint assigns a specified default value to a column when no value is explicitly provided during an INSERT operation.

What is normalization in databases?

Normalization is the process of organizing data to reduce redundancy and improve data integrity by dividing large tables into smaller, related tables and defining relationships.

What is denormalization, and when is it used?

Denormalization is combining normalized tables for performance reasons, used when complex queries and joins are slow and the performance gain outweighs redundancy drawbacks.

What is a query in SQL?

A query is a SQL statement used to retrieve, update, or manipulate data in a database, most commonly a SELECT statement to fetch data.

What are the different operators available in SQL?

Arithmetic (+, -, *, /, %), Comparison (=, !=, <>, >,

What is a view in SQL?

A view is a virtual table created by a SELECT query that does not store data but presents data from one or more tables, simplifying queries and enhancing security.

What is the purpose of the UNIQUE constraint?

The UNIQUE constraint ensures that all values in a column (or combination of columns) are distinct, preventing duplicate entries.

What are the different types of joins in SQL?

INNER JOIN, LEFT JOIN (LEFT OUTER JOIN), RIGHT JOIN (RIGHT OUTER JOIN), FULL JOIN (FULL OUTER JOIN), and CROSS JOIN.

What is the difference between INNER JOIN and OUTER JOIN?

INNER JOIN returns only rows with matches in both tables. OUTER JOIN (Left, Right, Full) returns all rows from one table and matching rows from the other, with NULLs for non-matches.

What is the purpose of the GROUP BY clause?

The GROUP BY clause is used to arrange identical data into groups, typically with aggregate functions (COUNT, SUM, AVG) to perform calculations on each group.

What are aggregate functions in SQL?

Aggregate functions perform calculations on a set of values and return a single value (e.g., COUNT(), SUM(), AVG(), MIN(), MAX()).

What is a subquery?

A subquery is a query nested within another query, often used in the WHERE clause to filter data based on another query's results.

What is the difference between the WHERE and HAVING clauses?

WHERE filters individual rows before grouping. HAVING filters grouped data after the GROUP BY clause has been applied.

What are indexes, and why are they used?

Indexes are database objects that improve query performance by allowing faster retrieval of rows, acting like a book's index. They speed up reads but can slow down writes.

What is the difference between DELETE and TRUNCATE commands?

DELETE removes rows one by one, logs each deletion, and allows rollback; it can use a WHERE clause. TRUNCATE removes all rows at once, doesn't log individual deletions, is faster for large data sets, and cannot use a WHERE clause.

What is the purpose of the SQL ORDER BY clause?

The ORDER BY clause sorts the result set of a query in ascending (default) or descending order based on one or more columns.

What are the differences between SQL and NoSQL databases?

SQL databases use structured tables with fixed schemas and ACID properties. NoSQL databases use flexible, schema-less structures (e.g., key-value, document) designed for horizontal scaling and often prioritize performance/scalability over strict consistency.

What is a table in SQL?

A table is a structured collection of related data organized into rows (records) and columns (attributes).

What are the types of constraints in SQL?

NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT constraints.

What is a cursor in SQL?

A cursor is a database object used to retrieve, manipulate, and traverse through rows in a result set one row at a time, useful for sequential processing.

What is a trigger in SQL?

A trigger is a set of SQL statements that automatically execute in response to specific events (INSERT, UPDATE, DELETE) on a table, used to maintain data consistency and enforce business rules.

What is the purpose of the SQL SELECT statement?

The SELECT statement retrieves data from one or more tables, allowing filtering, sorting, and display of data based on criteria.

What are NULL values in SQL?

NULL represents a missing or unknown value; it is distinct from zero or an empty string and indicates data is not available or applicable.

What is a stored procedure?

A stored procedure is a precompiled set of SQL statements stored in the database that can take parameters, perform logic, and return results, improving performance and maintainability.

What is the purpose of the ALTER command in SQL?

The ALTER command modifies the structure of an existing database object, used to add/drop columns, change data types, add/remove constraints, or rename objects.

What is a composite primary key?

A composite primary key is a primary key composed of two or more columns whose combined values uniquely identify each row.

How is data integrity maintained in SQL databases?

Data integrity is maintained through constraints (NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT) that enforce rules and prevent invalid/inconsistent data.

What is the purpose of the CASE statement?

The CASE statement implements conditional logic (like if-else) in SQL queries, evaluating conditions and returning a result based on the first true condition.

What are scalar functions in SQL?

Scalar functions operate on individual values and return a single value, used for formatting or converting data (e.g., LEN(), ROUND(), CONVERT()).

What is the purpose of the COALESCE function?

The COALESCE function returns the first non-NULL value from a list of expressions, commonly used to provide default values or handle missing data.

What are the differences between SQL's COUNT() and SUM() functions?

COUNT() counts the number of rows or non-NULL values. SUM() adds up all numeric values in a column.

What is the difference between the NVL and NVL2 functions?

NVL() replaces a NULL value with a specified replacement. NVL2() returns a second argument if the first is NOT NULL, otherwise returns a third argument if the first is NULL.

How does the RANK() function differ from DENSE_RANK()?

RANK() assigns ranks with gaps if there are ties. DENSE_RANK() assigns consecutive ranks without gaps, even with ties.

What is the difference between ROW_NUMBER() and RANK()?

ROW_NUMBER() assigns a unique sequential number to each row regardless of ties. RANK() assigns the same number to tied rows and skips subsequent numbers.

What is the difference between an index and a key?

An index is a physical structure for faster data lookup. A key is a logical concept that enforces rules for uniqueness or relationships (e.g., PRIMARY KEY, FOREIGN KEY).

How does indexing improve query performance?

Indexing allows the database to locate and access rows much faster by directly jumping to relevant data pages, reducing disk I/O.

What are the trade-offs of using indexes in SQL databases?

Advantages include faster SELECT queries. Disadvantages include increased storage, and overhead for write operations (INSERT, UPDATE, DELETE) as indexes must be updated.

What is the difference between clustered and non-clustered indexes?

A clustered index organizes the physical data in the table itself (only one per table). A non-clustered index maintains a separate structure with pointers to the data (multiple per table).

What are temporary tables, and how are they used?

Temporary tables exist only for the duration of a session or transaction, used for intermediate results or simplifying complex queries. They can be local (#) or global (##).

What is a materialized view, and how does it differ from a standard view?

A standard view is virtual and executes its query each time, showing real-time data. A materialized view is a physical table storing the query result, offering faster reads but requiring periodic refreshes.

What is a sequence in SQL?

A sequence is a database object that generates a series of unique numeric values, often used for unique identifiers like primary keys.

What are the advantages of using sequences over identity columns?

Sequences offer greater flexibility (start values, increments, max values), dynamic adjustment, and cross-table consistency compared to identity columns.

How do constraints improve database integrity?

Constraints enforce rules on data, preventing invalid or inconsistent entries (e.g., NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK), thereby maintaining data reliability.

What is the difference between a local and a global temporary table?

Local temporary tables (prefixed with #) are visible only to the session that created them and are dropped when the session ends. Global temporary tables (prefixed with ##) are visible to all sessions and are dropped when all referencing sessions close.

What is the purpose of the SQL MERGE statement?

The MERGE statement combines INSERT, UPDATE, and DELETE operations into one, useful for synchronizing two tables by handling new, updated, and deleted records efficiently.

How can you handle duplicates in a query without using DISTINCT?

By using GROUP BY with aggregate functions, or by using window functions like ROW_NUMBER() with a Common Table Expression (CTE) to filter for the first occurrence.

What is a correlated subquery?

A correlated subquery is a subquery that references columns from the outer query and is re-executed for each row processed by the outer query, making it dynamic but potentially less efficient.