How to Use If Else in SQL Select Statement

How to Use SQL Statements in MS Excel?

Last Updated: 01 Nov, 2023

Shivani

Most Excel spreadsheets need you to manually insert data into cells before analyzing it or performing calculations using formulae or other functions. You may use Excel to get data from a big data source, such as an Access database, an SQL Server database, or even a huge text file. SQL statements in Excel allow you to connect to an external data source, parse fields or table contents, and import data without having to manually enter the data.

After importing external data using SQL commands, you may sort, analyze, and conduct any necessary computations. Here, we will be discussing how to execute SQL statements in MS Excel. For this, an open-source package called ‘xlwings’ is required. So, before we begin with the process of running SQL queries in MS Excel, we will have to install xlwings. For running SQL queries in MS Excel using xlwings, having Windows OS and Python is a must.

Install Xlwings

Make sure you have installed pip for Python beforehand. If not, refer to this GeeksforGeeks link. Once you have installed pip, open your Command Prompt type pip install xlwings, and hit Enter. Once this command is executed completely, type xlwings add-in install and hit Enter. Now, open Excel, and you’ll find xlwings section added.

SQL Queries in Excel

Step 1: Creation of Tables in Excel.

For the execution of SQL queries in Excel, in this article, two tables have been created in Excel (same workbook) and will be used for demonstration of the same. The two tables are – Employee Table and Department Table, as depicted below:

Table 1: Employee Table.

Employee Table

Table 2: Department Table.

Department Table

Step 2: Write the SQL query in Excel.

Type in the SQL query to be executed in Excel. (You may first Merge and center the cells and then type in the SQL query).
Note: When only one table is being referred to, use ‘a’/’A’ for referring to it. If there are two tables, for example, when Joins are used, use ‘a’/’A’ for the first table and use ‘b’/’B’ for referring to the second table.

SQL Query

Step 3: Running the SQL query in Excel.

For executing the SQL query, type in =sql( in a new cell, where you need the retrieved data to be displayed. Then, click on the Insert Function option, displayed to the left of the Formula Bar.

Executing Query

On clicking the Insert Function option, a dialog box appears, which requires 2 inputs – Query and Tables. For the Query input, select the SQL query cell (above step) or simply manually type in the query to be executed.

For the Tables input, hold and drag the entire table to be used for the SQL query. If there is more than one table, add the table(s) in a similar fashion in the Tables input. After this, click on the Ok button, and presto, the data is retrieved!

function-argument-shown

Output: Now you can see the output of the SQL Query.

output

More Sample SQL Queries in Excel

SELECT STATEMENT SQL: The SELECT statement is used to get information from a database. The information returned is saved in a result table known as the result set.

Select statement syntax: SELECT Age FROM a

Output

SELECT Name, Gender FROM a

Output

WHERE CLAUSE SQL: To filter records, use the WHERE clause. It is used to extract only records that meet a predefined requirement.

Where clause syntax: SELECT * FROM a WHERE Gender = ‘Female’

Output

OR OPERATOR: The OR operators are used to filter records based on several criteria. If any of the conditions separated by OR is TRUE, the OR operator shows a record.

Or operator syntax: SELECT * FROM a WHERE Gender = ‘MALE’ OR Age < 40

Output

NOT OPERATOR: If the condition(s) is NOT TRUE, the NOT operator shows a record.

Not operator syntax: SELECT * FROM a WHERE NOT Gender = ‘Female’

Output

MIN() FUNCTION: The MIN() method returns the column with the lowest value.

Min function syntax: SELECT MIN(Age) FROM a

Output

AVERAGE FUNCTION SQL: AVG() returns the average value of a numerical column.

Avg function syntax: SELECT AVG(Age) FROM a

Output

GROUP BY STATEMENT: The SQL GROUP BY clause is used in conjunction with the SELECT statement to group identical data. In a SELECT statement, the GROUP BY clause comes after the WHERE clause and before the ORDER BY clause.