Tweet

Sukriti Macker

24 Jun, 13 tweets, 4 min read

Here is another thread of the most important clauses to boost your SQL queries to the next level!

Advance SQL, A beginner-friendly version! (#DataScience)

Thread 🧵

Let me explain what the table looks like.
The table name is customer_list, where the columns are ID, name, address, zip code, phone, city, country, notes & SID.

See the image for reference

☑️ GROUP BY
This clause is used to group rows that have the same values together. It summarises data from the database.

Note: The group by clause returns one row for each group.

👉 Query: select col_name from table_name GROUP BY column_name;

Example: Group the customers by country.
-> select country, count(*) from customer_list group by country;

Here we get the number of customers in each country. For ex: Argentina has 13 customers, Austria 3 and so on.

Let's implement another query with the same clause.

Query: Find the number of customers in each country where the state is not null.

Simplifying the query: Our aim is to find the number of customers where the state is NOT NULL grouped by country.

-> select country, count(*) from customers_list where city is not null GROUP BY country;

See the image for the output.

*Important*
How to group the results based on two columns? 🤔

Query: Count the no. of customers first based on country, then based on city.

SQL query: select country, city, count(*) from customer_list group by country, city;

See the image for output

Let me simplify this:-
The above query will be executed in two parts.

First, we will get the count of customers based on countries based on country

Then, corresponding to each country we will get each city in the country & the no. of customers in that city.

Observe the output of the above query.

Country Austria has two cities Linz and Salzburg. Linz has 1 customer, and Salzburg has 1 Customer.

See the flowchart for a better understanding.

☑️ HAVING
The clause WHERE cannot be used to filter the "grouped" results obtained using the GROUP BY clause.

We use the HAVING clause for that.

Note: If the group by clause is not present the HAVING clause behaves like a WHERE clause.

Query: Count the number of customers with respect to countries where the no. of customers is greater than 8.

SQL query: select country, count(*) from customer_list group by country HAVING count(country)>8;

☑️ ORDER BY
The clause is used to sort the results in ascending or descending order.

It sorts the result in ascending order by default. To sort the result in descending order, use the keyword DESC.

Query: Count the number of customers with respect to countries where the no. of customers is greater than 8 and sort the countries in descending order.

SQL query: select country, count(*) from customer_list group by country HAVING count(country)>8 order by country desc;

• • •

Missing some Tweet in this thread? You can try to force a refresh

This Thread may be Removed Anytime!

Twitter may remove this content at anytime! Save it as PDF for later use!

More from @Sukriti_Macker

Sukriti Macker

@Sukriti_Macker

25 Jun

I hope you are practising SQL whilst you are learning it. This technique makes it easier to understand concepts.

Alrighty, here is another thread for SQL. 🤩

SQL - A Beginner-friendly version! 📚
(Clauses: IN, BETWEEN and LIKE)

Thread 🧵

🌟Which table are we working on today?
The name of the table is "products". So the columns that we have are productCode, productName, productDescription, among others.

Take a look 👇

☑️ IN
This clause would reduce the efforts to write multiple OR conditions. It helps you find a specific match for a value.

👉 select column1, column2 from table_name where EXPRESSION IN ('value1', 'value2')

Read 15 tweets

Sukriti Macker

@Sukriti_Macker

14 Jun

Hey, folks! Some time back, I was scrolling through LinkedIn and found some essential SQL topics to revisit before an Interview.

Have a look 🧵👇

1. WHERE, AND, OR, NOT, IN
2. ORDER BY, ASC, DESC
3. IS NULL
4. LIMIT
5. MIN, MAX, COUNT, AVG, SUM
6. LIKE, WILDCARDS
7. IN BETWEEN
8. INNER JOIN
9. LEFT JOIN

10. UNION ALL
11. GROUP BY
12. HAVING
13. LEFT, RIGHT, MID, CONCAT
14. PARTITION BY, OVER
15. LEAD, LAG
16. Subqueries
17. RANK, DENSE_RANK, PERCENT_RANK
18. ROW_NUMBER, CUME_DIST
19. FIRST_VALUE, LAST_VALUE
20. AS

Read 5 tweets

Sukriti Macker

@Sukriti_Macker

10 Jun

Let's dive further into SQL to help you through the most demanding skill required in the Data Science Industry.

A Beginner's Guide to Filter Results in MySQL - Part 5!!

Thread 🧵

☑️ LIMIT Clause
To restrict the number of rows in the final result, we use the LIMIT clause.
That means if you have 10,000 rows in the data, you can fetch only 10 rows using this clause in your query.

👉 Query: select * table_name LIMIT limit_no;

For e.g., Consider a table PAYMENTS. It has more than 1000 rows. [See the image]

Read 8 tweets

Sukriti Macker

@Sukriti_Macker

9 Jun

Have you found the concepts of JOINS in SQL tricky? 😵‍💫

A Beginner-Friendly JOINS guide that would help you in your SQL & Data Science Journey.

Thread 🧵

☑️ JOINS (Some Theory)
Using JOIN, we can query data from two or more tables based on the related column present in both tables.

While performing a join, we need to specify the shared column & the condition on which we want to join tables.

☑️ Types of JOINS:-
1. Inner Join
2. Left Join
3. Right Join
4. Full Join

Read 14 tweets

Sukriti Macker

@Sukriti_Macker

7 Jun

Want to pursue Data Science? SQL is the most imperative skill required in it!

A Beginner's Guide to MySQL Queries part 4! 📖
RETRIEVAL & LOADING the data!

Thread 🧵

✅ RETRIEVE THE DATA

The SELECT command allows the retrieval of information as per our requirements.

You can select/retrieve:-
1. All the data from the table using *
2. A single column
3. Multiple columns

1️⃣ Select all columns of the table

SELECT * from table_name;

👉 E.g., Selecting/Retrieving all the values from the classroom1 table.

2️⃣ Select one column from the table

SELECT column_name from table_name;

👉 E.g., Selecting the student_name column from the table classroom1.

Read 5 tweets

Sukriti Macker

@Sukriti_Macker

6 Jun

SQL is the most wanted skill in Data Science!! 😱

A Beginner's Guide to MySQL basic queries - Part 3!! 😎

Making mistakes while creating tables is common. Let's learn how to update and fix them! 😬

Thread 🧵

✅ UPDATE THE TABLE

1. Add a column to the existing table:-

ALTER table table_name ADD (column_name datatype);

(Note: Adding a constraint to a column is optional)

👉 E.g., Adding the ADDRESS column to the classroom1 table.

2. Add MULTIPLE columns at once:-

ALTER table table_name ADD (colname1 datatype1, colname2 datatype2);

👉 E.g., Adding Email_id and ContactNo columns to the classroom1 table