sql課后題.doc

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