11 Common SQL Statements with Basic Examples

This page contains 11 of the most commonly used SQL statements, along with a basic example of usage.

SQL is the standard language for querying relational databases. Each DBMS has varying levels of compliance with the SQL standard and so I’ve provided a link to a page that shows the specific syntax for each of the most popular RDBMSs.

SQL SELECT Statement

The SELECT statement lets you retrieve data from the database. You can choose one or more tables, as well as which specific columns you want to select data from.

The following example selects two columns from the Artists table.

SELECT ArtistName, ArtistBio

FROM Artists;

The following example selects all columns from the Artists table.

SELECT *

FROM Artists;

For the exact syntax, see SQL SELECT Syntax – Listed by DBMS.

SQL INSERT Statement

The INSERT statement lets you insert new rows into a table (i.e. add new data).

Here, we insert a new row into the Albums table.

INSERT INTO Albums (AlbumName, ArtistId) VALUES 

('Ziltoid the Omniscient',  '12');

This can also be shortened to the following:

INSERT INTO Albums VALUES 

( DEFAULT, 'Ziltoid the Omniscient', '12' );

Using the DEFAULT keyword will populate the first column with the default constraint that has been configured for that column (assuming one has been configured).

To insert multiple rows:

INSERT INTO Albums (AlbumName, ArtistId) VALUES 

('Ziltoid the Omniscient',  '12'),

('Flying in a Blue Dream',  '7');

For the exact syntax, see SQL INSERT Syntax – Listed by DBMS.

SQL UPDATE Statement

The UPDATE statement allows you to update one or more records in the database.

In the following example, we update the value of the ArtistName column in the Artists table.

UPDATE Artists

SET ArtistName = 'The Artist Formerly Known as Prince'

WHERE ArtistName = 'Prince';

Although, it’s usually better practice to use the unique identifier if it’s known. So something like this:

UPDATE Artists

SET ArtistName = 'The Artist Formerly Known as Prince'

WHERE ArtistId = '7';

For the exact syntax, see SQL UPDATE Syntax – Listed by DBMS.

SQL DELETE Statement

The DELETE statement lets you delete specified rows from a table.

The following SQL statement deletes uses a WHERE clause to narrow down the deleted rows.

DELETE FROM Artists

WHERE ArtistId = '6';

Omitting the WHERE clause will delete all rows from the table.

DELETE FROM Artists;

For the exact syntax, see SQL DELETE Syntax – Listed by DBMS.

SQL TRUNCATE Statement

The TRUNCATE statement is used to delete all rows from a table. It is faster than the DELETE statement and uses fewer system and transaction log resources.

The TRUNCATE statement is ideal on larger tables.

TRUNCATE TABLE Artists;

For the exact syntax, see SQL TRUNCATE Syntax – Listed by DBMS.

Note that in SQL Server it’s called the TRUNCATE TABLE statement.

SQL CREATE DATABASE Statement

The CREATE DATABASE statement is used to create a new database.

CREATE DATABASE Movies;

The CREATE DATABASE statement is typically followed up with CREATE TABLE and other statements in order to create a complete database ready for use. See how to create a database from a script in MySQL for an example.

For the exact syntax, see SQL CREATE DATABASE Syntax – Listed by DBMS.

SQL ALTER DATABASE Statement

The ALTER DATABASE statement is used to modify a database, or the files and filegroups associated with the database.

The following SQL statement changes the collation of the Movies database to utf8_unicode_ci.

ALTER DATABASE Movies 

COLLATE utf8_unicode_ci ;

For the exact syntax, see SQL ALTER DATABASE Syntax – Listed by DBMS.

SQL DROP DATABASE Statement

The DROP DATABASE statement is used to drop (remove) a database.

DROP DATABASE Movies;

For the exact syntax, see SQL DROP DATABASE Syntax – Listed by DBMS.

SQL CREATE TABLE Statement

The CREATE TABLE statement allows you to create a table in a database. When you create a table, you also specify the columns and their datatypes, as well as any constraints and other settings that might be required.

Here’s an example of creating a table called Actors with two columns. We also set the ActorId  column as the primary key.

CREATE TABLE Actors (
 ActorId INT NOT NULL AUTO_INCREMENT,
 ActorName VARCHAR(255) NOT NULL,
 PRIMARY KEY (ActorId));

For the exact syntax, see SQL CREATE TABLE Syntax – Listed by DBMS.

SQL ALTER TABLE Statement

The ALTER TABLE statement is used to change the definition of a table.

The following SQL statement adds a column to a table and specifies its data type.

ALTER TABLE Movies

ADD COLUMN YearReleased DATETIME;

For the exact syntax, see SQL ALTER TABLE Syntax – Listed by DBMS.

SQL DROP TABLE Statement

The DROP TABLE statement is used to drop (remove) a table. Simply add the name of the table and the whole table will be removed from the database.

DROP TABLE Actors;

For the exact syntax, see SQL DROP TABLE Syntax – Listed by DBMS.

Using the GUI

Most DBMSs allow you to do all of the above tasks via the graphical user interface (GUI). For example, you can use MySQL Workbench to “point and click” your way through without having to remember the SQL code.