Structured Query Language (SQL) Commands

Structured Query Language (SQL)

Structure Query Language(SQL) is a database query language used for storing and managing data in Relational DBMS. SQL was the first commercial language introduced for E.F Codd's Relational model of database.

SQL is used to perform all types of data operations in RDBMS. Structured Query Language is a standard Database language which is used to create, maintain and retrieve the relational database.

SQL is the programming language for relational databases like MySQL, Oracle, Sybase, SQL Server, Postgre, etc. 

SQL commands are instructions, coded into SQL statements, which are used to communicate with the database to perform specific tasks, work, functions and queries with data. SQL commands are grouped into four major categories depending on their functionality:

I. Data Definition Language (DDL) - These SQL commands are used for creating, modifying, and dropping the structure of database objects. The commands are CREATE, ALTER, DROP, RENAME, and TRUNCATE.

create command :

create is a DDL SQL command used to create a table or a database in relational database management system.

To create a database in RDBMS, create command is used. Following is the syntax,

CREATE DATABASE <DB_NAME>;

Example:

CREATE DATABASE Test;

The above command will create a database named Test, which will be an empty schema without any table.

create command can also be used to create tables. To create a table, specify the details of the columns of the tables. Specify the names and datatypes of various columns in the create command itself.

Following is the syntax,

CREATE TABLE <TABLE_NAME>

(

    column_name1 datatype1,

    column_name2 datatype2,

    column_name3 datatype3,

    column_name4 datatype4

);

example:

CREATE TABLE Student(

    studentid INT,

    name VARCHAR(100),

    age INT);

ALTER command:

alter command is used for altering the table structure, such as,

• to add a column to existing table
• to rename any existing column
• to change data type of any column or to modify its size.
• to drop a column from the table.

Using ALTER command we can add a column to any existing table.

Syntax:

ALTER TABLE table_name ADD( column_name datatype);

Example:

ALTER TABLE student ADD(address VARCHAR(200));

The above command will add a new column address to the table student, which will hold data of type varchar which is nothing but string, of length 200.

TRUNCATE command:

TRUNCATE command removes all the records from a table. But this command will not destroy the table's structure. 

Syntax:

TRUNCATE TABLE table_name

Example:

TRUNCATE TABLE student;

The above query will delete all the records from the table student.

DROP command:

DROP command completely removes a table from the database. This command will also destroy the table structure and the data stored in it.

Syntax:

DROP TABLE table_name;

Example:

DROP TABLE student;

RENAME query:

RENAME command is used to set a new name for any existing table. 

Syntax:

RENAME TABLE oldtable_name to newtable_name;

Example:

RENAME TABLE student to students_info;

The above query will rename the table student to students_info.

II. Data Manipulation Language (DML) - These SQL commands are used for storing, retrieving, modifying, and deleting data. The Data Manipulation Language commands are: SELECT, INSERT, UPDATE, and DELETE.

INSERT command:

Insert command is used to insert data into a table.

Syntax:

INSERT INTO table_name VALUES(data1, data2, ...);

Example:

INSERT INTO student VALUES(101, 'Adam', 15);

UPDATE command:

UPDATE command is used to update any record of data in a table.

Syntax:

UPDATE table_name SET column_name = new_value WHERE condition;

WHERE is used to add a condition to any SQL query.

Example:

UPDATE student SET age=18 WHERE student_id=102;

DELETE command:

DELETE command is used to delete data from a table.

Syntax:

DELETE FROM table_name;

Example:

1. DELETE FROM student;

Deletes all records from student table.

2. DELETE FROM student WHERE s_id=103;

The above query deletes rows from the table student where condition is true.

SELECT SQL Query:

SELECT query is used to retrieve data from a table. It is the most used SQL query. Complete table data, or partial can be retrieved by specifying conditions using the WHERE clause.

SELECT query is used to retieve records from a table.

Syntax:

SELECT column_name1, column_name2, column_name3, ..., column_nameN

    FROM table_name;

Example:

SELECT s_id, name, age FROM student;

SELECT statement uses * character to retrieve all records from a table, for all the columns.

Example:

SELECT * FROM student;

The WHERE clause can be used to set a condition,

Example:

SELECT * FROM student WHERE name = 'Ash';

 III. Transaction Control Language (TCL) - These SQL commands are used for managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT.

COMMIT command:

COMMIT command is used to permanently save any transaction into the database. If any DML command like INSERT, UPDATE or DELETE, are used the changes made by these commands are not permanent, until the current session is closed, the changes made by these commands can be rolled back. The COMMIT command to mark the changes as permanent.

Syntax:

COMMIT;

ROLLBACK command:

This command restores the database to last commited state. It is also used with SAVEPOINT command to jump to a savepoint in an ongoing transaction.

Syntax:

ROLLBACK TO savepoint_name;

SAVEPOINT command:

SAVEPOINT command is used to temporarily save a transaction so that you can rollback to that point whenever required.

Syntax:

SAVEPOINT savepoint_name;

IV. Data Control Language (DCL) - These SQL commands are used for providing security to database objects. These commands are GRANT and REVOKE.

GRANT: Used to provide any user access privileges or other priviliges for the database.

Syntax:

grant privilege to username;

Example:

GRANT CREATE TABLE TO username;

The above statement allows a user to create tables in the database.

REVOKE: Used to take back permissions from any user.

Syntax:

REVOKE privilege FROM username;

Example:

REVOKE CREATE TABLE FROM username;