Most Useful SQL Commands List with Examples


SQL or Structured Query Language is a standard language used to to create, list, update, delete databases, tables, records. It provides this functionality with different type of commands. In this tutorial we will learn popular and useful SQL commands.

Command Types

SQL language is very advanced language which provides large command set. The commands are categorized according to their usage. Here is types of SQL commands.

  • DDL – Database Definition Language is used to create,change,delete database, table.
  • DML – Data Manipulation Language is used to manupilate data in the database and tables
  • DQL – Data Query Language is SELECt and similar query commands.
  • DCL – Data Control Language is used to to grant and revoke privileges

ALTER TABLE – Change Table Structure

In SQL tables are created at the start and generally used for long times. But if we need to change like adding new column to table we need to use ALTER TABLE . In this example we will add a new column named Age to the table Person. Age column will hold data in integer type.

AND – Logic

SQL provides logical operations like AND . We can use AND check multiple conditions if they are True . In this example we will check if 1=1 and a value is equal to b .

AS – Rename Column Names

After SELECT we can rename the column name whatever we want. We will use AS after the column name. In this example we will rename the column Age as YAS .

AVG Function – Calculate Average Of Column Values

In some cases we may need to make simple arithmetic calculations like average. We can calculate average value of given columns with AVG() function. In this example we will calculate average value of the Age column.

BETWEEN – Defined Range For Where

While printing records we can specify some conditions for a given value for a range. We can use BETWEEN to specify the column value should be between given values. In this example we will print rows where Age is between 18 and 25.

LEARN MORE  How To Create, Use and Delete SQL View?

COUNT – Count Values

We can simply count given column values. Attention this will not sum just COUNT . In this example we will count with Age column not sum.

CREATE TABLE – Create Table

One of the most popular command is CREATE TABLE which is used to create a table from scratch. We will provide the table name, columns and their types. In this example we will create a table named Person and add columns Id , Name , Surname and Age

DELETE – Delete Record/Row From Table

What if we need to delete some record from table. We will use DELETE by providing the condition to select rows we want to delete. In this exmaple we will delete records those Age is equal to 20.

GROUP BY – Group Result

We can group results according to given column. In this exmaple we will group by according to Age .

HAVING – Filter with Functions

HAVING can be used with aggregate functions where WHERE can not be used.

INNER JOIN – Join Multiple tables

If we need to join multiple tables according to same column we can use INNER JOIN . We will just match same column from multiple tables. In this tables we will match Id from tables Person and Student

INSERT – Add New Row/Record

One of the most used operation on SQL databases is record or row insert simply adding record. We will use INSERT in to insert new data into table. In this example we will insert data Name , Surname , Age into table Person .

LEARN MORE  How To Use Mysql / MariaDB From Console?

IS NULL – Check Value

There are different types of data. If the given value is not set it will be NULL . We can check whether given value is null with IS NULL . In this example we will find the records those Age not set properly.

LIKE – Filter Similar Values

If we want to filter according to a patterns we can use LIKE . In this example we will filter Name which starts with A .

LIMIT – Limit Results By Count

When we run a generic query we may get a lot of record which will slow down database and fill the result pane. We can limit listed records wit LIMIT keyword by providing the record count we want to list. In this example we will list first 50 records.

MAX() – Select Maximum Value

We can select maximum value of the given column with MAX() function. In this example we will select the maximum Age from table Person .

MIN() – Select Minimum Value

We can select minimum value of the given column with MIN() function. In this example we will select the minimum Age from table Person .

OR – Logic

SQL supports logic operations like OR . We can or two conditions where meeting one of them will make true. In this example we will list rows where Age is 20 or 30 .

LEARN MORE  How To Connect MySQL Database From Python Application and Execute Sql Query with Examples

ORDER BY – Order By Given Option

We can order selected rows with ORDER BY . In this example we will order by Age column values.

OUTER JOIN – Join Multiple Tables

Outer join is used to join multiple tables where there is not condition met. So every table on the left will be return and merge with right table. If column value not met NULL will be set for the given column.

ROUND() – Round Given Column Values

We can round given column and values. In this example we will round Age column.

SELECT – List Given Records/Rows

The most important is listing records. We will use SELECT which will print all columns with * or columns we provide. In this example we will select columns Name and Age .

SELECT DISTINCT – Return Unique Values

We can select given column in a unique way. We will use columns Name .

SUM() – Sum Given Column Values

We can sum given column values. In this example we will sum Age column.

UPDATE – Update Record/Row

We can update given row value with UPDATE . In this example we will update the Age of the record İsmail .

WHERE – Filter Results

We need to generally specify some conditions about the operations . We can use WHERE in order to specify the condition with the helpers like LIKE , BETWEEN  etc.