ÄÚÈÝ·¢²¼¸üÐÂʱ¼ä : 2026/6/7 18:21:16ÐÇÆÚÒ» ÏÂÃæÊÇÎÄÕµÄÈ«²¿ÄÚÈÝÇëÈÏÕæÔĶÁ¡£

ÐÕÃû£º_______________________ Personal Number:_________________ °à¼¶£º____________________

Oracle SQL&PL/SQL Test

Ò»¡¢

1. ÓÃÏÂÁдúÂë¿é»Ø´ðÎÊÌ⣺

SQL> select ______(-45) as output from dual; OUTPUT ------

-45

ÏÂÁÐÄĸöµ¥Ðк¯ÊýÎÞ·¨²úÉúÕâ¸öÊä³ö£¿

A£®abs() B£®ceil() C£®floor() D£®round()

2. ÓÃÏÂÁдúÂë»Ø´ðÎÊÌ⣺

Evaluate the set of SQL statements: CREATE TABLE dept (deptno NUMBER(2), dname VARCNAR2(14), loc VARCNAR2(13)); ROLLBACK; DESCRIBE DEPT What is true about the set? ÄĸöÓï¾äÊÇÕýÈ·µÄ£¿

A. The DESCRIBE DEPT statement displays the structure of the DEPT table. B. The ROLLBACK statement frees the storage space occupies by the DEPT table.

C. The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist. D. The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT statement introduced before the ROLLBACK statement.

3. ÓÃÏÂÁдúÂë»Ø´ðÎÊÌ⣺

Examine the structure of the EMPLOYEES and DEPARTMENTS tables: ¼ì²éÈçϱí½á¹¹ EMPLOYEES

Column name Data type Remarks

EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_NAME VARCHAR2 (30) JOB_ID VARCHAR2 (20) SALARY NUMBER

MGR_ID NUMBER References EMPLOYEE_ID COLUMN DEPARTMENT ID NUMBER Foreign key to DEPARTMENT ID column of the DEPARTMENTS table DEPARTMENTS

Ñ¡ÔñÌâ(45 * 2)

ÐÕÃû£º_______________________

Column name Data type Remarks

Personal Number:_________________ °à¼¶£º____________________

DEPARTMENT_ID NUMBER NOT NULL, Primary Key DEPARTMENT_NAME VARCHAR2(30)

MGR_ID NUMBER References MGR_ID column of the EMPLOYEES table

Evaluate this SQL statement:

SELECT employee_id, e.department_id, department_name, salary

FROM employees e, departments d WHERE e.department_id = d.department_id;

Which SQL statement is equivalent to the above SQL statement? Ò»ÏÂÄĸöÓï¾äºÍÉÏÃæµÄÓï¾äÖ´Ðнá¹ûÏàͬ£¿

A. SELECT employee_id, department_id, department_name,salary FROM employees

WHERE department_id IN (SELECT department_id FROM departments);

B. SELECT employee_id, department_id, department_name,salary FROM employees

NATURAL JOIN departments;

C. SELECT employee_id, d.department_id, department_name,salary FROM employees e JOIN departments d

ON e.department_id = d.department_id;

D. SELECT employee_id, department_id, department_name,Salary FROM employees JOIN departments

USING (e.department_id, d.department_id);

4. ÔËÐÐÏÂÁÐÄĸö²éѯʱ»á²úÉú´íÎó£¿

A£®select deptno, job, sum(sal) from emp group by job, deptno; B£®select sum(sal), deptno, job from emp group by job, deptno; C£®select deptno, job, sum(sal) from emp;

D£®select deptno, sum(sal), job from emp group by job, deptno;

5. ¼ì²éÏÂÁÐSQLµÄÊä³ö

SQL> select a.deptno,a.job,b.loc,sum(a.sal) 2 from emp a,dept b 3 where a.deptno = b.deptno 4 group by a.deptno,a.job,b.loc 5 order by sum(a.sal);

Õâ¸ö²éѯ½á¹û½«ÒÔÄĸöÁеÄ˳ÐòÊä³ö£¿

A.DEPTON B£®A.JOB C£®B.LOC D£®SUM(A.SAL)

6. Òª²éѯµÄPROFITS±í´æ´¢¹«Ë¾²»Í¬µØÇø¡¢²úÆ·ÀàÐͺͼ¾¶ÈµÄÀûÈóÐÅÏ¢¡£ÏÂÁÐÄĸöSQLÓï¾ä°´²»Í¬µØÇø¡¢

²úÆ·ÀàÐͺͼ¾¶ÈÇó³öƽ¾ùÀûÈó´óÓÚ100000ÃÀÔªµÄÀûÈó£¿

ÐÕÃû£º_______________________

Personal Number:_________________ °à¼¶£º____________________

A£®select region, prod_type, period, avg(profits) from profits where avg(prodit) > 100000 group by region, prod_type, period;

B£®select region, prod_type, period, avg(profits) from profits where avg(prodit) > 100000 order by region, prod_type, period;

C£®select region, prod_type, period, avg(profits) from profits group by region, prod_type, period having avg(prodit) > 100000;

D£®select region, prod_type, period, avg(profits) from profits group by region, prod_type, period having avg(prodit) < 100000;

7. ¿´¿´ÏÂÁÐÓï¾ä£º

SQL> select ename 2 from emp 3 where empno in 4 ( select empno 5 from expense

6 where vouch_amt > 10000 );

ÏÂÁÐÄĸöSQLÓï¾ä²úÉúÓëÉÏÃæÏàͬµÄÊä³ö£¬¸Äд³Éν´ÊÔËËã·û

A£®select e.ename from emp e where exists(select x.empno from expense x where x.vouch_amt>10000) and x.empno = e.empno;

B£®select e.ename from emp e where exists(select x.empno from expense x where x.vouch_amt>10000 and x.empno = e.empno);

C£®select e.ename from emp e where x.empno = e.empno and exists(select x.empno from expense x where x.vouch_amt>10000)

D£®select e.ename from emp e, expense x where x.empno = e.empno and x.vouch_amt>10000 and exists(select x.empno from expense x where)

8. ¹«Ë¾ÏúÊÛÊý¾Ý¿âÓÐÁ½¸ö±í£¬PROFITS´æ´¢²»Í¬µØÇø²»Í¬¼¾¶ÈµÄ²úÆ·ÏúÊÛÀûÈó£¬REGIONS´æ´¢Ã¿¸ö²¿ÃŵØ

ÇøÃû³Æ¡¢¸ÃµØÇø×ܲ¿µØÖ·ºÍ¸ÃµØÇø¸±×ܲÃÐÕÃû¡£ÏÂÁÐÄĸö²éѯȡµÃSMITHERS¡¢FUJIMORIÓëLIKKARAJUÁìµ¼µÄµØÇøµÄÍæ¾ßÏúÊÛÀûÈó£¿

A£®select sum(profit) from profits where region in (select region from regions where reg_head in (¡®SMITHERS¡¯, ¡®FUJMORI¡¯, ¡®LAKKARAJU¡¯)) and product =¡¯TOYS¡¯;

B£®select sum(profit) from profits where region like (select region from regions where reg_head in (¡®SMITHERS¡¯, ¡®FUJMORI¡¯, ¡®LAKKARAJU¡¯) and product =¡¯TOYS¡¯ );

C£®select sum(profit) from profits where region = (select region from regions where reg_head in (¡®SMITHERS¡¯, ¡®FUJMORI¡¯, ¡®LAKKARAJU¡¯)) and product =¡¯TOYS¡¯;

D£®select sum(profit) from profits where region is (select region from regions where reg_head in (¡®SMITHERS¡¯, ¡®FUJMORI¡¯, ¡®LAKKARAJU¡¯)) and product =¡¯TOYS¡¯;

9. ÓÃÏÂÁдúÂë»Ø´ðÎÊÌ⣺

The EMP table contains these columns: LAST NAME VARCHAR2(25) SALARY NUMBER(6,2) DEPARTMENT_ID NUMBER(6)

ÐÕÃû£º_______________________

²¿ÃŵĹÍÔ±

You write the SELECT statement:

Personal Number:_________________ °à¼¶£º____________________

You need to display the employees who have not been assigned to any department. Èç¹ûÐèÒªÏÔʾ»¹Ã»Óб»·ÖÅä

SELECT LAST_NAME, SALARY, DEPARTMENT_ID FROM EMP

WHERE DEPARTMENT_ID = NULL; What is true about this SQL statement? ÒÔÏÂÓï¾äÄĸöÕýÈ·

A. The SQL statement displays the desired results.

B. The column in the WHERE clause should be changed to display the desired results. C. The operator in the WHERE clause should be changed to display the desired results. D. The WHERE clause should be changed to use an outer join to display the desired results.

10. ¹«Ë¾ÏúÊÛÊý¾Ý¿â°üº¬Ò»¸öPROFITS±í£¬°´²úÆ·Ãû¡¢ÏúÊÛµØÇøºÍ¼¾¶ÈÁгöÀûÈóÐÅÏ¢¡£Èç¹ûҪȡµÃ¹«Ë¾Îå¸ö

×Ïú²úÆ·Çåµ¥£¬¿ÉÒÔÓÃÏÂÁÐÄĸöSQLÓï¾ä£º

A£®select p.prod_name, p.profit from (select prod_name, profit from profits order by profit desc) where rownum <= 5; B£®select p.prod_name, p.profit from (select prod_name, sum(profit) from profits group by prod_name order by sum(profit) desc) subq where p.prod_name = subq.prod_name;

C£®select p.prod_name, p.profit from (select prod_name, sum(profit) from profits group by prod_name order by sum(profit) desc) where rownum <= 5;

D£®select p.prod_name, p.profit from (select prod_name, sum(profit) from profits order by sum(profit) desc) where rownum <= 5;

11. OracleÖÐÒªÉú³ÉÊý¾Ý¿â±í,ÏÂÁÐÄĸö±íÉú³ÉÓï¾äÊÇÎÞЧµÄ£¿

A£®create table cats(c_name varchar2(10), c_weight number, c_owner varchar2(10)); B£®create table my_cats as select * from cats where owner = ¡®ME¡¯;

C£®create global temporary table temp_cats(c_name varchar2(10), c_weight number, c_owner varchar2(10)); D£®create table 51cats as select c_name, c_weight from cats where c_weight > 5;

12. ÊÔͼÔÚOracleÉú³É±íʱÓöµ½ÏÂÁдíÎó£ºORA-00955-name is already used by existing object¡£ÏÂÁÐÄĸöÑ¡Ïî

ÎÞ·¨¾ÀÕýÕâ¸ö´íÎó£¿

A£®ÒÔ²»Í¬µÄÓû§Éí·ÝÉú³É¶ÔÏó¡£ B£®É¾³ýÏÖÓÐͬÃû¶ÔÏó C£®¸Ä±äÉú³É¶ÔÏóÖеÄÁÐÃû¡£ D£®¸üÃûÏÖÓÐͬÃû¶ÔÏó¡£

13. SALES±íÖеÄPROFITSÁÐÉùÃ÷ΪNUMBER(10, 2)¡£ÏÂÁÐÄĸöÖµÎÞ·¨ÔÚÕâ¸öÁÐÖд洢£¿

A£®5392845.324 B£®871039453.1 C£®75439289.34 D£®60079829.25

14. ÓÃÏÂÁдúÂë»Ø´ðÎÊÌ⣺

Evaluate the SQL statement:

SELECT ROUND(TRUNC(MOD(1600,10),-1),2) FROM dual;

What will be displayed?ÒÔÏÂÏÔʾ½á¹ûÄĸöÕýÈ·£¿

ÐÕÃû£º_______________________

A. 0 B. 1 C. 0.00

D. An error statement

15. ÓÃÏÂÁдúÂë»Ø´ðÎÊÌ⣺

Personal Number:_________________ °à¼¶£º____________________

Examine the description of the MARKS table: STD_ID NUMBER(4)

STUDENT_NAME VARCHAR2(30) SUBJ1 NUMBER(3) SUBJ2 NUMBER(3)

SUBJ1 and SUBJ2 indicate the marks obtained by a student in two subjects. SUBJ1 and SUBJ2·Ö±ðÃèÊöµÄÊÇÒ»¸öѧÉúÁ½Ãſγ̵ijɼ¨ Examine this SELECT statement based on the MARKS table: SELECT subj1+subj2 total_marks, std_id FROM marks

WHERE subj1 > AVG(subj1) AND subj2 > AVG(subj2) ORDER BY total_marks;

What is the result of the SELECT statement? ÒÔÉÏÓï¾äµÄÖ´Ðнá¹ûÊÇÄÄÒ»¸ö£¿

A. The statement executes successfully and returns the student ID and sum of all marks for each student who obtained more than the average mark in each subject. B. The statement returns an error at the SELECT clause. C. The statement returns an error at the WHERE clause. D. The statement returns an error at the ORDER BY clause. E. This feature cannot be implemented through /SQL*Plus

16. SALES±íÖж¨ÒåµÄ¼ì²éÔ¼Êø°üº¬Á½¸öÁÐPRODUCT_NAMEÓëSALE_PERIOD¡£ÏÂÁÐÄÄЩѡÏî±íʾ¶¨Òå

µÄ¼ì²éÔ¼Êø£¿Ñ¡ÔñÁ½¸ö¡£

A£®alter table sales add constraint ck_sales_01 check(product_type in(¡®TOYS¡¯, ¡®HOT DOGS¡¯¡®,PALM PILOTS¡¯)); B£®alter table sales add constraint ck_sales_01 check(product_type in(select product_type from valid_products)); C£®alter table sales modify(product_type varchar2(30) check(product_type in(¡®TOYS¡¯, ¡®HOT DOGS¡¯,¡®PALM PILOTS¡¯)));

D£®alter table sales add(product_name varchar2(30) check(product_name <> ¡®AK-47¡¯));

17. ÄãÒª¹Ø±ÕSALES±íÖÐUNIT_PRICEÁеķÇNULLÔ¼Êø¡£ÏÂÁÐÄĸöÓï¾äÄÜÍê³ÉÕâ¸ö²Ù×÷£¿

A£®alter table sales modify(unit_prices null); B£®alter table sales modify(unit_prices not null); C£®alter table sales add(unit_prices null); D£®alter table sales add(unit_prices not null);

18. ÔÚOracleÖУ¬¿ÉÓÃÓÚÌáÈ¡ÈÕÆÚʱ¼äÀàÐÍÌض¨²¿·Ö£¨ÈçÄê¡¢Ô¡¢ÈÕ¡¢Ê±¡¢·Ö¡¢Ã룩µÄº¯ÊýÓУ¨£©£¬Ñ¡ÔñÁ½¸ö

A. DATEPART