Quiz Oracle Section 1-16 Programing ~ gus rifa

Section 1 Programming

CASE AND CARACTER MANIPULATION

Identify the output from the following SQL statement:

SELECT RPAD('SQL',6, '*')

FROM DUAL;

SQL***

Which query selects the first names of the DJ On Demand clients who have a first name beginning with "A"?

SELECT UPPER(first_name)

FROM d_clients

WHERE LOWER(first_name) LIKE 'a%'

Which character manipulation function always returns a numerical value?

LENGTH

What does the following SQL SELECT statement return?

SELECT UPPER( SUBSTR('Database Programming', INSTR('Database Programming','P'),20))

FROM dual;

PROGRAMMING

Which of the following SQL statements would correctly return a song title identified in the database as "All Thesears"?

WHERE title LIKE INITCAP('%all these years');

Which of the following are types of SQL functions?

Multi-Row Functions

Single-Row Functions

Character functions accept character arguments and only return character values. True or False?

False

Which query would return a user password combining the ID of an employee and the first 4 characters of the last name?

SELECT CONCAT (employee_id, SUBSTR(last_name,1,4))

AS "User Passwords"

FROM employees

NUMBER FUNCTIONS

The answer to the following script is 456. True or False?

SELECT TRUNC(ROUND(456.98))

FROM dual

False

Which number function may be used to determine if a value is odd or even?

MOD

What is the result of the following SQL Statement:

SELECT ROUND(45.923,-1)

FROM DUAL;

ROUND and TRUNC functions can be used with which of the following Datatypes?

Dates and numbers

DATE FUNCTIONS

What is the result of the following query?

SELECT ADD_YEARS ('11-JAN-94',6)

FROM dual;

This in not a valid SQL statement.

What function would you use to return the highest date in a month?

LAST_DAY

Which query would return a whole number if the sysdate is 26-MAY-04?

SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,'19-MAR-79') /12)

AS YEARS

FROM DUAL;

If hire_date has a value of '03-July-03', then what is the output from this code

SELECT ROUND(hire_date, 'Year') FROM employees;

01-JAN-04

Round and Trunc can be used on Date datatypes. True or False?

True

What is the result of the following query?

SELECT ADD_MONTHS ('11-JAN-94',6)

FROM dual;

11-JUL-1994

Section 2 Programming

CONVERSION FUNCTIONS

You need to display the HIRE_DATE values in this format: 25th of July 2002. Which SELECT statement would you use?

SELECT TO_CHAR(hire_date, 'ddth "of" Month YYYY')

FROM employees;

Which statement is true about SQL functions?

a, b and c are true.

A table has the following definition:

EMPLOYEES(

EMPLOYEE_ID NUMBER(6) NOT NULL,

NAME VARCHAR2(10) NOT NULL,

MANAGER_ID VARCHAR2(6))

and contains the following rows:

(1001, 'Bob Bevan', '200')

(200, 'Natacha Hansen', null)

Will the following query work?

SELECT *

FROM employees

WHERE employee_id = manager_id;

Yes, Oracle will perform implicit datatype conversion.

Which statement will return the salary of e.g. 6000 from the Employees table in the following format $6000.00?

SELECT TO_CHAR(salary, '$99999.00') SALARY

FROM employees

The following script will run successfully. True or False?

SELECT TO_CHAR(TO_DATE("25-DEC-04" ,'dd-MON-yy'))

FROM dual

False

Sysdate is 12-MAY-2004.

You need to store the following date: 7-DEC-89

Which statement about the date format for this value is true?

The RR date format will interpret the year as 1989, and the YY date format will interpret the year as 2089

NULL FUNCTIONS

The following statement returns 0 (zero). True or False?

SELECT 121/NULL

FROM dual;

False

Which function compares two expressions?

NULLIF

If quantity is a number datatype, what is the result of this statement?

SELECT NVL(200/quantity, 'zero') FROM inventory;

The statement fails

With the following data in Employees (last_name, commission_pct, manager_id) what is the result of the following statement?

DATA:

King,null,null

Kochhar, null,100

Vargas, null, 124

Zlotkey,.2, 100

SELECT last_name, NVL2(commission_pct, manager_id, -1) comm

FROM employees ;

King, -1

Kochhar, -1

Vargas, -1

Zlotkey, 100

Consider the following data in Employees table: (last_name, commission_pct, manager_id)

DATA:

King,null,null

Kochhar, null,100

Vargas, null, 124

Zlotkey,.2, 100

What is the result of the following statement:

SELECT last_name, COALESCE(commission_pct, manager_id, -1) comm

FROM employees ;

King, -1

Kochhar, 100

Vargas, 124

Zlotkey, .2

CONDITIONAL EXPRESSIONS

CASE and DECODE evaluate expressions in a similar way to IF-THEN-ELSE logic. However, DECODE is specific to Oracle syntax. True or False?

True

Which statement will return a listing of last names, salaries and a rating of 'Low', 'Medium', 'Good' or 'Excellent' depending on the salary value?

SELECT last_name,salary,

(CASE WHEN salary<5000 THEN 'Low'

WHEN salary<10000 THEN 'Medium'

WHEN salary<20000 THEN 'Good'

ELSE 'Excellent'

END) qualified_salary

FROM employees;

For the given data from Employees (last_name, manager_id) what is the result of the following statement:

DATA:( King, null

Kochhar, 100

De Haan, 100

Hunold, 102

Ernst, 103)

SELECT last_name,

DECODE(manager_id, 100, 'King', 'A N Other') "Works For?"

FROM employees

King, A N Other

Kochhar, King

De Haan, King

Hunold, A N Other

Ernst, A N Other

Which of the following is a conditional expression used in SQL?

CASE

Section 3 Programming

CROSS JOINS AND NATURAL JOINS

A NATURAL JOIN is based on:

Columns with the same name and datatype

The ___________ join is the ANSI-standard syntax used to generate a Cartesian product.

CROSS

The join column must be included in the select statement when you use the NATURAL JOIN clause. True or False?

False

What happens when you create a Cartesian product?

All rows from one table are joined to all rows of another table

JOIN CLAUSES

The following is a valid SQL statement.

SELECT e.employee_id, e.last_name, d.location_id, department_id

FROM employees e JOIN departments d

USING (department_id) ;

True or False?

True

The primary advantage of using JOIN ON is:

It permits columns with different names to be joined

Table aliases MUST be used with columns referenced in the JOIN USING clause. True or False?

False

You can do nonequi-joins with ANSI-Syntax. True or False?

True

The keywords JOIN _____________ should be used to join tables with the same column names but different datatypes.

USING

INNER VERSUS OUTER JOINS

What is another name for a simple join or an inner join?

Equijoin

Given the following descriptions of the employees and jobs tables, which of the following scripts will display each employee's possible minimum and maximum salaries based on their job title?

EMPLOYEES Table:

Name Null? Type

EMPLOYEE_ID NOT NULL NUMBER (6)

FIRST_NAME VARCHAR2 (20)

LAST_NAME NOT NULL VARCHAR2 (25)

EMAIL NOT NULL VARCHAR2 (25)

PHONE_NUMBER VARCHAR2 (20)

HIRE_DATE NOT NULL DATE

JOB_ID NOT NULL VARCHAR2 (10)

SALARY NUMBER (8,2)

COMMISSION_PCT NUMBER (2,2)

MANAGER_ID NUMBER (6)

DEPARTMENT_ID NUMBER (4)

JOBS Table:

Name Null? Type

JOB_ID NOT NULL VARCHAR2 (10)

JOB_TITLE NOT NULL VARCHAR2 (35)

MIN_SALARY NUMBER (6)

MAX_SALARY NUMBER (6)

SELECT first_name, last_name, job_id, min_salary, max_salary

FROM employees

NATURAL JOIN jobs;

The following statement is an example of what kind of join?

SELECT car.vehicle_id, driver.name

FROM car

LEFT OUTER JOIN driver ON (driver_id) ;

Outer Join

Which syntax would be used to retrieve all rows in both the EMPLOYEES and DEPARTMENTS tables, even when there is no match?

FULL OUTER JOIN

For which of the following tables will all the values be retrieved even if there is no match in the other?

SELECT e.last_name, e.department_id, d.department_name

FROM employees e

LEFT OUTER JOIN departments d

ON (e.department_id = d.department_id);

employees

EMPLOYEES Table:

Name Null? Type

EMPLOYEE_ID NOT NULL NUMBER(6)

FIRST_NAME VARCHAR2(20)

LAST_NAME NOT NULL VARCHAR2(25)

DEPARTMENT_ID NUMBER (4)

DEPARTMENTS Table:

Name Null? Type

DEPARTMENT_ID NOT NULL NUMBER 4

DEPARTMENT_NAME NOT NULL VARCHAR2(30)

MANAGER_ID NUMBER (6)

A query is needed to display each department and its manager name from the above tables. However, not all departments have a manager but we want departments returned in all cases. Which of the following SQL: 1999 syntax scripts will accomplish the task?

SELECT d.department_id, e.first_name, e.last_name

FROM employees e

RIGHT OUTER JOIN departments d

ON (e.employee_id = d.manager_id);

If you select rows from two tables (employees and departments) using an outer join, what will you get? Use the code below to arrive at your answer:

SELECT e.last_name, e.department_id, d.department_name

FROM employees e

LEFT OUTER JOIN departments d

ON (e.department_id = d.department_id);

All employees including those that do not have a departement_id assigned to them

SELF JOINS VERSUS OUTER JOINS

Which of the following database design concepts is implemented with a self join?

Recursive Relationship

Hierarchical queries can walk both Top-Down and Bottom-Up. True or False?

True

Which select statement will return the last name and hire date of an employee and his/ her manager for employees that started in the company before their managers?

SELECT w.last_name, w.hire_date, m.last_name, m.hire_date

FROM employees w , employees m

WHERE w.manager_id = m.employee_id

AND w.hire_date < m.hire_date

Hierarchical queries MUST use the LEVEL pseudo column. True or False?

False

Which SELECT statement implements a self join?

SELECT e.employee_id, m.manager_id

FROM employees e, employees m

WHERE m.employee_id = e.manager_id;

Which of the following database design concepts do you need in your tables to write Hierarchical queries?

Recursive Relationship

Section 4 Programming

GROUP FUNCTIONS

You can use GROUP functions in all clauses of a SELECT statement. True or False?

False

What two group functions can be used with any datatype?

MIN, MAX

Given the following data in the employees table (employee_id, salary, commission_pct)

DATA: (143, 2600, null

144, 2500, null

149, 10500, .2

174, 11000, .3

176, 8600, .2

178, 7000, .15)

What is the result of the following statement:

SELECT AVG(commission_pct)

FROM employees

WHERE employee_id IN( 143,144,149,174,176,178)

0.2125

The following statement will work because it uses the same column with different GROUP functions:

SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)

FROM employees;

True or False?

True

Given the following data in the employees table (employee_id, salary, commission_pct)

DATA: (143, 2600, null

144, 2500, null

149, 10500, .2

174, 11000, .3

176, 8600, .2

178, 7000, .15)

What is the result of the following statement:

SELECT SUM(commission_pct), COUNT(salary)

FROM employees

WHERE employee_id IN( 143,144,149,174,176,178)

SUM = .85 and COUNT = 6

COUNT, DISTINCT, NVL

What would the following SQL statement return?

SELECT COUNT(DISTINCT salary)

FROM employees;

The number of unique salaries in the employees table

To include null values in the calculations of a group function, you must:

Convert the null to a value using the NVL( ) function

What would the following SQL statement return?

SELECT COUNT(first_name)

FROM employees;

The total number of non-null first names in the employees table

Using your existing knowledge of the employees table, would the following two statements produce the same result?

SELECT COUNT(*)

FROM employees;

SELECT COUNT(commission_pct)

FROM employees;

Given the following data in the employees table (employee_id, salary, commission_pct)

DATA: (143, 2600, null

144, 2500, null

149, 10500, .2

174, 11000, .3

176, 8600, .2

178, 7000, .15)

What is the result of the following statement:

SELECT SUM(commission_pct), COUNT(commission_pct)

FROM employees

WHERE employee_id IN( 143,144,149,174,176,178)

SUM = .85 and COUNT = 4

Section 5 Programming

USING GROUP BY AND HAVING CLAUSES

Is the following statement correct?

SELECT department_id, AVG(salary)

FROM employees;

No, because a GROUP BY department_id clause is needed

Which of the following SQL statements could display the number of people with the same last name:

SELECT last_name, COUNT(last_name)

FROM EMPLOYEES

GROUP BY last_name;

How would you alter the following query to list only employees where there existed more than one per last_name with the same last name:

SELECT last_name, COUNT(employee_id)

FROM EMPLOYEES

GROUP BY last_name;

SELECT last_name, COUNT(last_name)

FROM EMPLOYEES

GROUP BY last_name

HAVING COUNT(last_name) > 1;

Read the following SELECT statement. Choose the column or columns that must be included in the GROUP BY clause.

SELECT COUNT(last_name), grade, gender

FROM STUDENTS

GROUP_BY ?????;

grade, gender

Is the following statement correct:

SELECT first_name, last_name, salary, department_id, COUNT(employee_id)

FROM employees

WHERE department_id = 50

GROUP BY last_name, first_name, department_id;

No, because the statement is missing salary in the GROUP BY clause

USING ROLLUP AND CUBE OPERATIONS, AND GROUPING SETS

Examine the following statement:

SELECT department_id, manager_id, job_id, SUM(salary)

FROM employees

GROUP BY GROUPING SETS(.......);

Select the correct GROUP BY GROUPING SETS clause from the following list:

GROUP BY GROUPING SETS ((department_id, manager_id), (department_id, job_id), (manager_id, job_id))

Examine the following statement:

SELECT department_id, manager_id, job_id, SUM(salary)

FROM employees

GROUP BY ROLLUP(department_id, manager_id)

What extra data will this query generate?

The statement will fail.

Examine the following statement:

SELECT department_id, manager_id, job_id, SUM(salary)

FROM employees

GROUP BY GROUPING SETS((department_id, manager_id), (department_id, job_id))

What data will this query generate?

Sum of salaries for (department_id, job_id) and (department_id, manager_id)

If you want to include subtotals and grand totals for all columns mentioned in a GROUP BY clause you should use which of the following extensions to the GROUP BY clause?

CUBE

USING SET OPERATORS

MINUS will give you rows from the first query not present in the second query? (True or False)

True

INTERSECT will give you the common rows found in both queries? (True or False)

True

Which ones of the following are correct SET operators?

UNION, MINUS

UNION ALL, INTERSECTION

The difference between UNION and UNION ALL is

UNION will remove duplicates, UNION ALL returns all rows from all queries

Section 6 Programming

FUNDAMENTALS OF SUBQUERIES

What will the following statement return:

SELECT last_name, salary

FROM employees

WHERE (department_id, job_id) = (SELECT department_id, job_id FROM employees WHERE employee_id = 103)

A list of last_names and salaries of employees that works in the same department and has the same job_id as that of employee 103.

What will the following statement return:

SELECT employee_id, last_name

FROM employees

WHERE salary =(SELECT MIN(salary) FROM employees GROUP BY department_id);

Nothing. It is an invalid statement.

Which of the following statements is a true guideline for using subqueries?

The outer and inner queries can reference more than one table. They can get data from different tables.

What will the following statement return:

SELECT last_name, salary

FROM employees

WHERE salary < (SELECT salary FROM employees WHERE employee_id = 103)

A list of last_names and salaries of employees that makes less than employee 103

Examine the following statement:

SELECT last_name, salary

FROM employees

WHERE department_id = (SELECT department_id FROM employees WHERE employee_id = 103) AND job_id = (SELECT job_id FROM employees WHERE employee_id = 103)

Is this a pair-wise or non-pair-wise Subquery?

This is an example of a non-pair-wise subquery.

Subqueries can only be placed in the WHERE clause. True or False?

False

SINGLE ROW SUBQUERIES

If the subquery returns no rows will the outer query return any values?

No, because the subquery will be treated like a null value.

Subqueries are limited to four per SQL transaction. True or False?

False

In a non-correlated subquery, the outer query always executes prior to the inner query's execution. True or False?

False

Single row subqueries may not include this operator:

ALL

The result of this statement will be:

SELECT last_name, job_id, salary, department_id

FROM employees

WHERE job_id =(SELECT job_id FROM employees WHERE employee_id = 141) AND department_id = (SELECT department_id FROM departments WHERE location_id =1500)

Only the employees whose job id matches employee 141 and who work in location 1500

MULTIPLE-ROW SUBQUERIES

In a subquery the ALL operator compares a value to every value returned by the inner query. True or False?

True

The SQL multiple-row subquery extends the capability of the single-row syntax through the use of what three comparison operators?

IN, ANY and ALL

Multiple-row subqueries must have NOT, IN or ANY in the WHERE clause of the inner query. True or False?

False

Group functions, such as HAVING and GROUP BY can be used in multiple-row subqueries. True or False?

True

When a multiple-row subquery uses the NOT IN (<>ALL) operator, if one of the values returned by the inner query is a null value, the entire query returns:

No rows returned

Group functions can be used in subqueries even though they may return many rows. True or False?

True

The salary column of the f_staffs table contains the following values: 4000 5050 6000 11000 23000

Which of the following statements will return the last_name and first_name of those employees who earn more than 5000.

SELECT last_name, first_name

FROM f_staffs

WHERE salary IN (SELECT salary FROM f_staffs WHERE salary > 5000);

There can be more than one subquery returning information to the outer query. True or False?

True

CORRELATED SUBQUERIES

The WITH-clause is a way of creating extra tables in the database? (True or False)

False

Correlated Subqueries must work on the same tables in both the inner and outer query?

False

In a correlated subquery the outer and inner query are joined on one or more columns?

True

Table aliases must be used when you are writing correlated subqueries?

True

Section 7 Programming

INSERT STATEMENTS

Insert statements can be combined with subqueries to create more than one row per statement. True or False?

True

Which of the following statements will add a new customer to the customers table in the Global Fast Foods database?

INSERT INTO customers (id, first_name, last_name, address, city, state, zip, phone_number)

VALUES (145, 'Katie', 'Hernandez', '92 Chico Way', 'Los Angeles', 'CA', 98008, 8586667641);

If the employees table have 7 rows how many rows are inserted into the copy_emps table with the following statement:

INSERT INTO copy_emps (employee_id, first_name, last_name, salary, department_id)

SELECT employee_id, first_name, last_name, salary, department_id

FROM employees

7 rows, as there is no WHERE-clause on the subquery.

To return a table summary on the customers table, which of the following is correct?

DESCRIBE customers, or DESC customers

DML is an acronym that stands for:

Data Manipulation Language

When inserting rows into a table all columns must be given values. True or False?

False

When inserting a new row the null keyword can be included in the values list for any null column. True or False?

True

Is it possible to insert more than one row at a time using an INSERT statement with a VALUES clause?

No, you can only create one row at a time when using the VALUES clause.

What is the quickest way to use today's date when you are creating a new row?

Use the SYSDATE function.

UPDATING COLUMN VALUES AND DELETING ROWS

If you are performing an UPDATE statement with a subquery, it MUST be a correlated subquery?

False

To change an existing row in a table, you can use the UPDATE or INSERT statements. True or False?

False

Assuming there are no Foreign Keys on the EMPLOYEES table, if the following subquery returns one row, how many rows will be deleted from the EMPLOYEES table?

DELETE FROM employees

WHERE department_id =

(SELECT department_id

FROM departments

WHERE department_name LIKE '%Public%');

All the rows in the EMPLOYEES table with department_ids matching the department_id returned by the subquery.

Using your knowledge of the employees table, what would be the result of the following statement:

DELETE FROM employees;

All rows in the employees table will be deleted if there are no constraints on the table.

Which of the following statements best describes what will happen to the student table in this SQL statement?

UPDATE students

SET lunch_number = (SELECT lunch_number FROM student WHERE student_id = 17)

WHERE student_id = 19;

The statement updates the student_table by replacing student id 19's lunch number with student id 17's lunch number.

DELETE statements can use correlated subqueries?

True

How many rows will be deleted from the employees table with the following statement?

DELETE FROM employees

WHERE last_name = 'king';

No rows will be deleted, as no employees match the WHERE-clause.

If the subquery returns one row, how many rows will be deleted from the employees table?

DELETE FROM employees

WHERE department_id = (SELECT department_id FROM departments WHERE department_name LIKE '%Public%');

All rows in the employees table which work in the given department will be deleted.

Is the following statement valid, i.e. is it allowed to update rows in one table, based on a subquery from another table?

UPDATE copy_emp

SET department_id = (SELECT department_id FROM employees WHERE employee_id = 100)

WHERE job_id = (SELECT job_id FROM employees WHERE employee_id = 200);

Yes, this is a perfectly valid statement.

DEFAULT VALUES, MERGE, AND MULTI-TABLE INSERTS

The MERGE function combines the:

INSERT and UPDATE commands

A multi-table insert statement can insert into more than one table?

True

In developing the Employees table, you create a column called hire_date. You assign the hire_date column a DATE datatype with a DEFAULT value of 0 (zero). A user can come back later and enter the correct hire_date. This is __________.

A bad idea. The default value must match the DATE datatype of the column.

Which statement below will not insert a row of data onto a table?

INSERT INTO (id, lname, fname, lunch_num)

VALUES (143354, 'Roberts', 'Cameron', 6543);

If a default value was set for a null column, Oracle sets the column to the default value. However, if no default value was set when the column was created, Oracle inserts an empty space. True or False?

False

The MERGE statement can be used to update rows in one table based on values in another table and if the update fails, then the rows will automatically be inserted instead. True or False?

True

A multi-table insert statement must have a subquery at the end of the statement?

True

The DEFAULT keyword can be used in the following statements:

INSERT and UPDATE

Section 8 Programming

CREATING TABLES

Once they are created, external tables are accessed with normal SQL statements?

True

I have a table named School_Friends in my schema. You want to build a table in your schema named School_Friends. This is ______________, because ____________________________________.

possible; my schema is separate from yours, and it is okay for us to have like-named tables in our separate schemas.

When creating a new table, which of the following naming rules apply.

Must begin with a letter

Must contain ONLY A - Z, a - z, 0 - 9, _ (underscore), $, and #

Must be between 1 to 30 characters long

It is possible to create a table by using the CREATE TABLE command in conjunction with a subquery. True or False?

True

CREATE TABLE student_table

(id NUMBER(6),

lname VARCHAR(20),

fname VARCHAR(20),

lunch_num NUMBER(4));

Which of the following statements best describes the above SQL statement:

Creates a table named student_table with four columns: id, lname, fname, lunch_num

CREATE TABLE bioclass

(hire_date DATE DEFAULT SYSDATE,

first_name varchar2(15),

last_name varchar2(15));

The above CREATE TABLE statement is acceptable, and will create a Table named bioclass that contains a hire_date, first_name and last_name column. True or False?

True

DCL, which is the acronym for Data Control Language, allows:

A Database Administrator the ability to grant privileges to users.

Given this employee table:

(employee_id NUMBER(10) NOT NULL,

first_name VARCHAR2(25) NOT NULL,

last_name VARCHAR2(30) NOT NULL,

hire_date DATE DEFAULT sysdate)

What will be the result in the hire_date column following this insert statement:

INSERT INTO employees VALUES (10, 'Natacha', 'Hansen', DEFAULT);

Statement will work and the hire_date column will have the value of the date when the statement was run.

Examine this CREATE TABLE statement:

CREATE TABLE emp_load (employee_number CHAR(5), employee_dob CHAR(20), employee_last_name CHAR(20), employee_first_name CHAR(15), employee_middle_name CHAR(15), employee_hire_date DATE)

ORGANIZATION EXTERNAL TYPE ORACLE_LOADER DEFAULT DIRECTORY def_dir1 ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE FIELDS (employee_number CHAR(2), employee_dob CHAR(20), employee_last_name CHAR(18), employee_first_name CHAR(11), employee_middle_name CHAR(11), employee_hire_date CHAR(10) date_format DATE mask "mm/dd/yyyy")) LOCATION ('info.dat'));

What kind of table is created here?

An external table with the data stored in a file outside the database.

USING DATA TYPES

INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. True or False?

True

Which of the following are valid Oracle datatypes?

DATE, TIMESTAMP WITH LOCAL TIMEZONE, BLOB

To store large amounts of text you should simply create a series of VARCHAR2 columns in a table. True or False?

False

The BLOB datatype can max hold 128 Terabytes of data. True or False?

True

MODIFYING A TABLE

ALTER TABLE table_name RENAME can be used to:

Rename a table.

A column's data type can always be changed from NUMBER to VARCHAR2 but not from VARCHAR2 to NUMBER, provided the table is empty. True or False?

False

Comments can be added to a table by using the COMMENT ON TABLE statement. The comments being added are enclosed in:

Single quotes ' '

The FLASHBACK TABLE to BEFORE DROP can restore only the table structure, but not its data back to before the table was dropped. True or False?

False

After issuing a SET UNUSED command on a column, another column with the same name can be added using an ALTER TABLE statement. True or False?

True

Which of the following will correctly change the name of the LOCATIONS table to NEW_LOCATIONS?

RENAME LOCATIONS TO NEW_LOCATIONS

You can use the ALTER TABLE statement to:

All of the above

The following code creates a table named student_table with four columns: id, lname, fname, lunch_num

CREATE TABLE student_table (id NUMBER(6), lname VARCHAR(20), fname VARCHAR(20), lunch_num NUMBER(4));

The lunch_num column in the above table has been marked as UNUSED. Which of the following is the best statement you can use if you wish to remove the UNUSED column from the student_table?

ALTER TABLE DROP UNUSED COLUMNS

The FLASHBACK QUERY statement can restore data back to a point in time before the last COMMIT? True or False?

False

To completely get rid of a table, its contents, its structure, AND release the storage space the keyword is:

DROP

When should you use the SET UNUSED command?

You should use it when the system is being heavily used

You can use DROP COLUMN to drop all columns in a table, leaving a table structure with no columns. True or False?

False

The data type of a column can never be changed once it has been created. True or False?

False

When you use ALTER TABLE to add a column, the new column:

Becomes the last column in the table

Section 10 Programming

DEFINING NOT NULL AND UNIQUE CONSTRAINTS

A table can have more than one UNIQUE key constraint. True or False?
True

Which of the following is not a valid Oracle constraint type?
EXTERNAL KEY

A column defined as NOT NULL can have a DEFAULT value of NULL. True or False?
False

A unique key constraint can only be defined on a not null column. True or False?
False

If the employees table has a UNIQUE constraint on the DEPARTMENT_ID column, we can only have one employee per department. True or False?
True

A table must have at least one not null constraint and one unique constraint. True or False?  
False

PRIMARY KEY, FOREIGN KEY, AND CHECK CONSTRAINTS

Foreign Key Constraints are also known as:


Referential Integrity Constraints


A Primary Key that is made up of more than one column is
called a:

Composite Primary Key




The employees table contains a foreign key column department_id that references the id column in the departments table. Which of the following constraint modifiers will NOT allow the deletion of id values in the department table?

Neither A nor B


Which line of the following code will cause an error: 

1. CREATE TABLEMclients

2. (client_number NUMBER(4) CONSTRAINT client_client_num_pk PRIMARY KEY client_number),

3. first_name VARCHAR2(14),

4. last_name VARCHAR2(13),

5. hire_date DATE CONSTRAINT emp_min_hire_date CHECK (hire_date < SYSDATE),

6. department_id VARCHAR(3),

7. CONSTRAINT clients_dept_id_fk FOREIGN KEY(department_id) REFERENCES departments(department_id));           


Line 2


The table that contains the Primary Key in a Foreign Key Constraint is known as:

Parent Table




If a Primary Key is made up of more than one column, one of the columns can be null. True or False?

False




An example of adding a check constraint to limit the salary that an employee can earn is:

ALTER TABLE employees ADD CONSTRAINT emp_salary_ck CHECK (salary < 100000)




A composite primary key may only be defined at the table level. True or False?

True


To automatically delete rows in a child table when a parent record is deleted use:

ON DELETE CASCADE


The main reason that constraints are added to a table is:

Constraints ensure data integrity


Which of the following pieces of code will NOT successfully create a foreign key in the CDS table that references the SONGS table

None of the above


The number of check constraints that can be defined on a column is:

There is no limit







MANAGING CONSTRAINTS



What mechamisn does Oracle use in the background to enforce uniqueness in Primary and Unique key constraints?

Unique indexes are created in the background by Oracle when Primary and Unique constraints are created or enabled




Once constraints have been created on a table you will have to live with them as they are unless you drop and re-create the table. True or False?

False


You can drop a column in a table with a simple ALTER TABLE DROP COLUMN statement, even if the column is referenced in a constraint. True or False?

False



All of a user's constraints can be viewed in the Oracle Data Dictionary view called:

USER_CONSTRAINTS



The command to 'switch off' a constraint is:

ALTER TABLE DISABLE CONSTRAINT

 

 

 


Section 11 Programming





CREATING VIEWS
What is one advantage of using views?

To provide restricted data access



Views contain no data of their own. True or False?

True



Given the following CREATE VIEW statement, what data will be returned?

CREATE OR REPLACE VIEW emp_dept

AS SELECT SUBSTR(e.first_name,1,1) ||' '||e.last_name emp_name, e.salary, e.hire_date, d.department_name

FROM employees e, departments d

WHERE e.department_id = d.department_id AND d.department_id >=50;

First character from employee first_name concatenated to the last_name, 
the salary, the hire_date and department_name of all employees working 
in department number 50 or higher



A view can contain a select statement with a subquery. True or False?

True



A view can contain group functions. True or False?

True



Any select statement can be stored in the database as a view. True or False?

True






DML OPERATIONS AND VIEWS
Given the following view what operations would be allowed on the emp_dept view:

CREATE OR REPLACE VIEW emp_dept

AS SELECT SUBSTR(e.first_name,1,1) ||' '||e.last_name emp_name, e.salary, e.hire_date, d.department_name

FROM employees e, departments d

WHERE e.department_id = d.department_id AND d.department_id >=50;

SELECT, UPDATE of some columns, DELETE



If a database administrator wants to ensure that changes performed 
through a view do not violate existing constraints, which clause should 
he/she include when creating the view?

WITH CHECK OPTION

Which of the following DML operations is not allowed when using a Simple View created with read only?

All of the above



There is only one kind view? True or False?

False



Using the pseudocolumn ROWNUM in a view has no implications on the ability to do DML's through the view. True or False?

False



Examine the view below and choose the operation that CANNOT be performed on it.

CREATE VIEW dj_view (last_name, number_events) AS

SELECT c.last_name, COUNT(e.name)

FROM d_clients c, d_events e

WHERE c.client_number = e.client_number

GROUP BY c.last_name

INSERT INTO dj_view VALUES ('Turner', 8);






MANAGING VIEWS
When you drop a table referenced by a view, the view is automatically dropped as well. True or False?

False



Which of the following is true about ROWNUM?

It is the number assigned to each row returned from a query as they are read from the table.



How do you remove a view?

DROP VIEW view_name



A Top-N Analysis is capable of ranking a top or bottom set of results. True or False?

True



When you drop a view, the data it contains is also deleted. True or False?

False



Which of these Keywords is typically used with a Top-N Analysis?

Rownum



Which of these is not a valid type of View?

ONLINE

 

 

 

 


Section 12 Programming






WORKING WITH SEQUENCES

A sequence is a window through which data can be queried or changed. True or False?

False

           

CURRVAL
 is a pseudocolumn used to refer to a sequence number that the current 
user has just generated by referencing NEXTVAL. True or False? 

True

           

A sequence is a database object. True or False?

True

           

Which is the correct syntax for specifying a maximum value in a sequence?

Maxvalue

           

In
 order to be able to generate primary key values that are not likely to 
contain gaps, which phrase should be included in the sequence creation 
statement? 

NOCACHE

           

Which keyword is used to remove a sequence?

Drop

          

Why do gaps in sequences occur?

All of the above

           

When you alter a sequence, a new increased MAXVALUE can be entered without changing the existing number order. True or False? 

True

           

Nextval and Currval are know as column aliases. True or False? 

False

           

Which keyword is used to modify a sequence? 

Alter

           

CURRVAL is a pseudocolumn used to extract successive sequence numbers from a specified sequence. True or False?

False

           

Examine the code for creating this sequence:

CREATE SEQUENCE track_id_seq 

INCREMENT BY 10 

START WITH 1000 MAXVALUE 10000 

What are the first three values that would be generated by the sequence?

1000, 1010, 1020

            




INDEXES AND SYNONYMS

It
 is possible to have an indexed column in a table where a value in the 
table column does not exist in the index. True or False?

False

           

What kind of INDEX is created by Oracle when you create a primary key?

UNIQUE INDEX

           

All tables must have indexes on them otherwise they cannot be queried. True or False?

False

           

You must use a synonym to access another users table. True or False?

False

           

In SQL what is a synonym?

A different name for a table, view or other database object

           

Which
 of the following SQL statements shows a correct syntax example of 
creating a synonym accessible to all users of a database?

CREATE PUBLIC SYNONYM emp FOR EMPLOYEES

           

Indexes can be used to speed up queries. True or False?

True

           

Which of the following statements best describes indexes and their use?

They contain the column value and pointers to the data in the table, but the data is sorted.

Section 13 Programming

CONTROLLING USER ACCESS

The following table shows some of the output from one of the data dictionary views. Which view is being queried?

USERNAME PRIVILEGE ADMIN_OPTION

USCA_ORACLE_SQL01_S08 CREATE VIEW NO

USCA_ORACLE_SQL01_S08 CREATE TABLE NO

USCA_ORACLE_SQL01_S08 CREATE SYNONYM NO

USCA_ORACLE_SQL01_S08 CREATE TRIGGER NO

USCA_ORACLE_SQL01_S08 CREATE SEQUENCE NO

USCA_ORACLE_SQL01_S08 CREATE DATABASE NO

user_sys_privs (lists system privileges granted to the user)

Which Object Privilege apart from Alter can be granted to a Sequence?

SELECT

Object privileges are:

Required to manipulate the content of objects in the database.

By Controlling User Access with Oracle Database Security you can give access to specific Objects in the Database. True or False?

True

Which of the following Object Privileges can be granted on an individual column on a table?

Update

References

A Schema is a collection of Objects such as Tables, Views and Sequences. True or False?

True

Which of these is not a System Privilege granted by the DBA?

Create Index

Which of the following is not a database object?

Subquery

What system privilege must be held in order to login to an Oracle database?

CREATE SESSION

A schema is:

A collection of objects, such as tables, views, and sequences.

System privileges are:

Required to gain access to the database.

CREATING AND REVOKING OBJECT PRIVILEGES

Which of the following statements about granting object privileges is false?

Object privileges can only be granted through roles.

If you are granted privileges to your friend's object, by default you may also grant access to this same object to other users. True or False?

False

What Oracle feature simplifies the process of granting and revoking privileges?

Role

User1 owns a table and grants select on it WITH GRANT OPTION to User2. User2 then grants select on the same table to User3. If User1 revokes select privileges from User2, will User3 be able to access the table?

Roles are:

Named groups of related privileges given to a user or another role.

When a user is logged into one database, they are restricted to working with objects found in that database. True or False?

False

To take away a privilege from a user you use which command?

REVOKE

Scott King owns a table called employees. He issues the following statement:

GRANT select ON employees TO PUBLIC;

Allison Plumb has been granted CREATE SESSION by the DBA. She logs into the database and issues the following statement:

GRANT ﾠselect ON ﾠscott_king.employees TO jennifer_cho;

True or False: Allison's statement will fail.

True

A role can be granted to another role. True or False?

True

Which of the following statements is true?

Database Links allow users to work on remote database objects without having to log into the other database.

REGULAR EXPRESSIONS

Select the correct REGULAR EXPRESSION functions:
REGEXP_LIKE, REGEXP_REPLACE
REGEXP_INSTR, REGEXP_SUBSTR

REGULAR EXPRESSIONS can be used as a part of contraint definitions?

True

REGULAR EXPRESSIONS does exactly the same as LIKE. No more and no less?

False

REGULAR EXPRESSIONS can be used on CHAR, CLOB and VARCHAR2 datatypes?

True

Section 14 Programming

DATABASE TRANSACTIONS

A transaction makes several successive changes to a table. If required, you want to be able to rollback the later changes while keeping the earlier changes. What must you include in your code to do this?

A savepoint

User BOB's CUSTOMERS table contains 20 rows. BOB inserts two more rows into the table but does not COMMIT his changes. User JANE now executes:

SELECT COUNT(*) FROM bob.customers;

What result will JANE see?

Which of the following best describes the term "read consistency"?

It prevents other users from seeing changes to a table until those changes have been committed

Table MYTAB contains only one column of datatype CHAR(1). A user executes the following statements in the order shown.

INSERT INTO mytab VALUES ('A');

INSERT INTO mytab VALUES ('B');

COMMIT;

INSERT INTO mytab VALUES ('C');

ROLLBACK;

Which rows does the table now contain?

A and B

If a database crashes, all uncommitted changes are automatically rolled back. True or False?

True

Steven King's row in the EMPLOYEES table has EMPLOYEE_ID = 100 and SALARY = 24000. A user issues the following statements in the order shown:

UPDATE employees

SET salary = salary * 2

WHERE employee_id = 100;

COMMIT;

UPDATE employees

SET salary = 30000

WHERE employee_id = 100;

The user's database session now ends abnormally. What is now King's salary in the table?

48000

Examine the following statements:

UPDATE employees SET salary = 15000;

SAVEPOINT upd1_done;

UPDATE employees SET salary = 22000;

SAVEPOINT upd2_done;

DELETE FROM employees;

You want to retain all the employees with a salary of 15000; What statement would you execute next?

ROLLBACK TO SAVEPOINT upd1_done;

Which SQL statement is used to remove all the changes made by an uncommitted transaction?

ROLLBACK;

Section 15 Programming

CARTESIAN PRODUCT AND THE JOIN OPERATIONS

When must column names be prefixed by table names in join syntax?

When the same column name appears in more than one table of the query

Will the following statement work?

SELECT department_name, last_name

FROM employees, departments

WHERE department_id = department_id;

No, Oracle will return a column ambiguously defined error.

If table A have 10 rows and table B have 5 rows, how many rows will be returned if you perform a cartesian join on those two tables?

Oracle proprietary JOINS can use the WHERE clause for conditions other than the join-condition. True or False?

True

What is the result of a query that selects from two tables but includes no join condition?

A Cartesian product

If table A have 10 rows and table B have 5 rows, how many rows will be returned if you perform a equi-join on those two tables?

It depends on the data found in the two tables.

NONEQUIJOINS

The following statement is an example of a nonequi-join?

SELECT e.last_name, e.salary, j.grade_level

FROM employees e, job_grades j

WHERE e.salary

BETWEEN j.lowest_sal AND j.highest_sal;

True or False?

True

Which of the following operators is/are typically used in a nonequijoin?

>=, <=, BETWEEN ...AND

Which statement about joining tables with a non-equijoin is false?

A WHERE clause must specify a column in one table that is compared to a column in the second table

OUTER JOINS

To perform a valid outer join between DEPARMENTS and EMPLOYEES to list departments without employees select the correct WHERE clause for the following select statement:

SELECT d.department_name, e.last_name

FROM employees e, departments d

WHERE

e.department_id(+) = d.department_id

The following is a valid outer join statement:

SELECT c.country_name, d.department_name

FROM countries c, departments d

WHERE c.country_id (+) = d.country_id (+)

True or False?

False

Which symbol is used to perform an outer join?

(+)

The ID column in the CLIENT table that corresponds to the CLIENT_ID column of the ORDER table contains null values for rows that need to be displayed. Which type of join should you use to display the data?

Outer join

Section 16 Programming

TESTING

All systems needs rigorous testing before they are delivered to end users. True or False?

True

Testing is done by programmers. True or False?

True

You need not worry about Contraints on tables when testing. True or False?

False

What kind of transactions should you test against your tables and views?

INSERT, UPDATE, DELETE, MERGE

sumur : http://quizoracle.blogspot.co.id/2010/11/section-16-programming.html

gus rifa

ora ono illah kang hak den sembah kejobo Alloh

Rabu, 21 Oktober 2015