Jawaban Pembahasan Soal Sertivikasi 1 [SQL 2]

1. Evaluate this SQL statement :

SELECT c.customer_id, o.order_id, o.order_date, p.product_name

FROM customer c, curr_order o, product p

WHERE customer.customer_id = curr_order. customer_id

AND o.product_id = p.product_id

ORDER BY o.order_amount;

This statement fails when executed. Which change will correct the problem?

  1. Use the table name in the ORDER BY clause.
  2. Remove the table aliases from the WHERE clause.
  3. Include the ORDER_AMOUNT column in the SELECT list.
  4. Use the table aliases instead of the table names in the WHERE clause.
  5. Remove the table alias from the ORDER BY clauses and use only the column name.

Jawaban : D

Ketika melakukan join beberapa table yang banyak, sehingga memudahkan dan tidak ambigu. Karena alias pada WHERE salah, seharusnya WHERE c.customer_id = o.customer_id

2. Seniority is based on the number of years a student has been enrolled at the university. You must

create a report that displays each student’s name, id number, and the number of years enrolled.

The years enrolled must be rounded to a whole number, based on the number of months from the date enrolled until today. which    statements produces the required result ?

a. SELECT first_name||’,’||last_name “Student Name, id ”id”, enroll_date,

b. TRUNC(SYSDATE,’YY’) – TRUNC(enroll_date,’YY’) “Seniority” FROM student;

c.  SELECT first_name||’,’||last_name “Student Name, id ”id”, enroll_date,                 ROUND(MONTHS_BETWEEN(SYSDATE,enroll_date)/12) “Seniority”  FROM student

d. SELECT first_name||’,’||last_name “Student Name”, id ”id”, enroll_date, ROUND(SYSDATE) – ROUND(enroll_date) “Seniority”

FROM student;

e. SELECT first_name||’,’||last_name “Student Name, id ”id”, enroll_date,

(ROUND((SYSDATE) – ROUND(enroll_date)) /12 “Seniority” FROM student

Jawaban : D

Enroll-date = tanggal sekarang

Sysdate = hari sekarang

Memakai ROUND tetapi tidak memakai /12

3. The EVENT table contains these columns:

EVENT_ID NUMBER

EVENT_NAME VARCHAR2(30)

EVENT_DESC VARCHAR2(100)

EVENT_TYPE NUMBER

LOCATION_ID NUMBER

You have been asked to provide a report of the number of different event types at each location. Which SELECT statement will produce the desired result?

  1. SELECT UNIQUE(location_id), COUNT(event_type) FROM event GROUP BY location_id;
  2. SELECT location_id, COUNT(DISTINCT event_type) FROM event GROUP BY location_id;
  3. SELECT location_id, MAX(DISTINCT event_type) FROM event GROUP BY location_id
  4. SELECT DISTINCT(event_type) FROM event GROUP BY location_id;
  5. SELECT COUNT(*), DISTINCT(location_id) FROM event;

Jawaban : B

DISTINCT = menampilkan secara unik

DISTINCT menurut event_type, GROUP BY dengan kolom location_id

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

  1. SELECT
  2. ORDER BY
  3. WHERE
  4. GROUP BY

Jawaban : C

Pada perintah WHERE penulisan AVG(list_price) > 60.00, salah karena Group Function AVG seharusnya tidak dipakai, sehingga menjadi Sub Query

SELECT supplier_id, AVG(cost)

FROM product

WHERE list_price > 60.00

(SELECT AVG(list_price)

FROM product)

GROUP BY supplier_id

ORDER BY AVG(cost) DESC;

5. Which statement type would be used to remove transactions more than one year old from the TRX table ?

    1. DCL
    2. DDL
    3. DML
    4. DRL
    5. TCL

Jawaban : C

Karena DELETE ada pada DML

DML juga bisa, SELECT,INSERT,UPDATE,DELETE,MERGE

6. Which Database Objects not incluided in a Oracle Database

  1. Table
  2. Sequence
  3. Constraint
  4. Synonym
  5. Index

Jawaban : C

Karena objek-objek database yaitu: table,view,synonym,sequence,index. Kecuali Constraint

7.            Evaluate this SELECT statement:

SELECT employee_id, name FROM employee

WHERE employee_id NOT IN

(SELECT employee_id FROM employee

WHERE department_id = 30 AND job = ‘CLERK’);

What would happen if the inner query returned a NULL values?

    1. A syntax error would be returned
    2. No rows would beselected from the employee tables
    3. All the EMPLOYEE_ID and NAME values in the EMPLOYEE table would be displayed
    4. Only the rows with EMPLOYEE_ID values equal to NULL would be included in the results

Jawaban : B

Sintak benar tapi tabel kosong karena tidak teridentifikasi

8.            You query the database with the SELECT statement:

SELECT COUNT(instructor_id)

FROM class;

Which value is displayed?

    1. 2
    2. 3
    3. 4
    4. 5
    5. The statement will NOT execute succesfully

Jawaban : C

Terdapat 4 baris yang terisi pada kolom INSTRUCTOR_ID yaitu: 4,1,4,2

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s