PRACTICE C

Which of the following values would not fit in a number column? Select all that apply.

'ABC' $123

Which option properly demonstrates the output from the following SQL statement? Select to_char(sysdate, 'mm/dd/yyyy hh24:mi:ss') from dual;

01 /01/2017 21:00:00

What is the result of the following SQL statement? select trunc(123.3354,2) from dual;

123.33

What will be the result of the following query? select count (test _ date) from test_values;

2

How many rows would the following query return? Select * from number_table order by number_id Offset 3 rows fetch next 5 rows only;

5

Which of the following statements best defines the purpose of a subquery? Select all that apply.

A subquery is used to define a set of rows that should be inserted into a table. A subquery is used to define conditions in a where clause to be applied to the results returned by a select statement.

Which of the following is true about a truncate statement? (Choose all that are correct)

A truncate statement includes an implicit commit. A truncate statement is considered a DDL statement.

Which of the following does not describe a situation in which you might use a subquery?

All of these are correct answers to the question.

Which of the following is part of Oracle's support for a relational RDBMS?

All of these.

What type of join best describes the following query? select location_description, zip_code, zip_region From location, zip_codes;

Cartesian

Which of the following are main benefits of Oracle Database 12c? (Choose all that apply)

Compression and storage tiering make for more efficient use of available storage. Multiple databases can be consolidated into one instance.

Which of the following create table statements will create the table listed in this exhibit in the tablespace called user_data?

Create table employees ( emp_num number primary key, Emp_last_name varchar2(30), Emp_first_name varchar2(30), Stores_store_num number, Dept_dept_num number, Emp_supervisor number) Tablespace user_data;

Which of the following is a valid create table command?

Create table my_table(table_number number);

Which of the following is not a group function?

Date

Which of the following options describe methods available to create constraints when creating a table with the create table command? Select all that apply.

Inline referential constraint Out of line referential constraint

Which of the following will insert rows in the employee table into the employee_history table with a term_date less than or equal to today's date?

Insert into employee_history select * from employee where term_date <=sysdate;

select item_number, quantity_soldfrom salesorder by sale_number, store_number, quantity_sold; Which of the following statements are true?

Item number 102 and quantity sold of 1 will appear first in the report.

Evaluate the following SELECT statement: select store_number, sales_date, item_number, sum(quantity_sold) qty_sold from sales where trunc(sales_date)=to_date('01/01/2017', 'mm/dd/yyyy') group by store_number, sales_date, item_number order by 1,2,3;

No rows will be returned by the query.

Which SQL statement option will produce the output seen below?

None of the SQL statements present will produce the desired output.

Which type of constraint cannot be created as an inline constraint when creating a table?

None of these

Which of the following parts of a SQL statement will cause the rowset of a query to be properly ordered?

None of these will properly order the resulting row sets from a query.

Which if the following is not a type of subquery?

Ordered subquery

What kind of join would be required to produce a list of all employees in the employees table, even if they were not assigned a department in the depts Table.

Outer join

Which of the following options describes the purpose of a set operation? Select all that apply.

Queries that contain a set operations are called compound queries. Combines the results of two or more queries into one result set. All set operators have equal precedence.

What is the result of the following query? Select to_char(number_id, '099') results from number_table where number_id < 5000;

RESU ---- 001 100 200 #### ####

Which of the following is true about schema objects? (Choose all that apply)

Schema objects are owned by a database user. Schema objects may, or may not, store data.

Which SQL statement will combine all employees in employee and employee_history who had supervisor number 1 ?

Select * from employee Where emp_supervisor=1; Union Select * from employee_history Where emp_supervisor=1;

Which of the following SQL statements would result in a Cartesian join?

Select a.emp_last_name, a.emp_first_name, b.dept_desc From employees a, depts B;

Which of the following SQL statements is an example of a nonequijoin?

Select a.emp_last_name, a.emp_first_name, b.dept_desc From employees a, depts b Where a.dept_dept_num=b.dept_num And a.stores_store_num not in ( select store_num from stores where store_description != 'WAREHOUSE');

Which of the following SQL statements will report each employee's first and last name, the store description for the store they are assigned to and also the related department description?

Select a.emp_num, a.emp_last_name, a.emp_first_name, b.store_description, c.dept_desc From employees a, stores b, depts C Where a.stores_store_num=b.store_num And a.dept_dept_num=c.dept_num;

Which SQL statement would join the locations table and the inspections table in such a way that locations with and without inspections would both appear in the output?

Select a.location_id, a.location_description, b.inspection_date From locations a, inspections b Where a.location_id=b.location_id (+);

Which of the following SQL statements contains a valid grouping function?

Select a.store_num, count(*) from stores a, employees b Where a.store_num=b.stores_store_num Group by a.store_num;

When using SQL*Plus, by default, which SQL statement will prompt you for a value that will impact the results of the SQL statement?

Select count(*) total_sales from sales_data where trunc(sales_date)=trunc(sysdate-&1);

Which of the following SQL statements will return the teacher_first_name and teacher_last_name columns in lower case?

Select lower(teacher_first_name), lower (teacher_last_name) from teachers;

Which of the following queries represents a multiple row subquery?

Select stores_store_num, emp_last_name, emp_first_name From employees Where dept_dept_num In ( select dept_num from depts where dept_desc in ('MGT','Asst. Mgr') );

Which of the following is a valid function type in Oracle? (Choose all that apply)

Singe-row functions Aggregate or group functions Analytic functions

Evaluate the following SELECT statement: select store_number, sales_date, item_number, sum(quantity_sold) qty_sold from sales group by store_number, sales_date, item_number order by 1,2,3; Which of the following statements is true?

Store number 1, with a sales date of 02-JAN-17 and item number 101 will appear in the first row.

What will be the result of the following SQL statement? Select b.teacher_last_name, b.teacher_first_name From class a, teachers b Where a.teacher_number=b.teacher_number And a.student_count=(select max(student_count) from class);

TEACHER_LAST_NAME TEACHER_FIRST_NAME ----------------------------------------- Sargon Lee

Which of the following describes a function that the SQL select statement supports? (Choose all that apply)

The ability to use alias names when referencing tables and columns. The ability to ensure that rows returned by the SQL statement are unique.

Review the following ERD diagram: Which of the following is correct related the ERD above?

The diagram indicates that for each row in the stores table there must be one or more rows in the employees table.

Which of the following statements about this table are false? Select all that apply.

The factory_number column must contain a value in each row of the cars table. The Vin column must contain a unique value for each row of the cars table.

Which of the following statements are true related to the truncate SQL statement? Select all that apply.

The truncate statement will be successful regardless of the status of foreign key constraints where the table being truncated is the child in the foreign key relationship. The truncate statement does not generate undo.

Which of the following describes the relationships defined in the exhibit?

There is a self-join in the employees table.

Which of the following best describes the purposes of a relational database like Oracle? (Choose all that apply)

To retrieve data To organize data To collect data To store data

Which of the following is not an Oracle schema object type?

Transactions

Which of the following is a conditional expression that can be used in a select statement?

case expression

Which of the following statements completes a transaction? Select all that apply.

commit rollback

Which of the following are SQL group functions? (Choose all that apply)

count min max

Which of the following statements would you use to create the ADDRESS table?

create table ADDRESS ( address_number number primary key, street_number number, address_one varchar2(100) not null, address_two varchar2(100), country_number number not null, postal_code varchar2(20) not null, constraint fk_country_number foreign key (country_number) references countries (country_number), constraint fk_postal_code foreign key (postal_code) references post_codes (postal_code) );

Which of the following delete statements will remove all of the classes with teacher 1 or teacher 3?

delete from class where teacher_number in (1 ,3);

Which of the following SQL statements will fail? (Choose all that apply)

delete from stores where store_num=l; delete * from stores;

Which if the following is a valid insert statement into the sales table?

insert into sales values (1,2,3);

Which SQL insert statement will add the following data to the user_page_counts table? Table Name:USER_PAGE_COUNTS

insert into user_page_counts values (1,to_date('12/25/2017','mm/dd/yyyy'), 22);

Which of the following is not a set operation?

minus all

If you wanted to store a maximum value of 999.999 in a number datatype, how would you define that column?

number(6,3)

Which of the following parts of a SQL statement will not limit the number of rows returned by a given query?

order by

Assume you issue the following command: delete from employees; What command would reverse the results of this delete?

rollback;

Examine the data in the SALES table below: Which query will sort the results first by sale_number, then store_number and sales_date, and then return just retum the first five sorted rows?

select * from (select * from sales order by sale_number, store_number, sales_date ) Where rownum < 6;

Which SQL statement will retum the rows in the table in ascending order?

select * from number_table order by 1;

Review the structure/data contained in two tables. The first table is called people and the second table is called people_history. The structure of both tables is the same as seen here: Which of the following queries will retum all rows from both the tables?

select * from people union select * from people_history;

Review the following data from the teachers table: Which of the following queries would list all of the rows in the teachers table in order of teacher_last_name and then teacher_first_name?

select * from teachers order by 2,3;

Review the following data from the teachers table: select * from teachers;

select * from teachers;

Examine the data in the USER_AGE table below: Which option will return all the rows and columns of the table?

select * from user_age;

Which SQL statement will join the locations and inspections tables together into a single report showing all the columns of each table as seen in this example?

select a.location_description, a.location_add_date, b.inspection_date from locations a, inspections b where a.location_id=b.location_id;

In this example, the people_num record represents a unique individual. Each individual is associated with a master record (the employee) which is represented by the value in the parent_people_num column. For example, people_num record 2 is part of the family of people_num 1, since the parent_people_num column has a 1 in it. The relation_code column indicates if the relationship is one of Employee (E), Spouse (S) or Dependent (D). Given this information, which of the following SQL statements will generate a report that will show the employee record associated with person_id = 3, as seen in the following exhibit:

select a.people_num, a.parent_people_num, a.relation_code from people a, people b where a.people_num=b.parent_people_num and b.people_num=3 order by 1;

Which of the following SQL statements will create the following output:

select c.store_description, a.emp_last_name, a.emp_first_name, b.dept_desc from employees a, stores c, dept b where a.stores_store_num=c.store_num and a.dept_dept_num=b.dept_num;

Which SQL statement will list all employees last and first names?

select emp_last_name, emp_first_name from employees;

Which of the following SQL statements contains a conditional expression?

select last_name,first_name, decode(substr(phone_number, 1, 3), '515', 'iowa', '405', 'iowa', '650', 'california', '011', 'international') as area From persons;

Which of the following SQL statements will report on all teachers that have a total count of more than 100 students in all of their classes combined?

select teacher_number, sum(student_count) from class group by teacher_number having sum(student_count)>100;

Which SQL statement will provide the average page counts for each user in the user_page_counts table?

select user_id, avg(page_count) avg_page_count from user_page_counts group by user_id;

Which of the following SQL statements will produce a report for all users who had over 100 page_counts between 12/01 /2016 and 12/04/2016 as seen in this example output:

select user_id, sum(page_count) page_counts from user_page_counts where action_date>=to_date('12/01/2016','mm/dd/yyyy') and action_date<=to_date('12/04/2016','mm/dd/yyyy') group by user_id having sum(page_count) > 100;

Which function would you use if you wanted to convert a date datatype to a character datatype?

to_char

Which SQL statement would increase the number of students for class number 1 ?

update class set student_count=student_count+1 where class=1;

Which of the following update statements will result in an error?

update employees set date=sysdate;

Further, assume the nls_date_format is set to a value of 'mm/dd/yyyy hh24:mi:ss'. Which SQL statement will properly update the term_date to 1 /25/2016 in the employees table for emp_num 1? (select all that are correct).

update employees set term_date=to_date('25-JAN-2016','dd-mon-yyyy') where emp_num=1; update employees set term_date=to_date('01/25/2016','mm/dd/yyyy') where emp_num=1;