Basic SQL Syntax

Data Definition Language

Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:

1. CREATE DATABASE

The CREATE DATABASE Statement is used to create database.

Syntax:
CREATE DATABASE database_name

If We create database called testdb. Then use following statement

CREATE DATABASE testdb

2. CREATE TABLE

The CREATE TABLE Statement is used to create table.

Syntax:
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
)

If We create table called EMPLOYEE. Then use following statement

CREATE TABLE EMPLOYEE
(
EMP_CODE int,
EMP_NAME varchar(100),
EMP_ADDRESS varchar(500)
)

3. ALTER TABLE

The ALTER TABLE Statement is used to add,modify or delete column in existing table.

-Add Column

Syntax:
ALTER TABLE table_name
ADD column_name datatype

If We add column in EMPLOYEE table called EMP_SAL. Then use following statement

ALTER TABLE EMPLOYEE ADD EMP_SAL int

-Delete Column

Syntax:
ALTER TABLE table_name
DROP COLUMN column_name

If We delete EMP_SAL column in EMPLOYEE table. Then use following statement

ALTER TABLE EMPLOYEE DROP COLUMN EMP_SAL

-Modify Column

Syntax:
ALTER TABLE table_name
MODIFY column_name datatype

If We modify EMP_NAME column size 100 to 200 in EMPLOYEE table. Then use following statement

ALTER TABLE EMPLOYEE MODIFY EMP_NAME varchar(200)

4. DROP DATABASE

The DROP DATABASE Statement is used to delete database.

Syntax:
DROP DATABASE database_name

If We delete database called testdb. Then use following statement

DROP DATABASE testdb

5. DROP TABLE

The DROP TABLE Statement is used to delete table.

Syntax:
DROP TABLE table_name

If We delete table called EMPLOYEE. Then use following statement

DROP TABLE EMPLOYEE
 

Data Manipulation Language

Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:

1. SELECT STATEMENT

The SELECT statement is used to select data from a database.

Syntax:
SELECT column_name1,column_name2,.. FROM table_name;
and
SELECT * FROM table_name;

Below the record is EMPLOYEE TABLE

EMP_CODE	EMP_NAME	EMP_ADDRESS
1	AJAY	DELHI
2	VIJAY	NOIDA
3	SANJAY	LUCKNOW
4	RAMESH	KANPUR
5	PANKAJ	PUNE

If We select EMP_CODE and EMP_NAME in EMPLOYEE table. Then use following statement

SELECT EMP_CODE,EMP_NAME FROM EMPLOYEE

If We select all column in EMPLOYEE table. Then use following statement

SELECT * FROM EMPLOYEE

2. INSERT STATEMENT

The INSERT statement is used to insert new data from a database.

Syntax:
INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);

If We inserT a new record in EMPLOYEE table. Then use following statement

INSERT INTO EMPLOYEE(EMP_CODE,EMP_NAME,EMP_ADDRESS) VALUES('EMP001','AJAY KUMAR','NEW DELHI')

3. UPDATE STATEMENT

The UPDATE statement is used to update existing data from a database.

Syntax:
UPDATE table_name SET column1=value1,column2=value2,... WHERE some_column=some_value;

If We update employee name AJAY KUMAR to AJAY SINGH where employee code EMP001 in EMPLOYEE table. Then use following statement

UPDATE EMPLOYEE SET EMP_NAME='AJAY SINGH' WHERE EMP_CODE='EMP001'

4. DELETE STATEMENT

The DELETE statement is used to delete data from a database.

Syntax:
DELETE FROM table_name WHERE some_column=some_value;

If We delete employee where employee code is EMP001 in EMPLOYEE table. Then use following statement

DELETE FROM EMPLOYEE WHERE EMP_CODE='EMP001'