Tampilkan daftar pelanggan yang telah melakukan pemesanan dan total jumlah pesanan:

SELECT c.customer_name, COUNT(o.order_id) AS total_orders

FROM Customers c

(a)   Orders o ON c.customer_id = o.customer_id

GROUP BY c.customer_name

ORDER BY total_orders DESC;

Tampilkan daftar produk yang memiliki harga lebih tinggi dibandingkan harga rata-rata semua produk.

SELECT product_name, price

FROM Products

WHERE price > ( (a)   (price) FROM Products);

Hitung Gunakan CTE untuk menghitung total penjualan per kategori.

(a)   CategorySales AS (

SELECT category_id, SUM(price * quantity) AS total_sales

FROM Products p JOIN Orders o ON p.product_id = o.product_id

GROUP BY category_id

)

SELECT * FROM CategorySales;

Tampilkan daftar karyawan dengan gaji dan peringkat gaji mereka dalam perusahaan, tanpa ada peringkat yang terlewat.

SELECT employee_name, salary,

(a)   () OVER (ORDER BY salary DESC) AS salary_rank

FROM Employees;

Hitung total pendapatan per bulan dari tabel transaksi ( atas dan bawah jawaban sama, 1 word saja)

SELECT ______ (transaction_date) AS month,

SUM(amount) AS total_revenue

FROM Transactions

GROUP BY ______ (transaction_date)

ORDER BY month;

(a)  

Cari karyawan dengan gaji tertinggi di setiap departemen beserta nama departemennya. Tabel yang digunakan: employees(emp_no, first_name, last_name) dept_emp(emp_no, dept_no) departments(dept_no, dept_name) salaries(emp_no, salary, from_date, to_date)

SELECT e.first_name, e.last_name, d.dept_name, s.salary

FROM (SELECT emp_no, salary,

RANK() OVER (PARTITION BY ​ (a)   ORDER BY salary DESC) AS rnk

FROM salaries WHERE to_date = '9999-01-01') s

JOIN employees e ON e.emp_no = ​ (b)  

JOIN dept_emp de ON e.emp_no = ​ (c)  

JOIN departments d ON de.dept_no = ​ (d)  

WHERE rnk = ​ (e)   ;

Cari pelanggan yang telah menyewa lebih dari 30 kali. Tabel yang digunakan: rental(rental_id, customer_id, rental_date) customer(customer_id, first_name, last_name)

SELECT c.first_name, c.last_name, COUNT(r.​ (a)   ) AS total_rentals

FROM customer c

JOIN rental r ON c.customer_id = r.​ (b)  

​ (c)   BY ​ (d)   , c.first_name, c.last_name

HAVING total_rentals > ​ (e)   ;

​

Cari film yang menghasilkan pendapatan tertinggi berdasarkan jumlah penyewaan dikali harga sewa.

Tabel yang digunakan:

rental(rental_id, inventory_id)

inventory(inventory_id, film_id)

film(film_id, title, rental_rate)

SELECT f.title, SUM(f.​ (a)   * COUNT(r.rental_id)) AS total_revenue

FROM rental r

JOIN inventory i ON r.inventory_id = i.inventory_id

JOIN film f ON i.film_id = f.film_id

​ (b)   f.title

ORDER BY total_revenue DESC

​ (c)   ;

Cari negara dengan populasi terbesar di setiap benua. Tabel yang digunakan: country(name, continent, population)

SELECT name, continent, population

FROM (SELECT name, continent, population,

​ (a)   ​ (b)   (​ (c)   BY ​ ​ (d)   ORDER BY population DESC) AS rnk

FROM country) ranked

WHERE rnk = ​ (e)   ;