Ilp Oracle Quiz Prelims 1
-
Which table name is valid?
-
Invoices-1996
-
Catch_#22
-
1996_invoices
-
.#_667
-
ILP Oracle Quiz Prelims 1 tests knowledge on SQL syntax, table creation, role management, and datatype selections in Oracle databases. It assesses practical skills crucial for database management and SQL programming, enhancing both academic learning and professional competency.
Quiz Preview
- 2.
You attempt to create the salary table with this command: 1. CREATE TABLE salary 2. (employee_id NUMBER(9) 3. CONSTRAINT salary_pk PRIMARY KEY, 4. 1995_salary NUMBER(8,2), 5. manager_nameVARCHAR2(25) 6. CONSTRAINT mgr_name_nn NOT NULL, 7. $salary_96NUMBER(8,2)); Which two lines of this statement will return errors?
-
4,5
-
1,7
-
4,7
-
3,7
Correct Answer
A. 4,7Explanation
Lines 4 and 7 will return errors. Line 4 will return an error because the column name "1995_salary" starts with a number, which is not allowed. Line 7 will return an error because the column name "$salary_96" contains special characters, specifically the dollar sign, which is not allowed.Rate this question:
-
- 3.
Which datatype should you use for interest rates with varying and unpredictable decimal places, such as 1.234, 3.4, 5, and 1.23?
-
NUMBER(p,s)
-
LONG
-
NUMBER
-
Float
Correct Answer
A. NUMBERExplanation
The correct answer is NUMBER because it is a datatype in most programming languages that can handle decimal numbers with varying and unpredictable decimal places. The NUMBER datatype allows for precision and scale to be specified, which means it can accommodate any number of decimal places. This makes it suitable for storing interest rates with varying decimal places such as 1.234, 3.4, 5, and 1.23.Rate this question:
-
- 4.
Evaluate this SQL script: CREATE ROLE manager; " CREATE ROLE clerk; CREATE ROLE inventory; CREATE USER scott IDENTIFIED BY tiger; GRANT inventory TO clerk; GRANT clerk TO manager; GRANT inventory TO scott / How many roles will user SCOTT have access to?
-
1
-
2
-
0
-
3
Correct Answer
A. 1Explanation
The user SCOTT will have access to 1 role. This can be determined by looking at the GRANT statements in the script. SCOTT is granted the role inventory, but no other roles are granted to SCOTT. Therefore, SCOTT will only have access to the inventory role.Rate this question:
-
- 5.
In airline reservation system, the entities are date,flight number,place of departure,destination , type of plane and seats available.The primary key is
-
Flight number
-
Flight number + place of departure
-
Flight number + date
-
Flight number + destination
Correct Answer
A. Flight number + dateExplanation
The primary key in an airline reservation system uniquely identifies each record in the system. In this case, the combination of flight number and date would be the most appropriate choice for the primary key. This is because each flight can have multiple occurrences on different dates, and each occurrence would have a unique combination of flight number and date. Using flight number alone may not be sufficient as it would not differentiate between different occurrences of the same flight. Therefore, flight number + date ensures that each record in the system is uniquely identified.Rate this question:
-
- 6.
How many copies of procedure needs to be loaded for execution by multiple users.
-
Only one copy
-
Equal to number of users
-
Equal to number of times the procedure is called
-
None of the above
Correct Answer
A. Only one copyExplanation
When multiple users execute a procedure, only one copy of the procedure needs to be loaded. This is because the procedure code remains the same regardless of the number of users. Loading multiple copies would be redundant and inefficient. Therefore, to optimize resource utilization, only one copy of the procedure is loaded for execution by multiple users.Rate this question:
-
- 7.
Which is not a valid type of trigger
-
Database trigger
-
Application trigger
-
Transaction trigger
-
System trigger
Correct Answer
A. Application triggerExplanation
An application trigger is not a valid type of trigger. Database triggers are used to automatically perform actions in response to specific events or changes in a database. Transaction triggers are used to initiate actions when a specific transaction occurs. System triggers are used to perform actions when certain system events occur. However, an application trigger is not a recognized type of trigger in the context of database management systems.Rate this question:
-
- 8.
To find information about trigger status, which of the following views are appropriate?
-
USER_TRIGGERS
-
USER_OBJECTS
-
USER_TRIGGER_COLS
-
ALL_TRIGGERS
Correct Answer
A. USER_TRIGGERSExplanation
The appropriate view to find information about trigger status is USER_TRIGGERS. This view contains information about all triggers owned by the current user, including their names, types, status, and the tables they are associated with. USER_OBJECTS provides information about all objects owned by the current user, but it does not specifically focus on triggers. USER_TRIGGER_COLS provides information about the columns used in triggers, but it does not provide status information. ALL_TRIGGERS provides information about all triggers accessible to the current user, not just those owned by the user.Rate this question:
-
- 9.
In which of the following trigger, we can use pseudo columns 'old' and 'new'?
-
Statement level trigger
-
Row level trigger
-
Any trigger
-
Schema level trigger
Correct Answer
A. Row level triggerExplanation
A row level trigger is the correct answer because it allows us to access the pseudo columns 'old' and 'new'. These pseudo columns are used to reference the old and new values of the row being affected by the trigger. This is useful for performing actions based on the old and new values of a row, such as updating related records or logging changes.Rate this question:
-
- 10.
If we do not mention as row level then the trigger by default is?
-
A statement level
-
A row level
-
A database level
-
None of the above
Correct Answer
A. A statement levelExplanation
When a trigger is not specified as row level, it means that it is designed to execute once for each triggering statement, regardless of the number of rows affected. In other words, the trigger will only be triggered once for a batch of rows affected by a single statement. Therefore, the correct answer is A statement level.Rate this question:
-
- 11.
When do we get the exception INVALID_CURSOR?
-
CURSOR is already open
-
CURSOR name is different in DECLARE as with OPEN
-
CURSOR name is duplicated
-
Referring the already closed CURSOR
Correct Answer
A. CURSOR name is different in DECLARE as with OPENExplanation
The exception INVALID_CURSOR is thrown when the name of the cursor used in the DECLARE statement is different from the name used in the OPEN statement. This means that the cursor being referenced in the OPEN statement does not exist or is not declared properly, causing the exception to be raised.Rate this question:
-
- 12.
Which of the following is a valid When clause in a trigger?
-
When (:new.job 'President')
-
When (.new.job 'President')
-
When (new.job 'President')
-
None of the above
Correct Answer
A. When (new.job 'President')Explanation
The correct answer is "When (new.job 'President')". In a trigger, the When clause is used to specify the condition under which the trigger should be executed. In this case, the When clause is checking if the value of the "job" attribute in the newly inserted or updated row is equal to "President". This means that the trigger will only be triggered when a row with the job "President" is inserted or updated. The other options are incorrect because they have syntax errors or incorrect usage of the ":new" keyword.Rate this question:
-
- 13.
Which view should a user query to display the columns associated with the constraints on a table owned bythe user?
-
USER_CONSTRAINTS
-
ALL_CONSTRAINTS
-
USER_CONS_COLUMNS
-
USER_COLUMNS
Correct Answer
A. USER_CONS_COLUMNSExplanation
The USER_CONS_COLUMNS view should be queried to display the columns associated with the constraints on a table owned by the user. This view provides information about the columns that are part of the constraints defined on tables owned by the user. It includes details such as the table name, constraint name, column name, and position of the column within the constraint. By querying this view, the user can easily retrieve the necessary information about the columns associated with the constraints on their table.Rate this question:
-
- 14.
Which statement about implicit cursors is true?
-
Programmers need to close all the implicit cursors before the end of the PL/SQL program.
-
Programmers can declare implicit cursors by using the CURSOR type in the declaration section.
-
Implicit cursors are declared implicitly only for DML statements.
-
Implicit cursors are declared implicitly for all the DML and SELECT statements.
Correct Answer
A. Implicit cursors are declared implicitly for all the DML and SELECT statements.Explanation
Implicit cursors are automatically created by the Oracle database for all DML (Data Manipulation Language) and SELECT statements. Programmers do not need to explicitly declare them using the CURSOR type in the declaration section. Additionally, there is no requirement for programmers to close implicit cursors before the end of the PL/SQL program.Rate this question:
-
- 15.
Which of the following is false?
-
Generally we use commit/rollback inside the cursor for loop
-
Generally we use commit/rollback outside the cursor for loop
-
Generally we do not use commit/rollback with the cursor for loop
-
None of the above
Correct Answer
A. Generally we use commit/rollback outside the cursor for loopExplanation
In general, we use commit/rollback inside the cursor for loop to ensure that each iteration of the loop is treated as a separate transaction. This allows us to control the commit or rollback behavior for each individual iteration. On the other hand, using commit/rollback outside the cursor for loop would treat the entire loop as a single transaction, which may not be desired in certain scenarios. Therefore, the statement "Generally we use commit/rollback outside the cursor for loop" is false.Rate this question:
-
- 16.
Which of the following clauses is valid with cursor for update?
-
Where current of
-
Where cursor of
-
Where update of
-
None of the above
Correct Answer
A. Where current ofExplanation
The "Where current of" clause is valid with cursor for update. This clause is used in SQL to specify that the current row being pointed to by a cursor should be updated. It allows for updating the row in a cursor without needing to specify any additional conditions or criteria.Rate this question:
-
Quiz Review Timeline (Updated): Mar 22, 2023 +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
-
Current Version
-
Mar 22, 2023Quiz Edited by
ProProfs Editorial Team -
Sep 12, 2012Quiz Created by
Srikanth_kanthet
Back to top