Query, Sql Server - rextester

Language: Layout:

-----------------------------------------------------------------------------------------------
--- Gue buat table Dummy biar lu lebih ngerti gimana hasil dari soal-soal yang dari sale stock.
--- Buat Running Query-nya, pencet "RUN" yang ada di bawah.
-----------------------------------------------------------------------------------------------
/*
Table 1 - member
Expected Output:
    member_id   email                      phone_number      name                   address_city
    1           kuncoro.barot@btpn.com     081218543812      Kuncoro Wicaksono      Jabodetabek
    2           anisayunijenius@gmail.com  081290206641      Anisa Yuni Primasari   Bandung
*/

create table [member]
    (member_id int, email varchar(55), phone_number varchar(55), name varchar(55), address_city varchar(55));
    insert into [member] values
    (1, 'kuncoro.barot@btpn.com', '081218543812', 'Kuncoro Wicaksono', 'Jabodetabek'),
    (2, 'anisayunijenius@gmail.com', '081290206641', 'Anisa Yuni Primasari', 'Bandung');

/*
Table 2 - order
Expected Output:
    order_id     member_id    coupon_code               total        grand_total  product_id   created
    1            1            Coupon Code #1            3            3            1            2017-01-24           
    2            1            Coupon Code #1            2            2            1            2017-11-24           
    3            1            Coupon Code #1            1            1            1            2017-11-24           
    4            1            Coupon Code #1            1            1            1            2017-03-24           
    5            2            Coupon Code #1            4            4            1            2017-11-24           
    6            2            Coupon Code #1            3            3            1            2017-11-24           
    7            2            Coupon Code #1            4            4            1            2017-11-24           
    8            2            Coupon Code #1            2            2            1            2017-11-24           
    9            2            Coupon Code #1            4            4            1            2017-11-24
*/
create table [order]
    (order_id int, member_id int, coupon_code varchar(55), total int, grand_total int, product_id int, created date);
    insert into [order] values
    (1, 1, 'Coupon Code #1', 3, 3, 1, '2017-01-24'),
    (2, 1, 'Coupon Code #1', 2, 2, 1, '2017-11-24'),
    (3, 1, 'Coupon Code #1', 1, 1, 1, '2017-11-24'),
    (4, 1, 'Coupon Code #1', 1, 1, 1, '2017-03-24'),
    (5, 2, 'Coupon Code #1', 4, 4, 1, '2017-11-24'),
    (6, 2, 'Coupon Code #1', 3, 3, 1, '2017-11-24'),
    (7, 2, 'Coupon Code #1', 4, 4, 1, '2017-11-24'),
    (8, 2, 'Coupon Code #1', 2, 2, 1, '2017-11-24'),
    (9, 2, 'Coupon Code #1', 4, 4, 1, '2017-11-24');

/*
Table 3 - shipping
Expected Output:
    shipping_id     order_id    address_city               
    1               1           Jabodetabek
    2               2           Bandung         
    3               3           Jabodetabek
    4               4           Sulawesi
*/
create table [shipping]
    (shipping_id int, order_id int, address_city varchar(55));
    insert into [shipping] values
    (1, 1, 'Jabodetabek'),
    (2, 2, 'Bandung'),
    (3, 3, 'Jabodetabek'),
    (4, 4, 'Sulawesi');

------------------------------------------------------------------
-- Nah, Sekarang Masuk ke Soal yang dikasih Sale Stock
------------------------------------------------------------------

-- SOAL 1 - Query data yang dikirimkan ke daerah Jabodetabek
    SELECT * FROM [shipping] WHERE ADDRESS_CITY = 'JABODETABEK'
    ----------------------------------------------------------------------------------------
    -- Ini maksudnya ngambil dari table shipping, yang kolom address_city-nya Jabodetabek. 
    ----------------------------------------------------------------------------------------
    
-- SOAL 2 -- Query data member dan jumlah order yang pernah dilakukan
    SELECT A.*, B.jumlah_order 
    FROM [member] A LEFT JOIN 
        (SELECT member_id, COUNT(1) as jumlah_order FROM [order] GROUP BY member_id) B 
    ON A.member_id = b.member_id
    ---------------------------------------------------------------------------------------------------
    -- A.* maksudnya ngambil semua kolom table member, karena doi di-define sebagai A ([MEMBER] A)
    -- B.Jumlah_oder itu didapat dari COUNT(1) as jumlah_order dari table order, artinya ngambil jumlah order yang didapat dari jumlah order per-member_id (GROUP BY member_id) 
    -- Join itu buat gabungin antara table member sama table order 
    -- Kalo "ON A.member_id = b.member_id" buat identifier dari dua table tersebut disamain by apa.
    ---------------------------------------------------------------------------------------------------
    
-- SOAL 3
    SELECT address_city, sum(jumlah_order) as jumlah_order FROM (
        SELECT A.*, B.jumlah_order 
        FROM [member] A LEFT JOIN 
            (SELECT member_id, COUNT(1) as jumlah_order FROM [order] GROUP BY member_id) B 
        ON A.member_id = b.member_id
    ) A GROUP BY address_city
-- SOAL 4
    SELECT address_city, sum(jumlah_order) as jumlah_order FROM (
        SELECT A.*, B.jumlah_order 
        FROM [member] A LEFT JOIN 
            (SELECT member_id, COUNT(1) as jumlah_order FROM [order] GROUP BY member_id) B 
        ON A.member_id = b.member_id
    ) A GROUP BY address_city ORDER BY jumlah_order DESC
-- SOAL 5
    SELECT A.*, C.address_city as Shipping_address_city
    FROM [member] A LEFT JOIN [order] B ON A.member_id = B.member_id
        LEFT JOIN [shipping] C ON B.order_id = C.order_id
     WHERE A.address_city <> C.address_city
-- SOAL 6
    SELECT A.*, B.jumlah_belanja, B.total_order, (B.total_order/B.jumlah_belanja) as rata2_belanja
    FROM [member] A LEFT JOIN 
        (SELECT member_id, COUNT(1) as jumlah_belanja, SUM(total) as total_order FROM [order] GROUP BY member_id) B 
    ON A.member_id = b.member_id
    WHERE jumlah_belanja >= 2
-- SOAL 7
    SELECT *
    FROM [member] WHERE member_id IN
        ( SELECT member_id FROM [order] WHERE MONTH(CREATED) IN ( 1 , 3 )
        AND member_id NOT IN ( SELECT member_id FROM [order] WHERE MONTH(CREATED) = 2 )
        GROUP BY member_id )

View schema
Execution time: 0,08 sec, rows selected: 13, rows affected: 15, absolute service time: 0,24 sec