SQL-Basic Concepts

SQL stands for Structured Query language. It is a non procedural language and it is a database language.

There are 3 broad categories in SQL commands.

• Data Definition language (DDL)
• Data Manipulation Language (DML)
• Transaction Control Language(TCL)
  

Now lets look at the various commands that come under each of the categories.

Create,Alter, Drop come under DDL.
Insert,Select,Delete,Update fall under DML.
Rollback,Savepoint,Commit fall under TCL.

Now lets look in detail.

DDL:

CREATE TABLE is the command used to create tables.

SYNTAX: CREATE TABLE tablename (col-name1 col-definition1, col-name2 col-definition2…);

EX:Create table student(stname varchar2(20),stno number(4));

ALTER TABLE command is used to modify a table definition or can be used to alter column definition in table.

SYNTAX: ALTER TABLE tablename MODIFY (Column definition);

EX1:alter table student rename to student_details;
Ex2:alter table student_details add stmarks number(3);

DROP TABLE:This command is used to remove an existing table permanently from database.

SYNTAX: DROP TABLE tablename; EX:Drop table student_details;

DML:

SELECT: This is used to select values or data from table.

SYNTAX: SELECT column name1, column name2, …. FROM tablename;

If all column values in a table are to be selected, then we use SELECT * from tablename;

EX1:select stname from student_details;
EX:2:select * from student_details;

INSERT:This command is used to insert rows into the table.

SYNTAX: INSERT INTO tablename VALUES (value,value,….);

EX:Insert into student_details values('A',01,95)

DELETE: This command is used to delete rows from a table.

SYNTAX: DELETE FROM tablename WHERE condition;

The WHERE clause is optional and when a condition is used in where clause of delete ,then the rows that satisfy the condition gets fetched from table and get deleted.

EX1:Delete from student_details where stno=01;
EX2:Delete from student_details;

In EX2 all the rows in the table gets deleted but still the table structure retains in the database.This is one major difference between delete and drop where if drop is used the entire table is deleted but unconditional delete deletes entire data but table structure remains.

UPDATE: This SQL command is used to modify the values in an existing table.

SYNTAX: UPDATE tablename SET columnname = value, columnname = value,….. WHERE condition;

If the WHERE clause is not used then,the value that is given to set, is set to entire coloumn.If WHERE is used,the rows which satisfies the WHERE condition are fetched and only for these rows the column values we placed in SET statement gets updated.

EX:Update student_details set stmarks=99 where stno=01;
This updates the marks of student no 01 as 99.

TCL:

ROLLBACK:-This is used to undo the current transaction.

SYNTAX: ROLLBACK;

COMMIT:-This is used to make all changes permanent in database and also marks the end of transaction.

SYNTAX: COMMIT;

It is always good practice to commit changes to database at regular intervals since it helps to avoid loss of data when computer shuts down due to unavoidable reasons.

SAVEPOINT: This is used to identify a point in a transaction to which we can later rollback.

SYNTAX: SAVEPOINT savepoint_identifier;