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
|
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?
|
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)
|
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
|
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?
|
6: | Which of the following statements will return only 30 rows from the employee table?
|
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?
|
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
|
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?
|
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?
|
12: | To insert the current date into the column named dt in the table foo, which of the following statements should be used?
|
13: | Which of the following statements deletes all the rows from the table?
|
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)
|
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
|
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
|
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
|
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
|
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 ) )
|
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 ) )
|