Data Analysis in SQL (PostgreSQL)

Photo by Glenn Carstens-Peters on Unsplash

Yapp kembali lagi setelah sekian lama tidak upload yaa hehehe, kali ini saya akan membagikan hasil dan soal Skill Assessment Data Analysis in SQL dari DataCamp online course yang telah saya selesaikan. langsung saja ke pemabahasan…

Task And Project Answer

1. The food table gives nutritional information about various items served in a restaurant. Return the item, energy and protein columns ordered by protein.

Skill : Aggregating and ordering data

database

SELECT item, energy, protein
FROM food
ORDER BY protein

• kita hanya hanya perlu mengambil data item, energy and protein menggunakan SELECT
• lalu kita urutkan berdasarkan protein menggunakan ORDER BY

output :

2. The favorite shows of a small group of subscribers is recorded in the favorite table. The titles of the shows were not entered consistently. Convert all the values in the title column to lower case.

Skill : Specific data types

SELECT user_id,
       lower(title) as lower
FROM favorite
LIMIT 5;

• pada perintah kita hanya menggubah kolom ‘title’ dengan huruf kecil menggunakan lower(title)

output :

3. Return the match_id and win_margin from the table match where the value for the column win_margin is greater than 100.

Skill : Understanding your data

SELECT match_id, win_margin 
FROM match
WHERE margin > 100
ORDER BY match_id

• kita hanya hanya perlu mengambil data match_id and win_margin menggunakan SELECT
• lalu filter win_margin lebih dari 100 menggunakan WHERE

output :

4. The wine table gives information about wines stocked in an online wine retailer. Calculate the average rating of wines by type, return the data ordered by the average rating, from lowest to highest.

Skill : Aggregating and ordering data

database

SELECT type, ROUND(AVG(rating), 2) as round
FROM wine
GROUP BY type
ORDER BY ROUND(AVG(rating), 2) asc

• kita perlu menghitung rata-rata ‘rating’ dengan AVG, lalu manfaat ROUND yaitu membulatkan hasil dengan 2 angka dibelakang koma
• selanjutnya urutkan hasil dari rata-rata rating dari bawah ke atas ORDER BY ASC dan jangan lupa untuk GROUP BY karena terhadap perhitungan pada SELECT

output :

5. Return the names of British, South African and Mexican artists contained in the artists table.

Skill : Understanding your data

SELECT name, nationality
FROM artists
WHERE nationality IN ('British', 'South African', 'Mexican')
ORDER BY name
LIMIT 10;

• kita hanya perlu mem-filter ‘nationality ’ pada WHERE menggunakan IN untuk memunculkan nama dari British, South African and Mexican artists pada tabel artists

output :

6. Using a subquery as a common expression, return all of the columns from the match table where win_margin is greater than 20 and the match_winner is equal to 1. Limit the results to 10 rows.

Skill : Creating subqueries

SELECT subquery FROM (
WITH subquery AS (
  SELECT * FROM match WHERE win_margin > 20
)

SELECT 	match_id,
	win_margin
FROM match
FROM subquery
WHERE match_winner = 1
ORDER BY match_id
LIMIT 10;

• manfaat penggunaan WITH ‘table_name’ AS yaitu sebagai pembuatan bentuk tabel baru yang nantinya dapat digunakan pada FROM
• lalu filter win_marginlebih dari 20 dan match_winner sama dengan1 pada WHERE, selain itu fungsi LIMIT yaitu untuk membatasi jumlah munculnya baris pada tabel

output :

7. The wine_region table gives a list of all wines offered by a restaurant. The pairing table lists the recommended food items for those wines. Return the style and price for all wines that have a pairing.

Skill : Adding to existing data

database

SELECT style, price
FROM wine_region
WHERE style(
	COUNT(id)
WHERE id IN(
	SELECT wine_id
	FROM pairing
)
ORDER BY price

• kita disini perlu menghubungkan antara ‘id’ pada tabel wine_region dan ‘wine_id ’ pada tabel pairing untuk mengahasilkan kecocokan dan kelengkapan pada kolom style, price pada key indicator ‘id’ dua tabel. untuk filter pada WHERE IN

output :

8. Determine the total number of players from each country.

Skill : Aggregating and ordering data

#database
--player_details
player_id    player_name country_name
1            SC Ganguly   India
2            BB McCullum  New Zealand        
3            RT Ponting   Australia

code :

SELECT country_name, count(player_name) as count
FROM player_details
GROUP BY country_name
ORDER BY country_name;

• disini kita hanya diperintah untuk menghitung jumlah pemain dari masing-masing negara menggunakan COUNT(player_name)
• jangan lupa untuk GROUP BY karena pada SELECT memiliki fungsi perhitungan yakni COUNT

output :

9. Include a subquery in the WHERE clause to identify rows where the win_margin is greater than the average win_margin, from the match dataset.

Skill : Creating subqueries

SELECT match_id, win_margin
FROM match
WHERE
  win_margin > (select avg(win_margin) from match)
ORDER BY match_id
LIMIT 10;

• jadi disini kita diperintah untuk mencari baris win_margin yang memiliki rata-rata lebih dari win_margin sehingga disini bisa kita filter pada WHERE > (…)

output :

10. The wine_region table gives a list of all wines offered by a restaurant. The pairing table lists the recommended food items for those wines. Return the style and price for all wines that DO NOT have a pairing.

Skill : Adding to existing data

database

SELECT style, price
FROM wine_region
WHERE id not in(
	select wine_id
	FROM pairing
)
ORDER BY price

• kita disini diperintah untuk mencari hasil ‘style’ dan ‘price’ yang tidak ada pada tabel pairing untuk itu kita perlu filter ‘id’ sebagai key indicator pada WHERE ‘Column’ NOT IN. untuk itu kita hubungkan dengan wine_id dari tabel pairing

output :

empty result

11. The match table has a win_type field that indicates how a match was won. Create a new categorical variable, win_description that describes the type of win.

Skill : Adding to existing data

Win Type: 1 = runs, 2 = wickets, 3 = no result, 4 = tie

#tabel
--match
match_id    win_type
335988            1
335989            2
335900            2

code :

SELECT match_id, win_type,
CASE WHEN win_type = 1 THEN 'runs'
    WHEN win_type = 2 THEN 'wickets'
    WHEN win_type = 3 THEN 'no result'
    WHEN win_type = 4 THEN 'tie'
    ELSE 'undefined'
END AS win_description
FROM match
ORDER BY match_id
LIMIT 10;

• disini kita perlu membuat suatu kondisi baru pada CASE, fungsi CASE ini berisi WHEN ‘condition’ THEN ‘result’ ELSE ‘result’ END AS … selain itu fungsi case ini dapat berisi lebih dari 1 kondisi

output :

12. Add the role_desc from the role table to the player_match table. The player_match and role tables have a matching column.

Skill : Adding to existing data

#tabel1
--player_match
match_id    player_id   role_id  
  335987    1            1
#tabel2
--role
role_id        role_desc       
  1            Captain

code :

SELECT match_id, player_id, role_desc
FROM player_match
INNER JOIN role
ON player_match.role_id=role.role_id
ORDER BY match_id
LIMIT 10;

• disini kita hanya perlu menggabungkan dua tabel menggunakan INNER JOIN ‘table_name’ ON key indicator kedua tabel yaitu ‘id’ player_match.role_id = role.role_id

output :

sekian hasil dari Data Analysis SQL Datacamp dari saya, walaupun sedikit semoga para pembaca mendapatkan wawasan serta ilmu yang bermanfaat. don’t forget to keep improve your skill although little. thankyouu…