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
  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
  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

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.

The Student_Grades table has these columns: STUDENT_ID NUMBER(12) SEMESTER_END DATE GPA NUMBER(4,3) The registrar has asked for a report on the average grade point average (GPA), sorted from the highest grade point average to each semester, starting from the earliest date. Which statement will accomplish this? A. SELECT Student_id, semester_end, gpa from student_grades order by semester_end DESC, gpa DESC; B. SELECT Student_id, semester_end, gpa from student_grades order by semester_end, gpa ASC order by semester_end DESC, gpa DESC; C. SELECT Student_id, semester_end, gpa from student_grades order by semester_end DESC, gpa ASC; D. SELECT Student_id, semester_end, gpa from student_grades order by semester_end ASC, gpa ASC; E. SELECT Student_id, semester_end, gpa from student_grades order by semester_end DESC, gpa ASC;

  1. A

  2. B

  3. C

  4. E


Correct Option: D

AI Explanation

To answer this question, you need to understand how the ORDER BY clause works in SQL. The ORDER BY clause is used to sort the result set based on one or more columns.

Let's go through each option to understand why it is correct or incorrect:

Option A) SELECT Student_id, semester_end, gpa FROM student_grades ORDER BY semester_end DESC, gpa DESC; This option sorts the result set by semester_end in descending order and then by gpa in descending order. However, the question specifically asks for sorting by semester_end in ascending order and then by gpa in ascending order. Therefore, this option is incorrect.

Option B) SELECT Student_id, semester_end, gpa FROM student_grades ORDER BY semester_end, gpa ASC ORDER BY semester_end DESC, gpa DESC; This option has two ORDER BY clauses, which is not valid in SQL. The second ORDER BY clause should be removed. Additionally, the sorting order is incorrect as it sorts semester_end in ascending order and gpa in ascending order, while the question asks for sorting semester_end in ascending order and gpa in descending order. Therefore, this option is incorrect.

Option C) SELECT Student_id, semester_end, gpa FROM student_grades ORDER BY semester_end DESC, gpa ASC; This option sorts the result set by semester_end in descending order and then by gpa in ascending order. However, the question asks for sorting semester_end in ascending order and gpa in descending order. Therefore, this option is incorrect.

Option D) SELECT Student_id, semester_end, gpa FROM student_grades ORDER BY semester_end ASC, gpa ASC; This option sorts the result set by semester_end in ascending order and then by gpa in ascending order, which matches the requirements of the question. Therefore, this option is correct.

Option E) SELECT Student_id, semester_end, gpa FROM student_grades ORDER BY semester_end DESC, gpa ASC; This option sorts the result set by semester_end in descending order and then by gpa in ascending order. However, the question asks for sorting semester_end in ascending order and gpa in descending order. Therefore, this option is incorrect.

The correct answer is D) Option D. This option is correct because it sorts the result set by semester_end in ascending order and then by gpa in ascending order, which matches the requirements of the question.

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
  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 get information about the currently running processes in SQL Server?

  1. sp_how

  2. sp_configure

  3. sp_who2

  4. sp_get_processinfo


Correct Option: C

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
  1. SQL Server Browser

  2. SQL Server Integration Services

  3. SQL Server Full-Text Search

  4. SQL Server Agent


Correct Option: D

What are the 3 recovery models in SQL Server?

  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