×
Detail Form
We will send your result on your email id and phone no. please fill detail
1.
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
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
2.
In which of the following situations should correlation names be used?
3.
Which of the following statements eliminates all but one of each set of duplicate rows in the DEPT column in the STAFF table?
4.
Given the following tables:
CONTINENTS
ID NAME COUNTRIES
1 Antarctica 0
2 Africa 53
3 Asia 47
4 Australia 14
5 Europe 43
6 North America 23
7 South America 12
REGION
ID LOCATION
1 East
2 West
How many rows would be returned using the following statement?
SELECT location FROM continents, region
CONTINENTS
ID NAME COUNTRIES
1 Antarctica 0
2 Africa 53
3 Asia 47
4 Australia 14
5 Europe 43
6 North America 23
7 South America 12
REGION
ID LOCATION
1 East
2 West
How many rows would be returned using the following statement?
SELECT location FROM continents, region
5.
How should the following UDF be invoked in order to convert US currency values stored in the EXPENSES table into Canadian currency?
CREATE FUNCTION getratews11 (
country1 VARCHAR(100),
country2 VARCHAR(100) )
RETURNS DOUBLE
LANGUAGE SQL
CONTAINS SQL
EXTERNAL ACTION NOT DETERMINISTIC
BEGIN
...
END
CREATE FUNCTION getratews11 (
country1 VARCHAR(100),
country2 VARCHAR(100) )
RETURNS DOUBLE
LANGUAGE SQL
CONTAINS SQL
EXTERNAL ACTION NOT DETERMINISTIC
BEGIN
...
END
6.
Given that tables T1 and T2 contain the following rows:
Table T1:
C1 C2
-- ---
5 4
5 2
5 5
Table T2:
C1 C2
-- --
5 1
5 2
5 3
Which of the following queries will return only those rows that exist in T1 and not in T2 ?
Table T1:
C1 C2
-- ---
5 4
5 2
5 5
Table T2:
C1 C2
-- --
5 1
5 2
5 3
Which of the following queries will return only those rows that exist in T1 and not in T2 ?
7.
Given the following two tables:
TAB1
C1 C2
__ __________
1 Antarctica
2 Africa
3 Asia
4 Australia
TAB2
CX CY
__ _____________
5 Europe
6 North America
7 South America
Which of the following SQL statements will insert all rows found in table TAB2 into table TAB1?
TAB1
C1 C2
__ __________
1 Antarctica
2 Africa
3 Asia
4 Australia
TAB2
CX CY
__ _____________
5 Europe
6 North America
7 South America
Which of the following SQL statements will insert all rows found in table TAB2 into table TAB1?
8.
Given the following table and XML data stored in the CONTACTINFO column:
CREATE TABLE clients(
id INT PRIMARY KEY NOT NULL,
name VARCHAR(50),
status VARCHAR(10),
contactinfo XML)
25 EastCreek
Toronto
Ontario
M8X-3T6
4165551358
9051112222
9051112222
by fax
What is the result of the following XQuery expression?
for $y in db2-fn:xmlcolumn('CLIENTS.CONTACTINFO')/Client//fax return $y
CREATE TABLE clients(
id INT PRIMARY KEY NOT NULL,
name VARCHAR(50),
status VARCHAR(10),
contactinfo XML)
Toronto
Ontario
M8X-3T6
4165551358
What is the result of the following XQuery expression?
for $y in db2-fn:xmlcolumn('CLIENTS.CONTACTINFO')/Client//fax return $y
9.
57 Given table T1 has column I1 containing the following data:
I1
---
If the following sequence of SQL statements is applied within a single unit of work:
UPDATE t1 SET i1 = 3 WHERE i1 = 2; S
AVEPOINT s1 ON ROLLBACK RETAIN CURSORS;
UPDATE t1 SET i1 = 5 WHERE i1 = 3;
SAVEPOINT s2 ON ROLLBACK RETAIN CURSORS;
INSERT INTO t1 (i1) VALUES (6);
ROLLBACK TO SAVEPOINT s1;
UPDATE t1 SET i1 = 2 WHERE i1 = 4;
COMMIT;
What is the expected sequence of values returned from?
SELECT i1 FROM t1 ORDER BY i1
I1
---
If the following sequence of SQL statements is applied within a single unit of work:
UPDATE t1 SET i1 = 3 WHERE i1 = 2; S
AVEPOINT s1 ON ROLLBACK RETAIN CURSORS;
UPDATE t1 SET i1 = 5 WHERE i1 = 3;
SAVEPOINT s2 ON ROLLBACK RETAIN CURSORS;
INSERT INTO t1 (i1) VALUES (6);
ROLLBACK TO SAVEPOINT s1;
UPDATE t1 SET i1 = 2 WHERE i1 = 4;
COMMIT;
What is the expected sequence of values returned from?
SELECT i1 FROM t1 ORDER BY i1
10.
Given the following two tables:
NAMES
-----------------------------
NAME NUMBER
---------- -------
Wayne Gretzky 99
Jaromir Jagr 68
Bobby Orr 4
Bobby Hull 23
Mario Lemieux 66
POINTS
-----------------------------
NAME POINTS
---------- ------
Wayne Gretzky 244
Bobby Orr 129
Brett Hull 121
Mario Lemieux 189
Joe Sakic 94
How many rows would be returned using the following statement?
SELECT name FROM names, points
NAMES
-----------------------------
NAME NUMBER
---------- -------
Wayne Gretzky 99
Jaromir Jagr 68
Bobby Orr 4
Bobby Hull 23
Mario Lemieux 66
POINTS
-----------------------------
NAME POINTS
---------- ------
Wayne Gretzky 244
Bobby Orr 129
Brett Hull 121
Mario Lemieux 189
Joe Sakic 94
How many rows would be returned using the following statement?
SELECT name FROM names, points