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 Programming

Basic SQL Queries to Create and Retrieve Information from a Database

Dr. Muniba Faiza
Last updated: September 19, 2020 1:32 pm
Dr. Muniba Faiza
Share
3 Min Read
basic sql queries
SHARE

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

Contents
To create a databaseTo drop a databaseTo create a table in a databaseTo insert values in a tableTo drop a tableTo alter tableTo add a columnTo drop a column from a tableTo alter a columnTo retrieve data from a databaseTo select only distinct values (no duplicate records)Retrieve data with a conditionTo update a table with a conditionTo delete record in a tableTo delete all rows from a table

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.

TAGGED:MySQLiSQLSQL 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

How to convert the PDB file to PSF format?
Bioinformatics ProgrammingPython

How to convert the PDB file to PSF format?

September 22, 2022
DockingAnalyzer.py-A Python script to identify ligand binding in protein pockets.
Bioinformatics ProgrammingBioinformatics Research UpdatesDockingDrug DiscoveryFeaturedGitHubPython

DockingAnalyzer.py: A New Python script to Identify Ligand Binding in Protein Pockets.

March 13, 2025
smitostr.py: Python script to convert SMILES to structures.
Bioinformatics ProgrammingCheminformaticsPython

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

November 19, 2022
Bioinformatics ProgrammingPerl

How to concatenate FASTA sequences using Perl?

May 20, 2020
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