Instructions

1. Complete the code in the editor to find the average rating for free apps.

3. Modify the existing code in the editor on the right to compute the average rating of non-free apps.

Optional exercise: Inspect the value of avg_rating_non_free and compare the average with that of free apps (the average rating of free apps is approximately 3.38 — we computed it in the first screen). Can we use the average values to say that free apps are better than non-free apps, or vice versa?

4. Following the same techniques we used in the diagram above, compute the average rating of non-gaming apps.

Optional exercise: Compare the average rating of gaming apps (3.69) with that of non-gaming apps. Why do you think we see this difference?

5. Complete the code in the editor to compute the average rating of free gaming apps.

6. Complete the code in the editor to compute the average rating of the apps whose genre is either "Social Networking" or "Games."

7. Compute the average rating of non-free apps whose genre is either "Social Networking" or "Games."

 Cara membuat aplikasi CRUD dengan PHP dan MongoDB

 OPERATOR LOGIKA MONGODB

        Holla teman - teman !! Pada kesempatan kali ini kita akan membahas tentang Operator Logika MongoDB.

Jenis Operator Logika, Contoh, dan Perbandingan dengan Perintah SQL

    Operator Logika memungkinkan teman - teman untuk menggabungkan beberapa kondisi untuk memfilter data sesuai dengan kebutuhan teman- teman. Artikel ini akan membahas berbagai jenis Operator Logika MongoDB, memberikan contoh penggunaan, dan membandingkannya dengan perintah SQL yang serupa.

Jenis Operator Logika MongoDB

Ada beberapa jenis Operator Logika yang dapat teman - teman gunakan dalam MongoDB untuk menggabungkan kondisi pencarian. Beberapa di antaranya adalah:

1. $and: Operator ini digunakan untuk menggabungkan beberapa kondisi yang semuanya harus benar. Hasilnya adalah dokumen yang memenuhi semua kondisi.

2. $or: Operator ini digunakan untuk menggabungkan beberapa kondisi, di mana setidaknya satu kondisi harus benar. Hasilnya adalah dokumen yang memenuhi salah satu atau semua kondisi.

3. $not: Operator ini digunakan untuk menggabungkan kondisi yang membalik hasilnya. Dengan kata lain, hasilnya adalah dokumen yang tidak memenuhi kondisi yang diberikan.

4. $nor: Operator ini digunakan untuk menggabungkan beberapa kondisi, di mana tidak satupun dari kondisi yang diberikan harus benar. Hasilnya adalah dokumen yang tidak memenuhi semua kondisi.

Contoh Operator Logika MongoDB

Berikut adalah contoh operator logika yang bisa teman - teman pakai :

1. Menggunakan $and:

db.products.find({ $and: [{ price: 899 }, { color: { $in: ["white", "black"] } }] }, { name: 1, price: 1, color: 1 }) 

Hasil : [ { _id: 2, name: 'xTablet', price: 899, color: [ 'white', 'black', 'purple' ] } ]

    2. Menggunakan $or:

db.products.find({ $or: [{ price: 799 }, { price: 899 }] }, { name: 1, price: 1 })

Hasil : [ { _id: 1, name: 'xPhone', price: 799 }, { _id: 2, name: 'xTablet', price: 899 }, { _id: 3, name: 'SmartTablet', price: 899 } ]

db.products.find({ $or: [ { price: {$lt: 699} }, { price: {$gt: 799} } ] }, { name: 1, price: 1 })

Hasil : [ { _id: 2, name: 'xTablet', price: 899 }, { _id: 3, name: 'SmartTablet', price: 899 }, { _id: 5, name: 'SmartPhone', price: 599 } ]

3. Menggunakan $not:

db.products.find({ $or: [ { price: {$lt: 699} }, { price: {$gt: 799} } ] }, { name: 1, price: 1 })

Hasil : [ { _id: 2, name: 'xTablet', price: 899 }, { _id: 3, name: 'SmartTablet', price: 899 }, { _id: 5, name: 'SmartPhone', price: 599 } ]

db.products.find({ price: { $not: { $gt: 699 } } }, { name: 1, price: 1 })

Hasil : [ { _id: 4, name: 'SmartPad', price: 699 }, { _id: 5, name: 'SmartPhone', price: 599 }, { _id: 6, name: 'xWidget' } ]

4. Menggunakan $nor:

db.products.find({ $nor :[ { price: 899}, { color: "gold"} ] }, { name: 1, price: 1, color: 1 })

Hasil : { "_id" : 1, "name" : "xPhone", "price" : 799, "color" : [ "white", "black" ] } { "_id" : 6, "name" : "xWidget", "color" : [ "black" ] }

5. Menggunakan $exist:

db.products.find( { price: { $exists: true } }, { name: 1, price: 1 } )

Hasil : { "_id" : 1, "name" : "xPhone", "price" : 799 } { "_id" : 2, "name" : "xTablet", "price" : 899 } { "_id" : 3, "name" : "SmartTablet", "price" : 899 } { "_id" : 4, "name" : "SmartPad", "price" : 699 } { "_id" : 5, "name" : "SmartPhone", "price" : 599 } { "_id" : 7, "name" : "xReader", "price" : null }

db.products.find({ price: { $exists: true, $gt: 699 } }, { name: 1, price: 1 });

Hasil : { "_id" : 1, "name" : "xPhone", "price" : 799 } { "_id" : 2, "name" : "xTablet", "price" : 899 } { "_id" : 3, "name" : "SmartTablet", "price" : 899 }

db.products.find({ price: { $exists: false } }, { name: 1, price: 1 });

 Hasil : { "_id" : 6, "name" : "xWidget" }

Perbandingan dengan Perintah SQL

Untuk memahami lebih baik Operator Logika MongoDB, mari bandingkan dengan perintah SQL yang serupa:

SQL untuk $and:

SELECT * FROM table

WHERE age >= 18 AND gender = 'female';

SQL untuk $or:

SELECT * FROM table

WHERE category = 'Electronics' OR price <= 500;

SQL untuk $not:

SELECT * FROM table

WHERE NOT (age < 18);

SQL untuk $nor:

SELECT * FROM table

WHERE NOT (status = 'active' OR age >= 65);

Kuis

• Buat tabel di MySQL dengan field dan data yang sama dengan contoh diatas 

        1.     Buat databasae baru di MySql  :  

        2.     Buat tabel produk dan warna produk dengan mengetik perintah dibawah ini  : 

        3.     Setelah tabel selesai dibuat, teman - teman bisa isi datanya sesuai perintah dibawah : 

INSERT INTO products (id, name, price, releaseDate, ram, screen, cpu, storage)

VALUES

    (1, 'xPhone', 799.00, '2011-05-14', 4, 6.5, 2.66, 64),

    (2, 'xTablet', 899.00, '2011-09-01', 16, 9.5, 3.66, 128),

    (3, 'SmartTablet', 899.00, '2015-01-14', 12, 9.7, 3.66, 16),

    (4, 'SmartPad', 699.00, '2020-05-14', 8, 9.7, 1.66, 128),

    (5, 'SmartPhone', 599.00, '2022-09-14', 4, 9.7, 1.66, 128),

    (6, 'xWidget', NULL, NULL, 64, 9.7, 3.66, 1024),

    (7, 'xReader', NULL, NULL, 64, 6.7, 3.66, 128);

INSERT INTO product_colors (product_id, color)

VALUES

    (1, 'white'),

    (1, 'black'),

    (2, 'white'),

    (2, 'black'),

    (2, 'purple'),

    (3, 'blue'),

    (4, 'white'),

    (4, 'orange'),

    (4, 'gold'),

    (4, 'gray'),

    (5, 'white'),

    (5, 'orange'),

    (5, 'gold'),

    (5, 'gray'),

    (6, 'black'),

    (7, 'black'),

    (7, 'white');

        4.     Maka hasil akhirnya akan muncul 2 tabel seperti ini : 

• Buat query SQL untuk dapat menampilkan data yang sama dengan contoh diatas

        1.     Menggunakan $and (SQL):

SELECT name, price, color

FROM products

JOIN product_colors ON products.id = product_colors.product_id

WHERE price = 899

  AND color IN ('white', 'black');

        2.     Menggunakan $or (SQL):

SELECT name, price

FROM products

WHERE price = 799 OR price = 899;

SELECT name, price

FROM products

WHERE price < 699 OR price > 799;

        3.     Menggunakan $not (SQL):

SELECT id AS _id, name, price

FROM products

WHERE price IS NULL OR NOT (price > 699);

SELECT name

FROM products

WHERE name NOT REGEXP '^Smart+';

        4.     Menggunakan $nor (SQL):

SELECT name, price, color

FROM products

LEFT JOIN product_colors ON products.id = product_colors.product_id

WHERE (price IS NULL OR price != 899) AND (color IS NULL OR color != 'gold');

        5.     Menggunakan $exists (SQL):

SELECT name, price

FROM products

WHERE price IS NOT NULL OR price IS NULL;

SELECT name, price

FROM products

WHERE price IS NOT NULL AND price > 699;

SELECT name, price

FROM products

WHERE price IS NULL;

 

Instructions

1. Store the second row ('Instagram', 0.0, 'USD', 2161558, 4.5) as a list in a variable named row_2.
Store the third row ('Clash of Clans', 0.0, 'USD', 2130805, 4.5) as a list in a variable named row_3.

2. In the code editor, you can already see the lists for the first three rows.

The fourth element in each list describes the number of ratings an app has received. Retrieve this fourth element from each list, and then find the average value of the retrieved numbers.

Assign the fourth element from the list row_1 to a variable named ratings_1. Don't forget that the indexing starts at 0.
Assign the fourth element from the list row_2 to a variable named ratings_2.
Assign the fourth element from the list row_3 to a variable named ratings_3.
Add the three numbers retrieved together and save the sum to a variable named total.
Divide the sum (now saved in the variable total) by 3 to get the average number of ratings for the first three rows. Assign the result to a variable named average.

3. The last element in each list shows the average rating of each application.

Retrieve the ratings for the first three rows, and then find the average value of all the ratings retrieved.

Assign the last element from the list row_1 to a variable named rating_1. Try to take advantage of negative indexing.
Assign the last element from the list row_2 to a variable named rating_2.
Assign the last element from the list row_3 to a variable named rating_3.
Add the three ratings together and save the sum to a variable named total_rating.
Divide the total by 3 to get the average rating. Assign the result to a variable named average_rating.

4. For Facebook, Instagram, and Pandora — Music & Radio, isolate the rating data in separate lists. Each list should contain the name of the app, the rating count, and the user rating. Don't forget that indexing starts at 0.

For Facebook, assign the list to a variable named fb_rating_data.
For Instagram, assign the list to a variable named insta_rating_data.
For Pandora — Music & Radio, assign the list to a variable named pandora_rating_data.
Compute the average user rating for Facebook, Instagram, and Pandora — Music & Radio using the data you stored in fb_rating_data, insta_rating_data, and pandora_rating_data.

You'll need to add the ratings together first, and then divide the total by the number of ratings. Assign the result to a variable named avg_rating. As a side note, we could calculate the average rating here a little bit better using the weighted mean — we'll learn about the weighted mean in the statistics courses.

5. Select the first four elements from row_1 using a list slicing syntax shortcut. Assign the output to a variable named first_4_fb.
Select the last three elements from row_1 using a list slicing syntax shortcut. Assign the output to a variable named last_3_fb.
From row_5, select the list slice ['USD', 1126879] using a list slicing syntax shortcut. Assign the output to a variable named pandora_3_4.

Compute the average rating of the apps by retrieving the right data points from the app_data_set list of lists.
The rating is the last element of each row. You'll need to sum up the ratings and then divide by the number of ratings.
Assign the result to a variable named avg_rating.

Open the file using the open() command. Save the output to a variable named opened_file.
Read in the opened file using the reader() command (we've already imported reader() for you from the csv module). Save the output to a variable named read_file.
Transform the read-in file to a list of lists using the list() command. Save the list of lists to a variable named apps_data. Explore apps_data. You could:
Print its length using the len() command
Print the first row (the row describing column names)
Print the second and the third row (try to use list slicing here)

8. Use the new technique we've learned to print all the rows in the app_data_set list of lists. Essentially, you'll need to translate this pattern into Python syntax: for each list in the app_data_set variable, print that list.

Don't forget about indentation.

Initialize a variable named rating_sum with a value of zero.
Loop through the apps_data[1:] list of lists (make sure you don't include the header row). For each of the 7,197 iterations of the loop (for each row in apps_data[1:]):
Extract the rating of the app and store it to a variable named rating (the rating has the index number 7). Make sure you convert the rating value from a string to a float using the float() command.
Add the value stored in rating to the current value of the rating_sum.
Divide the rating sum (stored in rating_sum) by the number of ratings to get an average value. Store the result in a variable named avg_rating.

11. Using the new technique we've learned, compute the average app rating for all of the 7,197 apps stored in our data set.

Initialize an empty list named all_ratings.
Loop through the apps_data[1:] list of lists (make sure you don't include the header row). For each of the 7,197 iterations of the loop:
Extract the rating of the app and store it to a variable named rating (the rating has the index number 7). Make sure you convert the rating value from a string to a float.
Append the value stored in rating to the list all_ratings.
Compute the sum of all ratings using the sum() command.
Divide the sum of all ratings by the number of ratings, and assign the result to a variable named avg_rating.