Sunday 15 November 2015

Oracle 10g SQL as it is from QSpiders

SQL Statements:DDL: create,alter,rename,drop and truncate.DML: insert,update and delete.DCL: grant,revoke.TCL: commit,rollback,savepoint. DQL: select
How to insert multiple rows into a table:

insert into emp values(&empid,&ename,&salary);  

update emp set salary = 10000 where ename = 'john';

delete from emp where ename = 'john';

create table  emp ( eid  number(4), ename varchar2(10). salary number(10));

alter table emp add dept number(4);

To rename a column: alter table emp rename column salary to newsalary;

drop table emp;

DDL commands are auto saved. 

select * from dual;    - shows dummy table. 

truncate table emp;  - deletes the data from table permanently. truncate is a DDL command as it is a autosave command. Also, it is DML because it deals with data.

commit; - saves the user action till that point.

rollback; - it will undo the user action to previous successfully saved status.

savepoint; - it is an instant save for the user action for that particular section.

example: insert 2 rows into emp;  - savepoint sp1
               update 1 row into emp;  - savepoint sp2
               insert 1 row into emp;   - savepoint sp3
               rollback to sp2; takes to last saved state until savepoint sp2.

grant  select,insert,update on emp to scott;

revoke select,insert,update on emp from scott;

Constraints: are conditions or limitations on the table.
Types: Not Null, unique, primary key, foreign key, check.

Not Null: does not allow null values. 
example: create table emp( empid number(4) unique);

Primary Key: It is used for maintaining the relation between two tables.
Also, it is a condition or limitation on the table.
In a single table, we can apply one pk.

create table emp(empid number(2), salary number(5), ename varchar2(10) ,deptid number(2),
         constraint emp_pk  primary key(empid), constraint dept_key foreign key(deptid) references dept(deptid));

Foriegn Key: It is a referential integrity key between two tables.
A table can contain one or more foreign keys. Foreign key will allow null values and duplicate values.

Check constraint: It is a constraint or condition or limitation on a table. It  will enforce the users to validate the data before storing in the database.

example: create table emp(empid number(2), salary number(5), ename varchar2(10), constraint emp_chk  check(salary > 500));

Logical,relational,special operators like 'like', in , between and is.

Set Operators:

Union: It returns all the unique values from both the tables.
Union all: It returns all the values from both the tables.
Intersect: It returns all the values common in both the tables.
Minus: It returns values which are there in one table but not there in another table.

NVL: It is a function used for handling null values while performing arithmetic operations.

select sal,comm, sal + NVL(comm,0) as "Total Sal" from emp;

- To do - functions 

 select statement syntax:
- select * from table where groupy having orderby 

- To do - views

- Indexes are used for quick retrieval of data.

create index <indexname>  on table name (colname) ; 
drop index <index name>;

- To do - Normalization.

- To do - subqueries.

- Joins: are used for fetching the data from more than one table. To write a join, two tables must have a common column.
Types of Joins:
a. Inner Join
b. Outer Join
c. Self Join

- Inner Join is used in order to fetch matched records from both the tables.

example:
select * from table1 inner join table2 on 
                              table1.commoncolumn = table2.commoncolumn;

- Outer Join

a.Left Outer Join
b.Right Outer Join
c.Full Outer Join

Left Outer Join:

select * from table1 left outer join table2 on 
                              table1.commoncolumn = table2.commoncolumn;

fetches matched records from both the  tables and non matched records from left side table.

b. Right Outer Join:
select * from table1 right outer join table2 on 
                              table1.commoncolumn = table2.commoncolumn;

fetches matched records from both the  tables and non matched records from right side table.

c.Full Outer Join: used for fetching matched and non-matched records from both the tables.
select * from table1 full outer join table2 on 
                              table1.commoncolumn = table2.commoncolumn;

- Self Join: is used to apply the join condition on the same table.
example: Display the employees who are earning same salaries.
select A.* from emp A, emp B where 
                                    A.sal = B.sal and A.empno <> B.empno;

- Distinct: is the function used to fetch unique values.

What is the differences between distinct and unique?

- unique is used for storing the unique values.

Correlated subqueries: Its a combination of subqueries and joins.

Pseudo Columns:
a.rowid - a unique address for each and every row in the database. rowid reference always starts with zero.It is static value.
b.rownum - It is a sequential number generated by the database as and when user fetches the data from database.rownumber reference always starts with one.It is dynamic value.









 


               












1 comment:

  1. Please let me know what you are going to do as I really cannot wear the replica rolex uk. The reason I came back to you was for the quality of the replica as when you put the first replica watches I brought from you next to a real Rolex watch, you could not tell the difference. The one you have sent me is rubbish, it does not even have the sticker on the cartier replica uk. I do not want this watch let me know as soon as possible. I either want a quality replica or my money back. I have today posted back to you the fake rolex sale, I do not understand why you have sent me the watch in the first place if it was not the exact replica watches I ordered. This has now cost me more money in freight to return it. The replica watches sale you send me must be the exact replica as in the rolex replica. It must have the bigger hour markers, hands, Rolex logo and sticker on the back. As you have the tracking number I would ask you to post to me the correct rolex replica uk now so I do not have to wait another month for it, I have paid over $200.00 and want the watch I should of received in the first rolex replica uk. I have no choice then, I will have to wait. It is very worrying that you are not prepared to send the correct watch I ordered until you receive the watch I did not order back!

    ReplyDelete

Tweets by @sriramperumalla