Analysis of ocp007 Question Bank

Q: 1 Examine the structure of the EMPLOYEE
Technorati label: ocp 007 database oracle
S table:
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2 (25)
LAST_NAME VARCHAR2 (25)
Which three statements insert a row into the table? (Choose three .)
A. insert into employees
VALUES (NULL, 'john', 'Smith ');
B. INSERT INTO employees (first_name, last_name)
VALUES ('john', 'Smith ');
C. insert into employees
VALUES ('20140901', 'john', NULL );
D. insert into employees (first_name, last_name, employee_id)
VALUES (1000, 'john', 'Smith ');
E. insert into employees (employee_id)
VALUES (1, 1000 );
F. insert into employees (employee_id, first_name, last_name)
VALUES (1000, 'john ','');
Resolution:
Correct answer: d, e, f
A. Because employee_id is the primary key, it cannot be blank and unique, so it is wrong;
B. The primary key is not empty and unique, so it is also wrong;
C. when the order of inserted fields is not specified after the table name, the inserted fields are inserted according to the default field order in the table. The default field sequence is set when the table is created, so it is wrong;
Summary: Insert a record to the table. The fields with the primary key must be non-empty and unique.
Throwing bricks: view the default order of fields in the table and modify the default order:
Step 1: query the table id
Select object_id from all_objects where owner = 'cutm' and object_name = 'rm _ PARTY_RELATION ';
Step 2: Identify the order of all fields in the table by id
Select obj #, col #, name from sys. col $ where obj # = 76109 order by col #;
Step 3: update the field order
Update sys. col $ set col # = 8 where obj # = 76109 and name = 'child _ IS_MAIN_RELATION ';
Commit;
Q: 2 Evaluate the SQL statement:
Select round (45.953,-1), TRUNC (45.936, 2)
FROM dual;
Which values are displayed?
A. 46 and 45
B. 46 and 45.93
C.50 and 45.93
D. 50 and 45.9
E. 45 and 45.93
F. 45.95 and 45.93
Resolution:
Correct answer: c
Round Function rounding
Trunc function screenshot
Select trunc (163.12321,-2) from dual; the result is 100.
Select round (163.12321,-2) from dual; the result is 200.
Trunc/round (data value, retain decimal places) The second parameter can be a negative number, indicating that the part after the specified number of digits on the left of the decimal point is truncated.
Brick throwing:
When two functions act on a date, the syntax trunc/round (date [, fmt])
Where: date is a date value.
Fmt date format, which is truncated by the specified Element format. Ignore him by the latest date.
Fmt Format:
Yyyy/yy/year
Mm/month
D/day week
Dd Day
Hh hour
Mi score
E. g:
Select trunc (to_date ('20170101', 'yyyy-mm-dd'), 'mm') from dual; Result: 20110820
Select round (to_date ('20170101', 'yyyy-mm-dd'), 'mm') from dual; Result: 20110820
 
Q: 3 Which are DML statements? (Choose all that apply .)
A. COMMIT
B. MERGE
C. UPDATE
D. DELETE
E. CREATE
F. DROP...
Resolution:
Correct answer: B, C, D
Five types of structured SQL query languages:
1. Data Query Language DQL: select statement. Some books classify select as DML language;
2. Data Operation Language DML:
INSERT-insert data into a table
UPDATE-updates existing data within a table
DELETE-deletes all records from a table, the space for the records remain
MERGE-UPSERT operation (insert or update)
CALL-call a PL/SQL or Java subprogram
Explain plan-explain access path to data
Lock table-control concurrency
3. Transaction Control Language TCL: mainly including commit, rollback, savepoint, set
4. Data Definition Language DDL: used to describe metadata and modify data dictionaries. DDL statements are DML statements for data dictionaries. They mainly include create, drop, and truncate.
5. Data Control Language DCL: mainly including grant and revoke
 
Q: 4 Evaluate the set of SQL statements:
Create table dept
(Deptno NUMBER (2 ),
Dname VARCHAR2 (14 ),
Loc VARCHAR2 (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 occupied 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.
Resolution:
Correct answer:
Create table is a DDL language. Before executing DDL, Oracle will issue a COMMIT statement, execute the DDL operation, and then issue a COMMIT operation.
 
Q: 5 Evaluate this SQL statement:
SELECT ename, sal, 12 * sal + 100
FROM emp;
The SAL column stores the monthly salary of the employee. which change must be made to the above syntax to calculate the annual compensation as "monthly salary plus a monthly bonus of $100, multiplied by 12 "?
A. No change is required to achieve the desired results.
B. SELECT ename, sal, 12 * (sal + 100) FROM emp;
C. SELECT ename, sal, (12 * sal) + 100 FROM emp;
D. SELECT ename, sal + 100, * 12 FROM emp;
Resolution:
Correct answer: B
Pure English math problems, it has nothing to do with oralce.
Author "growth footprint"