Introduced in 1974, SQL or Structured Query Language is a domain-specific language used in programming and for managing data held in a relational database management system or for stream processing in a relational data stream management system. It is the most used database environment and the third most popular language.

SQL is the standard language for dealing with relational databases in big tech companies like Facebook, Instagram, Accenture, Dell, etc. Essentially, SQL language is used to query a database.

Below, we look at the eight SQL queries all data scientists should know. 

Where Clause

The WHERE clause filters records based on input statements. Developers can use extract only those records that fulfil a specified condition. They can use the * operator to display the entire table and narrow down the results using the ‘WHERE’ keyword that displays specific rows.

Syntax:

SELECT column1, column2, …

FROM table_name

WHERE [condition]

Date Functions

The usual date format is YYYY-MM-DD, with the date-time format being YYYY-MM-DD HH:MI: SS. The data function allows developers to find a date in the present or future and dissect it into components for the month, the year, the day or a combination facilitating large-scale database design and maintenance.

Syntax:

select current_timestamp; — date and time, standard ANSI SQL so compatible across DBs

select getdate(); — date and time, specific to SQL Server

SQL Joins

A JOIN clause combines rows from two or more tables based on a related column. There are four types of Joins in SQL– the inner, left, right and full. The Inner Join clause creates a new table by combining rows with matching values in two or more tables. The Right Join returns all records from the right table matching with the left table, and the Left Join returns all records from the left table matching with the right. Lastly, the Full Join returns all records when there is a match in the left or right table.

Syntax for Inner Join:

SELECT column_name(s)

FROM table1

INNER JOIN table2

ON table1.column_name = table2.column_name;

Correlated Subqueries

Correlated Subqueries are used for row-by-row processing and select data from a table referenced in the outer query. The function works where each subquery is executed once for every outer query row. This allows developers to read every row in a table and compare their values against related data. A correlated subquery is evaluated once for every row processed by the parent statement, such as SELECT, UPDATE, or DELETE.

Syntax:

SELECT column1, column2, ….

FROM table1 outer

WHERE column1 operator

                    (SELECT column1, column2

                     FROM table2

                     WHERE expr1 = 

                               outer.expr2);

Case Clause 

The CASE clause implements the logic when the developer wants to set the value of one column based on the values in other columns. The function goes through conditions and returns a value when the first condition is met. It works till it finds a true condition, and if no condition is true, it returns the value in the ELSE clause. The Case clause is similar to the IF-ELSE statement in Excel.

Syntax:

CASE

    WHEN condition1 THEN result1

    WHEN condition2 THEN result2

    WHEN condition THEN resultN

    ELSE result

END;

Insert Values/ Rows into Tables

This query allows developers to easily insert multiple values or rows in a table with a single query. Inserting values in a table is a DML (Data manipulation language) operation to add data to the database. 

Syntax:

INSERT INTO table_name (column_list)

VALUES

    (value_list_1),

    (value_list_2),

    …

    (value_list_n);

SQL Queries to Retrieve All Data From A Table

One of the most used DLM commands, the SELECT query, is used to select data from a database, and the retrieved data is stored in a result table called the result-set. The query returns a result set of records from one or more tables. 

Syntax:

SELECT column1, column2, …

FROM table_name;

Get Average Values for Data

The average value syntax helps developers find the average value in the given data. It is an aggregate function that calculates the average value of a numerical dataset. The function can be used with the SELECT query for retrieving data from a table.

Syntax:

SELECT AVG( column_name ) FROM table_name

Find Duplicate records

This query helps developers find duplicate records in the table using a unique identifier. It is done via the GROUP BY statement in SQL that arranges identical data into groups. For instance, if a column has the same values in different rows, the tool will arrange those rows in a group. Duplicate values can be found in two steps: 

• Define criteria for duplicates.
• Write a query to search for duplicates.