SQL(Structured Query Language) all important commands
- DDL (Data Definition Language)
- DML (Data Manipulation Language)
- DCL (Data Control Language)
- TCL (Transaction Control Language)
Let's talk about each and every Language and its commands in detail..
Let's learn this.
DDL:-
1) CREATE : used to Create table in database.
CREATE TABLE Table_Name( name1 data_type(size) , name2 data_type(size) );
CREATE TABLE employee( id int(10) not null, name varchar(30), salary double(8)) ;
2) Alter : used to Modify, add, drop, rename.. To / Change of table's column.
a) MODIFY :- used to modify data-size and data-type of the particular column/s.
ALTER TABLE Table_Name MODIFY COLUMN_NAME(20);
ALTER TABLE Table_Name MODIFY COLUMN_NAME data_type.
ALTER TABLE employee MODIFY id(7) ;
ALTER TABLE employee MODIFY salary int(10);
b) ADD :- used to add new column to an existed TABLE in database.
ALTER TABLE Table_Name ADD COLUMN_NAME data_type;
ALTER TABLE employee ADD Bonus double(5);
c) DROP :- used to delete particular column and changes made after performing drop will be permanent.
ALTER TABLE Table_Name DROP COLUMN_NAME;
ALTER TABLE employee DROP bonus;
d) RENAME... TO/ CHANGE :- used to rename the column name.
(I) ALTER TABLE employee RENAME id TO empid;
(II) ALTER TABLE employee CHANGE id empid(6);
3) Drop :- used to delete entire table and changes cannot be undone.
DROP TABLE Table_Name;
DROP TABLE employee;
4) Truncate :- This command used to delete entire data in a table, but the table structure will be present.
TRUNCATE TABLE employee;
5) Comment :- it is not a command, it is just a way of representing comments in sql.
#.....
//.....
/*.... */
#this is comment
//this is comment
/*this is comment*/
DML :-
1) Insert :- used to insert values into the table
INSERT INTO TABLE employee(id, name, address) VALUES(1, 'jeswanth, 'elur'), (2, 'tigerRaj', 'kochi'), (3, 'raj', 'vizag') ;
2) Update :- used to update values in the particular row/s.
UPDATE employee SET id=6, address='kondur' WHERE name='jeswanth';
3) Delete :- used to delete entire table or a row of a table. Deleted table or row can be roll backed, in order to achieve this, ROLLBACK command is helpful.
DELETE FROM employee WHERE id=1, name='jeswanth';
- Command to delete entire table as follows
DELETE FROM employee;
4) SELECT :- used to select particular row or entire table in database.
You can select anything depending upon your preference in any table with select.
eg :- SELECT * FROM employee;
selects entire table named employee.
eg :- SELECT name FROM employee LIKE 'a%';
selects name starting with alphabet 'a'.
eg :- SELECT SUM(score) FROM cricket;
sum, min, max, avg, count are AGGRIGATE FUNCTIONS. All commands in aggregate functions are same except COUNT.
eg :- SELECT count(id) FROM employee;
eg :- SELECT count(*) From employee;
count(*) returns number of rows in employee table.
eg :- SELECT DISTINCT(country) FROM employee;
removes duplicate values and prints distinct values.
eg :- SELECT count(person), country FROM employee GROUP BY country ORDER BY person HAVING count(person) > 5;
5) MERGE :-
It is used to perform update, insert, delete operation at the same time.
This topic is seemingly large. I will explain this in my upcoming posts.
6) Call :- call statement in SQL is called 'STRUCTURAL PROCEDURES'.
used to envoke procedure keyword when ever it is required in the database implementation.
eg:- CREATE PROCEDURE tabledata AS select * from employee
Go;
EXEC tabledata;
eg:- CREATE PROCEDURE tabledata2 @city varchar(10), @id int(5) AS
select city=@city, id=@id from table;
EXEC tabledata2 @city='eluru', @id=34567;
Note :- Here EXEC is envoked when ever we want to execute the entire procedure.
7) Explain Plan :-
Explain plan statement is used to know the execution plan choose by oracle in plan table.
8) Lock Table :- locks are used to lock reading or writing modes in a table/s.
eg :- Suppose, 'send' is a table with one parameter, insert value into it
INSERT INTO TABLE send(message) values('got a message') ;
LOCK TABLE send READ;
After performing read lock, you cannot read it, let's execute SELECT *FROM send, then it gives you error.
We can do the similar with write lock.
The main theme of locks is to restrict user access of read | write in a table.
DCL :-
1) GRANT :- used to grant permissions to the users.
eg:- GRANT CREATE TABLE ANY TABLE TO jeswanth;
grants permission to user jeswanth to create any number of tables.
eg :- GRANT SYSDBS TO jeswanth;
grants access to system database to user jeswanth
eg :- GRANT DROP ANY TABLE TO jeswanth;
2) REVOKE :- used to deny permission to particular user.
eg :- REVOKE CREATE ANY TABLE TO jeswanth;
Here, we are denying access to user jeswanth;
TCL :-
1) COMMIT :- used to commit changes permanently.
Changes cannot be undone.
2) ROLLBACK :- used to undone the changes. In order to achieve this, we need to declare SAVEPOINT. so, system can identify the portion of session to rollback.
eg :- SAVEPOINT A;
ROLLBACK A;
Comments
Post a Comment