Chapters 6,7,11 - Final
The wildcard operator in SQL statements is
*
What will be returned when the following SQL statement is executed? SELECT driver_no, count(*) as num_deliveries FROM deliveries WHERE state = 'MA' GROUP BY driver_no;
A listing of each driver who made deliveries to state = 'MA' as well as the number of deliveries that each driver has made to that state
What results would the following SQL statement produce? SELECT owner, table_name FROM dba_tables WHERE table_name = 'CUSTOMER';
A listing of the owner of the customer table
What does the following SQL statement do? UPDATE Product_T SET Unit_Price = 775 WHERE Product_ID = 7
Changes the unit price of Product 7 to 775
The technique for optimizing the internal performance of the relational data model:
Clustering Data
The first part of an SQL query to read is the ______ statement.
FROM
What command can produce Scalar and vector aggregates?
GROUP BY
A view may not be updated directly if it contains
HAVING clause
Which of the following finds all groups meeting stated conditions? Where Find Select Having
Having
_______ differs from array because it can contain duplicates.
MULTI-SET
The new data types added in SQL 2008
MULTISET, XML, BIGINT
The following code is an example of a: SELECT CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostalCode FROM Customer_T WHERE Customer_T.CustomerID = (SELECT Order_T.CustomerIDFROM Order_T WHERE OrderID = 1008);
Subquery
What results will be produced by the following SQL query? SELECT SUM(standard_price) as Total_Price FROM Product_V WHERE Product_Type = 'WOOD';
The total price of all products that are of type wood
The purpose for the SQL standard is
To specify syntax and semantics of SQL data definition and manipulation
EXISTS will take a value of ________ if the subquery returns an intermediate results table which contains one or more rows.
True
The outer join syntax does not apply easily to a join condition of more than _______ tables.
Two
The ________ operator is used to combine the output from multiple queries into a single result table.
UNION
Multiple values returned from an SQL query that includes an aggregate function are called:
Vector Aggregates
In an SQL statement, what part states the condition for the row selection?
WHERE
RANK and DENSE-RANK are examples of:
Windows Functions
Given a table named store with 5 fields: store_id, address, city, state, zipcode, why would the following insert command not work? INSERT INTO store VALUES ('234 Park Street')
You must specify the fields to insert if you are only inserting some of the fields.
The MERGE command
allows one to combine the INSERT and UPDATE operations.
In order for two queries to be UNION-compatible, they must:
both output compatible data types for each column and return the same number of rows.
A procedure is
called by a name
User Defined data types
can have defined functions and methods
A join operatoin
causes two tables with a common domain to be combined into a single table or view
The UNION clause is used to:
combine the output from multiple queries into a single result table.
In SQL, a(n) ________ subquery is a type of subquery in which processing the inner query depends on data from the outer query.
correlated
The benefits of a standardized relational language include:
cross system communication
Any create command may be reversed by using a(n) ________ command.
drop
The advantages of SQL-Invoked routines is
efficiency, sharability, flexibility
The following code is an example of a(n): SELECT Customer_T.CustomerID, Order_T.CustomerID,CustomerName, OrderID FROM Customer_T, Order_T WHERE Customer_T.CustomerID = Order_T. CustomerID;
equi-join
SQL-invoked routines can be:
functions or procedures.
Embedded SQL consists of:
hard-coded SQL statements included in a program written in another language.
The first in a series of steps to follow when creating a table is to:
identify each attribute and its characteristics
One major advantage of the outer join is
information is not lost
Dynamic SQL
is used to generate appropriate SQL code on the fly as an application is processing.
Explicit commands to manage transactions are needed when:
multiple SQL commands must be run as part of a transaction.
A join that is based upon equality between values in two common columns with the same name and where one duplicate column has been removed is called a(n):
natural join
A join in which the joining condition is based on equality between values in the common columns is called a(n):
natural join and equi-join
The following code would include: SELECT Customer_T.CustomerID, Order_T.CustomerID,CustomerName, OrderID FROM Customer_T, Order_T WHERE Customer_T.CustomerID = Order_T. CustomerID;
only rows that match both Customer_T and Order_T Tables.
A join in which rows that do not have matching values in common columns are still included in the result table is called a(n):
outer join
Indexes are created in most RDBMS to
provide rapid random and sequential access to base-table data.
While triggers run automatically, ________ do not and have to be called.
routines
A type of join where a table is joined to itself is called a(n):
self join
An operation to join a table to itself is called a(n):
self join
An interactive command that can be used to dynamically control a user session for appropriate integrity measures is:
set autocommit
A type of query that is placed within a WHERE or HAVING clause of another query is called a:
subquery
SQL provides the ________ technique, which involves placing an inner query within the WHERE or HAVING clause of an outer query.
subquery
User-defined transactions can improve system performance because:
transactions are processed as sets, reducing system overhead.
A named set of SQL statements that are considered when a data modification occurs are called:
triggers
Establishing IF-THEN-ELSE logical processing within an SQL statement can be accomplished by:
using the CASE keyword in a statement
What does the following SQL command do? INSERT INTO Customer_T VALUES (001, 'John Smith', '231 West', 'Boston', 'MA', '02115'):
Adds new record to the Customer_T
What does the following SQL statement do? ALTER TABLE Customer_T ADD (Type Varchar (2));
Alters the Customer_T table, and adds a field called "Type"
The command for creating a database is
Create Schema
The _______ DBA view shows information about all users of the database in oracle
DBA_USERS
What does the following SQL statement do? DELETE FROM Customer_T WHERE state = 'HI';
Deletes all records from customer_t where the state is equal to HI
A ___________ is a temporary table used in the FROM clause of an SQL query.
Derived Table
The following code would include: SELECT Customer_T.CustomerID,CustomerName, OrderID FROM Customer_T LEFT OUTER JOIN Order_T ON Customer_T.CustomerID = Order_T.CustomerID;
all rows of the Customer_T Table regardless of matches with the Order_T Table.
Counts ONLY rows that contain a value COUNT or COUNT(*)
COUNT
_____ use the result of the outer query to determine the processing of the inner query.
Correlated subqueries
The SQL command ________ defines a logical table from one or more tables or views.
Create View
If the DBA wishes to describe all tables in the database, which data dictionary view should be accessed in Oracle? DBA_TAB_COMMENTS DBA_TABLE_LABEL DBA_TABLES DBA_TAB_PRIVS
DBA_TABLES
To eliminate duplicate rows in a query, the _____ qualifier is used in the SQL SELECT command.
DISTINCT
INSERT INTO is an example of ________ code.
DML
_____ is a set of commands used to update and query a database.
DML
The main concept of relational database was published in 1970 by
E.F. Codd
Which of the following questions is answered by the SQL statement? SELECT COUNT (Product_Description) FROM Product_T;
How many products have product descriptions in the product table?
The most commonly used form of join operation is the:
Natural Join
A new set of analytical functions added in SQL:2008 is referred to as:
OLAP Functions
The last part of an SQL query to be read is the ________ statement.
ORDER BY
To get all the customers from Hawaii sorted together, which of the following would be used?
ORDER BY
Extensions defined in SQL-99 that include the capability to create and drop modules of code stored in the database schema across user sessions are called:
Persistent Stored Modules
The guidelines for a better query design are
Retrieve only the data that you need, Understand how indexes are used in query processing, Write simple queries.
A single value returned from an SQL query that includes an aggregate function is called a(n):
Scalar Aggregate
The ________ is the structure that contains descriptions of objects such as tables and views created by users.
Schema
Which of the following will produce the minimum of all standard prices? A) Select min(standard_price) from Product_V; B)Select Standard_Price from min(Product_V); C) Select min(Standard_Price) from Product_V where Standard_Price = min(Standard_Price); D) Select standard_price from Product_V where Standard_Price = min;
Select min(standard_price) from Product_V;
What does the following SQL Statement do? SELECT * FROM Customer WHERE Cust_Type = "Best"
Selects all the fields from the Customer Table for each row with a customer label "Best"
What result set is returned from the following query? SELECT Customer_Name, telephone FROM customers WHERE city in ('Boston','New York','Denver');
The Customer_Name and telephone of all customers living in either Boston, New York or Denver
What result set will the following query return? SELECT Item_No, description FROM item WHERE weight > 100 AND weight < 200;
The Item_No and description for all items weighing between 101 and 199
What result set will the following query return? SELECT Item_No FROM Order_V WHERE quantity > 10;
The Item_No of all orders that had more than 10 items
What is true about the order in which SQL statements are evaluated?
The SELECT clause is processed before the ORDER BY clause
What result will the following SQL statement produce? SELECT AVG(standard_price) as average FROM Product_V;
The average Standard_Price of all products in Product_V
The following code would include: SELECT Customer_T.CustomerID,CustomerName, OrderID FROM Customer_T RIGHT OUTER JOIN Order_T ON Customer_T.CustomerID = Order_T.CustomerID;
all rows of the Order_T Table regardless of matches with the Customer_T Table.
What will be returned when the following SQL query is executed? SELECT driver_no, count(*) as num_deliveries FROM deliveries GROUP BY driver_no HAVING count(*) > 2;
A listing of all drivers who made more than 2 deliveries as well as a count of the number of deliveries
What will be returned when the following SQL statement is executed? SELECT driver_no,count(*) as num_deliveries FROM deliveries GROUP BY driver_no;
A listing of each driver as well as the number of deliveries that he or she has made
The parts of a coding structure are
Action, Event, Condition
______ is a set of commands used to control a database, including security.
DCL
A ________ view is materialized when referenced.
Dynamic
DDL is typically used during which phases of the development process?
Physical Design
Referential Integrity Constraints are generally established between
Primary and Foreign Keys
Requiring a CustomerID to exist in a Primary table before it can exist in a foreign table is an example of:
Referential Integrity
Relational databases are heavily based on the mathematical concept of:
Set Theory
What will result from the following SQL Select statement? SELECT MIN(Product_Description) FROM Product_V;
The first product description alphabetically in Product_V will be shown.
The SQL command ______ adds one or more new columns to a table.
ALTER TABLE
In order to embed SQL inside of another language, the ________ statement must be placed before the SQL in the host language.
EXEC SQL
______ takes a value of TRUE if a subquery returns an intermediate results table which contains one or more rows.
EXISTS