Essential SQL Commands For Data Science
Tags: database, datascience, sql
Published: 2023-03-15 16:24:25

Structured Query Language is a simple ,easy to write language used around the world to manipulate databases.Without data there is no Data Science hence SQL is very important.
In this post we will talk about some of the important sql commands used in Data Science.

**Data Retrieval**

**Select Command**
Together with other retrieve commands it is  used to retrieve specific data from the database.The Select Clause can be used to specify a column or columns from the database.To retieve more than one column a comma and a space  between the column names is used.And to get all the columns in a given table use an asterik (*).

Syntax of Select Command:
- Select * from table_name
- Select column_name from table_name
- Select column_name1, column_name2 from table_name
- Select * from table_name where condition

**Distinct Command**
It is used with the select command to  display only the different,unique or distinct data from a table that has some similar data.

Syntax of the distinct command:
- Select distinct column_name from table_name
- Select distinct column_name1, column_name2…….. from table_name

**Data Retrieval With Simple Conditions**
**where**
This is used to display specific data that meets the given condition.

Syntax of where statement:****
- Select * from table_name where condition
-  Select column_name1, column_name2…. where condition

**order by**
Used to retrieve data from the database in a specific order.It could be ascending and descending order.

Syntax of order by statement:
- Select * from table_name where condition order by column_name
- Select * from table_name where condition order by column_name 
DESC

**limit**
Used to get a limited number of entries i.e the top 10 records.

Syntax:
SELECT * FROM table_name where condition order by column_name desc limit 10.

**Aggregations**
An aggregate function calculates multiple values and returns a single value.Aggregate functions  in SQL includes group by, avg, count, sum, min, max and many others.NULL values are ignored during the calculations except for the count function. 

**GROUP BY**
The group by clause is used to display the result in the group with the aggregate functions.

Syntax of group by:
Select column_list from table_name where condition group by expression.

**COUNT()**
Used to count the number of all or distinct values in an expression. 

Syntax of count () function:
SELECT * from count (column_name) from table_name

**SUM()**
sum() function is used to add and get the total sum of values of a numeric column.

Syntax of sum() function:
SELECT sum (column_name) from table_name

**JOINS**

SQL JOINS is used to combine data or rows from two or more tables based on a common field between them.
There are 4 different types of SQL joins:

**SQL INNER JOIN (SIMPLE JOIN OR JOIN)**
Returns rows from multiple tables where the join condition is met,returns only the common data between the two tables that is where they intersect.

Syntax:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
INNER JOIN table2
ON table1.matching_column = table2.matching_column;

 **SQL LEFT OUTER JOIN (LEFT JOIN)**
Returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the join condition is met  or where they intersect.

Suntax:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;


**SQL RIGHT OUTER JOIN (RIGHT JOIN)**
It returns all the rows of the table on the right side of the join and matching rows for the table on the left side of the join.

Syntax:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;

 **SQL FULL OUTER JOIN (FULL JOIN)**
Returns all the rows from both tables. For the rows for which there is no matching,it returns NULL values.

Syntax:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1 
FULL JOIN table2
ON table1.matching_column = table2.matching_column;

**UNION**
Returns two query results together.

Syntax:
SELECT column_name AS Name FROM table_name
UNION
SELECT column_name FROM table_name

**Complex Conditions**

**CASE Statement**
This is the way SQL handles if/then logic.The statements are often followed by WHEN and THEN statements.The case statements ends with END statement.ELSE statements  are optional .

Syntax:
SELECT CASE Expression
When expression1 Then Result1
When expression2 Then Result2
...
ELSE Result
END

**Window Functions**
Uses agreggate functions and other functions over a particular set of rows.OVER clause is used in the  definition of the window. 

Syntax:
SELECT coulmn_name1, 
window_function(cloumn_name2)
OVER([PARTITION BY column_name1] [ORDER BY column_name3]) AS new_column
FROM table_name;