QUESTION: 5

The columns in a Customer table are defined as:

acctid INTEGER NOT NULL
acctnm VARCHAR(50)
and the columns in a Trans table are defined as:
acctid INTEGER NOT NULL
amount DECIMAL(8, 2) NOT NULL

The values (where ? indicates NULL) for these columns are the following:
Customer table Trans table
acctid acctnm__ acctid amount
227638 Martin 220532 55.38
220794 Miko 227638 21.73
228326 Nelly 220794 34.14
224753 ? 228326 42.88
216893 Jackie 223961 20.74
220794 15.47
227638 25.64
228326 47.21

The values in the amount column need to be presented in a report so that the column values look like the following:

amount
47.21
42.88
34.14
25.64
21.73
15.47
?
?

Which query provides the desired result?

SELECT amount
FROM Customer a LEFT JOIN Trans b
ON a.acctid = b.acctid
ORDER BY 1 DESC;
SELECT amount
FROM Customer a LEFT JOIN Trans b
WHERE a.acctid = b.acctid
ORDER BY 1 DESC;
SELECT amount
FROM Customer a RIGHT OUTER JOIN Trans b
WHERE a.acctid = b.acctid
ORDER BY 1 DESC;
SELECT amount
FROM Customer a
Trans b
ON a.acctid = b.acctid
ORDER BY 1 DESC;

Answer(s): B

Reveal Solution Next Question

QUESTION: 6

Given the following query:

SELECT * FROM Tbl_1
WHERE Col_1 NOT IN
(SELECT Col_2 FROM Tbl_2);

If one of the Col2 values is NULL, what is the impact on the Tbl_1 rows returned?

Tbl_1 will return all rows not in Tbl_2.
Tbl_1 will return all of the non-NULL rows.
No Tbl_1 rows are returned for the entire query.
The Tbl_1 rows that contain a NULL in Col_1 are returned.

Answer(s): B

Reveal Solution Next Question

QUESTION: 7

Given the following:

CREATE TABLE table1_ppi (
column1 INTEGER NOT NULL
, column2 INTEGER
, column3 DATE FORMAT 'yyyy-mm-dd'
)
PRIMARY INDEX (column1)
PARTITION BY RANGE_N(column3
BETWEEN DATE '2002-01-01' AND DATE '2012-12-31' EACH INTERVAL '1' YEAR,
NO RANGE);
ALTER TABLE table1_ppi
MODIFY PRIMARY INDEX
DROP RANGE BETWEEN DATE '2002-01-01' AND DATE '2002-12-31'
EACH INTERVAL '1' YEAR;

What is an effect of the ALTER TABLE statement?

Rows of the dropped partition are deleted.
Rows of the NO RANGE partition are deleted.
Rows are moved from the dropped partition to the save table.
Rows are moved from the dropped partition to the NO RANGE partition.

Answer(s): C

Reveal Solution Next Question

QUESTION: 8

An aggregation query produces the following result:

storeid prodid sales SumSales
----------- --------- ------------- ------------------

1001 A 100000.00 345000.00
1001 C 60000.00 345000.00
1001 D 35000.00 345000.00
1001 F 150000.00 345000.00
1002 A 40000.00 100000.00
1002 C 35000.00 100000.00
1002 D 25000.00 100000.00
1003 A 30000.00 165000.00
1003 B 65000.00 165000.00
1003 C 20000.00 165000.00
1003 D 50000.00 165000.00

Which three queries will produce this result? (Choose three.)

SELECTstoreid
, prodid
, sales
, SUM(sales) OVER (PARTITION BY storeid
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS SumSales
FROM salestbl
ORDER BY 1, 2;
SELECTstoreid
, prodid
, sales
, SUM(sales) OVER (PARTITION BY storeid) AS SumSales
FROM salestbl
ORDER BY 1, 2;
SELECTstoreid
, prodid
, sales
, SUM(sales) OVER (PARTITION BY storeid) AS SumSales
FROM salestbl
GROUP BY 1, 2, 3
ORDER BY 1, 2;
SELECTstoreid
, prodid
, sales
, SUM(sales) OVER (PARTITION BY storeid) AS SumSales
FROM salestbl
GROUP BY 1, 2, 3
ORDER BY 1, 2;

Answer(s): A,B,D

Reveal Solution Next Question

Free TE0-122 Exam Braindumps (page: 3)

QUESTION: 5

QUESTION: 6

QUESTION: 7

QUESTION: 8

TE0-122 Discussions & Posts