The four main categories of SQL statements are as follows: 1. DML (Data Manipulation Language) 2. DDL (Data Definition Language) 3. DCL (Data Control Language) 4. TCL (Transaction Control Language)
DML (Data Manipulation Language) DML statements affect records in a table. These are basic operations we perform on data such as selecting a few records from a table, inserting new records, deleting unnecessary records, and updating/modifying existing records. DML statements include the following: SELECT – select records from a table INSERT – insert new records UPDATE – update/Modify existing records DELETE – delete existing records
SELECT – select records from a table Select * from salesman where city=’paris’ and commission=’0.13’; INSERT – insert new records Insert into salesman values (‘’1’,santu’,’mbl’,’28’);
UPDATE – update/Modify existing records Update salesman set age=’32’ where name =’aunty’;
DELETE – delete existing records Delete from salesman where name =’aunty’;
DDL (Data Definition Language) DDL statements are used to alter/modify a database or table structure and schema. These statements handle the design and storage of database objects. CREATE – create a new Table, database, schema ALTER – alter existing table, column description DROP – delete existing objects from database
CREATE – create a new Table, database, schema Create table salesman (slno number(10), name varchar(10),place varchar(10),age number(3));
ALTER – alter existing table, column description Alter table salesman add mobile number number(10);
DROP – delete existing objects from database
Drop table salesman;
DCL (Data Control Language) DCL statements control the level of access that s have on database objects. GRANT – allows s to read/write on certain database objects REVOKE – keeps s from read/write permission on database objects
TCL (Transaction Control Language) TCL statements allow you to control and manage transactions to maintain the integrity of data within SQL statements. BEGIN Transaction – opens a transaction COMMIT Transaction – commits a transaction ROLLBACK Transaction – ROLLBACK a transaction in case of any error
The SQL CREATE TABLE Statement The CREATE TABLE statement is used to create a new table in a database.
Syntax CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, .... );
The column parameters specify the names of the columns of the table. The datatype parameter specifies the type of data the column can hold (e.g. varchar, integer, date, etc.). Tip: For an overview of the available data types, go to our complete Data Types Reference.
SQL CREATE TABLE Example The following example creates a table called "Persons" that contains five columns: PersonID, LastName, FirstName, Address, and City:
Example CREATE TABLE Persons ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) );
SQL ALTER TABLE Statement The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. The ALTER TABLE statement is also used to add and drop various constraints on an existing table.
ALTER TABLE - ADD Column To add a column in a table, use the following syntax: ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE - DROP COLUMN To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column): ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE - ALTER/MODIFY COLUMN To change the data type of a column in a table, use the following syntax:
SQL Server / MS Access: ALTER TABLE table_name ALTER COLUMN column_name datatype;
My SQL / Oracle (prior version 10G): ALTER TABLE table_name MODIFY COLUMN column_name datatype;
Oracle 10G and later: ALTER TABLE table_name MODIFY column_name datatype;
The SQL DROP TABLE Statement The DROP TABLE statement is used to drop an existing table in a database.
Syntax DROP TABLE table_name;
Note: Be careful before dropping a table. Deleting a table will result in loss of complete information stored in the table!
SQL DROP TABLE Example The following SQL statement drops the existing table "Shippers":
Example DROP TABLE Shippers;
Try it Yourself »
SQL TRUNCATE TABLE The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself.
Syntax TRUNCATE TABLE table_name;
❮ Previous
Next ❯
SELECT – select records from a table Select * from salesman where city=’paris’ and commission=’0.13’; INSERT – insert new records Insert into salesman values (‘’1’,santu’,’mbl’,’28’);
UPDATE – update/Modify existing records Update salesman set age=’32’ where name =’aunty’;
DELETE – delete existing records Delete from salesman where name =’aunty’; CREATE – create a new Table, database, schema Create table salesman (slno number(10), name varchar(10),place varchar(10),age number(3));
ALTER – alter existing table, column description Alter table salesman add mobile number number(10);
DROP – delete existing objects from database
Drop table salesman;