Connect with us

Bioinformatics Programming

Basic SQL Queries to Create and Retrieve Information from a Database

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

How to calculate drug-likeness using RDKit?

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?

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

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

LATEST ISSUE

ADVERT