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