SQL Practice Test 1 — Questions and Answers

Question 1: To delete data from a table, which SQL statement is used?

DELETE
TRUNCATE (Correct answer)
REMOVE
None of the above

Correct answer: TRUNCATE

The `TRUNCATE TABLE` statement is used to quickly remove all rows from a table in SQL. It is a Data Definition Language (DDL) command, which makes it faster and more resource-efficient than `DELETE` for emptying an entire table, as it deallocates the data pages rather than logging individual row deletions. While `DELETE` can also remove data, `TRUNCATE` is specifically for mass deletion of all rows.

Question 2: To update data in a database, which SQL statement is used?

SAVE AS
SAVE
UPDATE (Correct answer)
MODIFY

Correct answer: UPDATE

The `UPDATE` statement in SQL is specifically designed to modify existing records within a database table. It allows you to change the values of one or more columns for rows that meet a specified condition. This is the standard and correct SQL command for performing data modifications.

Question 3: How do you select all of the columns from the "Persons" table in SQL?

SELECT All Persons
SELECT [all] FROM Persons
SELECT * FROM Persons (Correct answer)
SELECT *.Pe1‘sons

Correct answer: SELECT * FROM Persons

In SQL, the asterisk (`*`) serves as a wildcard character, signifying 'all columns.' Therefore, `SELECT * FROM Persons` is the correct syntax to retrieve every column and all rows of data from the table named 'Persons.' This command provides a complete view of the table's contents.

Question 4: How do you get a column called "FirstName" from a table called "Persons" in SQL?

SELECT Persons.FirstName
EXTRACT FirstName FROM Persons
SELECT FirstName FROM Persons (Correct answer)
None of the above

Correct answer: SELECT FirstName FROM Persons

To retrieve specific columns from a table in SQL, you list the desired column names directly after the `SELECT` keyword. `SELECT FirstName FROM Persons` explicitly requests only the data from the 'FirstName' column within the 'Persons' table. This allows for targeted data extraction.

Question 5: To extract data from a database, which SQL statement is used?

EXTRACT
GET
SELECT (Correct answer)
OPEN

Correct answer: SELECT

The `SELECT` statement is the fundamental SQL command used to retrieve or extract data from one or more tables in a database. It allows users to specify which columns and rows they want to query, making it the primary tool for data retrieval operations. Other options like `EXTRACT` or `GET` are not standard SQL commands for this purpose.

Question 6: (T/F) When AUTOCOMMIT is enabled, updates are made at the end of each SQL query automatically.

A) False
B) True (Correct answer)

Correct answer: B) True

When `AUTOCOMMIT` is enabled in a database system, every SQL data manipulation language (DML) statement, such as `INSERT`, `UPDATE`, or `DELETE`, is automatically treated as a complete transaction. This means that changes made by each query are immediately and permanently saved to the database without requiring an explicit `COMMIT` command. Therefore, updates are indeed made automatically at the end of each SQL query.

Question 7: The AVG SQL function returns the following

the sum of values in a column
average in the values in a group (Correct answer)
maximum value from a column
None of the above

Correct answer: average in the values in a group

The `AVG()` aggregate function in SQL is used to calculate the average (arithmetic mean) of a set of values in a specified column. When combined with a `GROUP BY` clause, it computes the average for each distinct group of rows. It does not return the sum or maximum value.

Question 8: What does a TRUNCATE TABLE do?

deletes the table
checks if the table has primary key specified
deletes all rows from a table (Correct answer)
All of the above

Correct answer: deletes all rows from a table

The `TRUNCATE TABLE` statement is a Data Definition Language (DDL) command that removes all rows from a table, effectively emptying it. Unlike `DELETE`, it does not log individual row deletions, making it significantly faster and more efficient for large tables, but it also means the operation cannot typically be rolled back. It does not delete the table itself, only its contents.

Question 9: The primary - foreign key relations are utilized to

to index the database.
cross-reference database tables (Correct answer)
clean-up the database.
None of the above

Correct answer: cross-reference database tables

Primary and foreign keys are essential components for establishing relationships between tables in a relational database. A foreign key in one table references the primary key in another table, creating a logical link that allows data to be cross-referenced and ensures referential integrity across the database schema. This mechanism is crucial for maintaining consistent and related data.

Question 10: Which of the following statements about system information in an RDBMS is correct?

This information often cannot be updated by a user.
RDBMS store database definition information in system-created tables.
This information can be accessed using SQL.
All of the above. (Correct answer)

Correct answer: All of the above.

Relational Database Management Systems (RDBMS) store metadata, which is information about the database structure (like table names, column types, constraints), in special system-created tables, often called a data dictionary or catalog. This system information is typically read-only for regular users but can be accessed and queried using standard SQL commands. Therefore, all the statements provided are correct regarding system information in an RDBMS.

Question 11: Which of the following statements regarding embedded SQL is correct?

The process of making an application capable of generating specific SQL code on the fly-
Hard—coded SQL statements in a program language such as Java. (Correct answer)
Hard—coded SQL statements in a trigger.
Hard—coded SQL statements in a procedure

Correct answer: Hard—coded SQL statements in a program language such as Java.

Embedded SQL refers to SQL statements that are directly integrated or 'hard-coded' within a host programming language, such as Java, C, or Python. These SQL commands are processed by a precompiler or interpreter along with the host language code, allowing applications to interact with databases by executing predefined queries. It contrasts with dynamic SQL, where queries are constructed at runtime.

Question 12: Table columns are also referred to as

Fields (Correct answer)
Records
Attributes
None of the above

Correct answer: Fields

In the context of database tables, columns are frequently referred to as fields. Each field represents a specific attribute or piece of information that is stored for every record (row) in the table. For example, in a 'Persons' table, 'FirstName' and 'Age' would be considered fields or columns.

Question 13: Which of the SQL commands below deletes all rows in the SalesData table?

DELETE SalesData
DELETE ALL SalesData
DELETE rows FROM SalesData
DELETE FROM SalesData (Correct answer)

Correct answer: DELETE FROM SalesData

The standard SQL command to delete all rows from a table is `DELETE FROM TableName`. This statement removes all records from the specified table. While it can also be used with a `WHERE` clause to delete specific rows, omitting the `WHERE` clause results in the deletion of all entries.

Question 14: Which of the following statements regarding the CASE SQL statement is correct?

A way to establish a loop in SQL.
A way to establish a data definition in SQL.
A way to establish an IF-THEN-ELSE in SQL (Correct answer)
All of the above.

Correct answer: A way to establish an IF-THEN-ELSE in SQL

The `CASE` statement in SQL provides conditional logic, functioning similarly to an `IF-THEN-ELSE` structure found in many programming languages. It allows you to define different outputs or actions based on various conditions, making queries more dynamic and enabling complex conditional expressions within SQL statements. It is not used for loops or data definition.

Question 15: This form of database, which may be used to mine data for business patterns, does not require SQL queries and instead allows users to ask questions like <br> "How many Aptivas have been sold in Nebraska this year?"

multidimensional database (Correct answer)
line information database
functional specification
High Performance Storage System

Correct answer: multidimensional database

A multidimensional database (MDDB), often used in Online Analytical Processing (OLAP) and data warehousing, is designed for fast analysis of data from multiple perspectives. It stores data in a cube-like structure, enabling intuitive, non-SQL queries that resemble natural language questions for business intelligence, making it ideal for pattern mining without complex SQL.

Question 16: IBM's cross-platform relational database management system is part of this family of products.

DB2 (Correct answer)
database administrator
RDF
None of the above

Correct answer: DB2

DB2 is a family of relational database management system (RDBMS) products developed by IBM. It is designed to efficiently store, analyze, and retrieve data, and is widely used across various operating systems and platforms for enterprise-level applications. It is IBM's flagship database product.

Question 17: Which of the following is a group of formally-described tables from which data can be accessed or reassembled in a variety of ways without having to restructure the database tables?

splay tree
relational database (Correct answer)
file allocation table
array

Correct answer: relational database

A relational database organizes data into one or more formally-described tables (relations) with predefined relationships between them. This structure allows data to be accessed, combined, and reassembled in a multitude of flexible ways using SQL queries, without requiring changes to the underlying table structures. This flexibility is a core principle of relational databases.

Question 18: This is the process of organizing a database into tables in such a way that the database's results are always clear.

probability
data modeling
virtual organization
normalization (Correct answer)

Correct answer: normalization

Normalization is a systematic process in database design that organizes the columns and tables of a relational database to minimize data redundancy and improve data integrity. It involves breaking down large tables into smaller, related tables and defining relationships between them, ensuring data consistency and clarity by reducing anomalies. This process makes the database structure logical and efficient.

Question 19: This company was founded in 1977 with the goal of proving that the popular belief that relationship databases were not commercially viable was incorrect.

Oracle (Correct answer)
Hewlett-Packard
Solaris
Sybase

Correct answer: Oracle

Oracle Corporation was founded in 1977 by Larry Ellison, Bob Miner, and Ed Oates. Their primary goal was to develop and market relational database management systems, challenging the popular belief at the time that relational databases were not commercially viable. Oracle went on to become one of the world's largest and most influential database companies.

Question 20: This phrase refers to the process of forecasting, or simply identifying patterns in data that can lead to future predictions.

histogram
data mining (Correct answer)
customer relationship management
enterprise risk management

Correct answer: data mining

Data mining is the process of discovering patterns, anomalies, and correlations within large datasets to predict future outcomes or identify trends. It involves using various analytical techniques and algorithms to extract valuable insights and build predictive models from existing data, effectively forecasting or identifying patterns that lead to predictions.

Question 21: Which of the following is a standard interactive and programming language for retrieving and changing data from a database?

Erlang programming language
dynamic data exchange
WebLogic
Structured Query Language (Correct answer)

Correct answer: Structured Query Language

Structured Query Language (SQL) is the standard language for managing and manipulating relational databases. It is used for querying data, inserting, updating, and deleting records, as well as defining database schemas. SQL serves as both an interactive language for direct database interaction and a programming language for embedding within applications.

Question 22: This word refers to a database that takes up terabytes of magnetic storage and has billions of table rows.

Cold Fusion
holographic storage
Very Large Database (Correct answer)

Correct answer: Very Large Database

A Very Large Database (VLDB) is a term used to describe a database that contains an extremely large amount of data, typically measured in terabytes or petabytes, and often comprising billions of rows. These databases present significant challenges in terms of storage, performance, and management due to their immense scale. It specifically refers to the size of the database.

Question 23: This is a collection of data object descriptions provided for the convenience of programmers and others who may need to refer to them.

stored procedure
Virtual Address eXtension
data dictionary (Correct answer)
Virtual File Allocation table

Correct answer: data dictionary

A data dictionary is a centralized repository of information about data, often referred to as metadata (data about data). It provides descriptions of data objects, such as table names, column types, relationships, and constraints, for the convenience of programmers and users. It helps in understanding the structure and content of a database.

Question 24: This is an open-source database access application programming interface (API).

Open Database Connectivity (Correct answer)
Topic Map Query Language
Universal DataAccess
Open Data-Link Interface

Correct answer: Open Database Connectivity

Open Database Connectivity (ODBC) is a standard application programming interface (API) that allows applications to access data from various database management systems (DBMS) using a common set of functions. It acts as a bridge, enabling client applications to connect to different databases without needing to know the specifics of each database's proprietary API. This open-source nature promotes interoperability and flexibility in database access.

Question 25: Which of the following functions does not belong in the SQL Aggregate category?

COUNT()
ABS() (Correct answer)
AVG()
SUM()

Correct answer: ABS()

SQL aggregate functions operate on a set of rows and return a single summary value, such as COUNT(), AVG(), and SUM(), which calculate the number, average, or total of values in a column, respectively. ABS() (Absolute Value) is a scalar function that operates on a single value and returns a single result for that specific value, rather than summarizing a group of rows. Therefore, ABS() does not belong to the aggregate category.

SQL Practice Test 1

To delete data from a table, which SQL statement is used?