Download
1 / 7
80 likes | 274 Views
Soal Ujian Sertifikasi materiSorting Data. (Lus). 1. Which SELECT statements will execute successfully? SELECT donor_id, SUM(amount_pledged), SUM(amount_paid) FROM donation GROUP BY donor_id order by 2; SELECT SUM(amount_pledged), AVG(pledge_dt) FROM donation WHERE donor_id = 3 order by 3;
E N D
1. Which SELECT statements will execute successfully? • SELECT donor_id, SUM(amount_pledged), SUM(amount_paid) FROM donation GROUP BY donor_id order by 2; • SELECT SUM(amount_pledged), AVG(pledge_dt) FROM donation WHERE donor_id = 3 order by 3; • SELECT SUM(amount_pledged, amount_paid) FROM donation WHERE pledge_dt BETWEEN ’01-JAN-02 AND ’31-DEC-02’ order by 2 asc; • SELECT SUM(amount_pledged) FROM donation WHERE amount_pledged >= AVG(amount_pledged) order by 1; • SELECT MINIMUM(amount_pledged), MAXIMUM(amount_pledged) FROM donation order by 1;
2. The ACCOUNT table contains these colums: ACCOUNT_ID NUMBER(12) NEW_BALANCE NUMBER(7,2) PREV_BALANCE NUMBER(7,2) FINANCE_CHARGE NUMBER(7,2) You must create a statement to be mailed to all account holders. The statement must include the account holder’s previous balance and finance charge in this format: Previous Balance: 5000 Finance Charge: 45 Which SELECT clause will produce these results? • SELECT Previous Balance: ||prev_balance|| Finance Charge: ||prev_balance * .009 • SELECT ‘Previous Balance:’ ||prev_balance|| ‘Finance Charge:’ ||prev_balance * .009 • SELECT ‘Previous Balance: ’ ||prev_balance|| ‘ Finance Charge: ’ ||prev_balance * .009 • SELECT “Previous Balance: “ ||prev_balance||” Finance Charge: “ ||prev_balance * .009
3. Evaluate this SQL statement: SELECT id “Event”, SUM(reg_amt) “Registration Amt” FROM event WHERE reg_amt > 1000.00 GROUP BY “Event” ORDER BY 2; Which clause will cause an error? • SELECT id “Event”, SUM(reg_amt) “Registration Amt” • FROM event • WHERE reg_amt > 1000.00 • GROUP BY “Event” • ORDER BY 2
4. You attempt to query the database with this SQL statement: SELECT order_id “Order Number”, product_id “Product”, quantity “Quantity” FROM line_item WHERE Order_Number = 5570 ORDER By “Order Number”; This statement fails when executed. Which change will correct the problem? • Specify a sort order of ASC or DESC in the ORDER BY clause • Enclose all of the column aliases in single quotes instead of double quotes • Remove the column alias from the WHERE clause and use the column name • Remove the column alias from the ORDER BY clause and use the column name
5. Evaluate this SQL statement: SELECT id “Event”, SUM(reg_amt) “Registration Amt” FROM event WHERE reg_amt > 1000.00 GROUP BY id ORDER BY 3; Which clause will cause an error? • SELECT id “Event”, SUM(reg_amt) “Registration Amt” • FROM event • WHERE reg_amt > 1000.00 • GROUP BY id • ORDER BY 3
6. Evaluate this SQL statement SELECT supplier_id, AVG(cost) FROM product WHERE AVG(list_price) > 60.00 GROUP BY supplier_id ORDER BY AVG(cost) DESC; Which clause will cause an error? • SELECT • WHERE • GROUP BY • ORDER BY
