Metadata: saved under data dictionary.
Actual data saved under actual database.
---------------------------
8. Insert All:
Using this command, we can insert multiple records into single table or multiple table at a time.
Syntax:
insert all into table_name(col_name)
values(.........)
into table_name(col_name)
values(........)
into table_name(col_name)
values(....)
select * from dual;
Dual: it is a system defined table, which holds the values under buffer area.
EX: SQL>insert all
into emp_info
values(111,'a',1200,'clerk','21-oct-14')
into emp_info
values('112','b','1200','clerk','22-jan-14')
select * from dual;
display: 2 rows created.
--------------------------------------------------
9. Inserting record at run time:
by using &(address of) operator
Syntax:
Insert into table_name(col_name)
values('&col1','&col2','&col3','&col4',..........);
EX:
insert into emp_info
values('&eid','&ename','&sal','&desg','&jdate');
display:
Enter value for eid:....
Enter value for ename:....
Enter value for sal:....
Enter value for desg:...
Enter value for jdate:...
1 row created.
SQL>/
Enter value for eid:...
Enter value for ename:...
Enter value for sal:...
Enter value for desg:...
Enter value for jdate:...
1 row created.
--------------
Note: '/' command: it will re-execute recent SQL query in SQL * plus window.
-----------------------------------------------
9. NULL VALUE:
A null value is known as missed value in a column, null values are handled by oracle engine, a null value not equal to zero, space or other null value. null value are independent of data type.
EX: Null values are inserted using two methods:
1. implicit insertion:
EX: insert into emp_info(ename, sal)
values('hemant','1200');
2. explicit insertion:
EX: insert into emp_info
values(null,'hemant', 1200, null, null);
---------------------------
10. Distinct Clause:
display list of unique values from given column.
SYntax:
select distinct col1, col2,...
from table_name;
EX: display list of different desg:
SQL> select distinct desg from emp_info;
or
SQL> select distinct desg from emp_info where desg not equal to null;
---------------------------------
11. User Tables:
It is system defined table which contains list of table names, it will not maintain recycle bin table names.
->select table_name from user_tables;
# How to create a table from other table:
-> create table <new name>
as
select cl1,cl2,cl3,... / *
from <old_table_name>;
EX:1
SQL> create table emp as select * from scott.emp;
Display: Table Created.
EX:2
SQL> create table emp1
as
select ename,sal,job,deptno
from emp;
-> select table_name from user_table;
-> select * from tab; -> it shows tables in recycle bin too.
# creating empty table from other table: means creating only table structure from other.
SQL> create table emp2
as
select empno, ename, sal, job
from emp
where 1 = 2;
Ans: Write any query with a false condition, so it will create an empty table with structure only.
------------------------------------
12. change name of column temporary for output:
by default column names are displayed as titles in the output, to change the title, use below syntax
SQL> select colname "title Name"
from table_name.
-> title with spaces allowed & same case maintained in title if we use " ".
SQL> select ename EmpName from emp;
-> it will change ename to EMPNAME;
SQL> select ename "EmpName" from emp;
-> it will change ename to EmpName.
SQL> select ename "Emp Name" from emp;
-> it will change ename to Emp Name.
SQL> select ename as Emp_name from emp;
-> it will change ename to EMP_NAME.
--------------------------------------------------------
13. increase page size in sql * plus tool:
SQL> set pagesize = 100;
-------------------------------------------------------
14. Order by Clause:
-> it will display ascending and descending order data in the output.
Generally table data is random ordered data.
Syntax:
SQL> select col1, col2,...
from <table name>
order by cl1,cl2,..[ASC/DESC];
Note: ASC is default.
DESC: Reverse Order.
Note: At the place of col_name, we can also use col position number in order by clause.
EX:1. display emp_name, sal, job & dept no based on sal order.
SQL> select ename, sal, job, dept_no
from emp
order by sal;
(or)
SQL> select ename, sal, job, dept_no
from emp
order by 2;
-> display above output department wise & also from each department least salary emp to the highest salary employee.
EX:2 select ename, job, sal, dept_no
from emp
order by dept_no, sal;
Note: it will order the deptNo first in order, if their is only duplication then it will give second priority to sal for order.
EX:3. Display emp_name in reverse order:
SQL> select ename
from emp
order by ename desc;
EX:4. Display emp_name, salary & join date like latest employee to the old employee.
SQL> select ename, sal, jdate
from emp
order by 3 desc;
EX:5. Display customer details based on gender order:
SQL> select * from cust_dtls
order by gender;
--------------------------------------------------------------------------------
You may also like: --> Naming Rules in Oracle DBMS
No comments:
Post a Comment