| Terms |
Definitions |
|
Group functions _____ NULL values.
|
Ignore
|
|
D
|
Which of the following statements about granting object privileges is false?A) To grant privileges on an object, the object must be in your own schema, or you must have been granted the object privileges WITH GRANT OPTION.B)An object owner can grant any object privilege on the object to any other user or role of the database.C) The owner of an object automatically acquires all object privileges on that object.D)Object privileges can only be granted through roles.
|
|
Clauses are usually placed on separate line for ease of use and readability. True?
|
Yes
|
|
What is the escape character ?
|
/
|
|
INITCAP ___________.
|
Converts alpha character values to uppoercase for the first letter of each word.
|
|
What creates subtotals that roll up from the most detailed level to a grand total, following a grouping list specified in the rollup clause?
|
rollup
|
|
Clauses are usually placed on separate line for ease of use and readability. True?
|
Yes
|
|
Describe Startup NOMOUNT?
|
1-Parameter file is read
2-Memory Structure and background processes are initiated.
|
|
Define Table Spaces?
|
Tablespaces physically group schema objects for admin convenience.
They bridge physical structures such as datafiles or extents and logical structures such as tables and indexes.
They can store 0 or more segments.
|
|
NoCache
|
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? A) NoCacheB) CacheC) Maxvalue
|
|
True
|
REGULAR EXPRESSIONS can be used as a part of contraint definitions? (True or False)
|
|
You (can/cannot) use a column alias in a group by clause.
|
cannot
|
|
Group functions cannot be used in the ____ clause.
|
WHERE
|
|
Referential Integrity Constraints
|
Foreign Key Constraints are also known as: Parental Key Constraints Child Key Constraints Referential Integrity Constraints Multi-Table Constraints
|
|
Are indents a recommended way to make code more readable?
|
Yes
|
|
SQL Statements can be entered on more than one line. True?
|
Yes
|
|
Which four are attributes of single row functions? (Choose four.)
A. accept only one argument and return only one value
B. cannot be nested
C. manipulate data items
D. act on each row returned
E. return one result per row
F. accept arguments which can be
|
Answer: C,D,E,F
Explanation:
manipulate data items, act on each row returned, return one result per row, and accept arguments
that can be a column or expression.
Incorrect answer :
A is not single row attributes
* functions can accept more than one argument, e.g NVL2
|
|
What command line utility should be used to start AND stop isqlplus?
|
/bin>isqlplusctl start
/bin>isqlplusctl stop
|
|
False
|
When you drop a view, the data it contains is also deleted. True or False?
|
|
Select statements do what?
|
They extract data from the database.
|
|
There are four arithmetic operators for SQL, what are they?
|
+ - * /
|
|
Evaluate this SQL statement:
SELECT e.EMPLOYEE_ID,e.LAST_NAME,e.DEPARTMENT_ID, d.DEPARTMENT_NAME
FROM EMPLOYEES e, DEPARTMENTS d
WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID;
In the statement, which capabilities of a SELECT statement are performed?
A. selecti
|
Answer: A
Selection, projection and join capabilities of a SELECT statement are performed in this view.
|
|
What are the sections included in Template Definitions?
|
1-Common Options
2-Innitialization Parameters
3-Character Sets
4-Control Files
5-Tablespaces
6-Data Files
7-Redo Log Groups
|
|
What new Oracle 10g feature provides a centralized location to maintain and manage all the files related to database backups?
|
Flash Recovery
|
|
Describe local extent management?
|
With Local Extent management database tracks extents through bitmaps.
This is the default and preferred technique.
|
|
A Cartesian Product is the same as the ANSI _____.
|
Cross Join
|
|
How many categories are they for SQL*Plus commands and what are they?
|
7Enviroment, Format, File Manipulation, Execution, Edit, Interaction, Miscellaneous
|
|
What is iSQL*plus?
|
It is a browser environment where SQL commands execute. (different than SQL*Plus)
|
|
Which SQL statement returns a numeric value?
A. SELECT ADD_MONTHS(MAX(hire_Date), 6) FROM EMP;
B. SELECT sysdate-hire_date
FROM EMP;
C. SELECT TO_NUMBER(hire_date + 7)
FROM EMP;
D. SELECT ROUND(hire_date)
FROM EMP;
|
Answer: B
Explanation:
DATE value subtract DATE value will return numeric value.
|
|
Which SELECT statement will get the result 'elloworld' from the string 'HelloWorld'?
A. SELECT SUBSTR( 'HelloWorld',1) FROM dual;
B. SELECT INITCAP(TRIM ('HelloWorld', 1,1)) FROM dual;
C. SELECT LOWER(TRIM ('H' FROM 'HelloWorld')) FROM dual;
D. SELECT LOW
|
Answer: C
Explanation:
This statement will return correct result because function TRIM() will trim letter 'H' in the 'Hello
World' and function LOWER() will return data in string in lowercase..
|
|
What are the commands for Oracle DB Startup?
|
1-STARTUP NOMOUNT
2-STARTUP MOUNT
3-STARTUP OPEN
4-STARTUP FORCE
5-STARTUP RESTRICT
|
|
What does a non-seed template contains?
|
Non Seed templates only contain
Database information.
|
|
What types of segment space management can be used in local extent management?
|
1-Manual(PCT_FREE and PCT_USED paramenters are used to find out whether a datablock is available for use)
2-Automatic(Bitmaps are used to identify free blocks)
SQL> CREATE TABLESPACE HIST DATAFILE 'C:\ORADATA\HIST.DBF' SIZE 25G EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENNT SPACE MANAGEMENT AUTO;
|
|
What Commands do you use for creating a table space?
|
CREATE DATABASE
CREATE TABLESPACE
choices are:
bigfile|smallfile
EXTENT MANAGEMENT = LOCAL|DATADICTIONARY
SEGMENT SPACE MANAGEMENT =AUTOMATIC|MANUAL
|
|
To provide restricted data access.
|
What is one advantage of using views?
|
|
What is a Literal character string?
|
Literal strings are free-format text that can be included in the query result and are treated the same as a column in the select list. Data and character literals need to be enclosed by single quotation marks.
|
|
SQL can not be abbreviated, can SQL*Plus be abbreviated?
|
Yes, SQL*Plus can be abbreviated
|
|
Which SQL statement generates the alias Annual Salary for the calculated column SALARY*12?
A. SELECT ename, salary*12 'Annual Salary' FROM employees;
B. SELECT ename, salary*12 AS INITCAP("ANNUAL SALARY") FROM employees
C. SELECT ename, salary*1
|
Answer: C
This SQL statement provides correct syntax to generate the alias Annual Salary for the calculated
column SALARY*12.
|
|
Which SELECT statement should you use to extract the year from the system date and display it
in the format "1998"?
A. SELECT TO_CHAR(SUBSTR(SYSDATE, 8,2),'yyyy')
FROM dual;
B. SELECT DECODE(SUBSTR(SYSDATE, 8), 'year')
FROM dual;
C. SELECT TO_DA
|
Answer: D
Function TO_CHAR(x, y) converts the value x to a character or converts a date to a character
string using formatting conventions.
|
|
What does .djf extension files contain?
|
These are the associated predefined redo logs and data files.
|
|
Why does db need undo tablespace?
|
Undo table space contains undo segments which are used for:
1- Rolling back a transaction explicitly with a ROLLBACK statement.
2- Rolling back a transaction implicitly(recovery of the failed transaction)
3-Reconstructing a read consistent image of data.
4- Recovering from logical corruption.
|
|
What operations can be performed on Data Files?
|
1-Resizing
2-Taking them offline or online
3-Moving (Renaming)
4-Recovering
SQL>ALTER DATABASE DATAFILE 'C:\ORADATA\DATA01.DBF' RESIZE 100M;
SQL>ALTER DATABASE DATAFILE 'C:\ORADATA\DATA01.DBF' AUTOEXTEND ON 100M MAXSIZE 8000M;
SQL>ALTER DATABASE DATAFILE 'C:\ORADATA\DATA01.DBF' OFFLINE/ONLINE;
SQL>RECOVER DATAFILE 'C:\ORADATA\DATA01.DBF';
SQL>ALTER DATABASE DATAFILE RENAME 'C:\ORADATA\DATA01.DBF' 'C:\ORADATA\DATA01A.DBF'
|
|
Single row functions can be used in the __________ clauses.
|
SELECT, WHERE, ORDER BY, DECODE
|
|
What are default settings for Column Headings for default heading at the top, columns with characters, columns begining with numbers?
|
Default headings are centered and uppercase. Characters are left justified and numbers are right justified.
|
|
Are character searches using a WHERE clause case sensitive?
|
YES therefore 'BALL', 'BAll', 'ball' are all different searches.
|
|
Examine the description of the STUDENTS table:
STD_ID NUMBER(4)
COURSE_ID VARCHAR2(10)
START_DATE DATE
END_DATE DATE
Which two aggregate functions are valid on the START_DATE column? (Choose two.)
A. MIN(start_date)
B. SUM(start_date)
C. COUNT(start_date)
|
Answer: A,C
It is possible to apply COUNT() and MIN() functions on the column with DATE data type.
|
|
The EMP table contains these columns:
LAST_NAME VARCHAR2 (25)
SALARY NUMBER (6,2)
DEPARTMENT_ID NUMBER (6)
You need to display the employees who have not been assigned to any department. You write the
SELECT statement:
SELECT LAST_NAME, SALARY, DEPARTMENT
|
Answer: B
The operator in the WHERE clause should be changed to display the desired results. There are
times when you want to substitute a value in place of NULL. Oracle provides this functionality with
a special function, called NVL(). You cannot use operation equal with NULL, but you can achieve
desired results using NVL() function after the WHERE clause.
|
|
What is Oracle Management Agent?
|
Its a background process that runs on each managed target server.
|
|
Why do we need to take tablespaces offline?
|
For maintenance
1- Recovering a tablespace
2- Moving the datafiles to a new location.
SQL>ALTER TABLESPACE FIN OFFLINE/ONLINE;
|
|
An easy way to remember NVL2 is________.
|
If expression 1 has a value, substitute expression 2; if expression 1 is null, substitute expression 3.
|
|
What do Logical conditions like AND, OR, NOT allow you to do in a WHERE clause?
|
It allows one to combine several conditions.
|
|
Can wild cards and escape characters be combined?
|
Yes
WHERE lastname LIKE '_o%'
this clause searches for all names where the second character is an O.
|
|
What is necessary for your query on an existing view to execute successfully?
A. The underlying tables must be in the same schema.
B. You need SELECT privileges only on the underlying tables.
C. The underlying tables must have data.
D. You need SELECT pri
|
Answer: D
Explanation:
To query an existing view you need to have SELECT privileges on the view.
|
|
In a nonequijoin there is no ______.
|
exact match between the two columns in each table.
|
|
Where does the WHERE keyword fit into a statement?
|
The WHERE keyword forms a clause after the FROM clause.
|
|
What is the use of a from statement?
|
The FROM keyword can follow the SELECT keyword to tell from which table to pull data.ex. FROM employees;
|
|
When using a SELECT statement what does it mean for projection of data?
|
Protection uses the columns in a table to return data for a query.
|
|
You own a table called EMPLOYEES with this table structure:
EMPLOYEE_ID NUMBER Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
HIRE_DATE DATE
What happens when you execute this DELETE statement?
DELETE employees;
A. You get an error because of
|
Answer: D
You can remove existing rows from a table by using the DELETE statement.
DELETE [FROM] table
[WHERE condition];
|
|
Describe Oracle Enterprise Manager 10g Database Control?
|
This is a web based component of the Enterprise Management Framework wich allows to monitor and administer a single Oracle database instance or a real application cluster (RAC)
|
|
SQL does not have a continuation character, does SQL*Plus have a continuation character?
|
Yes. A dash (-) used to continue a line that is longer than one line.
|
|
When using a SELECT statement to quert columns, what is a way to specify order of the columns?
|
Specify the columns that you want in the order that you want then to appear in the output.
|
|
START WITH in a hierarchical query is used to _______.
|
tell Oracle which row to use as the starting point.
|
|
What is the statement used in SQL to extract data from the database?
|
use the SELECT statement from SQL in the iSQL*Plus environment.
|
|
Please explain the syntax of a select statement.
SELECT
*
DISTINCT
column/expression
alias
FROM table
|
SELECT is a list of one or more columns
DISTINCT suppress duplicates
column/expression selects the namd column or the expression
alias gives select cloumns different headings
FROM table specifies the table containng the columns
|
|
How can the NUL condition be used with WHERE?
|
IS NULL and IS NOT NULL seaches for values that are unavailible, unassigned, unknown, or inapplicable.
|
