*VET331: Oracle 12c Database Fundamentals 201610

You can use the SQL Worksheet to enter and execute SQL and PL/SQL statemets.

Commit: Writes changes to the DB and ends the transaction Run Statement: Executes a statement at the same point where the cursor is positioned Rollback: Undoes any changes and ends the transaction SQL History: Displays information about SQL statements that have been executed Tuning Advisory: Analyzes high volumes of SQL statements Run Script: Executes all statements

The rules precedence determine the order in which expressions are evaluated. In a SQL statement, you have already evaluated the arithmetic operator

Concatenation operator

DEFINE employee_num = 500

DEFINE

Match each SQL*Plus editing command to its function

DEL: Deletes the current line 0 text: Inserts a line before line 1 DEL m n: Deletes an inclusive set of lines C[HANGE]/text/: Deletes text from the current line I[NPUT]: Inserts an indefinite number of lines L[IST]: Displays all lines in the SQL buffer

Which commands could be used to display the structure of the Salaries table?

Describe salaries Desc salaries

Identify the option that enables you to insert a snippet into your code

Drag the snippet from the Snippet window to the desired location in the code, and then edit the syntax

Identify guidelines to create valid SQL statements

Enter statements across one or more lines Use indents to improve readability Do not abbreviate keywords Place clauses on separate lines Use semi colons to execute multiple SQL statements in a SQL Developer

Which options enable you to execute a saved script file in SQL Developer?

Enter the full path and name of the script in the SQL Worksheet area, prefix the entry with the @ symbol, and select the option to run the script Open the script file in the SQL Worksheet area, select the option to run the script, and select a connection

SELECT * FROM employees WHERE hire_date between '07-Feb-15' and '10-Jan-15'; SELECT * FROM employees WHERE last_name in ("King", "CHEN", "GEE");

In the IN clause, the names CHEN and GEE should not be upper case In the BETWEEN clause, the lower limit - the earlier date should be specialized first The names King, Chen and Gee should be enclosed in single quotes.

Match each character function to its description

Length: Returns the number of character value to the second character value CONCAT: Joins the first character value to the second character value TRIM: Enables you to remove leading character string INSTR: Returns the numeric position of a named string SUBSTR: Returns specified characters from the character value INITCAP: Converts aplha character values to uppercase for the first letter of each word.

Which options describe the use of arithmetic expressions in SQL?

Multiplication and division occur before addition and subtraction Operators of the same priority are evaluated from left to right Parenthesis can be used to override default precedence

Based on the given statement, identify how you would calculate the annual compensation of an employee. In the statement, the value 500 represents the employee's annual bonus: SELECT last_name, salary, 12*salary+500 FROM employees;

Multiply the annual salary by 12 and then add the bonus value of $500

Match each logical operator in SQL to the conditions they describe

NOT: Returns TRUE if the condition is false AND: Returns TRUE if both components are true OR: Returns TRUE if either component evaluates to true

Identify the sequence of the steps that enable you to view a report in SQL Developer

Open the Reports tab, locate and select the report you require, and choose a connection

Match each term used to discuss RDB to its description

Partitions: Enables a table to be subdivided into smaller pieces Views: A representation of a SQL statement stored in memory Sequences: Enables you to create auto-numbering fields Index: A database object to improve the performance of SELECT queries Table: Basic storage structure of a RDB Schema: Defines attributes of the DB

Which statements describe null values in SQL?

Primary key columns do not contain null values A value is null if it is unavailable, unassigned or unknown Arithmetic expression that use null values result in a null

Which operations describe the use of literal character strings in a SELECT statement?

Printed for each row returned Can include spaces Can include in the Query result

Match the description to the type of database management system it relates to.

Relational Database Management System -Supports logical operations-the application specifies what data is required -Supports physical operations- the db determines how a request is handled Object Relational DBMS -Supports classes and inheretance

Identify features of a single-row function

Returns one value Used Can

Identify the statements that represent the correct use of the alternative quote operator

SELECT department_name II q' [ Department's Manager ID: ]' II manager_id

Match the SERVEROUPUT and SPOOL commands to their functions. Options may be used more than once.

SPOOL: -Uses -Requires -Enables you SERVEROUTPUT: -Enables you to specify -Controls

Which statements describe SQL in a development environment?

SQL is the ANSI standard used for RDB The SELECT statement retrieves data The GRANT and REVOKE statements are referred to as Data Control Language

Match the SQL and SQL*Plus options to their features. Options may be used more than once.

SQL*Plus: -Accepts SQL input from files -Accepts ad hoc entry of statements -Formats SQL: -Reduces -Used -Formats

Identify some recommended settings when customizing SQL Developer

Specifying licensing for each connection Enabling the option to show line numbers

You have created and saves a SQL script, using the file name emp_dept_10.sql. You now want to execute this file.

Start @ symbol

ROUND and TRUNC dates for the special rows?

The 04-FEB-04 The 01-OCT-04 The 18-JUL-04

Identify options that describe SQL*Plus file commands

The APPEND command adds to an existing file The START command executes a previously saved command file The GET command writes the contents of a previously saves SQL file to the SQL buffer

Based on the given SQL statement, which options describe the column aliases?

The AS keyword is optional The second column's alias is commission, in uppercase

SELECT first name, lower(concat(subtr (first_name, 1 7), '_US' )) FROM employees WHERE department_id = 60;

The CONCAT function appends the first names to the string_US The (furst_name, 1 7)) function retrieves the first seven characters of the employees' first names The lower function is executed last

(sysdate, hire_date) >130;

The MONTHS_BETWEEN(sysdate, hire_date) The review date is calculated using the function ADD_MONTHS

SELECT * FROM employees WHERE hire_date between '07-Feb-15' and '10-Jan-15';

The names King in the Between On the IN clause

Which statements describe the data model and its components?

The physical schema describes how data is stored in tables The data model evolves into a logical model and then into a physical schema The Oracle SQL Developer Data Modeler simplifies data modeling tasks

You are working in SQL Developer and want to utilize the Data Modeler functionality to specify design rules and preferences.

Tools -> Data Modeler

Which options describe how to browse database objects and structures in SQL Developer?

Use the left pane in the Connections navigator to find objects Use the right pane in the Connections navigator to view the definition of objects

Which statements describe the AUTORACE SQL*Plus command?

Users should be granted PLUSTRACE privileges Enables you to display a report after the successful execution of SQL DML statements Can include execution statistics

SELECT last_name, job_id, commission_pct FROM employees WHERE commission_pct

is not null

SELECT sysdate, employee_id, last_name FROM employee;

sysdate

SELECT first_name, last_name, department_id FROM employees WHERE department_id = <create answer placeholder>

&department_id;

SELECT last_name, job_id FROM employees WHERE last_name = <answer field> SELECT last_name, job_id FROM employees WHERE hire_date = <answer field>

'21-MAR-15' 'Green'

Based on the syntax provided, which element is used to represent all columns? SELECT *I{[DISTINCT] column [alias],...] FROM table;

*(asterisk)

Which statements describe the relational database?

-Enables applications to manipulate the data and structure of the database -Supports Structured Query Language (SQL) -Includes kernel code to manage memory and storage -Supports data integrity for accuracy -Serves as a repository for auditing information and schema

Identify key features of Oracle SQL Developer

-Included by default in Oracle 12c, Release 1 -Supported on various platforms, including Linux and Mac -Includes GRANT statement support -Enables you to browse and manage objects and execute SQL scripts -Is the interface to manage Oracle Application Express Listener

Identify some debugging tasks available in SQL Developer

-Locating an execution point -Bypassing a method and continuing to the next statement -Moving to the last statement in a current method Removing invalid objects from the cache -Halting an execution

What actions can you perform when using the Recycle Bi in SQL Developer?

-Moving an object from the Recycle Bin back to its appropriate location in the Connections Navigator -Removing an object contents of a script into the SQL Worksheet

Which options describe using primary and foreign keys to relate tables?

-Provides only unique values in a primary key -Ensuring that a foreign key value matches an existing primary key value -Foreign keys are based on data values

Which options describe the entity relationship model?

-Separates business information from business activities -Outlines entities in a business and is derived from business specifications -Provides a framework to integrate multiple applications -Documents information clearly -Built in during the analysis phase of the system development lifecycle

Identify features of Oracle Database 12c

-Supports advanced data types, such as XML -Includes the SQL Performance Analyzer -Supports database replay -Manages environments such as PHP -Provides real-time database operations monitoring -Reduces downtime and data loss

What are some features of Oracle Cloud 12c?

-Supports provisioning, monitoring, and management control -Monitors the health of all components -Provides an integrated collection of application and platform cloud services -Enables you to scale up or down as needed -Manages all phases of the cloud lifecycle

SELECT ROUND (40.5555, -2)

0; 41; 40.56; 41

Suppose the current year is 1995 and the given date in a database is 05-MAR-15. What would the RR date format be?

2015

MOD (employee_id) <> 0;

A list of employees whose employee ID numbers are equal to an odd number

What actions can you perform when working with external tools in SQL Developer?

Adding a new tool Adding frequently used tools Removing unwanted tools Editing a tool

You are creating a table with SQL Developer, and want to specify the constraints at the same time.

Advanced

Which statements describe sample schemas in Oracle database?

Based on principles of simplicity, relevance, and inextensibility Examples of the sample schemas are HR, Sales Division, and Product Media The schemas are interlinked and have dependencies on each other

When creating a database connection, which connection type requires you to specify the hostname, port, and database service name?

Basic

you want to create a user-defined Salaries report within the employees folder. You have already accessed the User Defined Reports shortcuts menu. What is the next step?

Choose to create a new folder

Which actions enable you to execute a single SQL statement in SQL Developer?

Click the Run Statement icon Press F9 on the keyboard