Database & SQL Quiz

Your Answer is:

Second Normal Form (2NF) eliminates partial dependencies by ensuring that all non-key attributes are fully functionally dependent on the entire primary key. This is particularly important for tables with composite primary keys, where some attributes might depend on only part of the key.

Your Answer is:

The HAVING clause is used to filter groups based on a condition after the GROUP BY clause has been applied. While WHERE filters individual rows before grouping, HAVING filters groups based on aggregate functions or group properties.

Your Answer is:

A foreign key is used to establish a link between tables by referencing the primary key of another table. This creates a relationship between the tables and helps maintain referential integrity, ensuring that the linked data remains consistent across the database.

Your Answer is:

LEFT JOIN returns all records from the left table and the matched records from the right table. If there is no match, the result is NULL on the right side. This is useful when you want to preserve all records from the primary table regardless of whether there's a match in the secondary table.

Your Answer is:

ACID stands for Atomicity, Consistency, Isolation, and Durability. These are the four key properties that guarantee database transactions are processed reliably. Atomicity ensures that all operations in a transaction are completed successfully as a group; Consistency ensures that the database remains in a valid state after the transaction; Isolation ensures that concurrent transactions don't interfere with each other; and Durability ensures that once a transaction has been committed, it will remain committed even in the event of a system failure.

Your Answer is:

The DROP TABLE command is used to completely remove a table from a database, including its structure, data, indexes, triggers, constraints, and permissions. This is a permanent action and cannot be undone. In contrast, DELETE removes only the data from a table, and TRUNCATE removes all data but keeps the table structure.

Your Answer is:

A primary key is a unique identifier for each record in a table. It must contain unique values and cannot contain null values. Each table can have only one primary key, which may consist of single or multiple columns. The primary key constraint uniquely identifies each record in a table and provides a way to access the data efficiently.

Your Answer is:

DIAGONAL JOIN is not a standard type of SQL join. The common types of SQL joins include INNER JOIN, LEFT JOIN (or LEFT OUTER JOIN), RIGHT JOIN (or RIGHT OUTER JOIN), FULL OUTER JOIN, and CROSS JOIN. Each join type has a specific purpose for combining rows from two or more tables based on related columns.

Your Answer is:

Denormalization is the process of adding redundancy to a database to improve performance. It's the opposite of normalization and is often used in data warehousing and reporting systems where read performance is more important than write performance. By strategically adding redundant data or grouping data, denormalization can reduce the number of joins needed for complex queries, thus improving query speed.

Your Answer is:

The COUNT() function returns the number of rows that match a specified criterion. COUNT(*) counts all rows in a table, while COUNT(column_name) counts the number of rows where the specified column is not null. This function is commonly used to determine the size of a result set or to count occurrences of specific values.

Your Answer is:

A transaction is a sequence of operations performed as a single logical unit of work. All operations within a transaction must be completed successfully; if any operation fails, the entire transaction fails and the database is rolled back to its previous state. Transactions ensure data integrity and consistency by following the ACID properties (Atomicity, Consistency, Isolation, Durability).

Your Answer is:

The UPDATE statement is used to modify existing records in a table. It allows you to change the values in one or more columns for one or more rows based on a specified condition. The basic syntax is: UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; Without a WHERE clause, all rows in the table will be updated.

Your Answer is:

A deadlock is a situation where two or more transactions are waiting for each other to release locks, creating a cycle of dependencies that prevents any of the transactions from proceeding. For example, Transaction A might be holding a lock on Resource 1 and waiting for Resource 2, while Transaction B is holding a lock on Resource 2 and waiting for Resource 1. Database management systems typically include deadlock detection mechanisms to identify and resolve such situations.

Your Answer is:

STRING is not a standard SQL data type. While some database systems might support STRING as an alias for other text types, the standard SQL data types for text include CHAR, VARCHAR, TEXT, and others depending on the specific database system. BOOLEAN, VARCHAR, and DECIMAL are all valid SQL data types in most database systems.

Your Answer is:

An index is a data structure that improves the speed of data retrieval operations on a table at the cost of additional writes and storage space. Indexes are used to quickly locate data without having to search every row in a table every time a table is accessed. They can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.

Your Answer is:

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: % (percent sign) represents zero, one, or multiple characters, and _ (underscore) represents a single character. For example, 'SELECT * FROM Customers WHERE City LIKE 's%'' would return all customers whose city starts with 's'.

Your Answer is:

Referential integrity is a database concept that ensures that relationships between tables remain consistent. It prevents users from adding records to a related table if there is no associated record in the primary table, deleting records from a primary table if there are matching related records, or changing primary key values in the primary table if there are matching related records. Referential integrity is typically enforced through foreign key constraints.

Your Answer is:

The SELECT statement is used to retrieve data from a database. It is the most commonly used command in SQL and allows you to specify which columns you want to retrieve, from which table, and under what conditions. The basic syntax is: SELECT column1, column2, ... FROM table_name WHERE condition; You can also use SELECT with various clauses like JOIN, GROUP BY, HAVING, and ORDER BY to perform complex queries.

Your Answer is:

A view is a virtual table based on the result-set of an SQL statement. It contains rows and columns, just like a real table, but the fields in a view are fields from one or more real tables in the database. Views are used to simplify complex queries, restrict access to data, and present data in a different format from the base tables. Views do not store data themselves; they store the query that generates the data.

Your Answer is:

Third Normal Form (3NF) addresses transitive dependencies. A transitive dependency exists when a non-key attribute is functionally dependent on another non-key attribute. To be in 3NF, a table must first be in 2NF, and all non-key attributes must be directly dependent on the primary key, not on other non-key attributes. This helps eliminate redundancy and improve data integrity.

Your Answer is:

A stored procedure is a prepared SQL code that you can save and reuse. It's a set of SQL statements with an assigned name that's stored in the database in compiled form so that it can be shared by a number of programs. Stored procedures can accept input parameters, return output parameters, and can include programming statements that perform operations in the database.

Your Answer is:

The ORDER BY clause is used to sort the result-set in ascending or descending order. By default, it sorts the records in ascending order. To sort the records in descending order, you can use the DESC keyword. For example, 'SELECT * FROM Customers ORDER BY LastName DESC' would return all customers sorted by last name in descending order.

Your Answer is:

A cursor is a database object used to manipulate data in a set on a row-by-row basis. It's essentially a pointer to a specific row in a result set. Cursors allow you to iterate through the results of a query one row at a time, rather than processing the entire result set at once. While powerful, cursors can be resource-intensive and should be used judiciously, as set-based operations are generally more efficient in SQL.

Your Answer is:

The INSERT INTO statement is used to add new rows to a table. The basic syntax is: INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); You can also insert data from another table using a SELECT statement. This command is fundamental for populating tables with data in a database.

Your Answer is:

A trigger is a stored procedure that automatically executes when an event occurs in the database. DML triggers execute when a user tries to modify data through a DML event (INSERT, UPDATE, or DELETE) on a table or view. DDL triggers execute in response to a variety of Data Definition Language (DDL) events, such as CREATE, ALTER, or DROP statements. Triggers are used to enforce business rules, maintain data integrity, and audit changes.

Your Answer is:

The UNION operator is used to combine the result-set of two or more SELECT statements. Each SELECT statement within UNION must have the same number of columns, the columns must have similar data types, and the columns in each SELECT statement must be in the same order. The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.

Your Answer is:

A composite key is a key that consists of two or more columns in a table that can be used to uniquely identify each row in the table when the columns are combined, but individually do not guarantee uniqueness. Composite keys are often used when a single column is not sufficient to uniquely identify records. They can be used as primary keys or as part of a foreign key relationship.

Your Answer is:

The CREATE TABLE statement is used to create a new table in a database. The basic syntax is: CREATE TABLE table_name (column1 datatype, column2 datatype, column3 datatype, ...); You can also include constraints like PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, and CHECK when creating a table. This statement defines the structure of the table, including column names, data types, and any constraints.

Your Answer is:

A candidate key is a set of one or more columns that can uniquely identify a record in a table. A table can have multiple candidate keys, but only one of them can be chosen as the primary key. The remaining candidate keys are called alternate keys. For example, in a table of employees, both EmployeeID and SocialSecurityNumber might be candidate keys, but only one would be selected as the primary key.

Your Answer is:

Different database systems use different functions to return the current date and time. MySQL uses NOW(), SQL Server uses GETDATE(), Oracle uses SYSDATE, and PostgreSQL uses CURRENT_TIMESTAMP or NOW(). Some systems also have separate functions for just the date (CURRENT_DATE) or just the time (CURRENT_TIME). The specific function to use depends on the database system you're working with.

Your Answer is:

A many-to-many relationship exists when many records in a table are related to many records in another table. In relational databases, many-to-many relationships are implemented using a junction table (also known as an associative or linking table) that contains foreign keys referencing the primary keys of the two tables involved in the relationship. For example, in a database of students and courses, a many-to-many relationship exists because a student can enroll in multiple courses, and a course can have multiple students.

Your Answer is:

The ALTER TABLE command is used to add, delete, or modify columns in an existing table. It's also used to add and drop various constraints on an existing table. Common operations include ADD COLUMN, DROP COLUMN, MODIFY COLUMN (to change a column's data type), ADD CONSTRAINT, and DROP CONSTRAINT. This command is essential for maintaining and evolving database schemas over time.

Your Answer is:

A surrogate key is a unique identifier for a record in a table that is not derived from the data itself and has no business meaning. It's typically an auto-incrementing integer or a GUID that is generated by the database system. Surrogate keys are often used as primary keys because they are stable, unique, and compact, making them efficient for indexing and joining tables. They contrast with natural keys, which are derived from the data itself (like a Social Security Number or ISBN).

Your Answer is:

Different database systems use different syntax to limit the number of rows returned by a query. MySQL and PostgreSQL use LIMIT, SQL Server uses TOP, and Oracle uses ROWNUM in a WHERE clause. For example, in MySQL you would use 'SELECT * FROM Customers LIMIT 10', while in SQL Server you would use 'SELECT TOP 10 * FROM Customers'. These clauses are useful for pagination or when you only need a subset of the results.

Your Answer is:

A non-clustered index is an index that stores pointers to the actual data rows rather than the data itself. It contains the indexed columns and a pointer (or bookmark) to the location of the full data row. Unlike a clustered index, which determines the physical order of data in a table, a non-clustered index is a separate structure from the data rows. A table can have multiple non-clustered indexes, which can improve query performance for various search patterns.

Your Answer is:

The DELETE statement is used to delete existing records in a table. The basic syntax is: DELETE FROM table_name WHERE condition; It's important to include a WHERE clause to specify which records to delete, as omitting the WHERE clause will delete all records in the table. DELETE operations can be rolled back if they are part of a transaction, and they trigger any DELETE triggers that have been defined on the table.

Your Answer is:

A hierarchical database model is a data model in which data is organized into a tree-like structure. The data is stored as records which are connected to one another through links. Each record has a single parent record and may have multiple child records, creating a parent-child relationship. This model was popular in early mainframe database management systems and is still used in certain applications like Windows Registry and XML documents.

Your Answer is:

The GRANT command is used to give specific permissions or privileges to users or roles in a database. The basic syntax is: GRANT privilege_name ON object_name TO user_name; Common privileges include SELECT, INSERT, UPDATE, DELETE, EXECUTE, and ALL PRIVILEGES. The REVOKE command is used to remove previously granted permissions. These commands are essential for managing database security and controlling access to data.

Your Answer is:

A natural key is a key that is derived from the data itself and has business meaning. It's a real-world identifier that exists outside the database, such as a Social Security Number, ISBN, or vehicle identification number. Natural keys contrast with surrogate keys, which are artificially generated by the database system and have no business meaning. While natural keys can be more intuitive, they can be problematic if they change over time or if they contain sensitive information.

Your Answer is:

The TRUNCATE TABLE command is used to remove all data from a table but keep its structure. It's a faster alternative to DELETE when you want to remove all rows from a table, as it doesn't log individual row deletions. TRUNCATE is a DDL (Data Definition Language) command, while DELETE is a DML (Data Manipulation Language) command. Unlike DELETE, TRUNCATE cannot be rolled back (in most database systems) and doesn't fire DELETE triggers. It also resets any identity columns to their seed value.