Basic queries to get started on SQL

The Case clause is similar to the IF-ELSE statement in Excel.

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.


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.


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


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.


SELECT column1, column2, ….

FROM table1 outer

WHERE column1 operator

                    (SELECT column1, column2

                     FROM table2

                     WHERE expr1 = 


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.



    WHEN condition1 THEN result1

    WHEN condition2 THEN result2

    WHEN condition THEN resultN

    ELSE result


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. 


INSERT INTO table_name (column_list)






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. 


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.


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.

Download our Mobile App

Avi Gopani
Avi Gopani is a technology journalist that seeks to analyse industry trends and developments from an interdisciplinary perspective at Analytics India Magazine. Her articles chronicle cultural, political and social stories that are curated with a focus on the evolving technologies of artificial intelligence and data analytics.

Subscribe to our newsletter

Join our editors every weekday evening as they steer you through the most significant news of the day.
Your newsletter subscriptions are subject to AIM Privacy Policy and Terms and Conditions.

Our Recent Stories

Our Upcoming Events

3 Ways to Join our Community

Telegram group

Discover special offers, top stories, upcoming events, and more.

Discord Server

Stay Connected with a larger ecosystem of data science and ML Professionals

Subscribe to our Daily newsletter

Get our daily awesome stories & videos in your inbox

Can OpenAI Save SoftBank? 

After a tumultuous investment spree with significant losses, will SoftBank’s plans to invest in OpenAI and other AI companies provide the boost it needs?

Oracle’s Grand Multicloud Gamble

“Cloud Should be Open,” says Larry at Oracle CloudWorld 2023, Las Vegas, recollecting his discussions with Microsoft chief Satya Nadella last week.