SQL - Syntax

---

---

What is SQL Syntax?

SQL syntax is a unique set of rules and guidelines to be followed while writing SQL statements. This tutorial gives you a quick start with SQL by listing all the basic SQL Syntax.

All the SQL statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW and all the statements end with a semicolon (;).

Case Sensitivity

The most important point to be noted here is that SQL is case insensitive, which means SELECT and Select have same meaning in SQL statements. Whereas, MySQL makes difference in table names. So, if you are working with MySQL, then you need to give table names as they exist in the database.

SQL Table

Let us consider a table with the name CUSTOMERS shown below, and use it as a reference to demonstrate all the SQL Statements on the same.

SQL Statements

This tutorial lists down various SQL statements. Most of them are compatible with MySQL, Oracle, Postgres and SQL Server databases.

All the SQL statements require a semicolon (;) at the end of each statement. Semicolon is the standard way to separate different SQL statements which allows to include multiple SQL statements in a single line.

All the SQL Statements given in this tutorial have been tested with a MySQL server on Linux and Windows.

SQL CREATE DATABASE Statement

To store data within a database, you first need to create it. This is necessary to individualize the data belonging to an organization.

You can create a database using the following syntax −

CREATE DATABASE database_name;

Let us try to create a sample database sampleDB in SQL using the CREATE DATABASE statement −

CREATE DATABASE sampleDB

SQL USE Statement

Once the database is created, it needs to be used in order to start storing the data accordingly. Following is the syntax to change the current location to required database −

USE database_name;

We can set the previously created sampleDB as the default database by using the USE statement in SQL −

USE sampleDB;

SQL DROP DATABASE Statement

If a database is no longer necessary, you can also delete it. To delete/drop a database, use the following syntax −

DROP DATABASE database_name;

You can also drop the sampleDB database by using the DROP DATABASE statement in SQL −

DROP DATABASE sampleDB;

SQL CREATE TABLE Statement

In an SQL driven database, the data is stored in a structured manner, i.e. in the form of tables. To create a table, following syntax is used −

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

The following code block is an example, which creates a CUSTOMERS table given above, with an ID as a primary key and NOT NULL are the constraints showing that these fields cannot be NULL while creating records in this table −

CREATE TABLE CUSTOMERS(
   ID   INT              NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT              NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

SQL DESC Statement

Every table in a database has a structure of its own. To display the structure of database tables, we use the DESC statements. Following is the syntax −

DESC table_name;

The DESC Statement, however, only works in few RDBMS systems; hence, let us see an example by using DESC statement in the MySQL server −

DESC CUSTOMERS;

SQL INSERT INTO Statement

The SQL INSERT INTO Statement is used to insert data into database tables. Following is the syntax −

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

The following example statements would create seven records in the empty CUSTOMERS table.

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500),
(3, 'kaushik', 23, 'Kota', 2000),
(4, 'Chaitali', 25, 'Mumbai', 6500),
(5, 'Hardik', 27, 'Bhopal', 8500),
(6, 'Komal', 22, 'Hyderabad', 4500),
(7, 'Muffy', 24, 'Indore', 10000);

SQL SELECT Statement

In order to retrieve the result-sets of the stored data from a database table, we use the SELECT statement. Following is the syntax −

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

To retrieve the data from CUSTOMERS table, we use the SELECT statement as shown below.

SELECT * FROM CUSTOMERS;

SQL UPDATE Statement

When the stored data in a database table is outdated and needs to be updated without having to delete the table, we use the UPDATE statement. Following is the syntax −

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

To see an example, the following query will update the ADDRESS for a customer whose ID number is 6 in the table.

UPDATE CUSTOMERS SET ADDRESS = 'Pune' WHERE ID = 6;

SQL DELETE Statement

Without deleting the entire table from the database, you can also delete a certain part of the data by applying conditions. This is done using the DELETE FROM statement. Following is the syntax −

DELETE FROM table_name WHERE  {CONDITION};

The following code has a query, which will DELETE a customer, whose ID is 6.

DELETE FROM CUSTOMERS WHERE ID = 6;

SQL DROP TABLE Statement

To delete a table entirely from a database when it is no longer needed, following syntax is used −

DROP TABLE table_name;

This query will drop the CUSTOMERS table from the database.

DROP TABLE CUSTOMERS;

SQL TRUNCATE TABLE Statement

The TRUNCATE TABLE statement is implemented in SQL to delete the data of the table but not the table itself. When this SQL statement is used, the table stays in the database like an empty table. Following is the syntax −

TRUNCATE TABLE table_name;

Following query delete all the records of the CUSTOMERS table −

TRUNCATE TABLE CUSTOMERS;

SQL ALTER TABLE Statement

The ALTER TABLE statement is used to alter the structure of a table. For instance, you can add, drop, and modify the data of a column using this statement. Following is the syntax −

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

Following is the example to ADD a New Column to the CUSTOMERS table using ALTER TABLE command −

ALTER TABLE CUSTOMERS ADD SEX char(1);

SQL ALTER TABLE Statement (Rename)

The ALTER TABLE statement is also used to change the name of a table as well. Use the syntax below −

ALTER TABLE table_name RENAME TO new_table_name;

Following is the example to RENAME the CUSTOMERS table using ALTER TABLE command −

ALTER TABLE CUSTOMERS RENAME TO NEW_CUSTOMERS;

SQL DISTINCT Clause

The DISTINCT clause in a database is used to identify the non-duplicate data from a column. Using the SELECT DISTINCT statement, you can retrieve distinct values from a column. Following is the syntax −

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

As an example, let us use the DISTINCT keyword with a SELECT query. The repetitive salary 2000.00 will only be retrieved once and the other record is ignored.

SELECT DISTINCT SALARY FROM CUSTOMERS ORDER BY SALARY;

SQL WHERE Clause

The WHERE clause is used to filter rows from a table by applying a condition. Following is the syntax to retrieve filtered rows from a table −

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

The following query is an example to fetch all the records from CUSTOMERS table where the salary is greater than 2000, using the SELECT statement −

SELECT ID, NAME, SALARY 
FROM CUSTOMERS 
WHERE SALARY > 2000;

SQL AND/OR Operators

The AND/OR Operators are used to apply multiple conditions in the WHERE clause. Following is the syntax −

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

The following query is an example to fetch all the records from CUSTOMERS table where the salary is greater than 2000 AND age is less than 25, using the SELECT statement −

SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE SALARY > 2000 AND age < 25;

SQL IN Clause

The IN Operator is used to check whether the data is present in the column or not, using the WHERE clause. Following is the syntax −

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

For an example, we want to display records with NAME equal to 'Khilan', 'Hardik' and 'Muffy' (string values) using IN operator as follows −

SELECT * FROM CUSTOMERS 
WHERE NAME IN ('Khilan', 'Hardik', 'Muffy');

SQL BETWEEN Clause

The BETWEEN Operator is used to retrieve the values from a table that fall in a certain range, using the WHERE clause. Following is the syntax −

SELECT column1, column2....columnN
FROM   table_name
WHERE  column_name BETWEEN val-1 AND val-2;

Let us try to the BETWEEN operator to retrieve CUSTOMERS records whose AGE is between 20 and 25.

SELECT * FROM CUSTOMERS WHERE AGE BETWEEN 20 AND 25;

SQL LIKE Clause

The LIKE Operator is used to retrieve the values from a table that match a certain pattern, using the WHERE clause. Following is the syntax −

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

As an example, let us try to display all the records from the CUSTOMERS table, where the SALARY starts with 200.

SELECT * FROM CUSTOMERS WHERE SALARY LIKE '200%';

SQL ORDER BY Clause

The ORDER BY Clause is used to arrange the column values in a given/specified order. Following is the syntax −

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

In the following example we are trying to sort the result in an ascending order by the alphabetical order of customer names −

SELECT * FROM CUSTOMERS ORDER BY NAME ASC;

SQL GROUP BY Clause

The GROUP BY Clause is used to group the values of a column together. Following is the syntax −

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

We are trying to group the customers by their age and calculate the average salary for each age group using the following query −

SELECT ADDRESS, AGE, SUM(SALARY) 
AS TOTAL_SALARY FROM CUSTOMERS 
GROUP BY ADDRESS, AGE;

SQL COUNT Function

The COUNT Function gives the number of non-null values present in the specified column. Following is the syntax −

SELECT COUNT(column_name)
FROM   table_name
WHERE  CONDITION;

Let us see an example −

SELECT AGE, COUNT(Name) FROM CUSTOMERS GROUP BY AGE;

SQL HAVING Clause

The HAVING clause is also used to filter a group of rows by applying a condition. Following is the syntax −

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

In the following example, we are trying to retrieve all records from the CUSTOMERS table where the sum of their salary is greater than 5000 −

SELECT ADDRESS, AGE, SUM(SALARY) AS 
TOTAL_SALARY FROM CUSTOMERS GROUP BY 
ADDRESS, AGE HAVING TOTAL_SALARY >=5000 
ORDER BY TOTAL_SALARY DESC;

SQL CREATE INDEX Statement

To create an index on a database table, SQL provides the CREATE INDEX statement. Following is the syntax −

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

Let us create an index for the column named 'NAME' in the existing CUSTOMERS table using the following query −

CREATE INDEX sample_index on CUSTOMERS(NAME);

SQL DROP INDEX Statement

The DROP INDEX statement is used to drop an index from a table. Following is the syntax −

DROP INDEX index_name ON table_name;

Let us drop the index we created previously for the column named 'NAME' in the existing CUSTOMERS table using the following query −

DROP INDEX sample_index on CUSTOMERS;