Basic SQL Queries to Create and Retrieve Information from a Database

4 mins read
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.

Muniba is a Bioinformatician based in the South China University of Technology. 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 Reply

mega7
Previous Story

Tutorial: Constructing phylogenetic tree using MEGA7

RNA secondary structure prediction tools
Next Story

Most widely used tools for RNA Secondary Structure Prediction

Latest from Bioinformatics Programming

0 $0.00