SQL (Structural Query language)
SQL(Structural Query language) is the set of command that all programs and user must to access data within the ORACLE database. Application programs and ORACLE tools often allows user to access the database without directly using SQL , but these application in turn must use SQL when executing the user request.
|
*LOGIN ( user id and password for SQL) |
||
|
User name - scott Password - tiger |
User name - demo Password - demo |
User name - system Password - manager |
SQL Commands
1. Describe – Describe command is used to see the structure of database(table)
Syntax – Describe <user id> <table name>
Example – SQL> describe student
Name NULL Type
Roll Not Null Number(2)
Name char(20)
Marks Number(2)
2. Create table – this command is used to create database ( table )
Syntax – create table <table name> (column datatype,………..);
Example – SQL> create table record(name char(20) , roll number(2), marks number(2));
3. Insert – insertion of data into table.
Syntax – insert into <table name> values (data, ……..);
Example – insert into record values(“manish”,1,78);
Note – insert into record values(‘&name’,&roll,&marks);
Enter value for name –
Enter value for roll –
Enter value for marks –
4. Select – used to see the content of table.
Syntax – select column1, column2 ,….. from table-name;
Example – SQL > select roll, name,marks from records;
SQL > select * from records;
SQL > select distinct name from records;
Note – distinct keyword is used to suppress duplicate values in a column selected for output.
SQL > select * from records where marks>50;
SQL > select * from records order by marks;
Note – order by is used for getting the rows from a query in a specific order
Syntax – select columns or expression from table-name order by columns [ ASC/DSC]
ASC – Ascending order
DSC – Descending order
SQL > select * from records order by marks DESC;
Pattern search – like / not like
syntax – where column like / Not like ‘pattern’
ex- select * from records where name like ‘manish’
5. L or List – List the current SQL command .
Example – LIST
select * from records where name like ‘manish’
6. Run or / - execute the current SQL statement.
7. Edit – use an external editor to edit the SQL buffer .
8. Delete – it is used to delete the record / delete the record from given specific condition .
Syntax – delete from <table-name> <condition>
Example – delete from record where name=”manish”;
9. Roll back – it is used to backup your previous data which is deleted currently.
Syntax – Roll back ;
10. Commit – it is used to delete data from backup means when this command executed then all the deleted record cannot be roll back.
11. Clear screen – it is used to clear the output screen .
12. Quit – it is used to quit the SQL session .
Note – exit is also used for same purpose.
13. Update – to change existing values in a table .
Syntax – update <table –name> set (column={expr}) where condition;
Example – update emp set job=’manager’ , sal=sal+1000 , deptno=20 where name=’manish’ ;
14. User –
Create user user-name identified by password-value;
Example – create webmaster identified by webmaster123;
15. Drop – this command is used to delete the table along with all its data.
Syntax –Drop table table-name ;
16. Rename – this command is used to change the name of table.
Rename old-table-name new-table name;
Example – rename cust customer ;
17. Alter – this command is used to modify the existing column of table and also to insert new column in the table.
To add new column
Alter table <table-name> add(column-name type(width));
Example – alter table records add(address char(20));
To modify the column
Alter table <table –name> modify(column-name type(width));
Example – alter table records modify(roll number(4));