IT 214 Midtern #2

The following SQL statement uses a(n) ________. SELECT P_CODE, P_DESCRIPT, P_PRICE, V_NAME FROM PRODUCT, VENDOR WHERE PRODUCT.V_CODE = VENDOR.V_CODE;

"old-style" join

Assume you are using the UNION statement to combine the results from two identical tables CUSTOMER and CUSTOMER_2. The CUSTOMER table contains 10 rows, while the CUSTOMER_2 table contains seven rows. There are two customers included in both tables. How many records are returned when using the UNION statement?

15

How many different types of outer joins exist?

3

How many rows would be returned from a cross join of tables A and B if A contains 10 rows and B contains 20?

30

When using the Oracle TO_NUMBER function to convert a character string into a number, the ________ format character displays a digit.

9

Which operator(s) is/are used with subqueries that use inequality operators on a list of values?

ANY and ALL

________ is/are SQL character data type(s).

CHAR & VARCHAR2

The SQL command to create a database

CREATE DATABASE

The combination of all rows in the first table and all rows in the second table

Cartesian Product

data definition language

DDL:

The SQL command used to delete a row in a table

DELETE:

operator included in an ORDER BY clause when results are to be sorted in descending order

DESC

The SQL command used to list all the columns in a table and their corresponding data types

DESCRIBE

operator that eliminates duplicate values in the results of a query

DISTINCT

logical container for the database objects

Database schema

The set of all rows that are in the first table but that are not in the second table

EXISTS

To join tables, you simply list the tables in the ________ clause of the SELECT statement.

FROM

CHAR(n):

Fixed-length character data

Creates groups of tows that share some common characteristic

GROUPING

When using GROUP BY, ________ operates like the WHERE clause in the SELECT statement.

HAVING

The clause that limits a condition to the groups that are included

HAVING clause

special operator which finds a value in a group of values specified in the condition

IN

The clause that consists of the IN operator, the operator which finds a value in a group of values specified in the condition

IN clause

What type of inner join requires the use of a table qualifier?

JOIN ON

________ is a relational set operator.

MINUS

Operator that creates a temporary table containing the set of all rows that are in the first table but that are not in the second table

MINUS:

The ________ pseudo column is used to select the next available value from a sequence.

NEXTVAL

SQL command words

Reserved words

What command is used to list a unique value for Vendor Code (V_CODE), where the list will produce only a list of those values that are different from one another?

SELECT DISTINCT V_CODE FROM PRODUCT;

The clause that specifies the columns to retrieve in the query

SELECT clause

What is the syntax for a left outer join?

SELECT column-list FROM table1 LEFT [OUTER] JOIN table2 ON join-condition

The SQL command used to list all the columns in a table.

SHOW COLUMNS

Which data type is compatible with NUMBER?

SMALLINT

The current fully approved version of standard SQL prescribed by the American National Standards Institute is ________.

SQL-2003

The Oracle ________ function returns the current date.

SYSDATE

In Oracle, the ________ function converts a date to a character string.

TO_CHAR

The ________ statement combines rows from two or more queries without including duplicate rows.

UNION

Operator that creates a temporary table containing every row that is in either the first table, the second table, or both tables

UNION:

The SQL command to change the default database

USE

The SQL command used to insert values in a table

VALUES

VARCHAR(n):

Variable-length character data

Special SQL functions that apply to groups of rows and used to calculate sums, averages, counts, maximum values, and minimum values

aggregate function

A condition formed by connecting two or more simple conditions

compound condition

A column that does not exist in the database but can be computed using data in existing columns

computed column

The statement SELECT * FROM T1, T2 produces a(n) ________ join.

cross

DML

data manipulation language

A structure that contains different categories of information and the relationships between these categories

database

A software program that lets you create a database and then use it to add, change, delete, sort, and view the data in a database

database management system (DBMS):

Database to which all subsequent commands pertain

default database:

A ________ join of two tables returns rows with matching values and includes all rows from both tables with unmatched values.

full outer

A join in which all rows from both tables will be included regardless of whether they match rows from the other table

full outer join:

In a SELECT query, the attribute list can include a subquery expression, known as a(n) ________ subquery.

inline

A join that compare the tables in the FROM clause and lists only those rows that satisfy the condition in the WHERE clause

inner join:

In a subquery, which query is executed first?

innermost

A table which contains all rows that are in the two tables

intersection

If you wish to create an inner join but the two tables do not have a commonly named attribute, you can use a(n) ________ clause.

join on

The process of combining two or more tables by finding rows in the tables that have identical values in matching fields.

join:

The column on which data is to be sorted when the ORDER BY clause is used

key

A join in which all rows from the table on the left will be included regardless of whether they match rows from the table on the right

left outer join:

The more important column when data on two columns needs to be sorted

major sort key

The more important column when data on two columns needs to be sorted

minor sort key

What is wrong with the following query? SELECT V_CODE, P_CODE, P_DESCRIPT, P_PRICE FROM PRODUCT GROUP BY V_CODE;

no aggregate function is used

A(n) ________ join returns not only the rows matching the join condition but also the rows with unmatched values.

outer

A join in which all rows from one table in a join is listed, regardless of whether they match any rows in the other table

outer join:

A(n) ________ is a block of code (containing standard SQL statements and procedural extensions) that is stored and executed at the DBMS server.

persistent stored module

The more important column when data on two columns needs to be sorted

primary sort key

The combination of all rows in the first table and all rows in the second table

product:

right outer join: A join in which all rows from the table on the right will be included regardless of whether they match rows from the table on the left

right outer join

A condition that has the form: column name, comparison operator, and either another column name or a value

simple condition

When using an equality (=) or inequality (<, >, etc.) operator for a subquery, what type of value must the subquery return?

single value

The column on which data is to be sorted when the ORDER BY clause is used

sort key

A special area in which the most recently entered command is stored

statement history

INSERT INTO PRODUCT SELECT * FROM P; is an example of a ________.

subquery

: A table containing every row that is in either the first table, the second table, or both tables

union

DECIMAL(p,q):

used to represent decimal numbers

The most common type of subquery uses an inner SELECT subquery on the right side of a(n) ________ comparison expression.

where

The Oracle string concatenation function is ________.

||