Welcome to CodeCrew Infotech
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;
You may also like
