Extraction from Databases| SQL

Introduction

• SQL stands for Structured Query Language which is used to query, update, and manage Relational Databases (RDBMS) and extract data. It is an essential skill for Data Scientists and Analysts as Relational Databases are very common in organizations due to their simplicity and ease of maintenance.
• A Relational Database (RDBMS) is a collection of data items with pre-defined relationships with them. It organizes data in the form of tables, i.e. in the form of rows and columns. In RDBMS, the schema for each feature is pre-defined. A few of the most common Relational Databases are - MySQL, Oracle, etc.
• Relational Databases are managed and interacted with using SQL language. So to extract data from Relational Database(s), you need to write queries using SQL statements and clauses.

SQL Statements

One of the main advantages of SQL is its simple syntax and learning curve. To extract data from RDBMS, you need to write SQL statements which include _clauses, table names, field names, logical expressions,_ etc. As shown in the below table, there is a basic order for clauses you need to follow while writing SQL statements for data extraction.

Let’s see some examples of SQL queries to understand how to use the above clauses. Suppose we have a table named _student_ as shown below -

Suppose, if you want to display student name, their marks, section when their grade is A, you can use below query -

The above query will display below table -

If you want to display average marks for each section, you can use the GROUP BY clause as shown in the below query -

Above query will display below table -

Now, let’s say you want to display section where average marks are >= 80. Then you can use the below query -

Now, if you want to display the above table in ascending orders of average marks, then you can use the below query -

If you want to display the section with highest average marks, you can use the query below.

Filtering Data with SQL

In SQL, WHERE clause is used to filter data. Let’s get into some of the methods which you can use in the WHERE clause to filter data -

Using ‘=’ Expression

It is an equal sign which can be used to filter data when a column is equal to the specified value. Below is one example query for the equal operator -

Using Relational Expressions

Below are the relational expressions which you can use in WHERE clause for data filtering -

• > or <: select rows with greater or less than specified value
• >=: select rows with greater than or equal to specified value
• <=: select rows with less than or equal to specified value

Let’s have a look at the example mentioned below -

Using Logical Expressions

Below are the logical expressions you can use in the WHERE clause to filter data -

• AND (&) - When two specified conditions are met
• OR (|) - When any of the two specified conditions is met
• NOT - When a specified condition is negated

Let’s have a look at below SQL query to understand how logical expression works -

Using IN Operator

IN operator can be used to filter data when you want to specify a limited number of options to match for a column. Below is a query that uses IN operator to filter data

Using BETWEEN Operator

The BETWEEN operator is used when you want to specify a range in filtering condition.

Using LIKE Operator

The LIKE operator is used on String columns when you want to match a substring to filter input data.

Extraction From Multiple Tables

In a real-world scenario, data is stored in disparate databases, and you must extract data from multiple tables. One way of extracting data from multiple tables is by using JOIN clauses. JOIN clauses combine data from multiple tables based on matching conditions of specified key(s). There are various types of join -

• Left Join
• Right Join
• Inner Join
• Outer Join

Suppose we have another table named _student_details_ containing the student’s city and age details as shown below -

Now, if we want to fetch a student's city and age and combine it with another table, we can use a join operation. We can join these two tables based on _StudentID_ as it is a common key between these two tables. Let’s use INNER join for our case. Inner Join will display results only for those _StudentID_ which are present in both tables.

Combined Extraction

Another way to combine data from multiple tables is by using the UNION clause. The UNION operator combines data from multiple tables using multiple SELECT commands. While applying the UNION clause, you must ensure that columns in each SELECT statement should be of the same data types and in the same order.

General SQL Conventions

• SQL is not case sensitive. So it can be written in lower or upper case letters. However, it is good practice to write SQL clauses such as SELECT, WHERE, FROM, etc. in uppercase letters.
• Stored data in tables is case-sensitive. So while using LIKE conditions to match a substring, you must ensure that the specified string is in the required case order.
• You should avoid spaces in table and column names.
• SQL ignores white space in code, and no indentation is required while writing SQL queries.
• It is considered a good practice to write comments in your SQL queries.