Aguezz

Tugas Data Warehouse

May 19th, 2022 (edited)
289
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Jumlah Semua Data
  2. select
  3.     (select count(*) from stores) as toko,
  4.     (select count(*) from clothes) as baju,
  5.     (select count(*) from employees) as karyawan,
  6.     (select count(*) from customers) as pembeli,
  7.     (select count(*) from suppliers) as supplier,
  8.     (select count(*) from transaction_purchases) as transaksi_pembelian,
  9.     (select count(*) from transaction_sales) as transaksi_penjualan,
  10.     (select count(*) from transaction_rentals) as transaksi_penyewaan
  11.  
  12. -- Laporan Bulanan Transaksi Pembelian
  13. select
  14.     MONTH(transaction_date) as month,
  15.     CONVERT(YEAR(transaction_date), CHAR) as year,
  16.     SUM(quantity) as total_quantity,
  17.     SUM(quantity * unit_price) as total
  18. from transaction_purchases
  19. group by MONTH(transaction_date), YEAR(transaction_date)
  20. order by year, month
  21.  
  22. -- Laporan Kuartal Transaksi Pembelian
  23. select
  24.     QUARTER(transaction_date) as quarter,
  25.     CONVERT(YEAR(transaction_date), CHAR) as year,
  26.     SUM(quantity) as total_quantity,
  27.     SUM(quantity * unit_price) as total
  28. from transaction_purchases
  29. group by QUARTER(transaction_date), YEAR(transaction_date)
  30. order by year, quarter
  31.  
  32. -- Laporan Tahunan Transaksi Pembelian
  33. select
  34.     CONVERT(YEAR(transaction_date), CHAR) as year,
  35.     SUM(quantity) as total_quantity,
  36.     SUM(quantity * unit_price) as total
  37. from transaction_purchases
  38. group by YEAR(transaction_date)
  39. order by year
  40.  
  41. -- Laporan Bulanan Transaksi Penjualan
  42. select
  43.     MONTH(transaction_date) as month,
  44.     CONVERT(YEAR(transaction_date), CHAR) as year,
  45.     SUM(quantity) as total_quantity,
  46.     SUM(quantity * unit_price) as total
  47. from transaction_sales
  48. group by MONTH(transaction_date), YEAR(transaction_date)
  49. order by year, month
  50.  
  51. -- Laporan Kuartal Transaksi Penjualan
  52. select
  53.     QUARTER(transaction_date) as quarter,
  54.     CONVERT(YEAR(transaction_date), CHAR) as year,
  55.     SUM(quantity) as total_quantity,
  56.     SUM(quantity * unit_price) as total
  57. from transaction_sales
  58. group by QUARTER(transaction_date), YEAR(transaction_date)
  59. order by year, quarter
  60.  
  61. -- Laporan Tahunan Transaksi Penjualan
  62. select
  63.     CONVERT(YEAR(transaction_date), CHAR) as year,
  64.     SUM(quantity) as total_quantity,
  65.     SUM(quantity * unit_price) as total
  66. from transaction_sales
  67. group by YEAR(transaction_date)
  68. order by year
  69.  
  70. -- Laporan Bulanan Transaksi Penyewaan
  71. select
  72.     MONTH(transaction_date) as month,
  73.     CONVERT(YEAR(transaction_date), CHAR) as year,
  74.     SUM(quantity) as total_quantity,
  75.     SUM(quantity * unit_price) as total
  76. from transaction_rentals
  77. group by MONTH(transaction_date), YEAR(transaction_date)
  78. order by year, month
  79.  
  80. -- Laporan Kuartal Transaksi Penyewaan
  81. select
  82.     QUARTER(transaction_date) as quarter,
  83.     CONVERT(YEAR(transaction_date), CHAR) as year,
  84.     SUM(quantity) as total_quantity,
  85.     SUM(quantity * unit_price) as total
  86. from transaction_rentals
  87. group by QUARTER(transaction_date), YEAR(transaction_date)
  88. order by year, quarter
  89.  
  90. -- Laporan Tahunan Transaksi Penyewaan
  91. select
  92.     CONVERT(YEAR(transaction_date), CHAR) as year,
  93.     SUM(quantity) as total_quantity,
  94.     SUM(quantity * unit_price) as total
  95. from transaction_rentals
  96. group by YEAR(transaction_date)
  97. order by year
  98.  
  99. -- Laporan Tahunan Transaksi Penjualan Berdasarkan Toko
  100. select
  101.     s.name as store,
  102.     CONVERT(YEAR(ts.transaction_date), CHAR) as year,
  103.     SUM(ts.quantity) as total_quantity,
  104.     SUM(ts.quantity * ts.unit_price) as total
  105. from transaction_sales ts
  106. right join clothes c on ts.clothes_id = c.id
  107. inner join stores s on c.store_id = s.id
  108. group by s.id, YEAR(ts.transaction_date)
  109. order by store, year
  110.  
  111. -- Laporan Tahunan Transaksi Penjualan Berdasarkan Toko dan Baju
  112. select
  113.     s.name as store,
  114.     c.name as clothes,
  115.     CONVERT(YEAR(ts.transaction_date), CHAR) as year,
  116.     SUM(ts.quantity) as total_quantity,
  117.     SUM(ts.quantity * ts.unit_price) as total
  118. from transaction_sales ts
  119. right join clothes c on ts.clothes_id = c.id
  120. inner join stores s on c.store_id = s.id
  121. group by ts.clothes_id, YEAR(ts.transaction_date)
  122. order by store, year
  123.  
  124. -- Laporan Tahunan Transaksi Pembelian Oleh Customer
  125. select
  126.     c.name as customer,
  127.     CONVERT(YEAR(ts.transaction_date), CHAR) as year,
  128.     SUM(ts.quantity) as total_quantity,
  129.     SUM(ts.quantity * ts.unit_price) as total
  130. from transaction_sales ts
  131. right join customers c on c.id  = ts.customer_id
  132. group by ts.customer_id , YEAR(ts.transaction_date)
  133. order by customer, year
  134.  
  135. -- Laporan Total Transaksi Penjualan Tertinggi Oleh Toko
  136. select
  137.     s.name as store,
  138.     SUM(ts.quantity) as total_quantity,
  139.     SUM(ts.quantity * ts.unit_price) as total
  140. from transaction_sales ts
  141. right join clothes c on c.id  = ts.clothes_id
  142. inner join stores s on s.id = c.store_id
  143. group by c.store_id
  144. order by total desc
  145.  
  146. -- Laporan Total Transaksi Pembelian Tertinggi Oleh Customer
  147. select
  148.     c.name as customer,
  149.     SUM(ts.quantity) as total_quantity,
  150.     SUM(ts.quantity * ts.unit_price) as total
  151. from transaction_sales ts
  152. right join customers c on c.id  = ts.customer_id
  153. group by ts.customer_id
  154. order by total desc
  155.  
  156. -- Laporan Transaksi Pembelian Berdasarkan Jenis Kelamin Customer
  157. select
  158.     c.gender,
  159.     SUM(ts.quantity) as total_quantity,
  160.     SUM(ts.quantity * ts.unit_price) as total
  161. from transaction_sales ts
  162. right join customers c on c.id  = ts.customer_id
  163. group by c.gender
  164. order by total desc
  165.  
RAW Paste Data Copied