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 obtain SMILES of ligands using PDB ligand IDs?

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?

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.

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

LATEST ISSUE

ADVERT