Bioinformatics ReviewBioinformatics Review
Notification Show More
Font ResizerAa
  •  Home
  • Docking
  • MD Simulation
  • Tools
  • More Topics
    • Softwares
    • Sequence Analysis
    • Algorithms
    • Bioinformatics Programming
    • Bioinformatics Research Updates
    • Drug Discovery
    • Phylogenetics
    • Structural Bioinformatics
    • Editorials
    • Tips & Tricks
    • Bioinformatics News
    • Featured
    • Genomics
    • Bioinformatics Infographics
  • Community
    • BiR-Research Group
    • Community Q&A
    • Ask a question
    • Join Telegram Channel
    • Join Facebook Group
    • Join Reddit Group
    • Subscription Options
    • Become a Patron
    • Write for us
  • About Us
    • About BiR
    • BiR Scope
    • The Team
    • Guidelines for Research Collaboration
    • Feedback
    • Contact Us
    • Recent @ BiR
  • Subscription
  • Account
    • Visit Dashboard
    • Login
Font ResizerAa
Bioinformatics ReviewBioinformatics Review
Search
Have an existing account? Sign In
Follow US
Bioinformatics ProgrammingSQL

Nested SQL queries and aggregate functions for complex information retrieval from a database

Dr. Muniba Faiza
Last updated: October 1, 2020 12:00 am
Dr. Muniba Faiza
Share
4 Min Read
SQL queries for complex information retrieval
SHARE

In one of our previous articles, we have mentioned a few basic SQL queries to search in a database. In this article, we have described a few nested SQL queries and aggregate functions for information retrieval from a biological database.

Contents
1. To retrieve a limited number of records from a database.2. To retrieve the minimum and maximum values from the selected columnTo find the minimum valueTo find the maximum value3. Aggregate functions in SQLTo find the number of rows matching a conditionTo find the average value of a numeric column in a tableTo find the sum of a numeric column4. To search for a specific pattern in a column5. To specify multiple values in a WHERE clause6. To select records within a given range in a table

1. To retrieve a limited number of records from a database.

>SELECT TOP number * FROM table_name;

For example, you have a table named sequences in your database having different columns such as OrgName, Sequence, OrgNumber, and so on. Since a single species may consist of multiple sequences and you want to retrieve only the top 3 sequences of that species, then use the following query.

>SELECT TOP 3 * FROM sequences;

In MySQL, the LIMIT clause does the same thing,

>SELECT column_names * FROM table_name

WHERE condition LIMIT number;

For example,

>SELECT * FROM sequences

WHERE OrgName=’Arabidopsis thaliana’

LIMIT 3;

2. To retrieve the minimum and maximum values from the selected column

To find the minimum value

>SELECT MIN(column_name) FROM table_name

WHERE condition;

For example,

>SELECT MIN(strains) AS MinimumStrains

FROM sequences;

To find the maximum value

>SELECT MAX(column_name) FROM table_name

WHERE condition;

For example,

>SELECT MAX(strains) AS MinimumStrains

FROM sequences

WHERE OrgName=’Arabidopsis thaliana’;

3. Aggregate functions in SQL

To find the number of rows matching a condition

>SELECT COUNT(column_name)

FROM table_name

WHERE condition;

For example,

>SELECT COUNT(OrgNumber)

FROM sequences;

To find the average value of a numeric column in a table

>SELECT AVG(column_name)

FROM table_name

WHERE condition;

For example,

>SELECT AVG(TotSeqs)

FROM sequences;

To find the sum of a numeric column

>SELECT SUM(column_name)

FROM table_name

WHERE condition;

For example,

>SELECT SUM(TotSeqs)

FROM sequences;

4. To search for a specific pattern in a column

>SELECT column1, column2, …

FROM table_name

WHERE column_name LIKE pattern;

There are several other wildcards that can be used with LIKE operator such as ‘*’, ‘[]’, ‘^’, etc. They can be used in combination as well. There are two wildcards mostly used with LIKE operator: ‘%’ and ‘_’.

% represents zero, one, or multiple characters, whereas, _ represents a single character. These wildcards are used before and/or after the specific characters or a pattern you are looking for.

For example, if you wish to search for sequences of a species using a few characters, then use the following query.

>SELECT Sequence, OrgName

FROM sequences

WHERE OrgName LIKE ‘%dopsis%’;

>SELECT Sequence, OrgName

FROM sequences

WHERE OrgName LIKE ‘%thaliana’;

>SELECT Sequence, OrgName

FROM sequences

WHERE OrgName LIKE ‘_ _abidop%’;

5. To specify multiple values in a WHERE clause

>SELECT column1, column2, …

FROM table_name

WHERE column_name IN (value1, value2, …);

For example,

>SELECT * FROM sequences

WHERE OrgName IN (‘Arabidopsis thaliana’, ‘Agrocybe aegerita’, ‘Homo sapiens’);

Or instead of entering values, SELECT statement can also be used as shown below,

>SELECT * FROM sequences

WHERE OrgName IN (SELECT statement);

If you wish to select records that are not present in the given values, then use the following query.

>SELECT * FROM sequences

WHERE OrgName NOT IN (‘Arabidopsis thaliana’, ‘Agrocybe aegerita’, ‘Homo sapiens’);

6. To select records within a given range in a table

>SELECT column1, column2, …

FROM table_name

WHERE column_name BETWEEN value1 AND value2;

For example,

>SELECT * FROM sequences

WHERE OrgNumber BETWEEN 102 AND 130;

Other SQL queries for complex information retrieval from a database will be described in upcoming articles.

TAGGED:MySQLSQLSQL queries
Share This Article
Facebook Copy Link Print
ByDr. Muniba Faiza
Follow:
Dr. Muniba is a Bioinformatician based in New Delhi, India. She has completed her PhD in Bioinformatics from South China University of Technology, Guangzhou, China. She has cutting edge knowledge of bioinformatics tools, algorithms, and drug designing. When she is not reading she is found enjoying with the family. Know more about Muniba
Leave a Comment

Leave a Reply Cancel reply

You must be logged in to post a comment.

Starting in Bioinformatics? Do This First!
Starting in Bioinformatics? Do This First!
Tips & Tricks
[Editorial] Is it ethical to change the order of authors’ names in a manuscript?
Editorial Opinion
Installing bbtools on Ubuntu
[Tutorial] Installing BBTools on Ubuntu (Linux).
Sequence Analysis Software Tools
wes_data_analysis Whole Exome Sequencing (WES) Data visualization Toolkit
wes_data_analysis: Whole Exome Sequencing (WES) Data visualization Toolkit
Bioinformatics Programming GitHub Python

You Might Also Like

operations on fasta
Bioinformatics ProgrammingFasta

Operations on FASTA files using Perl, PHP, and Bash commands

July 20, 2020
AlgorithmsBioinformatics ProgrammingSoftware

BETSY: A new backward-chaining expert system for automated development of pipelines in Bioinformatics

May 20, 2020
basic sql queries
Bioinformatics Programming

Basic SQL Queries to Create and Retrieve Information from a Database

September 19, 2020
How to convert the PDB file to PSF format?
Bioinformatics ProgrammingPython

How to convert the PDB file to PSF format?

September 22, 2022
Copyright 2024 IQL Technologies
  • Journal
  • Customer Support
  • Contact Us
  • FAQs
  • Terms of Use
  • Privacy Policy
  • Cookie Policy
  • Sitemap
Welcome Back!

Sign in to your account

Username or Email Address
Password

Lost your password?

Not a member? Sign Up