Quiz
TE0142
Create a free account to finish this quiz
Save your progress and see your final score.
Already have an account? Sign in
Questions in this quiz (127)
1A company wants a sequential ranking of all employee salaries. They do not want employees with the same salary to be given the same rank number. Which ordered analytical function should be used?
- ARANK
- BQUANTILE
- CROW_NUMBER
- DPERCENT_RANK
2Which two will happen during a transaction when no rows are available in a queue table? (Choose two.)
- AThe transaction aborts automatically.
- BThe SELECT AND CONSUME statement will generate new rows in the table.
- CThe transaction enters a delay state until a row is inserted into the queue table.
- DThe transaction aborts either by an ABORT statement, or a DROP TABLE statement on the queue table.
3Which DCL statement is written correctly?
- AGRANT SELECT, EXECUTE, DELETE TO User1 ON User1;
- BGRANT SELECT, EXECUTE, DELETE ON Database1 TO Database1;
- CGRANT SELECT, EXECUTE, DELETE TO User1 ON DATABASE Database1;
- DGRANT SELECT, EXECUTE, DELETE ON DATABASE Database1 TO User1;
4Given the following: (? = NULL) Column1 Column2
JA LZ
JA ?
MX TX
MX LZ
? ?
SELECT CASE WHEN column_1 = 'JA' AND column_2 = 'LZ' THEN 'OK'
WHEN column_1 = 'JA' AND column_2 IS NULL THEN '??'
WHEN column_1 IS NOT NULL AND column_2 = 'LZ' THEN '??'
WHEN column_1 = 'DD' THEN '??'
ELSE 'OK' END FROM table_1;
How many rows will the SELECT statement return with the value 'OK'?- A1
- B2
- C3
- D4
- E5
5The 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
227638 Martin
220794 Miko
228326 Nelly
224753 ?
216893 Jackie
Trans table
acctid amount
220532 55.38
227638 21.73
220794 34.14
228326 42.88
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?- ASELECT amount
FROM Customer a LEFT JOIN Trans b ON a.acctid = b.acctid
ORDER BY 1 DESC; - BSELECT amount
FROM Customer a LEFT JOIN Trans b WHERE a.acctid = b.acctid
ORDER BY 1 DESC; - CSELECT amount
FROM Customer a RIGHT OUTER JOIN Trans b WHERE a.acctid = b.acctid
ORDER BY 1 DESC; - DSELECT amount FROM Customer a Trans b
ON a.acctid = b.acctid ORDER BY 1 DESC;
- ASELECT amount
6Given 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?- ATbl_1 will return all rows not in Tbl_2.
- BTbl_1 will return all of the non-NULL rows.
- CNo Tbl_1 rows are returned for the entire query.
- DThe Tbl_1 rows that contain a NULL in Col_1 are returned.
7Given 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?- ARows of the dropped partition are deleted.
- BRows of the NO RANGE partition are deleted.
- CRows are moved from the dropped partition to the save table.
- DRows are moved from the dropped partition to the NO RANGE partition.
8An 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.)- ASELECTstoreid
, prodid
, sales
, SUM(sales) OVER (PARTITION BY storeid
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS SumSales
FROM salestbl
ORDER BY 1,2; - BSELECTstoreid
, prodid
, sales
, SUM(sales) OVER (PARTITION BY storeid) AS SumSales FROM salestbl
ORDER BY 1,2; - CSELECTstoreid
, prodid
, sales
, SUM(sales) OVER (PARTITION BY storeid) AS SumSales FROM salestbl
GROUP BY 1,2,3
ORDER BY 1,2; - DSELECTstoreid
, prodid
, sales
, SUM(sales) OVER (PARTITION BY storeid) AS SumSales FROM salestbl
GROUP BY 1,2,3
ORDER BY 1,2;
- ASELECTstoreid
9Which three types of user-defined functions does Teradata support? (Choose three.)
- ATable
- BLocal
- CScalar
- DGlobal
- EAggregate
10A SELECT query uses the RANK() OVER (ORDER BY batting_avg DESC) syntax to produce a ranking of baseball players. The top ten baseball players are being ranked based on their batting averages. In the resulting output of the query there will be ten rows. There is a two-way tie for the second position and a three-way tie for the last three positions. What will be the ranking number of the player in the final row?
- A7
- B8
- C9
- D10
11A requirement exists to perform upsert processing against a partitioned table. You choose to execute a MERGE INTO instead of a UPDATE ELSE INSERT statement.
Which three restrictions on the UPDATE ELSE INSERT statement make the MERGE INTO statement a better choice? (Choose three.)- AThe target table may be defined with multilevel partitioning.
- BUPDATE portion may not modify any of the partitioning columns.
- CAll partitioning columns need to be in the UPDATE's WHERE clause.
- DINSERT portion must specify the same partition as the UPDATE portion.
12What is a restriction on a macro?
- AA DDL statement in a macro must be the first statement in the macro.
- BA DDL statement in a macro must be the only statement in the macro.
- CA DDL statement in a Multi-statement macro must be the last statement in the macro.
- DA DDL statement in a macro must be followed by an END TRANSACTION or COMMIT statement.
13Which statement is written correctly?
- ADELETEUserlist FROM ALL;
- BDELETE WHEREUserDate < DATE '2000-01-01';
- CDELETEUserList WHERE UserDate < DATE '2000-01-01' ALL;
- DDELETE FROMUserlist WHERE UserDate < DATE '2000-01-01';
14Given the following query:
SELECT Item, CAST(Cost AS INTEGER)
FROM StoreInv ORDER BY Item;
The statement returns the following rows: Item Cost
1001 10
1002 11
1004 9
1010 10
Given the following additional query: SELECT Item, Cost
FROM WarehouseInv ORDER BY Item;
The statement returns the following rows: Item Cost
1001 9.7
1002 11.0
1005 12.0
1006 13.5
Given the union of the following two queries: SELECT Item, CAST(Cost AS INTEGER)
FROM StoreInv UNION
SELECT Item, Cost FROM WarehouseInv;
How many rows will the query return?- A1
- B5
- C7
- D8
- EAn error is returned
15A company wants to extract statistical salary information from a sample employee population. It would like to create 10 salary partitions, with $0 as the lower bound and $1,000,000 as the upper bound. Which ordered analytical function should be used?
- ARANK
- BQUANTILE
- CSAMPLE_ID
- DWIDTH_BUCKET
16Given the following:
Job Description Name SalaryAmount
Programmer Smithson, John 55,000
Salesman Germain, Frank 75,000
Carpenter Johnson, Mark 40,000
After switching over to ANSI mode, an error was generated with the following statement: SELECT CAST(Name AS CHAR(5)) What caused the error?- AIn Release 14 of Teradata, ANSI mode has been eliminated.
- BIn ANSI mode, the CAST function can only be completed in the WHERE clause.
- CIn ANSI mode, the CAST function does not permit truncation of nonpad characters.
- DIn ANSI mode, the TRIM function should have been used to truncate character strings.
17A company wants to award a 3% increase in salary to all employees who meet these two criteria:
They have a valid department assignment.
Their current salary is less than $50,000.
Which two statements will accomplish this objective? (Choose two.)- AUPDATE emp FROM department AS dept, employee AS emp SET salary_amount = salary_amount * 1.03
WHERE emp.department_number = dept.department_number AND salary_amount < 50000; - BUPDATE employee ASemp FROM department AS dept SET salary_amount = salary_amount * 1.03
WHERE emp.department_number = dept.department_number AND salary_amount < 50000; - CUPDATE employee
SET salary_amount = salary_amount * 1.03 WHERE employee.department_number IN (SELECT department_number FROM department AND salary_amount < 50000); - DUPDATE employee
SET salary_amount = salary_amount * 1.03 WHERE employee.department_number IN (SELECT department_number FROM department) AND salary_amount < 50000;
- AUPDATE emp FROM department AS dept, employee AS emp SET salary_amount = salary_amount * 1.03
18Given the following CREATE TABLE statement:
CREATE SET TABLE SPA_APPT_AVAILABILITY ( SPA_ID INTEGER NOT NULL
,APPT_DATE DATE NOT NULL
,APPT_TIME CHAR(5) NOT NULL
,TREATMENT_ID INTEGER NOT NULL
,TREATMENT_NAME CHAR(50) NOT NULL
,AESTHETICIAN_ID INTEGER NOT NULL
,COMMENTS VARCHAR(100)
,PRIMARY KEY (SPA_ID, APPT_DATE))
;
Which column(s) will be defined as the primary index?- ASPA_ID
- BTREATMENT_ID
- CSPA_ID, APPT_DATE
- DSPA_ID, TREATMENT_ID
19The date '2009-04-10' is represented by StartDate and the date '2008-03-07' is represented by EndDate. Which calculation produces the number of days between the two dates?
- AEndDate - StartDate
- BDIFF(EndDate, StartDate)
- CDAYS(StartDate, EndDate)
- DEXTRACT(DAY FROM EndDate) - EXTRACT(DAY FROM StartDate)
20A mobile provider wants to increase the price on a specific feature included in several of their service offers. The feature name contains the string "walkabout".
Which query modifies the correct rows from the Phone_Packages table?- AUPDATEPhone_Packages SET price = price * 1.25
WHERE Features.feature_name LIKE '%walkabout%'; - BUPDATEPhone_Packages SET price = price * 1.25
WHERE feature_code IN (SELECT feature_code FROM Features
WHERE feature_name LIKE '%walkabout%'); - CUPDATEPhone_Packages FROM Features SET price = price * 1.25
WHERE Features.feature_name LIKE '%walkabout%'; - DUPDATEPhone_Packages FROM Features SET price = price * 1.25
WHERE feature_code IN (SELECT feature_code FROM Features
WHERE feature_name LIKE '%walkabout%');
- AUPDATEPhone_Packages SET price = price * 1.25
21Which expression will return what hour it will be in 3 hours 30 minutes?
- AEXTRACT(HOUR FROM CURRENT_TIME + '03:30')
- BEXTRACT(HOUR FROM CURRENT_TIME + '3:30' HOUR TO SECOND INTERVAL)
- CEXTRACT(HOUR FROM CURRENT_TIME + INTERVAL '3:30' HOUR TO MINUTE)
- DEXTRACT(HOUR FROM CURRENT_TIME + INTERVAL ('3' HOUR + '30' MINUTE))
- EEXTRACT(HOUR FROM CURRENT_TIME + ('3' HOUR + '30' MINUTE) INTERVAL)
22A table contains the following column:
Inv_TS TIMESTAMP(6) WITH TIME ZONE
Which expression returns the normalized hour (for the current session time zone) of column Inv_TS?- AEXTRACT(HOUR FROMInv_TS)
- BHOUR(Inv_TS AS TIMESTAMP(6))
- CHOUR(CAST(Inv_TS AS TIMESTAMP(6)))
- DEXTRACT(HOUR FROM CAST(Inv_TS AS TIMESTAMP(6)))
23Which statement is written correctly?
- ASELECT *, table2.logdate, --EL ASTERISCO NO PERMITE QUE FUNCIONE, NO DEBE IR AHI. table2.logtime FROM DBC.ResUsageSPMA db1
WHERE db1.thedate = table2.logdate AND db1.thetime = table2.logtime; - BSELECT table2.logdate, -- NO HAY NINGUNA FUNCION NI NADA ANTES DEL ASTERISCO table2.logtime, (*) FROM DBC.ResUsageSPMA db1
WHERE db1.thedate = table2.logdate AND db1.thetime = table2.logtime; - CSELECT table2.logdate, table2.logtime, --MISMA HISTORIA CON EL ASTERISCO
* FROM DBC.ResUsageSPMA db1
WHERE db1.thedate = table2.logdate AND db1.thetime = table2.logtime; - DSELECT table2.logdate, table2.logtime, --DE NINGUN LADO SE TRAE LA TABLA TABLE2 db1.* FROM DBC.ResUsageSPMA db1
WHERE db1.thedate = table2.logdate AND db1.thetime = table2.logtime;
- ASELECT *, table2.logdate, --EL ASTERISCO NO PERMITE QUE FUNCIONE, NO DEBE IR AHI. table2.logtime FROM DBC.ResUsageSPMA db1
24Given the following PPI table:
CREATE TABLE SalesHistory_Store1(
Product_CodeCHAR(8)
,Store_Number INTEGER
,Transaction_Date DATE
,Quantity_Sold INTEGER
,Notes CHAR(50))
PRIMARY INDEX (Product_Code, Store_Number, Transaction_Date) PARTITION BY RANGE_N (Transaction_Date BETWEEN
DATE '2009-01-01' AND DATE '2011-12-31' EACH INTERVAL '1' DAY);
Which table, when joined with the given table, may result in a rowkey-based merge join?- ACREATE TABLE SalesHistory_Store2( Product_CodeCHAR(8)
,Store_Number INTEGER
,Transaction_Date DATE
,Quantity_Sold INTEGER)
PRIMARY INDEX (Product_Code, Store_Number, Transaction_Date) PARTITION BY RANGE_N (Transaction_Date BETWEEN
DATE '2009-01-01' AND DATE '2011-12-31' EACH INTERVAL '1' DAY); - BCREATE TABLE SalesHistory_Store2( Product_CodeCHAR(8)
,Store_Number INTEGER
,Transaction_Date DATE
,Quantity_Sold INTEGER
,Notes CHAR(50))
PRIMARY INDEX (Product_Code, Store_Number) PARTITION BY RANGE_N (Transaction_Date BETWEEN
DATE '2009-01-01' AND DATE '2011-12-31' EACH INTERVAL '1' DAY); - CCREATE TABLE SalesHistory_Store2( Product_CodeCHAR(8)
,Store_Number INTEGER
,Transaction_Date DATE
,Quantity_Sold INTEGER
,Notes CHAR(50))
PRIMARY INDEX (Product_Code, Store_Number, Transaction_Date) PARTITION BY RANGE_N (Transaction_Date BETWEEN
DATE '2009-01-01' AND DATE '2011-12-31' EACH INTERVAL '1' MONTH); - DCREATE TABLE SalesHistory_Store2( Product_CodeCHAR(8)
,Store_Number INTEGER
,Transaction_Date DATE
,Quantity_Sold INTEGER
,Notes CHAR(50))
PRIMARY INDEX (Product_Code, Store_Number, Transaction_Date)
- ACREATE TABLE SalesHistory_Store2( Product_CodeCHAR(8)
25Given the following reporting requirements:
salary totals of each department
a grand total of all departments
Which grouping function is designed to handle these requirements?- ACUBE
- BROLLUP
- CPARTITION BY
- DGROUP BY GROUPING SETS
26Which two are reasons to use a view? (Choose two.)
- AIt enables renaming and formatting of columns.
- BIt simplifies updates to tables that make up the view.
- CIt enables building of large and complex applications.
- DIt simplifies selects from tables that make up the view.
27Given the following table definition: CREATE TABLE employee ( employee_id INTEGER NOT NULL
,employee_name VARCHAR(50)
,employee_job VARCHAR(50)) PRIMARY INDEX (employee_id);
Which statement is a correct use of a comparison operator?- ASELECT * FROM employee WHERE employee_job IS NULL;
- BSELECT * FROM employee WHERE employee_job BETWEEN POSITIONS;
- CSELECT * FROM employee WHEREemployee_job IS 'governance' OR 'quality';
- DSELECT * FROM employee WHEREemployee_job IN ANY (maintenance, management, engineer);
28Which two are compatible with DISTINCT in a SELECT statement? (Choose two.)
- AWITH clause
- BTOP N operator
- Crecursive queries
- DCLOB cast as VARCHAR
- EBLOB cast as VARBYTE
29Which DCL statement is written correctly?
- AGRANT ALL ON Database1 TO User1 WITH GRANT;
- BGRANT ALL ON Database1 TO User1 WITH ADMIN;
- CGRANT ALL ON Database1 TO User1 WITH ADMIN OPTION;
- DGRANT ALL ON Database1 TO User1 WITH GRANT OPTION;
30Which attribute function will return information about how a named expression will display?
- ATYPE
- BFORMAT
- CDISPLAY
- DDEFAULT
31An SQL developer is asked to develop a set of performance monitoring reports. The development team has decided to code the reports using derived tables to aggregate the system performance reports.
Which three are benefits of using a derived table in the report coding process? (Choose three.)- AData distribution will be controlled by defining the primary index.
- BThe derived tables will be defined in the Data Dictionary for reuse.
- CSQL coding will take fewer steps than using temporary table solutions.
- DThe temporary data will be available after the SQL request has completed.
- EColumn data types will be automatically defined except for casting requirements.
- FThe derived tables will not require the use of temporary space for materialization.
32Which statement is true regarding session transaction mode in Teradata?
- AANSI mode can be set after logon.
- BThe same query might return different results in each mode.
- CAll ANSI features work inTeradata mode but not all Teradata features work in ANSI mode.
- DAll Teradata features work in ANSI mode but not all ANSI features work in Teradata mode.
33Assume columns c1 and c2 have DATE data type. What is the resulting data type when subtracting c1 from c2?
- ADATE
- BINTEGER
- CSMALLINT
- DINTERVAL DAY
34Which two are primary benefits to using views? (Choose two.)
- AIt simplifies end-user access.
- BIt requires less space to store the data.
- CIt simplifies updates to the tables that make up the view.
- DIt provides an easily maintained level of security and authorization.
35Given the following table:
CREATE GLOBAL TEMPORARY TABLE database1.temp1 (
column1 INTEGER NOT NULL,
column2 INTEGER DEFAULT100 COMPRESS ,
column3 CHAR(80)) PRIMARY INDEX (column1)
ON COMMIT PRESERVE ROWS;
Which statement is true about this table?- AInserted rows will use spool space.
- BMultiple sessions can use this definition to insert rows but not share rows.
- CRows will be deleted after a transaction that inserted rows into this table is complete.
- DRows will be preserved after a session that inserted rows into this table is logged off.
36Which two parameters should be included as OUT parameters to assist with debugging in a stored procedure? (Choose two.)
- Areturn code
- Bactivity count
- Cboundary conditions
- Dinformative error message
- Echeck SQL argument result
37The columns in a Trans table are defined as: acctid INTEGER
amount DECIMAL(8,2)
The values for these columns are the following (? indicates null):
Trans table
acctid amount
220532 55.38
227638 21.73
? 34.14
228326 ?
223961 20.74
220794 15.47
227638 ?
228326 47.21
Given the following SQL statement: SELECT COUNT(*)
,COUNT(amount) FROM Trans; What is the result?- A8 6
- B8 8
- C5 6
- D7 6
38The stores table contains the following data:
store_id city state
----------- ------------------------- -----
Big Sur CA
Monterey CA
Napa CA
Oceanside CA
Rancho Bernardo CA
6 Sonoma CA
7 Algiers Point LA
8 New Orleans LA
9 Charlotte NC
10 North Wilkesboro NC
11 Midway UT
12 Moab UT
13 Park City UT
Springdale UT
Charleston WV
White Sulfur Springs WV
Given the following SQL statement: SELECT city
,state
,SAMPLEID
FROM stores
SAMPLE WITH REPLACEMENT WHEN state = 'LA' THEN 4, 1 WHEN state = 'CA' THEN 8, 4 WHEN state = 'CO' THEN 2, 2 END
ORDER BY 3;
How many rows are returned?- A7
- B14
- C16
- D17
- E21
39The stores table contains the following data: store_id city state
----------- ------------------------- -----
Big Sur CA
Monterey CA
Napa CA
Oceanside CA
Rancho Bernardo CA
6 Sonoma CA
7 Algiers Point LA
8 New Orleans LA
9 Charlotte NC
10 North Wilkesboro NC
11 Midway UT
12 Moab UT
13 Park City UT
Springdale UT
Charleston WV
White Sulfur Springs WV
Given the following SQL statement:
SELECT city
,state
,SAMPLEID
FROM stores
SAMPLE WITH REPLACEMENT RANDOMIZED ALLOCATION WHEN state = 'LA' THEN 5
WHEN state = 'CA' THEN 5 ELSE 7
END
ORDER BY 3;
How many rows are returned?- A2
- B6
- C8
- D14
- E17
40What will occur when a row level lock is requested in a macro and the Optimizer determines a table level lock is required?
- AIt will automatically upgrade the lock without warning.
- BIt will automatically upgrade the lock and provide a warning.
- CIt will provide a warning and put the query in the delay queue.
- DIt will send back an error message for the lock to be corrected.
41Given the following table:
Customer_Name Season_Purchased
Johnson Fall
Hamilton Summer
Toby Winter
Sinclair Spring
Hall Winter
Trader Fall
Earman Summer
A company wants to send a "Spring" catalog to those customers who have purchased Spring items in the past and a "Fall" catalog to those who have made Fall purchases. Which CASE expression produces the desired information?- ACASE season_purchased
WHEN "Fall" THEN Customer_Name WHEN "Spring" THEN Customer_Name ELSE "Unknown"
END - BCASE WHEN "Fall" THENCustomer_Name WHEN "Spring" THEN Customer_Name END
- CCASEseason_purchased
WHEN "Fall" THEN Customer_Name
ELSE WHEN "Spring" THEN Customer_Name END - DCASE WHEN "Fall" THEN Customer_Name ELSE WHEN "Spring" THEN Customer_Name END
- ACASE season_purchased
42Which data type can be converted to any other data type in the Day-Time interval family?
- AINTERVAL TIME
- BINTERVAL DAY
- CINTERVAL MONTH
- DINTERVAL DATE
43Given the following query: SELECT Name, State FROM Customers
ORDER BY Name;
The statement returns the following rows (? indicates null): Name State
Dillon B CA
Newman R ?
Osborne G AR
Given the following additional query: SELECT Name, State
FROM Employees ORDER BY Name;
The statement returns the following rows: Name State
Dillon B CA
Freeman A TN
Newman R TX
Oscar K FL
Given the union of the following two queries: SELECT Name, State
FROM Customers UNION
SELECT Name, State FROM Employees;
How many rows will the union return?- A1
- B5
- C6
- D7
44A table is created with the following DDL: CREATE TABLE Test (
Column1 CHAR(2) NOT NULL) UNIQUE PRIMARY INDEX(Column1);
The data inserted into Column1 looks like the following: Column1
50
35
79
Which query will have optimal performance, given the data inserted into Column1?- ASELECT Column1 FROM Test WHERE Column1=35;
- BSELECT Column1 FROM Test WHERE Column1='35';
- CSELECT Column1 FROM Test WHERE Column1(INTEGER)=35;
- DSELECT Column1 FROM Test WHERE CAST(Column1 AS INTEGER)=35;
45The stores table contains the following data: store_id city state
----------- ------------------------- -----
Big Sur CA
Monterey CA
Napa CA
Oceanside CA
Rancho Bernardo CA 6 Sonoma CA
7 Algiers Point LA 8 New Orleans LA 9 Charlotte NC
10 North Wilkesboro NC 11 Midway UT
12 Moab UT 13 Park City UT
Springdale UT
Charleston WV
White Sulfur Springs WV
Given the following SQL statement: SELECT city
,state
,SAMPLEID
FROM stores
SAMPLE WITH REPLACEMENT WHEN state = 'UT' THEN 4, 2 WHEN state = 'WV' THEN 2, 1 ELSE 10, 5 END ORDER BY 3;
How many rows are returned?- A6
- B9
- C19
- D24
- E32
46Which three requirements result in choosing stored procedures for implementing an application? (Choose three)
- Arecursive processing
- BUSING request modifiers
- Cwarning condition handlers
- Dre-scrolling of answer sets
47Which three statements are true about the functionality of queue tables? (Choose three.)
- AA queue table may or may not contain data.
- BThe first column must be a timestamp data type.
- CA normal SELECT may not be used to read rows in the table.
- DThe definition of a queue table is not stored in the data dictionary.
- EA SELECT AND CONSUME reads a row and deletes it from the table and queue.
48Company Z wants to identify the highest salaried employee in each department for a possible mentoring program. Which query will generate the desired results?
- ASELECT last_name
,department_number AS deptno
,salary_amount FROM employee ee
WHERE salary_amount = (SELECT MAX (salary_amount) FROM employee em); - BSELECT last_name
,department_number AS deptno
,salary_amount FROM employee ee
WHERE salary_amount = (SELECT MAX (salary_amount) FROM employee em
WHERE ee.department_number = em.department_number); - CSELECT last_name
,department_number AS deptno
,salary_amount FROM employee ee
WHERE salary_amount = (SELECT MAX (salary_amount) FROM employee em
WHERE ee.employee = salary_amount); - DSELECT last_name
,department_number AS deptno
,salary_amount FROM employee ee
WHERE salary_amount = (SELECT MAX (salary_amount) FROM employee em
WHERE ee.salary_amount = em.salary_amount);
- ASELECT last_name
49Why would adding a debug variable be useful when writing a stored procedure?
- AIt reduces the number of SET statements.
- BIt provides information on the compiler.
- CIt decreases the frequency of recompilation.
- DIt simplifies usage of various levels of tracing.
50A user wants to create two copies of the following table: CREATE TABLE table_1(
column1 INTEGER NOT NULL
,column2 INTEGER NOT NULL
,column3 DATE FORMAT 'YYYY-MM-DD'
,column4 INTEGER DEFAULT1000
,FOREIGN KEY (column2) REFERENCES WITH NO CHECK OPTION Parent_table1(column1)) PRIMARY INDEX (column1)
PARTITION BY RANGE_N(column3
BETWEEN DATE '2009-01-01' AND DATE '2011-12-31' EACH INTERVAL '1' DAY);
COLLECT STATISTICS ON table_1 INDEX (column1);
COLLECT STATISTICS ON table_1 COLUMN PARTITION;
CREATE TABLE table_2 AS table_1 WITH DATA AND STATISTICS;
CREATE TABLE table_3 AS (SELECT * FROM table_1) WITH DATA AND STATISTICS;
Which two statements are true about these two CREATE TABLE AS statements? (Choose two.)- Atable_3 will not be partitioned.
- Btable_3 will have the DEFAULT column attribute from table_1.
- Ctable_2 will have the referential integrity column from table_1.
- Dtable_2 will have statistics on the system-derived PARTITION column from table_1.
51The stores table contains the following data: store_id city state
----------- ------------------------- -----
Big Sur CA
Monterey CA
Napa CA
Oceanside CA
Rancho Bernardo CA
6 Sonoma CA
7 Algiers Point LA 8 New Orleans LA 9 Charlotte NC
10 North Wilkesboro NC 11 Midway UT
12 Moab UT 13 Park City UT
Springdale UT
Charleston WV
White Sulfur Springs WV
Given the following SQL statement: SELECT store_id
,city
,state
,SAMPLEID
FROM stores
SAMPLE 0.50, 0.25, 0.25
ORDER BY 4;
How many rows are returned?- A1
- B4
- C5
- D15
- E16
52Given the following expressions:
SUM(CASE WHEN max AWT EQ 0 THEN 1 ELSE 0 END) AS AWT_0
SUM(CASE WHEN max AWT BETWEEN 1 AND 10 THEN 1 ELSE 0 END) AS AWT1_10
SUM(CASE WHEN max AWT BETWEEN 11 AND 20 THEN 1 ELSE 0 END) AS AWT11_20
SUM(CASE WHEN max AWT BETWEEN 21 AND 30 THEN 1 ELSE 0 END) AS AWT21_30
SUM(CASE WHEN max AWT BETWEEN 31 AND 40 THEN 1 ELSE 0 END) AS AWT31_40
SUM(CASE WHEN max AWT GE 41 THEN 1 ELSE 0 END) AS AWT_41Plus
What is the common purpose of these expressions?- ASUM the max AWT value that are IN the BETWEEN range.
- BSUM the max AWT value that are NOT IN the BETWEEN range.
- CCount the total occurrences of the BETWEEN range being TRUE.
- DCount the total occurrences of the BETWEEN range being FALSE.
53Which is an example of a properly coded correlated subquery?
- ASELECT *
FROM employee emp WHERE EXISTS (SELECT *
FROM Department dept WHERE deptnum = deptnum); - BSELECT *
FROM Employee emp WHERE deptnum IN (SELECT deptnum
FROM department dept); - CSELECT *
FROM Employee emp WHERE EXISTS SELECT *
FROM Department dept
WHERE emp.deptnum = dept.deptnum; - DSELECT *
FROM Employee emp WHERE EXISTS (SELECT *
FROM Department dept
WHERE emp.deptnum = dept.deptnum);
- ASELECT *
54Which statement is true regarding the use of the DEFAULT ?
- AIt cannot be used as a predicate.
- BIt can be used in an insert statement.
- CIt can return a null value if the argument is 0.
- DIt can be used in the partitioning expression for defining partitioned primary indexes.
55Given the following table:
Employee Salary_Amount
Smith 75,000
Jones 60,000
Williams 55,000
White 35,000
Johnson 105,000
Company X and Company Y have merged to become Company Z. The CEO of Company Z wants to know how many employees fall in the same salary range after the merger because of the concern that there are now too many employees in the "Over $100K" range. Which CASE expression will determine the salary range for an employee?- ACASE
WHEN salary_amount < 50000 THEN "Under $50K"
WHEN salary_amount < 75000 THEN "Between $50K and $75K" WHEN salary_amount < 100000 THEN "Between $75K and $100K" ELSE "Over $100K"
END - BCASEsalary_amount
WHEN salary_amount < 50000 THEN "Under $50K"
WHEN salary_amount < 75000 THEN "Between $50K and $75K" WHEN salary_amount < 100000 THEN "Between $75K and $100K" ELSE "Over $100K"
END - CCASE salary_amount
WHEN < 50000 THEN "Under $50K"
WHEN < 75000 THEN "Between $50K and $75K" WHEN < 100000 THEN "Between $75K and $100K" ELSE "Over $100K"
END - DCASE
WHERE salary_amount < 50000 THEN "Under $50K"
WHERE salary_amount < 75000 THEN "Between $50K and $75K" WHERE salary_amount < 100000 THEN "Between $75K and $100K" OR "Over $100K"
END
- ACASE
56Which statement will correctly remove all granted privileges except the SELECT privilege on Database1 from Role1?
- AREVOKE ALL BUT SELECT FROM Role1 ON Database1;
- BREVOKE ALL BUT SELECT ON Database1 FROM Role1;
- CREVOKE EACH BUT SELECT FROM Role1 ON Database1;
- DREVOKE EACH BUT SELECT ON Database1 FROM Role1;
57Which DCL statement is written correctly to remove the UPDATE privilege?
- AREVOKE UPDATE ON Database1 FROM Role1;
- BREVOKE UPDATE FROM Role1 ON Database1;
- CREVOKE UPDATE ON Database1 FROM Role1 WITH ADMIN OPTION;
- DREVOKE UPDATE FROM Role1 ON Database1 WITH GRANT OPTION;
58A manager walks by a Teradata workstation and sees someone has left for the day but did not log out of Teradata.
Which built-in function could be used to determine which login name is assigned to this session?- AUSER
- BSESSION
- CACCOUNT
- DDATABASE
59Given the following data (? indicates null):
Name FAX Line Cell Phone Work Phone
Hammer (301)457-9023 (301)640-9123 ?
Taylor ? (513)153-9346 (513)560-5481
Heidema (679)840-9277 ? (679)678-2351
Jones ? (451)546-9225 (451)678-0364
When run against the given data, which COALESCE function will produce an error message?- ACOALESCE(FaxLine)
- BCOALESCE(NULL,NULL)
- CCOALESCE(NULL,Cellphone,WorkPhone)
- DCOALESCE(FaxLine,CellPhone,WorkPhone)
60Which ANSI SQL-2003 compliant function should be used when determining the length of a string?
- ACOUNT
- BCHAR_COUNT
- CCHARACTERS
- DCHARACTER_LENGTH
61Given the following definition of a table:
CREATE TABLE trans
(
trans_id INTEGER NOT NULL
,unit_price DECIMAL(10,2)
,qnty INTEGER
,t_time TIMESTAMP(6) NOT NULL)
PRIMARY INDEX (trans_id);
The table has a row that was inserted as follows:
INSERT trans VALUES (45912, 12.99, NULL, CURRENT_TIMESTAMP);
The following statement is submitted: SELECT COALESCE(unit_price,0)*qnty
FROM trans WHERE trans_id=45912 AND unit_price IS NOT NULL;
What is the result?- ANULL
- B0.00
- C12.99
- Dno rows returned
62Given the following command: HELP STATISTICS sales.orders
What is a valid output and what information is provided?- ADate Time Unique values Column names
------- ------- -------------- ------------
10/15/09 22:39:08 156798 Order Id
Displays summary attributes for statistics that have been collected in the Data Dictionary for the specified data table. - BDate Time Secondary Unique Values
-------- -------- ---------- --------------
10/15/09 22:39:08 P 16780
Displays the attributes for an index column including whether it is a primary or secondary index and the number of unique values within the column. - CDate Time Table/View/JI/HI Kind Unique Values
-------- -------- ---------------- ---- ------------- 10/15/09 02:19:22 Product T 2500
10/15/09 02:20:56 Active Orders V 29000
10/15/09 02:23:47 Product_Id JI 17000
10/15/09 02:19:22 Order_Id HI 11409
Displays attributes for statistics collected for all tables, views, Join Indexes and Hash Indexes. - DDate Time Column Name Unique Values
-------- -------- ------------ -------------
10/15/09 17:41:54 Order_Number 150986
Displays the attributes for the columns making up the Hash Index.
- ADate Time Unique values Column names
63Given the following: SELECT Department
,Employee_No
,SUM(Sales_Amt) FROM SALES
WHERE Department IN ('Westcoast','Central','Eastcoast') What is needed to make this query run without error?- AGROUP BY 1,2
- BORDER BY 1,2
- CGROUP BY 1 ORDER BY 1,2
- Dmodify the WHERE clause to includeEmployee_No
64Assume that all formatting symbols are set to default and that the column salary_amount is defined as DECIMAL (10,2).
Given the following statement:
SELECT salary_amount * (-1) (FORMAT '-Z(I)BN') FROM ........
What is a valid result of this statement?- A-100000USD
- B-100000US Dollars
- C-100000 USD
- D-100000 US Dollars
65The transtime column in the trans table is defined as the following:
transtimeTIMESTAMP(6) WITH TIME ZONE NOT NULL
In the trans table, the column values are the following: transtime _
2009-03-16 11:48:32.120000+08:00
2008-09-23 18:14:46.780000+02:00
2009-06-09 09:23:01.340000-06:00
2009-01-30 02:37:11.670000+11:00
2009-04-19 21:25:55.220000-09:00
The values in the transtime column need to be presented in a report so that the column values look like this:
transdate:
2008-09-24
2009-01-29
2009-03-16
2009-04-20
2009-06-09
Which SQL statement would provide the desired result when the current session time zone is INTERVAL '08:00' HOUR TO MINUTE?- ASELECT transtime AS DATE FROM trans ORDER BY 1;
- BSELECT transtime AS transdate FROM trans ORDER BY 1;
- CSELECT CAST(transtime AS transdate) FROM trans ORDER BY 1;
- DSELECT CAST(transtime AS DATE) transdate FROM trans ORDER BY 1;
66Given the following:
Job Description Name S alary Amount
Programmer Smithson, John 55,000
Salesman Germain, Frank 75,000
Carpenter Johnson, Mark 40,000
The manager of the Marketing Department noticed that the Name results on the monthly reports are being truncated as Smith, Germa, Johns. The manager asked the IT Department to investigate this issue.
Which function caused this problem?- ATRIM(Name)
- BPOSITION(Name, 5)
- CCHAR_LENGTH(Name, 5)
- DCAST(Name AS CHAR(5))
- ETRIM(TRAILING FROM Name)
67Given the following statement:
SELECT *
FROM sales s LEFT JOIN prod p
ON s.prdid = p.prdid
FULL JOIN customer c ON s.custid = c.custid;
Which two statements provide the same result as the statement above? (Choose two.)- ASELECT *
FROM (sales s LEFT JOIN prod p ON s.prdid = p.prdid) FULL OUTER JOIN customer c ON s.custid = c.custid; - BSELECT *
FROM sales s LEFT JOIN prod p ON s.prdid = p.prdid JOIN customer c ON s.custid = c.custid; - CSELECT s.*
,p.*
,c.*
FROM customer c FULL JOIN prod p
RIGHT OUTER JOIN sales s ON p.prdid = s.prdid ON c.custid = s.custid; - DSELECT s.*
,p.*
,c.*
FROM prod p LEFT JOIN sales s ON p.prdid = s.prdid FULL JOIN customer c ON s.custid = c.custid;
- ASELECT *
68What is an appropriate use of a macro?
- Adrop databases
- Binsert new rows
- Cstart load utilities
- Dcreate multiple users
69Which data type is supported for Multi-Value Compression (MVC)?
- ATIME
- BFLOAT
- CVARCHAR
- DINTERVAL
70Which function is needed when converting a character string to another server character set?
- ACAST
- BCONVERT
- CINTERPRET
- DTRANSLATE
71Which statement will reduce the price of books by 10% for all books published by any division of the Blackwell group?
- AUPDATE Books
SET Book_list_price = Book_list_price * .90
WHERE Books.Publisher_Id = Publisher.Publisher_Id AND Publisher_name = Blackwell; - BUPDATE Books
SET Book_list_price = Book_list_price * .90
WHERE Books.Publisher_Id = Publisher.Publisher_Id AND WHERE Publisher_name LIKE 'Blackwell%'; - CUPDATE Books
SET Book_list_price = Book_list_price * .90 WHERE Publisher_Id IN
(SELECT Publisher_Id FROM Publisher
WHERE Publisher_name = Blackwell); - DUPDATE Books
SET Book_list_price = Book_list_price * .90 WHERE Publisher_Id IN
(SELECT Publisher_Id FROM Publisher
WHERE Publisher_name LIKE 'Blackwell%');
- AUPDATE Books
72Given the following table: CREATE SET TABLE emp_table ( emp_no INTEGER
,last_name CHAR(20)
,first_name CHAR(20)
,hire_date DATE FORMAT 'yyyy-mm-dd') UNIQUE PRIMARY INDEX (emp_no);
Given the following statement: INSERT INTO emp_table
VALUES (1011, 'Krishnaswamyn-Laavanya', ' Rajagopala', '2009-04-27');
Which two will occur? (Choose two.)- AIn ANSI mode, the system will return an error.
- BIn Teradata mode, the system will return an error.
- CIn ANSI mode, a string is truncated and the row is inserted.
- DIn Teradata mode, a string is truncated and the row is inserted.
73A manager asks a data analyst to produce a one time aggregated monthly summary report from multiple tables for a meeting in an hour. Which option would require fewer steps to complete the task?
- Acreate multiple Derived Tables for the query report
- Bcreate multiple Volatile Tables for the query report
- Ccreate multiple Global Temporary Tables for the query report
- Dcreate multiple permanent tables to load data for the query report
74A company has a policy to grant all privileges at role level. Given the following:
Account_UserRole
GRANT SELECT ON Database Account_Records
GRANT EXECUTE ON Database Account_Records
Account_AdminRole
GRANT UPDATE ON Database Account_Records
GRANT DELETE ON Database Account_Records
Orders_UserRole
GRANT SELECT ON Database Account_Orders
GRANT INSERT ON Database Account_Orders
User A is a member of Account_User and Orders_User roles. What is the impact of changing user A's default role from Account_User to ALL?- AUser A can UPDATE on theAccount_Records database.
- BUser A can EXECUTE and DELETE on theAccount_Records database.
- CUser A can INSERT on theAccount_Orders database and EXECUTE on the Account_Records database.
- DUser A can EXECUTE on theAccount_Orders database and EXECUTE on the Account_Records database.
75Given the following two very large PPI tables with current statistics:
CREATE TABLE ppi_table1
( column1 INTEGER NOT NULL
,column2 DATE)
PRIMARY INDEX (column1) PARTITION BY RANGE_N(column2
BETWEEN DATE '2009-01-01' AND DATE '2011-12-31' EACH INTERVAL '1' YEAR);
CREATE TABLE ppi_table2
( column1 INTEGER NOT NULL
,column2 INTEGER) PRIMARY INDEX (column1)
PARTITION BY RANGE_N(column2 BETWEEN 1 AND 6 EACH 1);
SELECT * FROM ppi_table1 t1, ppi_table2 t2
WHERE t1.column1=t2 .column1;
Which statement is true about the step that can be taken by joining these tables?- Asingle-window merge join
- Browkey-based merge join
- Cproduct join with dynamic partition elimination
- Dproduct join without dynamic partition elimination
76Which set operator is represented by the shaded area shown in the Venn diagram?
- AMINUS
- BUNION
- CEXCEPT
- DINTERSECT
77A user is submitting additional requests via a BTEQ session as part of a multirequest transaction that is still open.
Which two sessions are in ANSI mode based on the additional requests being submitted? (Choose two.)- A1) An UPDATE statement is submitted that succeeds.
2) An ET statement is submitted that succeeds.
3) The user then sees, using a SELECT statement, that the UPDATE statement in step1 is reflected in the table. - B1) An UPDATE statement is submitted that succeeds.
2) An UPDATE statement is submitted that violates a CHECK constraint.
3) The user then sees, using a SELECT statement, that the UPDATE statement in step1 is reflected in the table. - C1) An UPDATE statement is submitted that violates a CHECK constraint.
2) An UPDATE statement is submitted that succeeds.
3) A ROLLBACK statement is submitted.
4) The user then sees, using a SELECT statement, that the UPDATE statement in step2 is reflected in the table. - D1) An UPDATE statement is submitted that succeeds.
2) An UPDATE statement is submitted that violates a CHECK constraint.
3) An UPDATE statement is submitted that succeeds
4) A ROLLBACK statement is submitted.
5) The user then sees, using a SELECT statement, that the UPDATE statements in step1 and 3 are not reflected in the table.
- A1) An UPDATE statement is submitted that succeeds.
78Which expression will return the month it will be 300 days from today's date?
- AMONTH(CURRENT_DATE + 300)
- B(CURRENT_DATE + 300) MOD 12 + 1
- CEXTRACT(MONTH FROM (CURRENT_DATE + 300))
- DCAST((CURRENT_DATE + 300) AS INTERVAL MONTH)
79What is the evaluation order when using a HAVING, WHERE, and GROUP BY clause together in the same SELECT statement?
- AWHERE, GROUP BY, HAVING
- BGROUP BY, HAVING, WHERE
- CWHERE, HAVING, GROUP BY
- DGROUP BY, WHERE, HAVING
80What information can be reviewed to indicate CPU skewed processing for a query?
- Athe response time from DBC.DBQLogTbl
- Bthe number of active AMPs in DBC.AccLogTbl
- Cthe results of the execution of the SHOWCPUSKEW command before the query
- Dthe ratio of maximum AMP CPU to the average AMP CPU fromDBC.DBQLogTbl
81What information could be examined in DBC.DBQLogTbl to identify report queries submitted by a business intelligence tool to identify potential performance issues?
- Anumber of active PEs
- Bnumber of active AMPs
- Cnumber of rows returned
- Dnumber of query bands used
82What are two reasons to use the SAMPLE clause in an SQL statement? (Choose two.)
- Ato obtain a portion of data from an ordered set
- Bto obtain a specific number of data rows in the result set
- Cto obtain a percentage of data rows from the SELECT query
- Dto obtain identical sample sets from multiple executions of a single SQL statement
83Which three statements are true regarding stored procedure input and output parameters? (Choose three.)
- ACALL arguments cannot be specified if a called procedure has no parameters.
- BNULL can be assigned to corresponding IN, INOUT and OUT parameters in the called procedure.
- Cthe number of arguments in a CALL request must equal the number of parameters in the called procedure.
- DIN, INOUT and OUT arguments in a CALL request must correspond to an IN, INOUT or OUT parameter in the called procedure.
84The columns in a Customer table are defined as:
acctid INTEGER NOT NULL
acctnm VARCHAR(50) NOT NULL
and the columns in a Trans table are defined as:
acctid INTEGER NOT NULL
amount DECIMAL(8,2) NOT NULL
The values for these columns are the following:
Customer table
acctid acctnm _
227638 Martin
220794 Yuko
228324 Sally
Trans table
acctid amount
227638 21.73
220794 55.38
220794 34.14
228324 42.88
223962 20.74
220794 15.47
227638 25.64
228324 39.99
228324 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 55.38
34.14
15.47
Which query would provide the desired result?- ASELECT amount FROM Customer a
,Trans b
ON a.acctid = b.acctid WHERE a.acctnm = 'Yuko' ORDER BY 1 DESC; - BSELECT amount FROM Customer a
,Trans b
WHERE a.acctid = b.acctid AND a.acctnm = 'Yuko' ORDER BY 1 DESC; - CSELECT amount
FROM Customer a INNER JOIN Trans b WHERE a.acctid = b.acctid
AND a.acctnm = 'Yuko' ORDER BY 1 DESC;
- ASELECT amount FROM Customer a
85Given the following business question:
Display the employee number, department, and salary for full time employees who either earn less than $50,000 or work in department 610.
Which WHERE clause will answer the business question?- Adepartment_number = 610 OR (salary_amount < 50000.00 AND job_status = 'full time')
- B(department_number = 610 AND salary_amount < 50000.00) AND job_status = 'full time'
- Cdepartment_number = 610 OR salary_amount < 50000.00 OR job_status = 'full time'
- D(department_number = 610 OR salary_amount < 50000.00) AND job_status = 'full time'
86Given the following information:
Employee Salary
Mike Taylor 75675.50
Bill Hammer 100350.25
John Wysong 65115.50
George Donze 85750.25
Bill Hammer noticed that on his Year-To-End statement earnings, his salary was always listed in whole dollars (without rounding) even though his pay stub always showed his salary with cents. Which CAST caused his Year-To-End earnings to be displayed in this manner?- ACAST(Salary_Amount AS INTEGER)
- BCAST(Salary_Amount AS CHAR(8))
- CCAST(Salary_Amount AS DEC(6,2))
- DCAST(Salary_Amount AS DEC(6,0))
87A development team has decided that recursive queries are required to create a set of inventory reports. Which two facts must be considered when using derived tables for this project? (Choose two.)
- AThe derived tables can act like a viewed table.
- BData distribution will be controlled by defining the primary index.
- CThe derived tables will be defined in the Data Dictionary for reuse.
- DDerived tables are valid when coded as part of a seed statement of a recursive query.
- EThe temporary data will be available for other reports after the SQL request completes.
88A company needs to determine which employees have invalid job codes assigned to them. In order to write the query to determine this, a correlated subquery will be executed using the Employee and Job tables. What does the subquery require in order to make it a correlated subquery?
- AIt cannot use aliases.
- BIt will have a join condition.
- CIt has a nested set of parenthesis.
- DIt must contain a GROUP BY clause.
89Which describes the functionality of QUANTILE?
- AA function which divides a set of rows into a set of partitions such as percentiles and tertiles.
- BA function which allows a user to specify a sample of results returned by a specified SELECT statement.
- CA function which assigns partition numbers to values based on a specified range and the number of desired partitions.
- DA function which permits a column to be ranked, either based on a high or low order, against other rows in the answer set.
90As part of a query, the average age of employees in the company needs to be compared to the age of each employee in the company. Which construct can be used to allow the writing of the query as a single SELECT statement?
- AVirtual Table
- BDerived Table
- Cattribute function
- Drecursive subquery
91Typical display of birthdates is in the following format:
Name DOB
Connie Lynn Jul 3, 54
Marcus Norman Oct 14, 81
Lynn Marie Jul 8, 83
Susan JoAnna Mar 5, 87
New management wants to see all dates in 4-digit year format. The dates should now appear on all reports as:
Name DOB
Connie Lynn 1954-07-03
Marcus Norman 1981-10-14
Lynn Marie 1983-07-08
Susan JoAnna 1987-03-05
Which expression formats column DOB in this 4-digit year format?- ADOB(FORMAT 'yyyy-mm-dd')
- BCAST(DOB AS Year-Month-Day)
- CDOB AS FORMAT(Year-Month-Day)
- DDOB(CAST AS FORMAT 'yyyy-mm-dd')
92Which two are benefits of using a macro? (Choose two.)
- AIt enables procedural logic.
- BIt allows parameterization of object names.
- CIt can simplify operations that are complex.
- DIt is a short cut for executing frequently run SQL statements.
93Which statement is true about the use of NO RANGE for a partition expression?
- AUNKNOWN and NO RANGE are mutually exclusive
- BIts use may allow unwanted data to be inserted to the table.
- CWhen dropping a partition, no rows in that partition will be moved to the NO RANGE partition.
- DWhen adding a partition, no rows from the NO RANGE partition are moved to the added partition.
94What causes the Teradata Database to treat each request as an implicit transaction?
- Athe use of the BT ET statements
- Bthe use of the COMMIT statement
- Cthe absence of the BT ET statements
- Dthe absence of the COMMIT statement
95Which statement is written correctly?
- ADELETE FROM employeeemp WHERE emp IN
(SELECT dept_no FROM department WHERE salary_pool < 50000); - BDELETE FROM employee WHERE dept_no IN
(SELECT dept_no FROM department WHERE salary_pool < 50000); - CDELETE employee FROM WHERE dept_no IN
(SELECT dept_no FROM department WHERE salary_pool < 50000); - DDELETE FROM employeeemp WHERE dept_no.emp IN
(SELECT dept_no FROM department WHERE salary_pool < 50000);
- ADELETE FROM employeeemp WHERE emp IN
96Given the following information:
Employee Salary
Mike Taylor 75675.50
Bill Hammer 100350.25
John Wysong 65115.50
George Donze 85750.25
Mike Taylor has notified his manager that his salary keeps getting rounded-up to the whole-dollar on his paycheck stub. Which function is causing this behavior?- ACAST(Salary_Amount AS INTEGER)
- BCAST(Salary_Amount AS DEC(10,0))
- CCAST(Salary_Amount AS DEC(10,2))
- DTRIM(TRAILING FROMSalary_Amount)
97Given the following PPI table: CREATE TABLE SalesHistory_Store1( Product_CodeCHAR(8)
,Store_Number INTEGER
,Transaction_Date DATE
,Quantity_Sold INTEGER
,Notes CHAR(50))
PRIMARY INDEX (Product_Code, Store_Number, Transaction_Date) PARTITION BY RANGE_N (Transaction_Date BETWEEN
DATE '2009-01-01' AND DATE '2011-12-31' EACH INTERVAL '1' DAY);
Which three conditions are necessary to join to this table using a rowkey-based merge join? (Choose three.)- Asame primary index
- Bidentical partitioning expressions
- Ccondition specified with partitioning column
- Dall primary index and partitioning columns are specified as equality join terms
98Column c1 has SMALLINT data type and column c2 has CHARACTER data type. What is the resulting data type when multiplying c1 and c2?
- AFLOAT
- BINTEGER
- CSMALLINT
- DCHARACTER
99A retailer needs to identify manufacturers that are no longer providing shoes to their shops so the manufacturer can be removed from their active Shoe_Manufacturer table.
Which DELETE statement removes the correct rows from the Shoe_Manufacturer table?- ADELETE FROM Shoes WHERE 0 = (SELECT COUNT(*) FROM Shoes
,Shoe_Manufacturer
WHERE Shoes.Shoe_style = Shoe_Manufacturer.Shoe_style); - BDELETE FROM Shoes WHERE Shoes.Shoe_style IN (SELECT Shoes.Shoe_style FROM Shoes
,Shoe_Manufacturer
WHERE Shoes.Shoe_style = Shoe_Manufacturer.Shoe_style); - CDELETE FROM Shoe_Manufacturer WHERE 0 = (SELECT COUNT(*) FROM Shoes
,Shoe_Manufacturer
WHERE Shoes.Shoe_style = Shoe_Manufacturer.Shoe_style); - DDELETE FROMShoe_Manufacturer
WHERE Shoe_Manufacturer.Shoe_style NOT IN (SELECT Shoes.Shoe_style
FROM Shoes
,Shoe_Manufacturer
WHERE Shoes.Shoe_style = Shoe_Manufacturer.Shoe_style);
- ADELETE FROM Shoes WHERE 0 = (SELECT COUNT(*) FROM Shoes
100Which three requirements result in choosing stored procedures for implementing an application? (Choose three)
- Adynamic SQL
- Bexecution of macros
- CCASE statement logic
- DLarge object (LOB) processing
101A table contains the following column: Inv_TSTIMESTAMP(6) WITH TIME ZONE
which contains the following values: 2009-04-18 22:47:30.000000+03:00
2009-04-18 10:30:22.000000+12:00
Which dates will be returned if CAST(Inv_TS AS DATE) is used on the table, given that the Local Time Zone for the session is defined as -'00:00'?- A04/18/2009
04/17/2009 - B04/18/2009
04/18/2009 - C04/18/2009
04/19/2009 - D04/19/2009
04/19/2009
- A04/18/2009
102The account balance column in the Customer table is defined as the following:
acctbal DECIMAL(8,2) NOT NULL
In the Customer table, the column values are the following: acctbal
227638.57
220794.47
228324.07
227498.12
229561.95
The following SQL statement is submitted:
SELECT CAST(acctbal AS INTEGER) balance FROM Customer WHERE CAST(acctbal AS DECIMAL(6,0)) > acctbal;
What is the result?- Abalance 220794 228324 227498
- Bbalance 227639 220795 228325 227499 229562
- Cbalance 227638 229561
- Dbalance 227639 229562
103When creating a new macro, the name of the macro must be different from which objects?
- Aall other macros within the system
- Bthe name of the database containing the macro
- Call other objects within the same database as the new macro
- Donly other macros within the same database as the new macro
104What information would be reviewed to indicate I/O skewed processing for a query?
- Athe amount of temporary space consumed by the query
- Bthe ratio of maximum I/O to average I/O from DBC.DBQLogTbl
- Cthe results of the execution of the EXPLAIN command before the query
- Dthe results of the execution of the SHOWIOSKEW command before the query
105Which two pieces of information can be reviewed in the trace output to assist in debugging a stored procedure?(Choose two.)
- Aisolate the failure point
- Bidentify compilation parameters
- Cremoval of database error handling
- Dset severity warnings in ERRORLEVEL
- Edetermine if the procedure is logically correct
106A company thinks there are some employees who earn more than their managers. They have asked for a report showing the employee number, department number, and salary of all department managers who have the highest salary in their department. Which query will produce the desired report?
- ASELECT d.man_employee_num, d.department_num, e.salary_amount
FROM department d INNER JOIN employee e ON e.employee_num=d.man_employee_num WHERE e.salary_amount =
(SELECT MAX (salary_amount) FROM employee em
WHERE d.department_num = em.department_num); - BSELECTd.man_employee_num, d.department_num, e.salary_amount
FROM department d INNER JOIN employee e ON e.employee_num=d.man_employee_num WHERE e.salary_amount =
(SELECT MAX (salary_amount) FROM department d
WHERE d.department_num = em.department_num); - CSELECTd.man_employee_num, d.department_num, e.salary_amount
FROM department d INNER JOIN employee e ON e.employee_num=d.man_employee_num WHERE e.salary_amount =
(SELECT MAX (department_num) FROM department d
WHERE d.department_num = em.department_num); - DSELECTd.man_employee_num, d.department_num, e.salary_amount
FROM department d INNER JOIN employee e ON e.employee_num=d.man_employee_num WHERE e.salary_amount =
(SELECT MAX (salary_amount) FROM employee em);
- ASELECT d.man_employee_num, d.department_num, e.salary_amount
107Which function can be used to aid in searching for a carriage return character in a string?
- ACAST
- BINTERPRET
- CTRANSLATE
- DCHAR2HEXINT
108The following table identifies those customers who have made purchases in the current year: (? indicates null)
Customer Name Date Purchased
Short Jan 5, 2009
Wetzel Oct 13, 2009
Capps ?
Smith July 3, 2009
Johns Oct 14, 2009
Miller ?
Donze Mar 5, 2009
Which approach would produce a list of customers with purchase dates, as well as those who do not have purchase dates?- ANULLIF CASE
- BValued CASE
- CNo NULL CASE
- DSearched CASE
109What are two uses of the GROUP BY clause? (Choose two.)
- Aranking
- Bqualifying
- Caggregating data groups
- Dsetting the sort order of data groups
- Ealternative method to SELECT DISTINCT
110Which expression adds 10 hours 20 minutes 15 seconds to CURRENT_TIME?
- ACURRENT_TIME + '10:20:15'
- BCURRENT_TIME + INTERVAL '10:20:15' HOUR TO SECOND
- CCURRENT_TIME + ('10:20:15' HOUR TO SECOND) INTERVAL
- DCURRENT_TIME + ('10' HOUR + '20' MINUTE + '15' SECOND) INTERVAL
- ECURRENT_TIME + INTERVAL ('10' HOUR + '20' MINUTE + '15' SECOND)
111The 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
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?- ASELECT amount FROM Customer a
,Trans b
ON a.acctid = b.acctid ORDER BY 1 DESC; - BSELECT amount
FROM Customer a JOIN Trans b ON a.acctid = b.acctid
ORDER BY 1 DESC; - CSELECT amount
FROM Customer a INNER JOIN Trans b ON a.acctid = b.acctid
ORDER BY 1 DESC;
- ASELECT amount FROM Customer a
112Which two statements describe the functionality of a UDF? (Choose two.)
- AIt is an automatically driven database event.
- BIt is globally accessible to those with the proper access rights.
- CIt is a combination of SQL and control and condition handling statements that provide an interface toTeradata.
- DIt allows SQL to be extended by writing functions and installing them on the database, and then using them like standard SQL functions.
113Which two statements describe the functionality of a UDF? (Choose two.)
- AIt is an automatically driven database event.
- BIt is globally accessible to those with the proper access rights.
- CIt is a combination of SQL and control and condition handling statements that provide an interface toTeradata.
- DIt allows SQL to be extended by writing functions and installing them on the database, and then using them like standard SQL functions.
114Given the following reporting requirements:
a breakdown of salaries by department, by manager, by manager/department
a grand total of all salaries
Which grouping function is designed to handle these requirements?- ACUBE
- BROLLUP
- CPARTITION BY
- DGROUP BY GROUPING SETS
115Which statement will update the customer's order priority to Rush (R) and change the delivery city to Atlanta?
- AUPDATE Orders
SET Order_priority = 'R'
,Order_destination = 'Atlanta' WHERE Order_id = 5001; - BUPDATE Orders
SET Order_priority = R
,Order_destination = Atlanta WHERE Order_id = 5001; - CUPDATE Orders
SET Order_priority = 'R' Order_destination= 'Atlanta' WHERE Order_id = 5001; - DUPDATE Orders WHERE Order_id = 5001 SET Order_priority = 'R'
,Order_destination = 'Atlanta';
- AUPDATE Orders
116Given the following table:
CREATE TABLE Division_Sales_2008 (Division_id INTEGER,
Sales_Rep CHAR(10) NOT NULL PRIMARY KEY,
Sales_Amt DEC(10,2));
Each row in the table represents the 2008 total sales for an agent in a specific division. Given the following query:
SELECT Division_id
,Sales_Rep,
RANK ( ) OVER (PARTITION BY Division_id ORDER BY SALES DESC) AS "Ranking"
FROM Division_Sales_2008 QUALIFY Ranking <= 3;
What describes the output from this query?- Athe top three divisions and their total sales
- Bthe bottom three divisions and their total sales
- Cthe bottom three sales reps and their total sales
- Dthe top three sales reps in the each of the divisions
117Table 'logtable' is defined as the following:
CREATE TABLE logtable (col1 INT);
Which three stored procedures will compile successfully? (Choose three.)- ACREATE PROCEDURE sp_ins
BEGIN
DECLARE vcount INTEGER DEFAULT0;
INSERT logtable (:vcount); END; - BCREATE PROCEDURE sp_ins BEGIN
DECLARE vcount INTEGER DEFAULT0;
INSERT logtable (vcount); END; - CCREATE PROCEDUREsp_ins BEGIN
DECLARE col1 INTEGER DEFAULT0;
INSERT logtable (:col1); END; - DCREATE PROCEDUREsp_ins BEGIN
DECLARE col1 INTEGER DEFAULT0;
INSERT logtable (col1); END;
- ACREATE PROCEDURE sp_ins
118A user needs to store a summary result but does not want to store it in a permanent table. The user also wants to collect statistics. Which type of tables should be used?
- ASpool Table
- BDerived Table
- CVolatile Table
- DGlobal Temporary Table
119Given the following statement: SHOW MACRO Customer;
Which is a valid output of this statement?- AREPLACE MACRO Customer ( First name CHAR(20)
,Last name CHAR(25)
,Cellphone_number INTEGER
,Business_phone_number INTEGER)
AS (INSERT INTO Customer (FName, LName, Cell, Biz); VALUES (:fname, :lname, :cell, :biz)
); - BUPDATE MACRO Customer ( First name CHAR(20)
,Last name CHAR(25)
,Cellphone_number INTEGER
,Business_phone_number INTEGER)
AS (INSERT INTO Customer (FName, LName, Cell, Biz); VALUES (:fname, :lname, :cell, :biz)
); - CDEFINE MACRO Customer ( First name CHAR(20)
,Last name CHAR(25)
,Cellphone_number INTEGER
,Business_phone_number INTEGER)
AS (INSERT INTO Customer (FName, LName, Cell, Biz); VALUES (:fname, :lname, :cell, :biz)
); - DDROP MACRO Customer ( First name CHAR(20)
,Last name CHAR(25)
,Cellphone_number INTEGER
,Business_phone_number INTEGER)
AS (INSERT INTO Customer (FName, LName, Cell, Biz); VALUES (:fname, :lname, :cell, :biz)
);
- AREPLACE MACRO Customer ( First name CHAR(20)
120A user is writing a complex query that needs to aggregate values from multiple tables and join those results.
Which construct can be used in the query to allow the writing of the query as a single SELECT statement?- AVirtual Table
- BDerived Table
- CVolatile Table
- DTemporary Table
121
Given the following two PPI tables:
CREATE TABLE orders (
o_orderkey INTEGER NOT NULL,
o_orderdate DATE FORMAT 'yyyy-mm-dd' NOT NULL, o_shipdate DATE FORMAT 'yyyy-mm-dd' NOT NULL, o_shippriorityINTEGER,
o_commentVARCHAR(80)) PRIMARY INDEX (o_orderkey) PARTITION BY (
RANGE_N (o_orderdate BETWEEN DATE '2009-01-01' AND DATE '2011-12-31' EACH INTERVAL '1' MONTH),
RANGE_N (o_shipdate BETWEEN DATE '2009-01-01' AND DATE '2011-12-31' EACH INTERVAL '1' MONTH));
CREATE TABLE lineitem (
l_orderkey INTEGER NOT NULL, l_partkey INTEGER NOT NULL, l_suppkey INTEGER, l_linenumberINTEGER, l_quantity INTEGER NOT NULL) PRIMARY INDEX (l_orderkey) PARTITION BY
RANGE_N (l_suppkey BETWEEN 0 and 4999 EACH 10)); Given the query:
SELECT *
FROM orders, lineitem
WHERE o_orderkey=l_orderkey AND ....
Which three additional conditions will perform partition elimination when added after AND? (Choose three.)- Al_suppkey < 3000
- Bl_suppkey in (3000, 4000)
- Co_orderdate< o_shipdate - 30
- Do_orderdate < CURRENT_DATE -30
122A manager for Company A has asked the IT Department to generate a report that shows the relative ranking of all products that are sold in the company stores. The following report was generated:
storeid prodid sales Pct_Rank_Sales
1001 F 150000.00 0.000000
1001 A 100000.00 0.100000
1003 B 65000.00 0.200000
1001 C 60000.00 0.300000
1003 D 50000.00 0.400000
1002 A 40000.00 0.500000
1001 D 35000.00 0.600000
1002 C 35000.00 0.600000
1003 A 30000.00 0.800000
1002 D 25000.00 0.900000
1003 C 20000.00 1.000000
Which query will generate the report as it appears above?- ASELECTstoreid, prodid, sales,
PERCENT_RANK() OVER (ORDER BY sales DESC) AS Pct_Rank_Sales FROM salestbl; - BSELECTstoreid, prodid, sales,
PERCENT_RANK () OVER (ORDER BY storeid DESC) AS Pct_Rank_Sales FROM salestbl; - CSELECTstoreid, prodid, sales,
RANK(WITH RELRANK) OVER (ORDER BY sales DESC) AS Pct_Rank_Sales
FROM salestbl; - DSELECTstoreid, prodid, sales,
RELRANK() OVER (ORDER BY sales DESC) AS Pct_Rank_Sales FROM salestbl;
- ASELECTstoreid, prodid, sales,
123Given the following columns and their data types: Inv_DateDATE
Inv_TimeTIME(0)
IntFld INTEGER
Which expression will execute without an error?- ASELECT IntFld - Inv_Time
- BSELECT IntFld - Inv_date
- CSELECT Inv_Date - IntFld
- DSELECT Inv_Time - IntFld
124Consider the following data in the Billing table:
Description BillingRate Hourly_Salary Education
Analyst 100.00 40.00
System Analyst 125.00 .00
Database Administrator 150.00 60.00
This is the desired answer set:
Description Billing to Cost Ratio Education
Analyst 2.5
System Analyst ?
Database Administrator 2.5
Which query will produce this answer set?- ASELECT Description,Billing_Rate / (NULLIFZERO(Hourly_Salary,0)) FROM Billing;
- BSELECT Description,Billing_Rate / (NULLIF(Hourly_Salary,0)) FROM Billing;
- CSELECT Description,Billing_Rate / (COALESCE(Hourly_Salary,0)) FROM Billing;
- DSELECT Description,Billing_Rate / (ZEROIFNULL(Hourly_Salary,0)) FROM Billing;
125A database administrator has initiated charge-back accounting for an entire session and wants to determine whether the current account string is correct. Which built-in function should be issued?
- AUSER
- BPROFILE
- CACCOUNT
- DSESSION
126The stores table contains the following data: store_id city state
----------- ------------------------- -----
Big Sur CA
Monterey CA
Napa CA
Oceanside CA
Rancho Bernardo CA
Sonoma CA
7 Algiers Point LA 8 New Orleans LA 9 Charlotte NC
10 North Wilkesboro NC 11 Midway UT
12 Moab UT 13 Park City UT
Springdale UT
Charleston WV
White Sulfur Springs WV
Given the following SQL statement: SELECT city
,state
,SAMPLEID
FROM stores
SAMPLE RANDOMIZED ALLOCATION WHEN state = 'UT' THEN 0.25, 0.25 WHEN state = 'CA' THEN 0.50, 0.50 END
ORDER BY 3;
How many rows are returned?- A4
- B5
- C8
- D10
- E16
127A company has a policy to grant all privileges at role level. Given the following:
Account_UserRole
GRANT SELECT ON Database Account_Records
GRANT EXECUTE ON Database Account_Records
Account_AdminRole
GRANT UPDATE ON Database Account_Records
GRANT DELETE ON Database Account_Records
What is the impact of changing user A's default role from Account_User to Account_Admin?- AUser A's current privileges do not change.
- BUser A is no longer a member of theAccount_User role.
- CUser A's current privileges are UPDATE and DELETE onAccount_Records.
- DUser A's current privileges are SELECT, EXECUTE, UPDATE, and DELETE onAccount_Records.
Information
- Description
- Quiz on TE0142, created by Mario David Barbosa Escobar on 03/11/2017.
- Language
- English
- Views
- 2
- Rating
- No ratings yet Rate this quiz