PROGRAMMING: Section 16

TESTING
All systems needs rigorous testing before they are delivered to end users. True or False?
True
          
Testing is done by programmers. True or False?
True
          
You need not worry about Contraints on tables when testing. True or False?
False
          
What kind of transactions should you test against your tables and views?
INSERT, UPDATE, DELETE, MERGE

PROGRAMMING: Section 15

CARTESIAN PRODUCT AND THE JOIN OPERATIONS
When must column names be prefixed by table names in join syntax?
When the same column name appears in more than one table of the query
          
Will the following statement work?
SELECT department_name, last_name
FROM employees, departments
WHERE department_id = department_id;
No, Oracle will return a column ambiguously defined error.
          
If table A have 10 rows and table B have 5 rows, how many rows will be returned if you perform a cartesian join on those two tables?
50
          
Oracle proprietary JOINS can use the WHERE clause for conditions other than the join-condition. True or False?
True
          
What is the result of a query that selects from two tables but includes no join condition?
A Cartesian product
          
If table A have 10 rows and table B have 5 rows, how many rows will be returned if you perform a equi-join on those two tables?
It depends on the data found in the two tables.
          

NONEQUIJOINS
The following statement is an example of a nonequi-join?
SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j
WHERE e.salary
BETWEEN j.lowest_sal AND j.highest_sal;
True or False?
True
          
Which of the following operators is/are typically used in a nonequijoin?
>=, <=, BETWEEN ...AND
          
Which statement about joining tables with a non-equijoin is false?
A WHERE clause must specify a column in one table that is compared to a column in the second table
          


OUTER JOINS
To perform a valid outer join between DEPARMENTS and EMPLOYEES to list departments without employees select the correct WHERE clause for the following select statement:
SELECT d.department_name, e.last_name
FROM employees e, departments d
WHERE
e.department_id(+) = d.department_id
          
The following is a valid outer join statement:
SELECT c.country_name, d.department_name
FROM countries c, departments d
WHERE c.country_id (+) = d.country_id (+)
True or False?
False
          
Which symbol is used to perform an outer join?
(+)
          
The ID column in the CLIENT table that corresponds to the CLIENT_ID column of the ORDER table contains null values for rows that need to be displayed. Which type of join should you use to display the data?         
Outer join

PROGRAMMING: Section 14

DATABASE TRANSACTIONS
A transaction makes several successive changes to a table. If required, you want to be able to rollback the later changes while keeping the earlier changes. What must you include in your code to do this?
A savepoint
          
User BOB's CUSTOMERS table contains 20 rows. BOB inserts two more rows into the table but does not COMMIT his changes. User JANE now executes:
SELECT COUNT(*) FROM bob.customers;
What result will JANE see?
20
          
Which of the following best describes the term "read consistency"?
It prevents other users from seeing changes to a table until those changes have been committed
          
Table MYTAB contains only one column of datatype CHAR(1). A user executes the following statements in the order shown.
INSERT INTO mytab VALUES ('A');
INSERT INTO mytab VALUES ('B');
COMMIT;
INSERT INTO mytab VALUES ('C');
ROLLBACK;
Which rows does the table now contain?
A and B
          
If a database crashes, all uncommitted changes are automatically rolled back. True or False?
True
          
Steven King's row in the EMPLOYEES table has EMPLOYEE_ID = 100 and SALARY = 24000. A user issues the following statements in the order shown:
UPDATE employees
SET salary = salary * 2
WHERE employee_id = 100;
COMMIT;
UPDATE employees
SET salary = 30000
WHERE employee_id = 100;
The user's database session now ends abnormally. What is now King's salary in the table?
48000
          
Examine the following statements:
UPDATE employees SET salary = 15000;
SAVEPOINT upd1_done;
UPDATE employees SET salary = 22000;
SAVEPOINT upd2_done;
DELETE FROM employees;
You want to retain all the employees with a salary of 15000; What statement would you execute next?
ROLLBACK TO SAVEPOINT upd1_done;
          
Which SQL statement is used to remove all the changes made by an uncommitted transaction?
ROLLBACK;

PROGRAMMING: Section 13

CONTROLLING USER ACCESS
The following table shows some of the output from one of the data dictionary views. Which view is being queried?
USERNAME                                PRIVILEGE    ADMIN_OPTION
USCA_ORACLE_SQL01_S08       CREATE        VIEW NO
USCA_ORACLE_SQL01_S08       CREATE        TABLE NO
USCA_ORACLE_SQL01_S08       CREATE        SYNONYM NO
USCA_ORACLE_SQL01_S08       CREATE        TRIGGER NO
USCA_ORACLE_SQL01_S08       CREATE        SEQUENCE NO
USCA_ORACLE_SQL01_S08       CREATE        DATABASE NO
user_sys_privs (lists system privileges granted to the user)
          
Which Object Privilege apart from Alter can be granted to a Sequence?
SELECT
          
Object privileges are:
Required to manipulate the content of objects in the database.
          
By Controlling User Access with Oracle Database Security you can give access to specific Objects in the Database. True or False?
True
          
Which of the following Object Privileges can be granted on an individual column on a table?
Update
References
          
A Schema is a collection of Objects such as Tables, Views and Sequences. True or False?         
True
          
Which of these is not a System Privilege granted by the DBA?
Create Index
          
Which of the following is not a database object?
Subquery
          
What system privilege must be held in order to login to an Oracle database?
CREATE SESSION
          
A schema is:
A collection of objects, such as tables, views, and sequences.
          
System privileges are:
Required to gain access to the database.
          

CREATING AND REVOKING OBJECT PRIVILEGES
Which of the following statements about granting object privileges is false?
Object privileges can only be granted through roles.
          
If you are granted privileges to your friend's object, by default you may also grant access to this same object to other users. True or False?
False
          
What Oracle feature simplifies the process of granting and revoking privileges?
Role
          
User1 owns a table and grants select on it WITH GRANT OPTION to User2. User2 then grants select on the same table to User3. If User1 revokes select privileges from User2, will User3 be able to access the table?
No
          
Roles are:
Named groups of related privileges given to a user or another role.
          
When a user is logged into one database, they are restricted to working with objects found in that database. True or False?
False
          
To take away a privilege from a user you use which command?
REVOKE
          
Scott King owns a table called employees. He issues the following statement:
GRANT select ON employees TO PUBLIC;
Allison Plumb has been granted CREATE SESSION by the DBA. She logs into the database and issues the following statement:
GRANT ﾠselect ON ﾠscott_king.employees TO jennifer_cho;
True or False: Allison's statement will fail.
True
          
A role can be granted to another role. True or False?
True
          
Which of the following statements is true?
Database Links allow users to work on remote database objects without having to log into the other database.
          

REGULAR EXPRESSIONS
Select the correct REGULAR EXPRESSION functions:
REGEXP_LIKE, REGEXP_REPLACE
REGEXP_INSTR, REGEXP_SUBSTR
          
REGULAR EXPRESSIONS can be used as a part of contraint definitions?
True
          
REGULAR EXPRESSIONS does exactly the same as LIKE. No more and no less?
False
          
REGULAR EXPRESSIONS can be used on CHAR, CLOB and VARCHAR2 datatypes?
True

PROGRAMMING: Section 12

WORKING WITH SEQUENCES
A sequence is a window through which data can be queried or changed. True or False?
False
          
CURRVAL is a pseudocolumn used to refer to a sequence number that the current user has just generated by referencing NEXTVAL. True or False?
True
          
A sequence is a database object. True or False?
True
          
Which is the correct syntax for specifying a maximum value in a sequence?
Maxvalue
          
In order to be able to generate primary key values that are not likely to contain gaps, which phrase should be included in the sequence creation statement?
NOCACHE
          
Which keyword is used to remove a sequence?
Drop
         
Why do gaps in sequences occur?
All of the above
          
When you alter a sequence, a new increased MAXVALUE can be entered without changing the existing number order. True or False?
True
          
Nextval and Currval are know as column aliases. True or False?
False
          
Which keyword is used to modify a sequence?
Alter
          
CURRVAL is a pseudocolumn used to extract successive sequence numbers from a specified sequence. True or False?
False
          
Examine the code for creating this sequence:
CREATE SEQUENCE track_id_seq
INCREMENT BY 10
START WITH 1000 MAXVALUE 10000
What are the first three values that would be generated by the sequence?
1000, 1010, 1020
          

INDEXES AND SYNONYMS
It is possible to have an indexed column in a table where a value in the table column does not exist in the index. True or False?
False
          
What kind of INDEX is created by Oracle when you create a primary key?
UNIQUE INDEX
          
All tables must have indexes on them otherwise they cannot be queried. True or False?
False
          
You must use a synonym to access another users table. True or False?
False
          
In SQL what is a synonym?
A different name for a table, view or other database object
          
Which of the following SQL statements shows a correct syntax example of creating a synonym accessible to all users of a database?
CREATE PUBLIC SYNONYM emp FOR EMPLOYEES
          
Indexes can be used to speed up queries. True or False?
True
          
Which of the following statements best describes indexes and their use?
They contain the column value and pointers to the data in the table, but the data is sorted.

PROGRAMMING: Section 11

CREATING VIEWS
What is one advantage of using views?
To provide restricted data access

Views contain no data of their own. True or False?
True

Given the following CREATE VIEW statement, what data will be returned?
CREATE OR REPLACE VIEW emp_dept
AS SELECT SUBSTR(e.first_name,1,1) ||' '||e.last_name emp_name, e.salary, e.hire_date, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id AND d.department_id >=50;
First character from employee first_name concatenated to the last_name, the salary, the hire_date and department_name of all employees working in department number 50 or higher

A view can contain a select statement with a subquery. True or False?
True

A view can contain group functions. True or False?
True

Any select statement can be stored in the database as a view. True or False?
True


DML OPERATIONS AND VIEWS
Given the following view what operations would be allowed on the emp_dept view:
CREATE OR REPLACE VIEW emp_dept
AS SELECT SUBSTR(e.first_name,1,1) ||' '||e.last_name emp_name, e.salary, e.hire_date, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id AND d.department_id >=50;
SELECT, UPDATE of some columns, DELETE

If a database administrator wants to ensure that changes performed through a view do not violate existing constraints, which clause should he/she include when creating the view?
WITH CHECK OPTION
Which of the following DML operations is not allowed when using a Simple View created with read only?
All of the above

There is only one kind view? True or False?
False

Using the pseudocolumn ROWNUM in a view has no implications on the ability to do DML's through the view. True or False?
False

Examine the view below and choose the operation that CANNOT be performed on it.
CREATE VIEW dj_view (last_name, number_events) AS
SELECT c.last_name, COUNT(e.name)
FROM d_clients c, d_events e
WHERE c.client_number = e.client_number
GROUP BY c.last_name
INSERT INTO dj_view VALUES ('Turner', 8);


MANAGING VIEWS
When you drop a table referenced by a view, the view is automatically dropped as well. True or False?
False

Which of the following is true about ROWNUM?
It is the number assigned to each row returned from a query as they are read from the table.

How do you remove a view?
DROP VIEW view_name

A Top-N Analysis is capable of ranking a top or bottom set of results. True or False?
True

When you drop a view, the data it contains is also deleted. True or False?
False

Which of these Keywords is typically used with a Top-N Analysis?
Rownum

Which of these is not a valid type of View?
ONLINE

PROGRAMMING: Section 10

DEFINING NOT NULL AND UNIQUE CONSTRAINTS

A table can have more than one UNIQUE key constraint. True or False?
True


Which of the following is not a valid Oracle constraint type?
EXTERNAL KEY


A column defined as NOT NULL can have a DEFAULT value of NULL. True or False?
False


A unique key constraint can only be defined on a not null column. True or False?
False


If the employees table has a UNIQUE constraint on the DEPARTMENT_ID column, we can only have one employee per department. True or False?
True


A table must have at least one not null constraint and one unique constraint. True or False? 
False


PRIMARY KEY, FOREIGN KEY, AND CHECK CONSTRAINTS

Foreign Key Constraints are also known as:

Referential Integrity Constraints

A Primary Key that is made up of more than one column is called a:
Composite Primary Key
The employees table contains a foreign key column department_id that references the id column in the departments table. Which of the following constraint modifiers will NOT allow the deletion of id values in the department table?
Neither A nor B

Which line of the following code will cause an error:
1. CREATE TABLEMclients
2. (client_number NUMBER(4) CONSTRAINT client_client_num_pk PRIMARY KEY client_number),
3. first_name VARCHAR2(14),
4. last_name VARCHAR2(13),
5. hire_date DATE CONSTRAINT emp_min_hire_date CHECK (hire_date < SYSDATE),
6. department_id VARCHAR(3),
7. CONSTRAINT clients_dept_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id));         
Line 2

The table that contains the Primary Key in a Foreign Key Constraint is known as:
Parent Table
If a Primary Key is made up of more than one column, one of the columns can be null. True or False?
False
An example of adding a check constraint to limit the salary that an employee can earn is:
ALTER TABLE employees ADD CONSTRAINT emp_salary_ck CHECK (salary < 100000)
A composite primary key may only be defined at the table level. True or False?
True


To automatically delete rows in a child table when a parent record is deleted use:

ON DELETE CASCADE


The main reason that constraints are added to a table is:

Constraints ensure data integrity


Which of the following pieces of code will NOT successfully create a foreign key in the CDS table that references the SONGS table

None of the above


The number of check constraints that can be defined on a column is:

There is no limit
MANAGING CONSTRAINTS
What mechamisn does Oracle use in the background to enforce uniqueness in Primary and Unique key constraints?
Unique indexes are created in the background by Oracle when Primary and Unique constraints are created or enabled
Once constraints have been created on a table you will have to live with them as they are unless you drop and re-create the table. True or False?
False

You can drop a column in a table with a simple ALTER TABLE DROP COLUMN statement, even if the column is referenced in a constraint. True or False?
False
All of a user's constraints can be viewed in the Oracle Data Dictionary view called:
USER_CONSTRAINTS
The command to 'switch off' a constraint is:
ALTER TABLE DISABLE CONSTRAINT

PROGRAMMING: Section 8

CREATING TABLES
Once they are created, external tables are accessed with normal SQL statements?
True
          
I have a table named School_Friends in my schema. You want to build a table in your schema named School_Friends. This is ______________, because ____________________________________.        
possible; my schema is separate from yours, and it is okay for us to have like-named tables in our separate schemas.
          
When creating a new table, which of the following naming rules apply.
Must begin with a letter
Must contain ONLY A - Z, a - z, 0 - 9, _ (underscore), $, and #
Must be between 1 to 30 characters long
         
It is possible to create a table by using the CREATE TABLE command in conjunction with a subquery. True or False?
True
          
CREATE TABLE student_table
    (id NUMBER(6),
     lname VARCHAR(20),
     fname VARCHAR(20),
     lunch_num NUMBER(4));
Which of the following statements best describes the above SQL statement:
Creates a table named student_table with four columns: id, lname, fname, lunch_num
          
CREATE TABLE bioclass
    (hire_date DATE DEFAULT SYSDATE,
     first_name varchar2(15),
     last_name varchar2(15));
The above CREATE TABLE statement is acceptable, and will create a Table named bioclass that contains a hire_date, first_name and last_name column. True or False?
True
         
DCL, which is the acronym for Data Control Language, allows:
A Database Administrator the ability to grant privileges to users.
          
Given this employee table:
(employee_id NUMBER(10) NOT NULL,
first_name VARCHAR2(25) NOT NULL,
last_name VARCHAR2(30) NOT NULL,
hire_date DATE DEFAULT sysdate)
What will be the result in the hire_date column following this insert statement:
INSERT INTO employees VALUES (10, 'Natacha', 'Hansen', DEFAULT);
Statement will work and the hire_date column will have the value of the date when the statement was run.
          
Examine this CREATE TABLE statement:
CREATE TABLE emp_load (employee_number CHAR(5), employee_dob CHAR(20), employee_last_name CHAR(20), employee_first_name CHAR(15), employee_middle_name CHAR(15), employee_hire_date DATE)
ORGANIZATION EXTERNAL TYPE ORACLE_LOADER DEFAULT DIRECTORY def_dir1 ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE FIELDS (employee_number CHAR(2), employee_dob CHAR(20), employee_last_name CHAR(18), employee_first_name CHAR(11), employee_middle_name CHAR(11), employee_hire_date CHAR(10) date_format DATE mask "mm/dd/yyyy")) LOCATION ('info.dat'));
What kind of table is created here?
An external table with the data stored in a file outside the database.
            

USING DATA TYPES
INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. True or False?
True
          
Which of the following are valid Oracle datatypes?
DATE, TIMESTAMP WITH LOCAL TIMEZONE, BLOB
          
To store large amounts of text you should simply create a series of VARCHAR2 columns in a table. True or False?
False
          
The BLOB datatype can max hold 128 Terabytes of data. True or False?
True
          

 MODIFYING A TABLE

ALTER TABLE table_name RENAME can be used to:
Rename a table.
          
A column's data type can always be changed from NUMBER to VARCHAR2 but not from VARCHAR2 to NUMBER, provided the table is empty. True or False?
False
          
Comments can be added to a table by using the COMMENT ON TABLE statement. The comments being added are enclosed in:
Single quotes ' '
          
The FLASHBACK TABLE to BEFORE DROP can restore only the table structure, but not its data back to before the table was dropped. True or False?
False
          
After issuing a SET UNUSED command on a column, another column with the same name can be added using an ALTER TABLE statement. True or False?
True
          
Which of the following will correctly change the name of the LOCATIONS table to NEW_LOCATIONS?  
RENAME LOCATIONS TO NEW_LOCATIONS
          
You can use the ALTER TABLE statement to:
All of the above
            
The following code creates a table named student_table with four columns: id, lname, fname, lunch_num
CREATE TABLE student_table (id NUMBER(6), lname VARCHAR(20),  fname VARCHAR(20), lunch_num NUMBER(4));
The lunch_num column in the above table has been marked as UNUSED. Which of the following is the best statement you can use if you wish to remove the UNUSED column from the student_table?
ALTER TABLE DROP UNUSED COLUMNS
          
The FLASHBACK QUERY statement can restore data back to a point in time before the last COMMIT? True or False?
False
          
To completely get rid of a table, its contents, its structure, AND release the storage space the keyword is:
DROP
          
When should you use the SET UNUSED command?
You should use it when the system is being heavily used
          
You can use DROP COLUMN to drop all columns in a table, leaving a table structure with no columns. True or False?
False
          
The data type of a column can never be changed once it has been created. True or False?
False
          
When you use ALTER TABLE to add a column, the new column:
Becomes the last column in the table

PROGRAMMING: Section 7

INSERT STATEMENTS
Insert statements can be combined with subqueries to create more than one row per statement. True or False?
True
          
Which of the following statements will add a new customer to the customers table in the Global Fast Foods database?
INSERT INTO customers (id, first_name, last_name, address, city, state, zip, phone_number)
VALUES (145, 'Katie', 'Hernandez', '92 Chico Way', 'Los Angeles', 'CA', 98008, 8586667641);     

If the employees table have 7 rows how many rows are inserted into the copy_emps table with the following statement:
INSERT INTO copy_emps (employee_id, first_name, last_name, salary, department_id)
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees
7 rows, as there is no WHERE-clause on the subquery.
          
To return a table summary on the customers table, which of the following is correct?
DESCRIBE customers, or DESC customers
        
DML is an acronym that stands for:
Data Manipulation Language
          
When inserting rows into a table all columns must be given values. True or False?
False
         
When inserting a new row the null keyword can be included in the values list for any null column. True or False? 
True
          
Is it possible to insert more than one row at a time using an INSERT statement with a VALUES clause?          
No, you can only create one row at a time when using the VALUES clause.

What is the quickest way to use today's date when you are creating a new row?
Use the SYSDATE function.


UPDATING COLUMN VALUES AND DELETING ROWS
If you are performing an UPDATE statement with a subquery, it MUST be a correlated subquery?
False
          
To change an existing row in a table, you can use the UPDATE or INSERT statements. True or False?
False
          
Assuming there are no Foreign Keys on the EMPLOYEES table, if the following subquery returns one row, how many rows will be deleted from the EMPLOYEES table?
DELETE FROM employees
WHERE department_id =
(SELECT department_id
FROM departments
WHERE department_name LIKE '%Public%');
All the rows in the EMPLOYEES table with department_ids matching the department_id returned by the subquery.
          
Using your knowledge of the employees table, what would be the result of the following statement:
DELETE FROM employees;
All rows in the employees table will be deleted if there are no constraints on the table.
          
Which of the following statements best describes what will happen to the student table in this SQL statement?
UPDATE students
SET lunch_number = (SELECT lunch_number FROM student WHERE student_id = 17)
WHERE student_id = 19;
The statement updates the student_table by replacing student id 19's lunch number with student id 17's lunch number.
          
DELETE statements can use correlated subqueries?
True
          
How many rows will be deleted from the employees table with the following statement?
DELETE FROM employees
WHERE last_name = 'king';
No rows will be deleted, as no employees match the WHERE-clause.
          
If the subquery returns one row, how many rows will be deleted from the employees table?
DELETE FROM employees
WHERE department_id = (SELECT department_id FROM departments WHERE department_name LIKE '%Public%');
All rows in the employees table which work in the given department will be deleted.
          
Is the following statement valid, i.e. is it allowed to update rows in one table, based on a subquery from another table?
UPDATE copy_emp
SET department_id = (SELECT department_id FROM employees WHERE employee_id = 100)
WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 200);
Yes, this is a perfectly valid statement.
          

DEFAULT VALUES, MERGE, AND MULTI-TABLE INSERTS
The MERGE function combines the:
INSERT and UPDATE commands
          
A multi-table insert statement can insert into more than one table?
True
          
In developing the Employees table, you create a column called hire_date. You assign the hire_date column a DATE datatype with a DEFAULT value of 0 (zero). A user can come back later and enter the correct hire_date. This is __________.
A bad idea. The default value must match the DATE datatype of the column.
          
Which statement below will not insert a row of data onto a table?
INSERT INTO (id, lname, fname, lunch_num)
VALUES (143354, 'Roberts', 'Cameron', 6543);
          
If a default value was set for a null column, Oracle sets the column to the default value. However, if no default value was set when the column was created, Oracle inserts an empty space. True or False?
False
          
The MERGE statement can be used to update rows in one table based on values in another table and if the update fails, then the rows will automatically be inserted instead. True or False?
True
          
A multi-table insert statement must have a subquery at the end of the statement?
True
         
The DEFAULT keyword can be used in the following statements:
INSERT and UPDATE

PROGRAMMING: Section 6

FUNDAMENTALS OF SUBQUERIES
What will the following statement return:
SELECT last_name, salary
FROM employees
WHERE (department_id, job_id) = (SELECT department_id, job_id FROM employees WHERE employee_id = 103)
A list of last_names and salaries of employees that works in the same department and has the same job_id as that of employee 103.

What will the following statement return:
SELECT employee_id, last_name
FROM employees
WHERE salary =(SELECT MIN(salary) FROM employees GROUP BY department_id);
Nothing. It is an invalid statement.    

Which of the following statements is a true guideline for using subqueries?
The outer and inner queries can reference more than one table. They can get data from different tables.
          
What will the following statement return:
SELECT last_name, salary
FROM employees
WHERE salary < (SELECT salary FROM employees WHERE employee_id = 103)
A list of last_names and salaries of employees that makes less than employee 103
          
Examine the following statement:
SELECT last_name, salary
FROM employees
WHERE department_id = (SELECT department_id FROM employees WHERE employee_id = 103) AND job_id = (SELECT job_id FROM employees WHERE employee_id = 103)
Is this a pair-wise or non-pair-wise Subquery?
This is an example of a non-pair-wise subquery.
          
Subqueries can only be placed in the WHERE clause. True or False?
False


SINGLE ROW SUBQUERIES
If the subquery returns no rows will the outer query return any values?
No, because the subquery will be treated like a null value.
          
Subqueries are limited to four per SQL transaction. True or False?
False
          
In a non-correlated subquery, the outer query always executes prior to the inner query's execution. True or False?
False
          
Single row subqueries may not include this operator:
ALL
          
The result of this statement will be:
SELECT last_name, job_id, salary, department_id
FROM employees
WHERE job_id =(SELECT job_id FROM employees WHERE employee_id = 141) AND department_id = (SELECT department_id FROM departments WHERE location_id =1500)
Only the employees whose job id matches employee 141 and who work in location 1500


MULTIPLE-ROW SUBQUERIES
In a subquery the ALL operator compares a value to every value returned by the inner query. True or False?     
True
          
The SQL multiple-row subquery extends the capability of the single-row syntax through the use of what three comparison operators?
IN, ANY and ALL
          
Multiple-row subqueries must have NOT, IN or ANY in the WHERE clause of the inner query. True or False?
False
          
Group functions, such as HAVING and GROUP BY can be used in multiple-row subqueries. True or False?   
True
      
When a multiple-row subquery uses the NOT IN (<>ALL) operator, if one of the values returned by the inner query is a null value, the entire query returns:
No rows returned
          
Group functions can be used in subqueries even though they may return many rows. True or False?
True
          
The salary column of the f_staffs table contains the following values: 4000 5050 6000 11000 23000
Which of the following statements will return the last_name and first_name of those employees who earn more than 5000.
SELECT last_name, first_name
FROM f_staffs
WHERE salary IN (SELECT salary FROM f_staffs WHERE salary > 5000);

There can be more than one subquery returning information to the outer query. True or False?
True
          

CORRELATED SUBQUERIES
The WITH-clause is a way of creating extra tables in the database? (True or False)
False
          
Correlated Subqueries must work on the same tables in both the inner and outer query?
False
         
In a correlated subquery the outer and inner query are joined on one or more columns?
True
          
Table aliases must be used when you are writing correlated subqueries?
True

PROGRAMMING: Section 5

USING GROUP BY AND HAVING CLAUSES
Is the following statement correct?
SELECT department_id, AVG(salary)
FROM employees;
No, because a GROUP BY department_id clause is needed
          
Which of the following SQL statements could display the number of people with the same last name:      
SELECT last_name, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name;

How would you alter the following query to list only employees where there existed more than one per last_name with the same last name:
SELECT last_name, COUNT(employee_id)
FROM EMPLOYEES
GROUP BY last_name;
SELECT last_name, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name
HAVING COUNT(last_name) > 1;          

Read the following SELECT statement. Choose the column or columns that must be included in the GROUP BY clause.
SELECT COUNT(last_name), grade, gender
FROM STUDENTS
GROUP_BY ?????; 
grade, gender
          
Is the following statement correct:
SELECT first_name, last_name, salary, department_id, COUNT(employee_id)
FROM employees
WHERE department_id = 50
GROUP BY last_name, first_name, department_id;
No, because the statement is missing salary in the GROUP BY clause


USING ROLLUP AND CUBE OPERATIONS, AND GROUPING SETS
Examine the following statement:
SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS(.......);
Select the correct GROUP BY GROUPING SETS clause from the following list:
GROUP BY GROUPING SETS ((department_id, manager_id), (department_id, job_id), (manager_id, job_id))
          
Examine the following statement:
SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY ROLLUP(department_id, manager_id)
What extra data will this query generate?
The statement will fail.

Examine the following statement:
SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS((department_id, manager_id), (department_id, job_id))
What data will this query generate?
Sum of salaries for (department_id, job_id) and (department_id, manager_id)

If you want to include subtotals and grand totals for all columns mentioned in a GROUP BY clause you should use which of the following extensions to the GROUP BY clause?
CUBE
          
           
USING SET OPERATORS
MINUS will give you rows from the first query not present in the second query? (True or False)
True

INTERSECT will give you the common rows found in both queries? (True or False)
True
          
Which ones of the following are correct SET operators?
UNION, MINUS
UNION ALL, INTERSECTION

The difference between UNION and UNION ALL is
UNION will remove duplicates, UNION ALL returns all rows from all queries

PROGRAMMING: Section 4

GROUP FUNCTIONS
You can use GROUP functions in all clauses of a SELECT statement. True or False?
False  

What two group functions can be used with any datatype?
MIN, MAX
          
Given the following data in the employees table (employee_id, salary, commission_pct)
DATA: (143, 2600, null
144, 2500, null
149, 10500, .2
174, 11000, .3
176, 8600, .2
178, 7000, .15)
What is the result of the following statement:
SELECT AVG(commission_pct)
FROM employees
WHERE employee_id IN( 143,144,149,174,176,178)
0.2125
          
The following statement will work because it uses the same column with different GROUP functions:
SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)
FROM employees;
True or False?
True
          
Given the following data in the employees table (employee_id, salary, commission_pct)
DATA: (143, 2600, null
144, 2500, null
149, 10500, .2
174, 11000, .3
176, 8600, .2
178, 7000, .15)
What is the result of the following statement:
SELECT SUM(commission_pct), COUNT(salary)
FROM employees
WHERE employee_id IN( 143,144,149,174,176,178)
SUM = .85 and COUNT = 6


COUNT, DISTINCT, NVL
What would the following SQL statement return?
SELECT COUNT(DISTINCT salary)
FROM employees;
The number of unique salaries in the employees table

To include null values in the calculations of a group function, you must:
Convert the null to a value using the NVL( ) function
          
What would the following SQL statement return?
SELECT COUNT(first_name)
FROM employees;
The total number of non-null first names in the employees table    

Using your existing knowledge of the employees table, would the following two statements produce the same result?
SELECT COUNT(*)
FROM employees;
SELECT COUNT(commission_pct)
FROM employees;
No

Given the following data in the employees table (employee_id, salary, commission_pct)
DATA: (143, 2600, null
144, 2500, null
149, 10500, .2
174, 11000, .3
176, 8600, .2
178, 7000, .15)
What is the result of the following statement:
SELECT SUM(commission_pct), COUNT(commission_pct)
FROM employees
WHERE employee_id IN( 143,144,149,174,176,178)
SUM = .85 and COUNT = 4

PROGRAMMING: Section 3

CROSS JOINS AND NATURAL JOINS

A NATURAL JOIN is based on:
Columns with the same name and datatype
          
The ___________ join is the ANSI-standard syntax used to generate a Cartesian product.
CROSS
          

The join column must be included in the select statement when you use the NATURAL JOIN clause. True or False?
False
          
What happens when you create a Cartesian product?
All rows from one table are joined to all rows of another table



JOIN CLAUSES

The following is a valid SQL statement.
SELECT e.employee_id, e.last_name, d.location_id, department_id
FROM employees e JOIN departments d
USING (department_id) ;
True or False?
True
        
The primary advantage of using JOIN ON is:
It permits columns with different names to be joined
          
Table aliases MUST be used with columns referenced in the JOIN USING clause. True or False?        
False      

You can do nonequi-joins with ANSI-Syntax. True or False?
True

The keywords JOIN _____________ should be used to join tables with the same column names but different datatypes.
USING


INNER VERSUS OUTER JOINS
What is another name for a simple join or an inner join?
Equijoin
          
Given the following descriptions of the employees and jobs tables, which of the following scripts will display each employee's possible minimum and maximum salaries based on their job title?
EMPLOYEES Table:
Name  Null?   Type
EMPLOYEE_ID       NOT NULL   NUMBER (6)
FIRST_NAME                      VARCHAR2 (20)
LAST_NAME           NOT NULL   VARCHAR2 (25)
EMAIL           NOT NULL   VARCHAR2 (25)
PHONE_NUMBER              VARCHAR2 (20)
HIRE_DATE NOT NULL   DATE
JOB_ID          NOT NULL   VARCHAR2 (10)
SALARY                   NUMBER (8,2)
COMMISSION_PCT                       NUMBER (2,2)
MANAGER_ID                    NUMBER (6)
DEPARTMENT_ID              NUMBER (4)
JOBS Table:
Name  Null?   Type
JOB_ID          NOT NULL   VARCHAR2 (10)
JOB_TITLE   NOT NULL   VARCHAR2 (35)
MIN_SALARY                     NUMBER (6)
MAX_SALARY                   NUMBER (6)
SELECT first_name, last_name, job_id, min_salary, max_salary
FROM employees
NATURAL JOIN jobs;          

The following statement is an example of what kind of join?
SELECT car.vehicle_id, driver.name
FROM car
LEFT OUTER JOIN driver ON (driver_id) ;
Outer Join
          
Which syntax would be used to retrieve all rows in both the EMPLOYEES and DEPARTMENTS tables, even when there is no match?
FULL OUTER JOIN

For which of the following tables will all the values be retrieved even if there is no match in the other?
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);
employees
          
EMPLOYEES Table:
Name  Null?   Type
EMPLOYEE_ID       NOT NULL   NUMBER(6)
FIRST_NAME                      VARCHAR2(20)
LAST_NAME           NOT NULL   VARCHAR2(25)
DEPARTMENT_ID              NUMBER (4)
DEPARTMENTS Table:
Name  Null?   Type
DEPARTMENT_ID  NOT NULL   NUMBER 4
DEPARTMENT_NAME      NOT NULL   VARCHAR2(30)
MANAGER_ID                    NUMBER (6)
A query is needed to display each department and its manager name from the above tables. However, not all departments have a manager but we want departments returned in all cases. Which of the following SQL: 1999 syntax scripts will accomplish the task?
SELECT d.department_id, e.first_name, e.last_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.employee_id = d.manager_id);

If you select rows from two tables (employees and departments) using an outer join, what will you get? Use the code below to arrive at your answer:
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);
All employees including those that do not have a departement_id assigned to them


SELF JOINS VERSUS OUTER JOINS
Which of the following database design concepts is implemented with a self join?
Recursive Relationship
          
Hierarchical queries can walk both Top-Down and Bottom-Up. True or False?
True      

Which select statement will return the last name and hire date of an employee and his/ her manager for employees that started in the company before their managers?
SELECT w.last_name, w.hire_date, m.last_name, m.hire_date
FROM employees w , employees m
WHERE w.manager_id = m.employee_id
AND w.hire_date < m.hire_date

Hierarchical queries MUST use the LEVEL pseudo column. True or False?
False
          
Which SELECT statement implements a self join?
SELECT e.employee_id, m.manager_id
FROM employees e, employees m
WHERE m.employee_id = e.manager_id;

Which of the following database design concepts do you need in your tables to write Hierarchical queries?
Recursive Relationship
Google
Custom Search
  London Time
Share
Blog Archive
Final Exam Semester 1
Mid Term Exam Semester 2
Final Exam Semester 2
Best Free Domains

PROGRAMMING: Section 2

CONVERSION FUNCTIONS
You need to display the HIRE_DATE values in this format: 25th of July 2002. Which SELECT statement would you use?
SELECT TO_CHAR(hire_date, 'ddth "of" Month YYYY')
FROM employees;         

Which statement is true about SQL functions?
a, b and c are true.
          
A table has the following definition:
EMPLOYEES(
EMPLOYEE_ID NUMBER(6) NOT NULL,
NAME VARCHAR2(10) NOT NULL,
MANAGER_ID VARCHAR2(6))
and contains the following rows:
(1001, 'Bob Bevan', '200')
(200, 'Natacha Hansen', null)
Will the following query work?
SELECT *
FROM employees
WHERE employee_id = manager_id;
Yes, Oracle will perform implicit datatype conversion.
          
Which statement will return the salary of e.g. 6000 from the Employees table in the following format $6000.00?
SELECT TO_CHAR(salary, '$99999.00') SALARY
FROM employees         

The following script will run successfully. True or False?
SELECT TO_CHAR(TO_DATE("25-DEC-04" ,'dd-MON-yy'))
FROM dual
False
          
Sysdate is 12-MAY-2004.
You need to store the following date: 7-DEC-89
Which statement about the date format for this value is true?
The RR date format will interpret the year as 1989, and the YY date format will interpret the year as 2089


NULL FUNCTIONS
The following statement returns 0 (zero). True or False?
SELECT 121/NULL
FROM dual;
False          

Which function compares two expressions?
NULLIF
          
If quantity is a number datatype, what is the result of this statement?
SELECT NVL(200/quantity, 'zero') FROM inventory;
The statement fails      

With the following data in Employees (last_name, commission_pct, manager_id) what is the result of the following statement?
DATA:
King,null,null
Kochhar, null,100
Vargas, null, 124
Zlotkey,.2, 100
SELECT last_name, NVL2(commission_pct, manager_id, -1) comm
FROM employees ;
King, -1
Kochhar, -1
Vargas, -1
Zlotkey, 100          

Consider the following data in Employees table: (last_name, commission_pct, manager_id)
DATA:
King,null,null
Kochhar, null,100
Vargas, null, 124
Zlotkey,.2, 100
What is the result of the following statement:
SELECT last_name, COALESCE(commission_pct, manager_id, -1) comm
FROM employees ;
King, -1
Kochhar, 100
Vargas, 124
Zlotkey, .2


CONDITIONAL EXPRESSIONS

CASE and DECODE evaluate expressions in a similar way to IF-THEN-ELSE logic. However, DECODE is specific to Oracle syntax. True or False?
True
          
Which statement will return a listing of last names, salaries and a rating of 'Low', 'Medium', 'Good' or 'Excellent' depending on the salary value?
SELECT last_name,salary,
(CASE WHEN salary<5000 THEN 'Low'
     WHEN salary<10000 THEN 'Medium'
     WHEN salary<20000 THEN 'Good'
     ELSE 'Excellent'
END) qualified_salary
FROM employees;

For the given data from Employees (last_name, manager_id) what is the result of the following statement:
DATA:( King, null
Kochhar, 100
De Haan, 100
Hunold, 102
Ernst, 103)
SELECT last_name,
DECODE(manager_id, 100, 'King', 'A N Other') "Works For?"
FROM employees
King, A N Other
Kochhar, King
De Haan, King
Hunold, A N Other
Ernst, A N Other

Which of the following is a conditional expression used in SQL?
CASE

PROGRAMMING: Section 1

CASE AND CARACTER MANIPULATION
Identify the output from the following SQL statement:
SELECT RPAD('SQL',6, '*')
FROM DUAL;
SQL***

Which query selects the first names of the DJ On Demand clients who have a first name beginning with "A"?
SELECT UPPER(first_name)
FROM d_clients
WHERE LOWER(first_name) LIKE 'a%'

Which character manipulation function always returns a numerical value?
LENGTH

What does the following SQL SELECT statement return?
SELECT UPPER( SUBSTR('Database Programming', INSTR('Database Programming','P'),20))
FROM dual;
PROGRAMMING

Which of the following SQL statements would correctly return a song title identified in the database as "All Thesears"?     
WHERE title LIKE INITCAP('%all these years');

Which of the following are types of SQL functions?
Multi-Row Functions
Single-Row Functions

Character functions accept character arguments and only return character values. True or False?
False
       
Which query would return a user password combining the ID of an employee and the first 4 characters of the last name?
SELECT CONCAT (employee_id, SUBSTR(last_name,1,4))
AS "User Passwords"
FROM employees


NUMBER FUNCTIONS
The answer to the following script is 456. True or False?
SELECT TRUNC(ROUND(456.98))
FROM dual
False
          
Which number function may be used to determine if a value is odd or even?
MOD
           
What is the result of the following SQL Statement:
SELECT ROUND(45.923,-1)
FROM DUAL;
50
          
ROUND and TRUNC functions can be used with which of the following Datatypes?
Dates and numbers
          

DATE FUNCTIONS
What is the result of the following query?
SELECT ADD_YEARS ('11-JAN-94',6)
FROM dual;
This in not a valid SQL statement.
          
What function would you use to return the highest date in a month?
LAST_DAY
          
Which query would return a whole number if the sysdate is 26-MAY-04?
SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,'19-MAR-79') /12)
AS YEARS
FROM DUAL;
          
If hire_date has a value of '03-July-03', then what is the output from this code
SELECT ROUND(hire_date, 'Year') FROM employees;
01-JAN-04
          
Round and Trunc can be used on Date datatypes. True or False?
True
         
What is the result of the following query?
SELECT ADD_MONTHS ('11-JAN-94',6)
FROM dual;
11-JUL-1994

Database Design Mid Term Exam Semester 2

Which statement about group functions is true?
Group functions ignore null values.

The PAYMENT table contains these columns:
PAYMENT_ID NUMBER(9) PK
PAYMENT_DATE DATE
CUSTOMER_ID NUMBER(9)
Which SELECT statement could you use to display the number of times each customer payment was made between January 1, 2003 and June 30, 2003 ?
SELECT customer_id, COUNT(payment_id)
FROM payment
WHERE payment_date BETWEEN '01-JAN-2003' AND '30-JUN-2003'
GROUP BY customer_id;


Evaluate this statement:
SELECT department_id, AVG(salary)
FROM employees
WHERE job_id <> 69879
GROUP BY job_id, department_id
HAVING AVG(salary) > 35000
ORDER BY department_id;
Which clauses restricts the result?
WHERE job_id <> 69879
HAVING AVG(salary) > 35000

What is the correct order of clauses in a SELECT statement?               
SELECT FROM WHERE GROUP BY HAVING ORDER BY

The MANUFACTURER table contains these columns:
MANUFACTURER_ID NUMBER
MANUFACTURER_NAME VARCHAR2(30)
TYPE VARCHAR2(25)
LOCATION_ID NUMBER
You need to display the number of unique types of manufacturers at each location. Which SELECT statement should you use?
SELECT location_id, COUNT(DISTINCT type)
FROM manufacturer
GROUP BY location_id;


Evaluate this SELECT statement:
SELECT COUNT(employee_id), department_id
FROM employees
GROUP BY department_id;
You only want to include employees who earn more than 15000.
Which clause should you include in the SELECT statement?
WHERE salary > 15000

Evaluate this SELECT statement:
SELECT SUM(salary), department_id, manager_id
FROM employees
GROUP BY department_id, manager_id;
Which SELECT clause allows you to restrict the rows returned, based on a group function?                  
HAVING SUM(salary) > 100000


Group functions can be nested to a depth of?
Two


The EMPLOYEES table contains these columns:
ID_NUMBER NUMBER Primary Key
NAME VARCHAR2 (30)
DEPARTMENT_ID NUMBER
SALARY NUMBER (7,2)
HIRE_DATE DATE
Evaluate this SQL statement:
SELECT id_number, name, department_id, SUM(salary)
FROM employees
WHERE salary > 25000
GROUP BY department_id, id_number, name
ORDER BY hire_date;
Why will this statement cause an error?
The HIRE_DATE column is NOT included in the GROUP BY clause.


To control the order of rows returned using SET operators, the ORDER BY clause is used ______ and is placed in the _____ SELECT statement of the query.   
ONCE; LAST


When using SET operators the names of the columns must be identified in all of the SELECT statements used in the query. True or False?
False

GROUPING SETS is another extension to the GROUP BY clause and is used to specify multiple groupings of data but provide a single result set. True or False?
True

CUBE can be applied to all aggregate functions including AVG, SUM, MIN, MAX, and COUNT. True or False?
True

You use ROLLUP to:
produce subtotal values

You use GROUPING function to:                  
Identify the extra row values created by either a ROLLUP or CUBE operation


Which statement is false?
The WITH clause decreases performance.

The Oracle server performs a correlated subquery when the subquery references a column from a table referred to in the parent. True or False?
True

Which answer is incorrect? The parent statement can be:
An INSERT statement

The WITH clause enables a SELECT statement to define the subquery block at the start of the query, process the block just once, label the results, and then refer to the results multiple times. True or False?
True

If a single-row subquery returns a null value and uses the equality comparison operator, what will the outer query return?
No rows

Which comparison operator can only be used with a single-row subquery?
<>

Which of the following is a valid reason why the query below will not execute successfully?
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id =
(SELECT department_id FROM employees WHERE last_name like '%u%')
Single rather than multiple value operator used.

Which operator can be used with a multiple-row subquery?
IN

You need to display all the players whose salaries are greater than or equal to John Brown's salary. Which comparison operator should you use?
>=

You need to create a report to display the names of products with a cost value greater than the average cost of all products. Which SELECT statement should you use?
SELECT product_name
FROM products
WHERE cost > (SELECT AVG(cost)
FROM product);

The TEACHERS and CLASS_ASSIGNMENTS tables contain these columns:
TEACHERS
TEACHER_ID NUMBER(5) Primary Key
NAME VARCHAR2 (25)
SUBJECT_ID NUMBER(5)

CLASS_ASSIGNMENTS
CLASS_ID NUMBER (5) Primary Key
TEACHER_ID NUMBER (5)
DATE
MAX_CAPACITY NUMBER (3)

All MAX_CAPACITY values are greater than 10. Which two SQL statements correctly use subqueries?
SELECT *
FROM class_assignments
WHERE max_capacity = (SELECT AVG(max_capacity) FROM class_assignments);

SELECT *
FROM teachers
WHERE teacher_id = (SELECT teacher_id FROM teachers WHERE class_id = 45963);

If you use the equality operator (=) with a subquery, how many values can the subquery return?
Only 1

You need to remove a row from the EMPLOYEES table. Which statement would you use?
DELETE with a WHERE clause

You need to update both the DEPARTMENT_ID and LOCATION_ID columns in the EMPLOYEES table using one UPDATE statement. Which clause should you include in the UPDATE statement to update multiple columns?  
The SET clause

What keyword in an UPDATE statement speficies the columns you want to change?
SET

Examine the structures of the PRODUCTS and SUPPLIERS tables:
SUPPLIERS
SUPPLIER_ID NUMBER NOT NULL, Primary Key
SUPPLIER_NAME VARCHAR2 (25)
ADDRESS VARCHAR2 (30)
CITY VARCHAR2 (25)
REGION VARCHAR2 (10)
POSTAL_CODE VARCHAR2 (11)

PRODUCTS
PRODUCT_ID NUMBER NOT NULL, Primary Key
PRODUCT_NAME VARCHAR2 (25)
SUPPLIER_ID NUMBER Foreign key to SUPPLIER_ID of the SUPPLIERS table
CATEGORY_ID NUMBER
QTY_PER_UNIT NUMBER
UNIT_PRICE NUMBER (7,2)
QTY_IN_STOCK NUMBER
QTY_ON_ORDER NUMBER
REORDER_LEVEL NUMBER
You want to delete any products supplied by the five suppliers located in Atlanta. Which script should you use?
DELETE FROM products
WHERE supplier_id IN
(SELECT supplier_id FROM suppliers WHERE UPPER(city) = 'ATLANTA');


One of the sales representatives, Janet Roper, has informed you that she was recently married, and she has requested that you update her name in the employee database. Her new last name is Cooper. Janet is the only person with the last name of Roper that is employed by the company. The EMPLOYEES table contains these columns and all data is stored in lowercase:
EMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
DEPARTMENT_ID VARCHAR2 (20)
HIRE_DATE DATE
SALARY NUMBER(10)
Which UPDATE statement will accomplish your objective?
UPDATE employees
SET last_name = 'cooper'
WHERE last_name = 'roper';

One of your employees was recently married. Her employee ID is still 189, however, her last name is now Rockefeller. Which SQL statement will allow you to reflect this change?
UPDATE my_employees SET last_name = 'Rockefeller' WHERE employee_ID = 189;

Assume all the column names are correct. The following SQL statement will execute which of the following?
INSERT INTO departments (department_id, department_name, manager_id, location_id)
VALUES (70, 'Public Relations', 100, 1700);
70 will be inserted into the department_id column

You need to display each employee's name in all uppercase letters. Which function should you use?
UPPER

You query the database with this SQL statement:
SELECT CONCAT(last_name, (SUBSTR(LOWER(first_name), 4))) "Default Password"
FROM employees;
Which function will be evaluated first?
LOWER

Which SQL function is used to return the position where a specific character string begins within a larger character string?
INSTR

Which three statements about functions are true?
The SYSDATE function returns the Oracle Server date and time.
The ROUND number function rounds a value to a specified decimal place or the nearest whole number.
The SUBSTR character function returns a portion of a string beginning at a defined character position to a specified length.

The PRICE table contains this data:
PRODUCT_ID MANUFACTURER_ID
86950 59604
You query the database and return the value 95. Which script did you use?
SELECT SUBSTR(product_id, 3, 2)
FROM price
WHERE manufacturer_id = 59604;

You need to display the number of characters in each customer's last name. Which function should you use?
LENGTH

You issue this SQL statement:
SELECT INSTR ('organizational sales', 'al')
FROM dual;
Which value is returned by this command?
13

You issue this SQL statement:
SELECT ROUND (1282.248, -2)
FROM dual;
What value does this statement produce?
1300

Evaluate this function: MOD (25, 2) Which value is returned?
1

Which two functions can be used to manipulate number or date column values, but NOT character column values?
TRUNC
ROUND

You need to display the current year as a character value (for example: Two Thousand and One). Which element would you use?
YEAR

Which SELECT statement will NOT return a date value?
SELECT (SYSDATE - hire_date) + 10*8
FROM employees;

You need to display the number of months between today's date and each employee's hiredate. Which function should you use?
MONTHS_BETWEEN

You want to create a report that displays all orders and their amounts that were placed during the month of January. You want the orders with the highest amounts to appear first. Which query should you issue?
SELECT orderid, total
FROM orders
WHERE order_date BETWEEN '01-jan-02' AND '31-jan-02'
ORDER BY total DESC;

Evaluate this SELECT statement:
SELECT SYSDATE + 30
FROM dual;
Which value is returned by the query?
the current date plus 30 days

Which three statements concerning explicit data type conversions are true?
Use the TO_DATE function to convert a character string to a date value.
Use the TO_NUMBER function to convert a character string of digits to a number.
Use the TO_CHAR function to convert a number or date value to character string.

The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2 (25)
FIRST_NAME VARCHAR2 (25)
SALARY NUMBER(6)
You need to create a report to display the salaries of all employees. Which script should you use to display the salaries in format: "$45,000.00"?
SELECT TO_CHAR(salary, '$999,999.00')
FROM employees;

Which statement concerning single row functions is true?
Single row functions can be nested.

Which SQL Statement should you use to display the prices in this format: "$00.30"?
SELECT TO_CHAR(price, '$99,900.99') FROM product;

Which best describes the TO_CHAR function?
The TO_CHAR function can be used to display dates and numbers according to formatting conventions that are supported by Oracle.

The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2 (25)
FIRST_NAME VARCHAR2 (25)
HIRE_DATE DATE
You need to display HIRE_DATE values in this format: January 28, 2000
Which SELECT statement could you use?
SELECT TO_CHAR(hire_date, 'Month DD, YYYY')
FROM employees;

Which of the following General Functions will return the first non-null expression in the expression list?
COALESCE

Which statement about group functions is true?
NVL, NVL2, and COALESCE can be used with group functions to replace null values.

The STYLES table contains this data:
STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85940 12.00
968950 SANDAL 85909 10.00
869506 SANDAL 89690 15.00
809090 LOAFER 89098 10.00
890890 LOAFER 89789 14.00
857689 HEEL 85940 11.00
758960 SANDAL 86979
Evaluate this SELECT statement:
SELECT style_id, style_name, category, cost
FROM styles
WHERE style_name = 'SANDAL' AND NVL(cost, 0) < 15.00 ORDER BY category, cost;
Which result will the query provide?
STYLE_ID STYLE_NAME CATEGORY COST 968950 SANDAL 85909 10.00 895840 SANDAL 85940 12.00 758960 SANDAL 86979

When joining 3 tables in a SELECT statement, how many join conditions are needed in the WHERE clause?
2

You have the following EMPLOYEES table:
EMPLOYEE_ID NUMBER(5) NOT NULL PRIMARY KEY
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
ADDRESS VARCHAR2(35)
CITY VARCHAR2(25)
STATE VARCHAR2(2)
ZIP NUMBER(9)
TELEPHONE NUMBER(10)
DEPARTMENT_ID NUMBER(5) NOT NULL FOREIGN KEY
The BONUS table includes the following columns:
BONUS_ID NUMBER(5) NOT NULL PRIMARY KEY
ANNUAL_SALARY NUMBER(10)
BONUS_PCT NUMBER(3, 2)
EMPLOYEE_ID VARCHAR2(5) NOT NULL FOREIGN KEY
You want to determine the amount of each employee's bonus, as a calculation of salary times bonus. Which of the following queries should you issue?
SELECT e.first_name, e.last_name, b.annual_salary * b. bonus_pct FROM employees e, bonus b WHERE e.employee_id = b.employee_id;

You have been asked to create a report that lists all corporate customers and all orders that they have placed. The customers should be listed alphabetically beginning with the letter 'A', and their corresponding order totals should be sorted from the highest amount to the lowest amount. Which of the following statements should you issue?
SELECT c.custid, c.companyname, o.orderdate, o. custid, o.amount FROM customers c, orders o WHERE c.custid = o.custid ORDER BY companyname, amount DESC;

Evaluate this SQL statement:
SELECT e.employee_id, e.last_name, e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id AND employees.department_id > 5000
ORDER BY 4;
Which clause contains a syntax error?
AND employees.department_id > 5000 (*)

You need to create a report that lists all employees in department 10 (Sales) whose salary is not equal to $25,000 per year. Which query should you issue to accomplish this task?
SELECT last_name, first_name, salary
FROM employees
WHERE salary != 25000 AND department_id = 10;

What happens when you create a Cartesian product?
All rows from one table are joined to all rows of another table

Which statement about outer joins is true?
The OR operator cannot be used to link outer join conditions.

The EMPLOYEE_ID column in the EMPLOYEES table corresponds to the EMPLOYEE_ID column of the ORDERS table. The EMPLOYEE_ID column in the ORDERS table contains null values for rows that you need to display. Which type of join should you use to display the data?
outer join

Using Oracle Proprietary join syntax, which two operators can be used in an outer join condition using the outer join operator (+)?
AND and =

Which statement about a natural join is true?
Columns with the same names must have the same precision and datatype.

Which of the following best describes a natural join?
A join between two tables that includes columns that share the same name, datatypes and lengths

The following SQL statement will produce what output?
SELECT last_name, department_name
FROM employees
CROSS JOIN departments;
A Cartesian product between the two tables.

For which condition would you use an equijoin query with the USING keyword?
You need to perform a join of the CUSTOMER and ORDER tables but limit the number of columns in the join condition.

Below find the structure of the CUSTOMERS and SALES_ORDER tables:
CUSTOMERS
CUSTOMER_ID NUMBER NOT NULL, Primary Key
CUSTOMER_NAME VARCHAR2 (30)
CONTACT_NAME VARCHAR2 (30)
CONTACT_TITLE VARCHAR2 (20)
ADDRESS VARCHAR2 (30)
CITY VARCHAR2 (25)
REGION VARCHAR2 (10)
POSTAL_CODE VARCHAR2 (20)
COUNTRY_ID NUMBER Foreign key to COUNTRY_ID column of the COUNTRY table
PHONE VARCHAR2 (20)
FAX VARCHAR2 (20)
CREDIT_LIMIT NUMBER(7,2)

SALES_ORDER
ORDER_ID NUMBER NOT NULL, Primary Key
CUSTOMER_ID NUMBER Foreign key to CUSTOMER_ID column of the CUSTOMER table
ORDER_DT DATE
ORDER_AMT NUMBER (7,2)
SHIP_METHOD VARCHAR2 (5)
You need to create a report that displays customers without a sales order. Which statement could you use?
SELECT c.customer_name
FROM customers c
WHERE c.customer_id not in (SELECT s.customer_id FROM sales_order s);

The primary advantage of using JOIN ON is:
It permits columns with different names to be joined

Evaluate this SELECT statement:
SELECT a.last_name || ', ' || a.first_name as "Patient", b.last_name || ', ' || b.first_name as "Physician", c.admission
FROM patient a
JOIN physician b
ON (b.physician_id = c.physician_id)
JOIN admission c
ON (a.patient_id = c.patient_id);
Which clause generates an error?
ON (b.physician_id = c.physician_id)

What should be included in a SELECT statement to return NULL values from all tables?
full outer joins

Which query will retrieve all the rows in the EMPLOYEES table, even if there is no match in the DEPARTMENTS table?
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d ON (e.department_id = d.department_id);

Which type of join returns rows from one table that have NO direct match in the other table?
outer join

Evaluate this SELECT statement:
SELECT MIN(hire_date), department_id
FROM employees
GROUP BY department_id;
Which values are displayed?
The earliest hire date in each department.

Which statement about the GROUP BY clause is true?
The first column listed in the GROUP BY clause is the most major grouping.

Evaluate this SELECT statement:
SELECT MAX(salary), department_id
FROM employees
GROUP BY department_id;
Which values are displayed?
The highest salary in each department.

What will the following SQL Statement do?
SELECT job_id, COUNT(*)
FROM employees
GROUP BY job_id;
Displays each job id and the number of people assigned to that job id.

Which group function would you use to display the lowest value in the SALES_AMOUNT column?
MIN

Group functions return a value for ________________ and ________________ null values in their computations.
a row set, ignore

The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
SALARY NUMBER(9,2)
HIRE_DATE DATE
BONUS NUMBER(7,2)
COMM_PCT NUMBER(4,2)
Which three functions could be used with the HIRE_DATE, LAST_NAME, or SALARY columns?
MAX
MIN
COUNT

Which group function would you use to display the total of all salary values in the EMPLOYEE table?
SUM

You need to calculate the standard deviation for the cost of products produced in the Birmingham facility. Which group function will you use?
STDDEV

You need to calculate the average salary of employees in each department. Which group function will you use?
AVG

You need to compute the total salary for all employees in department 10. Which group function will you use?
SUM

Which aggregate function can be used on a column of the DATE data type?
MAX

Evaluate this SQL statement:
SELECT COUNT (amount)
FROM inventory;
What will occur when the statement is issued?
The statement will count the number of rows in the INVENTORY table where the AMOUNT column is not null.

Which SELECT statement will calculate the number of rows in the PRODUCTS table?
SELECT COUNT (*) FROM products;

The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
SALARY NUMBER(7,2)
DEPARTMENT_ID NUMBER(9)
You need to display the number of employees whose salary is greater than $50,000? Which SELECT would you use?
SELECT * FROM employees
WHERE salary < 50000; SELECT COUNT(*) FROM employees WHERE salary < 50000; SELECT COUNT(*) FROM employees WHERE salary > 50000;

Examine the data from the LINE_ITEM table:
LINE_ITEM_ID ORDER_ID PRODUCT_ID PRICE DISCOUNT
890898 847589 848399 8.99 0.10
768385 862459 849869 5.60 0.05
867950 985490 945809 5.60
954039 439203 438925 5.25 0.15
543949 349302 453235 4.50
You query the LINE_ITEM table and a value of 3 is returned. Which SQL statement did you execute?
SELECT COUNT(discount) FROM line_item;

Evaluate this SELECT statement:
SELECT COUNT(emp_id), mgr_id, dept_id
FROM employee
WHERE status = 'I'
GROUP BY dept_id
HAVING salary > 30000
ORDER BY 2;
Why does this statement return a syntax error?
MGR_ID must be included in the GROUP BY clause.

The PRODUCTS table contains these columns:
PROD_ID NUMBER(4)
PROD_NAME VARCHAR(20)
PROD_CAT VARCHAR2(15)
PROD_PRICE NUMBER(5)
PROD_QTY NUMBER(4)
You need to identify the minimum product price in each product category. Which statement could you use to accomplish this task?
SELECT prod_cat, MIN (prod_price)
FROM products
GROUP BY prod_cat;

You want to write a report that returns the average salary of all employees in the company, sorted by departments. The EMPLOYEES table contains the following columns:
EMPLOYEES:
EMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
DEPARTMENT VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(10)
Which SELECT statement will return the information that you require?
SELECT AVG (salary)
FROM employees
GROUP BY department;

Evaluate this SELECT statement:
SELECT SUM(salary), department_id, department_name
FROM employees
WHERE department_id = 1
GROUP BY department;
Which clause of the SELECT statement contains a syntax error?
GROUP BY

The EMPLOYEES table contains the following columns:
EMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
DEPARTMENT VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(10)
You want to create a report that includes each employee's last name, employee identification number, date of hire and salary. The report should include only those employees who have been with the company for more than one year and whose salary exceeds $40,000. Which of the following SELECT statements will accomplish this task?
SELECT employee_id, last_name, hire_date, salary
FROM employees
WHERE salary > 40000
AND (sysdate-hire_date) / 365 > 1;

The PRODUCTS table contains these columns:
PRODUCT_ID NUMBER(9) PK
CATEGORY_ID VARCHAR2(10)
LOCATION_ID NUMBER(9)
DESCRIPTION VARCHAR2(30)
COST NUMBER(7,2)
PRICE NUMBER(7,2)
QUANTITY NUMBER
You display the total of the extended costs for each product category by location. You need to include only the products that have a price less than $25.00. The extended cost of each item equals the quantity value multiplied by the cost value. Which SQL statement will display the desired result?
SELECT category_id, SUM(cost * quantity) TOTAL, location_id FROM products WHERE price < 25.00 GROUP BY category_id, location_id;

Which statement about the GROUP BY clause is true?
To exclude rows before dividing them into groups using the GROUP BY clause, you should use a WHERE clause.

The PLAYERS and TEAMS tables contain these columns:
PLAYERS PLAYER_ID NUMBER NOT NULL, Primary Key LAST_NAME VARCHAR2 (30) NOT NULL FIRST_NAME VARCHAR2 (25) NOT NULL TEAM_ID NUMBER POSITION VARCHAR2 (25)
TEAMS TEAM_ID NUMBER NOT NULL, Primary Key TEAM_NAME VARCHAR2 (25)
You need to create a report that lists the names of each team with more than three goal keepers. Which SELECT statement will produce the desired result?
SELECT t.team_name, COUNT(p.player_id)
FROM players p JOIN teams t ON (p.team_id = t.team_id)
WHERE UPPER(p.position) = 'GOAL KEEPER'
GROUP BY t.team_name HAVING COUNT(p.player_id) > 3;

The EMPLOYEES and ORDERS tables contain these columns:
EMPLOYEES
EMPLOYEE_ID NUMBER(10) NOT NULL PRIMARY KEY
FIRST_NAME VARCHAR2(30)
LAST_NAME VARCHAR2(30)
ADDRESS VARCHAR2(25)
CITY VARCHAR2(20)
STATE VARCHAR2(2)
ZIP NUMBER(9)
TELEPHONE NUMBER(10)

ORDERS
ORDER_ID NUMBER(10) NOT NULL PRIMARY KEY
EMPLOYEE_ID NUMBER(10) NOT NULL FOREIGN KEY
ORDER_DATE DATE
TOTAL NUMBER(10)
Which SELECT statement will return all orders generated by a sales representative named Franklin during the year 2001?
SELECT order_id, total
FROM ORDERS
WHERE employee_id = (SELECT employee_id FROM employees WHERE last_name = 'Franklin')
AND order_date BETWEEN '01-jan-01' AND '31-dec-01';

Using a subquery in which of the following clauses will return a syntax error?
You can use subqueries in all of the above clauses.

Which of the following is TRUE regarding the order of subquery execution?
The subquery executes once before the main query

Which statement about subqueries is true?
Subqueries are often used in a WHERE clause to return values for an unknown conditional value.

Which operator can be used with subqueries that return only one row?
LIKE

Examine the structure of the EMPLOYEE, DEPARTMENT, and ORDERS tables.
EMPLOYEE
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER(9)

DEPARTMENT
DEPARTMENT_ID NUMBER(9)
DEPARTMENT_NAME VARCHAR2(25)
CREATION_DATE DATE

ORDERS
ORDER_ID NUMBER(9)
EMPLOYEE_ID NUMBER(9)
DATE DATE
CUSTOMER_ID NUMBER(9)
You want to display all employees who had an order after the Sales department was established. Which of the following constructs would you use?
a single-row subquery

Examine the following EMPLOYEES table:
EMPLOYEES
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER(9)
SUPERVISOR_ID NUMBER(9)
You need to produce a report that contains all employee-related information for those employees who have Brad Carter as a supervisor. However, you are not sure which supervisor ID belongs to Brad Carter. Which query should you issue to accomplish this task?
SELECT *
FROM employees
WHERE supervisor_id =
(SELECT employee_id
FROM employees
WHERE last_name = 'Carter');

Which best describes a single-row subquery?
a query that returns only one row from the inner SELECT statement

Which best describes a multiple-row subquery?
A query that returns one or more rows from the inner SELECT statement

What is wrong with the following query?
SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary) FROM employees GROUP BY department_id);
Subquery returns more than one row and single row comparison operator is used.

Evaluate this SELECT statement:
SELECT customer_id, name
FROM customer
WHERE customer_id IN
(SELECT customer_id
 FROM customer
 WHERE state_id = 'GA' AND credit_limit > 500.00);
What would happen if the inner query returned null?
No rows would be returned by the outer query.

Evaluate this SQL statement:
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id IN
(SELECT department_id
FROM employees
WHERE salary > 30000 AND salary < 50000);
Which values will be displayed?
All employees who work in a department with employees who earn more than $30,000, but less than $50,000.

Evaluate this SELECT statement:
SELECT student_id, last_name, first_name FROM student WHERE major_id NOT IN (SELECT major_id  FROM majors  WHERE department_head_id = 30 AND title = 'ADJUNCT');
What would happen if the inner query returned a NULL value row?
No rows would be returned from the STUDENT table.

Which of the following statements contains a comparison operator that is used to restrict rows based on a list of values returned from an inner query?
All of the above.

A multiple-row operator expects how many values?
One or more

What would happen if you attempted to use a single-row operator with a multiple-row subquery?
An error would be returned.

Which operator or keyword cannot be used with a multiple-row subquery?
=

You need to display all the products that cost more than the maximum cost of every product produced in Japan. Which multiple-row comparison operator could you use?
>ANY

Evaluate the structure of the EMPLOYEE and DEPART_HIST tables:
EMPLOYEE:
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER(9)
MANAGER_ID NUMBER(9)
SALARY NUMBER(7,2)

DEPART_HIST:
EMPLOYEE_ID NUMBER(9)
OLD_DEPT_ID NUMBER(9)
NEW_DEPT_ID NUMBER(9)
CHANGE_DATE DATE
You want to generate a list of employees who are in department 10, but used to be in department 15.
Which query should you use?
SELECT employee_id, last_name, first_name, department_id
FROM employee
WHERE (employee_id, department_id) IN
(SELECT employee_id, new_dept_id
 FROM depart_hist
 WHERE old_dept_id = 15) AND new_dept_id = 10;

The PRODUCTS table contains these columns:
PRODUCT_ID NUMBER NOT NULL
PRODUCT_NAME VARCHAR2 (25)
SUPPLIER_ID NUMBER NOT NULL
LIST_PRICE NUMBER (7,2)
COST NUMBER (5,2)
QTY_IN_STOCK NUMBER(4)
LAST_ORDER_DT DATE NOT NULL DEFAULT SYSDATE
Which INSERT statement will execute successfully?
INSERT INTO products VALUES (2958, 'Cable', 8690, 7.09, 4.04, 700);

The PRODUCTS table contains these columns:
PROD_ID NUMBER(4)
PROD_NAME VARCHAR2(25)
PROD_PRICE NUMBER(3)
You want to add the following row data to the PRODUCTS table:
(1) a NULL value in the PROD_ID column
(2) "6-foot nylon leash" in the PROD_NAME column
(3) "10" in the PROD_PRICE column
You issue this statement:
INSERT INTO products
VALUES (null,'6-foot nylon leash', 10);
What row data did you add to the table?
The row was created with the correct data in all three columns.

You have been instructed to add a new customer to the CUSTOMERS table. Because the new customer has not had a credit check, you should not add an amount to the CREDIT column.
The CUSTOMERS table contains these columns:
CUST_ID NUMBER(10)
COMPANY VARCHAR2(30)
CREDIT NUMBER(10)
POC VARCHAR2(30)
LOCATION VARCHAR2(30)
Which two INSERT statements will accomplish your objective?
INSERT INTO customers (cust_id, company, poc, location)
VALUES (200, 'InterCargo', 'tflanders', 'samerica');

INSERT INTO customers
VALUES (200, 'InterCargo', null, 'tflanders', 'samerica');

Which statement about the VALUES clause of an INSERT statement is true?
If no column list is specified, then the values must be in the order the columns are specified in the table.

You need to update the area code of employees that live in Atlanta . Evaluate this partial UPDATE statement:
UPDATE employee
SET area_code = 770
Which of the following should you include in your UPDATE statement to achieve the desired results?
WHERE city = 'Atlanta';

What would happen if you issued a DELETE statement without a WHERE clause?
All the rows in the table would be deleted.

You need to delete a record in the EMPLOYEES table for Tim Jones, whose unique employee identification number is 348. The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(5) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
ADDRESS VARCHAR2(30)
PHONE NUMBER(10)
Which DELETE statement will delete the appropriate record without deleting any additional records?
DELETE FROM employees WHERE employee_id = 348;

Which two commands can be used to modify existing data in a database row?
MERGE
UPDATE

When the WHERE clause is missing in a DELETE statement, what is the result?
All rows are deleted from the table.

Which of the following represents the correct syntax for an INSERT statement?
INSERT INTO customers VALUES (3178, 'J.', 'Smith', '123 Main Street', 'Nashville', 'TN', '37777');

The EMPLOYEES table contains the following columns:
EMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
DEPARTMENT_ID NUMBER(10)
HIRE_DATE DATE
SALARY NUMBER(9,2)
BONUS NUMBER(9,2)
You want to execute one DML statement to change the salary of all employees in department 10 to equal the new salary of employee id 89898. Currently, all employees in department 10 have the same salary value. Which statement should you execute?
UPDATE employees
SET salary = (SELECT salary FROM employees WHERE employee_id = 89898)
WHERE department_id = 10;

You want to enter a new record into the CUSTOMERS table. Which two commands can be used to create new rows?
INSERT, MERGE

Examine the structures of the PLAYERS, MANAGERS, and TEAMS tables:
PLAYERS
PLAYER_ID NUMBER Primary Key
LAST_NAME VARCHAR2 (30)
FIRST_NAME VARCHAR2 (25)
TEAM_ID NUMBER
MGR_ID NUMBER
SIGNING_BONUS NUMBER(9,2)
SALARY NUMBER(9,2)

MANAGERS
MANAGER_ID NUMBER Primary Key
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
TEAM_ID NUMBER

TEAMS
TEAM_ID NUMBER Primary Key
TEAM_NAME VARCHAR2 (20)
OWNER_LAST_NAME VARCHAR2 (20)
OWNER_FIRST_NAME VARCHAR2 (20)
Which situation would require a subquery to return the desired result?
To display the names of the managers for all the teams owned by a given owner

Evaluate this statement: DELETE FROM customer; Which statement is true?
The statement deletes all the rows from the CUSTOMER table.