Nest 0 Cart
Nest Account
Popular Exams
All Categories...
Nest
Nest 0
Contact
Log In / Sign Up
Copyright 2024 © store. All rights reserved.
Home Oracle 1z0-051

Oracle 1z0-051 Exam Practice Questions (P. 4)

  • Full Access (290 questions)
  • Six months of Premium Access
  • Access to one million comments
  • Seamless ChatGPT Integration
  • Ability to download PDF files
  • Anki Flashcard files for revision
  • No Captcha & No AdSense
  • Advanced Exam Configuration
Get Contributor Access
Question #16
Examine the structure of the STUDENTS table:

You need to create a report of the 10 students who achieved the highest ranking in the course INT SQL and who completed the course in the year 1999.
Which SQL statement accomplishes this task?
  1. A
    SELECT student_ id, marks, ROWNUM "Rank"FROM studentsWHERE ROWNUM <= 10AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99AND course_id = 'INT_SQL'ORDER BY marks DESC;
  2. B
    SELECT student_id, marks, ROWID "Rank"FROM studentsWHERE ROWID <= 10AND finish_date BETWEEN '01-JAN-99' AND '31-DEC-99'AND course_id = 'INT_SQL'ORDER BY marks;
  3. C
    SELECT student_id, marks, ROWNUM "Rank"FROM (SELECT student_id, marksFROM studentsWHERE ROWNUM <= 10AND finish_date BETWEEN '01- JAN-99' AND '31-DEC-99'AND course_id = 'INT_SQL'ORDER BY marks DESC);
  4. D
    SELECT student_id, marks, ROWNUM "Rank"FROM (SELECT student_id, marksFROM studentsWHERE (finish_date BETWEEN ’01-JAN-99 AND ’31-DEC- 99AND course_id = INT_SQLORDER BY marks DESC)WHERE ROWNUM <= 10 ;
  5. E
    SELECTstudent id, marks, ROWNUM "Rank"FROM(SELECT student_id, marksFROM studentsORDER BY marks)WHEREROWNUM <= 10ANDfinish date BETWEEN 01-JAN-99 AND 31-DEC-99ANDcourse_id = INT_SQL;

Correct Answer:
D

Question #17
Evaluate the following SQL statements:
Exhibit:

Exhibit:

The above command fails when executed. What could be the reason?
  1. A
    The BETWEEN clause cannot be used for the CHECK constraint
  2. B
    SYSDATE cannot be used with the CHECK constraint
  3. C
    ORD_NO and ITEM_NO cannot be used as a composite primary key because ORD_NO is also the FOREIGN KEY
  4. D
    The CHECK constraint cannot be placed on columns having the DATE data type

Correct Answer:
B
CHECK Constraint -
The CHECK constraint defines a condition that each row must satisfy. The condition can use the same constructs as the query conditions, with the following exceptions:
References to the CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns
Calls to SYSDATE, UID, USER, and USERENV functions
Queries that refer to other values in other rows
A single column can have multiple CHECK constraints that refer to the column in its definition.
There is no limit to the number of CHECK constraints that you can define on a column.
CHECK constraints can be defined at the column level or table level.

CREATE TABLE employees -
(...
salary NUMBER(8,2) CONSTRAINT emp_salary_min
CHECK (salary > 0),

Question #18
Evaluate the following SQL statements:
DELETE FROM sales;
There are no other uncommitted transactions on the SALES table.
Which statement is true about the DELETE statement?
  1. A
    It removes all the rows as well as the structure of the table
  2. B
    It removes all the rows in the table and deleted rows cannot be rolled back
  3. C
    It removes all the rows in the table and deleted rows can be rolled back
  4. D
    It would not remove the rows if the table has a primary key

Correct Answer:
C

Question #19
Examine the structure of the EMPLOYEES table:

You want to create a SQL script file that contains an INSERT statement. When the script is run, the INSERT statement should insert a row with the specified values into the EMPLOYEES table. The INSERT statement should pass values to the table columns as specified below:

Which INSERT statement meets the above requirements?
  1. A
    INSERT INTO employees VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
  2. B
    INSERT INTO employees VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did IN (20,50));
  3. C
    INSERT INTO (SELECT * FROM employees WHERE department_id IN (20,50)) VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
  4. D
    INSERT INTO (SELECT * FROM employees WHERE department_id IN (20,50) WITH CHECK OPTION)VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);
  5. E
    INSERT INTO (SELECT * FROM employees WHERE (department_id = 20 AND department_id = 50) WITH CHECK OPTION ) VALUES (emp_id_seq.NEXTVAL, '&ename', '&jobid', 2000, NULL, &did);

Correct Answer:
D

Question #20
Which two statements are true regarding constraints? (Choose two.)
  1. A
    A constraint can be disabled even if the constraint column contains data
  2. B
    A constraint is enforced only for the INSERT operation on a table
  3. C
    A foreign key cannot contain NULL values
  4. D
    All constraints can be defined at the column level as well as the table level
  5. E
    A column with the UNIQUE constraint can contain NULL values

Correct Answer:
AE
Including Constraints -
Constraints enforce rules at the table level.
Constraints prevent the deletion of a table if there are dependencies.
The following constraint types are valid:
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK

Previous Questions Next Questions

All Pages