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.

ALTER TABLE Person ADD Age int;

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 .

SELECT * FROM Person WHERE 1=1 AND a=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 .

SELECT Age AS YAS FROM Person;

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.

SELECT AVG(Age) FROM Person;

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.

SELECT * FROM Person WHERE Age BETWEEN 18 AND 25;

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.

SELECT COUNT(Age) FROM Person;

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

CREATE TABLE Person (
Id int,
Name varchar;
Surname varchar,
Age int
);

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.

DELETE FROM Person WHERE Age=20;

GROUP BY – Group Result

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

SELECT Age, COUNT(*) FROM Person BROUP BY Age;

HAVING – Filter with Functions

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

SELECT Age, COUNT(*) FROM Person GROUP BY Age HAVING COUNT(*) > 20;

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

SELECT Name, Surname, Age FROM Person JOIN Student ON Person.Id = Student.Id;

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 .

INSERT INTO Person (Name, Surname,Age) VALUES ("Ahmet Ali","Baydan","6");

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.

SELECT * FROM Person WHERE Age IS NULL;

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 .

SELECT * FROM Person WHERE LIKE '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.

SELECT * FROM Person LIMIT 50;

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 .

SELECT MAX(Age) FROM 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 .

SELECT MIN(Age) FROM 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 .

SELECT * FROM Person WHERE Age=20 OR Age=30;

ORDER BY – Order By Given Option

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

SELECT * FROM Person ORDER BY Age;

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.

SELECT * FROM Person LEFT JOIN Student ON Person.Id = Student.Id;

ROUND() – Round Given Column Values

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

SELECT ROUND(Age) FROM Person;

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 Name, Age FROM Person;

SELECT DISTINCT – Return Unique Values

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

SELECT DISTINCT Name FROM Person;

SUM() – Sum Given Column Values

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

SELECT SUM(Age) FROM Person;

UPDATE – Update Record/Row

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

UPDATE Person SET Age=34 WHERE Name='İ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.

SELECT * FROM Person WHERE Age < 30;