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;
Top comments (0)