IBM DB2 9 Family Fundamentals

Question - 45 Maximum mark : - 84

Home
All Exams
IBM DB2 9 Family Fundamentals Online Mock Tests
IBM DB2 9 Family Fundamentals

Given the following query:
SELECT quantity,
CASE WHEN itemcode = '099' THEN 'SILVER'
WHEN itemcode = '788' THEN 'GOLD'
WHEN itemcode = '899' THEN 'PLATINUM'
ELSE 'ERROR'
END
FROM supplier
What will be the result of the query if the following data is evaluated by the CASE expression?
SUPPLIER
------------------------------------------
QUANTITY ITEMCODE
3 099
4 099
1 788
1 899
5 009
3 788
1 899

3,SILVER4,GOLD1,SILVER1,GOLD5,PLATINUM3,SILVER1,GOLD ✘ 3,SILVER4,SILVER1,GOLD1,PLATINUM5,ERROR3,GOLD1,PLATINUM ✘ 3,SILVER4,SILVER1,PLATINUM1,ERROR5,SILVER3,GOLD1,PLATINUM ✘ 3,SILVER4,SILVER1,GOLD1,GOLD5,SILVER3,ERROR1,PLATINUM ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Given the following expression:
SELECT QUANTITY,
CASE WHEN ITEMCODE = '099' THEN "SILVER"
WHEN ITEMCODE = '788' THEN "GOLD"
WHEN ITEMCODE = '899' THEN "PLATINUM"
ELSE ERROR
END
FROM SUPPLIER
What will be the result of the query if the following data is evaluated by the CASE expression?
QUANTITY ITEMCODE
3 099
4 099
1 788
1 899
5 009
3 788
1 899

3, SILVER4, GOLD1, SILVER1, GOLD5, PLATINUM3, SILVER1, GOLD ✘ 3, SILVER4, SILVER1, GOLD1, PLATINUM5, ERROR3, GOLD1, PLATINUM ✘ C. C.3, SILVER4, SILVER1, PLATINUM1, ERROR5, SILVER3, GOLD1, PLATINUM ✘ D. D.3, SILVER4, SILVER1, GOLD1, GOLD5, SILVER3, ERROR1, PLATINUM ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Which of the following queries will correctly return the manager information sorted by the manager's last name, department and project name?

SELECT lastname, dept, projname, manager, startdateFROM (SELECT name, dept, proj AS projname, manager, startdateFROM employee, projectWHERE

empno = empno) AS empprojORDER BY name, dept, projname ✘ SELECT lastname, dept, projname, manager, startdateFROM (SELECT name AS lastname, dept, proj AS projname, manager, startdateFROM employee,projectWHERE empno = mgrno) AS empprojSORT BY lastname, firstname, dept, projname ✘ SELECT lastname, dept, projname, manager, startdateFROM (SELECT name AS lastname, dept, proj AS projname, manager, startdateFROM employee,projectWHERE empno = mgrno) AS empprojORDER BY lastname ASCSORT BY dept, projname DESC ✘ SELECT lastname, dept, projname, manager, startdateFROM (SELECT name AS lastname, dept, proj AS projname, manager, startdateFROM employee,projectWHERE empno = mgrno) AS empprojORDER BY lastname, dept, projname ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Assuming table TAB1 contains 100 rows, which of the following queries will return only half of the rows available?

SELECT * FROM tab1 FIND FIRST 50 ROWS

✘

SELECT * FROM tab1 FETCH FIRST 50 ROWS ONLY

✘

SELECT * FROM tab1 WHILE ROW_NUM < 50

✘

SELECT * FROM tab1 MAXROWS 50

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Given the following two tables:
NAMES
NAME NUMBER
---------- -------
Wayne Gretzky 99
Jaromir Jagr 68
Bobby Orr 4
Bobby Hull 23
Brett Hull 16
Mario Lemieux 66
Mark Messier 11
POINTS
NAME POINTS
---------- ------
Wayne Gretzky 244
Jaromir Jagr 168
Bobby Orr 129
Brett Hull 121
Mario Lemieux 189
Joe Sakic 94
Which of the following statements will display the player name, number, and points for all players that have scored points?

SELECT p.name, n.number, p.points FROM names n INNER JOIN points p ON n.name = p.name ✘ SELECT p.name, n.number, p.points FROM names n LEFT OUTER JOIN points p ON n.name = ✘ name ✘ SELECT p.name, n.number, p.points FROM names n RIGHT OUTER JOIN points p ON n.name = ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

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

✘

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Given the following tables:
YEAR_2006
EMPID NAME
----- ---------------
1 Jagger, Mick
2 Richards, Keith
3 Wood, Ronnie
4 Watts, Charlie
5 Jones, Darryl
6 Leavell, Chuck
YEAR_1962
EMPID NAME
----- ---------------
1 Jagger, Mick
2 Richards, Keith
3 Jones, Brian
4 Wyman, Bill
5 Watts, Charlie
6 Stewart, Ian
If the following SQL statement is executed, how many rows will be returned?
SELECT name FROM year_2007
UNION ALL
SELECT name FROM year_1962

6 ✘ 9 ✘ 10 ✘ 12 ✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Given the following table definition:
SALES
--------------------------------------
INVOICE_NO CHAR(20) NOT NULL
SALES_DATE DATE
SALES_PERSON VARCHAR(25)
REGION CHAR(20)
SALES_AMT DECIMAL(9,2)
Which of the following queries will return SALES information, sorted by SALES_PERSON, from A to Z, and SALES_DATE, from most recent to earliest?

SELECT invoice_no, sales_person, sales_date, sales_amt FROM sales SORT BY sales_person, sales_date DESC

✘

SELECT invoice_no, sales_person, sales_date, sales_amt FROM sales SORT BY sales_person DESC, sales_date

✘

SELECT invoice_no, sales_person, sales_date, sales_amt FROM sales ORDER BY sales_person, sales_date DESC

✘

SELECT invoice_no, sales_person, sales_date, sales_amt FROM sales ORDER BY sales_person DESC, sales_date

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Given the following statement:
SELECT hyear, AVG(salary)
FROM (SELECT YEAR(hiredate) AS hyear, salary
FROM employee WHERE salary > 30000)
GROUP BY hyear
Which of the following describes the result if this statement is executed?

The statement will return the year and average salary for all employees that have a salary greater than $30,000, sorted by year.

✘

The statement will return the year and average salary for all employees hired within a given year that have a salary greater than $30,000.

✘

The statement will return the year and average salary for all years that every employee hired had a salary greater than $30,000.

✘

The statement will return the year and average salary for all years that any employee had a salary greater than $30,000.

✔

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

10.

Which two of the following statements are true about the HAVING clause?

The HAVING clause is used in place of the WHERE clause. The HAVING clause uses the same syntax as the WHERE clause. The HAVING clause can only be used with the GROUP BY clause. The HAVING clause accepts wildcards. The HAVING clause uses the same syntax as the IN clause.

Answer & Solution Discuss in Board Save for Later

Answer & Solution

Answer: Option B

Solution:

It provides fallback for IE8.

Prev Next

Congratulations

you have successfully completed the test

Back to Question View Result

Detail Form

We will send your result on your email id and phone no. please fill detail

Name

Email ID Note: Report will be send to the above Email ID

Phone No

IBM DB2 9 Family Fundamentals

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Answer & Solution

Answer: Option B

Solution:

Congratulations

COMPANY

Products

OTHERS

Partner