SQL Syntax

SQL (Structured Query Language) is used to create, modify and access data or information from a database. To perform these opertaions SQL follows a set of rules and guidelines called as syntax. This tutorial help in understanding all basic SQL syntax. A database is nothing but a collection of information objects. The most commonly used database object is database tables.

Database Tables

A database table is made up of rows and columns. A database table is used to represent real world entity in the form of software enitity. A table holds relative information about some object or concept in real world. Each table in a database is indentified with a unique name.

The column names are called the data fields or attributes (characteristics) of a real world enity. The rows of a table are also knows as record.

In this tutorial, we will use a very simple "Student" database table.
 id | first_name | last_name | age |   subject   
----+------------+-----------+-----+-------------
  1 | Carol      | Dentist   |  15 | FoodTech
  2 | Sam        | Wick      |  19 | Architect
  3 | Daryl      | Dixon     |  13 | Mathematics
  5 | Morgan     | Dee       |  15 | Wrestling
  4 | Rick       | Grimes    |  14 | Physics
The table above contains five records and five columns (id, first_name, last_name, age, subject).

SQL Statements

To perform any action on a database you need to use SQL statements. SELECT statement is used to list all the records in the "Customer" table.
 SELECT * from student;
The output will display all the records in "Student" table.
 id | first_name | last_name | age |   subject   
----+------------+-----------+-----+-------------
  1 | Carol      | Dentist   |  15 | FoodTech
  2 | Sam        | Wick      |  19 | Architect
  3 | Daryl      | Dixon     |  13 | Mathematics
  5 | Morgan     | Dee       |  15 | Wrestling
  4 | Rick       | Grimes    |  14 | Physics
Note: SQL keywords are not case sensitive. So SELECT is same as select. However in some database systems like MySQL, tables names are case sensitive.

When to Write Semicolon at End of SQL Statements?

There are many databases systems out there nowadays. Some of them require a semicolon at end of each SQL statement. Whenever a database server allows more than one SQL statements or queries to be executed at the same time, semicolon must be used to seperate different statements.

SQL Statements Syntax

Syntax of most of the SQL statements is given below. You may not understand anything, but it is ok for now as we are going to learn them in greater detail in upcoming SQL tutorials.

SQL CREATE TABLE Statement Syntax

CREATE TABLE table_name(
column1 datatype,
column2 datatype,
column3 datatype,
.....
columnN datatype,
PRIMARY KEY( one or more columns )
);

SQL DROP TABLE Statement Syntax

DROP TABLE table_name;

SQL SELECT Statement Syntax

SELECT column_name1, column2....columnN
FROM table_name;

SQL WHERE Clause Syntax

SELECT column1, column2....columnN
FROM   table_name
WHERE  CONDITION; 

SQL INSERT INTO Statement Syntax

INSERT INTO table_name( column1, column2....columnN)
VALUES ( value1, value2....valueN); 

SQL UPDATE Statement Syntax

UPDATE table_name
SET column1 = value1, column2 = value2....columnN=valueN
[ WHERE  CONDITION ]; 

SQL DELETE Statement Syntax

DELETE FROM table_name
WHERE  {CONDITION}; 

SQL CREATE DATABASE Statement Syntax

DELETE FROM table_name
CREATE DATABASE database_name; 

SQL DROP DATABASE Statement Syntax

DROP DATABASE database_name; 

SQL DISTINCT Clause Syntax

SELECT DISTINCT column_1, column2....columnN
FROM table_name;

SQL BETWEEN Clause Syntax

SELECT column1, column2....columnN
FROM   table_name
WHERE  column_name BETWEEN value1 AND value2; 

SQL LIKE Clause Syntax

SELECT column1, column2....columnN
FROM   table_name
WHERE  column_name LIKE { PATTERN };

SQL IN Clause Syntax

SELECT column1, column2....columnN
FROM   table_name
WHERE  column_name IN (val-1, val-2,...val-N);

SQL AND/OR Clause Syntax

SELECT column1, column2....columnN
FROM   table_name
WHERE  CONDITION-1 {AND|OR} CONDITION-2;

SQL ORDER BY Clause Syntax

SELECT column1, column2....columnN
FROM   table_name
WHERE  CONDITION
ORDER BY column_name {ASC|DESC};

SQL GROUP BY Clause Syntax

SELECT SUM(column_name)
FROM   table_name
WHERE  CONDITION
GROUP BY column_name;

SQL COUNT Clause Syntax

SELECT COUNT(column_name)
FROM   table_name
WHERE  CONDITION;

SQL HAVING Clause Syntax

SELECT SUM(column_name)
FROM   table_name
WHERE  CONDITION
GROUP BY column_name
HAVING (arithematic function condition);

SQL CREATE INDEX Statement Syntax

CREATE UNIQUE INDEX index_name
ON table_name ( column1, column2,...columnN); 

SQL DROP INDEX Statement Syntax

ALTER TABLE table_name
DROP INDEX index_name; 

SQL DESC Statement Syntax

DESC table_name; 

SQL TRUNCATE TABLE Statement Syntax

TRUNCATE TABLE table_name; 

SQL ALTER TABLE Statement Syntax

ALTER TABLE table_name {ADD|DROP|MODIFY} column_name {data_ype}; 

SQL ALTER TABLE Statement (Rename) Syntax

ALTER TABLE table_name RENAME TO new_table_name; 

SQL USE Statement Syntax

USE database_name; 

SQL COMMIT Statement Syntax

COMMIT; 

SQL ROLLBACK Statement Syntax

ROLLBACK;

Comments