/

August 6, 2024

SQL Commands

SQL is a programming language for Relational Databases. It is designed over relational algebra and tuple relational calculus. SQL comes as a package with all major distributions of RDBMS.

SQL comprises both data definition language and data manipulation languages. Using the data definition(DDL) properties of SQL, one can design and modify database schema whereas data manipulation(DML) properties allow SQL to store and retrieve data from the database.

DATA DEFINITION LANGUAGE

CREATE – to create a table structure in the database

ALTER – alters the table structure of the database

DROP – delete table structure from the database

TRUNCATE – remove all records from a table, including all spaces allocated for the records that are removed

RENAME – rename an object

 

CREATE

Create: It used to create a table.

Syntax:

CREATE TABLE <TableName> (column1 datatype (size), column2 datatype (size) … columnN datatype (size));

Example:

SQL>CREATE TABLE student (Rollno number (15), Name varchar2 (20), Age number (5), DOB date);

Output: Table created.

 

DESCRIBE

To describe a table use command DESCRIBE,

Syntax:

DESCRIBE <TableName>; OR DESC <TableName>;

Example: DESC student;

 

ALTER TABLE

ALTER: It is used to modify the table structure.

1) Adding New Columns

Syntax:

Alter Table TableName Add (NewColumnName1

Datatype (size), NewColumnName2 Datatype(size)…. NewColumnNameN Datatype (size),);

Example: ALTER TABLE student ADD (marks number(15)); //Add column marks

Output: Table altered.

ALTER TABLE

2) Modifying Existing Columns

Syntax:

Alter Table TableName Modify (ColumnName1 NewDatatype(NewSize), ColumnName2 NewDatatype (NewSize)……ColumnNameN NewDatatype (NewSize));

Example: ALTER TABLE student Modify (marks number (20));// Change size of column

Output: Table altered

ALTER TABLE

3) Dropping(deleting) Existing Columns

Syntax: Alter Table TableName DROP COLUMN ColumnName1, ColumnName2;

Example: ALTER TABLE student DROP COLUMN marks; //Remove marks column

Output: Table altered

 

DROP AND TRUNCATE TABLE

DROP: It is used to delete or destroy a table from a database.

Syntax: DROP TABLE <TableName>;

Example: DROP TABLE student;

Output: Table Dropped.

TRUNCATE:

TRUNCATE TABLE used to delete all data from a table

Logically, this is equivalent to a DELETE statement that deletes all

rows

TRUNCATE command is faster than the DELETE command

The number of deleted rows is not returned

Syntax: TRUNCATE TABLE <TableName>;

Example: TRUNCATE TABLE student;

Output: Table Truncated.

 

DATA MANIPULATION LANGUAGE

DML is a set of SQL Commands used to insert, modify and delete data in a database.

These SQL commands are used for storing, retrieving, modifying, and deleting data.

It is normally used by general users who are accessing databases via pre-developed applications.

These Data Manipulation Language commands are:

1. INSERT

2. UPDATE

3. DELETE

 

1. INSERT COMMAND

The INSERT Statement is used to add new rows of data to a table.

Syntax:

INSERT INTO <TableName> (ColumnName1, ColumnName2…ColumnNameN) values (Expression1, Expression2….ExpressionN);

Example:

INSERT INTO Student (Rollno, Name, Age, DOB) values (1,’CODECREW’,19,’1-june-99’);

Output: 1 row inserted.

We can insert NULL value in Table Using INSERT Statement.

Example:

INSERT INTO Student (Rollno, Name, Age, DOB) values (2,’INFOTECH’,20,’27-aug-99’);

Output: 1 row inserted.

 

2. UPDATE COMMAND

The UPDATE Statement is used to modify the existing rows in a table.

Syntax:

UPDATE TableName SET Column_Name1 = value1, Column_Name2 = value2 WHERE condition;

Example:

UPDATE Student SET Name=’Maanav’ WHERE Rollno=1;

Output: 1 row Updated.

 

3. DELETE COMMAND

The DELETE Statement is used to delete rows from a table.

Syntax:

DELETE FROM TableName WHERE condition;

Example:

To delete an employee with id 100 from the employee table, the SQL delete query would be like this,

DELETE FROM Student WHERE Rollno = 1;

To delete all the rows from the employee table, the query would be like this,

DELETE FROM Student;

 

DATA QUERY LANGUAGE

SELECT/FROM/WHERE

SELECT

This is one of the fundamental query command of SQL. It is similar to projection operation of relational

algebra. It selects the attributes based on the condition described by WHERE clause.

FROM

This clause takes a relation name as an argument from which attributes are to be selected/projected. In case

more than one relation names are given this clause corresponds to cartesian product.

WHERE

This clause defines predicate or conditions which must match in order to qualify the attributes to be projected.

Example:

Select * from Student;

In this example you can see all the data of table but if you want to see particular column data then used column name insted of ‘*’ for example you want to see only student name then

Select Name from Student;

 

DATA CONTROL LANGUAGE

DCL commands are used to grant and take back authority from any database user.

Here are some commands that come under DCL:

1. Grant

2. Revoke

Grant: It is used to give user access privileges to a database.

Example:

GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER

Revoke: It is used to take back permissions from the user.

Example:

REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2

 

TRANSACTION CONTROL LANGUAGE

TCL commands can only use with DML commands like INSERT, DELETE, and UPDATE only.

These operations are automatically committed in the database that’s why they cannot be used while creating tables or dropping them.

Here are some commands that come under TCL:

COMMIT

ROLLBACK

SAVEPOINT

Commit: Commit command is used to save all the transactions to the database

Example:

DELETE FROM Student WHERE Rollno = 1;

COMMIT;

Rollback: Rollback command is used to undo transactions that have not already been saved to the database

Example:

DELETE FROM Student WHERE Rollno = 1;

ROLLBACK;

SAVEPOINT: It is used to roll the transaction back to a certain point without rolling back the entire transaction.

SAVEPOINT SAVEPOINT_NAME;

 

 

From the same category