0

databases Online Quiz - 91

Description: databases Online Quiz - 91
Number of Questions: 20
Created by:
Tags: databases
Attempted 0/20 Correct 0 Score 0

KCRT_REQUEST_DETAILS Request_type_id Request_id Batch_No Visible_parameter41 Visible_parameter31 --------------- ----------- --------- ------------------- ------------------- 1 31300 1 Online (null) 1 31300 2 Batch (null) 1 31300 3 Manually (null) 2 31301 1 2010-03-17 11:55:00 99 2 31301 2 2009-11-09 14:00:00 55 2 31301 3 2010-05-27 11:55:00 789 3 31302 1 TCS ACCM1 3 31302 2 INFOSYS BAPP1 3 31302 3 CTS IT1 KCRT_REQUEST_TYPES Request_Type_Id Request_Type_Name --------------- ----------------- 1 AXA_RT_Application_Demand 2 AXA_RT_Application_Definition 3 AXA_RT_Support_Release The statement that converts the Visible_parameter41 in the format ‘17/03/10’ for Request_id = 31301 for batch No ‘1’?

  1. SELECT to_char(visible_parameter41,’yyyy-mm-dd hh24:mi:ss’) from KCRT_REQUEST_DETAILS where request_id = 31301 and batch_number = ‘1’;

  2. SELECT to_date(visible_parameter41) from KCRT_REQUEST_DETAILS where request_id = 31301 and batch_number = ‘1’;

  3. SELECT to_date(visible_parameter41, ‘mm-dd-yyyy hh24:mi:ss’) from KCRT_REQUEST_DETAILS where request_id = 31301 and batch_number = ‘1’;

  4. SELECT to_date(visible_parameter41,’yyyy-mm-dd hh24:mi:ss’) from KCRT_REQUEST_DETAILS where request_id = 31301 and batch_number = ‘1’;


Correct Option: D

KCRT_REQUEST_DETAILS Request_type_id Request_id Batch_No Visible_parameter41 Visible_parameter31 --------------- ----------- --------- ------------------- ------------------- 1 31300 1 Online (null) 1 31300 2 Batch (null) 1 31300 3 Manually (null) 2 31301 1 2010-03-17 11:55:00 99 2 31301 2 2009-11-09 14:00:00 55 2 31301 3 2010-05-27 11:55:00 789 3 31302 1 TCS ACCM1 3 31302 2 INFOSYS BAPP1 3 31302 3 CTS IT1 KCRT_REQUEST_TYPES Request_Type_Id Request_Type_Name --------------- ----------------- 1 AXA_RT_Application_Demand 2 AXA_RT_Application_Definition 3 AXA_RT_Support_Release Which is the best option to create a VIEW for visible_parametr41 for batch 3 along with the request_id?

  1. CREATE OR REPLACE VIEW PARAM41_VIEWAS Select request_id, visible_parameter41 from KCRT_REQUEST_DETAILS where batch_number = 3;

  2. CREATE OR REPLACE VIEW PARAM1 VIEW AS Select request_id, visible_parameter41 from KCRT_REQUEST_DETAILS;

  3. CREATE OR REPLACE VIEW AS Select request_id, visible_parameter41 from KCRT_REQUEST_DETAILS where batch_number = 3;

  4. CREATE VIEW AS Select request_id, visible_parameter41 from KCRT_REQUEST_DETAILS where batch_number = 3;


Correct Option: A

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: EMPLOYEES ---------- EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE NEW_EMPLOYEES -------------- EMPLOYEE_ID NUMBER Primary Key NAME VARCHAR2(25) Which DELETE Statement is valid?

  1. Delete from employees where employee_id = (select employee_id from employees);

  2. Delete * from employees where employee_id = (select employee_id from new_employees);

  3. Delete from employees where employee_id in (select employee_id from new_employees where name = 'Carrey');

  4. Delete * from Employees where employee_id in (select employee_id from new_employees where last_name = 'Carrey');


Correct Option: C

AI Explanation

To answer this question, let's examine each DELETE statement and determine if it is valid or not:

A. Delete from employees where employee_id = (select employee_id from employees); This statement is not valid because it attempts to delete rows from the same table that it is querying (EMPLOYEES).

B. Delete * from employees where employee_id = (select employee_id from new_employees); This statement is not valid because the "DELETE" keyword should not be followed by an asterisk (*). It should be "DELETE FROM" instead.

C. Delete from employees where employee_id in (select employee_id from new_employees where name = 'Carrey'); This statement is valid. It deletes rows from the EMPLOYEES table where the employee_id is present in the result of the subquery.

D. Delete * from Employees where employee_id in (select employee_id from new_employees where last_name = 'Carrey'); This statement is not valid because, similar to option B, the "DELETE" keyword should not be followed by an asterisk (*). It should be "DELETE FROM" instead.

The correct answer is C. This statement is valid because it uses the correct syntax and deletes rows from the EMPLOYEES table based on a condition specified in the subquery.

Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) Which three statements insert a row in to 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 ('1000', 'John', NULL); D - INSERT INTO employees(first_name, last_name, employee_id) VALUES ('1000', 'John', 'Smith'); E - INSERT INTO employees(employee_id) VALUES (1000); F - INSERT INTO employees(employee_id,first_name,last_name) VALUES (1000,'John',' ');

  1. A, B, D

  2. C, D, E

  3. C, E, F

  4. B, D, F


Correct Option: C

AI Explanation

To answer this question, let's go through each option to understand why it is correct or incorrect:

Option A) INSERT INTO employees VALUES (NULL, 'John', 'Smith'); This option is incorrect because it tries to insert a NULL value for the primary key column EMPLOYEE_ID, which is not allowed.

Option B) INSERT INTO employees(first_name, last_name) VALUES ('John', 'Smith'); This option is incorrect because it does not provide a value for the primary key column EMPLOYEE_ID, which is required.

Option C) INSERT INTO employees VALUES ('1000', 'John', NULL); This option is correct because it provides values for all three columns (EMPLOYEE_ID, FIRST_NAME, LAST_NAME) and does not violate any constraints.

Option D) INSERT INTO employees(first_name, last_name, employee_id) VALUES ('1000', 'John', 'Smith'); This option is incorrect because it provides a value 'Smith' for the column EMPLOYEE_ID, which is defined as a NUMBER data type.

Option E) INSERT INTO employees(employee_id) VALUES (1000); This option is correct because it provides a value for the primary key column EMPLOYEE_ID, which is required. It does not provide values for the other columns, but that is allowed as long as they have default values or accept NULL values.

Option F) INSERT INTO employees(employee_id,first_name,last_name) VALUES (1000,'John',' '); This option is correct because it provides values for all three columns (EMPLOYEE_ID, FIRST_NAME, LAST_NAME) and does not violate any constraints.

The correct answer is C. The three statements that insert a row into the table are C, E, and F. These statements provide valid values for all columns and do not violate any constraints.

Which two statements about view are true? (Choose two) A. A view can be created as read only B. A view can be created as a join on two or more tables C. A view can not have an ORDER BY clause in the SELECT statement D. A view can not be created with a GROUP BY Clause in the SELECT statement E. A view must have aliases defined for the column names in the SELECT statement

  1. C, D

  2. A, E

  3. A, B

  4. B, D


Correct Option: C

AI Explanation

To answer this question, let's go through each option to understand why it is correct or incorrect:

Option A) A view can be created as read-only - This option is correct. Views can be created as read-only, meaning that data can be queried from the view but not modified.

Option B) A view can be created as a join on two or more tables - This option is correct. Views can be created as a result of joining two or more tables, providing a consolidated and simplified view of the data.

Option C) A view cannot have an ORDER BY clause in the SELECT statement - This option is incorrect. Views can have an ORDER BY clause in the SELECT statement to specify the sorting of the result set.

Option D) A view cannot be created with a GROUP BY clause in the SELECT statement - This option is incorrect. Views can have a GROUP BY clause in the SELECT statement to group the data based on specified criteria.

Option E) A view must have aliases defined for the column names in the SELECT statement - This option is incorrect. While it is recommended to provide aliases for column names in a view, it is not mandatory. Views can be created without aliases for column names.

The correct answer is C) A, B. This option is correct because both statements are true. Views can be created as read-only (A), and views can be created as a join on two or more tables (B).

The "Leading" hint causes the optimizer to

  1. Use the specified table as the "Last" table in the join

  2. Use the specified table as the "Log" table in the join

  3. Use the specified table as the "Driving" table in the join

  4. Use the query as the "First" query in the set.


Correct Option: C

Which are the two ways of retrieving the execution plan for an SQL statement?

  1. select * from utlxplan;

  2. select * from plan_table;

  3. select plan from plan_table;

  4. select * from table(dbms_xplan.display);


Correct Option: B,D

Which of these are types of table partitioning?

  1. Index Partitioning

  2. Range Partitioning

  3. Rowid Partitioning

  4. List Partitioning

  5. Hash Partitioning

  6. Composite Range Hash Partitioning


Correct Option: B,D,E,F

The "ENABLE ROW MOVEMENT"

  1. allows Logical Rowid's to remain independent of physical rowid's

  2. allows rows to move across partitions

  3. allows rows to be physically located in a different tablespace, while remaining tagged to a partition

  4. allows rows to be copied to non-partitioned tables


Correct Option: B

Which are the 4 ways of partitioning an existing table?

  1. Online Redifinition method

  2. Hybrid Method

  3. Export/Import Method

  4. Insert with a Subquery method

  5. Dump Exchange Method

  6. Partition Exchange method


Correct Option: A,C,D,F

What is the command to view/change SQL Server level paramaters/properties?

  1. sp_helpserver

  2. sp_configure

  3. sp_helpdb

  4. sp_get_serverinfo


Correct Option: B

Which DMV is used to identify fragmentation of Indexes

  1. sys.dm_os_schedulers

  2. sys.dm_exec_requests

  3. sys.dm_db_index_physical_stats

  4. sys.dm_db_index_usage_stats


Correct Option: C

Which of the following SQL service is dependent of SQL Server service?

  1. SQL Server Browser

  2. SQL Server Integration Services

  3. SQL Server Full-Text Search

  4. SQL Server Agent


Correct Option: D
  1. Simple

  2. Full

  3. T-log

  4. Bulk-Logged


Correct Option: A,B,D

What is the command to get information about the currently running query for a particular process?

  1. sp_who2

  2. dbcc checkdb

  3. dbcc inputbuffer(spid)

  4. dbcc processinfo


Correct Option: C

On what default port does SQL Server run?

  1. 1441

  2. 25

  3. 8000

  4. 1433


Correct Option: D

What are the different types of Replication?

  1. Transactional

  2. Snapshot

  3. Mirroring

  4. Merge


Correct Option: A,B,D

What component of SQL Server is used to send emails in SQL Server 2005?

  1. SQL Mail

  2. Server Mail Utility

  3. Database Mail

  4. Mail Forms


Correct Option: C
- Hide questions