CISP 350 SQL

A correlated subquery is a subquery that is executed once for each _________ in the outer query

row

In a join, column names need to be qualified only

when the same names are used in both tables

May 31, 2008 at 2:00 PM is 1. between '01-may-2008' and '31-may-2008' 2. after '31-may-2008' 3. before ' 31-may-2008'

2. after '31-may-2008'

SELECT vendor_name invoice_date FROM vendors v LEFT JOIN invoices i ON v.vendor_id = i vendor_id If the LEFT keyword is this query is replaced with the RIGHT keyword, the total number of rows that are returned must equal. 1. the number of rows in the invoice table 2. none of the other answers 3. the number of rows in the vendors table 4. the number of rows in the invoice table plus the number of rows in the vendors table.

2. none of the other answers

If you define a colomn with a default value, that value is used whenever a row 1. is added to the table 2. that doesn't include a value for that column is added to the table 3. in the table is updated 4. with a zero value for that column is added to the table

2. that doesn't include a value for that column is added to the table

When you code a union that combines two result sets, which of the following is not true? 1. the result sets may or may not have any duplicate rows. 2. the result set must be derived from different tables 3. each result set must have the same number of columns 4. the corresponding columns in the result sets must have compatible data types.

2. the result set must be derived from different tables

Which of the following types of SQL statements isn't a DML statement? 1. update 2. insert 3. create table

3. create table

Which of the following could be used to calculate the number of days (rounded) between the current date and the value in a future_date column? 1. ROUND(curr_date - future_date) 2. ROUND( future_date - cur_date) 3. ROUND( SYSDATE - future_date) 4. ROUND( future_date - SYSDATE)

4. ROUND( future_date - SYSDATE)

If introduced as follows, the subquery can return which of the values listed below? WHERE vendor_id NOT IN ( subquery) 1. a table 2. a subquery cant be introduced this way 3. a single value 4. a column of one or more rows

4. a column of one or more rows

If introduced as follows, the subquery can return which of the values listed below? WHERE (subquery) 1. a table 2. a single value 3. a column of one or more rows 4. a subquery cant be introduced in this way

4. a subquery cant be introduced in this way

If introduced as follows, the subquery can return which of the values listed below? FROM( subquery) 1. a column of one or more rows 2. a single value 3. a subquery cant be introduced this way 4. a table

4. a table

The extension to the SQL language that oracle database offers is called

PL/SQL

If you want to filter the result set that's returned by a select statement, you must include a/an ________ clause.

WHERE

When you code a DELETE statement for one or more rows, the _______________ clause specifies which row or rows are to be deleted.

WHERE

When you code an UPDATE statement for one or more rows, the SET clause specifies the new data for the specified columns and the _______________ clause specifies which row or rows are to be updated.

WHERE

When you use the implicit syntax for coding joins, the join conditions are coded in the ________ clause.

WHERE

To prevent prevent duplicate rows from being returned by a select statement, you can code the ________ keyword in the select clause

DISTINCT

to relate one table to another ,a/an ________ in one table is used to point to a primary key in another table.

Foreign key

A subquery can be coded in a WHERE, FROM, SELECT, or

HAVING

When you code a column list in an INSERT statement, you can omit columns with default values and columns that allow _______________ values.

null

Which of the following types of statements isn't an action query? 1. update 2. Select 3. delete 4. insert

select

To insert several rows into a table with an INSERT statement, you code a(n) _______________ in place of the VALUES clause.

subquery

A SELECT statement that includes aggregate functions is often called an ________ query

summary

A subquery factoring clause creates a temporary ________ that can be used by a query that is coded after the subquery factoring clause.

table

a relational database consist of one or more ________.

tables

To test expressions that use literal values, arithemtic operators, and functions, you can use the ________ table that is automatically available

dual

The ________ clause of the select statement names the tables that contains data to be retrieved.

from

If you want to join all of the rows in two tables whether or not they are matched, you use an ________ join.

full outer

When you code a union with the INTERSECT keyword to combine two result sets, the union

includes only rows that occur in both result sets

When you code a subquery in a FROM clause, it returns a result set that can be referred to as an ________ view.

inline

To retrieve or update the database, the client sends a/an ______ to the database.

SQL query

To return all of the columns from the base table, you can code the ________ operator in the SELECT clause

*

when coded in a where clause, whicg search condition will return invoices when payment_date isnt null and invoice_total is greater than or equal to $500? 1. payment_date IS NOT NULL OR invoice_total >=500 2. NOT ( payment_date is NULL and invoice_total <= 500 3. payment_date IS NOT NULL AND invoice_total >500 4. payment_date IS NOT NULL AND invoice_total >= 500

4. payment_date IS NOT NULL AND invoice_total >= 500

Which of the following recommendations won't improve the readability of your SQL statements 1. indent continued lines 2. start each clause on a new line 3. break long clauses into multiple lines 4. use comments to describe what each statement does

4. use comments to describe what each statement does

You can use the ________ Keyword in a WHERE clause to test whether a value is greater than at least one of the values returned by the subquery.

ANY

Write an aggregate expression for the number of entries in the vendor_name column

COUNT(vendor_name)

SQL statements that define the tables in a database are referred to as ________ statements

DDL

f you code a column list in an INSERT statement that includes a column that's defined with a default value, you can insert the default value for that column by coding the ____________________ keyword in the VALUES clause of the INSERT statement.

DEFAULT

Write the code for a DELETE statement that deletes every row in the Invoices table:

DELETE FROM invoices

To sort the records that are retrieved be a select statement in descending sequence by invoice_total, you code ________ after ORDER BY invoice_total.

DESC

With version 12c and above, you can use the ________ clause to limit the number of rows in a result set

FETCH

Write an aggregate expression to find the vendor_name column that's last in alphabetical order

MAX( vendor_name)

The order of precedence for the logical operators in a WHERE clause is

NOT, AND, OR

With version 12c and above, you can use the ________ clause to designate a stating row for a result set.

OFFSET

A subquery is a/an ______________ statement that's coded within another SQL statement.

SELECT

When you code a select statement, you must code the four main clauses in the following order

SELECT, FROM, WHERE, ORDER BY

SELECT vendor_state, Vendor_city, vendor_name, COUNT(*) as Invoice_qty SUM( invoice_total) AS invoice_average FROM Invoices JOIN vendors On invoices.vendor_id = vendors_vendor_id Where vendor_state < 'e' GROUB BY vendor_state, vendor_city, vendor_name HAVING SUM( invoice_total) > 500 ORDER BY vendor_state, vendor_city, vendor_name Although this query runs as coded, it contains what logical error?

The column name for the fifth column in the result set doesn't match the data.

SELECT vendor_state, Vendor_city, vendor_name, COUNT(*) as Invoice_qty SUM( invoice_total) AS invoice_average FROM Invoices JOIN vendors On invoices.vendor_id = vendors_vendor_id Where vendor_state > 'e' GROUB BY vendor_state, vendor_city, vendor_name HAVING SUM( invoice_total) > 500 ORDER BY vendor_state, vendor_city, vendor_name WHAT IS THE LOGICAL ERROR?

The condition in the WHERE clause should be coded in the HAVING clause.

When you code a subquery in a FROM clause, you must assign an ________ to it

alias

When you code a subquery in a FROM clause, you must assign an ________ to it>

alias

The processing thats done by the DBMS is typically referred to as

back-end-proccessing

The intersection of a row and a column is commonly called a/an

cell

Unless you assign a/an ________, the column name in the result set is the same as the column name in the base table

column alias

When you code an ORDER BY clause, you can specify a

column name, alias, expression, or column name

A view is a select statement that is stored with the

database

SELECT vendor_name invoice_date FROM vendors v JOIN invoices i ON v.vendor_id = i vendor_id The join in this query is coded using ________ syntax

explicit

In many cases, a subquery can be restated as an

join

To retrieve rows in which an expression matches a string pattern called a mask, you can use the ________ keyword followed by the mask

like

The most common type of a relationship between two tables is a/an ________ relationship

one-to-many

When a column in a table is defined , it given a data ________ that determines what kind of data it can store.

type

if you assign an alias to one table in a join, you have to

use that name for the table

A subquery can return a result set, a column of one or more rows, or a single ________.

value