Subqueries untuk Memecahkan Pertanyaan Queries

1. Which of the following are the types of sub-queries?

  1. Sub-kueri yang diurutkan
  2. Sub-kueri yang dikelompokkan
  3. Sub-kueri baris tunggal
  4. Bukan dari salah satu di atas

Answer: C. Subkueri adalah kueri lengkap yang bersarang di klausa SELECT, FROM, HAVING, atau WHERE dari kueri lain. Subkueri harus diapit tanda kurung dan minimal memiliki klausa SELECT dan FROM. Sub-kueri baris tunggal dan sub-kueri multi-baris adalah jenis utama sub-kueri

2.Which of the following is true about sub-queries?

  1. Mereka dieksekusi setelah kueri utama dijalankan
  2. Mereka dieksekusi secara paralel dengan kueri utama
  3. Pengguna dapat menjalankan kueri utama dan kemudian, jika diinginkan, menjalankan sub-kueri
  4. Mereka mengeksekusi sebelum kueri utama dijalankan.

Answer: D. Sub-kueri selalu dieksekusi sebelum eksekusi kueri utama, subkueri diselesaikan terlebih dahulu, hasil subkueri digunakan sebagai input untuk kueri luar.

3.Which of the following is true about the result of a sub-query?

  1. Hasil sub-kueri umumnya diabaikan saat dijalankan.
  2. Hasil sub-kueri tidak memberikan hasil, hanya membantu dalam mempercepat eksekusi kueri utama
  3. Hasil dari sub-kueri digunakan oleh kueri utama.
  4. Hasil sub-query selalu NULL

Answer: C. Subkueri diselesaikan terlebih dahulu. Hasil subkueri digunakan sebagai input untuk kueri luar.

4.Which of the following clause is mandatorily used in a sub-query?

  1. SELECT
  2. WHERE
  3. DIPESAN OLEH
  4. GRUP OLEH

Answer: A. Sub-kueri sama seperti kueri lainnya yang harus dimulai dengan klausa SELECT. Mereka terkandung dalam kueri luar.

5. Which of the following is a method for writing a sub-query in a main query?

  1. Dengan menggunakan JOINS
  2. Dengan menggunakan klausa WHERE
  3. Dengan menggunakan klausa GROUP BY
  4. Dengan menulis pernyataan SELECT yang disematkan dalam klausa pernyataan SELECT lainnya

Answer: D. Subkueri adalah kueri lengkap yang bersarang di klausa SELECT, FROM, HAVING, atau WHERE dari kueri lain. Subkueri harus diapit tanda kurung dan minimal memiliki klausa SELECT dan FROM.

6.In the given scenarios, which one would appropriately justify the usage of sub-query?

  1. Saat kita perlu meringkas nilai
  2. Saat kita perlu mengubah nilai karakter menjadi nilai tanggal atau angka
  3. Saat kita perlu memilih baris dari tabel dengan kondisi yang bergantung pada data dari tabel yang sama atau berbeda.
  4. Bukan dari salah satu di atas

Answer: C.

7.In which of the following clauses can a sub-query be used?

  1. HAVING
  2. WHERE
  3. FROM
  4. Semua yang di atas

Answer: D. Sub-kueri tidak berbeda dari kueri normal. Itu dapat menggunakan semua klausa utama dari pernyataan SELECT.

8.Which of the following single-row operators can be used for writing a sub-query?

  1. >=
  2. <
  3. =
  4. Semua yang di atas

Answer: D. Operator baris tunggal meliputi =,>, <,> =, <=, dan <>.

9.Which of the following multi-row operators can be used with a sub-query?

  1. IN
  2. ANY
  3. ALL
  4. Semua yang di atas

Answer: D. Subkueri beberapa baris mengembalikan lebih dari satu baris hasil. Operator yang bisa digunakan dengan subkueri beberapa baris termasuk IN, ALL, ANY, dan EXISTS.

10.What is true about the output obtained from a sub-query?

  1. Itu tetap di cache penyangga
  2. Itu tetap berada di dalam sub-kueri dan dapat digunakan nanti saat diperlukan
  3. Ini digunakan untuk menyelesaikan kueri luar (utama)
  4. Baik A dan C

Answer: C. Subkueri diselesaikan terlebih dahulu. Hasil subkueri digunakan sebagai input untuk kueri luar.

11.You need to find the salaries for all the employees who have a higher salary than the Vice President of a company 'ABC'.Which of the following queries will give you the required result? (Consider the table structure as given)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
  1. SELECT first_name, last_name, salary
FROM employees 
WHERE salary > (SELECT salary
FROM employees 
WHERE job_id = 'VICE-PRESIDENT');
  2. SELECT first_name, last_name, salary
FROM employees 
WHERE salary = (SELECT salary
FROM employees 
WHERE job_id = 'VICE-PRESIDENT');
  3. SELECT first_name, last_name, salary
FROM employees 
WHERE job_id = 'VICE-PRESIDENT');
  4. Bukan dari salah satu di atas

Answer: A. Dalam opsi 'A', sub-kueri bagian dalam memberikan gaji VP sebagai hasil dari kueri luar.

12.What among the following is true about sub-queries?

  1. Sub-kueri dapat ditulis di kedua sisi operator perbandingan
  2. Tanda kurung tidak wajib untuk sub-kueri
  3. Sub-kueri baris tunggal dapat menggunakan operator multi-baris, tetapi sebaliknya tidak memungkinkan
  4. Semua yang di atas

Answer: A. Sub kueri dapat ditempatkan di sisi kiri atau kanan operator perbandingan tergantung pada indentasi kueri dan kegunaan.

13. What will be the outcome of the following query? (Consider the given table structure)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
SELECT first_name, last_name, salary
FROM employees
WHERE salary ANY (SELECT salary FROM employees);
  1. Ini dijalankan dengan sukses memberikan hasil yang diinginkan
  2. Ini dijalankan dengan sukses tetapi tidak memberikan hasil yang diinginkan
  3. Ini melempar kesalahan ORA
  4. Itu berhasil dijalankan dan memberikan dua nilai untuk setiap baris yang diperoleh dalam set hasil

Answer: C. Operator multi-baris tidak dapat digunakan dalam sub-query baris tunggal dan sebaliknya.

14.Which of the following is true about single-row sub-queries?

  1. Mereka memberikan satu hasil dari kueri utama
  2. Mereka hanya memberikan satu baris pada set hasil
  3. Mereka hanya mengembalikan satu baris dari pernyataan SELECT bagian dalam
  4. Mereka memberikan beberapa baris dari kueri utama (luar)

Answer: C. Subkueri baris tunggal dapat mengembalikan maksimal satu nilai.

15.What is true about multi-row sub-queries?

  1. Mereka bisa mengembalikan lebih dari satu kolom sebagai hasil dari kueri dalam
  2. Mereka mengembalikan beberapa baris di kueri utama tetapi hanya satu hasil yang ditetapkan di kueri bagian dalam
  3. Mereka mengembalikan satu baris di kueri utama tetapi beberapa baris di sub-kueri bagian dalam
  4. Mereka mengembalikan lebih dari satu baris dari pernyataan SELECT bagian dalam

Answer: D. Sub-kueri multi-kolom mengembalikan lebih dari satu kolom dalam kumpulan hasil mereka, sub-kueri multi-baris mengembalikan lebih dari satu baris dari kueri dalam.

16.What among the following is true about single-row sub-queries?

  1. Mereka hanya mengembalikan satu baris
  2. Mereka menggunakan operator baris tunggal
  3. Baik A dan B
  4. Bukan dari salah satu di atas

Answer: C.

17.Which of the following operators cannot be used in a sub-query?

  1. AND
  2. <
  3. >
  4. <>

Answer: A. Operator baris tunggal meliputi =,>, <,> =, <=, dan <>. Operator multi-baris yang dapat digunakan dengan subkueri multi-baris termasuk IN, ALL, ANY, dan EXISTS.

Examine the exhibit and answer the questions 18 to 21 that follow.

18.You need to find out the names of all employees who belong to the same department as the employee 'Jessica Butcher' who is in department 100 and has an employee ID 40. Which of the following queries will be correct?

  1. SELECT first_name, last_name
FROM employees
WHERE last_name = 'Butcher'
And first_name = 'Jessica';
  2. SELECT first_name, last_name
FROM employees
WHERE department =100;
  3. SELECT first_name, last_name
FROM employees
WHERE department  = (SELECT department
FROM employees
WHERE first_name = 'Jessica'
AND last_name = 'Butcher');
  4. SELECT first_name, last_name
FROM employees
WHERE department  = (SELECT department
FROM employees
WHERE first_name = 'Jessica'
AND last_name = 'Butcher'
AND department = 100
AND employee_id = 40);

Answer: D. 'D' lebih tepat daripada 'C' karena menyaring id karyawan yang unik dan memastikan bahwa sub-query hanya akan mengembalikan satu baris. 'C' bisa gagal jika ada lebih dari satu karyawan dengan nama depan dan belakang yang sama.

19.You need to find out the employees which belong to the department of 'Jessica Butcher' and have salary greater than the salary of 'Jessica Butcher' who has an employee ID of 40. Which of the following queries will work?

  1. SELECT first_name, last_name
FROM employees
WHERE last_name = 'Butcher'
AND first_name = 'Jessica'
AND salary > 10000;
  2. SELECT first_name, last_name
FROM employees
WHERE department = 100;
  3. SELECT first_name, last_name
FROM employees
WHERE department = (SELECT department
			FROM employees
			WHERE first_name = 'Jessica'
			AND last_name = 'Butcher'
			AND employee_id = 40)
AND salary > (SELECT salary
			 FROM employees
			 WHERE first_name = 'Jessica'
			 AND last_name = 'Butcher'
			 AND employee_id = 40);
  4. SELECT first_name, last_name
FROM employees
WHERE department  = (SELECT department
			FROM employees
			WHERE first_name = 'Jessica'
			AND last_name = 'Butcher'
			AND department = 100);

Answer: C. Lebih dari satu sub-kueri dapat ditulis dalam satu pernyataan SQL untuk menambahkan lebih dari satu kondisi.

20.Based on the answers for questions 18th and 19th, what type of sub-queries is used by them?

  1. Sub-kueri baris tunggal
  2. Beberapa baris sub-kueri
  3. Baik A dan B
  4. Sub-kueri sebaris

Answer: A. Pertanyaan ke-18 dan ke-19 yang diberikan di atas menunjukkan penggunaan sub-kueri dalam pernyataan SELECT.

21.Consider two statements about outer and inner queries in context of SQL sub-queries?

saya. Kueri dalam bisa mendapatkan data hanya dari satu tabel

ii. Kueri dalam bisa mendapatkan data dari lebih dari satu tabel

Manakah dari pernyataan di atas yang benar?

  1. (i)
  2. (ii)
  3. Baik (i) dan (ii)
  4. Baik (i) maupun (ii)

Answer: B. Sub-kueri dapat mengambil data dari lebih dari satu tabel.

Examine the table structure as follows and answer the questions 22 to 27 that follow:

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

22.What will be the outcome of the following query? (Choose the most appropriate answer) 

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
SELECT last_name, job_id, salary
FROM employees
WHERE salary = (SELECT max(salary)
FROM employees);
  1. Itu berhasil dijalankan dan memberi karyawan yang memiliki gaji sama dengan gaji maksimal.
  2. Itu berhasil dijalankan tetapi tidak memberikan hasil yang diperlukan
  3. Ini melempar kesalahan karena fungsi grup digunakan dalam sub-kueri
  4. Ini melempar kesalahan karena satu baris sub-kueri harus berisi operator multi-baris

Answer: A. Fungsi grup dapat digunakan dalam sub-kueri.

23.What will be the outcome of the query that follows?

SELECT first_name, last_name, min(salary)
FROM employees
GROUP BY department_id 
HAVING MIN(salary) > 
		(SELECT min(salary)
		FROM employees
		WHERE department_id = 100);
  1. Ini berhasil dijalankan dan memberikan nama dan gaji minimum lebih besar dari departemen 100 dari semua karyawan
  2. Ini berhasil dijalankan dan memberikan gaji karyawan di departemen 100
  3. Ini berhasil dijalankan dan memberikan nama dan gaji minimum semua karyawan.
  4. Ini membuat kesalahan.

Answer: A. Klausa HAVING dapat digunakan dalam sub-query seperti yang ditunjukkan

24.You need to find the job which has a maximum average salary.Which of the following queries will give you the required results?

  1. SELECT job_id, avg(salary)
FROM employees
GROUP BY job_id;
  2. SELECT job_id, avg(salary)
FROM employees
GROUP BY job_id
HAVING job_id in (SELECT max(avg(salary) FROM employees);
  3. SELECT job_id, avg(salary)
FROM employees
GROUP BY job_id
HAVING max(avg(salary) in (SELECT max(avg(salary) FROM employees);
  4. SELECT job_id, avg(salary)
FROM employees
GROUP BY job_id
HAVING avg(salary) in (SELECT max(avg(salary) FROM employees GROUP BY job_id);

Answer: D. Sub-pertanyaan dapat menggunakan fungsi grup dan klausa HAVING untuk membatasi grup.

25.The following query throws an error. Choose the correct reason for the error as given in the options. 

SELECT first_name, last_name
FROM employees
WHERE commission_pct  = (SELECT min(commission_pct )
          FROM employees
          GROUP BY department_id);
  1. Klausa GROUP BY tidak diperlukan dalam sub-kueri
  2. Fungsi tidak dapat digunakan dalam pernyataan SELECT sub-kueri
  3. Sub-kueri baris tunggal memberikan banyak catatan
  4. Penggunaan operator "=" tidak valid; operator IN akan bekerja dengan benar

Answer: C, D. Klausa GROUP BY memberikan komisi_pct minimum untuk setiap departemen dan karenanya beberapa hasil diambil ke kueri utama yang memberikan kesalahan.

26.Consider the query given below.How many records will be returned as a result of the above query? (Assuming the no employee with job id XX exists in the company)

SELECT first_name, last_name
FROM employees
WHERE salary = (SELECT salary
		FROM employees
		WHERE job_id = 'XX');
  1. 1
  2. NULL
  3. 0
  4. Kueri menimbulkan kesalahan ORA karena sub-kueri tidak valid.

Answer: C. Karena tidak ada karyawan dengan job_id "XX" di perusahaan, sub-kueri tidak mengembalikan hasil, yang bila disamakan dengan job_id di kueri utama memberikan 0.

27.What happens if the WHERE condition in the query given in question 26 is replaced with a new one (WHERE job_id IS NOT NULL)? (Assume the number of records in 'employees' table is 14).

  1. 1
  2. 14
  3. 0
  4. Kesalahan ORA

Answer: D. Eksekusi kueri memunculkan pengecualian "ORA-01427: subkueri baris tunggal mengembalikan lebih dari satu baris".

28.Which of the following are valid multi row operators used for sub-queries?

  1. <=
  2. APA PUN> =
  3. !=
  4. >=

Answer: B. Subkueri beberapa baris mengembalikan lebih dari satu baris hasil. Operator yang bisa digunakan dengan subkueri beberapa baris termasuk IN, ALL, ANY, dan EXISTS. Operator multi baris IN, ANY, ALL harus digunakan dengan operator baris tunggal seperti yang ditunjukkan di opsi B.

Examine the table structure as given. Consider the query given below and answer the questions 29 to 33 that follow

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
SELECT first_name, last_name, salary, commission_pct 
FROM employees 
WHERE salary < ANY  (SELECT salary
		FROM employees
		WHERE department_id  = 100)
AND department_id  <> 101;

29.What does the ANY operator evaluates to in the above query?

  1. TRUE
  2. FALSE
  3. NULL
  4. 0

Answer: A. Operator multi baris mengembalikan hasil Boolean. Karena ada hasil gaji di departemen 100, hasilnya BENAR. Jika ada 0 hasil, itu dievaluasi ke FALSE.

30.What will be the outcome of the query if we assume that the department 100 has only one employee?

  1. Ini dijalankan dengan sukses memberikan satu hasil
  2. Itu mengeksekusi berhasil memberikan gaji semua karyawan
  3. NULL
  4. Ini melempar kesalahan ORA

Answer: D. Jika departemen 100 memiliki satu hasil (sub-query baris tunggal), operator <ANY memberikan kesalahan karena merupakan operator multi-baris.

31.What will be the outcome of the query given above if the < ANY operator is replaced with = ANY operator?

  1. Oracle akan memperlakukan setiap nilai gaji yang dikembalikan dari sub-query seperti halnya dengan operator IN
  2. Tidak akan ada perbedaan hasil
  3. Hasilnya akan berbeda
  4. Eksekusi akan memunculkan kesalahan ORA

Answer: A. = Operator APAPUN setara dengan operator IN.

32.What can be said about the < ANY operator in the query given above?

  1. Ini memberi nilai gaji maksimal
  2. Ini memberikan nilai gaji minimum
  3. Artinya memberikan nilai yang lebih rendah dari yang tertinggi
  4. Bukan dari salah satu di atas

Answer: C. Operator multi baris <ANY mengevaluasi ke pernyataan "Kurang dari maksimum" dari subkueri. '> ALL' Lebih dari nilai tertinggi yang dikembalikan oleh subkueri. '<ALL' Kurang dari nilai terendah yang dikembalikan oleh subkueri. '<ANY' Kurang dari nilai tertinggi yang dikembalikan oleh subkueri. '<ANY' Lebih dari nilai terendah yang dikembalikan oleh subkueri. '= ANY' Sama dengan nilai apa pun yang dikembalikan oleh subkueri (sama seperti IN). Baris '[NOT] EXISTS' harus cocok dengan nilai di subkueri

33.Assume that the < ANY operator is replaced with the > ANY. What is true about this operator?

  1. Ini memberi gaji maksimal
  2. Ia hanya menemukan gaji maksimum dari sub-query
  3. Ini memberi lebih dari gaji minimum
  4. Ini memberi gaji minimum

Answer: C. Operator multi baris> ANY mengevaluasi pernyataan "Lebih besar dari minimum" subkueri. '> ALL' Lebih dari nilai tertinggi yang dikembalikan oleh subkueri. '<ALL' Kurang dari nilai terendah yang dikembalikan oleh subkueri. '<ANY' Kurang dari nilai tertinggi yang dikembalikan oleh subkueri. '> ANY' Lebih dari nilai terendah yang dikembalikan oleh subkueri. '= ANY' Sama dengan nilai apa pun yang dikembalikan oleh subkueri (sama seperti IN). Baris '[NOT] EXISTS' harus cocok dengan nilai di subkueri

34. Examine the given table structure and consider the following query:

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
SELECT employee_id, first_name, last_name
FROM employees
WHERE salary IN (SELECT max(salary)
		FROM employees
		GROUP BY department_id );

Klausa WHERE manakah di antara berikut ini yang setara dengan yang diberikan dalam kueri di atas? (Asumsikan bahwa gaji 2.500, 3000, 3500,4000)

  1. WHERE salary < ANY (SELECT max(salary)
			FROM employees
			GROUP BY department_id );
  2. WHERE salary < ALL (SELECT max(salary)
			FROM employees
			GROUP BY department_id );
  3. WHERE salary = (SELECT max(salary)
			FROM employees
			GROUP BY department_id );
  4. WHERE salary IN (2500,3000,3500,4000);

Answer: D. Saat operator IN digunakan, Oracle memperlakukan hasil individual dari sub-query seperti yang ditunjukkan pada opsi D.

Examine the structure of the EMPLOYEES table as given below and answer the questions 35 to 37 that follow.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

35. You need to find out which of the employees have a salary less than that of the salary for the job ID 'FIN_ACT'. Which of the following queries will give you the required output?

  1. SELECT employee_id, first_name, last_name
FROM employees
WHERE salary < ALL 
		(SELECT salary
		 FROM employees
		 WHERE job_id = 'FIN_ACT')
		 AND job_id <> 'FIN_ACT';
  2. SELECT employee_id, first_name, last_name
FROM employees
WHERE salary > ALL 
		(SELECT salary
		FROM employees
		WHERE job_id = 'FIN_ACT')
		AND job_id <> 'FIN_ACT';
  3. SELECT employee_id, first_name, last_name
FROM employees
WHERE salary < ANY 
		(SELECT salary
		FROM employees
		WHERE job_id = 'FIN_ACT')
		AND job_id <> 'FIN_ACT';
  4. SELECT employee_id, first_name, last_name
FROM employees
WHERE salary = 	(SELECT salary
		FROM employees
		WHERE job_id = 'FIN_ACT')
		AND job_id <> 'FIN_ACT';

Answer: A. <SEMUA berarti kurang dari minimum. '> ALL' Lebih dari nilai tertinggi yang dikembalikan oleh subkueri. '<ALL' Kurang dari nilai terendah yang dikembalikan oleh subkueri. '<ANY' Kurang dari nilai tertinggi yang dikembalikan oleh subkueri. '> ANY' Lebih dari nilai terendah yang dikembalikan oleh subkueri. '= ANY' Sama dengan nilai apa pun yang dikembalikan oleh subkueri (sama seperti IN). Baris '[NOT] EXISTS' harus cocok dengan nilai di subkueri

36.What will be the outcome of the above query (the option A in the question above), if the < ALL is replaced with the >ALL?

  1. Ini akan dieksekusi dengan sukses memberikan hasil yang sama.
  2. Ini akan memunculkan kesalahan ORA
  3. Ini akan berhasil dijalankan tetapi memberikan detail karyawan yang memiliki gaji lebih rendah dari semua karyawan dengan job_id 'FI_ACCOUNTANT'.
  4. Bukan dari salah satu di atas

Answer: C. > SEMUA berarti kurang dari minimum. '> ALL' Lebih dari nilai tertinggi yang dikembalikan oleh subkueri. '<ALL' Kurang dari nilai terendah yang dikembalikan oleh subkueri. '<ANY' Kurang dari nilai tertinggi yang dikembalikan oleh subkueri. '> ANY' Lebih dari nilai terendah yang dikembalikan oleh subkueri. '= ANY' Sama dengan nilai apa pun yang dikembalikan oleh subkueri (sama seperti IN). Baris '[NOT] EXISTS' harus cocok dengan nilai di subkueri

37.You need to find the salaries for all employees who are not in the department 100. Which of the following queries will give you the required result?

  1. SELECT employee_id, first_name, last_name
FROM employees
WHERE salary !=ALL 
		(SELECT salary
		FROM employees
		WHERE department_id  = 100)
		AND department_id  <> 100;
  2. SELECT employee_id, first_name, last_name
FROM employees
WHERE salary NOT IN 
		(SELECT salary
		FROM employees
		WHERE department_id  = 100)
		AND department_id  <> 100;
  3. SELECT employee_id, first_name, last_name
FROM employees
WHERE salary NOT ALL 
		(SELECT salary
		FROM employees
		WHERE department_id  = 100)
		AND department_id  <> 100;
  4. SELECT employee_id, first_name, last_name
FROM employees
WHERE salary != (SELECT salary
		FROM employees
		WHERE department_id  = 100)
		AND department_id  <> 100;

Answer: C. TIDAK dapat digunakan dengan operator multi baris IN, ANY dan ALL.

Periksa struktur tabel seperti yang diberikan. Pertimbangkan pertanyaan berikut dan jawab pertanyaan 38 dan 39 yang mengikuti. Anda perlu menemukan karyawan yang tidak memiliki bawahan sub-ordinat kepada mereka. (Asumsikan ada 0 hasil yang diharapkan)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
SELECT first_name, last_name 
FROM employees
WHERE employee_id NOT IN 
		(SELECT manager_id
		FROM employees);

38.What will be the result of the query given above?

  1. 10
  2. NULL
  3. Kesalahan ORA
  4. 0

Answer: D. Salah satu nilai di dalam sub-query adalah NULL (semua karyawan bukan manajer!)

39.Which of the following WHERE clauses should be added / modified to the above query to give the expected results?

  1. WHERE employee_id != (SELECT manager_id FROM employees);
  2. WHERE employee_id IN (SELECT manager_id FROM employees);
  3. WHERE employee_id <>ALL (SELECT manager_id FROM employees);
  4. WHERE employee_id NOT IN (SELECT manager_id
			FROM employees
			WHERE manager_id is NOT NULL);

Answer: B, D. Jika sub-kueri cenderung memiliki nilai NULL, jangan gunakan operator NOT IN atau jika menggunakan, ubah sub-kueri dengan klausa WHERE tambahan (opsi D)

40.What is true about sub-queries in general?

  1. Sub-kueri harus dijalankan secara terpisah dari kueri utama
  2. Sub-kueri dapat dieksekusi sesuai keinginan pengguna, mereka tidak terkait dengan eksekusi kueri utama
  3. Sub-kueri sama dengan dua kueri berurutan di mana hasil kueri dalam digunakan oleh kueri utama
  4. Semua yang di atas

Answer: C.

41. Which of the following is true about sub-queries?

  1. Sub-kueri dapat mengembalikan 0 baris atau lebih
  2. Sub-kueri hanya dapat digunakan dalam klausa SELECT
  3. Penyusunan sub-kueri dibatasi hingga 2 level
  4. Fungsi grup tidak dapat digunakan di sub-kueri

Answer: A. Subkueri adalah kueri lengkap yang bersarang di klausa SELECT, FROM, HAVING, atau WHERE dari kueri lain. Subkueri harus diapit tanda kurung dan minimal memiliki klausa SELECT dan FROM. Subkueri baris tunggal dapat mengembalikan maksimal satu nilai. Subkueri multi-kolom mengembalikan lebih dari satu kolom ke kueri luar.

42. Examine the table structure as given.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)

Pertimbangkan pertanyaan berikut.

SELECT first_name, last_name 
FROM employees
WHERE employee_id NOT IN 
		(SELECT manager_id, hire_date
		FROM employees 
		WHERE manager_id is not null);

Kueri ini mengembalikan kesalahan. Apa alasan terjadinya kesalahan?

  1. Operator NOT IN yang digunakan tidak valid
  2. Klausa WHERE di sub-kueri tidak ditulis dengan benar
  3. Kolom di klausa SELECT sub-kueri seharusnya hanya satu jika ada ketidaksamaan yang digunakan dalam kueri utama
  4. Sub-kueri menggunakan tabel yang sama sebagai kueri utama

Answer: C. Kolom yang dipilih di sub-kueri harus sama seperti di sisi lain operator perbandingan. Setiap ketidaksamaan tipe data atau jumlah kolom akan menghasilkan kesalahan ORA.

43.A report has to be extracted which displays all the departments that have one or more employees assigned to them. Which of the following queries will give the required output? (Consider the table structure as given)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
  1. SELECT department_name
FROM employees
WHERE department_id  IN (SELECT distinct (department_id )
			FROM employees);
  2. SELECT department_name
FROM employees
WHERE department_id  ANY (SELECT distinct (department_id )
			FROM employees);
  3. SELECT department_name
FROM employees
WHERE department_id  < ANY (SELECT distinct (department_id )
			FROM employees);
  4. SELECT department_name
FROM employees
WHERE department_id  = ANY (SELECT distinct (department_id )
			FROM employees);

Answer: A, D.

44.What is the maximum level of sub-queries allowed in Oracle in a single SQL statement?

  1. 20
  2. 50
  3. Unlimited
  4. 255

Answer: D. Oracle mendukung Penyusunan kueri hingga 255 level.

45. What should be the best practice to follow when we know what values we need to pass on to the main query in Oracle queries?

  1. Menggunakan GROUP BY
  2. Menggunakan sub-kueri
  3. Menggunakan HAVING
  4. Bukan dari salah satu di atas

Answer: D. Mungkin saja sub-queri memberikan hasil NULL, yang menghasilkan 0 baris pada hasil utama; karena itu adalah praktik yang baik untuk menggunakannya hanya jika kita tahu nilai apa yang kita butuhkan.

Examine the table structure as given. Consider the following query and answer the questions 46 and 47 that follow: 

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
SELECT employee_id, first_name, last_name, job_id
FROM employees
WHERE job_id = (SELECT job_id FROM employees);

46.You need to find all the employees whose job ID is the same as that of an employee with ID as 210. Which of the following WHERE clauses would you add / modify to achieve this result? (Consider the table structure as given

  1. WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 210);
  2. WHERE job_id IN (SELECT job_id FROM employees WHERE employee_id = 210);
  3. WHERE job_id > (SELECT job_id FROM employees WHERE employee_id = 210);
  4. WHERE job_id >= (SELECT job_id FROM employees WHERE employee_id = 210);

Answer: A.

47.Assume that you change the WHERE clause as given in the option A in question 46 as the following.

WHERE job_id = (SELECT job_id FROM employees WHERE employee_id < 210);

Apa hasil dari perubahan ini?

  1. Hasilnya akan sama
  2. Kesalahan ORA muncul saat eksekusi
  3. Hasilnya akan berbeda
  4. Kueri akan berhasil dieksekusi dengan memberikan 0 baris.

Answer: B. Sub-kueri memberikan lebih dari satu hasil pada perubahan yang diberikan dan karenanya operator multi baris harus mengganti "=" dalam kueri utama yang diberikan di atas.

48.Examine the table structures as shown in the exhibit below.

Anda perlu menampilkan nama-nama karyawan yang memiliki gaji tertinggi. Manakah dari pernyataan SQL berikut yang benar?

  1. SELECT first_name, last_name, grade
FROM employees, grade
WHERE (SELECT max (salary) FROM employees) BETWEEN losal and hisal;
  2. SELECT first_name, last_name, grade
FROM employees, grade
WHERE (SELECT max (salary) FROM employees) BETWEEN losal and hisal
AND salary BETWEEN losal and hisal;
  3. SELECT first_name, last_name, grade
FROM employees, grade
WHERE salary = (SELECT max (salary) FROM employees) 
AND salary BETWEEN losal and hisal;
  4. SELECT first_name, last_name, grade
FROM employees, grade
WHERE salary IN (SELECT max (salary) FROM employees) 
AND max(salary) BETWEEN losal and hisal;

Answer: B, C. Sub-kueri dapat ditulis di kedua sisi operator

49.What is the sub-query in the FROM clause of an SQL statement? (Choose the most appropriate answer)

  1. Sub-kueri baris tunggal
  2. Sub-kueri multi baris
  3. Tampilan Sebaris
  4. Sub-kueri terkait bersama

Answer: C. Jika sub-kueri muncul di klausa FROM dari pernyataan SELECT, itu membentuk tampilan Inline. Oracle secara internal membuat tampilan sementara untuk eksekusi kueri.

50.What is the maximum number of nesting level allowed in an Inline View type sub-query?

  1. 255
  2. 300
  3. 216
  4. Unlimited

Answer: D. Karena tidak ada batasan pada jumlah tabel yang dapat digabungkan, tidak ada batasan pada jumlah tampilan sebaris dalam sebuah kueri.

51.What is true about co-related sub-queries?

  1. Tabel yang digunakan dalam kueri utama juga digunakan dalam sub-kueri terkait bersama
  2. Sub-kueri yang mereferensikan kolom yang digunakan dalam kueri utama disebut sub-kueri terkait bersama
  3. Sub-kueri yang ditulis tanpa tanda kurung disebut sub-kueri yang terkait bersama
  4. Sub-kueri yang wajib menggunakan tabel yang berbeda dari yang digunakan dalam kueri utama disebut sub-kueri terkait bersama.

Answer: B. Subkueri terkait mereferensikan kolom di kueri luar dan mengeksekusi subkueri sekali untuk setiap baris di kueri luar sementara subkueri tak berkorelasi mengeksekusi subkueri terlebih dahulu dan meneruskan nilai ke kueri luar.

52.Which of the following statements cannot be parent statements for a sub-query?

  1. SELECT
  2. GRUP OLEH
  3. UPDATE
  4. DELETE

Answer: B. Opsi lainnya bisa berada di kueri utama (kueri induk) dari sub-kueri.

53.What is true about a co-related sub-query?

  1. Ini dievaluasi hanya sekali untuk kueri induk
  2. Ini dievaluasi hanya tiga kali untuk kueri induk
  3. Ini dievaluasi sekali untuk setiap baris yang diproses oleh sub-kueri induk
  4. Semua yang di atas

Answer: C. Subkueri terkait mereferensikan kolom di kueri luar dan mengeksekusi subkueri sekali untuk setiap baris di kueri luar; dan operator EXISTS digunakan untuk menguji apakah hubungan atau tautan ada.

54.Examine the given table structure. You need to write a query which returns the names of the employees whose salaries exceed their respective department's average salary. Which of the following will work? (Choose the most appropriate answer)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
  1. SELECT employee_id, first_name, last_name
FROM employees e
WHERE salary > (SELECT avg (salary)
		FROM employees
		WHERE e.department_id  = department_id )
		ORDER BY department_id ;
  2. SELECT employee_id, first_name, last_name
FROM employees e
WHERE salary > ANY (SELECT avg(salary)
		FROM employees
		WHERE e.department_id  = department_id )
		ORDER BY department_id ;
  3. SELECT employee_id, first_name, last_name
FROM employees e
WHERE salary = (SELECT avg(salary)
		FROM employees
		WHERE e.department_id  = department_id )
		ORDER BY department_id ;
  4. SELECT employee_id, first_name, last_name
FROM employees e
WHERE salary < ANY  (SELECT avg(salary)
		FROM employees
		WHERE e.department_id  = department_id )
		ORDER BY department_id ;

Answer: A. Di sini ID departemen diperoleh, digunakan untuk mengevaluasi kueri induk dan jika gaji di baris itu lebih besar dari gaji rata-rata departemen di baris itu, hasilnya dikembalikan.

55.Examine the given table structure. Which of the following queries will display duplicate records in a table EMPLOYEES?

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
  1. SELECT *
FROM employees E
WHERE exists (SELECT 1 FROM employees E1
		WHERE E.employee_id = E1.employee_id);
  2. SELECT *
FROM employees E
WHERE exists (SELECT 1 FROM employees E1
		WHERE E.employee_id = E1.employee_id
		AND E.ROWID < E1.ROWID);
  3. SELECT *
FROM employees E
WHERE exists (SELECT 1 FROM employees E1
		WHERE E.ROWID < E1.ROWID);
  4. SELECT *
FROM employees E
WHERE = ANY (SELECT 1 FROM employees E1
		WHERE E.employee_id = E1.employee_id
		And E.ROWID < E1.ROWID);

Answer: A. Subkueri terkait mereferensikan kolom di kueri luar dan mengeksekusi subkueri sekali untuk setiap baris di kueri luar; dan operator EXISTS digunakan untuk menguji apakah hubungan atau tautan ada. Ini dapat digunakan untuk menemukan baris duplikat dalam tabel di mana duplikat dikenai kolom atau kumpulan kolom.

Examine the structures for the tables DEPARTMENTS and EMPLOYEES and answer the questions 56 and 57 that follow. 

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)

56.Which of the following queries will display the system date and count of records in the DEPARTMENTS and EMPLOYEES table?

  1. SELECT sysdate,
		(SELECT * FROM departments) dept_count,
		(SELECT * FROM employees) emp_count
FROM DUAL;
  2. SELECT sysdate,
		(SELECT count(*) FROM departments) dept_count,
		(SELECT count(*) FROM employees) emp_count
FROM DUAL
GROUP BY department_id ;
  3. SELECT sysdate,
		(SELECT * FROM departments) dept_count,
		(SELECT * FROM employees) emp_count
FROM DUAL
GROUP BY employee_id;
  4. SELECT sysdate,
		(SELECT count(*) FROM departments) dept_count,
		(SELECT count(*) FROM employees) emp_count
FROM DUAL;

Answer: D. Subkueri baris tunggal juga bisa bertumpuk di klausa SELECT kueri luar. Dalam kasus ini, nilai yang dikembalikan subkueri tersedia untuk setiap baris keluaran yang dihasilkan kueri luar. Biasanya, teknik ini digunakan untuk melakukan penghitungan dengan nilai yang dihasilkan dari subkueri.

57.Which of the following queries will tell whether a given employee is a manager in a Company 'XYZ'?

  1. SELECT employee_id, manager_id
FROM employees A
WHERE employee_id ANY (SELECT manager_id from employees B)
ORDER BY manager_id desc;
  2. SELECT employee_id, manager_id
FROM employees A
WHERE employee_id < ALL (SELECT manager_id from employees B)
  3. SELECT employee_id, manager_id
FROM employees A
WHERE employee_id IN (SELECT manager_id from employees B)
ORDER BY manager_id desc;
  4. SELECT employee_id, manager_id
FROM employees A
WHERE employee_id in (SELECT manager_id from employees B)
GROUP BY department_id ;

Answer: C.

Examine the exhibit and answer the question 58 that follows:

58.Which of the following queries will give you maximum salary of an employee in a particular city?

  1. SELECT max (salary), city
FROM
(SELECT salary, department_id , loc, city
FROM employees natural join departments natural join locations);
  2. SELECT salary, city
FROM
(SELECT salary, department_id , loc, city
FROM employees natural join departments natural join locations);
  3. SELECT max (salary), city
FROM
(SELECT salary, department_id , loc, city
FROM employees natural join departments natural join locations)
GROUP BY city;
  4. SELECT max (avg(salary)), city
FROM
(SELECT salary, department_id , loc, city
FROM employees natural join departments natural join locations);

Answer: C. Saat subkueri multi-kolom digunakan di klausa FROM kueri luar, itu membuat tabel sementara yang bisa direferensikan oleh klausa lain dari kueri luar. Tabel sementara ini secara lebih formal disebut tampilan sebaris. Hasil subkueri diperlakukan seperti tabel lain di klausa FROM. Jika tabel sementara berisi data yang dikelompokkan, subset yang dikelompokkan akan diperlakukan sebagai baris data terpisah dalam tabel.

Examine the table structures as given below.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)

Pertimbangkan pertanyaan berikut dan jawablah pertanyaan 59 hingga 62 yang mengikuti.

SELECT  department_name
FROM departments d INNER JOIN employees e
ON (d.employee_id = e.employee_id)
GROUP BY department_name;

59.Which of the following queries can replace the above query by using sub-queries giving the same result?

  1. SELECT department_name
FROM departments
WHERE department_id  = ANY (SELECT department_id  FROM employees);
  2. SELECT department_name
FROM departments
WHERE department_id  IN (SELECT distinct(department_id ) FROM employees);
  3. SELECT department_name
FROM departments
WHERE department_id  = (SELECT distinct(department_id ) FROM employees);
  4. SELECT department_name
FROM departments
WHERE department_id  ANY (SELECT distinct(department_id ) FROM employees);

Answer: A, B.

60.Assume that the sub-query as shown in the query given above is modified to the following.

(SELECT distinct (department_id ) FROM employees ORDER BY department_id );

Apa hasil dari perubahan ini? (Pilih jawaban yang paling tepat)

  1. Ini akan memerintahkan department_id diambil dari sub-kueri dan menampilkannya dalam urutan menaik
  2. Ini akan memunculkan kesalahan ORA karena klausa ORDER BY harus disertai dengan klausa GROUP BY
  3. Ini akan memunculkan kesalahan ORA karena klausa ORDER BY tidak dapat digunakan di dalam sub-kueri
  4. Ini akan berhasil dijalankan.

Answer: C. Sebuah subkueri, kecuali satu di klausa FROM, tidak bisa memiliki klausa ORDER BY. Jika Anda perlu menampilkan keluaran dalam urutan tertentu, sertakan klausa ORDER BY sebagai klausa terakhir kueri luar.

61.Assume that the query given above is modified as the below one.

SELECT department_name
FROM departments
WHERE department_id  = ANY (SELECT department_id  FROM employees)
ORDER BY department_id  desc;

Apa hasil dari perubahan ini? (Pilih jawaban yang paling tepat)

  1. Ini akan memerintahkan department_id diambil dari sub-kueri dan menampilkannya dalam urutan menaik
  2. Ini akan memerintahkan department_id diambil dari sub-kueri dan menampilkannya dalam urutan menurun
  3. Ini akan memunculkan kesalahan ORA karena klausa ORDER BY tidak dapat digunakan di dalam sub-kueri
  4. Bukan dari salah satu di atas

Answer: D. Sebuah subkueri, kecuali satu di klausa FROM, tidak bisa memiliki klausa ORDER BY. Jika Anda perlu menampilkan keluaran dalam urutan tertentu, sertakan klausa ORDER BY sebagai klausa terakhir kueri luar.

62.Which of the following can be used to order results in a sub-query?

  1. DIPESAN OLEH
  2. HAVING
  3. GRUP OLEH
  4. Semua yang di atas

Answer: C. Secara default, klausa GROUP BY melakukan pengurutan di sub-kueri.

Examine the exhibit below and answer the questions 63 to 65 that follow:

Pertimbangkan pertanyaan berikut:

SELECT au_id, au_title
FROM audit
WHERE au_details in (SELECT au_details
		  FROM audit
		  WHERE au_title like 'S%')
		  ORDER BY au_title;

63.What will be the outcome of the query given above?

  1. Ini memberikan semua AU_ID dan AU_TITLE yang dimulai dengan huruf 'S%'
  2. Ini memberikan semua AU_ID dan AU_TITLE yang dimulai dengan huruf 'S%' yang diurutkan berdasarkan judul dalam urutan menaik
  3. Ini melempar kesalahan ORA
  4. Ini mengembalikan nilai 0

Answer: C. Kolom dengan CLOB, BLOB, NCLOB atau ARRAY tidak dapat digunakan dalam sub-query.

64. What will be the outcome of the following query?

SELECT *
FROM employees
WHERE salary BETWEEN (SELECT max(salary)
			FROM employees
			WHERE department_id  = 100) 
AND (SELECT min(salary) FROM employees where department_id  = 100);

Kueri ini mengembalikan kesalahan. Apa alasan terjadinya kesalahan tersebut?

  1. Klausa GROUP BY harus digunakan saat fungsi MAX digunakan
  2. Kedua sub-kueri tidak dapat menggunakan ID departemen yang sama di kueri luar yang sama
  3. BETWEEN operator tidak dapat digunakan dengan sub-query
  4. Klausa SELECT harus memiliki kolom yang disebutkan dan bukan tanda bintang (*)

Answer: C. Operator BETWEEN dapat digunakan dalam sub-kueri tetapi tidak dengan sub-kueri.

65.What is true about using NOT IN when writing queries with sub-queries in them?

  1. NOT IN mengabaikan semua nilai NULL dan hanya memberikan nilai NOT NULL
  2. NOT IN menempatkan semua nilai NULL di akhir dan memberikan NOT NULL untuk ditampilkan pertama kali
  3. NOT IN tidak boleh digunakan jika nilai NULL diharapkan dalam set hasil
  4. NOT IN hanyalah negasi dari operator IN dan dapat diubah tanpa peringatan apa pun.

Answer: C. SQL menangani nilai NULL dengan cara yang berbeda dan karenanya merupakan praktik yang baik untuk menghindari NOT IN jika set hasil mungkin berisi NULL.

Consider the following table structures and answer the questions 66 to 72 that follow:

66. You need to find out the names and IDs of the departments in which the least salary is greater than the highest salary in the department 10. Which of the following queries will give the required result.

  1. SELECT department_id , min(salary)
FROM employees
GROUP BY department_id 
HAVING min(salary) >
		(
		select max(salary)
		FROM employees
		where department_id  = 10
		)
  2. SELECT department_id , min(salary)
FROM employees
GROUP BY department_id 
HAVING min(salary) > ANY
		(
		select max(salary)
		FROM employees
		)
  3. SELECT department_id , min(salary)
FROM employees
HAVING max(salary) < ANY
		(
		select min(salary)
		FROM employees
		where department_id  = 10
		)
  4. SELECT department_id , min(salary)
FROM employees
GROUP BY department_id 
HAVING min(salary) > ALL
		(
		select max(salary)
		FROM employees
		where department_id  = 10
		)

Answer: A.

67.Write a query to find the employees whose salary is equal to the salary of at least one employee in department of id 10. (Choose the best answer)

  1. SELECT employee_id, Salary
FROM employees
WHERE salary in
		(
		SELECT salary
		FROM employees
		where department_id  = 10
		)
  2. SELECT employee_id, Salary
FROM employees
WHERE salary =ANY
		(
		SELECT salary
		FROM employees
		where department_id  = 10
		)
  3. SELECT employee_id, Salary
FROM employees
WHERE salary ALL
		(
		SELECT salary
		FROM employees
		where department_id  = 10
		)
  4. SELECT employee_id, Salary
FROM employees
WHERE salary  < ANY
		(
		SELECT salary
		FROM employees
		where department_id  = 10
		)

Answer: A, B.

68.You need to find out all the employees who have salary greater than at least one employee in the department 10. Which of the following queries will give you the required output?

  1. SELECT employee_id, Salary
FROM employees
WHERE salary >= ANY
		(
		SELECT salary
		FROM employees
		where department_id  = 10
		)
  2. SELECT employee_id, Salary
FROM employees
WHERE salary > ANY
		(
		SELECT salary
		FROM employees
		where department_id  = 10
		)
  3. SELECT employee_id, Salary
FROM employees
WHERE salary < ANY
		(
		SELECT salary
		FROM employees
		where department_id  = 10
		)
  4. SELECT employee_id, Salary
FROM employees
WHERE salary = ALL
		(
		SELECT salary
		FROM employees
		where department_id  = 10
		)

Answer: B.

69.You need to find out all the employees who have salary lesser than the salary of all the employees in the department 10. Which of the following queries will give you the required output?

  1. SELECT employee_id, Salary
FROM employees
WHERE salary > ALL
		(
		SELECT salary
		FROM employees
		where department_id  = 10
		)
  2. SELECT employee_id, Salary
FROM employees
WHERE salary =ALL
		(
		SELECT salary
		FROM employees
		where department_id  = 10
		)
  3. SELECT employee_id, Salary
FROM employees
WHERE salary < ALL
		(
		SELECT salary
		FROM employees
		where department_id  = 10
		)
  4. SELECT employee_id, Salary
FROM employees
WHERE salary < ANY
		(
		SELECT salary
		FROM employees
		where department_id  = 10
		)

Answer: C. Subkueri beberapa baris mengembalikan lebih dari satu baris hasil. Operator yang bisa digunakan dengan subkueri multi-baris menyertakan IN, ALL, ANY, dan EXISTS. Subkueri multi-kolom mengembalikan lebih dari satu kolom ke kueri luar. Kolom data diteruskan ke kueri luar dalam urutan yang sama seperti yang dicantumkan di klausa SELECT subkueri.

70.You need to find out all the employees who have their manager and department matching with the employee having an Employee ID of 121 or 200. Which of the following queries will give you the required output?

  1. SELECT employee_id, manager_id,department_id 
FROM employees
WHERE (manager_id,department_id ) = ANY
		(
		select manager_id,
		department_id 
		FROM employees
		where employee_id in (121,200)
		)
  2. SELECT employee_id, manager_id,department_id 
FROM employees
WHERE (manager_id,department_id ) < ANY
		(
		select manager_id,
		department_id 
		FROM employees
		where employee_id in (121,200)
		)
  3. SELECT employee_id, manager_id,department_id 
FROM employees
WHERE (manager_id,department_id ) > ANY
		(
		select manager_id,
		department_id 
		FROM employees
		where employee_id in (121,200)
		)
  4. SELECT employee_id, manager_id,department_id 
FROM employees
WHERE (manager_id,department_id ) in
		(
		select manager_id,
		department_id 
		FROM employees
		where employee_id in (121,200)
		)

Answer: A, D. Subkueri beberapa baris mengembalikan lebih dari satu baris hasil. Operator yang dapat digunakan dengan subkueri multi-baris termasuk IN, ALL, ANY, dan EXISTS. Subkueri multi-kolom mengembalikan lebih dari satu kolom ke kueri luar. Kolom data diteruskan ke kueri luar dalam urutan yang sama seperti yang dicantumkan di klausa SELECT subkueri.

71.You need to find the department name of an employee with employee ID 200. Which of the following queries will be correct? (Choose the most appropriate answer)

  1. SELECT employee_id, first_name, last_name,department_id ,
(SELECT department_name
FROM departments d, employees E
WHERE d.department_id  = e.department_id 
And employee_id = 200
)
FROM employees e
  2. SELECT employee_id, first_name, last_name,department_id ,
(SELECT department_ID
FROM departments d
WHERE d.department_id  = department_id 
)
FROM employees e
WHERE employee_id = 200;
  3. SELECT employee_id, first_name, last_name,department_id ,
(SELECT department_name
FROM departments d
WHERE d.department_id  = e.department_id 
And employee_id = 200
)
FROM employees e
  4. SELECT employee_id, first_name, last_name,department_id ,
(SELECT department_name
FROM departments d,employee E 
WHERE d.department_id  = e.department_id 
)
FROM employees e

Answer: C.

72.You need to find the highest earning employee with the job ID as 'SA_REP'. Which of the following queries will be correct? (Choose the most appropriate answer)

  1. SELECT job_id, employee_id, Salary
FROM employees e
WHERE job_id  =
(
SELECT  distinct salary
FROM employees E1
WHERE E.job_id  = E1.job_id 
AND E.salary <=  E1.salary
AND job_id  = 'SA_REP'
  2. SELECT department_id , employee_id, Salary
FROM employees E
WHERE 1 =
(
SELECT  count(distinct salary)
FROM employees E1
WHERE E.job_id  = E1.job_id 
AND E.salary <=  E1.salary
AND job_id  = 'SA_REP'
)
  3. SELECT department_id , employee_id, Salary
FROM employees E
WHERE 0 =
(
SELECT  count(distinct salary)
FROM employees E1
WHERE E.job_id  = E1.job_id 
AND E.salary =  E1.salary
AND job_id  = 'SA_REP'
)
  4. SELECT department_id , employee_id, Salary
FROM employees E
WHERE 1 =
(
SELECT salary
FROM employees E1
WHERE E.job_id  < E1.job_id 
AND E.salary <=  E1.salary
AND job_id  = 'SA_REP'
)

Answer: B.

Consider the EMPLOYEES table structure as shown in the exhibit and answer the questions 73 to 77 that follow:

73.You need to find the job which has at least one employee in it. Which of the following queries will be correct? (Choose the most appropriate answer)

  1. SELECT employee_id, Job_id
FROM employees E
WHERE exists
(
SELECT 1
FROM employees E1
WHERE E.job_id  = E1.job_id )
  2. SELECT employee_id, Job_id 
FROM employees E
WHERE exists
(
SELECT *
FROM employees E1
WHERE E.job_id  = E1.job_id )
  3. SELECT employee_id, Job_id 
FROM employees E
WHERE not exists
(
SELECT *
FROM employees E1
WHERE E.job_id  = E1.job_id )
  4. SELECT employee_id, Job_id 
FROM employees E
WHERE exists
(
SELECT 1
FROM employees E1
WHERE E.job_id  < E1.job_id )

Answer: A. Operator EXISTS digunakan untuk memeriksa dan mencocokkan catatan antara kueri. Ini mengembalikan nilai BOOLEAN. Subkueri terkait mereferensikan kolom di kueri luar dan mengeksekusi subkueri sekali untuk setiap baris di kueri luar; dan operator EXISTS digunakan untuk menguji apakah hubungan atau tautan ada. Subkueri tidak berkorelasi mengeksekusi subkueri terlebih dahulu dan meneruskan nilai ke kueri luar.

74.You need to find the job which has no employees in it. Which of the following queries will be correct? (Choose the most appropriate answer)

  1. SELECT employee_id, Job_id 
FROM employees E
WHERE exists
(
SELECT *
FROM employees E1
WHERE E.job_id  = E1.job_id )
  2. SELECT employee_id, Job_id 
FROM employees E
WHERE not exists
(
SELECT 1
FROM employees E1
WHERE E.job_id  = E1.job_id )
  3. SELECT employee_id, Job_id 
FROM employees E
WHERE not exists
(
SELECT *
FROM employees E1
WHERE E.job_id  = E1.job_id )
  4. SELECT employee_id, Job_id 
FROM employees E
WHERE exists
(
SELECT 1
FROM employees E1
WHERE E.job_id  < E1.job_id )

Answer: B. NOT EXISTS adalah operator negasi untuk EXISTS.

75.You need to find the 3rd maximum salary from the EMPLOYEES table. Which of the following queries will give you the required results? (Choose the most appropriate answer)

  1. SELECT * 
FROM employees E
WHERE salary = (SELECT count(distinct salary ) 
		FROM employees 
		WHERE e.salary = salary 
		);
  2. SELECT * 
FROM employees E
WHERE 1 = (SELECT count(distinct salary ) 
		FROM employees 
		WHERE e.salary < salary 
		);
  3. SELECT * 
FROM employees E
WHERE 2 = (SELECT count(distinct salary ) 
		FROM employees 
		WHERE e.salary >salary 
		);
  4. SELECT * 
FROM employees E
WHERE 3 = (SELECT count(distinct salary ) 
		FROM employees 
		WHERE e.salary <= salary 
		);

Answer: D.

76. You need to find the maximum salary by using the user input for getting the value of N. Which of the following queries will give you the required results? (Choose the most appropriate answer)

  1. SELECT salary FROM 
(
	SELECT rowid as user_sal 
	FROM (SELECT distinct salary  from employees ORDER BY salary  desc)
) 
WHERE user_sal=&N ;
  2. SELECT salary FROM 
(	
	SELECT rownum as user_sal
	FROM (SELECT distinct salary  FROM employees   		GROUP BY salary )
) 
WHERE user_sal <= &N ;
  3. SELECT salary FROM 
(
	SELECT rownum as user_sal, salary 	FROM (SELECT distinct salary  FROM employees 		 ORDER BY salary  desc)
) 
WHERE user_sal=&N ;
  4. SELECT salary FROM 
(
	SELECT max(rownum) as user_sal, salary 	FROM (SELECT distinct salary  FROM employees 		ORDER BY salary  desc)
) 
WHERE user_sal=&N ;

Answer: C. ROWNUM adalah kolom semu yang digunakan untuk mencari hasil urutan ke-n.

77.What will happen if a value is provided to the &N variable in the above query (option C in question 76) does not match with any row? (Choose the best answer)

  1. Pernyataan itu akan memunculkan kesalahan ORA
  2. Pernyataan tersebut akan mengembalikan semua baris dalam tabel
  3. Pernyataan tersebut akan mengembalikan NULL sebagai hasil keluaran.
  4. Pernyataan tersebut tidak akan mengembalikan baris pada hasil.

Answer: D.

78.What is the maximum level up to which Sub-queries can be nested?

  1. 255
  2. 100
  3. 2
  4. 16

Answer: A.

79.What is true about the EXISTS operator in SQL queries with respect to sub-queries?

  1. Kolom yang dipilih di sub-kueri penting
  2. Kueri dalam harus mengembalikan baris, hasil apa pun adalah yang penting, bukan yang DIPILIH
  3. Baik A dan B
  4. Baik A maupun B

Answer: B.

80.What is true about the ANY operator used for sub-queries?

  1. Mengembalikan baris yang cocok dengan semua nilai dalam daftar / sub-kueri
  2. Mengembalikan baris yang cocok dengan 5 nilai pertama dalam daftar / sub-kueri
  3. Mengembalikan baris yang cocok dengan nilai apa pun dalam daftar / sub-kueri
  4. Mengembalikan nilai 0 ketika semua baris cocok dalam daftar / sub-kueri

Answer: C.

81.What is true about the ALL operator used for sub-queries? (Choose the most appropriate answer.)

  1. Mengembalikan baris yang cocok dengan semua nilai dalam daftar / sub-kueri
  2. Mengembalikan baris yang hanya cocok dengan beberapa nilai dalam daftar / sub-kueri
  3. Mengembalikan baris hanya jika semua nilai cocok dalam daftar / sub-kueri
  4. Semua yang di atas

Answer: C. '> ALL' Lebih dari nilai tertinggi yang dikembalikan oleh subkueri. '<ALL' Kurang dari nilai terendah yang dikembalikan oleh subkueri. '<ANY' Kurang dari nilai tertinggi yang dikembalikan oleh subkueri. '> ANY' Lebih dari nilai terendah yang dikembalikan oleh subkueri. '= ANY' Sama dengan nilai apa pun yang dikembalikan oleh subkueri (sama seperti IN). Baris '[NOT] EXISTS' harus cocok dengan nilai di subkueri.

82.What is true about using sub-queries in INSERT statements in Oracle?

  1. Mereka dapat digunakan dalam klausa INSERT tanpa batasan apa pun
  2. Mereka dapat digunakan dalam klausa INSERT hanya untuk nilai Numerik
  3. Daftar SELECT dari sub-query harus sama dengan daftar kolom dari pernyataan INSERT.
  4. Bukan dari salah satu di atas

Answer: C.

Examine the table structures as given below and answer the questions 83 to 86 that follow.

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
SQL> DESC departments
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 DEPARTMENT_ID		 NOT NULL NUMBER(4)
 DEPARTMENT_NAME	 NOT NULL VARCHAR2(30)
 MANAGER_ID			  NUMBER(6)
 LOCATION_ID			  NUMBER(4)

83.You need to find the details of all employees who were hired for the job ID 'SA_REP' in the month of June, 2013. Which of the following queries will give the required results? (Consider the table structure as given)

  1. SELECT first_name 
FROM employees 
WHERE employee_id  =
(	SELECT employee_id 
	FROM employees 
	WHERE to_char(hiredate, 'MM/YYYY')= '02/1981'
	AND job_id  = 'SA_REP'
);
  2. SELECT first_name 
FROM employees 
WHERE employee_id  = ANY
(	SELECT employee_id 
	FROM employees 
	WHERE to_char(hiredate, 'MM/YYYY')= '02/1981'
	AND job_id  = 'SA_REP'
);
  3. SELECT first_name 
FROM employees 
WHERE employee_id  ANY
(	SELECT employee_id 
	FROM employees 
	WHERE to_char(hiredate, 'MM/YYYY')= '02/1981'
	AND job_id  = 'SA_REP'
);
  4. SELECT first_name 
FROM employees 
WHERE employee_id  exists
(	SELECT employee_id 
	FROM employees 
	WHERE to_char(hiredate, 'MM/YYYY')= '02/1981'
	AND job_id  = 'SA_REP'
);

Answer: B.

84.Which of the following statements are equivalent? 

  1. SELECT employee_id , salary
FROM employees 
WHERE salary < ALL (SELECT salary FROM employees WHERE department_id=100);
  2. SELECT employee_id , salary
FROM employees WHERE salary < (SELECT min(salary) FROM employees WHERE department_id=100);
  3. SELECT employee_id 
FROM employees 
WHERE salary not >= ANY (SELECT salary FROM employees WHERE department_id=100);
  4. Bukan dari salah satu di atas

Answer: A, B.

85.Consider the following two queries:

Query 1: 
SELECT first_name 
FROM employees e join departments d
ON e.department_id  = d.department_id
WHERE department_name='ACCOUNTS';
Query 2: 
SELECT first_name 
FROM employees  e 
WHERE department_id  = ANY (SELECT department_id 		FROM departments d 
		WHERE department_name='ACCOUNTS');

Apa yang dapat dikatakan tentang kedua pernyataan tersebut?

  1. Kedua kueri tersebut harus menghasilkan hasil yang sama.
  2. Kedua kueri tersebut akan memunculkan kesalahan.
  3. Jika ada dua departemen dengan nama yang sama, kedua kueri tersebut akan gagal.
  4. Kedua kueri tersebut akan berjalan dengan sukses meskipun ada lebih dari satu departemen bernama 'ACCOUNTS'.

Answer: A, D.

86.You need to display all the employees who have the highest salary in a department 100. You fire a query as below.

SELECT E.first_name, E.last_name , E.salary
FROM employees E
WHERE E.salary > ALL (SELECT E1.salary
      FROM employees E1
      WHERE E.department_id  =E1.department_id 
      AND E.department_id  = 100);

Apa hasil dari pertanyaan di atas?

  1. Itu berhasil dijalankan dan memberikan hasil yang diperlukan
  2. Ini berhasil dijalankan tetapi tidak memberikan output yang diperlukan
  3. Ini melempar kesalahan ORA saat eksekusi
  4. Ini dijalankan dengan sukses dan memberikan hasil yang dibutuhkan ketika> ALL diganti dengan> = ALL

Answer: B, D. > ALL tidak akan memberikan hasil yang diminta karena mungkin ada dua karyawan dengan gaji yang sama dan yang merupakan penerima tertinggi di departemen 100

Consider table structures as shown in the exhibit and answer the questions 87 to 89 that follow:

87.You need to fetch the first names (in a reverse alphabetical order) of all the employees in the department ID = 100 and who have the maximum salary in the JOB ID = 'SA_REP'. Which of the following queries will give the required output? (Choose the most appropriate output)

  1. SELECT E.first_name, job_id , salary
FROM employees E
WHERE salary =
		(SELECT max(salary)
		FROM employees E1
		WHERE E1.department_id  = 100
		GROUP BY job_id )
AND job_id  = 'SA_REP'
ORDER BY first_name;
  2. SELECT E.first_name, job_id , salary
FROM employees E
WHERE salary in
		(SELECT max(salary)
		FROM employees E1
		where E1.department_id  = 100)
ORDER BY first_name;
  3. SELECT E.first_name, job_id , salary
FROM employees E
WHERE salary IN
		(SELECT max(salary)
		FROM employees E1
		where job_id  = 'SA_REP'
		GROUP BY job_id )
AND WHERE E.department_id  = 100
ORDER BY first_name desc;
  4. SELECT E.first_name, job_id , salary
FROM employees E
WHERE salary IN
		(SELECT max(salary)
		FROM employees E1
		WHERE E1.department_id  = 100
		GROUP BY job_id )
ORDER BY first_name ;

Answer: C.

88.In the queries given above (option C is the correct answer), you need to display all the employees with the JOB ID 'SA_REP' who have the maximum salary in the department 100. Which of the following queries will give the required output? 

  1. SELECT E.first_name, job_id , salary
FROM employees E
WHERE salary IN
		(SELECT max(salary)
		FROM employees E1
		WHERE E1.department_id  = 100
		GROUP BY job_id )
AND job_id  = 'SA_REP'
ORDER BY first_name;
  2. SELECT E.first_name, job_id , salary
FROM employees E
WHERE salary in
		(SELECT max(salary)
		FROM employees E1
		WHERE E1.department_id  = 100)
ORDER BY first_name;
  3. SELECT E.first_name, job_id , salary
FROM employees E
WHERE salary in
		(SELECT max(salary)
		FROM employees E1
		WHERE job_id  = 'SA_REP'
		GROUP BY job_id )
And WHERE E.department_id  = 100
ORDER BY first_name desc;
  4. SELECT E.first_name, job_id , salary
FROM employees E
WHERE salary in
		(SELECT max(salary)
		FROM employees E1
		WHERE E1.department_id  = 100
		GROUP BY job_id )
ORDER BY first_name ;

Answer: A.

89.Select the query which will give you the maximum salary and maximum comm percentage. The query should also give the maximum comm percentage paid if the highest salaried employee gets the maximum comm percentage.

  1. SELECT employee_id, max(salary), max(commission_pct )
FROM employees E
GROUP BY salary, commission_pct ;
  2. SELECT employee_id, max(salary), max(commission_pct )
FROM employees E
GROUP BY salary;
  3. SELECT employee_id, max(salary)
FROM employees E
GROUP BY salary, commission_pct 
HAVING max(commission_pct ) = 100;
  4. SELECT employee_id,
(SELECT max(salary) FROM employees) * (SELECT max(commission_pct ) FROM employees)
FROM DUAL;

Answer: D. Subkueri baris tunggal juga bisa bertumpuk di klausa SELECT kueri luar. Dalam kasus ini, nilai yang dikembalikan subkueri tersedia untuk setiap baris keluaran yang dihasilkan kueri luar. Biasanya, teknik ini digunakan untuk melakukan penghitungan dengan nilai yang dihasilkan dari subkueri.

90.What is true about the sub-queries used in the SELECT clause of an SQL statement?

  1. Sub-kueri ini sama di semua aspek seperti yang digunakan di klausa FROM atau WHERE
  2. Sub-kueri ini harus berupa sub-kueri baris tunggal
  3. Kita bisa menggunakan operator multi baris saat menulis sub-query seperti itu
  4. Bukan dari salah satu di atas

Answer: B.

91.What will be the outcome of the following query? (Consider the table structure as given)

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
SELECT sysdate,
(SELECT max(salary) FROM employees GROUP BY department_id )
FROM DUAL;
  1. Ini memberikan tanggal sistem dan gaji maksimum untuk setiap departemen
  2. Ini memberikan gaji maksimum untuk semua departemen
  3. Ini melempar kesalahan ORA
  4. Ini berhasil dijalankan dengan 0 baris

Answer: C. Sub-kueri multi baris tidak dapat digunakan dalam klausa SELECT dari pernyataan SQL. Hanya subkueri baris tunggal yang bisa disarangkan di klausa SELECT kueri luar.

Examine the given table structure. Consider the following query and answer the questions 92 to 95 that follow: 

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
SELECT salary
FROM employees
WHERE salary > ALL (10, 20, 30);

92.Which of the following queries are equivalent to the above query?

  1. SELECT salary
FROM employees
WHERE salary >10 or salary > 20 and salary >30;
  2. SELECT salary
FROM employees
WHERE salary <10 and salary < 20 and salary <30;
  3. SELECT salary
FROM employees
WHERE salary >10 and salary > 20 and salary >30;
  4. SELECT salary
FROM employees
WHERE salary >10 and salary > 20 or salary < 30;

Answer: C. Pertanyaannya menampilkan klausa ALL dengan cara yang disederhanakan jika diikuti oleh daftar.

93. If in the above query the list (10,20,30) is replaced by a sub-query, which of the following queries will give the required output for the department number 100?

  1. SELECT E.salary
FROM employees E
WHERE E.salary > (SELECT  E1.salary
FROM employees E1
WHERE E1.department_id  = 100);
  2. SELECT E.salary
FROM employees E
WHERE E.salary >ALL (SELECT  E1.salary
FROM employees E1
WHERE E1.department_id  = 100);
  3. SELECT E.salary
FROM employees E
WHERE E.salary = (SELECT  E1.salary
FROM employees E1
WHERE E1.department_id  = 100);
  4. SELECT E.salary
FROM employees E
WHERE E.salary >= (SELECT  E1.salary
FROM employees E1
WHERE E1.department_id  = 100);

Answer: B. Pertanyaan menunjukkan klausa ALL dengan cara yang disederhanakan ketika diikuti oleh sub-query

94.With respect to the question 14 above, what among the following will be an equivalent query if ALL has to be replaced with ANY?

  1. SELECT E.salary
FROM employees E
WHERE NOT EXISTS (E.salary =ANY (SELECT  E1.salary
FROM employees E1
WHERE E1.department_id  = 100);
  2. SELECT E.salary
FROM employees E
WHERE E.salary >ANY (SELECT  E1.salary
FROM employees E1
WHERE E1.department_id  = 100);
  3. SELECT E.salary
FROM employees E
WHERE E.salary =ANY (SELECT  E1.salary
FROM employees E1
WHERE E1.department_id  = 100);
  4. SELECT E.salary
FROM employees E
WHERE NOT ( E.salary <= ANY (SELECT  E1.salary
FROM employees E1
WHERE E1.department_id  = 100));

Answer: D. Operator NOT digunakan saat menggunakan '<= ANY' digunakan untuk negasi hasil yang dikembalikan oleh sub-query

95.With respect to the question 94, if the operator ANY is not to be used, which of the following queries will be correct?

  1. SELECT E.salary
FROM employees E
WHERE NOT EXISTS (E.salary = ANY (SELECT  E1.salary
FROM employees E1
WHERE E1.department_id  = 100);
  2. SELECT E.salary
FROM employees E
WHERE NOT EXISTS (SELECT  E1.salary
FROM employees E1
WHERE E1.department_id  = 100
And E.salary <= E1.salary);
  3. Baik A atau B
  4. Bukan dari salah satu di atas

Answer: B. Subkueri terkait mereferensikan kolom di kueri luar dan mengeksekusi subkueri sekali untuk setiap baris di kueri luar; dan operator EXISTS digunakan untuk menguji apakah hubungan atau tautan ada. Subkueri tidak berkorelasi mengeksekusi subkueri terlebih dahulu dan meneruskan nilai ke kueri luar.

Examine the given table structures. Consider the following query and answer the questions 96 to 98 that follow: 

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
SELECT salary
FROM employees
WHERE salary > ANY (10, 20, 30);

96. Which of the following queries are equivalent to the above query?

  1. SELECT salary
FROM employees
WHERE salary >10 or salary > 20 and or >30;
  2. SELECT salary
FROM employees
WHERE salary <10 and salary < 20 and salary <30;
  3. SELECT salary
FROM employees
WHERE salary >10 and salary > 20 or salary >30;
  4. SELECT salary
FROM employees
WHERE salary >10 and salary > 20 or salary < 30;

Answer: A. Pertanyaan menunjukkan klausa ANY dengan cara yang disederhanakan jika diikuti oleh daftar.

97. In the above query, if the list (10, 20, 30) is replaced by a sub-query, which of the following queries will give the required output for the department number 100?

  1. SELECT E.salary
FROM employees E
WHERE E.salary > (SELECT  E1.salary
FROM employees E1
WHERE E1.department_id  = 100);
  2. SELECT E.salary
FROM employees E
WHERE E.salary >ANY (SELECT  E1.salary
FROM employees E1
WHERE E1.department_id  = 100);
  3. SELECT E.salary
FROM employees E
WHERE E.salary = (SELECT  E1.salary
FROM employees E1
WHERE E1.department_id  = 100);
  4. SELECT E.salary
FROM employees E
WHERE E.salary >= (SELECT  E1.salary
FROM employees E1
WHERE E1.department_id  = 100);

Answer: B. Pertanyaan tersebut menunjukkan klausa ANY dengan cara yang disederhanakan jika diikuti oleh sub-kueri

98.With respect to the question 97 above, what among the following will be an equivalent query if ANY is removed? 

  1. SELECT E.salary
FROM employees E
WHERE NOT EXISTS (E.salary =ANY (SELECT  E1.salary
FROM employees E1
WHERE E1.department_id  = 100);
  2. SELECT E.salary
FROM employees E
WHERE EXISTS (SELECT  E1.salary
FROM employees E1
WHERE E1.department_id  = 100
And E.salary >E1.salary);
  3. SELECT E.salary
FROM employees E
WHERE EXISTS (SELECT  E1.salary
FROM employees E1
WHERE E1.department_id  = 100
);
  4. SELECT E.salary
FROM employees E
WHERE IN (SELECT  E1.salary
FROM employees E1
WHERE E1.department_id  = 100);

Answer: B. Operator EXISTS dapat menggantikan operator ANY. Subkueri terkait mereferensikan kolom di kueri luar dan mengeksekusi subkueri sekali untuk setiap baris di kueri luar; dan operator EXISTS digunakan untuk menguji apakah hubungan atau tautan ada.

99.Examine the given table structure. How many rows will get generated if the sub-query mentioned returns 0 rows?

SQL> DESC employees
 Name			 Null?	  Type
 ----------------------- -------- ----------------
 EMPLOYEE_ID		 NOT NULL NUMBER(6)
 FIRST_NAME			  VARCHAR2(20)
 LAST_NAME		 NOT NULL VARCHAR2(25)
 EMAIL			 NOT NULL VARCHAR2(25)
 PHONE_NUMBER			  VARCHAR2(20)
 HIRE_DATE		 NOT NULL DATE
 JOB_ID 		 NOT NULL VARCHAR2(10)
 SALARY 			  NUMBER(8,2)
 COMMISSION_PCT 		  NUMBER(2,2)
 MANAGER_ID			  NUMBER(6)
 DEPARTMENT_ID			  NUMBER(4)
SELECT E.salary
FROM employees E
WHERE E.salary > ANY ( select E1.salary FROM employees E1 where E1.department_id  = 100);
  1. 1 baris
  2. Tidak ada baris
  3. Baik A atau B
  4. Bukan dari salah satu di atas

Answer: B. Jika sub-kueri mengembalikan nol baris, kondisi '> ANY' mengevaluasi ke FALSE, maka "Tidak ada baris" dikembalikan.

100. A subquery must be placed in the outer query's HAVING clause if:

  1. Kueri dalam perlu merujuk nilai yang dikembalikan ke kueri luar.
  2. Nilai yang dikembalikan oleh kueri dalam akan dibandingkan dengan data yang dikelompokkan di kueri luar.
  3. Subkueri mengembalikan lebih dari satu nilai ke kueri luar.
  4. Bukan dari salah satu di atas. Subkueri tidak bisa digunakan di klausa HAVING kueri luar.

Answer: B. Klausa HAVING digunakan ketika hasil grup dari kueri perlu dibatasi berdasarkan beberapa kondisi. Jika hasil subkueri harus dibandingkan dengan fungsi grup, Anda harus menumpuk kueri dalam di klausa HAVING kueri luar.

ja/tutorial
es/tutorial
de/tutorial
fr/tutorial
th/tutorial
pt/tutorial
ru/tutorial
vi/tutorial
it/tutorial
ko/tutorial
tr/tutorial
id/tutorial
pl/tutorial
hi/tutorial
japost
espost
depost
frpost
thpost
ptpost
rupost
vipost
itpost
kopost
trpost
idpost
plpost
hipost

© Copyright 2021 - 2024 | All Rights Reserved