Data Management
_____ is followed by a list of one or more tables from which you want to select data. FROM WHERE SELECT LIST
FROM
In a(n) _____ a row is associated with one or more rows in another table based on the equality of column values or expressions. equijoin outer join inner join self-join
equijoin
A(n) _____ is an object in the database that is primarily used to generate data for primary key columns in tables. sequence synonym index view
sequence
What are the "Vs" of big data? (Choose all that apply.) variety velocity volume veracity
variety velocity volume veracity
Also known as a "virtual table", a(n) _____ is a named query that is stored in the database. view query table row
view
Which of the normal forms is most commonly used for transactional database applications?
3NF
The precision of 3.14159 is ___ and the scale is ___.
6 and 5
If a table named REGIONS contains 6 rows is cross joined to a table named COUNTRIES that contains 12 rows, how many rows will appear in the final result set? 72 60 24 2 None of the above
72
_____ is used to modify the structure, name, or some other attribute of an existing object in the database. ALTER UPDATE RENAME CHANGE
ALTER
Choose the aggregate, or group, functions that are typically used with the GROUP BY clause to arrange data into groups. AVG COUNT SUM MAX MIN AVERAGE MAXIMUM MINIMUM CASE
AVG COUNT SUM MAX MIN
_____ computes the average value for a set of rows. AVG function AVERAGE function SUM function COUNT function MEAN function
AVG function
_____ is used to cause changes within the current session to be made permanent and saved to the database. COMMIT SAVE UPDATE STORE
COMMIT
_____ is used to remove entire rows of data from a table. DELETE UPDATE INSERT SELECT
DELETE
A WHERE clause cannot be used with a DELETE statement. True or False
False
Which data type would be appropriate for storing a whole number such as 32567? (Choose all answers that apply.) INTEGER NUMERIC(5) CHAR VARCHAR2 NUMERIC(5,0)
INTEGER NUMERIC(5,0)
_____ returns the single maximum value. MAX function MAXIMUM function LARGEST function HIGHEST function GREATEST function
MAX function
______ compares to expressions and if they are equal, the function returns null. NULLIF NVL NEGATE NULL
NULLIF
_____ languages follow a step by step process of how to do something.
Procedural
_____ is used to restore the database to an earlier state and cancels the effects of a transaction in progress. ROLLBACK REVERSE UNDO RESTORE
ROLLBACK
_____ is used in conjunction with the FROM clause to extract data from the database in an organized, readable format. SELECT ORDER BY INSERT DELETE
SELECT
The 3 clauses that make up the basic structure of a SQL query are _____, _____, and _____.
SELECT FROM WHERE
Choose the clauses required in a SELECT statement. SELECT FROM WHERE ORDER BY
SELECT FROM
In an UPDATE statement the keyword _____ determines the value that will be placed in the column. SET VALUE WHERE UPDATE
SET
The _____ function converts the number or date to VARCHAR2 data type in the specified format. TO_CHAR TO_NUMBER TO_WORD TO_DATE
TO_CHAR
The _____ function converts an expression to a value of number data type. TO_NUMBER TO_CHAR TO_DATE TO_WORD
TO_NUMBER
_____ removes all of the rows (i.e., data) from an existing table in the database. TRUNCATE REMOVE DELETE DROP
TRUNCATE
Which data type would be appropriate for storing a home address? VARCHAR2 CHAR INTEGER NUMBER
VARCHAR2
When will an index be created automatically? (Choose 2) When a primary key is created When a unique key is created When a foreign key is created When a sequence is created
When a primary key is created When a unique key is created
When using set operators, the columns in the queries must be _____. in the same order and of the same data type in the same order and of the same length in the same order and of the same domain in the same order and of the same cardinality
in the same order and of the same data type
A(n) _____ is an object you can create in the database that supports faster queries on tables. index view sequence synonym
index
A(n) _____ provides a highly scalable and well-performing method to generate surrogate keys for a number data type. sequence generator sequence key synonym
sequence generator
HAVING is to GROUP BY as WHERE is to SELECT. True or False
True
If you add a new column to a table that contains data, the new column is initially NULL for all of the pre-existing rows. True or False
True
If you select from two different tables but fail to join the tables you will create a Cartisian product. True or False
True
Which set operator returns the combined rows from two queries, sorting them, and removing duplicates? UNION UNION ALL INTERSECT MINUS
UNION
Which set operator returns the combined rows from two queries without sorting or removing duplicates? UNION ALL UNION INTERSECT MINUS
UNION ALL
_____ is used to modify data within one or more existing rows of data in a table. UPDATE INSERT SELECT DELETE
UPDATE
_____ is used to place conditions on a query by eliminating rows that would normally be returned by a query without conditions. WHERE LIMIT NOT EXCLUDE
WHERE
The following sequence will increment by 1000. CREATE SEQUENCE customers_seq START WITH 1000 INCREMENT BY 1 NOCACHE NOCYCLE; True or False
False
_____ when used in conjunction with the GROUP BY clause in a SELECT statement, tells GROUP BY which groups to include in the output. HAVING WHERE SELECT ORDER BY
HAVING
You can create both private and public _____. synonyms keys joins subqueries
synonyms
A(n) _____ is a series of one or more SQL statements that are executed between commit events. transaction query update insert
transaction
_____ sets are so voluminous that traditional data processing software just can't manage them. But these massive volumes of data can be used to address business problems you wouldn't have been able to tackle before. Big data Relational data Plain text
Big data
Which should you choose to compare a column in a query to a list of values that are produced by a subquery? IN EXISTS WHERE BETWEEN =
IN
_____ is used to add rows into a table. INSERT UPDATE DELETE SELECT
INSERT
Which SQL statements can include a subquery? Choose all answers that apply.) INSERT UPDATE DELETE CREATE TABLE ALTER TABLE
INSERT UPDATE DELETE
Which set operator returns only the rows that occur in both queries' result sets, sorting them and removing duplicates? INTERSECT UNION UNION ALL MINUS
INTERSECT
_____ returns the single minimum value. MIN function LEAST function SMALLEST function LOW function MINIMUM function
MIN function
Which set operator returns only the rows in the first result set that do not appear in the second results set, sorting them and removing duplicates? MINUS UNION UNION ALL INTERSECT
MINUS
_____ is used to sort or arrange the results of a query in format you specify. ORDER BY SORT ARRANGE ORDER
ORDER BY
What is the order of execution for a SQL query? SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY WHERE, FROM, SELECT, HAVING, GROUP BY, ORDER BY FROM, SELECT, WHERE, ORDER BY, HAVING, GROUP BY HAVING, WHERE, SELECT, FROM, GROUP BY, ORDER BY
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
Which statements allow you to set transaction properties? (Choose all that apply.) START TRANSACTION SET TRANSACTION COMMIT SAVEPOINT ROLLBACK
SET TRANSACTION
_____ is used to specify characteristics for the transaction that follows. SET TRANSACTION CREATE TRANSACTION PREPARE TRANSACTION ALTER TRANSACTION
SET TRANSACTION
SAVEPOINT creates a point within a session to which a the database can be restored using a ROLLBACK statement. True or False
True
Synonyms allow you to change complicated and lengthy names by simplified aliases. True or False
True
The CASE expression will let you use IF.... THEN... ELSE... logic in a SQL statement. True or False
True
There are extensions to standard SQL which add procedural programming language functionality,
True
The relationship between bank accounts and deposit transactions is _____.
one to many
Which of the following statements true regarding when NOT to create an index? (Choose all that apply.) The table is small. The columns are not often used in a WHERE clause or join condition. The indexed columns are referenced as part of an expression. The table is not updated frequently.
The table is small. The columns are not often used in a WHERE clause or join condition. The indexed columns are referenced as part of an expression.
_____ determines the number of occurrences of non-NULL values. COUNT function AVG function SUM function TOTAL function MAX function
COUNT function
_____ is used to prevent duplicate rows from being displayed in the output. DISTINCT REMOVE SELECT DELETE
DISTINCT
_____ is used to completely remove a table from the database. DROP TABLE DELETE TABLE TRUNCATE TABLE REMOVE TABLE
DROP TABLE
_____ is a subset of SQL that refers to the set of SQL statements that is used to create database objects, modify their structure, and remove them from the database.
Data Definition Language (DDL)
_____ refers to those SQL statements that are used to build database objects. Data Definition Language (DDL) Data Manipulation Language (DML) Transaction Control Language (TCL) Data Control Language (DCL)
Data Definition Language (DDL)
_____refers to those statements in SQL that are used to add, modify, and delete data in a database object, such as a table. Data Manipulation Language (DML) Data Definition Language (DDL) Transaction Control Language (TCL) Data Control Language (DCL)
Data Manipulation Language (DML)
Which characteristics of a column can be modified with an ALTER statement? (Choose all that apply.) Data type Size/Length Default value Values Order in table
Data type Size/Length Default value
NULL, NULLIF, NVL AND NEGATE are all considered SQL Null functions True or False
False
Once a transaction has been committed, it can be rolled back. True or False
False
SQL statements are case sensitive; however, the data is always case insensitive. True or False
False
When inserting data into a table, numeric fields must be enclosed in quotes; character fields can be left without quotes. True or False
False
You can add or modify a column in a table and specify where the column appears. True or False
False
_____ is used in collaboration with the SELECT statement to arrange identical data into groups. GROUP BY ARRANGE ORDER BY SORT
GROUP BY
In which clauses of a SELECT statement can you include a subquery? (Choose all answers that apply.) HAVING SELECT WHERE ORDER BY GROUP BY
HAVING SELECT WHERE
Choose 3 characteristics of a NoSQL database. High Scalability Distributed computing Flexible data structure Little to no growth potential Has one data type
High Scalability Distributed computing Flexible data structure
Categorize the following SQL commands as Data Definition Language (DDL) or Data Manipulation Language (DML).
SELECT - DML INSERT - DML UPDATE - DML DELETE - DML CREATE - DDL ALTER - DDL DROP - DDL TRUNCATE - DDL
Which of the statements below most accurate describe SQL?
SQL is a declarative language SQL has some procedural elements
_____ adds numeric values in a given column. SUM function ADD function AVG function COUNT function
SUM function
_____ work with sets of output data from two or more SELECT statements. Set operators Joins Queries Database administrators
Set operators
_____ is the language used by programmers to interact with a relational database management system (RDBMS).
Structured Query Language (SQL)
Which of the following statements true regarding when to create an index? (Choose all that apply.) The column contains a wide range of values. A column contains a large number of NULL values. One ore more columns are frequently used together in a WHERE clause or a join condition. The table is small and most queries are expected to retrieve few rows.
The column contains a wide range of values. A column contains a large number of NULL values. One ore more columns are frequently used together in a WHERE clause or a join condition.
Choose the syntax details that are included in a CREATE TABLE statement. (Choose all that apply.) The name of the table The size of the columns The size of the table The name of the values
The name of the table The size of the columns
A rollback that does not specify a savepoint always rolls back to the beginning of the transaction. True or False
True
A view derives its data from the tables on which it is based, called base tables. Base tables can be tables or other views. True or False
True
Always consider using a _____ clause with an UPDATE statement , or else you update all rows. WHERE FROM SELECT ORDER BY
WHERE
Join conditions are located in the _____ clause. WHERE SELECT FROM GROUP BY
WHERE
An individual's first name is a good example of a(n) _____.
attribute
Normalization is a process for evaluating and correcting table structures to minimize _____.
data redundancies
A(n) _____ associates a fixed set of properties with the value. data type constraint default value domain
data type
In _____ languages, programmers describe the results they expect without providing step by step instructions for how they should be obtained.
declarative
The _____ constraint is used to set a default value for a column. default unique primary key foreign key not null
default
The problem of ambiguous column names is addressed with _____. dot notation keywords aliases indexes
dot notation
A bank account is a good example of a(n) _____.
entity
A(n) _____ constraint requires values in one table to match values in another table. foreign key primary key unique check not null
foreign key
In a(n) _____, two or more tables are combined and the result consists exclusively of rows in one table that match rows in the other table according to a join condition that is specified. inner join outer join self-join cross join
inner join
A relational database cannot directly implement the _____ relationship type.
many-to-many
In relational database design, the process of organizing data to minimize redundancy is called _________________.
normalization
The _____ is a special value that that signifies that the value is unknown or does not exist.
null value
Which type of join would be used to return records from one table, regardless of the existence of associated records in the related table? outer join inner join self-join cross join
outer join
A table can have only one _____ constraint defined on it. primary key foreign key unique check
primary key
A _____ is used to extract data from the database in a readable format.
query
A relational database stores information in tables with columns and ___________________________.
rows
In a(n) _____ rows in a table are connected with other rows in the same table. self-joins cross join inner join outer join
self-joins
A(n) _____ is a SELECT statement contained within another SELECT statement. subquery join query UNION
subquery
A(n) _____ is an object within the database that consists of nothing more than an alternative name - an alias - for another object in the database. synonym view index table
synonym
A rollback that does not specify a savepoint always rolls back _____. to the beginning of the transaction to the last statement to run to the last record modified It will not rollback without a savepoint.
to the beginning of the transaction
A subquery that must run once for each row in the outer query is known as _____. correlated subquery non-correlated subquery single-value subquery multi-value subquery corrugated subquery
correlated subquery
A ______, sometimes called a cross join, creates one row of output for every combination of source and target table rows. cartesian product outer join self-join inner join
cartesian product
Match the following statements with the proper definition as they relate to data transactions. changes written to database undoes changes that have not been committed to the database a marker in a transaction, which breaks the transaction into smaller parts
changes written to database - COMMIT undoes changes that have not been committed to the database - ROLLBACK a marker in a transaction, which breaks the transaction into smaller parts - SAVEPOINT
A(n) _____ constraint contains an assertion, such as (PRICE > 0.0), that must always be true for the column on which it is defined. check unique foreign key primary key
check
Set operators _____. combine standalone SELECT statemens in ways that cannot be done with joins or other conventional methods in SQL. retrieve data from two or more tables and connect that data together to produce a combined set of output. are SELECT statements within larger SQL statements. return one value for each sest of zero or more rows they encounter.
combine standalone SELECT statemens in ways that cannot be done with joins or other conventional methods in SQL.