Database Management Final

What is data cleaning?

The process of correcting or removing erroneous data to ensure accuracy in analysis.

What are the consequences of 'dirty' data?

Low productivity, revenue loss, stakeholder distrust, and harm to customers.

What causes erroneous data?

Human error, lack of integrity constraints, and encoding corruption.

What is the purpose of reshaping data?

To organize data into a relational structure suitable for analysis.

What is the difference between wide and long data?

Wide data has rows for subjects and columns for attributes; long data has rows for subject-attribute pairs.

What does normalization involve?

Separating columns into 3NF tables and ensuring primary/foreign key connections.

What is de-duplication?

The process of removing duplicate rows from a dataset.

What are surrogate keys?

Artificially generated unique identifiers used when natural keys are unreliable.

What is the goal of data wrangling?

To prepare clean, consistent data for loading into a database.

What is the SQL command for creating a new table?

CREATE TABLE.

What is the purpose of Data Definition Language (DDL)?

To define and modify database structure, including tables and constraints.

What are the three families of SQL commands?

Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL).

What is the syntax for creating a table in SQL?

CREATE TABLE table_name (field_definitions);

What is referential integrity?

A set of rules that ensures relationships between tables remain consistent.

What is the purpose of field constraints in SQL?

To specify rules for data entry, such as uniqueness or required fields.

What is the DROP TABLE command used for?

To delete a table from the database.

What is the significance of primary keys in a database?

They uniquely identify each record in a table.

What is the difference between DML and DDL?

DML is for manipulating data (e.g., INSERT, UPDATE), while DDL is for defining database structure (e.g., CREATE, ALTER).

What is the purpose of data type constraints?

To ensure that the data entered into a field matches the expected type.

What is the function of encoding in data management?

To convert data into a specific format for storage and retrieval.

What is flattening in data reshaping?

Resolving multivalued attributes to fit data into a 2D, tabular structure.

What are typographical errors in data?

Mistakes in data entry, such as misspellings or incorrect formatting.

What is the purpose of the SQL command GRANT?

To give user privileges to access or manipulate database objects.

What does the term 'data decomposition' refer to?

Breaking down data into smaller, normalized relations.

What is the importance of constraints in data entry?

To prevent errors and ensure data integrity during input.

What is the 'Marty McFly Problem' in database management?

The issue that arises when referential integrity is violated, such as deleting parent records before child records.

What is the role of SQL in database management?

To create, manipulate, and control access to relational databases.

What are illogical values in data?

Data points that are impossible or nonsensical, such as negative ages.

What is the purpose of the SQL command SELECT?

To retrieve data from a database.

What is the purpose of the CREATE TABLE statement?

To define a new table and its structure in a database.

What are the naming rules for SQL tables?

Table names must be ≤ 30 characters, start with a letter, contain no spaces or reserved words, and be unique.

What is the difference between CHAR and VARCHAR2 data types?

CHAR is a fixed-length string, while VARCHAR2 is a variable-length string.

What does the NUMBER data type represent?

Numeric values, where precision and scale can be defined.

What is the purpose of the NOT NULL constraint?

It ensures that a column cannot have a NULL value.

What does the UNIQUE constraint enforce?

It ensures that all values in a column are distinct.

What is a PRIMARY KEY?

A constraint that uniquely identifies each row in a table.

What is a FOREIGN KEY?

A constraint that enforces a relationship between two tables.

What SQL command is used to modify an existing table's structure?

ALTER TABLE

What does the DROP TABLE command do?

It deletes a table and all its data from the database.

What is the purpose of the INSERT command?

To add new records into a table.

What is the syntax for inserting multiple rows into a table?

INSERT ALL INTO table_name VALUES (...) INTO table_name VALUES (...) SELECT * FROM DUAL;

What does the UPDATE command do?

It modifies existing records in a table.

What happens if you run a DELETE command without a WHERE clause?

It deletes all rows from the table but keeps the structure.

What is the purpose of the SELECT command?

To retrieve records from a database.

What is the function of the WHERE clause in SQL?

It specifies filtering criteria for rows of data.

What does the GROUP BY clause do?

It divides data into subsets based on values in one or more fields.

What is the HAVING clause used for?

It specifies filtering criteria for groups of data, often using aggregate functions.

What is the purpose of the ORDER BY clause?

To sort the results of a query.

What does the OFFSET clause do in a SQL query?

It skips a specified number of rows before returning the result set.

What is the FETCH FIRST clause used for?

To limit the number of rows returned by a query.

What is a literal in SQL?

A fixed data value, such as 'Danny' or 150.

What is a logic error in SQL?

Code that runs but produces unintended results.

What is the purpose of the TRUNCATE command?

To delete all rows from a table while keeping its structure.

What does the COMMENT command do in SQL?

It adds notes or documentation to database objects.

What is the syntax for a basic SELECT statement?

SELECT column1, column2, ... FROM table_name [WHERE condition];

What is the significance of ending SQL statements with a semicolon?

It signifies the end of the SQL statement.

What SQL command is used to rename or alias columns?

Use AS to rename or alias columns.

How can you remove duplicates in a SQL query?

Use DISTINCT to remove duplicates.

What is the order of operations in SQL?

Parentheses → Multiplication/Division → Addition/Subtraction.

What operator is used for concatenation in SQL?

The concatenation operator is ||.

What is the purpose of the WHERE clause in SQL?

Filters rows using logical conditions.

What is the difference between WHERE and HAVING clauses?

WHERE filters individual rows; HAVING filters groups after aggregation.

What does the INNER JOIN do?

Joins two tables based on matching values in shared columns, excluding non-matching rows.

What is a CROSS JOIN?

Joins two tables without specifying common columns, producing a Cartesian product.

What is a LEFT JOIN?

Keeps all rows from the left table, adding NULLs for unmatched rows from the right table.

What is a FULL OUTER JOIN?

Keeps all rows from both tables, including unmatched rows.

What is a self join?

Joins a table to itself, often requiring an alias to differentiate copies.

What is the execution order of a SQL query?

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → OFFSET/FETCH.

What does the function COUNT() do?

Counts the number of rows in a group.

What does the function AVG() calculate?

Calculates the average value of a numeric column.

What is the purpose of the JOIN condition?

Determines how rows are matched across tables.

What is an equi-join?

A join type that matches rows where values are equal.

What is a natural join?

Joins tables automatically based on columns with the same name.

What is referential integrity in the context of joins?

Ensures that matching key values are valid.

When should you prefer JOIN...ON syntax?

For clarity and flexibility in joins.

What is the risk of using a natural join?

It can lead to errors if tables have homonymous fields.

What does the OFFSET clause do?

Skips a specified number of rows in the result set.

What is the purpose of the FETCH clause?

Limits the number of rows returned in the result set.

What is the significance of using parentheses in SQL?

They clarify the order of operations in complex queries.

What is the result of a CROSS JOIN?

Generates all combinations of rows from the joined tables.

What is the difference between mandatory and optional relationships in joins?

Mandatory requires matching rows; optional allows for unmatched rows.

What SQL function would you use to find the square root of a number?

Use the SQRT function.

What is a Right Join?

Keeps all records from the right (second) table in the join, even if there are no matching values in the left table.

What are Set Operators in SQL?

Used to combine the results of multiple queries into compound queries.

What does the UNION operator do?

Combines all rows from the results of both queries, removing duplicate rows.

What is the difference between UNION and UNION ALL?

UNION removes duplicates, while UNION ALL includes all duplicates.

What does the INTERSECT operator do?

Returns common rows from the results of both queries, removing duplicates.

What does the MINUS operator do?

Returns rows from the results of the first query that are not contained in the results of the second query, removing duplicates.

What is a Scalar Subquery?

A subquery that returns exactly one value (one row, one column).

What is a Rows Subquery?

A subquery that returns multiple rows from one column, often used with IN, NOT IN, ANY, or ALL.

What is a Table Subquery?

A subquery that returns multiple rows and multiple columns, acting as a temporary table.

What is a Correlated Subquery?

A subquery that depends on outer query values and runs once per row.

What is the execution order of compound queries?

Each query runs individually, and results are combined using the set operator.

What does the CASE statement do in SQL?

Evaluates conditions and returns different values depending on which condition is true.

Where can CASE statements be used?

Anywhere an expression is allowed: SELECT, ORDER BY, GROUP BY, WHERE, HAVING.

What is the purpose of using parentheses in compound queries?

To control the order of execution.

What is the main purpose of a Compound Query?

To combine results of multiple SELECT queries vertically (stacking rows).

What is the difference between a Compound Query and a Subquery?

A Compound Query combines queries side by side, while a Subquery is nested inside another query.

What is meant by union compatibility?

Queries must return the same number of columns and matching data types and order.

What does the term 'absolute filtering' refer to in SQL?

Finding instances where specific criteria are always met or never met.