DBA 120: Database Programming I - Chapter 2 (uCerfity)

Which two statements regarding substitution variables are true? A. &variable is defined by SQL*Plus, and its value will be available for the duration of the session. B. &&variable is defined by SQL*Plus, and its value will be available for the duration of the session. C. &n (where n is any integer) variables are defined by SQL*Plus when values are passed in as arguments to the script, and their values will be available for the duration of the session. D. &&variable is defined by SQL*Plus, and its value will be available only for every reference to that variable in the current SQL.

&&variable is defined by SQL*Plus, and its value will be available for the duration of the session. &n (where n is any integer) variables are defined by SQL*Plus when values are passed in as arguments to the script, and their values will be available for the duration of the session.

In the following SELECT statement, which component is a literal? (Choose all that apply.) SELECT 'Employee Name: ' || ename FROM emp WHERE deptno = 10; A. 10 B. ename C. Employee Name: D. ||

10 Employee Name:

the following listing shows the records of the EMP table: How many rows will the following query return? SELECT * FROM emp WHERE ename BETWEEN 'A' AND 'C' A. 4 B. 2 C. A character column cannot be used in the BETWEEN operator D. 3

3

the following listing shows the records of the EMP table: When you issue the following query, which value will be displayed in the first row? SELECT empno FROM emp WHERE deptno = 10 ORDER BY ename DESC; A. MILLER B. 7934 C. 7876 D. No rows will be returned because ename cannot be used in the ORDER BY clause

7934

Literal

A literal is a value that represents a fixed value (constant).

Which of the following is the default value of the ORDER BY clause to sort the result set? A. BOTTOM B. TOP C. DESC D. ASC

ASC

Operator

An operator is a manipulator that is applied to a data item in order to return a result.

Which of the following is used to retrieve all records from a table? A. % B. * C. & D. @

B. *

Which of the following operators tests whether or not a value lies within a range of values? A. BETWEEN B. IN C. LIKE D. EXISTS

BETWEEN

Which of the following operators will be evaluated first in a SELECT statement? A. - B. + C. / D. *

D. *

Which of the following clauses is used to prevent rows from getting duplicated in the result set? A. DISTINCT B. ORDER BY C. UPDATE D. GROUP BY

DISTINCT

Which of the following operators tests whether or not a subquery returns rows? A. NOT IN B. BETWEEN C. EXISTS D. IN

EXISTS

You issue the following query: SELECT salary "Employee Salary" FROM employees; How will the column heading appear in the result? A. EMPLOYEE SALARY B. EMPLOYEE_SALARY C. Employee Salary D. employee_salary

Employee Salary

In which of the following clauses will you include all the table names separated by commas? A. ALTER B. WHERE C. FROM D. IN

FROM

Which of the following operators tests whether or not a value exists in a list of values? A. BETWEEN B. IN C. LIKE D. EXISTS

IN

You issue the following query: SELECT empno, ename FROM emp WHERE empno = 7782 OR empno = 7876; Which other operator can replace the OR condition in the WHERE clause? A. IN B. BETWEEN ... AND ... C. LIKE D. <= E >=

IN

Which of the following statements is NOT true about the HAVING clause? A. It was added to SQL because the WHERE clause could not be used with aggregate functions. B. It specifies filtering conditions that work on groups C. It cannot be specified without a GROUP BY clause D. It is the last clause to be specified in any SQL query.

It is the last clause to be specified in any SQL query

Which of the following operators is a comparison operator used for pattern matching? A. EXISTS B. BETWEEN C. IN D. LIKE

LIKE

Which of the following operators evaluates to TRUE if the value does not exist in the list or the result set from a subquery? A. NOT IN B. IN C. NOT EXISTS D. EXISTS

NOT IN

The EMP table has the following data: What will happen if you query the EMP table with the following? SELECT empno, DISTINCT ename, salary FROM emp; A. EMPNO, unique values of ENAME, and then SALARY are displayed B. EMPNO and unique values of the two columns, ENAME and SALARY, are displayed. C. DISTINCT is not a valid keyword in SQL. D. No values will be displayed because the statement will return an error.

No values will be displayed because the statement will return an error.

Which of the following clauses is used to sort the records in your result set? A. ORDER BY B. GROUP BY C. HAVING D. WITH

ORDER BY

Which of the following clauses of the SQL statement sorts the items in the result set prior to returning them to the user? A. WHERE B. HAVING C. GROUP BY D. ORDER BY

ORDER BY

In which of the following clauses will you list the columns you are interested in from all the related tables? A. CREATE B. SELECT C. FROM D. WHERE

SELECT

Which of the following statements is used to retrieve data from a database? A. SELECT B. UPDATE C. DELETE D. DROP

SELECT

Which statement searches for PRODUCT_ID values that begin with DI_ from the ORDERS table? A. SELECT * FROM ORDERS WHERE PRODUCT_ID = 'DI%'; B. SELECT * FROM ORDERS WHERE PRODUCT_ID LIKE 'DI_' ESCAPE '\'; C. SELECT * FROM ORDERS WHERE PRODUCT_ID LIKE 'DI\_%' ESCAPE '\'; D. SELECT * FROM ORDERS WHERE PRODUCT_ID LIKE 'DI\_' ESCAPE '\'; E. SELECT * FROM ORDERS WHERE PRODUCT_ID LIKE 'DI_%' ESCAPE '\';

SELECT * FROM ORDERS WHERE PRODUCT_ID LIKE 'DI\_%' ESCAPE '\';

Which of the following SQL statements selects all rows from a table named Sales and orders the result set by the City column? A. SELECT City FROM Sales; B. SELECT * FROM Sales GROUP BY City; C. SELECT * FROM Sales ORDER BY City; D. SELECT City FROM Sales WHERE City > 1000;

SELECT * FROM Sales ORDER BY City;

The DUAL Table

The DUAL table is a special table available to all users in the database. It has one column and one row.

What is wrong with the following statements submitted in SQL*Plus? DEFINE V_DEPTNO = 20 SELECT LAST_NAME, SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID = V_DeptNo; A Nothing is wrong. The query lists the employee name and salary of the employees who belong to department 20 B. The DEFINE statement declaration is wrong C. The substitution variable is not preceded with the & character D. The substitution variable in the WHERE clause should be V_DEPTNO instead of V_DeptNo.

The substitution variable is not preceded with the & character

Which of the following clauses specifies a search condition to restrict the rows returned? A. LIKE B. BETWEEN C. WHERE D. DROP

WHERE

Column alias names cannot be used in which clause? A. SELECT clause B. WHERE clause C. ORDER BY clause D. None of these

WHERE clause

The EMP table has the following data: Consider the following two SQL statements: 1. SELECT empno, ename, sal, comm FROM emp WHERE comm IN (0, NULL); 2. SELECT empno, ename, sal, comm FROM emp WHERE comm = 0 OR comm IS NULL; Which of the following is true? A. 1 and 2 will produce the same result. B. 1 will error; 2 will work fine C. 1 and 2 will produce different results D. 1 and 2 will work but will not return any rows.

1 and 2 will produce different results

The EMPLOYEE table has the following data: What will be the value in the first row of the result set when the following query is executed? SELECT hire_date FROM employee ORDER BY salary, emp_name; A. 02-APR-91 B. 17-DEC-90 C. 28-SEP-91 D. The query is invalid, because you cannot have a column in the ORDER BY clause that is not part of the SELECT clause.

17-DEC-90

The EMP table is as follows: When you issue the following query, which line has an error? SELECT empno "Enumber", ename "EmpName" FROM emp WHERE deptno = 10 AND "Enumber" = 7782 ORDER BY "Enumber"; A. 1 B. 5 C. 4 D. No error; the statement will finish successfully.

4

Expression

An expression is a combination of one or more values, operators, and SQL functions that result in a value.

DDL

Data Definition Language (DDL) is used to define, alter, or drop database objects and their privileges.

DML

Data Manipulation Language (DML) is used to access, insert, modify, or delete data in the existing structures of the database.

Which of the following terms refers to an element in a select list that is not a column? A. Expression B. Statement C. Literal D. Keyword

Expression

Look at the data in table PRODUCTS. Which SQL statements will list the items on the BL shelves? (Show the result with the most available quantity at the top row.) A. SELECT * FROM products WHERE shelf like '%BL' ORDER BY available_qty SORT DESC; B. SELECT * FROM products WHERE shelf like 'BL%'; C. SELECT * FROM products WHERE shelf = 'BL%' ORDER BY available_qty DESC; D. SELECT * FROM products WHERE shelf like 'BL%' ORDER BY available_qty DESC; E. SELECT * FROM products WHERE shelf like 'BL%' ORDER BY available_qty SORT;

SELECT * FROM products WHERE shelf like 'BL%' ORDER BY available_qty DESC;

COUNTRY_NAME and REGION_ID are valid column names in the COUNTRIES table. Which one of the following statements will execute without an error? A. SELECT country_name, region_id,CASE region_id = 1 THEN 'Europe', region_id = 2 THEN 'America', region_id = 3 THEN 'Asia', ELSE 'Other' END ContinentFROM countries; B. SELECT country_name, region_id,CASE (region_id WHEN 1 THEN 'Europe', WHEN 2 THEN 'America', WHEN 3 THEN 'Asia', ELSE 'Other') ContinentFROM countries; C. SELECT country_name, region_id,CASE region_id WHEN 1 THEN 'Europe' WHEN 2 THEN 'America' WHEN 3 THEN 'Asia' ELSE 'Other' END ContinentFROM countries; D. SELECT country_name, region_id,CASE region_id WHEN 1 THEN 'Europe' WHEN 2 THEN 'America' WHEN 3 THEN 'Asia' ELSE 'Other' ContinentFROM countries;

SELECT country_name, region_id,CASE region_id WHEN 1 THEN 'Europe' WHEN 2 THEN 'America' WHEN 3 THEN 'Asia' ELSE 'Other' END ContinentFROM countries;

The EMP table has the following data: Which SQL code will retrieve the names of employees whose salary is at the fourth position from top? A. SELECT ename, sal FROM emp ORDER BY 2 DESC OFFSET 3 ROWS FETCH NEXT 1 ROW WITH TIES; B. SELECT ename, sal FROM emp ORDER BY 2 OFFSET 3 ROWS FETCH NEXT 1 ROW; C, SELECT ename, sal FROM emp ORDER BY 2 DESC OFFSET 4 ROWS FETCH NEXT 1 ROW ONLY; D. SELECT ename, sal FROM emp ORDER BY 2 FETCH FIRST 4 ROWS ONLY;

SELECT ename, sal FROM emp ORDER BY 2 DESC OFFSET 3 ROWS FETCH NEXT 1 ROW WITH TIES;

Which SQL statement will query the EMPLOYEES table for FIRST_NAME, LAST_NAME, and SALARY of all employees in DEPARTMENT_ID 40 in the alphabetical order of last name? A. SELECT first_name last_name salary FROM employees ORDER BY last_name WHERE department_id = 40; B. SELECT first_name, last_name, salaryFROM employees ORDER BY last_name ASC WHERE department_id = 40; C. SELECT first_name last_name salary FROM employees WHERE department_id = 40 ORDER BY last_name ASC; D. SELECT first_name, last_name, salary FROM employees WHERE department_id = 40 ORDER BY last_name; E. SELECT first_name, last_name, salary FROM TABLE employees WHERE department_id IS 40 ORDER BY last_name ASC;

SELECT first_name, last_name, salary FROM employees WHERE department_id = 40 ORDER BY last_name;

Which of the following will return the names and departments of all employees who earn a salary of more than 100000? A. SELECT name, department FROM employee_table WHERE salary != 100000; B. SELECT name, department FROM employee_table WHERE salary < 100000; C. SELECT name, department FROM employee_table WHERE salary > 100000; D. SELECT name, department FROM employee_table WHERE salary >= 100000;

SELECT name, department FROM employee_table WHERE salary > 100000;

Which of the following capabilities refers to the restriction of tuples or rows from a table? A. Projection B. Selection C. Joining D. Sorting

Selection

Which of the following filters the SELECT statement to choose only the rows that you want to be returned? A. Sorting B. Joining C. Projection D. Selection

Selection

The EMP table is as follows: You perform the following two queries: SELECT empno enumber, ename FROM emp ORDER BY 1; SELECT empno, ename FROM emp ORDER BY empno ASC; A. Statements 1 and 2 will produce the same result in data B. Statement 1 will execute; statement 2 will return an error. C. Statement 2 will execute; statement 1 will return an error. D. Statements 1 and 2 will execute but produce different results.

Statements 1 and 2 will produce the same result in data

In which of the following clauses will you define the relationship between the tables listed in the FROM clause using comparison operators? A. WHERE B. LIKE C. FROM D. SELECT

WHERE

Which clause in a query restricts the rows selected? A. ORDER BY B. WHERE C. SELECT D. FROM

WHERE

Which of the following clauses is used to determine the values that will be returned in the resultset? A. WHERE B. HAVING C. ORDER BY D. GROUP BY

WHERE