Oracle Pen Questions

Oracle Pen Questions
First, single choice
1. In Oracle, the following are not part of the set operator ().
A.unionb. SUMC. MINUSD. INTERSECT
2. In Oracle, execute the following statement:
Selectceil (-97.342),
Floor (-97.342),
Round (-97.342),
Trunc (-97.342)
fromdual;

The return value of which function is not equal to-97 ().
A.ceil () B.floor () c.round (0) D.trunc ()

3. Which of the following commands can be used to drop column UPDATE_DT () from table state.
A.altertablestatedropcolumnupdate_dt;
B.altertablestateremovecolumnupdate_dt;
C.dropcolumnupdate_dtfromstate;
D. You cannot drop the column from this table.
4. Which command is used to create a primarykeyconstraintpk_books on table Books, column ISBN, select one ().
A.createprimarykeyonbooks (ISBN);
B.createconstraintpk_booksprimarykeyonbooks (ISBN);
C.altertablebooksaddconstraintpk_booksprimarykey (ISBN);
D.altertablebooksaddprimarykey (ISBN);
5. The following line is wrong ().
1x:=y+200;
2ifx<10then
3y:=30;
4elseifx<40then
5y:=20;
6ENDIF;
7ENDIF;
A.line2b.line3c.line4d.line5
6. A view is created by the following statement, what can be done on the view ()
Createviewusa_states
Asselect*fromstate
Wherecnt_code=1
Withreadonly;
A.SELECTB. Select,updatec. Select,deleted. Select,insert
7. Which of the following statements represents the SQL statement () that lists the 3rd to 5th row of data in the T table.
A.select*fromtwhererownum<=5
Minus
select*fromtwhererownum<=2;
B.select*fromtwhererownum<=5
Intersect
select*fromtwhererownum<=2;
C.select*fromtwhererownum<=5
Union
select*fromtwhererownum<=2;
D.select*fromtwhererownum<=5
UnionAll
select*fromtwhererownum<=2;
8. The structure of the table employees is:
(
Emp_idnumber (4) Notnull
LAST_NAMEVARCHAR2 (Notnull)
FIRST_NAMEVARCHAR2 (30)
Dept_idnumber (2)
JOB_CATVARCHAR2 (30)
Salarynumber (8,2)
)
Which of the following statements can detect the maximum salary value and the minimum salary value for each dept_id and its minimum value is less than 5000 maximum value greater than 15000 ()
A.selectdept_id,min (Salary), MAX (Salary) Fromemployees
Wheremin (Salary) <5000andmax (salary) >15000;
B.selectdept_id,min (Salary), MAX (Salary) Fromemployees
Wheremin (Salary) <5000andmax (salary) >15000GROUPBYdept_id;
C.selectdept_id,min (Salary), MAX (Salary) Fromemployees
Havingmin (Salary) <5000andmax (salary) >15000;
D.selectdept_id,min (Salary), MAX (Salary) Fromemployees
Groupbydept_idhavingmin (Salary) <5000andmax (salary) >15000;
E.selectdept_id,min (Salary), MAX (Salary) Fromemployees
Groupbydept_id,salaryhavingmin (Salary) <5000andmax (salary) >15000;

9. In Oracle, which of the following functions has the same function as the | | operator ().
A, Ltrimb, CONTACTC, SUBSTRD, instr

10. Function floor (-2345.67) returns the result ().
a.2345.67b.2346c.-2346d.-2345

In a 11.Oracle database, the following () can be a valid column name. (Select an item)
A.COLUMNB.123_NUMC. num_#123d. #NUM123
12. The table test data is as follows, P_ID is the parent ID:
Createtabletestas
select1id,nullp_id, ' a ' namefromdualunion
select2id,1p_id, ' B ' namefromdualunion
select3id,1p_id, ' C ' namefromdualunion
select4id,2p_id, ' d ' namefromdualunion
select5id,2p_id, ' e ' namefromdualunion
select6id,4p_id, ' F ' namefromdualunion
select7id,4p_id, ' G ' namefromdualunion
select8id,1p_id, ' h ' namefromdual;
Which of the following SQL implementations has a query ID of 2 but does not include all downlevel data () for id=2.
a.select*fromtestconnectbypriorp_id=idstartwithid=2;
b.select*fromtestconnectbypriorid=p_idstartwithid=2;
c.select*fromtestconnectbypriorp_id=idstartwithp_id=2;
d.select*fromtestconnectbypriorid=p_idstartwithp_id=2;
13. The table employees structure is
(
Employee_idnumber (6) Notnullprimarykey,
FIRST_NAMEVARCHAR2 () Unique,
LAST_NAMEVARCHAR2 (25),
EMAILVARCHAR2 (+) Notnull,
PHONE_NUMBERVARCHAR2 (20),
Hire_datedate,
JOB_IDVARCHAR2 (10),
Salarynumber (8,2),
Commission_pctnumber (2,2),
Manager_idnumber (6),
Department_idnumber (4)
)
Which of the following statements can be used to calculate the records in a table, multiple selection ()
A.selectcount (*) fromemployees
B.selectcount (employee_id) fromemployees
C.selectcount (first_name) fromemployees
D.selectcount (EMAIL) fromemployees
E.selectcount (job_id) fromemployees
Second, simple answer
1. There is an EMP table, the field Id,name,id has duplicate data, please write out the duplicate data ID, name statement, and write out no duplicate data ID, name statement.

2. Remove the spaces (functions) on both sides of the string "Aabb".

The 3.student table field (classno,name,birthday) deletes all 3 records with a birth date less than May 12, 1981.

4.student table field (Classno,name,score)
Modify the data of the table student and add 10 points to all students in the class

Third, programming problems
The table structure is as follows:
Dept (department table): DNO (department number), Dname (department name)
EMP (Employee table): ENO (employee number), ENAME (employee name), Esex (Gender: male/female), Sal (Salary), DNO (department number), Join_date (Entry time), Elevel (level: ... ）
1. List the department as "sales department", the entry time for all male employees before December 1, 2011, the salary of more than 5000 yuan, and according to the entry time descending, wages in ascending order to display, display content: Department name, employee name, salary, entry time.

2. List the average salary of the department employees with the largest number of employees.
For:

3. Write out the SQL implementation employees who have been in employment for more than three years have an increase of 10%, level plus 1.

4. The number of employees from 2000 to 3000 in each department is shown in the following fields: Department, number of men, number of women.

Oracle Pen Questions