| 1: | 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 2 3 4 5
|
| 2: | 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? SELECT * FROM t1 WHERE bonus = NULL SELECT * FROM t1 WHERE bonus = '' SELECT * FROM t1 WHERE bonus = '' OR bonus = 'NULL' SELECT * FROM t1 WHERE bonus IS NULL SELECT * FROM t1 WHERE bonus = '' OR bonus = ""
|
| 3: | 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)
The statement will fail because a SELECT statement cannot contain a DELETE statement. The statement will succeed if no row is found for the DELETE statement. The statement will succeed and return the number of rows deleted with the DELETE statement. The statement will succeed and return the number of rows left in t1 after the DELETE statement is executed.
|
| 4: | 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 2 3 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? db2 select * from org db2 select(*) from org db2 "select * from org" db2 "select(*) from org"
|
| 6: | Which of the following statements will return only 30 rows from the employee table? SELECT FIRST 30 ROWS FROM employee SELECT * FROM employee READ FIRST 30 ROWS ONLY SELECT * FROM employee OPTIMIZE FOR 30 ROWS SELECT * FROM employee FETCH FIRST 30 ROWS ONLY
|
| 7: | Which of the following is a valid wildcard character in a LIKE clause of a SELECT statement? % _ * @
|
| 8: | 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? INSERT INTO employee VALUES ( NULL, NULL, 'A00') INSERT INTO employee (name, dept) VALUES ('Peter', DEFAULT) INSERT INTO employee (id, name) VALUES (1234, 'Peter') INSERT INTO employee (id) VALUES (1234)
|
| 9: | 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
The rows are not sorted in any particular order. The rows are sorted by id in ascending order. The rows are sorted by id in descending order. The rows are ordered based on the sequence of when the data were inserted into the table.
|
| 10: | 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?
SELECT baby_name, doctor_name
FROM newborn
WHERE birth_date = DAYADD(TODAY, 5)
SELECT baby_name, doctor_name
FROM newborn
WHERE birth_date = CURRENT DATE 5 DAYS
SELECT baby_name, doctor_name
FROM newborn
WHERE birth_date < TODAY 5 DAYS
SELECT baby_name, doctor_name
FROM newborn
WHERE birth_date = DAYADD(CURRENT DATE, 5)
|
| 11: | 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? SELECT * FROM foo SELECT c1,c3 FROM foo SELECT 1,3 FROM foo SELECT columns 1 and 3 FROM foo
|
| 12: | To insert the current date into the column named dt in the table foo, which of the following statements should be used? INSERT INTO foo (dt) VALUES date INSERT INTO foo (dt) VALUES current date INSERT INTO foo (dt) VALUES (date) INSERT INTO foo (dt) VALUES (currentdate)
|
| 13: | Which of the following statements deletes all the rows from the table? DELETE FROM foo DELETE * FROM foo DELETE (SELECT * FROM foo) DELETE ALL FROM foo
|
| 14: | 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)
0 2 4 6 8
|
| 15: | 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
0 1 2 3 4
|
| 16: | 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
0 2 4 6 8
|
| 17: | 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
0 2 4 6 8
|
| 18: | 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
0 2 4 6 8
|
| 19: | 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 ) )
0 2 32 34 36
|
| 20: | 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 ) )
0 2 4 10 12
|
