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.

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;

See SQL SELECT for Beginners for more examples.

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');

See SQL INSERT for Beginners for more examples.

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...'

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...'

WHERE ArtistId = '7';

See SQL UPDATE for Beginners for more examples.

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;

See SQL DELETE for Beginners for more examples.

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;

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.

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));

See SQL CREATE TABLE for Beginners for more examples.

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;

See SQL ALTER TABLE for Beginners for more examples.

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;

See SQL DROP TABLE for Beginners for more examples.

Using the GUI

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

Further Reference

Also see: