SQL

Lakukan tugas rumah & ujian kamu dengan baik sekarang menggunakan Quizwiz!

A string literal must be enclosed in ____.

""

The default keyword for group functions is ____.

ALL

Which of the following commands will drop any columns marked as unused from the TABLEA table?

ALTER TABLE tablea DROP UNUSED COLUMNS;

Which of the following keywords is used to mark a column for deletion at a later time?

ALTER TABLE...SET UNUSED

To instruct Oracle12c to sort data in ascending order, enter ____ after the column name in the ORDER BY clause

ASC

Which of the following is a correct statement

An asterisk can be used as the argument for the COUNT function to include NULL values in the results.

Explain the difference between an implicit and an explicit COMMIT.

An explicit COMMIT occurs when you enter a COMMIT statement, an implicit COMMIT occurs when you exit client tools.

Explain the similarities and differences between the UNIQUE and PRIMARY KEY constraints.

Both validate the uniquness of a value, but the UNIQUE constraint allows NULL values.

Which of the following symbols can be used in a column name?

#

Which of the following symbols cannot be used in a table name?

%

How is a "TOP-N" analysis performed?

A TOP-N analysis is performed by determining if the rows ROWNUM value is less or equal to N.

Explain the difference between a WHERE and a HAVING clause.

A WHERE clause is used for filtering rows, and a HAVING clause is used to filter groups.

What is the purpose of an E-R Model?

A logical representation of the data for an organization or for a business area, using entities for categories of data and relationships for associations between entities

How does a materialized view differ from the other types of views?

A materialized view replicates data

What special consideration needs to be given to search conditions when they are being compared to character or date columns?

A primary sort is the first column a table is ordered by.and a secondary sort is a second field to sort a table by.

What is the difference between a shared and exclusive lock?

A shared lock allows other users to view information on a table but not alter it. An exlusive lock is put on a table so no other user can alter or update the table's contents.

How do the following three types of views differ in terms of the data they can contain: simple view, complex view and inline view?

A simple view references only one table and doesn't include a group function. A complex view can retrieve data from more than one table and can contain group functions. An inline view exists only while the command is being executed.

Which of the following is a valid SQL statement

CREATE TABLE newname (colA DATE, colB VARCHAR2(3);

Which of the following keywords is used to create a Cartesian join?

CROSS JOIN

Which of the following rules does not apply to column names in Oracle 12c?

Column names can contain an ampersand (&).

Which of the following keywords is used to remove a database table in Oracle 12c?

DROP TABLE

Which command will delete a view

DROP VIEW

The ____ function is used to calculate the total amount stored in a numeric field.

SUM

What is the difference between dropping a column with the DROP COLUMN clause and setting a column as unused with the SET UNUSED clause?

Setting it as unused will allow you to mark the column to be deleted at a later time.

What is the difference between how a NOT NULL constraint is added to an existing column and how other types of constraints are added?

The NOT NULL constraint can only be added to an existing column using the MODIFY clause of the ALTER TABLE command, while other types of constraints can be added using the ADD clause of the ALTER TABLE command.

What is the difference between a standard SELECT statement and a SELECT...FOR UPDATE statement?

The SELECT .. FOR UPDATE command places a shared lock on records to be changed.

Which of the following statements about the FOREIGN KEY constraint is incorrect?

The constraint can reference any column in another table, even a column that has not been designated as the primary key for the referenced table.

A CHAR column cannot be resized to a width that is smaller than the data it already contains.

True

A FOREIGN KEY constraint can be added to the column of a table to ensure that the referenced data value actually exists in the other table

True

Concatenation refers to combining the results of several SELECT statements into one result

True?

Data mining refers to analyzing historical data stored in a database.

True?

Explain the purpose of a set operator.

UNION = Returns the results of both queries and removes duplicates UNION ALL = Returns the results of both queries and includes duplicates INTERSECT = Returns only the rows included in the results of both queries

Describe how the COUNT function processes NULL values when the function is executed.

Use an asterisk, so when an asterisk is encountered, the entire record is counted, so a NULL in one column doesn't cause the row to be ignored.

When does a COMMIT command implicitly occur

When the user issues a DDL command such as CREATE or ALTER TABLE.

A DBMS includes which of the following capabilities?

all of the above

A user can perform a DML operation (add, modify, delete) on a simple view if it does not violate which type of existing constraint on the underlying base table?​

all of the above

Based upon the contents of the BOOKS table in the accompanying figure, which of the following queries will retrieve all books published in 2001?

all of the above

Functions that return one result per group of rows are called ____ functions.

all of the above

Which of the following is a valid statement?

all of the above

Which of the following is displayed by the DESCRIBE command?

all of the above

Which of the following is not a step in the Systems Development Life Cycle (SDLC)?

all of the above

The outer join operator in the WHERE clause cannot be used with which of the following operators

both a and b

Which of the following queries will return the same results as the following SQL statement? SELECT c.customer#, lastname, firstname, order# FROM customers c, orders o WHERE c.customer# = o.customer#;

both a and b

Which of the following SQL statements will list the name of each customer stored in the customers table, and, if the customer has placed an order that is contained in the ORDERS table, the order# of any order each customer has placed?bith

both a and c

A(n) ____ is used to indicate how data should relate to a given search condition.

comparison operator

A view based on the contents of one table that uses an expression for one of the columns is considered a(n) ____ view.​

complex

Combining the contents of two or more columns is known as ____.

concatenation

Commands used to modify data are called ____ commands

data manipulation language (DML)

In an E-R Model a person, place, or thing with characteristics to be stored in the database are referred to as?

entity

A(n) ____ lock will prevent any DDL operations from being performed on the locked table

exclusive

A COMMIT is explicitly issued when the user exits SQL*Plus. _________________________

false

A FOREIGN KEY constraint will not allow a row containing a NULL value in the foreign key column to be added to the table.

false

A NULL value is the same as a blank space.

false

A column alias that has been defined in the SELECT clause of a SELECT statement cannot be referenced in an ORDER BY clause

false

A complex view cannot contain any group functions

false

A correlated subquery is one in which the inner query is executed first, and then the outer query is executed

false

A group function cannot be included in the SELECT clause of a single-row subquery

false

A multiple-column subquery cannot be nested in a WHERE clause

false

A shared lock prevents another user from performing DDL or DML operations on the table

false

Comparison operators are used to combine search conditions

false

DML commands are used to create or modify database tables.

false

Data manipulation language commands are used to create or modify database tables.

false

Group functions return a group of results per row processed

false

TOP-N" analysis can be used to find the highest values in a column by sorting the data in ascending order

false

The DELETE TABLE command can be used to delete all the data stored in a table and release the storage space, yet retain the structure of the table. _________________________

false

The JOIN keyword is used in the WHERE clause to indicate the tables that should be joined or linked.

false

The data being inserted into a table are listed in the ADD clause of the INSERT command.

false

The only group function that includes NULL values by default is the MIN function

false

The results of the outer query are passed to the inner query.

false

To find rows containing a NULL value in a specified column, you must use the search condition of = NULL.

false

When a SELECT statement includes WHERE, HAVING, and GROUP BY clauses, the GROUP BY clause is always evaluated first

false

When combining the results of two SELECT statements with the UNION keyword, duplicate rows are suppressed in the results

false

​A single-row subquery can return several columns, but only one row, of results to the outer query.

false

Which of the following does not contain repeating groups, but has a primary key and possibly partial dependencies?

firt normal form

A temporary table that is created when a multiple-column subquery is used in the FROM clause of an outer query is called a(n) ____.​

inline view

When the WHERE clause contains multiple types of operators, which of the following is resolved last?

logical operators

A foreign key is usually found on which side of a relationship?

many

Which of the following is the standard abbreviation for the constraint NOT NULL?

nn

Based on the contents of the BOOKS table, which line of the following SQL statement contains an error? 1 SELECT isbn, title 2 FROM books 3 WHERE pubid = 4 (SELECT pubid 5 FROM books 6 WHERE title = 'SHORTEST POEMS') 7 AND retail-cost > 8 (SELECT AVG(retail-cost) 9 FROM books);

none of the above

Based on the contents of the BOOKS table, which of the following SQL statements will display the title of all books published by the publisher of SHORTEST POEMS?

none of the above

Based on the contents of the BOOKS table, which of the following SQL statements will return an error message?

none of the above

Which of the following SQL statements will display the gift that should be sent to any customer who orders the book titled THE WOK WAY TO COOK?

none of the above

The results of a subquery are passed back as input to the ____ query.

outer

Data in first normal form (1NF) does not contain which of the following?

repeating groups

A(n) ____ is used to combine the results of two queries.

set operator

Which type of view is created with the following command? ​ CREATE VIEW inventory AS SELECT isbn, title, retail price FROM books WITH READ ONLY;

simple

The following SQL statement contains what type of subqueries? SELECT isbn, title FROM books WHERE pubid = (SELECT pubid FROM books WHERE title = 'SHORTEST POEMS') AND retail-cost > (SELECT AVG(retail-cost) FROM books);

single row

The > operator is referred to as a(n) ____ operator

single-row

A(n) ____ in a SQL command instructs Oracle 12c to use a substituted value in place of the variable at the time the command is actually executed.

substitution variable

In which step of the Systems Development Life Cycle (SDLC) is the solution to the identified problem determined and understood?

systems analysis

In which step of the Systems Development Life Cycle (SDLC) is the system actually used by the end-user on a regular basis?

systems deployment

In which step of the Systems Development Life Cycle (SDLC) are the logical and physical components defined?

systems design

Data mining refers to ____.

the extraction of hidden predictive information from large databases.

If the data has no partial dependencies, repeating groups, or transitive dependencies, and has a composite primary key, the data is in which form?

third normal

A user who is issuing DML commands can save modified data or undo uncommitted changes by issuing ____ statements.

transaction control

A CHECK constraint requires that a data value meet a certain condition before the record is added to the database table.

true

A NULL value can be included in the data being added to a table by explicitly entering the word NULL.

true

A UNIQUE constraint is the same as a PRIMARY KEY constraint, except that it will accept NULL values.

true

A bridging table can be used to eliminate a many-to-many relationship in a relational database.

true

A column name can consist of up to 225 characters.

true

A column represents a field in the physical database table.

true

A deadlock occurs when two users hold shared locks on portions of a table that are needed to complete the transaction of the other user.

true

A group function can be used in a(n) inline view

true

A lock is automatically released when the user issues a transaction control statement such as COMMIT or ROLLBACK.

true

A subquery can be used with the INSERT command to enter data from an existing table into the destination table.

true

A subquery is required when the condition for the outer query is based upon an unknown

true

A table that has been dropped without the PURGE option can be retrieved using the FLASHBACK TABLE command.

true

A view can be created to simplify issuing complex SQL queries.

true

AVG, COUNT, and STDDEV are all considered group functions

true

By default, the SUM function assumes the ALL keyword and the result will include multiple occurrences of numeric values

true

DDL commands are used to create or modify database objects

true

Data in second normal form (2NF) may contain which of the following?

true

Group functions are also known as aggregate functions

true

If a subquery is nested in a HAVING clause, the subquery must be on the right side of the comparison operator

true

If you don't inform users of the name change it could prevent them from finishing their work or accessing the table

true

The ALL option can be used in the SELECT clause to indicate that all columns should be retrieved.

true

The most common type of join is an equijoin, which joins two or more tables together based upon the tables having equivalent data values in a common column.

true

The only required clauses for a SELECT statement are the WHERE and FROM clauses. _________________________

true

When a self-join is created, each copy of the table must be assigned a table alias.

true

a complex view can retrieve data from more than one table

true

table can be locked in SHARE MODE or EXCLUSIVE MODE. _________________________

true

Which of the following symbols can be used to combine data from different columns into one column of output?

||

Which of the following types of constraints is used to enforce referential integrity?

FOREIGN KEY

A column heading that is longer than the width of a column will not be truncated for a column that is defined to store character data. _________________________

False

Data is in second normal form (2NF) if it contains no repeating groups and has a primary key to uniquely identify each record.

False

The ON clause can be used only if the tables being joined have a common column with the same name.

False

The outer join operator is used to combine the results of multiple SELECT statements

False

A(n) WHERE statement allows a user to retrieve data from a database table. _________________________

From

​Which clause is used when the group results of a subquery need to be restricted, based on some condition?

HAVING

Which comparison operator allows you to search for NULL values in a subquery?

IS NULL

What is the problem associated with changing the name of a table?

If you don't inform users of the name change it could prevent them from finishing their work or accessing the table

Which of the following statements about the DELETE command is incorrect?

If you omit the mandatory WHERE clause, an error message will be issued

Explain the different processing methods used by correlated and uncorrelated subqueries

In an uncorrelated subquery, the subquery is exectued first, the results are passed to the outer query, then the outer query is executed. A correlated subquery refrences an outer query, then the exists operator is used to test if a link is present.

Explain how nested group functions are processed

In nested group functions the inner function is resolved first.

What is the purpose of the ORDER BY clause?

It is used to display query results in a sorted order.

Which of the following keywords can be used to join two tables that do not contain a commonly named and defined column

JOIN...USING

Based on the contents of the BOOKS table, which line in the following SQL statement contains an error? 1 SELECT title 2 FROM books 3 WHERE pubid EXISTS IN 4 (SELECT pubid 5 FROM books 6 WHERE retail > 41.95);

Line 3

Explain the difference between multiple-row and multiple-column subqueries.

Multiple-row subqueries return more than row of results using WHERE and HAVING clauses. Multiple-column returns columns to an outer query using FROM, WHERE, or HAVING.

Which of the following can only be used to link tables that have a common column?

NATURAL JOIN

Give the appropriate situation in which to use each of the following join keywords: NATURAL JOIN, JOIN...USING, and JOIN...ON.

NATURAL JOIN = used in the FROM clause to join table containing a common column with the same name and definition JOUN... USING =identifies the common column used to join the tables JOIN ... ON = identifies the column used to join tables

Which of the following is a valid column name?

NEW_COLUMN

Which of the following constraints cannot be added to an existing table with the ADD clause of the ALTER TABLE command

NOT NULL

What is the relationship between a NOT NULL constraint and a CHECK constraint?

NOT NULL is a special CHECK where the condition is not a NULL value.

If it is possible for a subquery to return a NULL value to the outer query for comparison, the ____ function should be used to substitute an actual value for the NULL.

NVL

Which of the following rules apply to table names in Oracle 12c?

Names can contain a number sign (#).

Which of the following keywords must have been included during the creation of a FOREIGN KEY constraint to allow a row from the parent table to be deleted, even if it is referenced by a row in the child table?

ON DELETE CASCADE

A subquery, except one in the FROM clause, cannot contain a(n) ____ clause.​

ORDER BY

Which of the following clauses is used to indicate a particular sort sequence for presenting query results?

ORDER BY

Based upon the contents of the BOOKS table in the accompanying figure, which of the following queries will return all books that cost at least $25.00?

SELECT * FROM books WHERE cost >= 25.00;

Based upon the contents of the BOOKS table in the accompanying figure, which of the following queries will retrieve all books stored in the BOOKS table with Pubid 1 or 2 or that have a retail price of at least $42.00?

SELECT * FROM books WHERE pubid = 1 OR pubid = 2 OR retail >= 42;

Based upon the contents of the BOOKS table in the accompanying figure, which of the following SQL statements will retrieve all books published by the publisher assigned Pubid 1?

SELECT * FROM books WHERE pubid = 1;

Based upon the contents of the BOOKS table in the accompanying figure, which of the following queries will return the category and title of all books stored in the BOOKS table presented in order of their category and, for the books in the same category, sort the title of the books in descending order?

SELECT category, title FROM books ORDER BY 1 ASC, 2 DESC;

Which of the following SQL statements will list the name of each customer stored in the CUSTOMERS table, and, if the customer has placed an order that is contained in the ORDERS table, the order# of any order each customer has placed?

SELECT lastname, firstname, order# FROM customers c LEFT OUTER JOIN orders o ON c.customer# = o.customer#;

Based upon the contents of the BOOKS table in the accompanying figure, which of the following queries will retrieve all book titles that are in the Business or Computer category and have a retail price of more than $35.00?

SELECT title FROM books WHERE (category = 'BUSINESS' OR category ='COMPUTER' ) AND retail >35;

Which of the following keywords can be included in a SELECT statement to suppress duplicate data?

DISTINCT

Which of the following is not considered a group function?

TRUNC

Which of the following is not a valid comparison operator?

=>

Explain the difference between an inner join and an outer join

An inner join gives an intersection of the row in common, an outer join combines all the rows.

he ____ operator is used to determine whether a condition is present in a subquery.

EXISTS

Which of the following datatypes refers to variable-length character data, where n represents the maximum length of the column?

VARCHAR2(n)

Which of the following statements about a PRIMARY KEY is incorrect

Which of the following statements about a PRIMARY KEY is incorrect

A lock arising from a SELECT...FOR UPDATE command will be released when ____.

a COMMIT command is executed

Partial dependency exists if what conditions exist?

a column is dependent only on a portion of a composite


Set pelajaran terkait

Chapter 50: Respiratory Problems of the Adult Client

View Set

Environmental Conservation Exam 1.

View Set

Unit 23 - Building Design, Plans, and Spectifications

View Set

TERMINOS DE ADMINISTRACION FINANCIERA

View Set

Workers Comp. Chapter 1 Section A

View Set

Marketing 255 Exam 1 (Modiri Spring 2024)

View Set

Biology A: Preparing for Quiz 2 Unit 4

View Set