Connect with us

Bioinformatics Programming

Basic SQL Queries to Create and Retrieve Information from a Database

Dr. Muniba Faiza

Published

on

basic sql queries

In this article, basic SQL queries are described to create, update, drop, and perform other functions on a database.

Open a terminal by pressing Ctrl+Alt+T on Ubuntu or a command prompt (type cmd in the search box) on Windows and type $ mysql.

To create a database

>CREATE DATABASE database_name;

For example,

>CREATE DATABASE testdb;

To drop a database

>DROP DATABASE testdb;

To create a table in a database

>USE database_name;

>CREATE TABLE table_name;

For example,

>CREATE TABLE students;

To insert values in a table

>INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value, ...);

For example,

>INSERT INTO students (StdID, StdName, StdClass, StdAddress)

VALUES (MG20615, John, IX, 2311 North Los Robles Avenue Pasadena California USA);

You can add as many columns as you want to add to the table. If you want to add values in all columns of a table, then there is no need to specify column names, you can only insert values as shown below.

>INSERT INTO table_name

VALUES (value1, value2, value, ...);

To drop a table

>DROP TABLE table_name;

To delete all data inside a table:

>TRUNCATE TABLE table_name;

It will all data inside the table but not the data.

To alter table

To add a column

>ALTER TABLE table_name

ADD column_name datatype;

For example,

>ALTER TABLE students

ADD StdEmail varchar(255);

To drop a column from a table

>ALTER TABLE table_name

DROP column_name;

To alter a column

For SQL Server/MS Access:

>ALTER TABLE table_name

ALTER COLUMN column_name datatype;

For MySQL/Oracle (version <10G):

>ALTER TABLE table_name

MODIFY COLUMN column_name datatype;

To retrieve data from a database

>SELECT column1, column2, column3, ...

FROM table_name;

To select all data:

>SELECT * FROM table_name;

To select only distinct values (no duplicate records)

>SELECT DISTINCT column1, column2, column3, ...

FROM table_name;

Retrieve data with a condition

>SELECT column1, column2, column3, ...

FROM table_name

WHERE condition;

For example,

>SELECT * FROM students

WHERE StdName='John';

In the above example, we have used the ‘=’ operator. You can use other operators such as ‘>’, ‘<‘, ‘>=’, ‘<=’, IN, LIKE, and ‘BETWEEN’ depending upon your condition.

To update a table with a condition

>UPDATE table_name

SET column1 = value1, column2 = value2, ...

WHERE condition;

For example,

>UPDATE students

SET StdName = 'John Smith'

WHERE StdID = MG20615;

To delete record in a table

>DELETE FROM table_name

WHERE condition;

For example,

>DELETE FROM students

WHERE StdName = 'John Smith';

To delete all rows from a table

>DELETE FROM table_name;

It will delete all records from the table without deleting the table itself.

These are some basic SQL queries. More queries will be described in the upcoming articles.

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

Advertisement
Click to comment

You must be logged in to post a comment Login

Leave a Reply

Bioinformatics Programming

Free_Energy_Landscape-MD: Python package to create Free Energy Landscape using PCA from GROMACS.

Dr. Muniba Faiza

Published

on

In molecular dynamics (MD) simulations, a free energy landscape (FEL) serves as a crucial tool for understanding the behavior of molecules and biomolecules over time. It is difficult to understand and plot a meaningful FEL and then extract the time frames at which the plot shows minima. In this article, we introduce a new Python package (Free_Energy_Landscape-MD) to generate an FEL based on principal component analysis (PCA) from MD simulation done by GROMACS [1].

(more…)

Continue Reading

Bioinformatics News

VS_Analysis: A Python package to perform post-virtual screening analysis

Dr. Muniba Faiza

Published

on

VS_Analysis: A Python package to perform post-virtual screening analysis

Virtual screening (VS) is a crucial aspect of bioinformatics. As you may already know, there are various tools available for this purpose, including both paid and freely accessible options such as Autodock Vina. Conducting virtual screening with Autodock Vina requires less effort than analyzing its results. However, the analysis process can be challenging due to the large number of output files generated. To address this, we offer a comprehensive Python package designed to automate the analysis of virtual screening results.

(more…)

Continue Reading

Bioinformatics Programming

vs_interaction_analysis.py: Python script to perform post-virtual screening analysis

Dr. Muniba Faiza

Published

on

vs_interaction_analysis.py: Python script to perform post-virtual screening analysis

Analyzing the results of virtual screening (VS) performed with Autodock Vina [1] can be challenging when done manually. In earlier instances, we supplied two scripts, namely vs_analysis.py [2,3] and vs_analysis_compounds.py [4]. This time, we have developed a new Python script to simplify the analysis of VS results.

(more…)

Continue Reading

Bioinformatics Programming

How to create a pie chart using Python?

Dr. Muniba Faiza

Published

on

How to create a pie chart using Python?

In this article. we are creating a pie chart of the docking score of five different compounds docked with the same protein. (more…)

Continue Reading

Bioinformatics Programming

How to make swarm boxplot?

Dr. Muniba Faiza

Published

on

How to make swarm boxplot?

With the new year, we are going to start with a very simple yet complicated topic (for beginners) in bioinformatics. In this tutorial, we provide a simple code to plot swarm boxplot using matplotlib and seaborn. (more…)

Continue Reading

Bioinformatics Programming

How to obtain ligand structures in PDB format from PDB ligand IDs?

Dr. Muniba Faiza

Published

on

How to obtain ligand structures in PDB format from PDB ligand IDs?

Previously, we provided a similar script to download ligand SMILES from PDB ligand IDs. In this article, we are downloading PDB ligand structures from their corresponding IDs. (more…)

Continue Reading

Bioinformatics Programming

How to obtain SMILES of ligands using PDB ligand IDs?

Dr. Muniba Faiza

Published

on

How to obtain SMILES of ligands using PDB ligand IDs?

Fetching SMILE strings for a given number of SDF files of chemical compounds is not such a trivial task. We can quickly obtain them using RDKit or OpenBabel. But what if you don’t have SDF files of ligands in the first place? All you have is Ligand IDs from PDB. If they are a few then you can think of downloading SDF files manually but still, it seems time-consuming, especially when you have multiple compounds to work with. Therefore, we provide a Python script that will read all Ligand IDs and fetch their SDF files, and will finally convert them into SMILE strings. (more…)

Continue Reading

Bioinformatics Programming

How to get secondary structure of multiple PDB files using DSSP in Python?

Dr. Muniba Faiza

Published

on

How to get secondary structure of multiple PDB files using DSSP in Python?

In this article, we will obtain the secondary structure of multiple PDB files present in a directory using DSSP [1]. You need to have DSSP installed on your system. (more…)

Continue Reading

Bioinformatics Programming

vs_analysis_compound.py: Python script to search for binding affinities based on compound names.

Dr. Muniba Faiza

Published

on

vs_analysis_compound.py: Python script to search for binding affinities based on compound names.

Previously, we have provided the vs_analysis.py script to analyze virtual screening (VS) results obtained from Autodock Vina. In this article, we have provided another script to search for binding affinity associated with a compound. (more…)

Continue Reading

Bioinformatics Programming

How to download files from an FTP server using Python?

Dr. Muniba Faiza

Published

on

How to download files from an FTP server using Python?

In this article, we provide a simple Python script to download files from an FTP server using Python. (more…)

Continue Reading

Bioinformatics Programming

How to convert the PDB file to PSF format?

Dr. Muniba Faiza

Published

on

How to convert the PDB file to PSF format?

VMD allows converting PDB to PSF format but sometimes it gives multiple errors. Therefore, in this article, we are going to convert PDB into PSF format using a different method. (more…)

Continue Reading

Bioinformatics Programming

smitostr.py: Python script to convert SMILES to structures.

Dr. Muniba Faiza

Published

on

smitostr.py: Python script to convert SMILES to structures.

As mentioned in some of our previous articles, RDKit provides a wide range of functions. In this article, we are using RDKit [1] to draw a molecular structure using SMILES. (more…)

Continue Reading

Bioinformatics Programming

How to preprocess data for clustering in MATLAB?

Dr. Muniba Faiza

Published

on

How to preprocess data for clustering in MATLAB?

Data preprocessing is a foremost and essential step in clustering based on machine learning methods. It removes noise and provides better results. In this article, we are going to discuss the steps involved in data preprocessing using MATLAB [1]. (more…)

Continue Reading

Bioinformatics Programming

How to calculate drug-likeness using RDKit?

Dr. Muniba Faiza

Published

on

How to calculate drug-likeness using RDKit?

RDKit [1] allows performing multiple functions on chemical compounds. One is the quantitative estimation of drug-likeness also known as QED properties. These properties include molecular weight (MW), octanol-water partition coefficient (ALOGP), number of hydrogen bond donors (HBD), number of hydrogen bond acceptors (HBA), polar surface area (PSA), number of rotatable bonds (ROTB), number of aromatic rings (AROM), structural alerts (ALERTS). (more…)

Continue Reading

Bioinformatics Programming

sdftosmi.py: Convert multiple ligands/compounds in SDF format to SMILES.

Dr. Muniba Faiza

Published

on

sdftosmi.py: Convert multiple ligands/compounds in SDF format to SMILES?

You can obtain SMILES of multiple compounds or ligands in an SDF file in one go. Here, we provide a simple Python script to do that. (more…)

Continue Reading

Bioinformatics Programming

tanimoto_similarities_one_vs_all.py – Python script to calculate Tanimoto Similarities of multiple compounds

Dr. Muniba Faiza

Published

on

tanimoto_similarities_one_vs_all.py – Python script to calculate Tanimoto Similarities of a compound with multiple compounds

We previously provided a Python script to calculate the Tanimoto similarities of multiple compounds against each other. In this article, we are providing another Python script to calculate the Tanimoto similarities of one compound with multiple compounds. (more…)

Continue Reading

Bioinformatics Programming

tanimoto_similarities.py: A Python script to calculate Tanimoto similarities of multiple compounds using RDKit.

Dr. Muniba Faiza

Published

on

tanimoto_similarities.py: A Python script to calculate Tanimoto similarities of multiple compounds using RDKit.

RDKit [1] is a very nice cheminformatics software. It allows us to perform a wide range of operations on chemical compounds/ ligands. We have provided a Python script to perform fingerprinting using Tanimoto similarity on multiple compounds using RDKit. (more…)

Continue Reading

Bioinformatics Programming

How to commit changes to GitHub repository using vs code?

Published

on

How to commit changes to GitHub repository using vs code?

In this article, we are providing a few commands that are used to commit changes to GitHub repositories using VS code terminal.

(more…)

Continue Reading

Bioinformatics Programming

Extracting first and last residue from helix file in DSSP format.

Dr. Muniba Faiza

Published

on

Extracting first and last residue from helix file in DSSP format.

Previously, we have provided a tutorial on using dssp_parser to extract all helices including long and short separately. Now, we have provided a new python script to find the first and last residue in each helix file. (more…)

Continue Reading

Bioinformatics Programming

How to extract x,y,z coordinates of atoms from PDB file?

Published

on

How to extract x,y,z coordinates of atoms from PDB file?

The x, y, and z coordinates of atoms are provided in the PDB file. One way to extract them is by using the Biopython package [1]. In this article, we will extract coordinates of C-alpha atoms for each residue from the PDB file using Biopython. (more…)

Continue Reading

LATEST ISSUE

ADVERT