Unlock hundreds more features
Save your Quiz to the Dashboard
View and Export Results
Use AI to Create Quizzes and Analyse Results

OR
Sign inSign in with Facebook
Sign inSign in with Google
Quizzes > Computers & Technology > Programming & Software

Functions of Relational Databases Quiz

Think you know relational database management? Take the quiz and prove your SQL mastery!

Difficulty: Moderate
2-5mins
Learning OutcomesCheat Sheet
Paper art database cylinders and server stacks on dark blue background representing SQL relational database quiz

This quiz helps you practice the functions of relational databases, from SQL queries to core RDBMS rules. Work through questions on SELECT, joins, keys, constraints, transactions, and indexes so you can spot gaps before an exam or interview. You can also explore the fundamentals quiz or go deeper with the management quiz .

What is the purpose of a primary key in a relational database table?
Encrypt table data
Define foreign key relationships
Uniquely identify each record in the table
Allow duplicate records for performance
A primary key uniquely identifies each row in a table, ensuring records can be distinguished. It must be unique and not null. This constraint is core to relational integrity.
Which key establishes a relationship between two tables in a relational database?
Surrogate key
Foreign key
Candidate key
Primary key
A foreign key in one table refers to the primary key of another table, creating a relationship between them. It enforces referential integrity by ensuring that the referenced record exists. This linkage is fundamental in relational design.
Which SQL clause is used to retrieve data from a database table?
INSERT
UPDATE
DELETE
SELECT
The SELECT clause is used to query and retrieve data from one or more tables. It can specify columns, calculated fields, and filtering criteria. It's the primary read operation in SQL.
What type of SQL commands does DDL (Data Definition Language) include?
CREATE, ALTER, DROP
GRANT, REVOKE
SELECT, INSERT, UPDATE
BEGIN, COMMIT, ROLLBACK
DDL commands define and modify database structures: CREATE for new objects, ALTER to change existing ones, and DROP to remove them. They are distinct from DML commands, which handle data manipulation. These operations impact schema.
Which of the following is NOT one of the CRUD operations?
Delete
Create
Compile
Update
CRUD stands for Create, Read, Update, and Delete, which are the four basic data operations. 'Compile' is not related to data manipulation within a database. CRUD maps to SQL commands INSERT, SELECT, UPDATE, and DELETE.
What is the main goal of database normalization?
Optimize network protocols
Encrypt stored data
Increase data duplication for backup
Reduce data redundancy and improve integrity
Normalization organizes tables to minimize duplicate data and ensure data dependencies make sense. It involves decomposing tables into smaller ones and defining relationships. This improves data integrity and reduces anomalies.
Which SQL clause is used to filter rows based on specified conditions?
GROUP BY
ORDER BY
HAVING
WHERE
The WHERE clause filters individual rows before grouping or ordering. It specifies conditions that rows must meet to be selected. It is fundamental for precise data queries.
Which SQL command adds new rows to a table?
DELETE
SELECT
UPDATE
INSERT
INSERT is the SQL command used to add one or more new records into a table. It can specify column values explicitly or use a subquery. Proper use ensures database growth management.
To change existing data in a relational table, which SQL command is used?
CHANGE
ALTER
MODIFY
UPDATE
The UPDATE statement modifies existing records in a table based on a condition. It can update one or multiple rows and specify new values for one or more columns. It's essential for maintaining current data.
Which SQL statement is used to remove records from a table?
REMOVE
DROP
TRUNCATE
DELETE
DELETE removes rows that satisfy a specified condition but keeps the table structure intact. Unlike DROP, it does not delete the entire table. TRUNCATE also removes all rows but is a DDL operation.
Which SQL function returns the number of rows in a result set?
COUNT()
SUM()
AVG()
MIN()
COUNT() computes the total number of rows returned by a query. It can count all rows or only non-null values in a column when specified. It's widely used for simple row counts and analytics. W3Schools Aggregate Functions
What does the DISTINCT keyword do in a SELECT statement?
Groups rows
Removes duplicate rows from the result set
Sorts the result set
Counts rows
DISTINCT filters out duplicate rows, returning only unique results for the selected columns. It is useful when you need unique values only. Overuse can impact performance on large datasets.
Which SQL keyword allows you to assign a temporary name to a table or column in a query?
LABEL
NAME
AS
ALIAS
The AS keyword is used to create an alias for a column or table in a query, making results more readable. Although the AS keyword is optional in some systems, it improves clarity. Aliases exist only for the duration of the query.
What is the main difference between an INNER JOIN and a LEFT OUTER JOIN?
LEFT OUTER JOIN excludes all matched rows
INNER JOIN returns rows from right table only
INNER JOIN includes unmatched rows
LEFT OUTER JOIN includes all rows from left table even without match
INNER JOIN returns only rows with matching values in both tables. LEFT OUTER JOIN returns all rows from the left table and matching rows from the right, filling with NULLs when no match exists. This controls result completeness.
Which clause groups rows that have the same values into summary rows?
WHERE
GROUP BY
ORDER BY
HAVING
GROUP BY aggregates rows sharing the same column values into summary rows, often used with aggregate functions. ORDER BY merely sorts the result set. GROUP BY precedes HAVING, which filters grouped records.
When would you use the HAVING clause in SQL?
To sort the result set
To filter groups after aggregation
To filter rows before grouping
To rename columns
HAVING applies conditions to grouped rows created by GROUP BY. It filters on aggregate values, whereas WHERE filters individual rows before grouping. This allows complex aggregated data queries.
Which method can sometimes be more efficient than a subquery for combining data from two tables?
INTERSECT
JOIN
EXISTS
UNION
JOIN operations often allow the database engine to optimize data retrieval more effectively than subqueries by combining tables directly. Joins can use indexes and reduce nested execution. However, performance depends on the queries and database.
What is the primary benefit of creating an index on a database column?
Reduced storage use
Automatic backups
Improved data integrity
Faster data retrieval
Indexes speed up query performance by allowing quick location of rows without scanning the entire table. They may slow writes slightly due to maintenance overhead. Proper indexing balances read/write performance.
A composite key is a primary key made up of:
Auto-incremented values
A single unique column
More than one column
Foreign key constraints
A composite key uses a combination of two or more columns to uniquely identify a record. It's useful when no single column is sufficient for uniqueness. The combination must be unique together.
Which ACID property ensures that a transaction is fully completed or fully rolled back?
Consistency
Durability
Atomicity
Isolation
Atomicity guarantees that a transaction is an indivisible unit: all operations succeed or none are applied. This prevents partial updates. It's critical to maintain database integrity.
If an error occurs during a transaction, which command reverses all changes made in that transaction?
UNDO
REVERT
CANCEL
ROLLBACK
ROLLBACK undoes all operations in the current transaction, restoring the database to its prior state. It is used when errors or conflicts are detected. This preserves data consistency.
What is the key difference between CHAR and VARCHAR data types?
CHAR has fixed length, VARCHAR varies
VARCHAR is deprecated
CHAR varies length, VARCHAR fixed
CHAR stores numbers only
CHAR allocates a fixed-length string, padding with spaces if needed. VARCHAR allocates variable length up to a defined maximum. VARCHAR saves space for shorter strings.
What does referential integrity ensure in a relational database?
Encryption of sensitive data
Consistency of foreign key relationships
Faster query performance
Automatic index creation
Referential integrity ensures that foreign key values always reference existing primary key values. It prevents orphan records and maintains data consistency across tables. Databases enforce this via constraints.
Which constraint prohibits duplicate values in a column but allows nulls?
CHECK
DEFAULT
PRIMARY KEY
UNIQUE
The UNIQUE constraint enforces that all values in a column are distinct but allows one or more NULLs depending on DBMS. PRIMARY KEY also prohibits NULLs. UNIQUE is useful for alternate keys.
What is the purpose of a DEFAULT constraint in SQL?
Encrypt column data
Enforce referential integrity
Provide a default value when none is supplied
Calculate running totals
A DEFAULT constraint assigns a predefined value to a column when INSERT statements omit it. This ensures data completeness and consistency. Defaults can be constants or functions.
What is one main use of a database view?
Replace tables entirely
Store indexes
Execute batch jobs
Encapsulate complex queries for reuse
Views act as virtual tables defined by a query, simplifying complex SELECTs and improving security by exposing limited columns. They do not store data physically. Views support code reuse and abstraction.
Which condition must a table satisfy to be in Third Normal Form (3NF)?
No foreign keys
No transitive dependencies
All columns stored together
Only single-column primary keys
3NF requires that all non-key columns depend only on the primary key, eliminating transitive dependencies. This avoids indirect relationships between non-key columns. It enhances data integrity.
In Second Normal Form (2NF), which type of dependency must be eliminated?
Multivalued dependencies
Transitive dependencies
Functional dependencies
Partial dependencies
2NF demands removal of partial dependencies where non-key columns depend on part of a composite key. Tables must first be in 1NF, then be decomposed to eliminate partial key dependencies. This reduces data redundancy.
What is a requirement for a table to be in First Normal Form (1NF)?
All columns nullable
Only one table per database
Atomic, indivisible column values
No primary key
1NF requires that each column holds atomic values with no repeating groups or arrays. This ensures each field contains only one piece of data. It's the basis for higher normal forms.
How is a many-to-many relationship typically implemented in a relational database?
Using a view
With a single foreign key
Using a junction (join) table
By duplicating tables
A junction table contains foreign keys referencing the two related tables, converting many-to-many into two one-to-many relationships. This aligns with relational schema principles. It manages associations without redundancy.
Which SQL clause introduces window functions for analytics?
WINDOW
FRAME
PARTITION BY
OVER()
The OVER() clause applies a window function over a defined row set. It optionally includes PARTITION BY to segment data and ORDER BY to define order. Window functions compute aggregates without collapsing rows. PostgreSQL Window Functions
What is a database trigger?
An index type
A saved view
A procedure automatically executed on certain events
A type of primary key
A trigger is a stored program that runs automatically in response to specified events like INSERT, UPDATE, or DELETE. Triggers enforce complex business rules and maintain audit trails. They operate at row or statement level.
Which is a key difference between stored procedures and functions in SQL?
Functions cannot accept parameters
Procedures must be scalar
Functions return a value and can be used in SELECT statements
Procedures always return values
Functions return a value and are allowed in SQL expressions like SELECT. Stored procedures do not have to return a value and are called with EXECUTE/ CALL. Both can accept parameters. SQL Server Procedures vs Functions
What behavior does the READ COMMITTED isolation level prevent?
Phantom reads
Lost updates
Non-repeatable reads
Dirty reads
READ COMMITTED ensures only committed data is read, preventing dirty reads. However, it does not protect against non-repeatable reads or phantom reads. This level balances consistency and concurrency.
Which practice helps prevent deadlocks in a relational database?
Access resources in a consistent order
Avoid indexing
Use larger transactions
Enable autocommit always
Accessing locks in a consistent sequence across transactions reduces circular wait conditions that cause deadlocks. Smaller transactions and proper indexing help, but ordering is key. Deadlock detection and resolution also assist.
What are optimizer hints used for in SQL queries?
Influence the query execution plan
Schedule batch jobs
Define user permissions
Encrypt queries
Optimizer hints guide the database engine to choose or avoid specific execution plans. They can improve performance for complex queries but should be used sparingly. They override default optimizer behavior.
How does a materialized view differ from a regular view?
It is faster to create
It cannot be indexed
It updates automatically with every query
It stores actual data rather than computing on the fly
Materialized views cache query results physically, improving read performance at the cost of storage and data freshness. Regular views compute results on each query execution. Refresh strategies can be manual or automatic.
What is a clustering index?
An index that prevents row deletion
An index that determines physical row order
An index on a computed column
An index stored in cache only
A clustering index defines how rows are stored on disk in order of the indexed column. This can speed range queries but may slow random inserts. Each table can have only one clustering index.
Which referential action causes related child rows to be deleted when a parent row is deleted?
ON DELETE RESTRICT
ON DELETE CASCADE
ON UPDATE CASCADE
ON DELETE SET NULL
ON DELETE CASCADE removes dependent child records automatically when the referenced parent record is deleted. This maintains referential integrity without orphaned rows. Other options set NULL or prevent deletion.
What mechanism does MVCC (Multi-Version Concurrency Control) use to manage concurrent transactions?
Serializes all operations
Locks entire tables
Uses global transaction locks
Maintains multiple versions of data rows
MVCC preserves data consistency by keeping previous versions of rows for reading transactions while allowing writes. Readers access stable snapshots without blocking writers. This improves concurrency and reduces lock contention.
In a distributed database, what is the purpose of the two-phase commit protocol?
Ensure atomic commitment across all nodes
Replicate data asynchronously
Repair broken network links
Encrypt data in transit
Two-phase commit coordinates all participating nodes in a transaction to either commit or abort in unison. The prepare phase gathers votes, and the commit phase finalizes based on consensus. This ensures distributed atomicity.
Which type of distributed transaction ensures consistency across multiple databases?
Temporary transaction
Autocommit transaction
XA transaction
Local transaction
XA is a two-phase commit protocol standardized for distributed transactions across heterogeneous resources. It ensures atomicity and consistency. Local or autocommit transactions don't span systems.
What is the key difference between sharding and replication?
Sharding only for backups
Replication requires manual sync
Sharding partitions data across nodes, replication copies data across nodes
Sharding copies data, replication partitions it
Sharding splits data horizontally into separate databases to distribute load. Replication duplicates the same data to multiple nodes for redundancy and read scaling. They solve different scaling challenges.
0
{"name":"What is the purpose of a primary key in a relational database table?", "url":"https://www.quiz-maker.com/QPREVIEW","txt":"What is the purpose of a primary key in a relational database table?, Which key establishes a relationship between two tables in a relational database?, Which SQL clause is used to retrieve data from a database table?","img":"https://www.quiz-maker.com/3012/images/ogquiz.png"}

Study Outcomes

  1. Identify Core Functions of Relational Databases -

    Recognize fundamental operations such as data definition, manipulation, and control examined in the functions of relational databases quiz.

  2. Explain Data Independence and Representation -

    Articulate how relational database models separate logical schemas from physical storage to achieve both logical and physical data independence.

  3. Analyze SQL Queries for Integrity -

    Break down SQL statements in the SQL and RDBMS quiz to verify proper use of primary keys, foreign keys, and integrity constraints.

  4. Apply RDBMS Core Principles to Schema Design -

    Implement normalization rules and design relational schemas that optimize performance and minimize redundancy.

  5. Evaluate Data Integrity Mechanisms -

    Assess the effectiveness of triggers, transactions, and integrity constraints in maintaining accurate and consistent data.

  6. Differentiate Relational Models from Alternatives -

    Compare and contrast flat-file, hierarchical, and network database approaches against the relational model to understand their relative strengths.

Cheat Sheet

  1. Relational Model Fundamentals -

    Data in relational databases is organized into relations (tables) composed of tuples (rows) and attributes (columns), as defined by E.F. Codd's seminal 1970 IBM paper. This core structure underpins all functions of relational databases, allowing SQL to perform set-based operations like JOINs, SELECTs, and PROJECTs with mathematical precision. A handy mnemonic is "Tables + Keys = Orderly Data."

  2. Data Independence -

    One of the hallmark functions of relational databases is separating physical storage from the logical schema, as described in the ANSI/SPARC three-schema architecture (ISO, 1975). This distinction lets you alter storage details without rewriting SQL queries and adjust schemas without touching hardware configurations. Remember: "Change the wires, keep the queries."

  3. Data Integrity & Constraints -

    Relational database integrity is enforced through PRIMARY KEY, FOREIGN KEY, UNIQUE, and CHECK constraints, maintaining accuracy and consistency (see Oracle Database Concepts). For example, a FOREIGN KEY ensures referential integrity by linking child records to valid parent rows. Think "Keys lock rows tight."

  4. SQL Aggregate & Scalar Functions -

    Master aggregate functions (SUM, COUNT, AVG, MIN, MAX) and scalar functions (UPPER, LOWER, CONCAT) to manipulate and summarize data efficiently (MySQL Reference Manual). For instance, SELECT COUNT(*) FROM orders WHERE status = 'shipped' instantly totals your dispatched orders. Mnemonic: "SUM up stats, COUNT on clarity."

  5. ACID Transactions -

    Understanding Atomicity, Consistency, Isolation, and Durability (ACID) is vital for reliable relational database operations (IEEE Transactions on Knowledge and Data Engineering). Transactions either fully commit or rollback to preserve data integrity, and isolation levels prevent concurrent corruption. Recall the acronym ACID for rock-solid database reliability.

Make a Quiz (FREE) Copy & Edit This Quiz Create a Quiz with AI

Programming & Software Quizzes

Powered by: Quiz Maker