حل اسئلة الكتاب السعودى فى شرح قواعد البيانات اوراكل

  • Published on
    14-Jun-2015

  • View
    619

  • Download
    6

Embed Size (px)

DESCRIPTION

حل اسئلة الكتاب السعودى فى شرح قواعد البيانات اوراكل

Transcript

  • 1. Mohamed_SuezMohamed_SuezMohamed_SuezMohamed_Suez& HIS Wife& HIS Wife& HIS Wife& HIS WifeARABOUGARABOUGARABOUGARABOUG.org.org.org.org 3/3/2010^ S)SQL(bZ b ^ ^ V S _^^ ^ [ ^ [.

2. SQL> desc deptName Null? Type-----------------------------------------DEPTNO NOT NULL NUMBER(2)DNAMELOC VARCHAR2(13)-------------------------------------------------------------------------------------------SQL> select *2 from dept;DEPTNO DNAME LOC---------- -------------- -------------10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTONName Null? Type----------------------------------------- -------- ----------------------------DEPTNO NOT NULL NUMBER(2)VARCHAR2(14)LOC VARCHAR2(13)-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 3. SQL> select empno,ename,job,hiredate2 from emp;EMPNO ENAME JOB HIREDATE---------- ---------- --------- --------7369 SMITH CLERK 17/12/807499 ALLEN SALESMAN 20/02/817521 WARD SALESMAN 22/02/817566 JONES MANAGER 02/04/817654 MARTIN SALESMAN 28/09/817698 BLAKE MANAGER 01/05/817782 CLARK MANAGER 09/06/817788 SCOTT ANALYST 19/04/877839 KING PRESIDENT 17/11/817844 TURNER SALESMAN 08/09/817876 ADAMS CLERK 23/05/87EMPNO ENAME JOB HIREDATE---------- ---------- --------- --------7900 JAMES CLERK 03/12/817902 FORD ANALYST 03/12/817934 MILLER CLERK 23/01/8214 rows selected.SQL> select empno,ename,job,hiredateHIREDATE--------7369 SMITH CLERK 17/12/807499 ALLEN SALESMAN 20/02/817521 WARD SALESMAN 22/02/817566 JONES MANAGER 02/04/819/817698 BLAKE MANAGER 01/05/817782 CLARK MANAGER 09/06/817788 SCOTT ANALYST 19/04/877839 KING PRESIDENT 17/11/817844 TURNER SALESMAN 08/09/817876 ADAMS CLERK 23/05/87EMPNO ENAME JOB HIREDATE--------7900 JAMES CLERK 03/12/817902 FORD ANALYST 03/12/817934 MILLER CLERK 23/01/82 4. SQL> select distinct job2* from emp;JOB---------CLERKSALESMANPRESIDENTMANAGERANALYST 5. SQL> select empno employee_no, ename employee_name, job jobs2* from empEMPLOYEE_NO EMPLOYEE_N JOBS----------- ---------- ---------7369 SMITH CLERK7499 ALLEN SALESMAN7521 WARD SALESMAN7566 JONES MANAGER7654 MARTIN SALESMAN7698 BLAKE MANAGER7782 CLARK MANAGER7788 SCOTT ANALYST7839 KING PRESIDENT7844 TURNER SALESMAN7876 ADAMS CLERKEMPLOYEE_NO EMPLOYEE_N JOBS----------- ---------- ---------7900 JAMES CLERK7902 FORD ANALYST7934 MILLER CLERK14 rows selected.SQL> select empno employee_no, ename employee_name, job jobs 6. SQL> select ename,sal2 from emp3 where sal>28504 order by sal desc;ENAME SAL---------- ----------KING 5000FORD 3000SCOTT 3000JONES 2975 7. SQL> select ename,sal from emp where sal not between 1500 andENAME SAL--------------------SMITH 800WARD 1250JONES 2975MARTIN 1250SCOTT 3000KING 5000ADAMS 1100JAMES 950FORD 3000MILLER 1300and 2850 8. SQL> select ename,sal2 from emp3 where(deptno=10 or deptno=304 order by sal desc;ENAME SAL---------- ----------KING 5000BLAKE 2850CLARK 2450ALLEN 160030) and sal>1500 9. SQL> select ename,hiredate2 from emp3 where hiredate like%82;ENAME HIREDATE---------- --------MILLER 23/01/82=================================SQL> select hiredatefrom empwhere ename = SCOTT;HIREDATE---------19-APR-87 10. SQL> select ename,sal,comm2 from emp3 where comm is not null;ENAME SAL COMM---------- ---------- ----------ALLEN 1600 300WARD 1250 500MARTIN 1250 1400TURNER 1500 0 11. SQL> select ename2 from emp3 where ename like__A%;ENAME----------BLAKECLARKADAMS 12. SQL> select ename2 from emp3 where ename like%LL%;ENAME----------ALLENMILLER 13. 1 SQL> select initcap(ename) EMP_NAME,length(ename)NUMBER1,substr(ename,0,4) SECTION2* from empEMP_NAME NUMBER1 SECT---------- ---------- ----Smith 5 SMITAllen 5 ALLEWard 4 WARDJones 5 JONEMartin 6 MARTBlake 5 BLAKClark 5 CLARScott 5 SCOTKing 4 KINGTurner 6 TURNAdams 5 ADAMEMP_NAME NUMBER1 SECT---------- ---------- ----James 5 JAMEFord 4 FORDMiller 6 MILL SQL> select initcap(ename) EMP_NAME,length(ename)) SECTION 14. 1 SQL> select ename || job TITLE2 from emp3* where deptno=30TITLE-------------------ALLENSALESMANWARDSALESMANMARTINSALESMANBLAKEMANAGERTURNERSALESMANJAMESCLERKSQL> select ename || job TITLE 15. 1 SQL> select ename ENAME,round((months_between(sysdate,hiredate)/2* from empENAME ROUND((MONTHS_BETWEEN(SYSDATE,HIREDATE)/12),0)---------- ----------------------------------------------SMITHALLEN 29WARD 29JONES 29MARTINBLAKE 29CLARK 29SCOTT 23KING 28TURNERADAMS 23ENAME ROUND((MONTHS_BETWEEN(SYSDATE,HIREDATE)/12),0)---------- ----------------------------------------------JAMESFORD 28MILLER 28select ename ENAME,round((months_between(sysdate,hiredate)/ENAME ROUND((MONTHS_BETWEEN(SYSDATE,HIREDATE)/12),0)----------------------------------------------29ALLEN 29WARD 29JONES 29MARTIN 28BLAKE 29CLARK 29SCOTT 23KING 2828ADAMS 23ENAME ROUND((MONTHS_BETWEEN(SYSDATE,HIREDATE)/12),0)----------------------------------------------28FORD 28MILLER 28select ename ENAME,round((months_between(sysdate,hiredate)/12),0) 16. SQL > select TO_CHAR(SYSDATE,DAY "OF" MM YYYY)2* from dualTO_CHAR(SYSDATE,DAY--------------------FRIDAY OF 03 2010SQL > select TO_CHAR(SYSDATE,DAY "OF" MM YYYY) 17. SQL> Select to_date(FEBRUARY2 From dual;TO_DATE---------22-FEB-04SQL> Select to_date(FEBRUARY 22, 2004,mm / dd/yyyy) 18. SQL> select max(sal) MAXIMUM,min(sal) NINIMUM,sum(sal) SUM,round(avg(sal))AVERAGE;from empMAXIMUM NINIMUM SUM AVERAGE----------------------------------------5000 800 29025 2073 SQL> select max(sal) MAXIMUM,min(sal) NINIMUM,sum(sal) SUM,round(avg(sal))MAXIMUM NINIMUM SUM AVERAGESQL> select max(sal) MAXIMUM,min(sal) NINIMUM,sum(sal) SUM,round(avg(sal)) 19. SQL> select job,max(sal) as MAXIMUM,min(sal) as MINIMUMfrom emp;group by jobJOB MAXIMUM MINIMUM-----------------------------CLERK 1300 800SALESMAN 1600 1250PRESIDENT 5000 5000MANAGER 2975 2450ANALYST 3000 3000ob,max(sal) as MAXIMUM,min(sal) as MINIMUM 20. SQL> select job,count(job)from empgroup by job;JOB COUNT(JOB)-------------------CLERK 4SALESMAN 4PRESIDENT 1MANAGER 3ANALYST 2 21. SQL> select count(distinct mgr) NUMBER;from empNUMBER1 OF MANAGER------------------6NUMBER1 OF MANAGER 22. SQL> select e.ename,e.deptno,d.dname2 from emp e,dept d3 where e.deptno=d.deptno4* and e.deptno=30;ENAME DEPTNO DNAME---------- ---------- --------------ALLEN 30 SALESWARD 30 SALESMARTIN 30 SALESBLAKE 30 SALESTURNER 30 SALESJAMES 30 SALES6 rows selected.SQL> select e.ename,e.deptno,d.dname 23. SQL> select distinct( e.job),d.loc2 from emp e,dept d3 where e.deptno=d.deptno4* and e.deptno=30;JOB LOC--------- -------------SALESMAN CHICAGOMANAGER CHICAGOCLERK CHICAGOc 24. SQL> select e.ename,e.job,d.dname2 from emp e,dept d3 where e.deptno=d.deptno4* and d.loc like DALLASENAME JOB DNAME---------- --------- --------------SMITH CLERK RESEARCHJONES MANAGER RESEARCHSCOTT ANALYST RESEARCHADAMS CLERK RESEARCHFORD ANALYST RESEARCHSQL> select e.ename,e.job,d.dname 25. SQL> select e.ename,e.job,d.dname,e.sal,s.grade2 from emp e,dept d,salgrade s3 where (e.deptno=d.deptno4 and e.sal between s.losal and s.hisal)5 and e.deptno=106* order by sal;ENAME JOB DNAME---------- --------- -------------- ----------MILLER CLERK ACCOUNTING 1300 2CLARK MANAGER ACCOUNTING 2450 4KING PRESIDENT ACCOUNTING 5> select e.ename,e.job,d.dname,e.sal,s.gradeand e.sal between s.losal and s.hisal)ENAME JOB DNAME SAL GRADE---------- ----------MILLER CLERK ACCOUNTING 1300 2CLARK MANAGER ACCOUNTING 2450 4KING PRESIDENT ACCOUNTING 5000 5 26. SQL> select e.empno,e.ename,m.empno,m.ename2 from emp e,emp m3 where e.mgr=m.empno4 and e.deptno=10;EMPNO ENAME EMPNO ENAME---------- ---------- ---------- ----------7782 CLARK 7839 KING7934 MILLER 7782 CLARKSQL> select e.empno,e.ename,m.empno,m.ename---------- 27. SQL> select ename,hiredate2 from emp3 where deptno=(select deptno4 from emp5 where ename like BLAKE);ENAME HIREDATE---------- --------ALLEN 20/02/81WARD 22/02/81MARTIN 28/09/81BLAKE 01/05/81TURNER 08/09/81JAMES 03/12/816 rows selected. 28. SQL> select empno,ename2 from emp3 where sal> all(select avg(sal)4 from emp)5* order by sal desc;EMPNO ENAME---------- ----------7839 KING7902 FORD7788 SCOTT7566 JONES7698 BLAKE7782 CLARK6 rows selected. 29. SQL> select ename,deptno,job2 from emp3 where deptno=(select deptno4 from dept5* where loc like DALLAS)ENAME DEPTNO JOB---------- ---------- ---------SMITH 20 CLERKJONES 20 MANAGERSCOTT 20 ANALYSTADAMS 20 CLERKFORD 20 ANALYST* where loc like DALLAS); 30. SQL> select empno,ename,job,sal2 from emp3 where sal< all(select sal4 from emp5 where job like SALESMAN)6 and jobSALESMAN;EMPNO ENAME JOB SAL---------- ---------- --------- ----------7369 SMITH CLERK 8007876 ADAMS CLERK 11007900 JAMES CLERK 950SQL> select sal2 from emp3 where job like SALESMAN;SAL----------1600125012501500SQL> select empno,ename,job,salwhere job like SALESMAN)EMPNO ENAME JOB SAL----------7369 SMITH CLERK 800CLERK 11007900 JAMES CLERK 950 31. SQL> select ename,deptno,sal2 from emp3 where (sal,deptno) in (select sal,deptno4 from emp5 where comm is not null)6 order by sal;ENAME DEPTNO SAL---------- ---------- ----------MARTIN 30 1250WARD 30 1250TURNER 30 1500ALLEN 30 1600SQL> spool offere (sal,deptno) in (select sal,deptnowhere comm is not null)ENAME DEPTNO SALMARTIN 30 1250WARD 30 1250ALLEN 30 1600 32. SQL> select distinct e.ename,dal.dname ,e.sal2 from emp e,(select m.sal,m.comm,d.dname3 from emp m,dept d4 where m.deptno=d.deptno5 and d.loc = DALLAS)dal6 where e.sal=dal.sal7 and nvl(e.comm,0) = nvl(dal.comm,8 order by sal;ENAME DNAME SAL---------- -------------- ----------SMITH RESEARCH 800ADAMS RESEARCH 1100JONES RESEARCH 2975FORD RESEARCH 3000SCOTT RESEARCH 3000SQL> select distinct e.ename,dal.dname ,e.salfrom emp e,(select m.sal,m.comm,d.dnamewhere m.deptno=d.deptnoand d.loc = DALLAS)dal) = nvl(dal.comm,0) 33. SQL> select ename,hiredate,sal2 from emp3 where (sal,nvl(comm,0)) in (select sal,nvl(comm,4 from emp5 where ename = SCOTT)6 and enameSCOTT;ENAME HIREDATE SAL---------- -------- ----------FORD 03/12/81 3000SQL> spool off)) in (select sal,nvl(comm,0)ENAME HIREDATE SALFORD 03/12/81 3000 34. SQL>insert into empvalues(1111,AHMED,ENGENEER,1 row created.============================================commit--SQL >update empset sal=7000where ename=AHMED;1 row updated.,AHMED,ENGENEER,7839,01-01-2004,5000,200,10);=============== 35. SQL> update emp2 set sal = 10003 where salcommit 36. SQL> delete from emp2 where empno=1111;1 row deleted. 37. SQL> insert into emp2 values (&empno,&ename,&job,&mgr,&hiredate,&sal,&comm,&deptno);Enter value for empno: 111Enter value for ename: Moh_SuezEnter value for job: DBAEnter value for mgr: 0Enter value for hiredate: 01-01-2010Enter value for sal: 12000Enter value for comm: 1200Enter value for deptno: 10old 2: values (&empno,&ename,&job,&mgr,&hiredate,&sal,&comm,&deptno)new 2: values (111,Moh_Suez,DBA,1 row created.values (&empno,&ename,&job,&mgr,&hiredate,&sal,&comm,&deptno);Enter value for ename: Moh_Suez2010: values (&empno,&ename,&job,&mgr,&hiredate,&sal,&comm,&deptno),Moh_Suez,DBA,0,01-01-2010,12000,1200,10)values (&empno,&ename,&job,&mgr,&hiredate,&sal,&comm,&deptno);: values (&empno,&ename,&job,&mgr,&hiredate,&sal,&comm,&deptno) 38. SQL> create table department(Id number(7),Name varchar2(2525)); 39. SQL> create table EMPLOYEE2 (ID number(7),LAST_NAME varcharnumber(7));Table created.),LAST_NAME varchar2(25),FIRST_NAME varchar2(2525),DEPT_ID 40. SQL> alter table employee2 modify ( 3 LAST_NAME varcharTable altered.LAST_NAME varchar2(50)); 41. SQL> create table employee2 (ID,LAST_NAME,DEPT_ID)3 as4 select empno,ename,deptno5 from emp;Table created.******SQL> select*2 from employee;ID LAST_NAME DEPT_ID---------- ---------- ----------7369 SMITH 207499 ALLEN 307521 WARD 307566 JONES 207654 MARTIN 307698 BLAKE 307782 CLARK 107788 SCOTT 207839 KING 107844 TURNER 307876 ADAMS 20ID LAST_NAME DEPT_ID---------- ---------- ----------7900 JAMES 307902 FORD 207934 MILLER 1014 rows selected.SQL> create table employeeas4 select empno ID,enameLAST_NAME,deptno DEPT_ID5 from emp;Table created.******SQL> select *2 from employee;ID LAST_NAME DEPT_ID---------- ---------- ----------7369 SMITH 207499 ALLEN 307521 WARD 307566 JONES 207654 MARTIN 307698 BLAKE 307782 CLARK 107788 SCOTT 207839 KING 107844 TURNER 307876 ADAMS 20ID LAST_NAME DEPT_ID---------- ---------- ----------7900 JAMES 307902 FORD 207934 MILLER 1014 rows selected. 42. SQL> alter table employee22 add (FIRST_NAME varchar2(25Table altered.SQL> drop table employee;Table dropped.SQL> Rename employee2 to employee;Table renamed.SQL> alter table employeedrop (last_name);Table altered25));to employee; 43. SQL> create table DEPARTMENT ( deptno number(varchar2(10),loc varchar2(15));Table created. SQL> create table DEPARTMENT ( deptno number(3) primary key,dname) primary key,dname 44. SQL> create table EMPLOYEE(empno number(varchar2(10),2 constraint emp_pk primary key(empno),3* constraint emp_uk unique(dname) ) ;Table created.SQL> create table EMPLOYEE(empno number(3) ,ename varchar2(10),dnamep_pk primary key(empno),* constraint emp_uk unique(dname) ) ;),dname 45. SQL> alter table employeeadd constraint deptno_fk forign key(deptno) references dept(deptno);Table altered.SQL> alter table employeeadd constraint sal_ck check(sal betweenTable altered.SQL> select constraint_name,column_namefrom user_cons_columnwhere table_name=EMPLOYEE;XXXXXXXXXXXXXXXXXXSQL> select constraint-name,constraint_typefrom user_constraintwhere table_name=EMPLOYEE;CONSTRAINT_NAME C------------------------------ -PK_EMP PFK_DEPTNO Radd constraint deptno_fk forign key(deptno) references dept(deptno);al between 1000 and 5000);SQL> select constraint_name,column_namewhere table_name=EMPLOYEE;name,constraint_typewhere table_name=EMPLOYEE;