Jawaban Pembahasan Soal Sertivikasi 1
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?
- Use the table name in the ORDER BY clause.
- Remove the table aliases from the WHERE clause.
- Include the ORDER_AMOUNT column in the SELECT list.
- Use the table aliases instead of the table names in the WHERE clause.
- 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?
- SELECT UNIQUE(location_id), COUNT(event_type) FROM event GROUP BY location_id;
- SELECT location_id, COUNT(DISTINCT event_type) FROM event GROUP BY location_id;
- SELECT location_id, MAX(DISTINCT event_type) FROM event GROUP BY location_id
- SELECT DISTINCT(event_type) FROM event GROUP BY location_id;
- 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?
- SELECT
- ORDER BY
- WHERE
- 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 ?
- DCL
- DDL
- DML
- DRL
- 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
- Table
- Sequence
- Constraint
- Synonym
- 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?
- A syntax error would be returned
- No rows would beselected from the employee tables
- All the EMPLOYEE_ID and NAME values in the EMPLOYEE table would be displayed
- 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?
- 2
- 3
- 4
- 5
- The statement will NOT execute succesfully
Jawaban : C
Terdapat 4 baris yang terisi pada kolom INSTRUCTOR_ID yaitu: 4,1,4,2