| 1: | Which statement is true?
The CHAR scalar function returns a string representation of the input value. The DECIMAL scalar function returns a decimal representation of the input value. The YEAR scalar function produces a single scalar value for each table row that satisfies the search condition. All of the above are true.
|
| 2: | Which result table below could be generated by the following
SQL
statement?
SELECT EMPNO, (SALARY + COMM)
FROM EMP;
EMPNO SALARY COMM
====== ======== ======= ========
000010 52750.00 4220.00 56970.00
000020 41250.00 3300.00 44550.00
000030 38250.00 3060.00 41310.00
000060 32250.00 2500.00 34830.00
EMPNO
====== ========
000010 56970.00
000020 44550.00
000030 41310.00
000060 34830.00
EMPNO SALARY COMM SALARY + COMM
====== ========= ======= =============
000010 52750.00 4220.00 56970.00
000020 41250.00 3300.00 44550.00
000030 38250.00 3060.00 41310.00
000060 32250.00 2500.00 34830.00
EMPNO SALARY + COMM
====== =============
000010 56970.00
000020 44550.00
000030 41310.00
000060 34830.00
|
| 3: | A programmer wants to add the content of all the rows in a numeric column named SALARY. Which
SQL
aggregate function (not fully executable) could the programmer use?
|
| 4: | Which result table below could be generated by the following
SQL
statement?
SELECT EMPNO, SALARY + COMM AS TOTAL_SAL
FROM EMP
ORDER BY TOTAL_SAL;
EMPNO SALARY COMM TOTAL_SAL
====== ========= ======= =========
000060 32250.00 2500.00 34830.00
000030 38250.00 3060.00 41310.00
000020 41250.00 3300.00 44550.00
000010 52750.00 4220.00 56970.00
EMPNO SALARY COMM SALARY + COMM
====== ========= ======= =============
000060 32250.00 2500.00 34830.00
000030 38250.00 3060.00 41310.00
000020 41250.00 3300.00 44550.00
000010 52750.00 4220.00 56970.00
EMPNO TOTAL_SAL
====== =========
000060 34830.00
000030 41310.00
000020 44550.00
000010 56970.00
EMPNO TOTAL_SAL
====== =========
000010 56970.00
000020 44550.00
000030 41310.00
000060 34830.00
|
| 5: | To calculate the average earnings of all employees who have a commission greater than 0, which statement could a programmer use?
SELECT AVERAGE(SALARY+NULLIF)(COMM,0))
FROM EMP;
SELECT AVERAGE(SALARY)+NULLIF(COMM,0)
FROM EMP;
SELECT AVG(SALARY+NULLIF(COMM>0))
FROM EMP;
SELECT AVG(SALARY+NULLIF(COMM,0))
FROM EMP;
|
| 6: | Which of the items below is not a DB2 scalar function?
None of the above; all are scalar functions.
|
| 7: | Which
SQL
statement with a CASE expression is valid?
SELECT EMPNO, LASTNAME,
CASE
WHEN EDL<=12 THEN 'HIGH SCHOOL OR LESS'
END
AS EDUCATION
FROM EMP;
SELECT EMPNO, LASTNAME,
CASE
WHEN EDL<=12 THEN 'HIGH SCHOOL OR LESS'
AS EDUCATION
FROM EMP;
SELECT EMPNO, LASTNAME,
CASE
WHEN EDL<=12 THEN 'HIGH SCHOOL OR LESS' AS EDUCATION
END
FROM EMP;
SELECT EMPNO, LASTNAME,
CASE
WHEN EDL<=12 THEN 'HIGH SCHOOL OR LESS'
ENDCASE
AS EDUCATION
FROM EMP;
|
| 8: | Which statement is false?
A WHERE clause can be used to indicate which rows of data to access. A WHERE clause specifies a search condition. The search condition of a WHERE clause includes one or more predicates that are combined through the use of logical operators AND, OR, and NOT. None of the above; all statements are true.
|
| 9: | To select the rows that contain a null value in the MIDINIT (middle initial) column, which WHERE clause could a programmer use?
|
| 10: | Which pair of WHERE clauses are not equivalent?
WHERE NOT DEPTNO < 'A00' and WHERE DEPTNO >= 'A00' WHERE NOT DEPTNO >= 'A00' and WHERE DEPTNO < 'A00' WHERE DEPTNO = 'A00' and WHERE NOT DEPTNO <> 'A00' None of the above; all pairs are equivalent.
|
| 11: | Which WHERE clause is not a valid equivalent to the others?
WHERE DEPTNO = ('B01', 'C01', 'D11') WHERE DEPTNO IN ('B01', 'C01', 'D11') WHERE DEPTNO = 'B01' OR DEPTNO = 'C01' OR DEPTNO = 'D11' None of the above; all are valid and equivalent.
|
| 12: | Which statement about the ORDER BY clause is false?
Using the ORDER BY clause is the only way to guarantee that your rows are in the sequence in which you want them. The order of the selected rows depends on the column that you identify in the ORDER BY column. Null values appear first in an ascending sort and last in a descending sort. To retrieve the results in ascending order, specify or use the default of ASC.
|
| 13: | Which statement-and-result pair reflects a pair that will work as shown?
SELECT JOB, EDL, LASTNAME
FROM EMP
WHERE DEPT = 'A00'
ORDER BY EDL, JOB;
JOB EDL LASTNAME
=== === ========
PRES 18 HAAS
SLS 14 CONNOR
SLS 18 HEMMINGER
SELECT JOB, EDL, LASTNAME
FROM EMP
WHERE DEPT = 'A00'
ORDER BY JOB, EDL;
JOB EDL LASTNAME
=== === ========
PRES 18 HAAS
SLS 18 HEMMINGER
SLS 14 CONNER
SELECT JOB, EDL, LASTNAME
FROM EMP
WHERE DEPT = 'A00'
ORDER BY JOB, EDL;
JOB EDL LASTNAME
=== === ========
PRES 18 HAAS
SLS 14 CONNER
SLS 18 HEMMINGER
SELECT JOB, EDL, LASTNAME
FROM EMP
WHERE DEPT = 'A00'
ORDER BY LASTNAME, EDL;
JOB EDL LASTNAME
=== === ========
SLS 14 CONNOR
SLS 18 HEMMINGER
PRES 18 HAAS
|
| 14: | Which statement about joins is false?
The main ingredient of a join is typically matching column values in rows of each table that participates in the join. A join is specified in the WHERE clause of a query. A joined table specifies an intermediate result table that is the result of either an inner join or an outer join. The result of a join associates rows from one table with rows from another table.
|
| 15: | Which statement is true?
An inner join combines the rows that are produced by the outer join, plus the missing rows, depending on the type of inner join. There are two types of inner join: a left inner join and a right inner join. A full outer join includes the rows from both tables that were missing from the inner join. An outer join combines each row of the left table with each row of the right table, keeping only the rows in which the join condition is true.
|
| 16: | Which type of join could a programmer use to combine each row of the left table with each row of the right table, keeping only the rows in which the join condition is true?
|
| 17: | Which statement is false?
A subquery is helpful when you need to narrow your search condition that is based on information in an interim table. The COALESCE function is particularly helpful in full outer join operations because it returns the first nonnull value. The join condition of a right outer join can be any simple or compound search condition that contains a subquery reference. None of the above; all statements are true.
|
| 18: | Which statement is false?
SQL statements can be used to insert, update, or delete data. DB2 ensures that an insert, update, or delete operation does not violate any referential constraints or check constraints that are defined on the table. The INSERT statement or LOAD utility can be used to add new data to an existing table. The SQL INSERT statement can be used to add rows to a table, but not to a view.
|
| 19: | Which
SQL
statement is valid?
INSERT INTO NEWDEPT (DEPTNO, DEPTNAME, MGRNO, ADMRDEPT)
VALUES (E31, PUBLISHING, 000020, D11);
INSERT IN NEWDEPT (DEPTNO, DEPTNAME, MGRNO, ADMRDEPT)
VALUES ('E31', 'PUBLISHING', '000020', 'D11'),
INSERT INTO NEWDEPT (DEPTNO, DEPTNAME, MGRNO, ADMRDEPT)
VALUES ('E31', 'PUBLISHING', '000020', 'D11'),
INSERT VALUES ('E31', 'PUBLISHING', '000020', 'D11')
INTO NEWDEPT (DEPTNO, DEPTNAME, MGRNO, ADMRDEPT);
|
| 20: | Which task
cannot
be done with an
SQL
INSERT statement?
Include a SELECT statement in the INSERT statement to tell DB2 that another table contains the data for the new row or rows. Use host variable arrays in the VALUES clause of the INSERT FOR n ROWS statement to insert multiple rows into a table. Specify the values to insert in a single row. None of the above; SQL INSERT statements can be used for the tasks in options A, B, and C.
|
