1) DDL
DDL
is Data Definition Language statements. Some examples:
1) CREATE
-To create objects in the database
2) ALTER
-Alters
the structure of the database
Ex:-
Alter (column to be modify must
be empty to reduce the size or to
Change the datatype)
1) alter table emp modify(ename varchar2(40));
Add (to add new column or new constraints)
1) alter table emp add(address
varchar2(20));
2) alter table emp add foreign key(dept
number) reference dept(deptno) on
delete cascade;
3) DROP
-Delete objects from the database
Ex:
Alter table <Table name> drop
column <column_name>
Alter table <table name> set
unused (last_name)
Alter table <table_name> drop
unused columns
4) TRUNCATE
-Remove all records from a
table, including all spaces Allocated for the records are removed
5) OMMENT
-Add
comments to the data dictionary
Difference between Delete and truncate
Delete Truncate
|
1
DML
- It is DDL
|
2
It will mark
the records to delete - WE CAN'T
ROLLBACK
|
3
We can
rollback
- WE CAN'T APPLY CONDITIONS
|
5
latter we use
that space - It
will releases the space and we can
|
All this type of values are stored in
user_unused_col_tab table
QUERIES:
1) Copy the rows of emp to emp1, if both are structure
remains the same?
Insert into emp1
select * from
emp;
2) If
structure is not same, only the four columns in emp1?
Insert into emp1
Select empno, ename,
sal, deptno from emp;.
3) Copying the structure of emp to emp1 “As it is”
Here data also copied?
But here
constraints will not be copyed?
Create table emp2 as select *
from emp;
4) I want to copy the only structure of
emp in emp1? Here no data will be
copied?
Create table emp1 as select * from
emp where 1=2;
Distinct key word:
1)
Select all unique departments of employers?
Select distinct deptno from emp;
In Operator:
--- is to define a list of elements or
values
--- all the elements need to be enclosed
with in paranthesis
1)
list the emp those who are either clerks are analysis?
Select * from emp where job
in(‘clerk’,’analysis’);
Between operator:
---is used to spa city the range
---between operator not only work
with numbers and also can work date datatype
1)list
the employees of 1981?
Select * from emp
Where hiredate
between ’01-jan-1981’ and
’31-dec-1981’;
2)display
the empno, name, sal, annual sal of all the emp in the descending order of
annual sal?
Select eno, ename, sal,sal*12 annusal
from emp
order by annusal desc;
3)list
the employees whose exp is ranging from 15 to 25 years?
Select * from emp where
(sysdate-hiredate)/365 between 15 and 25;
4)
write a query to display emp details of those working on dept 30
Select * from emp where deptno=30 and
job=’clerk’ and commotion is null;
5)
Display the analyst & manager to the annual sal more than 30,000 working
under some manager with out commotion with experience less than 30 years and
whose name should not be equal to ‘blenk’ and who empno not equal to 7788 in
descending order of salary?
Select * from emp
Where job
in(‘analyst’,’mamager’) and
Sal * 12 >
30,000 and mgr is not null and comm. Is null and
(hiredate-sysdate)/365 < 30 and
Name ! = ‘black’
and empno != 7788
Order by sal desc;
Like operator:
--- is to match a pattern of characters
--- the delimiters used along with like
operator are -,%.
1) all the 5 charactores names starting with S?
select *
from emp where ename like ‘S----‘;
2) all 5 charactores ending with H?
select *
from emp where ename like ‘----H’;
3)
list the emp who joined in the month which is started with A?
Select * from emo where hiredate
like ‘A%’;
4) Those who joined in 1980’es?
Select *
from emp where hiredate like ‘%8_’;
2) DML
1) SELECT
- retrieve data from the a database
1)
INSERT
-insert data
into a table
2)
UPDATE
-updates
existing data within a table
Ex:
1)
Transfer the emp’s of research to accounting?
Update emp
Set deptno = (select deptno
from dept
where dname = ‘account’)
where deptno= (select deptno
from dept
where
dname = ‘research’);
3)
DELETE
-deletes all
records from a table, the space for the records remain
Ex:-
1) Delete salesman without commotion?
Delete
from emp
Where
job = ‘salesman’ and comm. Is null;
4)
CALL
-call a PL/SQL or Java subprogram
5)
EXPLAINS
PLAN
-explain access
path to data
6)
LOCK
TABLE
-control
concurrency
7)
MUTATINGTABLE
- Mutating Table is a table that is currently being
modified by a DML statement. For a Trigger, this is the table on which the
table is defined.
3) TCL
TCL
is Data Control Language statements.
1 COMMIT
- save work done
2
SAVEPOINT
-identify a point
in a transaction to which you can later roll back
Ex: savepoint (id) or (name)
3
ROLLBACK
-restore
database to original since the last COMMIT
Ex: rollback [to savepoint <savepoint name>]
4
SET
TRANSACTION
-Change transaction options like what rollback segment
to use Save point
Ex:
Commit(work);
5
Rollback:
- Oracle Issues implicit commit before and after any
DDL statement. Even if your DDL statement does not execute then also it Issues
commit statement
-If we issue commit all
savepoints will be erased and all locks are released
4) DCL:
1) GRANT:
--- Is used to issue the privileges to
other valid users of data base
1) User level
--is granted by the
DBA.
2) Object level
--These can be granted
by one user to another user
Ex:
1)
Give select, update permit ions to the user demo on emp?
Grant
select, update
On emp
To demo;
2) Grant all permitions to the user’s sys and system on
salgrade table?
Grant all
On salgrade
To sys,system;
2) REVOKE:
----To cancel the permitions
Ex:
1) cancle the select permition from the
user sys the object salgrade?
Revoke select
On salgrde
From sys
Note:
1) user_pri_objects
2) user_table_pri
3) user_col_pri
Select * from
DICT where table_name like ‘userpri%’;
5) Intigrity Constants:
Null, Not Null, Default, Check, Primary Key, Foreign Key
1 Primary
key : not null+ unique +index+ referenced to another
table column values(foreign key).
--one table can have only one
primary key
-- primary
key’s we can’t update
2
Unique : it will not allow duplicate values and it accept null
values can be defined at the column level and table level
3 Check : it will check with column definition before accept.
check constraint defines a condition
that each row must satisfy references to the currval, nextval, level, rownum pseudo
columns calls to sysdate uid user, and userenv. check cons. can be defined at
the column level and table level
4 Foriegn Key:
-foreign
keys provide referential integrity rules wither within a table or b/w
Tables
- give reference to the primary key column.
references - identifies
the table and column in the parent table.
On delete cascade -
deletes the dependent rows in the child table when a
row in the parent table is deleted.
On delete set null
- converts dependent foriegn key values to null.
Cascade constraint - the
cascade constraints clause drops all referential
integrity constraints that refer to the
primary and unique keys defined on the
dropped columns
-the
cascade constraints clause also drops all multicolumn constraints defined in
the dropped columns.
Diff between POST and COMMIT:
POST
puts the data from client to the server’s Rollback segment whereas COMMIT put
the data from rollback segment to the Table.
6) JOINS
Def: -A join is used when a SQL query requires
data from more than one table or the same table on the database.
TYPES OF
JOINS:
1
EQUI-JOIN: If the relation ship between the columns of different tables is establish
by using an operator EQUAL TO is equi join.
Ex: 1) display the location of the smith?
Select loc
from
dept, emp
Where ename = ‘smith’ and
dept.deptno = emp.deptno;
2
NON-EQUI-JOIN: - If
the relation ship between the columns of
different tables is establish by using an operator OTHER THAN EQUAL TO is equi
join.
Ex: 1) Select the details of the emp whose sal
is more than smith salary?
Select b.*
from emp a, emp b
where a.ename =’smith’ and b.sal
>a.sal;
3
SELF-JOIN: A join relates a table to itself is called a
self-join.
Ex: 1) display the details of the emp whose job is
same as blacks job?
Select b.*
from emp a, emp b
Where a.ename = blake and a.job =b.job;
4 OUTER-JOIN:
(+) always at the child side.(always child table)
--You can
use OUTER-JOIN to also see rows that do not meet the join
condition.
--The outer-join operator is the plus sigh
(+).
--The missing rows can be returned if
outer-join operator is used in join
condition.
Ex: 1) display all the details of
dept with or with out of employers?
Select dept.*
from dept,
emp
Where
dept.deptno = emp.dept(+);
7) SUB QUERIES->
-- A Sub query is a SELECT statement that is embedded
in a clause of another SELECT statement.
-- The inner query or the sub query
returns a value that is used by the outer query or the main query.
-- You can write up to 255 sub
queries.
TYPES OF SUBQUERIES:
1. Single-Row sub query: Query that returns only one row from the inner SELECT
statement.
Simple Query--This fires every time
for entire stmt
Ex:1)
display the details of the employers whose sal is more than smith
salary?
select
sal
from
emp
where
sal >(select sal
from emp
where ename =’smith’);
2. Multiple-Row sub query: Query that returns more than one row from the inner
SELECT statement.
CORRELATED SUBQUERY:
SQL*Plus
performs a sub query repeatedly once for every row of the main query.
Correlated
Sub query - Which fires only once/ per row for entire stmt.
Ex: 1) display the details of emp who are senior to their own manager?
Select *
from emp y
Where hiredate < (select hiredate
from emp y
Where y.empno = x.mgr);
FILTERS:
A)ANY B) ALL
---These two clauses are to filter the values those are returned by sub query
---These two clauses are totally operator dependent that is the returning value is totally based on the operator used along with ‘any’ or ‘all’.
Any è =1000 or =2000 or =3000 or =4000
In(1000,2000,3000,4000)
All è =1000 and =2000 and 3000 and 4000
Ex: 1) display the employer whose sal is more than any employer of dept 20
Select emp.*, sal
from emp
Where sal > (select sal
from emp
Where deptno = 20);
--- Here the query return only one value. So u have to use ANY or ALL
3) display the employer who are drawing the sal more than all the employer of research department?
Select emp.*
from emp
where sal > all (select sal
from emp,dept
where dname =’research’
and emp.deptno = dept.deptno);
8) SET OPERATORS:
- The SET operator combine the result of
two or more component queries
into one result.
- Queries containing SET operator are called COMPOUND QUERIES.
- All SET operators have equal precedence.
1. UNION : All distinct rows selected by both querys
EX:1) To display the
details of emp’s whose job is same as either ‘fold’ or ‘miller’
select job
from emp
where job in (select job
from emp
where ename =
‘ford’)
union
(Select job
from
emp
where ename = ‘miller’);
2) display the emp’s whose sal are same
as any of the following……
A) The sal of all the clerk of
emp1 table?
B) the 2/3rd
sal of any manager with exp is more than 10 years of emp2 table?
Select *
from emp
Where sal = any (select sal
from emp1
Where job = ‘clerk’)
Select 2/3 * sal
from emp2
where job =
‘manager’ and
(sysdate-hiredate)/365 >10;
2. UNION ALL: All rows
selected by both querys, including all duplicates.
EX: select job from emp where deptno=10
union all
Select job from emp where deptno=20
3. INTERSECT: All common
rows selected by both queries.
EX: select job from emp where deptno=10
intersect
Select job from emp where deptno=20
4. MINUS: All distinct rows
that are selected by the first SELECT statement and not selected in the second
SELECT statement.
EX: select job from emp where deptno=10
Minus
Select job from emp where deptno=20
9) FUNCTINS
CONVERSION FUNCTIONS:
1.
Name some
conversion functions?
•TO_CHAR
converts a number or date string to a character string.
•TO_NUMBER
converts a character string containing digits to a number.
•TO_DATE
converts a character string of a date to a date value.
DATE FUNCTIONS:
add_months,
last_day,
months_between,
new_time,
next_day,
round,
sysdate,
trunc.
DECODE:
Nested IF THEN ELSE
SELECT DECODE (ename,
‘Smith’, ’a’, ‘Something’);
GROUP FUNCTIONS:
These functions process a set of
rows and return a single value
1. max ()
2. min ()
3. count ()
4. avg ()
5. sum ()
6. variance ()
7. stddev ()
GROUP FUNCTION WITH having CLAUSE CONDITION:
NOTE: all group functions ignore null values in the column.
EX:
SQL> SELECT job_id, SUM (salary) PAYROLL
FROM employee
WHERE
job_id NOT LIKE '%REP%'
GROUP
BY job_id
HAVING
SUM (salary)>1000
ORDER
BY SUM (salary);
Group by Clause:
---is to logically separate the data
based on the one or more columns especially, to find the summaries.
Note :
--- The columns those are specified in
the group by are only allowed in the select. The columns other than the columns
specified in the group by can also used in the select but some with group
function.
Ex:1) Select deptno,max(sal),min(sal),sum(sal),count(sal)
from emp
group by deptno
2)
count emp by each location?
Select loc,count(*)
From emp,dept and Emp.deptno
=dept.deptno
Group by loc;
Having Clause:
--- having clause is used to check the
condition of multiple groups or conditions of group by.
Ex:1)
display the details of dept’s where at least two clerks are working?
Select ename,loc,deptno
From emp,dept
Where job=’clerk’ and emp.deptno
= dept.detpno
Group by dept.deptno,ename,loc
Having count (*) >=2;
10) indexes:
It improves the performance of some queries
1) Unique
A) Heap
B) Hash
2) non unique
Syntax:
1) create index <index name>
On <table name> [<col1>, <col2>];
2) create unique index <index name>
On <table name> [<col1>, <col2>];
Ex:
1) Create index on sal column?
Create index sal
On emp
11) Views:
What is a simple
view?
Logically represent subset of
data from one or more tables
Syntax: Create view
<view name> <col1>, <col2>…..
As
Select <col1>,
<col2>….
From <table1>,
<table2>….
what is c
complex view?
A complex view is one that derives data from
many tables, contains functions or
groups of data and does not always allow dml through the view.
What is inline
view?
An inline view is a sub query with an alias
(correlation name) that you can use with in a sql statement .an inline view is
similar to using a named sub query in the from clause of the main query.
Select * from ( select empno, ename from emp
where deptno =10)
Materialized views:
- Materialized
views provide indirect access to table data by storing the results of a
Query in a separate schema object.
- Another
name for materialized view is snapshot.
- Materialized
view can be stored in a same database or diff database.
- Data
can be replicated using materialized view.e
12) Sequence :
----To generate primary key values.
Syntax: create
sequence <sequence name>
Start with <int> ---default 0
Increment by <int> --- 1
Min value/no min value
<int> ---0
Max value/no max value
<int> ---unknow
Cycle/no cycle ---no cycle
Cache/no cache <int> ---cache 20
Order;
Ex:-
1) Create a sequence to generate a series of numbers from
1 to 1000?
Create sequence numbers
Start with 1,
Increment by 1,
Max value 1000;
13) Synonym:
----Alternative name for an object. (Nothing but creating
permanent alias name)
Ex:
1) Create a synonym
for emp table?
Create synonym E
For emp
14) Oracle 8i features?
1) Data management
2) Scalability
3) Oracle apps security managing
4) Internet computing architecture
5) Improve transaction processing
15)
Can you create index on views? No
16) Can views be specified in a
trigger statement? No17) Are views automatically updated when base tables are updated? Yes
18) Can a trigger written for a view? No
19) When a table is dropped, what happens to the view defined on that table? View becomes invalid
20) normalization
1) Break the data
2) In order to sort the data
3) Reduce the redundancy
4) Normalization supports functional
dependency
5) Normalization does not support
transactional dependency
6) RDBMS supports functional dependency.
1nf
- unnormalised data
transfer to normalized form.
2nf - functional dependencies can be
find out & decompose the table without loss of data.
3nf - transist dependencies, every non-key attribute is
functionally dependant on just pk.
4nf (bcnf)-the relation,
which has multiple candidate keys, then we have to go for bcnf.
denormalization-at the same time when information is required from
more than one table at faster rate then it is wiser to add some sort of
dependencies.
21) how can we
drop a table and its dependent integrity constraints?
drop table table [cascade constraints];
22) What is candidate key?
Candidate keys are the columns in the
table that could be the primary key.
23) Oracle
instance?
Sga
Background process
24) Which table
stores subprogram errors?
user_errors
25) What is the
use of on delete cascade clause in a table?
–allows
deletion in the parent table and deletion of the dependent rows in the child table.
26) What is p-code?
P-code is the compiled form of the source
code of a subprogram.
27) Can primary key contain more than one column?
Yes, but table contains one primary key.
28) Mutating tables?
When
a table is in state of transaction it is said to be mutaing.
29) psudo columns?
1) curr val 2) next val 3) level 4) row
id 5) row num
No comments:
Post a Comment