SQL Queries Syntax

SQL SELECT Statement:

SELECT column1, column2....columnN

FROM   table_name;

SQL DISTINCT Clause:

SELECT DISTINCT column1, column2....columnN

FROM   table_name;

SQL WHERE Clause:

SELECT column1, column2....columnN

FROM   table_name

WHERE  CONDITION;

SQL AND/OR Clause:

SELECT column1, column2....columnN

FROM   table_name

WHERE  CONDITION-1{AND|OR} CONDITION-2;

SQL IN Clause:

SELECT column1, column2....columnN

FROM   table_name

WHERE  column_name IN (val-1, val-2,...val-N);

SQL BETWEEN Clause:

SELECT column1, column2....columnN

FROM   table_name

WHERE  column_name BETWEEN val-1 AND val-2;

SQL LIKE Clause:

SELECT column1, column2....columnN

FROM   table_name

WHERE  column_name LIKE { PATTERN };

SQL ORDER BY Clause:

SELECT column1, column2....columnN

FROM   table_name

WHERE  CONDITION

ORDER BY column_name{ASC|DESC};

SQL GROUP BY Clause:

SELECT SUM(column_name)

FROM   table_name

WHERE  CONDITION

GROUP BY column_name;

SQL COUNT Clause:

SELECT COUNT(column_name)

FROM   table_name

WHERE  CONDITION;

SQL HAVING Clause:

SELECT SUM(column_name)

FROM   table_name

WHERE  CONDITION

GROUP BY column_name

HAVING (arithematicfunction condition);

SELECT COUNT(*)

FROM SAMP.STAFF

GROUP BY ID

HAVING SALARY > 15000

 

SQL CREATE TABLE Statement:

CREATE TABLE table_name(

column1datatype,

column2datatype,

column3datatype,

.....

columnNdatatype,

PRIMARY KEY( one or more columns )

);

SQL DROP TABLE Statement:

DROP TABLE table_name;

SQL CREATE INDEX Statement :

CREATE UNIQUE INDEX index_name

ON table_name( column1, column2,...columnN);

SQL DROP INDEX Statement :

ALTER TABLE table_name

DROP INDEX index_name;

SQL DESC Statement :

DESC table_name;

SQL TRUNCATE TABLE Statement:

TRUNCATE TABLE table_name;

SQL ALTER TABLE Statement:

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

SQL ALTER TABLE Statement (Rename) :

ALTER TABLE table_name RENAME TO new_table_name;

SQL INSERT INTO Statement:

INSERT INTO table_name( column1, column2....columnN)

VALUES ( value1, value2....valueN);

SQL UPDATE Statement:

UPDATE table_name

SET column1 = value1, column2 = value2....columnN=valueN

[ WHERE  CONDITION ];

SQL DELETE Statement:

DELETE FROM table_name

WHERE  {CONDITION};

SQL CREATE DATABASE Statement:

CREATE DATABASE database_name;

SQL DROP DATABASE Statement:

DROP DATABASE database_name;

SQL USE Statement:

USE database_name;

SQL COMMIT Statement:

COMMIT;

SQL ROLLBACK Statement:

ROLLBACK;

SQL Commands:

The standard SQL commands to interact with relational databases are CREATE, SELECT, INSERT, UPDATE, DELETE, and DROP. These commands can be classified into groups based on their nature:

DDL - Data Definition Language:

Command	Description
CREATE	Creates a new table, a view of a table, or other object in database
ALTER	Modifies an existing database object, such as a table.
DROP	Deletes an entire table, a view of a table or other object in the database.
Truncate	Permanently delete table from database
Rename	Rename table name from database

DML - Data Manipulation Language:

Command	Description
INSERT	Creates a record
UPDATE	Modifies records
DELETE	Deletes records
SELECT	Retrieves certain records from one or more tables

DCL - Data Control Language:

Command	Description
GRANT	Gives a privilege to user
REVOKE	Takes back privileges granted from user

 

TCL – Transaction Control Language:

Command	Description
ROLLBACK	Rollback data from recycle bin
ROMMIT	Commit data from database
SAVEPOINT	Save point is used to save data for particular session

The SQL CREATE TABLE statement is used to create a new table.

Syntax:

Basic syntax of CREATE TABLE statement is as follows:

CREATE TABLE table_name(

   column1 datatype,

   column2 datatype,

   column3 datatype,

.....

   columnN datatype,

   PRIMARY KEY( one or more columns )

);

CREATE TABLE is the keyword telling the database system what you want to do.in this case, you want to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement.

Then in brackets comes the list defining each column in the table and what sort of data type it is. The syntax becomes clearer with an example below.

A copy of an existing table can be created using a combination of the CREATE TABLE statement and the SELECT statement.

Example:

Following is an example which creates a CUSTOMERS table with ID as primary key and NOT NULL are the constraints showing that these fileds can not be NULL while creating records in this table:

SQL> 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)

);

The SQL INSERT INTO Statement is used to add new rows of data to a table in the database.

Syntax:

There are two basic syntax of INSERT INTO statement is as follows:

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)]  

VALUES (value1, value2, value3,...valueN);

Here column1, column2,...columnN are the names of the columns in the table into which you want to insert data.

You may not need to specify the column(s) name in the SQL query if you are adding values for all the columns of the table. But make sure the order of the values is in the same order as the columns in the table. The SQL INSERT INTO syntax would be as follows:

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

SQL SELECT Statement is used to fetch the data from a database table which returns data in the form of result table. These result tables are called result-sets.

Syntax:

The basic syntax of SELECT statement is as follows:

SELECT column1, column2, columnN FROM table_name;

Here column1, column2...are the fields of a table whose values you want to fetch. If you want to fetch all the fields available in the field then you can use following syntax:

SELECT * FROM table_name;

The SQL UPDATE Query is used to modify the existing records in a table.

You can use WHERE clause with UPDATE query to update selected rows otherwise all the rows would be effected.

Syntax:

The basic syntax of UPDATE query with WHERE clause is as follows:

UPDATE table_name

SET column1 = value1, column2 = value2...., columnN = valueN

WHERE [condition];

The SQL DELETE Query is used to delete the existing records from a table.

You can use WHERE clause with DELETE query to delete selected rows, otherwise all the records would be deleted.

Syntax:

The basic syntax of DELETE query with WHERE clause is as follows:

DELETE FROM table_name

WHERE [condition];

Share this

Share on FacebookPlus on Google+