Resume I
SQL 2
- Alias Table digunaakan saat menggunakan Join Table.
Example:
Select
c.customer_id, o.order_id, p.product_name
From customer c, curr_order o, product
p
Where c.customer_id=o.customer_id
And o.product_id=p.product_id
Order by o.order_ammount ;
- Round adalah statement untuk pembulatan decimal ke atas
Example :
Select
first_name ||,|| last name “student name”,id “id”, enroll_date ,round (sysdate)-
round (enroll_date)”seniority”
From student ;
- - Distinct adalah statement untuk memilih nilai yang unik, menghilangkan duplikasi
Example :
Select
location_id, count (distinct event_type)
From event
Group by location_id ;
Select count (distinct dep_id)
From dept ;
- - Subquery digunakan untuk mencari nilai yang tidak diketahui
Example :
Select supplier_id, avg (cost)
From product
Where list_price (select avg(list_price)
From
product)
Group by supplier_id
Order by avg (cost) desc;
Menampilkan data karyawan yang memiliki gaji dibawah rata-rata.
SELECT last_name, title, salary
FROM employee
WHERE salary < ( SELECT AVG(salary)
FROM employee );
FROM employee
WHERE salary < ( SELECT AVG(salary)
FROM employee );
Menampilkan data departemen yang memiliki rata-rata gaji diatas rata-rata gaji departemen 32.
SELECT dept_id, AVG (salary)
FROM employee
GROUP BY dept_id
HAVING AVG(salary) > ( SELECT AVG(salary)
FROM employee
WHERE dept_id = 32 );
FROM employee
GROUP BY dept_id
HAVING AVG(salary) > ( SELECT AVG(salary)
FROM employee
WHERE dept_id = 32 );