SORTING & GROUPING IN MySQL

[email protected]

Sorting Queries :

1. Find the top 5 Samsung phones with the biggest screen size:

Query:

SELECT model, screen_size

FROM newdb.smartphones
WHERE brand_name ='samsung'
ORDER BY screen_size DESC LIMIT 5;

Explanation: This query selects the model and screen size of Samsung phones, orders them
by screen size in descending order, and limits the result to the top 5 phones.

2. Sort phones in descending order of total cameras:

Query:

SELECT model, num_front_cameras + num_rear_cameras AS total_cameras

FROM newdb.smartphones
ORDER BY total_cameras DESC LIMIT 10;

Explanation: It calculates the total number of cameras (front + rear) for each phone, sorts them
in descending order, and limits the result to the top 10 phones.

3. Sort phones by PPI (pixels per inch) in decreasing order:

Query:

SELECT model, ROUND(SQRT(resolution_width * resolution_width +

resolution_height * resolution_height) / screen_size) AS PPI
FROM newdb.smartphones
ORDER BY PPI DESC;

Explanation: This calculates the PPI for each phone using its screen resolution and size, and
sorts the data by PPI in descending order.

4. Find the phone with the 2nd largest battery:

Query:

SELECT model, battery_capacity

FROM newdb.smartphones
ORDER BY battery_capacity DESC LIMIT 1, 1;

Explanation: This query sorts phones by battery capacity and skips the first largest one to find
the second largest.

5. Find the worst-rated Apple phone:

Query:

SELECT model, rating

FROM newdb.smartphones
WHERE brand_name = 'apple'
ORDER BY rating ASC LIMIT 1;

Explanation: It selects the model and rating of Apple phones, orders by rating in ascending
order, and returns the worst-rated phone.

6. Sort phones alphabetically by model and then by rating (descending):

Query:

SELECT *
FROM newdb.smartphones
ORDER BY model ASC, rating DESC;

Explanation: This query sorts phones first alphabetically by their model name, and then by
rating in descending order.

7. Sort phones alphabetically by model and then by price (ascending):

Query:

SELECT *
FROM newdb.smartphones
ORDER BY model ASC, price ASC;

Explanation: Similar to query 6, but here the second sorting criterion is the phone's price in
ascending order.

Grouping Queries :

1. Group smartphones by brand and get count, average price, max rating, average
screen size, and battery capacity:

Query:

SELECT brand_name, COUNT(*) AS num_phones, ROUND(AVG(price)) AS

avg_price, MAX(rating) AS max_rating,
ROUND(AVG(screen_size)) AS avg_screen_size,
ROUND(AVG(battery_capacity)) AS avg_battery
FROM newdb.smartphones
GROUP BY brand_name;

Explanation: This groups smartphones by brand and returns various statistics like the number
of phones, average price, maximum rating, average screen size, and battery capacity.

2. Group smartphones by whether they have fast charging and get average price and
rating:

Query:

SELECT fast_charging_available, AVG(price) AS avg_price, AVG(rating)

AS avg_rating
FROM newdb.smartphones
GROUP BY fast_charging_available;

Explanation: This query groups smartphones based on whether they support fast charging,
and calculates the average price and rating for each group.

3. Group smartphones by extended memory availability and get the average price:
Query:

SELECT extended_memory_available, ROUND(AVG(price)) AS avg_price

FROM newdb.smartphones
GROUP BY extended_memory_available;

Explanation: This groups phones by their extended memory availability and returns the
average price for each group.

4. Group smartphones by brand and processor brand and get the count of models
and average rear camera resolution:

Query:

SELECT brand_name, processor_brand, COUNT(*) AS num_phones,

ROUND(AVG(primary_camera_rear)) AS avg_camera_resolution
FROM newdb.smartphones
GROUP BY brand_name, processor_brand;

Explanation: This groups phones by brand and processor type and provides the count of
phones and average resolution of the rear camera.

5. Find the top 5 most expensive phone brands:

Query:

SELECT brand_name, ROUND(AVG(price)) AS avg_price

FROM newdb.smartphones
GROUP BY brand_name
ORDER BY avg_price DESC LIMIT 5;

Explanation: This query groups phones by brand, calculates the average price, and returns the
top 5 brands with the highest average price.

6. Which brand makes the smallest screen smartphones:

Query:

SELECT brand_name, ROUND(AVG(screen_size)) AS avg_screen_size

FROM newdb.smartphones
GROUP BY brand_name
ORDER BY avg_screen_size ASC LIMIT 1;
Explanation: Groups phones by brand, finds the average screen size for each brand, and
returns the one with the smallest average screen size.

7. Average price of 5G phones vs non-5G phones:

Query:

SELECT has_5g, ROUND(AVG(price)) AS avg_price

FROM newdb.smartphones
GROUP BY has_5g;

Explanation: This groups phones based on whether they support 5G and calculates the
average price for both groups.

8. Find the brand with the highest number of models that have both NFC and IR
blaster:

Query:

SELECT brand_name, COUNT(*) AS count

FROM newdb.smartphones
WHERE has_nfc = 'TRUE' AND has_ir_blaster = 'TRUE'
GROUP BY brand_name
ORDER BY count DESC LIMIT 1;

Explanation: This finds the brand with the most models that have both NFC and IR blaster
features.

9. Find average price for Samsung NFC vs non-NFC phones:

Query:

SELECT has_nfc, AVG(price) AS avg_price

FROM newdb.smartphones
WHERE brand_name = 'samsung'
GROUP BY has_nfc
ORDER BY avg_price DESC LIMIT 2;

Explanation: This calculates the average price of Samsung phones based on whether they
have NFC, and compares the two groups.
Having Clause Queries

1. Find the average rating of smartphone brands with more than 20 models:

Query:

SELECT brand_name, COUNT(*) AS count, ROUND(AVG(rating)) AS avg_rating

FROM newdb.smartphones
GROUP BY brand_name
HAVING count > 20;

Explanation: This finds the average rating of brands that have more than 20 models in the
database.

2. Find top 3 brands with the highest average RAM, refresh rate > 90, fast charging,
and at least 10 models:

Query:

SELECT brand_name, ROUND(AVG(ram_capacity)) AS avg_ram

FROM newdb.smartphones
WHERE refresh_rate > 90 AND fast_charging_available = 1
GROUP BY brand_name
HAVING COUNT(*) > 10
ORDER BY avg_ram DESC LIMIT 3;

Explanation: This query filters phones with a refresh rate greater than 90 and fast charging,
and groups them by brand to find the top 3 brands with the highest average RAM and more than
10 models.

3. Find the average price of 5G-enabled phones with high ratings and more than 10
models:

Query:

SELECT brand_name, ROUND(AVG(price)) AS avg_price

FROM newdb.smartphones
WHERE has_5g = 'TRUE'
GROUP BY brand_name
HAVING COUNT(*) > 10 AND AVG(rating) > 70
ORDER BY avg_price DESC;

Explanation: This finds the average price of 5G-enabled phones for brands that have more
than 10 models and an average rating greater than 70.

4. Count the number of rows in the table:

Query:

SELECT COUNT(*) FROM newdb.smartphones;