Essential SQL Commands for Data Science
Tags: sql, codenewbie, datascience, programming
Published: 2023-03-29 14:41:56

**Introduction:**
SQL is a standard language used for managing relational databases. In the data science field, SQL is one of the essential tools used for data analysis, data cleaning, and data management. SQL allows data scientists to extract and manipulate data from databases, which makes it a vital skill for anyone working in this field. In this article, we will discuss the essential SQL commands for data science, including SELECT, FROM, WHERE, JOIN, GROUP BY, and ORDER BY.

**Data Set:**
To demonstrate the essential SQL commands for data science, we will be using the "World Happiness Report" dataset from Kaggle.com. The dataset contains information about happiness scores and rankings for countries around the world. You can download the dataset from this link:[(https://www.kaggle.com/datasets/unsdsn/world-happiness)]


**SELECT** Command:
The SELECT command is used to retrieve data from a database. It is one of the most basic SQL commands and is used in almost every SQL query. To retrieve data from a specific table, you need to use the SELECT command, followed by the name of the columns you want to retrieve. For example, to retrieve the names of all countries from the "world_happiness" table, the query would be:

```
SELECT Country FROM world_happiness;

```
**FROM** Command:
The FROM command specifies the table or tables from which to retrieve data. For example, if you want to retrieve data from the "world_happiness" table, you would use the FROM command followed by the table name. For example:

```
SELECT * FROM world_happiness;

```
This command retrieves all the columns from the "world_happiness" table.

**WHERE** Command:
The WHERE command is used to filter data based on specific conditions. For example, if you want to retrieve only the data from the "world_happiness" table where the happiness score is greater than 7.5, you would use the WHERE command, followed by the condition. For example:

```
SELECT * FROM world_happiness WHERE Score > 7.5;

```
This command retrieves all the columns from the "world_happiness" table where the happiness score is greater than 7.5.

**JOIN** Command:
The JOIN command is used to combine data from two or more tables based on a related column. For example, if you have two tables, "world_happiness" and "GDP", and you want to retrieve the GDP for each country, you can use the JOIN command. For example:

```
SELECT world_happiness.Country, GDP.GDP
FROM world_happiness
JOIN GDP
ON world_happiness.Country = GDP.Country;

```
This command retrieves the country name and GDP from the "world_happiness" and "GDP" tables, respectively. The ON command specifies the related column between the two tables.

**GROUP BY** Command:
The GROUP BY command is used to group data based on a specific column. For example, if you have a "world_happiness" table and you want to know the average happiness score for each region, you can use the GROUP BY command. For example:

```
SELECT Region, AVG(Score) as Avg_Score
FROM world_happiness
GROUP BY Region;

```
This command retrieves the average happiness score for each region by grouping the data by the region column. The AVG command is used to calculate the average score.

**ORDER BY** Command:
The ORDER BY command is used to sort the data based on a specific column. For example, if you have a "world_happiness" table and you want to retrieve the happiness score data for each country in descending order:

```
SELECT Country, Score 
FROM world_happiness 
ORDER BY Score DESC;

```
This command retrieves the country name and happiness score from the "world_happiness" table and sorts the data by the score column in descending order. The DESC keyword specifies the descending order.

**Conclusion:**
In this article, we have discussed the essential SQL commands for data science, including SELECT, FROM, WHERE, JOIN, GROUP BY, and ORDER BY. These commands are the foundation of SQL and are used extensively in data science for data manipulation and analysis.

We have also demonstrated how to use these commands in SQL queries using the "World Happiness Report" dataset from Kaggle.com. By working with this dataset, we have shown how to extract data from a specific table, filter data based on specific conditions, combine data from multiple tables, group data based on a specific column, and sort data based on a specific column.

Learning SQL is a critical skill for any data scientist or data analyst. By mastering these essential SQL commands, you will be better equipped to work with relational databases, manipulate data, and perform data analysis.

In conclusion, we hope that this article has provided you with a basic understanding of SQL commands and their applications in data science. We encourage you to continue exploring SQL and its advanced features to improve your data science skills further.