SQL Syntax

A database contains one or more tables. Each table is identified by a name. Tables contains data in the form of columns and rows. Most of the actions we performed on a database are done with using SQL statements. SQL statement informs the database that what information we would like to retrieve or what operation we want to perform on the data.

SQL statement end with a semi colon (;), which specifies the RDBMS that it is the end of SQL statement. We can write more than one SQL statements together, but we should separate each one of them with semicolon so that the database management system knows that they are different SQL statements. This way RDBMS can serve more than one SQL queries in a single database call.

SQL Keywords are NOT case sensitive?

SQL keywords are NOT case sensitive. select is same as SELECT. The table name and database name may be case-sensitive, and it depends on the operating Systems. For Windows, they are not case-sensitive. Coming to Unix, Linux, they are case-sensitive. Generally, keywords are used in upper case to understand easily and to maintain difference with other text in statement. Keywords have their own pre-defined meaning in SQL Language. Some keywords are abbreviated too.

Consider the SQL statement retrieving records from the table

SELECT required_column1_name, required_column2_name FROM table_name; 

This can be also written as

select required_column1_name, required_column2_name from table_name; 

In the above example, both SELECT and select are same. However, as discussed above, the case sensitivity depends on the operating where the query is executing.

SQL Statement

SQL Statement start with the keyword like SELECT, CREAT, UPDATE, DELETE. SQL Statement is Combination of keywords and identifiers in an order, end with semicolon (;).

In SQL, table names, column names have no rules, however names should be meaningful and distinguishable. User should maintain one case throughout querying the database that is either the upper case or lower case.

Valid Syntax

Most commonly used command in SQL statement is SELECT. The Basic SQL query, to fetch all details from one table is shown as follows -

SELECT * FROM table_name;

To fetch particular column's information in particular table the statement needs to write is as shown below -

SELECT column 1, column 2 FROM table_name;

Semicolon required after SQL statement?

The answer for the above query is "Required" for some databases and "Not required" for some databases. However, it is always advisable to use semicolon at the end of SQL statement. Using the semicolon is the standard way to separate SQL statements if more than one executed at a time.

SQL Statements Syntax Examples

Some of the most used statement syntaxes are specified below -

SELECT Statement -

SELECT
       required_column1_name,
       required_column2_name,
       ....,   
       required_columnN_name 
FROM   table_name; 

SELECT with DISTINCT Clause -

SELECT DISTINCT 
       required_column1_name,
       required_column2_name,
       ....,  
       required_columnN_name 
FROM   table_name; 

SELECT with WHERE Clause -

SELECT   
      required_column1_name,
      required_column2_name,
      ....,  
      required_columnN_name
FROM   table_name
WHERE <condition>; 

SELECT with AND/OR Clause -

SELECT
     required_column1_name,
     required_column2_name,
     ....,
     required_columnN_name
FROM   table_nam
WHERE <condition1> {AND|OR} <condition-2>;

SELECT with IN Clause -

SELECT
     required_column1_name,
     required_column2_name,
     ....,
    required_columnN_name
FROM   table_nam
WHERE maching_column_name IN (value1, value2,..., valueN);

SELECT with BETWEEN Clause -

SELECT
     required_column1_name,
     required_column2_name,
     ....,
     required_columnN_name 
FROM   table_name 
WHERE maching_column_name BETWEEN value1 AND value2;

SELECT with LIKE Clause -

SELECT
     required_column1_name,
     required_column2_name,
     ....,
     required_columnN_name 
FROM   table_name
WHERE maching_column_name LIKE "pattern"; 

SELECT with ORDER BY Clause -

SELECT  
    required_column1_name,
    required_column2_name,
    ....,
    required_columnN_name
FROM   table_name
WHERE <condition>
ORDER BY order_column_name {ASC|DESC};

SELECT with GROUP BY Clause -

SELECT
SUM(column_name)
FROM   table_name
WHERE <condition>
GROUP BY column_name; 

SELECT with COUNT Clause-

SELECTCOUNT(column_name)
FROM   table_name 
WHERE  <condition>; 

SELECT with HAVING Clause -

SELECT SUM(column_name)
FROM   table_name
WHERE <condition>
GROUP BY column_name
HAVING <arithmetic_expression>;

CREATE TABLE Statement -

CREATE TABLE table_name(
column1_name datatype,
column2_name datatype,
column3_name datatype,
.....,
columnN_name datatype,
PRIMARY KEY(required_column1_name, required_column2_name,….)
); 

DROP TABLE Statement -

DROP TABLE table_name;

CREATE INDEX Statement -

CREATE UNIQUE INDEX index_name
ON table_name (column1_name, column2_name,…., columnN_name); 

DROP INDEX Statement -

ALTER TABLE table_name
DROP INDEX index_name; 

TRUNCATE TABLE Statement -

TRUNCATE TABLE table_name;

ALTER TABLE Statement -

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

ALTER TABLE Statement to Rename table -

ALTER TABLE table_name RENAME TO new_table_name; 

INSERT INTO Statement -

INSERT INTO table_name (
                     column1_name,
                     column2_name,
                     ....,
                     columnN_name)
    VALUES (value1, value2....valueN); 

UPDATE Statement -

UPDATE table_name
SET column1_name = value1, column2_name = value2,...., 
                                  columnN_name=valueN 
[ WHERE  <condition> ]; 

DELETE Statement -

DELETE FROM  table_name
WHERE  <condition>; 

CREATE DATABASE Statement -

CREATE DATABASE database_name;

DROP DATABASE Statement-

DROP  DATABASE  database_name;

USE Statement -

USE database_name;

COMMIT Statement -

COMMIT;

ROLLBACK Statement -

ROLLBACK;