SQL/Fundamental SQL Using INNER JOIN and UNION.ipynb

{"nbformat":4,"nbformat_minor":0,"metadata":{"colab":{"name":"Fundamental SQL Using INNER JOIN and UNION.ipynb","private_outputs":true,"provenance":[],"authorship_tag":"ABX9TyMLUfMO0U/Qqk6F7jJJ+oP2"},"kernelspec":{"name":"python3","display_name":"Python 3"},"language_info":{"name":"python"}},"cells":[{"cell_type":"markdown","metadata":{"id":"3YXXdYJlgO48"},"source":["# [Tugas Praktek-1.1](https://academy.dqlab.id/main/livecode/244/406/2034?pr=0)"]},{"cell_type":"code","metadata":{"id":"YUIjrEFdgFMF"},"source":["SELECT * FROM ms_item_kategori; \n","SELECT * FROM ms_item_warna ;"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"rkcXjtElh0hI"},"source":["# [Menggabungkan Tabel dengan Key Columns](https://academy.dqlab.id/main/livecode/244/406/2038?pr=0)"]},{"cell_type":"code","metadata":{"id":"ORFoFk54h7eA"},"source":["SELECT * FROM ms_item_kategori, ms_item_warna\n","WHERE nama_barang = nama_item;"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"BDWdyBZnh8Mm"},"source":["# [Bagaimana jika urutan Tabel diubah?](https://academy.dqlab.id/main/livecode/244/406/2044?pr=0)"]},{"cell_type":"code","metadata":{"id":"MymhZPPbiFJP"},"source":["SELECT * FROM ms_item_warna, ms_item_kategori\n","WHERE nama_barang=nama_item;"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"rlbZXaqjiF57"},"source":["# [Menggunakan Prefix Nama Tabel](https://academy.dqlab.id/main/livecode/244/406/2045?pr=0)"]},{"cell_type":"code","metadata":{"id":"fORP_llQiOEr"},"source":["SELECT ms_item_kategori.*, ms_item_warna.*\n","FROM ms_item_warna, ms_item_kategori\n","WHERE nama_barang=nama_item;"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"1bGbZOkYiOeC"},"source":["# [Penggabungan Tanpa Kondisi](https://academy.dqlab.id/main/livecode/244/406/2046?pr=0)"]},{"cell_type":"code","metadata":{"id":"8x2LSNQOiX-h"},"source":["SELECT * FROM ms_item_kategori, ms_item_warna;"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"LBCt2qCsjYXj"},"source":["# [Tugas Praktek: Menggunakan INNER JOIN (1/3)](https://academy.dqlab.id/main/livecode/244/407/2051?pr=0)"]},{"cell_type":"code","metadata":{"id":"EcnnS8J-jeC1"},"source":["SELECT * FROM ms_item_warna\n","INNER JOIN ms_item_kategori\n","ON ms_item_warna.nama_barang = ms_item_kategori.nama_item;"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"txt65Cekjeqd"},"source":["# [tabel tr_penjualan dan tabel ms_produk](https://academy.dqlab.id/main/livecode/244/407/2052?pr=0)"]},{"cell_type":"code","metadata":{"id":"zWBdUtszjmIP"},"source":["SELECT * FROM tr_penjualan;\n","SELECT * FROM ms_produk;"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"mTovJYQjjmw4"},"source":["# [Tugas Praktek: Menggunakan INNER JOIN (2/3)](https://academy.dqlab.id/main/livecode/244/407/2054?pr=0)"]},{"cell_type":"code","metadata":{"id":"SF181TK0jutl"},"source":["SELECT * \n","FROM tr_penjualan\n","INNER JOIN ms_produk\n","ON tr_penjualan.kode_produk = ms_produk.kode_produk;"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"bz9kpBptjvdL"},"source":["# [Tugas Praktek: Menggunakan INNER JOIN (2/3)](https://academy.dqlab.id/main/livecode/244/407/2054?pr=0)"]},{"cell_type":"code","metadata":{"id":"1b2AC60yj3lg"},"source":["SELECT * \n","FROM tr_penjualan\n","INNER JOIN ms_produk\n","ON tr_penjualan.kode_produk = ms_produk.kode_produk;"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"vd6n8Jpuj4Qc"},"source":["# [Tugas Praktek: Menggunakan INNER JOIN (3/3)](https://academy.dqlab.id/main/livecode/244/407/2057?pr=0)"]},{"cell_type":"code","metadata":{"id":"ruFdqmuckADA"},"source":["SELECT tr_penjualan.kode_transaksi,\ttr_penjualan.kode_pelanggan,tr_penjualan.kode_produk,ms_produk.nama_produk,ms_produk.harga,tr_penjualan.qty,ms_produk.harga * tr_penjualan.qty AS total\n","FROM tr_penjualan\n","INNER JOIN ms_produk\n","ON tr_penjualan.kode_produk = ms_produk.kode_produk"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"JIva8RZ1kao2"},"source":["# [Tabel yang Akan Digabungkan](https://academy.dqlab.id/main/livecode/244/408/2061?pr=0)"]},{"cell_type":"code","metadata":{"id":"CZiveLjrkgv3"},"source":["SELECT * FROM tabel_a;\n","\n","SELECT * FROM tabel_b;"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"U7AwasjlkhWR"},"source":["# [Menggunakan UNION](https://academy.dqlab.id/main/livecode/244/408/2062?pr=0)"]},{"cell_type":"code","metadata":{"id":"VysVWpNvkoDF"},"source":["SELECT * FROM tabel_A\n","UNION\n","SELECT * FROM tabel_B;"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"z3PBvmx2kojR"},"source":["# [Menggunakan UNION dengan Klausa WHERE](https://academy.dqlab.id/main/livecode/244/408/2063?pr=0)"]},{"cell_type":"code","metadata":{"id":"LGNz1sz0lTwR"},"source":["SELECT * FROM tabel_A\n","WHERE kode_pelanggan='dqlabcust03'\n","UNION\n","SELECT * FROM tabel_B\n","WHERE kode_pelanggan='dqlabcust03';"],"execution_count":null,"outputs":[]},{"cell_type":"markdown","metadata":{"id":"CdzdYRYKlUTN"},"source":["# [Menggunakan UNION dan Menyelaraskan Kolom-Kolomnya](https://academy.dqlab.id/main/livecode/244/408/2065?pr=0)"]},{"cell_type":"code","metadata":{"id":"-65CicrRleaw"},"source":["SELECT CustomerName, ContactName, City, PostalCode \n","FROM Customers \n","UNION \n","SELECT SupplierName, ContactName, City, PostalCode \n","FROM Suppliers;\t"],"execution_count":null,"outputs":[]}]}