Docstoc

Structural Query Language

Document Sample
Structural Query Language Powered By Docstoc
					    Oracle




                /




/




    2011 2010
                            10
                 .(TABLE , ROW, COLUMN)                       -1
                                                            TABLE
                             Record                          ROW
                              Filed                       COLUMN


                                                                -2



     .


                         .SQL    SQL*PLUS                       -3
                                               SQL                   -1
                                                      Objects
     .SQL

         SQL                                     SQL*plus            -2
          SQL                                  SQL*plus
                         .


                                                                -4
                 SQL*PLUS             SELECT                     •
                                                     X.
                    √.                 DML                      •
                                      SQL> SPOOL                •
                                                     √.
√.                                           L24                •
            √.                        SQL> START                •
                    X . SQL                   RUN               •
                                        22

                                                                                  -1

Name                                Null?                         Type
----------------------------      ----------                      -----------
DEPTNO                             NOT NULL                       NUMBER (2)
DNAME                                                             VARCHAR2 (14)
LOC                                                               VARCHAR2 (13)
desc dept
select * from dept;
                                                                                  -2

select empno,ename,job,hiredate
from emp;

                                                                                  -3
JOB
-------
ANALYST
CLERK
MANGER
PRISIDENT
SALESMAN

select distinct job
from emp
order by job;

                                                                                  -4
                                                            :
EMPLOYEE_NO                    EMPLOYEE_NAME                    JOBS
----------------------         --------------------------       ---------------

select empno as EMPLOYEE_No,ename "EMPLOYEE NAME" ,job
JOBS
from emp;
                             39-38

    2850                                                          -1
                  :
  select ename,sal
  from emp
  where sal>2850
  order by sal desc;

                                                                  -2
                                                    (1500,2850)
   select ename,sal
   from emp
   where sal not between 1500 and 2850;
     1500                                                        -3
                       30                   10
                                      :
select ename,sal    from emp     where sal>1500 and deptno in (10,30)
order by sal desc;
        1982                                                     -4
                                                 :
select ename,hiredate from emp where hiredate like'%82';
                                                                 -5
                                                 :
select ename,sal,comm from emp where comm is not null
order by ename;
    A                                                            -6
                                             :
select ename from emp where ename like '__A%';
        LL                                                       -7
                                                 :
   select ename from emp where ename like '%LL%';
                               60-59
                                                                  -1

Select initcap (ename) "EMP_NAME", length (ename)"NUMBER",
substr (ename,1,4) "SECTION"
from emp;
                                                             -2
                             :                30
  select concat (ename,job) title
  from emp
  where deptno= 30;
                                                             -3

  elect ename,round ((months_between(sysdate,HIREDATE))/12) as
  "number a years"
   from emp
                                                                  -4
select to_char(sysdate,'DAY "OF" MM/YYYY HH12:MI') AS
TODAY
From dual

                                                             -5
                                       (FEBRUARY 22,2004)
select to_date( ' FEBRUARY 22, 2004','mm / dd/yyyy')
from dual;
                                 70
                                                                -1


select max(sal) as maximum,min(sal)as minimum ,sum (sal)as sum,round
(avg(sal)) as average
From emp

                                                                -2

select job JOB, max(sal) MAXIMUM,min(sal) MINIMUM
from emp
group by job
order by job;

                                                                -3
Select distinct job, count (empno) as "count (*)"
From emp
Group by job
Order by job asc

                                                                -4
select count (distinct (mgr)) "number of managers"
from emp
where mgr is not null
                              83

                                                           -1
                                                  30
Select emp.ename, emp.deptno,dept.DNAME
From emp,dept
where emp.deptno like 30
and dept.dname like 'SALES
30                                                         -2

  select e.job, d.loc
  from emp e, dept d
  where e.deptno = d.deptno
  and e.deptno= 30
  group by job, loc
  order by job;
                                                           -3
                                         DALLAS
  select e.ename, e.job, d.dname
  from emp e, dept d
  where e.deptno = d.deptno
and loc like '%DALLAS%';
                                                           -4
                                   10
select ename,job,DNAME,sal,GRADE
from emp,dept,SALGRADE
where dept.deptno like 10
                                                           -5
                                   .10
  select worker.empno , worker.ename ,worker.mgr, manager.ename
  from emp worker , emp manager
  where worker.mgr = manager.empno
  and worker.deptno= 10;
                            96

                                                           -1
                                         BLAKE
 select ename, hiredate
 from emp
 where deptno= (select deptno
 from emp
 where ename = 'BLAKE')
  and job <>'CLERK' ;

                                                           -2

 select empno, ename
 from emp
 where sal> (select avg(sal) from emp)
 order by sal desc

(DALALLS)                                                  -3
 select ename,deptno,job
 from emp
 where deptno=(select deptno from dept where loc like 'DALLAS')
                                                           -4
                     (SALESMAN)
                                             (SALESMAN)
 select empno,ename,job,sal
 from emp
 where sal< all(select sal
 from emp
 where job like 'SALESMAN')
 and job<>'SALESMAN';

 select sal
 from emp
 where job like 'SALESMAN ;
                             105

                                                               -1

  select emp.ename,emp.deptno, emp.sal
  from emp
  where emp.comm is not null
  order by sal asc

                                                               -2
                                              DALLAS
select distinct e.ename,dal.dname ,e.sal
from emp e,(select m.sal,m.comm,d.dname
from emp m,dept d
where m.deptno=d.deptno
and d.loc = 'DALLAS')dal
where e.sal=dal.sal
and nvl(e.comm,0) = nvl(dal.comm,0)
order by sal

                                                               -3
                                                       SCOTT
select ename,hiredate,sal
from emp
where (sal,nvl(comm,0)) in (select sal,nvl(comm,0)
from emp
where ename = 'SCOTT')
                        and ename<>'SCOTT'
                             119
                                                               -1


  insert into emp
  (empno,ename,job,mgr,hiredate,sal,comm,deptno
  values
  (1111,'ahmed','engeneer',7839,'01/01/2004',5000,200,10)

  insert into emp
  (empno,ename,job,mgr,hiredate,sal,comm,deptno)
  values
  (2222,'saud','sales',7698,'05-02-2003',3000,100,20)

                                                               -2
                                    7000       AHMED
  update emp
  Set sal = 7000
  Where ename like 'ahmed'

                   900                                         -3
                                                        1000
update emp
Set sal = 1000
Where sal <900

                         2222                                  -4
delete from emp
Where empno= 2222
                                                      -5


insert into emp
values 2
 ((&empno,&ename,&job,&mgr,&hiredate,&sal,&comm,&deptno
Enter value for empno: 111
'Enter value for ename: 'nasser_alsumat
'Enter value for job: 'DBA
Enter value for mgr: 0
Enter value for hiredate: '01-01-2011
Enter value for sal: 12000
Enter value for comm: 1200
Enter value for deptno: 10
old 2: values
((&empno,&ename,&job,&mgr,&hiredate,&sal,&comm,&deptno
               'new 2: values (111,'nasser_alsumat','DBA
                             131
                       DEPARTMENT                         -1

Create table department
(id number(7),
name varchar2(25))
                           EMPLOYEE                       -2

Create table employee
(id number(7),
last_name varchar2(25),
first_name varchar2(25),
detp_id number(7))
     LAST_NAME               EMPLOYEE                     -3
                                          . (25)   (50)
alter table employee
modify(last_name varchar2(50))
           )                  EMPLOYEE2                   -4
              EMP                  (
          ID,LAST_NAME,DEPT_ID
create table employee
(ID,LAST_NAME,DEPT_ID)
as
select empno,ename,deptno
 from emp
                         FIRST_NAME                       -5
                                            EMPLOYEE2
alter table employee
 ((add (FIRST_NAME varchar2(25
                           EMPLOYEE                       -6
drop table employee

      EMPLOYEE              EMPLOYEE2                     -7
rename employee2 to employee

             EMPLOYEE              LAST_NAME              -8

alter table employee
 (drop (last_name
                            145
                      DEPARTMENT                            -1
    primary key

create table DEPARTMENT
(deptno number(15) primary key,
deptname varchar2(20),
deptloc varchar(100))
                          EMPLOYEE                          -2
                      UNIQUE
              primary key

  create table EMPLOYEE(empno number(3),ename
  varchar2(10),dname varchar2(10),
  constraint emp_pk primary key(empno),
  constraint emp_uk unique(dname));
                                 FOREINE KEY                -3

  alter table employee
  add constraint deptno_fk forign key(deptno) references
  dept(deptno);
         1000 5000                                          -4
  alter table employee
  add constraint sal_ck check(sal between 1000 and 5000);
                                                            -5

select constraint_name,column_name from user_cons_column
where table_name='EMPLOYEE';

          EMPLOYEE                                          -6
  select constraint-name,constraint_type
  from user_constraint
  where table_name='EMPLOYEE';

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:113
posted:1/8/2011
language:English
pages:13
Description: Structural Query Language