Chapter 02 Introducing SQL

Question 1

What is DML and what is it used for?

Answer 1

Database Manipulation Language is used to access, insert, modify, or delete data in the existing structures of the database.

DML Statements: SELECT, INSERT, UPDATE, DELETE, MERGE, EXPLAIN PLAN, LOCK TABLE,

Question 2

What is DDL and what is it used for?

Answer 2

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

DDL Statements: CREATE, ALTER, DROP, RENAME, TRUNCATE, GRANT, REVOKE, AUDIT, NOAUDIT, COMMENT

Question 3

How do you write the concatenation operator?

Answer 3

||

‘Oracle12c’ || ‘Database’ results in ‘Oracle12cDatabase’.

Question 4

What is a query?

Answer 4

A query is a request for information from the database tables.

Question 5

Does query modify data? If not, what does it do?

Answer 5

Queries do not modify data; they read data from database tables and views.

Question 6

A table is used to [Blank] and is [Blank] in rows and columns.

Answer 6

A table is used to store data and is stored in rows and columns.

Question 7

What does the SELECT statement do?

Answer 7

It allows you to retrieve information already stored in the database.

Question 8

Column Alias Name Keyword

Answer 8

AS

Question 9

Keyword to ensure unique rows

Answer 9

DISTINCT

Question 10

What is the DUAL table and what is it used for?

Answer 10

The DUAL table is a special table available to all users in the database. The DUAL table is mostly used to select system variables or to evaluate an expression.

Question 11

What is the LIKE keyword?

Answer 11

Using the LIKE operator, you can perform pattern matching.
The pattern-search character % is used to match any character and any number of characters.
The pattern-search character _ is used to match any single character.

Question 12

What is the ORDER BY keyword?

Answer 12

ORDER BY clause to sort the resulting rows in a specific
order based on the data in the columns.
ASC
DESC

Question 13

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

Answer 13

C. Column alias names enclosed in quotation marks will appear as typed. Spaces and mixed case appear in the column alias name only when the alias is enclosed in double quotation marks.

Question 14

SELECT empno enumber, ename FROM emp ORDER BY 1;
2. SELECT empno, ename FROM emp ORDER BY empno ASC;
Which of the following is true?
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.

Answer 14

A. Statements 1 and 2 will produce the same result. You can use the column name,
column alias, or column position in the ORDER BY clause. The default sort order is
ascending. For a descending sort, you must explicitly specify that order with the DESC
keyword.

Question 15

You issue the following SELECT statement on the EMP table shown in question 2.
SELECT (200+((salary*0.1)/2)) FROM emp;
What will happen to the result if all the parentheses are removed?
A. No difference, because the answer will always be NULL.
B. No difference, because the result will be the same.
C. The result will be higher.
D. The result will be lower.

Answer 15

B. In the arithmetic evaluation, multiplication and division have precedence over addition
and subtraction. Even if you do not include the parentheses, salary*0.1 will be
evaluated first. The result is then divided by 2, and its result is added to 200.

Question 16

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. ||

Answer 16

A, C. Character literals in the SQL statement are enclosed in single quotation marks.
Literals are concatenated using ||. Employee Name: is a character literal, and 10 is a
numeric literal.

Question 17

What will happen if you query the EMP table shown in question 2 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.

Answer 17

D. DISTINCT is used to display a unique result row, and it should follow immediately after
the keyword SELECT. Uniqueness is identified across the row, not by a single column.

Question 18

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

Answer 18

B. The WHERE clause is used to filter the rows returned from a query. The WHERE clause
condition is evaluated, and rows are returned only if the result is TRUE. The ORDER BY
clause is used to display the result in a certain order. The OFFSET and FETCH clauses are
used to limit the rows returned.

Question 19

The following listing shows the records of the EMP table:
EMPNO ENAME SALARY COMM DEPTNO
--------- ---------- --------- --------- ---------
7369 SMITH 800 20
7499 ALLEN 1600 300 30
7521 WARD 1250 500 30
7566 JONES 2975 20
7654 MARTIN 1250 1400 30
7698 BLAKE 2850 30
7782 CLARK 2450 24500 10
7788 SCOTT 3000 20
7839 KING 5000 50000 10
7844 TURNER 1500 0 30
7876 ADAMS 1100 20
7900 JAMES 950 30
7902 FORD 3000 20
7934 MILLER 1300 13000 10
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.

Answer 19

B. There are three records belonging to DEPTNO 10: EMPNO 7934 (MILLER), 7839 (KING),
and 7782 (CLARK). When you sort their names by descending order, MILLER is the first
row to display. You can use alias names and columns that are not in the SELECT clause
in the ORDER BY clause.

Question 20

Refer to the listing of records in the EMP table in question 9. 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

Answer 20

D. Here, a character column is compared against a string using the BETWEEN operator, which is equivalent to ename >= ‘A’ AND ename <= ‘C’. The name CLARK will not be included in this query, because ‘CLARK’ is > ‘C’.

Question 21

Refer to the EMP table in question 2. When you issue the following query, which line
has an error?
1. SELECT empno “Enumber”, ename “EmpName”
2. FROM emp
3. WHERE deptno = 10
4. AND “Enumber” = 7782
5. ORDER BY “Enumber”;
A. 1
B. 5
C. 4
D. No error; the statement will finish successfully.

Answer 21

C. Column alias names cannot be used in the WHERE clause. They can be used in the ORDER BY clause.

Question 22

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. >=

Answer 22

A. The IN operator can be used. You can write the WHERE clause as WHERE empno IN
(7782, 7876);. Using the =ANY operator also produces the same result.

Question 23

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 '\';

Answer 23

C. Because _ is a special pattern-matching character, you need to include the ESCAPE
clause in LIKE. The % character matches any number of characters including 0, and _
matches a single character.

Question 24

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;

Answer 24

C. A CASE expression begins with the keyword CASE and ends with the keyword END.

Question 25

``` The EMPLOYEE table has the following data: EMP_NAME HIRE_DATE SALARY ---------- --------- ---------- SMITH 17-DEC-90 800 ALLEN 20-FEB-91 1600 WARD 22-FEB-91 1250 JONES 02-APR-91 5975 WARDEN 28-SEP-91 1250 BLAKE 01-MAY-91 2850 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. ```

Answer 25

B. The default sorting order for a numeric column is ascending. The columns are sorted first by salary and then by name, so the row with the lowest salary is displayed first. It is perfectly valid to use a column in the ORDER BY clause that is not part of the SELECT clause.

Question 26

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;

Answer 26

D. In the SELECT clause, the column names should be separated by commas. An alias name may be provided for each column with a space or by using the keyword AS. The FROM clause should appear after the SELECT clause. The WHERE clause appears after the FROM clause. The ORDER BY clause comes after the WHERE clause.

Question 27

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

Answer 27

B. Column alias names cannot be used in the WHERE clause of the SQL statement. In the ORDER BY clause, you can use the column name or alias name, or you can indicate the column by its position in the SELECT clause.

Question 28

``` 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.) PRODUCT_ID PRODUCT_NAME SHELF AVAILABLE_QTY ---------- -------------------- ------ ------------- 1001 CREST BL36 354 1002 COLGATE BL36 54 1003 AQUAFRESH BL37 43 2002 SUNNY-D LA21 53 2003 CAPRISUN LA22 45 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; ```

Answer 28

D. % is the wild character to pattern-match for any number of characters. Option A is almost correct, except for the SORT keyword in the ORDER BY clause, which will produce an error because it is not a valid syntax. Option B will produce results but will sort them in the order you want. Option C will not return any rows because LIKE is the operator for pattern matching, not =. Option E has an error similar to Option A.

Question 29

``` The EMP table has the following data: EMPNO ENAME SAL COMM ---------- ---------- ---------- ---------- 7369 SMITH 800 7499 ALLEN 1600 300 7521 WARD 1250 500 7566 JONES 2975 7654 MARTIN 1250 1400 7698 BLAKE 2850 7782 CLARK 2450 7788 SCOTT 3000 7839 KING 5000 7844 TURNER 1500 0 7876 ADAMS 1100 7900 JAMES 950 7902 FORD 3000 7934 MILLER 1300 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; 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. ```

Answer 29

C. In the first SQL statement, the comm IN (0, NULL) will be treated as comm = 0 OR comm = NULL. For all NULL comparisons, you should use IS NULL instead of = NULL. The first SQL statement will return only one row where comm = 0, whereas the second SQL will return all the rows that have comm = NULL as well as comm = 0.

Question 30

Consider the EMP table in the previous question. 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;

Answer 30

A. Option A is correct. It includes the OFFSET 3 clause to skip the first three rows and the FETCH 1 ROW clause to get the fourth row. The WITH TIES option retrieves all records if there is a tie for the fourth position. Option B SQL will error out because the SQL is missing the ONLY or WITH TIES clause—one should be specified. Even after the syntax is fixed, the result will be wrong. The sorting is performed in the reverse order. Option C is similar to Option A, but will retrieve the fifth position instead of the fourth. Option D will produce the wrong results—it is missing the DESC keyword as well as the OFFSET clause.