Learning Visually with Examples [Electronic resources]

Given the following

employee table:

empno    firstnme     lastname      salary
----------------------------------------------
000010   Peter        Smith       38752.00
000020   Christine    Haas        52234.00
000030   John         Geyer       38250.00
000040   Irving       Poon        40175.00
000050   Eva          Pulaski     36170.00

How many rows are returned from the following SQL statement?

SELECT empno 
FROM employee 
WHERE lastname LIKE 'P%' 
AND salary > 38500 

1. 1

2. 2

3. 3

4. 4

5. 5

Given the following table

t1 :

id    job     bonus
---------------------------
1     Mgr     -
2     Sales   10
3     Mgr     -
4     DBA     15

Which of the following SQL statement will retrieve the rows that have unknown values in the

bonus column?

1. SELECT * FROM t1 WHERE bonus = NULL

2. SELECT * FROM t1 WHERE bonus = ''

3. SELECT * FROM t1 WHERE bonus = '' OR bonus = 'NULL'

4. SELECT * FROM t1 WHERE bonus IS NULL

5. SELECT * FROM t1 WHERE bonus = '' OR bonus = ""

Given the following table

t1 :

id    job     bonus
---------------------------
1     Mgr     -
2     Sales   10
3     Mgr     -
4     DBA     15

Which of the following describes the result if this statement is executed in DB2 V8.1 FixPak 6?

SELECT id, job FROM OLD TABLE (DELETE FROM t1 WHERE bonus IS NULL) 

1. The statement will fail because a SELECT statement cannot contain a DELETE statement.

2. The statement will succeed if no row is found for the DELETE statement.

3. The statement will succeed and return the number of rows deleted with the DELETE statement.

4. The statement will succeed and return the number of rows left in

   t1 after the DELETE statement is executed.

Given the following tables:

Student_classA
NAME         AGE
---------------------
Mary          30
Peter         35
John          45
Lilian        38
Raymond       26
Lilian        24
Peter         38
Peter         40
Student_classB
NAME         AGE
---------------------
Paul          26
Peter         35
Peter         29
Christ        32
Raymond       26
Lilian        24

If the following SQL statement is executed, how many rows will be returned?

SELECT name FROM student_classA 
INTERSECT 
SELECT name FROM student_classB 

1. 1

2. 2

3. 3

4. 4

5. 5

If you are working on a UNIX system and you want to select all rows from the

org table, which of the following commands must be used?

1. db2 select * from org

2. db2 select(*) from org

3. db2 "select * from org"

4. db2 "select(*) from org"

Which of the following statements will return only 30 rows from the

employee table?

1. SELECT FIRST 30 ROWS FROM employee

2. SELECT * FROM employee READ FIRST 30 ROWS ONLY

3. SELECT * FROM employee OPTIMIZE FOR 30 ROWS

4. SELECT * FROM employee FETCH FIRST 30 ROWS ONLY

Which of the following is a valid wildcard character in a LIKE clause of a SELECT statement?

1. %

2. _

3. *

4. @

Given the following

CREATE TABLE statement:

CREATE TABLE employee 
( id   INTEGER NOT NULL 
, name VARCHAR(50) 
, dept VARCHAR(10) NOT NULL DEFAULT 'A00' 
, PRIMARY KEY (id) ) 

Which two of the following statements will execute successfully?

1. INSERT INTO employee VALUES ( NULL, NULL, 'A00')

2. INSERT INTO employee (name, dept) VALUES ('Peter', DEFAULT)

3. INSERT INTO employee (id, name) VALUES (1234, 'Peter')

4. INSERT INTO employee (id) VALUES (1234)

Given the following table:

CREATE TABLE employee
( id   INTEGER NOT NULL
, name VARCHAR(50)
, dept VARCHAR(10) NOT NULL DEFAULT 'A00'
, PRIMARY KEY (id) )

If the following

SELECT statement is executed, which of the following describes the order of the rows in the result returned?

SELECT id FROM employee 

1. The rows are not sorted in any particular order.

2. The rows are sorted by

   id in ascending order.

3. The rows are sorted by

   id in descending order.

4. The rows are ordered based on the sequence of when the data were inserted into the table.

Given the following table,

newborn :

baby_name      birth_date        doctor_name
--------------------------------------------------
JEREMY         05/22/2005        REICHER
KATHY          03/03/2005        WONG
CHLOE          01/23/2005        RICCI
WESLEY         10/24/2004        ATKINSON
FIONA          12/25/2004        JOHNSON

Which of the statements returns the list of baby names and their doctors who were born five days ago?

1. SELECT baby_name, doctor_name

   FROM newborn

   WHERE birth_date = DAYADD(TODAY, 5)

2. SELECT baby_name, doctor_name

   FROM newborn

   WHERE birth_date = CURRENT DATE 5 DAYS

3. SELECT baby_name, doctor_name

   FROM newborn

   WHERE birth_date < TODAY 5 DAYS

4. SELECT baby_name, doctor_name

   FROM newborn

   WHERE birth_date = DAYADD(CURRENT DATE, 5)

Given a table created using the statement:

CREATE TABLE foo (c1 INT, c2 INT, c3 INT) 

To retrieve only the columns c1 and c3 from the table, which of the following statements should be used?

1. SELECT * FROM foo

2. SELECT c1,c3 FROM foo

3. SELECT 1,3 FROM foo

4. SELECT columns 1 and 3 FROM foo

To insert the current date into the column named

dt in the table

foo , which of the following statements should be used?

1. INSERT INTO foo (dt) VALUES date

2. INSERT INTO foo (dt) VALUES current date

3. INSERT INTO foo (dt) VALUES (date)

4. INSERT INTO foo (dt) VALUES (currentdate)

Which of the following statements deletes all the rows from the table?

1. DELETE FROM foo

2. DELETE * FROM foo

3. DELETE (SELECT * FROM foo)

4. DELETE ALL FROM foo

Given the following tables:

Table T                Table S
col 1     col2         col 1     col2
-------   ------       -------   ------
2         Raul         2         Susan
4         Mary         5         Clara
8         Tom          6         Jenny
9         Glenn        9         Luisa

How many rows will the following MERGE statement return?

MERGE INTO t USING s 
ON   t.col1 = s.col1 
WHEN MATCHED THEN 
UPDATE SET t.col2 = s.col2 
WHEN NOT MATCHED THEN 
INSERT VALUES (s.col1, s.col2) 

1. 0

2. 2

3. 4

4. 6

5. 8

Given the same tables as in question 14, how many rows will the following statement return?

SELECT * FROM t INNER JOIN s ON s.col1 = t.col1 

1. 0

2. 1

3. 2

4. 3

5. 4

Given the same tables as in question 14, how many rows will the following statement return?

SELECT * FROM t FULL OUTER JOIN s ON s.col1 = t.col1 

1. 0

2. 2

3. 4

4. 6

5. 8

Given the same tables as in question 14, how many rows will the following statement return?

SELECT * FROM t LEFT OUTER JOIN s ON s.col1 = t.col1 

1. 0

2. 2

3. 4

4. 6

5. 8

Given the same tables as in question 14, how many rows will the following statement return?

SELECT * FROM t RIGHT OUTER JOIN s ON s.col1 = t.col1 

1. 0

2. 2

3. 4

4. 6

5. 8

Assuming the table

employee has 32 records, how many rows will the following statement return?

SELECT empno, salary 
FROM FINAL TABLE (INSERT INTO employee 
(empno, firstnme, midinit, lastname, edlevel) 
VALUES ('000999', 'SMITH', 'A', 'JOHN', 18 ), 
('001000', 'JOHNSON', 'A', 'TOM', 22 ) 
) 

1. 0

2. 2

3. 32

4. 34

5. 36

Assuming the table

employee has 10 records, how many records will there be in total after executing the following statement?

SELECT salary 
FROM NEW TABLE (INSERT INTO employee 
(empno, firstnme, midinit, lastname, edlevel) 
VALUES ('000999 ', 'SMITH ', 'A ', 'JOHN ', 18 ), 
('001000 ', 'JOHNSON ', 'A ', 'TOM ', 22 ) ) 

1. 0

2. 2

3. 4

4. 10

5. 12