Home › Quizzes › SQL Server Administration & Database Concepts Quiz

SQL Server Administration & Database Concepts Quiz

Test your knowledge of SQL Server administration (configuration, recovery models, DMVs, services) and general database concepts including table partitioning, query optimization, views, and SQL DML operations.

20 Questions Published
Play Quiz Practise this topic out loud

Questions

Question 1 Multiple Choice (Single Answer)

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’;
Question 2 Multiple Choice (Single Answer)

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;
Question 3 Multiple Choice (Single Answer)

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');
Question 4 Multiple Choice (Single Answer)

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
Question 5 Multiple Choice (Single Answer)

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
Question 6 Multiple Choice (Single Answer)

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
Question 7 Multiple Choice (Single Answer)

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.
Question 8 Multiple Choice (Multiple Answers)

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);
Question 9 Multiple Choice (Multiple Answers)

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
Question 10 Multiple Choice (Single Answer)

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
Question 11 Multiple Choice (Multiple Answers)

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
Question 12 Multiple Choice (Single Answer)

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
Question 13 Multiple Choice (Single Answer)

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
Question 14 Multiple Choice (Single Answer)

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
Question 15 Multiple Choice (Single Answer)

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
Question 16 Multiple Choice (Multiple Answers)

What are the 3 recovery models in SQL Server?

  1. Simple
  2. Full
  3. T-log
  4. Bulk-Logged
Question 17 Multiple Choice (Single Answer)

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
Question 18 Multiple Choice (Single Answer)

On what default port does SQL Server run?

  1. 1441
  2. 25
  3. 8000
  4. 1433
Question 19 Multiple Choice (Multiple Answers)

What are the different types of Replication?

  1. Transactional
  2. Snapshot
  3. Mirroring
  4. Merge
Question 20 Multiple Choice (Single Answer)

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
← Back to Quizzes