1 / 7

Soal Ujian Sertifikasi materiSorting Data

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;

sachi
Télécharger la présentation

Soal Ujian Sertifikasi materiSorting Data

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Soal Ujian Sertifikasi materiSorting Data (Lus)

  2. 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;

  3. 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

  4. 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

  5. 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

  6. 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

  7. 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

More Related