PRACTICE D

When using SQL*Plus, by default, which character is used to indicate that SQL*Plus should prompt the user for input from the keyboard?

&

Review the following update statement. How many rows will be updated?

1

What is the result of the following SQL statement? select instr('STRINGS' , 'STR') from dual;

1

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

4

Which of the following statement(s) does not describe the purpose of a subquery? (Choose all that are correct).

A subquery is used to group rows and provide group values such as averages, sums and counts. A subquery is used to join the result sets of two or more queries together into one rowset. A subquery is used to define the order of the rows returned by a select statement.

Which of the following clauses might be found in a select statement?

All of these

Review the following query: Select * From teachers Order by 4 desc, 2, 3 asc; Which of the following statements is true?

Column 2 will be sorted in ascending order.

Which of the following DDL statements will create at least one constraint on the table involved? (Choose all that apply)

Create table employment (emp_id number primary key, emp_lname varchar2(30), emp_fname varchar2(30) ) Create table employment (emp_id number unique, emp_lname varchar2(30), emp_fname varchar2(30) ) Create table employment (emp_id number, emp_lname varchar2(30) not null, emp_fname varchar2(30) not null ) ;

Which of the following is a valid create table command?

Create table notifications (notification_id number primary key, notification_type varchar2(2));

Which of the following delete statements will remove all of the classes that have teacher 2 as the teacher and where there are less than 30 students in the class?

Delete from class where teacher_number=2 and student_count<30;

Which of the following is not possible in an Oracle relational database?

Dirty reads

Which of the following cannot be contained in a create table command?

Explicit index creation

Which of the following are not schema objects?

Grants

Which statement best describes the use of a group function? (Choose all that apply)

Group functions return a single result row based on groups of rows. Group functions require the use of a function. Group functions must include a group by clause.

Which of the following is not a valid function type in Oracle?

Indexing functions

Which of the following will insert a single row into the employee table?

Insert into employee select * from employee_history where rownum < 2;

Which of the following SQL statements will successfully insert this new row?

Insert into teachers values (10,'Jeffery','Whipple', to_date('09/02/2017,'mm/dd/yyyy'));

Evaluate the following SELECT statement: select item_number, quantity_sold from sales order by store_number, sales_date, employee_number, item_number Which of the following statements is true?

Item number 43 will appear last in the report.

If the average class size is 28, which of the following statements is true about the SQL statement above?

Lee Sargon will appear on the report.

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

More efficient sharing of resources. The ability to easily expand compute resources with Oracle RAC.

Which of the following queries represents a Cartesian join between locations and inspections?

None of the queries will result in a Cartesian join.

Which of the following is correct related to the ERD above?

None of these are true.

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 store in the stores table?

Outer join

You want to ensure that data in a specific column of a table is not duplicated, and that the column cannot store NULL values. Which of the following constraints would you use?

Primary key

Which query will produce a report that shows each employee and their associated manager?

SELECT e1.last_name||','||e1.first_name employee, e2.last_name||','||e2.first_name manager FROM hr.employees e1, hr.employees e2 WHERE e1.manager_id = e2.employee_id;

Which of the following SQL statements is an example of the use of a subquery?

SELECT employee_id, first_name, last_name, (select count (role_id) from hr.roles where roles.employee_id = employees.employee_id)as number_of_roles from hr.employees where employee_id<120;

Which of the following is a tool available in Oracle Database 12c that you can use to issue SQL statements? (Choose all that apply)

SQL*PLUS Oracle SQL Developer

Select sale_number, store_number, sales_date, item_number From sales Where store_number =1 Order by 3,1,2; Which of the following values for sales_number, store_number and sales_date would appear first in the result set?

Sale_number 1 store_number 1 sales_date 01 /02/2017 item_number 115

Which of the following is a type of subquery? (Choose all that apply)

Scalar subquery Nested subquery

Which of the following query represents the use of a set operation?

Select 'Manager', last_name, frist_name from hr.employees where manager_id is null Union Select 'Employee', last_name, frist_name from hr.employees where manager_id is not null;

Which of the following SQL statements involves the use of a set operation? (Choose all that apply)

Select * from table_alpha union Select * from table_beta; Select * from table_alpha minus Select * from table_beta Order by 1;

Which of the following SQL statements will return rows from the teacher table for all teachers hired before 8/15/2017?

Select * from teachers where teacher_hire_date between to_date('08/01/2017', 'mm/dd/yyyy') and to_date('08/14/2017', 'mm/dd/yyyy') Order by teacher_number;

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 an equijoin?

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;

Which of the following queries will retum all employee first and last names, their department descriptions and stores descriptions, properly joined together?

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

Which of the following queries would return rows from both the employees and stores tables - even if a row in the employees table contains a null in the store_num column?

Select a.emp_last_name, a.emp_first_name, b.store_description From employees a, stores b Where a.stores_store_num=b.store_num (+);

Which of the following SQL statements will display the columns employees.emp_first_name and employees.emp_last_name for all employees that are assigned to store_num 1.

Select a.emp_num, a.emp_last_name, a.emp_first_name From employees a, stores b Where a.stores_store_num=b.store_num And b.store_num=1;

Which SQL statement will list all stores that do not have an employee assigned to them?

Select store_num from stores where store_num not in (select distinct stores_store_num from employees where stores_store_num is not null);

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.

select sales_date, store_number, item_number, avg(quantity_sold) avg_qty_soldfrom salesgroup by sales_date, store_number, item_numberorder by 1,2 desc,3; Which of the following statements is true?

Store number 2 will appear first in the output.

Which object would you create if you wanted to store data in the database?

Table

Select case (teacher_number) when 1 then 'School 1' When 2 then 'School 1' Else 'School 2' end as school_assignment From teachers; Which of the following statements is true?

The statement contains a conditional expression in the form of the case statement.

Which of the following is true of the truncate command?

The truncate command resets the high-water mark of the table being truncated.

Which of the following statements is true related to the truncate SQL statement?

The truncate statement is usually faster than an equivalent delete statement.

Which of the following statements is true about this table? (Choose all that apply)

The value in the car_number column for a given row must be unique compared to all of the other rows in the table. The Vin column must contain a unique value for each row of the cars table.

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; What is the purpose of the having clause within the query above?

To reduce the result set to include only rows that have a sum(page_count) greater than 100.

Which SQL operator combines the results of two SQL statements into one result set?

Union

Which of the following SQL statements would successfully update the comment for comment_id number 4?

Update comments set comment_value='This is not a change' where comment_id=4;

If you wanted an average of all salaries paid to employees, what function are you likely to use?

avg

Which of the following is a conditional expression in SQL?

case

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

create table SALES ( Sales_id number primary key, Store_number number not null, Item_number number not null, Quantity number not null);

Which of the following SQL statements will delete just two rows?

delete from employees where emp_first_name like 'Am%'

Which if the following is a valid insert statement into the comments table? (Choose all that apply)

insert into comments (comment_id, comment_value) values (5,'This is a new comment); insert into comments values (5,'This is a new comment);

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 (34,to_date('01/02/2017','mm/dd/yyyy'), 50);

Which of the following data types are valid when creating a table? (Select all that are correct)

number varchar2(100) integer date

What command would ensure that the results of this operation can be recovered if the database fails?

rollback;

Which of the following SQL statements will return all rows where the word comment occurs?

select * from comments where instr(comment_value, 'comment')>O;

Which of the following parts of a SQL statements will correctly sort the output of the query by the name of the department the employee is assigned too?

select * from employees a, depts b where a.dept_dept_num=b.dept_num order by b.dept_desc;

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

select * from number_table order by 1 desc;

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

select * from people intersect select * from people_history;

Which of the following queries would list the rows in the teachers table for teacher_number 2 and 4?

select * from teachers where teacher_number in (2,4);

Which SQL statement will join the locations and inspections tables together into a single report showing all the inspections that occurred in 2016?

select a.location_description, a.location_add_date, b.inspection_date from locations a, inspections b where a.location_id=b.location_id and to_char(b.inspection_date, '/yyyy')='2016';

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 people_num = 6, 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=6 order by 1;

Which SQL statement will list all employee emp_first_name and emp_last_name for all employees where the dept_dept_num column equals 5?

select emp_last_name, emp_first_name from employees where dept_dept_no=5;

Which of the following queries will display the term_date in the following format:

select emp_num, to_char(term_date, 'mm/dd/yyyy hh24:mi:ss - DAY') from employees;

Which SQL below demonstrates a SQL group function? (Choose all that apply)

select last_name, first_name, sum(salary) from hr.employees group by last_name, first_name order by 1; select job_id, avg(salary) from hr.employees group by job_id;

Which SQL statement below will produce a list of all location descriptions that have a zip code that is in zip_region 2. An example of the output is seen here:

select location_description, count(*) from location where location_zip_id in (select zip_id from zip_codes where zip_region=2) group by location_description;

Which query will display the store_number, sales_date, employee_number and quantity_sold for the sales_date of 01 /02/2017?

select store_number, sales_date, employee_number, quantity_sold From sales Where to_char(sales_date,'mm/dd/yyyy')='01/02/2017';

Which of the following SQL statements will report on all teachers that have an average number of students of 25 or more in all their classes?

select teacher_number, avg(student_count) from class group by teacher_number having avg(student_count)>=25;

Which of the following answers would reformat the output so that it would look as follows:

select to_char(to_date('07/17/2017','mm/dd/yyyy'), 'DY DD MON YYYY') today_date from dual;

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

select user_id, sum(page_count) page_count from user_page_counts group by user_id;

Which option will return the columns user_last_name and user_first_name from the user_age table and then return them in order of user_age?

select user_last_name, user_first_name from user_age order by user_age;

Which of the following is(are) not a group function? (Choose all that apply)

total summary

Which of the answers below properly demonstrates the output from the following SQL statement? select to_char(sysdate, 'day') from dual;

tuesday

Which of the alter table commands adds a column that will store variable character data type that is up to 500 bytes in length?

varchar2(500 bytes)