sql課后題.doc

1 作業(yè)(1)從表中查詢出數(shù)據(jù)查詢出scott.dept表中部門號（deptno）小于50的部門名稱(dname)此處有隱式轉(zhuǎn)換，oracle把varchar2轉(zhuǎn)換成了數(shù)字。(2)描述表scott.emp的結(jié)構(gòu)(3)進行數(shù)學(xué)表達式的計算、使用列的別名從scott.emp表中查出所有員工的工資和獎金之和。(4) 正確統(tǒng)計出scott.emp表中部門號為10的，每個員工全年收入(5)運用 SQL*Plus工具將緩沖區(qū)中的SQL語句保存到一個文件，并執(zhí)行該文件中的SQL語句。SQL L 1* select * from testSQL save /u01/test02.sql上面（3）中的結(jié)果：2作業(yè)1。創(chuàng)建一個查詢，來顯示雇員的名字和工資。其工資要在$2850以上。將該語句保存到一個文件中：p2q1.sql在sqlplus中執(zhí)行該文件。2.更改文件p2q1.sql中的命令，將查詢條件改為：工資（sal）在$1500和$2850之間。重新執(zhí)行該文件。3.查詢出雇員表中部門號為10和30的所有員工的姓名、工作、雇傭日期信息，要求按照雇傭日期的降序進行排序?；螂[身轉(zhuǎn)換：4.查詢出姓名中第三個字母為：“A”的所有員工的姓名。5.查出傭金比工資多出10%的員工的姓名。簡單寫成：復(fù)雜寫成：注意如果sal有空的（注意分母不能為0，而且保持原來的數(shù)據(jù)最好用1）：這道題顛倒一下就很難了：3 作業(yè)以下練習(xí)題使用hr用戶登錄 1.查詢所有員工及對應(yīng)部門的記錄，包括沒有對應(yīng)部門編號(department_id)的員工記錄 a.XXX=b.XXX(+) 等價于 a left outer join b on (a.XXX=b.XXX);左外連接。2.查詢所有員工及對應(yīng)部門的記錄，包括沒有任何員工的部門記錄。 a.XXX(+)=b.XXX 等價于 a right outer join b on (a.XXX=b.XXX);右外連接。3.查詢所有員工及對應(yīng)部門的記錄，包括沒有對應(yīng)部門編號(department_id)的員工記錄和沒有任何員工的部門記錄。 全外連接只有一種寫法：a all outer join b on (a.XXX=b.XXX);4.寫一個查詢來查詢出雇員的名字，部門號，部門名稱。5.輸出30號部門的所有工作的列表，以及部門名稱。6.寫一個查詢，來查詢出掙到傭金（comm）的雇員姓名，部門名稱，和部門所處的位置。7.寫一個查詢，查出在名字里面有一個A的所有雇員姓名和其所在的部門的名稱?；蛘撸?.寫出一個查詢，查出工作在DALLAS的所有員工的姓名、工作、部門號、部門名稱。9.查出每個雇員的編號、姓名、其管理者的編號和姓名。各個列分別命名為：Emp#,EmpName,EmpM#,EmpName.注意看下面的語句：上述表是沒有管理者的命名的，我們可以用自連接：如果a.id=b.上司號，那么這時，a的信息就是當(dāng)領(lǐng)導(dǎo)的人的信息（不管管幾個人）。也就是a表中的哥們是b表中哥們兒的的領(lǐng)導(dǎo)。這時a表中有king，而且顯示出了該領(lǐng)導(dǎo)管理的人員名單。如果a.上司號=b.id，那么這時，a的信息就是有上司的人的信息（也就是有領(lǐng)導(dǎo)的人的信息）。也就是b表中的哥們兒是a表中哥們的領(lǐng)導(dǎo)。9題結(jié)果：10.修改第9題的查詢，使得沒有管理者的員工的信息也可以顯示出來。4作業(yè)1.查詢出入職超過一年的員工信息 2 查詢出每個員工從工齡有多少個周3 今年公司準(zhǔn)備給職位為ANALYST工資漲10%，CLERK工資漲15%，MANAGER漲20%，其它職位不變，請使用一個select語句模擬工資漲之后的各個員工工資或?qū)懗桑?.寫一個查詢，選擇出當(dāng)前的日期。5.查詢出雇員表（emp)表中的雇員號、姓名、新的薪水（是原先薪水的1.25倍），并將該列標(biāo)記為new salary。將該腳本保存到p3q2.sql中。6.運行文件p3q2中的查詢。7.查詢出雇員表中所有員工的姓名；雇傭日期；和工資調(diào)整日期（工作6個月后的第一個星期一）。其格式要求是類似于：“1981-09-23”。8.查出每個員工的名字（ename）、雇傭日期到現(xiàn)在的日期間隔的月數(shù)（要求為整數(shù)），該列命名為MONTHS_WORKED，并以該列的降序排序9.寫一個查詢，查詢出雇員的姓名，以及姓名的長度。要求其姓名的第一個字母為大寫，其它的字母為小寫。5作業(yè)1.統(tǒng)計每個部門的平均工資，和最高工資，最低工資，并按照部門號排序 我表里有兩個沒部門的人，所以你的結(jié)果可能和我不同，是正常的。2.查詢出平均工資高于2000的部門以及其平均工資 6 作業(yè)創(chuàng)建多列的子查詢。在基于未知值的查詢中運用子查詢。使用子查詢，查詢在一個數(shù)據(jù)集中存在，而在另一個數(shù)據(jù)集中不存在的數(shù)據(jù)。1.查詢出部門位置為“DALLAS”的員工號，姓名，工資，職位，部門號 2查詢出部門位置為“DALLAS” ”，“NEW YORK”的員工號，姓名，工資，職位，部門號3.查詢出與員工號為7788的job和sal相匹配的員工信息 4.查詢出與員工號為7788的job或者sal相匹配的員工信息 5.查詢出其經(jīng)理不是10部門經(jīng)理的員工信息注意10部門經(jīng)理可以有空值的。6.查出與BLACK在同一個部門的所有雇員的姓名和雇傭日期。7.查出工資高于平均工資的所有員工的編號和姓名，其結(jié)果按照工資的降序排列。如果是高于本部門的員工：8.查出所有名字中包含一個T的員工所在的部門號，和這些部門所包含的所有員工編號和姓名。9.查出所有工作在Dallas的員工的姓名、部門編號、工作。10.查出由King直接管轄的所有員工的姓名和工資。11.查詢銷售部（Sales)的編號，所有員工的名字和工作。12.寫出與任何一個能掙到傭金的雇員的部門號、薪水相匹配的所有雇員的名字、部門號。13.查詢出與在Dallas的任何一個雇員的薪水和傭金相匹配的所有雇員的姓名、部門名稱和工資。14.查詢出所有工資和傭金與Scott相同的雇員的姓名、雇傭日期和工資。注意：結(jié)果中不包括Scott應(yīng)該nvl一下，否則：15去除重復(fù)數(shù)據(jù) ：這兩條數(shù)據(jù)沒有什么不同，除了rowid。這些是原先的數(shù)據(jù)。這些是新的數(shù)據(jù)。道理很簡單，如果沒有重復(fù)數(shù)據(jù)，那這條數(shù)據(jù)的rowid既是最大的，也是最小的。如果有重復(fù)的數(shù)據(jù)，那么一般而言rowid小的是老數(shù)據(jù)，rowid大的是新數(shù)據(jù)（不是絕對的，請注意），在此只要保留一個就行了。delete from 就可以刪除新的數(shù)據(jù)：7 作業(yè)創(chuàng)建使用替換變量的查詢。創(chuàng)建包含變量的命令文件。使用ACCEPT 命令1.使用替換變量，提示用戶輸入職位名稱，查詢出員工號，姓名，工資，部門號 2.預(yù)定義一個職位名稱的一個變量，并在查詢出員工號，姓名，工資，部門號注意define最好給個初值?；颍?.設(shè)置sqlplus環(huán)境的linesize為100，并讓這個變量永久生效 4. 寫一個腳本，要求顯示某個雇傭時間范圍內(nèi)，所有雇員的姓名、工作和雇傭日期的信息。要求：將名字和工作連接起來，中間用“,”隔開，將列命名為EMPLOYEES和HIREDATE。并使用ACCEPT提示客戶輸入兩個時間范圍。頭標(biāo)題為：employee and hiredate info其輸出結(jié)果應(yīng)當(dāng)類似下面： Please enter the low date range (MM/DD/YYYY): 01/01/1981 Please enter the high date range (MM/DD/YYYY): 01/01/1982 EMPLOYEES HIREDATE - - KING, PRESIDENT 17-NOV-81 BLAKE, MANAGER 01-MAY-81 CLARK, MANAGER 09-JUN-81 JONES, MANAGER 02-APR-81 MARTIN, SALESMAN 28-SEP-81 ALLEN, SALESMAN 20-FEB-81 TURNER, SALESMAN 08-SEP-81 JAMES, CLERK 03-DEC-81 WARD, SALESMAN 22-FEB-81 FORD, ANALYST 03-DEC-81 10 rows selected.首先拼出所要的語句：然后替換需要的值：再寫出提示：注意引號需要轉(zhuǎn)義。實驗一下：最后形成報表：最后看一下結(jié)果： 8作業(yè)（1）向表中插入數(shù)據(jù)行（2）修改和刪除表中的數(shù)據(jù)行（3）控制事務(wù)1.將員工信息表的部門號為10的員工備份到emp01表，emp01的表結(jié)構(gòu)與emp表結(jié)構(gòu)保持一致 2.更改7698號雇員變更到與7499號雇員相同的部門，并且職位也一樣 3.做一個保持點的實例 .在dept表中插入兩行數(shù)據(jù)：.將上面的數(shù)據(jù)刪除。.定義一個事務(wù)：.將60號部門的位置改為“TIANJIIN”9 作業(yè)（1）使用 CREATE TABLE AS 語句來創(chuàng)建一個新表（2）更改列定義（3）驗證這個表是存在的（4）為該表增加注釋（5）更改tables結(jié)構(gòu)（6）廢棄該表1.使用子查詢創(chuàng)建一個dept10表，結(jié)構(gòu)與dept表一致 2.從數(shù)據(jù)字典中查詢，驗證dept10表是否創(chuàng)建 3.將表dept10的列“DNAME”的數(shù)據(jù)類型改為“VARCHAR2(20)” 4.給表dept10添加注釋為“this is dept Information”5.仿照dept表的格式創(chuàng)建表department并將dept中的數(shù)據(jù)插入。6.為dept增加一個新的列，該列的名字為descript,類型為varchar2(100)。缺省值為new column。7.創(chuàng)建如下結(jié)構(gòu)的EMPLOYEE表： Name Null? Type - - - ID NUMBER(7) LAST_NAMEVARCHAR2(50) FIRST_NAMEVARCHAR2(25) DEPT_IDNUMBER(7)8.查詢數(shù)據(jù)字典表或者使用describe命令確認上表的存在。并將它改名為：EMPLOYEE_BAK9.為表EMPLOYEE_BAK增加一條注釋：“this is a duplicated table”.duplicated 意思是復(fù)制的。10 作業(yè)1.創(chuàng)建一個結(jié)構(gòu)與EMP表相同結(jié)構(gòu)的EMPLOYEE表，使用ALTER TABLE語句為該表的empno列增加一個表級的主鍵約束，并命名該約束。2.創(chuàng)建一個結(jié)構(gòu)與DEPT表相同結(jié)構(gòu)的DEPARTMENT表，在創(chuàng)建表時為該表的deptno列增加一個表級的主鍵約束，并命名該約束。3.為EMPLOYEE表增加一個外鍵約束，保證雇員不能安排到一個不存在的系。4.從數(shù)據(jù)字典視圖USER_CONSTRAINTS中查詢出所添加約束的名稱、類型。11 作業(yè)1.創(chuàng)建一個叫做EMP_VU的視圖，它的列分別來自于EMP表的empno,ename,deptno；將ename列改名為EMPLOYEE。該視圖能夠獲取EMP所有行的信息。不允許從該視圖更改數(shù)據(jù)?；?2.查詢出EMP_VU的所有數(shù)據(jù)。并試圖更改SMITH的名字為“JHON”。3.從數(shù)據(jù)字典USER_VIEWS中，獲取視圖名稱（VIEW_NAME）和視圖文本（TEXT）的信息。4.創(chuàng)建一個叫做DEPT20的視圖，該視圖中包括第20號部門的雇員號，雇員名字，部門名稱；分別命名為：EMPLOYEE_ID, EMPLOYEE, 和 DEPARTMENT_NAME。或12作業(yè)（1）創(chuàng)建一個序列（2）使用序列（3）創(chuàng)建一個非唯一索引（4）獲取并展示關(guān)于序列和索引的數(shù)據(jù)字典信息（5）丟棄索引擴展作業(yè)：1.創(chuàng)建一個序列來產(chǎn)生表DEPARTMENT的主鍵值。該序列名字為DEPT_ID_SEQ，它的初始值為60，最大值為200，每次遞增10,并且不能循環(huán)使用這些值。2.從user_sequences視圖中查出序列名稱、最大值、遞增值、何下一個值的信息。3.寫一個腳本，往DEPARTMENT表中插入兩行信息，要求使用上面創(chuàng)建的序列來產(chǎn)生部門號，并提示用戶輸入部門名稱和部門地址。執(zhí)行該腳本。vi /home/oracle/input.sql4.在EMPLOYEE表中的deptno列上創(chuàng)建一個非唯一索引deptno_idx。創(chuàng)建非唯一性索引：如果創(chuàng)建唯一性索引就是：13作業(yè)1.創(chuàng)建test01,test02的2個用戶，密碼和用戶名一樣 2.賦予test01用戶在user表空間有創(chuàng)建視圖的權(quán)限 沒有users表空間的先創(chuàng)建一下：下面先給用戶分配配額：或：再賦予創(chuàng)建視圖的權(quán)限：3.賦予test02用戶能查詢scott.emp表的權(quán)限 grant create session to test02;有級聯(lián)是：4.收回test01用戶在user表空間創(chuàng)建視圖的權(quán)限 5.使用DBA賬戶創(chuàng)建一個賬號為temp,密碼為temp用戶,并授予會話創(chuàng)建權(quán)限：CREATE SESSION6.以SCOTT用戶登錄，將查詢Scott用戶的emp表的權(quán)限授權(quán)給temp用戶如果是想級聯(lián)授予可以加with grant option;7.使用t