List of SQL commands for commonly used Excel operations

Introduction

Learning SQL after Excel couldn't be easier!!

I have spent more than a decade working in Excel. But nevertheless, There is much to learn. If you don't like encoding, Excel could be your rescue in the world of data science (until a certain point). Once you understand the operations of Excel, learning SQL is very easy.

Why You Can't Use Excel for Serious Data Science Work?

Now, in this stage, you might wonder, Why can't i use excel for all my work? There are several reasons for this:

1. For large data sets, Excel is not effective. Calculations on large data sets will not be done or will take a long time. Just a warning: Microsoft recently released Power BI and I need to explore it. It could have changed the boundaries of big data.
2. There is no audit trail in Excel. With tools based on coding and workflow management, you can rescan and run the process over and over again. It is very difficult to do it in Excel. If you accidentally change or delete a cell in Excel, it's hard to track her.
3. Finally, Excel takes a long time to update libraries with the latest algorithms in data science and machine learning. Try looking for XGboost and FTRL in Excel!

Moving to SQL would address the point 1 and the point 2 until a certain point. What's more, SQL is one of the most sought after skills for a data scientist.

If you don't know SQL yet and have worked in Excel, can start right now. I designed this tutorial with the most commonly used Excel operations in mind. Your previous experience combined with this tutorial can quickly make you a SQL expert. (Note: If you find any problem, write me in the comment section below.

Related: SQL and RDBMS Basics for Beginners

List of common Excel operations

Here is the list of commonly used Excel operations. In this tutorial, I have performed all these operations in SQL:

1. See data
2. Sort data
3. Filter data
4. Delete records
5. Add records
6. Update data in an existing record
7. Show unique values
8. Write an expression to generate a new column
9. Find data from another table
10. Dynamic table

To perform the operations listed above, I will use the data listed below (Employee) :

1. See data

In excel, we can see all the records directly. But SQL requires a command to process this request. This can be done using SELECT command.

Syntax:

SELECT column_1, column_2,… column_n | * FROM table_name;

Exercise:

A. See all data in the employee table

Select * from Employee;

B. See only ECODE and gender data from the employee table

Select ECODE, employee gender

2. Sort data

Information organization becomes important when you have more data. Helps generate quick inferences. You can quickly organize one excel worksheet per classification your data in ascending or descending order.

Syntax:

SELECT column_1, column_2,… column_n | * FROM table_name orden por column_1 [desc], column_2 [desc];

Exercise:

A. Organize records in employee table in descending order of Total_Payout.

Select * from Employee order by Total_Payout desc;

B. Organize the records in the Employees table by city (rising) y Total_Payout (descendant).

Select * from Employee order by City, Total_Payout desc;

3. Filter data

In addition to ordering, we often apply filters to analyze the data in a better way. When data is filtered, only rows that meet the filter criteria are displayed, while other rows are hidden. What's more, we can apply several criteria to filter data.

Syntax:

SELECT column_1, column_2,… column_n | * FROM table_name donde column_1 operator value;

Below is the common list of operators that we can use to form a condition.

Operador	Descripción
=	Igual
	No es igual. Nota: En algunas versiones de SQL, este operador puede escribirse como! =
>	Mas grande que
<	Menos que
> =	Mayor que o igual
<=	Menor o igual
ENTRE	Entre una gama inclusiva
IGUAL QUE	Busca un patrón
EN	Para especificar varios valores posibles para una columna

Exercise:

A. Filter the observations associated with the city “Delhi”

Select * from Employee where City="Delhi";

B. Filter department observations “Admin” y Total_Payout> = 500

Select * from Employee where Department="Admin" and Total_Payout >=500;

4. Delete records

Deleting records or columns is a commonly used operation in Excel. In excel, we simply press the ‘Delete key’ on the keyboard to delete a record. In the same way, SQL has command REMOVE to delete records from a table.

Syntax:

DELETE FROM table name WHERE some_column=some_value;

Exercise:

A. Delete observations that have Total_Payout> = 600

Delete * from Employee where Total_Payout >=600;

Delete two records just because these two observations satisfy the condition stated above. But be careful! if we don't provide any conditions, will remove all records from a table.

B. Delete observations that have Total_Payout> = 600 and Department = “Admin”

Delete * from Employee where Total_Payout >=600 and Department ="Admin";

The above command will delete only one record that meets the condition.

5. Add records

We have seen methods to delete records, we can also add records to SQL table like we do in Excel. INSERT The command helps to perform this operation.

Syntax:

INSERT IN table name VALUES (valor1, valor2, value3,…); -> Insert values ​​in all columns

O,

INSERT IN table name (columna1,columna2,columna3,…) VALUES (valor1,valor2,value3,…); -> Insert values ​​into selected columns

Exercise:

A. Add the records below to the table 'Employee’

Insert into employee values('A002','05-Nov-12',0.8,'Female','Admin',12.05,26,313.3,'Mumbai');

Select * from Employee where ECODE='A002';

B. Insert values ​​in ECODE (A016) and Department (HR) only.

Insert into employee (ECODE, Department) values('A016','HR');

Select * from Employee where Department="HR";

6. Update Data in existing observations

Suppose we want to update the name of the department of “RRHH” a “Workforce” for all employees. For such cases, SQL has a command UPGRADE that performs this function.

Syntax:

ACTUALIZAR table_name SET column1 = value1, column2 = value2,… WHERE some_column = some_value;

Exercise: change department name “Human Resources” a “Workforce”

Update Employee SET Department="Manpower" where Department="HR";

Please select * of Employee;

7. Show unique values

We can show unique values ​​of variable (s) applying DIFFERENT keyword before variable name.

Syntax:

SELECT DISTINCT column_name, column_name FROM table_name;

Exercise: show unique city values

Select distinct City from Employee;

8. Write an expression to generate a new column.

In excel, we can create a column, based on an existing column using functions or operators. This can be done in SQL using the following commands.

Exercise:

A. Create a new Incentive column that is the 10% de Total_Payout

Please select *, Total_Payout * 01 as an employee incentive;

B. Create a new column City_Code that has the first three characters of City.

Select *, Left(City,3) as City_Code from Employee where Department="Admin";

For more details on SQL functions, I would recommend you check this Link.

9. Find data from another table

The Excel function most used by any BI professional / data analyst is VLOOKUP (). Helps to map data from another table to main table. In other words, we can say that it is the ‘excellent way’ to unite 2 data sets via a common key.

And SQL, we have a similar functionality known as LOG IN.

SQL JOIN is used to join rows from two or more tables, based on a common field between them. It has several types:

• INTERNALLY JOIN: Returns rows when there is a match in both tables
• JOIN THE LEFT: Returns all rows from the left table and matching rows from the right table.
• JOIN CORRECTLY: Returns all rows from the right table and matching rows from the left table
• JOIN FULL: Returns all rows when there is a match in ONE of the tables

Syntax:

SELECT table1.column1, table2.column2..... FROM table1 INNER | LEFT| RIGHT| FULL JOIN table2 ON table1.column = table2.column;

Exercise: Below is the city category table “City_Cat”, now I want to assign city category to Employee table and show all records from Employee table.Here, I want to show all the records from the Employee table. Then, we will use join the left.

SELECT Employee.*,City_Cat.City_Category FROM Employee LEFT JOIN City_Cat ON Employee.City = City_Cat.City;

To learn more about JOIN operations, I would recommend you check this Link.

10. Dynamic table

Pivot Table is an advanced way to analyze data in Excel. Not only useful, it allows you to extract hidden information from the data.

What's more, helps us generate inference by summarizing data and allows us manipulate in different ways. This operation can be done in SQL using aggregate functions and GROUP BY command.

Syntax:

SELECT columna, added_function (column) FROM the table WHERE column operator value GROUP BY column;

Exercise:

A. Show the sum of Total_Payout by gender

SELECT Gender, Sum(Total_Payout) from Employee Group by Gender;

B. Show the sum of Total_Payout and the record count by gender and city

SELECT Gender, City, Count(City), Sum(Total_Payout) from Employee Group by Gender, City;

Final notes

Once you work on SQL, you will find that data handling and manipulation can be much faster. For the installation, mysql is open source. You can install it and get started.

In this article, we have analyzed the SQL commands to 10 common excel operations how to view, order, filter, remove, search and summarize data. We also analyze the different operators and types of joins to perform SQL operations without problems.

If you like what you have just read and want to continue learning about analytics, subscribe to our emails, Follow us on twitter or like ours page the Facebook.

Related