Berikut contoh soal dari simple view :
1. Menurut anda, mengapa user memerlukan view? Jelaskan!
2. Buatlah 10 simple view dari tabel – tabel yang tersedia dalam skema HR. Beri nama view yang menggambarkan keadaan view tersebut.
3. Buatlah 5 contoh view yang bukan termasuk simple view dan beri penjelasan kenapa view tersebut bukan termasuk simple view.
Jawaban :
1. Karena, untuk mempermudah user dalam penulisan query karena pada view banyak query yang dijadikan dalam satu query yaitu view sehingga tidak perlu menulis
banyak query hanya perlu memanggil viewnya, untuk keamanan juga yatu dengan menyembunyikan beberapa kolom yang bersifat rahasia karena yang dipanggil hanya viewnya,
maupun dalam beberapa kasus bisa digunakan untuk mempercepat proses menampilkan data (terutama jika kita akan menjalankan query tersebut secara berulang) karena hanya dengan memanggil viewnya.
2. create view vemployees as select employee_id, first_name, last_name,email,phone_number_hire_date,job_id,salary from employees;
create view vjobs as select job_id, job_title,min_salary,max_salary from jobs;
create view vregions as select region_id, region_name from regions;
create view vlocation as select location_id,street_address,postal_code, city, state_province,country_id from locations;
create view vemployeesName as select employee_id, first_name,last_name from employees;
create view vdepartments as select department_id, department_name,manager_id, location_id from departments;
create view vjob_history as select employee_id, start_date, end_date, job_id,department_id from job_history;
create view vdepmanager as select department_id,manager_id from departments;
create view vemployeesjob as select employee_id,job_id from employees;
create view vlocacity as select location_id,city from locations;
3. create view vmanager as select distinct manager_id from departments; *Menggunakan perintah disctinct
create view vemployeejobs as select job_title,SUM(employees.job_id) as Jumlah from jobs,employees where jobs.job_id = employees.job_id group by job_title; *Terdapat perintah group by dan agregasi sum
create view vemployeesalary as select employee_id,first_name,last_name,SUM(salary) as Jumlah from employees group by employee_id,first_name,last_name having SUM(salary) > 3000; *Terdapat having dan fungsi > dan agregasi SUM
create view vemployees as select employee_id,max(salary) as Max from employees group by employee_id; *Terdapat Agregasi Max dan group by
create view vemplength as select employee_id,length(first_name) as First from employees ; *Terdapat agregasi length
EmoticonEmoticon