|
“Nothing in the
world can take the place of persistence. Talent will not;
nothing is more common than unsuccessful men with talent. Genius
will not; unrewarded genius is almost a proverb. Educations will
not the world is full of educated derelicts. Persistence and
determination alone are omnipotent. The slogan, 'Press on,' has
solved and always will solve the problems of the human race.”
--Calvin Coolidge |
Answers:
Q: What are the definitions
of the following items?
column,
record,
table,
item,
field,
element,
primary key,
foreign key, and
datatype.
A: A column
is a smallest unit in a database that you need to deal with. A record
is a collection of columns and a table is a collection of
records. The terms: elements, columns, fields and
items can be used interchangeably. A primary key is a
unique identifier in a table. A foreign key is a column in a
table (child) that references to a primary key column in
another table (parent). Relationships between two tables are normally
established by defining primary or foreign keys. A datatype is
a format that an input data will be stored in a column.
Q: What is the relationship
between primary and foreign keys?
A: Relationships
between two tables are normally established by defining primary or
foreign keys. It will establish a child and parent relationships. A foreign
key is a column in a table (child) that references to a primary
key column in another table (parent).
Q: Describe the Entity
Relationship diagram and Logical Data Model.
A: "Entity
Relationship Diagram" or "Logical Data Model" is used
to establish relationships between entities.
Q: What is a composite
index?
A: If
an index key or a primary key were composed of more than one column.
We call it a composite index.
Q: What are the
responsibilities of an Oracle DBA and Oracle Developer?
A: The
integrity, security, connectivity, performance, and tuning of a
database will be maintained by DBAs. One of the responsibilities of a
DBA is to plan a contingency for disaster and ensure recovery of the
database. On the other hand developers use front-end and back-end
tools along with management tools to perform their tasks. They develop
applications to manipulate a database’s data. Their application will
query, insert, delete and update a record or records. They use
front-end tools such as "form builder," "report
builder," and "graphics builder." They use back-end
tools such as "schema builder," "procedure
builder," and "query builder." They use project builder
tools to manage and deliver their applications to their clients.
Q: What is a Database?
A: A
collection of all tables under a single or many different schemas can
be stored and maintained in a database. A database, in effect, is a
collection of objects such as tables, indexes, stored procedures, etc.
Q: Query the employee names
and their salaries from the employee table.
A: SQL>
SELECT ename, sal FROM emp;
Q: Do the above query and
use an “as” clause for the “salary” column aliases or column
headings.
A: SQL>
SELECT ename, sal AS salary
FROM emp;
Q: Repeat the previous
query and have “Full Name” for the ename’s column heading and
“Salary” for the “sal” column heading.
A: SQL>
SELECT ename “Full Name”, sal "Salary"
FROM emp;
Q: What is the result of
100 + NULL?
A: NULL.
Q: Query the employee names
with their commissions.
A: SQL>
SELECT ename, comm commission FROM
emp;
Q: Use the (NVL) the null
value function to assign zero to any null value in the commission
column for the previous query.
A: SQL>
SELECT ename, NVL(comm,0)
commission
FROM emp;
Q: Concatenate the
customers’ last name and first name separated by comma.
A: SQL>
SELECT last_name || ', ' ||
first_name AS "full name"
FROM customers;
Q: Query the employees name
sorted by ascending order.
A: SQL>
SELECT ename
FROM emp
ORDER
BY ename ASC;
Q: Query the employees name
sorted by descending order.
A: SQL>
SELECT ename FROM emp
ORDER
BY ename DESC;
Q: Query the employee
information whose employee number is 7788.
A: SQL>
SELECT *
FROM emp
WHERE
empno = 7788;
Q: Query the employees name
whose names start with the letter “M.”
A: SQL>
SELECT ename
FROM emp
WHERE ename LIKE
'M%';
Q: Query the employees name
whose names end with the letter “R.”
A: SQL>
SELECT ename
FROM emp
WHERE ename LIKE
'%R';
Q: Query the employees name
whose salaries between 2000 and 3000 dollars.
A: SQL>
SELECT ename
FROM emp
WHERE sal BETWEEN
2000 AND 3000;
Q: Query the employees name
and their department name using the “DECODE” function. If the
department number is 10 then print "accounting.” If the
department number is 20 then print "research," or if the
department number is 30 then print "sales." Anything else
prints others.
A: SQL>
SELECT ename, DECODE (deptno, 10,
'Accounting',
20, 'Research',
30, 'Sales',
'Others') AS
"Department"
FROM emp;
Q: What is an ambiguous
column?
A: An
ambiguous column is a column that is not defined clearly. Having two
tables with the same column name, you should reference them such that
there is no ambiguity on their ownerships.
Q: How can you resolve an
ambiguous column problem?
A: The
column name should be identified by alias to make it clear that to
what table that column is belong.
Q: What is a Cartesian
product?
A: A
“Cartesian” product is caused by joining “N” number of tables
while you have less than “N-1” joins condition in the query.
Q: How can you avoid a
Cartesian product?
A: To
avoid it, just when joining “N” number of tables you should have
more or equal “N-1” joins condition in the query.
Q: What is an inner join or
equi-join?
A: Joining
two or more tables together using the WHERE clause with the equal sign
(=) in a query. This type of query will retrieve records that have
exact match and will be called inner join or equi-join.
Q: What is an outer join?
A: Joining
two or more tables using OUTER join, not only you retrieve all
matching records but also you retrieve the records that do not match.
Q: What is a self join?
A: When
a table refers to itself in the WHERE clause, we call that join is a
self-join.
Q: Query all the employee
names and their department including all the departments with no
employees.
A: SQL>
SELECT ename, dname
FROM emp e, dept d
WHERE e.deptno (+)
= d.deptno;
Q: Query the managers’
name with their employees sorted by the manager name.
A: SQL>
SELECT mgr.ename “Manager Name”, e.ename “Employee Name”
FROM emp
mgr, emp e
WHERE mgr.empno = e.mgr
ORDER BY mgr.ename;
Q: Query the department
number and their total, average, min, and max salaries for each
department.
A: SQL>
SELECT deptno, SUM(sal), AVG(sal),
MIN(sal), MAX(sal)
FROM emp
GROUP BY deptno;
Q: Query the department no
and their total salaries that have more than 5 employees working in
their department.
A: SQL>
SELECT deptno, SUM(sal)
FROM emp
GROUP
BY deptno
HAVING count(*) > 5;
Q: Query the employees name
that work for the Research or Sales department (the department number
20 or 30).
A: SQL>
SELECT ename, deptno
FROM emp
WHERE deptno IN
(20, 30);
Q: Query the employees name
that work in the "accounting" department. Assuming the
department number is unknown.
A: SQL>
SELECT ename
FROM emp
WHERE deptno IN
(SELECT
deptno
FROM dept
WHERE dname =
"ACCOUNTING");
Q: Query the employees name
and use the runtime variable to substitute the department number? Then
run it for following department no 10, 20, and 30.
A: SQL>
SELECT ename
FROM emp
WHERE deptno = &deptno;
SQL> /
Q: Query the customer names
which have more than four orders.
A: SQL>
SELECT name
FROM customer c
WHERE exists
(SELECT 'T'
FROM ord
WHERE custid = c.custid
GROUP BY custid
HAVING count(*) > 4);
Q: Create an employee table
that contains five columns:
Such as Employee Id, last
name, First name, Phone number and Department number with the
following constraints.
1. The last name and
first name should be not null.
2. Make a check
constraint to check the department number is between 9 and 100.
3. Make a primary
constraint on the employee ID column.
4. Make a foreign key
on the department number column.
5. Use the "delete
cascade" to delete all records if parent gets deleted.
6. Use the "phone
number" as a unique key.
A: SQL>
CREATE TABLE employee
(empid NUMBER(10),
lastname VARCHAR2(20)
not null,
firstname VARCHAR2 (20)
not null,
phone_no VARCHAR2 (15),
deptno NUMBER(2) CHECK
(deptno BETWEEN 9 AND 100),
constraint pk_employee_01 PRIMARY
KEY (empid),
constraint fk_dept_01 FOREIGN
KEY (deptno)
references dept (deptno) ON
DELETE CASCADE,
constraint uk_employee_01 UNQUE
(phone_no));
Q: Create a composite index
on the employee table that contains two index columns (last name and
first name).
A: SQL>
CREATE INDEX employee_lname_fname_ind_01
ON employee (lastname,
firstname);
Q: Query the tables that
you as a user own.
A: SQL>
SELECT table_name
FROM user_tables
ORDER BY table_name;
Q: Query the index tables
that belong to the employee table and owns by the iself user.
A: SQL>
SELECT index_name, uniqueness
FROM user_indexes
WHERE table_name =
'EMPLOYEE';
Q: Change the size of the
"column_name" to 30 characters logically (for display only).
A: SQL>
COLUMN column_name FORMAT a30
Q: Query the indexes
columns of the employee table.
A: SQL>
SELECT index_name, column_name, column_position
FROM user_ind_columns
WHERE table_name =
'EMPLOYEE';
Q: Insert a record into the
"employee" table using column names.
A: SQL>
INSERT INTO employee
(empid, lastname, deptno,
firstname, phone_no)
VALUES (100, 'smith', 10,'joe',
‘7038212211');
Q: Insert a record using
the column position format.
A: SQL>
INSERT INTO employee
VALUES (200,
'KING', 'Allen', 5464327532, 10);
Q: How do you save the
inserted transaction?
A: COMMIT;
Q: Change the "last_name"
column value from “Smith” to “Judd” where the "employee
id" is 100.
A: SQL>
UPDATE employee
SET lastname = 'Judd'
WHERE empid = 100;
Q: Delete all the employee
records from the "employee" table using the delete command
and the truncate command.
A: SQL>
DELETE FROM employee;
OR
SQL> TRUNCATE TABLE
employee;
Q: How do you undo a
transaction?
A: ROLLBACK;
Q: What is the difference
between the delete statement and the truncate statement?
A: Notice
that the TRUNCATE command is a DDL statement and all DDL statements
have commit inclusive. That is why the ROLLBACK action after
truncation does not work. Also, if you want to delete all records from
a table, you should use the TRUNCATE statement. It will change the
table watermark. The table watermark is an address that indicates a
last location of a record in a table. On the DELETE statement the
watermark will not change. But using the TRUNCATE statement will
change the watermark to the beginning of the table.
Q: Copy the “EMP” table
to another table and name the new table "employee." In the
new employee table use the employee name, job, commission and
department number.
A: SQL>
CREATE TABLE employee
AS
SELECT ename, job, comm, deptno
FROM emp;
Q: Add a salary column to
the employee table.
A: SQL>
ALTER TABLE employee
ADD (salary NUMBER(8,2));
Q: Modify the "ename"
column size from varchar10 to varchar15.
A: SQL>
ALTER TABLE employee
MODIFY (ename
VARCHAR2(15));
Q: Rename the
"employee" table to the "iself_employee" table.
A: SQL>
RENAME employee TO iself_employee;
Q: Create a view to display
the employee names of the “Accounting” department only.
A: SQL>
CREATE VIEW employee_name
AS SELECT ename
FROM iself_employee
WHERE deptno = 10;
Q: Why do you use the view?
A: You
use view to present rows and columns of a table in the way you want.
You may use it for security reason. For example, you may eliminate
some rows and columns that are very sensitive information. These
changes are transparent to a user.
Q: How do you compile the
view?
A: SQL>
ALTER VIEW employee_name COMPILE;
Q: How do you delete the
view?
A: SQL>
DROP VIEW employee_name;
Q: Create an index on the
employee table on the ename column only and name it employee_indx.
A: SQL>
CREATE INDEX employee_indx
ON employee (ename);
Q: Reorganize the “employee_indx”
index table.
A: SQL>
ALTER INDEX employee_ indx REBUILD;
Q: Drop the employee_ename
index table.
A: SQL>
DROP INDEX employee_indx;
Q: Create a user with
username “newuser” and password "newpass." Its default
tablespace should be the "iself_data" tablespace.
A: SQL>
CREATE USER newuser IDENTIFIED BY by newpass
DEFAULT TABLESPACE
iself_data;
Q: Grant the resource and
connect roles to newuser.
A: SQL>
GRANT resource, connect TO newuser;
Q: Change the newuser
password to "mypass".
A: SQL>
ALTER USER newuser IDENTIFIED BY mypass;
Q: Can the above new user
access to any other user tables?
A: No.
Q: What is a public
synonym?
A: It
is a synonym that public users can use. We create public synonym so
that the users don’t need to type schema name to a table when they
query the table. Creating a public synonym does not mean that oracle
users can access to that table or object. Still the owner of the
object has to grant access to a user on its table.
Q: What is the syntax to
create a public synonym?
A: SQL>
CREATE PUBLIC SYNONYM employees FOR iself.iself_employee;
Q: What is the difference
between public and private synonym?
A: The
private synonym is only for the user who owns or created the synonym,
but the public can be used by every users.
Q: Create and drop a
private synonym.
A: SQL>
CREATE SYNONYM emp_table FOR iself.iself_employee;
To drop:
SQL>
DROP SYNONYM emp_table;
Q: Revoke an object
privilege on a table from a user.
A: SQL>
REVOKE UPDATE, SELECT ON employee FROM newuser;
Q: What does the LIST or
‘L’ command line editor?
A: It
lists the current SQL statement that was typed in the Oracle buffer.
Q: What does the INSERT or
‘I’ command line editor?
A: It
inserts a command in the Oracle buffer after the current active line
that was indicated with an *.
Q: What does the DEL or ‘D’
command line editor?
A: It
deletes the current active line in the Oracle Buffer.
Q: How do you change a
string in the Oracle Buffer?
A: First,
mark the line as a current active line and then type the‘del’
command.
Q: How do you save the SQL
script in the Oracle Buffer?
A: SQL>
save c:.sql
Q: How do you open the SQL
Script into the Oracle Buffer?
A: SQL>
get c:.sql
Q: How do you use the
notepad editor?
A: Just
type: the ed command to open the default editor.
Q: What is afiedt.buf?
A: The
"afiedt.buf" file is a place that into which SQL*PLUS stores
the most recently executed SQL statement.
Q: How do you change your
text editor in the SQLPLUS tool?
A: Issue
the define_editor='your editor' statement from the SQL*PLUS prompt.
Q: What does the ed command
in the SQLPLUS tool?
A: We
use the "ed" command, to open your default word editor.
Q: Can you have multiple
SQL statements in the afiedt.buf file?
A: No.
You can only use one SQL statement at a time.
Q: How do you use the
notepad editor as an independent tool in the SQLPLUS utility?
A: Just
open your notepad editor outside of your SQLPLUS.
Q: How do you execute or
run a SQL script?
A: SQL>
run c:.sql or start c:
Q: What is the SQL ANSI
statement?
A: It
is some standard roles that provided by American
National Standards Institute.
Q: What is the difference
between the SQL ANSI statement and Original Oracle statement?
A: The
Original Oracle statements are not follow the role of American
National Standards Institute.
Q: Is the SET command a SQL
statement?
A: No.
Q: How do you change your
workstation’s page size or line size?
A: SQL>
SET LINESIZE 100 PAGESIZE 55
Q: What does the JOIN
syntax in the Oracle SQL (DML) statement?
A: It
does innor join using the ON clause.
SQL> SELECT ename,
dept.deptno, dname
FROM emp JOIN dept
ON emp.deptno = dept.deptno
AND dname <> 'SALES'
/
Q: What is the difference
between the JOIN syntax and the NATURAL JOIN syntax?
A: In
the NATURAL JOIN syntax, you don't need the ON clause if the column’s
names are the same.
Q: What does the USING
clause in the Oracle SQL statement?
A: It
joins two tables and in the USING clause the join column names must be
the same.
Q: What is the advantage of
the NATURAL JOIN syntax?
A: It
is less typing.
Q: What does the CROSS JOIN
syntax in the Oracle SQL statement?
A: We
can use the Oracle9i ANSI standard CROSS JOIN syntax with no WHERE
clause to create a Cartesian product.
Q: What does the IN clause
in the Oracle SQL statement?
A: The
IN clause in the Oracle SQL statement is an equivalent of the OR
condition in the SQL statement.
Q: What do the OUTER JOIN,
RIGHT OUTER JOIN, LEFT OUTER JOIN, and FULL OUTER JOIN syntax in the
Oracle SQL statement?
A: We
use the OUTER option when we want all records that have exact match
plus those records that have no match.
Q: How can you perform the
FULL OUTER JOIN syntax using the Original Oracle syntax?
A: Although
it is possible but it is very difficult to perform the full outer join
using the original Oracle syntax.
Q: When do you use the WITH
… AS clause in the SQL statement?
A: If
we have a query which it needs to process the same sub-query several
times, we should consider using the WITH …AS clause in our
statement.
Q: How does the WITH … AS
clause help your performance?
A: The
query will create a temporary table to query it over and over.
Q: Write a query to list
all the department names that their total paid salaries are more than
1/3 of the total salary of the company.
A: SQL>
WITH summary_totals AS
(SELECT dname,
SUM(sal) AS totals
FROM emp NATURAL JOIN dept
GROUP BY dname)
SELECT dname, totals
FROM summary_totals
WHERE totals > (SELECT SUM(totals)*1/3
FROM summary_totals)
ORDER BY totals DESC
SQL>/
Q: What are the multiple
columns in the SQL statement? Where or how do you use them?
A: We
use multiple columns to match the multiple columns returned from the
sub-query.
Q: Write a SQL statement to
query the name of all employees who earn the maximum salary in their
department using the multiple columns syntax.
A: SQL>
SELECT deptno, ename, job, sal
FROM emp
WHERE (deptno, sal) IN
(SELECT deptno, MAX(sal)
FROM emp
GROUP BY deptno)
/
Q: What is the inline view
in the Oracle SQL statement?
A: If
we have a sub-query in a FROM clause in the Oracle SQL statement, is
called an inline view.
Q: Write a SQL statement to
query all of the employee names, jobs, and salaries where their salary
is more than 10% of the total company paid salary.
A: SQL>
SELECT ename, job, sal
FROM (SELECT ename, job, sal
FROM emp
WHERE sal > (SELECT SUM(sal) * .1
FROM emp)
ORDER BY 3)
/
Q: What does the MERGE
statement in the SQL statement?
A: We
use the MERGE statement to merge one table into another table.
Q: Can you update, insert,
or delete any records while you are using the MERGE statement?
A: Yes.
Q: What is a Materialized
View?
A: A
materialized view (MVIEW) is a replica of a target master from a
single point in time.
Q: What are the
Materialized View types?
A: Read-Only
Materialized Views
Updatable Materialized
Views
Sub-query Materialized
Views
Row-id vs. Primary Key
Materialized Views
Q: Write the difference
between ROWID and PRIMARY KEY in the Materialized View.
A: Fast
refresh requires association between rows at snapshot and master
sites. Snapshots that use ROWIDs to refresh are called ROWID snapshots
while those that use primary keys are called primary key snapshots.
Q: What is the difference
between a Materialized View and View?
A: A
Materialized View is a physical duplicated data in a table, but a View
is just a logical presentation of a table.
Q: When or why do you use a
Materialized View?
A: You
use Materialized Views to create summaries in a data warehouse
environment or replicate a data in a distributed environment. In data
warehouses, you can use materialized views to pre-compute and store
aggregated data such as the sum of sales. In distributed environments,
you can use materialized views to replicate data from a master site to
other distributed sites.
Q: What is a materialized
view log?
A: A
materialized view log is a holder that contains updated, inserted, or
deleted records’ information in the primary table.
Q: What are the PRIMARY KEY
and ROWID in the Materialized View Log?
A: The
Materialized View log that use ROWIDs to refresh are called ROWID view
log while those that use primary keys are called primary key view log.
Q: What does the
USER_SNAPSHOT_LOGS view contain?
A: It
shows if our log was created successfully and its name (MLOG$_EMP).
Q: Create a materialized
view that contains the department number, number of employees, and
total salaries paid to employees by department.
A: SQL>
CREATE MATERIALIZED VIEW mv_sal
BUILD IMMEDIATE
REFRESH ON DEMAND
AS SELECT deptno,
COUNT(1) AS no_of_emp,
SUM(sal) AS salary
FROM emp
GROUP BY deptno
SQL> /
Q: Who can create a
materialized view?
A: The
one that was granted the CREATE MATERIALIZED VIEW privilege.
Q: What does the
USER_MVIEWS view contain?
A: It
contains all the Materialized Views’ information that were created
by the user.
Q: How do you refresh a
materialized view?
A: SQL>
EXECUTE dbms_snapshot.refresh('mv_sal','C');
Q: What parameter should be
used to update the materialized view every month automatically without
human intervention?
A: The
START WITH SYSDATE option will create an immediate data, and the
NEXT(SYSDATE+30) option will update the table every 30 days.
Q: What does the USER_JOBS
view contain?
A: It
contains all users’ jobs in the Oracle queue.
Q: How do you remove a job
from the Oracle Job Queue?
A: SQL>
EXECUTE dbms_job.remove(job_number);
Q: How do you drop a
materialized view log and a materialized view?
A: SQL>
DROP MATERIALIZED VIEW LOG ON emp;
To drop it:
SQL> DROP MATERIALIZED
VIEW mv_sal;
Q: What does the BREAK ON
clause in SQLPLUS?
A: It
builds a break on a column.
Q: What do the REPHEADER
and REPFOOTER commands in SQLPLUS?
A: They
make a report header and footer.
Q: What does the following
commands?
COLUMN sal HEADING 'Salary'
FORMAT $99,999.99 --Creates heading format.
COLUMN ename HEADING
'Employee' FORMAT a20 - Creates heading format.
REPHEADER ''
- Creates report heading.
BREAK ON dname SKIP 1
- Creates control bread on a column and skip 1 line after the break.
COMPUTE SUM OF sal ON dname
- Computes total salary within a department.
SPOOL c:.out
-- Activates spooling.
SPOOL OFF
-- Deactivate spooling.
REPFOOTER ''
- Creates report footer.
CLEAR BUFFER
-- Clear the Oracle buffer.
CLEAR COLUMNS
- Clears columns.
CLEAR COMPUTE
-- Clears compute functions.
Q: What does the CLEAR
command in SQLPLUS?
A: Note
that all the values in REPHEADER, REPFOOTER, BUFFER, COLUMNS, COMPUTE
and etc are going to stay the same during your open session. In order
to clean them, you should use the CLEAR command for BUFFER, COLUMNS,
and COMPUTE. And input NULL to REPHEADER and REPFOOTER.
Q: What does the UNION
statement in the SQL statement?
A: It
will query all the records that match or not match with the base
table.
Q: What does the INTERSET
statement in the SQL statement?
A: It
will query all the records that match with the base table. It is the
same as joining two tables.
Q: What does the MINUS
statement in the SQL statement?
A: It
will query all the records that are not matching against your base
table.
Q: Why it is important to
eliminate duplicate records?
A: To
keep your database integrity.
Q: What does the following
SQL statement?
SQL> DELETE FROM dup_emp
WHERE ROWID IN (SELECT MAX(ROWID)
FROM dup_emp
GROUP BY empno
HAVING COUNT (empno) > 1)
SQL> /
A: Deletes all the rows that have the same
employee number except the first one.
Q: What is a data
partitioning in the Oracle database?
A: The
data partitioning in the Oracle database is that the data will be
partitioned in multi-tablespaces for ease of maintenances.
Q: When should you use data
partitioning?
A: When
you have a huge data file and can be classified to some partitions.
Q: What is the advantage of
using a data partitioning?
A: It
is faster to access. It is easier to maintain.
Q: What is a partition key?
A: It
is used to separate data and associates them to their own assigned
tablespace.
Q: What is a local index in
the data partitioning?
A: A
Local index is one that is partitioned exactly like the table to which
it belongs.
Q: What is a global index
in the data partitioning?
A: A
Global index, unlike local indexes, you should explicitly partition
range boundaries using the “VALUE LESS THAN” methods.
Q: What are the differences
between local and global indexes?
A: In
the local index you don’t define explicitly partition range.
Q: How does the ‘VALUE
LESS THAN’ method work in the data partitioning?
A: The
VALUES LESS THAN clause indicates the partition key value must be less
then its assigned value in order to be illegible for any DML
transaction on its assigned tablespace.
Q: Why do you need multiple
tablespaces?
A: Multiple
tablespaces give us more flexibility to maintain a tablespace without
affecting any performance or downtime to others.
Q: Create a range-based
partitioning table named p_emp. Make sure that the data entry of the
each department goes to its own provided tablespaces such as the
accounting department goes to the dept10ts tablespace, the data entry
of the research department goes to the dept20ts tablespace, etc.
A: SQL>
CREATE TABLE p_emp (
empno NUMBER(4) PRIMARY KEY,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sale NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2))
STORAGE (INITIAL 5K
NEXT 5K
PCTINCREASE 0)
PARTITION BY RANGE (deptno)
(PARTITION dept10
VALUES LESS THAN (20)
TABLESPACE dept10ts,
PARTITION dept20
VALUES LESS THAN (30)
TABLESPACE dept20ts,
PARTITION dept30
VALUES LESS THAN (40)
TABLESPACE dept30ts,
PARTITION deptxx
VALUES LESS THAN (MAXVALUE)
TABLESPACE deptxxts)
SQL> /
Q: What does the MAXVALUE
parameter mean in the data partitioning?
A: It
means as large as the column can hold.
Q: How do you analyze a
partition table?
A: SQL>
ANALYZE TABLE p_emp COMPUTE STATISTICS;
Q: What does the
USER_TAB_PARTITIONS view contain?
A: A
user can query its partitions table’s information that was created
by the user.
Q: Write a query to list
the accounting employees from the partition table. Use the partition
option.
A: SQL>
SELECT * FROM p_emp PARTITION (dept10);
Q: Write a query to list
employee number 7900 from the sales department?
A: SQL>
SELECT * FROM p_emp PARTITION (dept30)
WHERE empno = 7900
SQL> /
Q: How do you create a
local partition index?
A: SQL>
CREATE INDEX p_emp_ind ON p_emp (deptno) LOCAL;
Q: How do you analyze a
partition table index?
A: SQL>
ANALYZE INDEX p_emp_ind COMPUTE STATISTICS;
Q: What does the
USER_IND_PARTITIONS view contain?
A: It
contains information in regard to the user’s partition indexes.
Q: What does the ROLLUP
operator?
A: The
ROLLUP operator returns both ‘regular rows’ and ‘super-aggregate
rows.’ Super-aggregate rows are rows that contain a sub-total value.
Q: What does the CUBE
function?
A: The
CUBE operator returns cross-tabulation values, thus produces totals in
all possible dimensions, and is used for warehousing aggregated data
reports.
Q: What are the differences
between the CUBE and ROLLUP functions?
A: See
the output…
Q: What environments may
use the CUBE and ROLLUP functions most?
A: Warehousing.
Q: Write a query to list an
aggregation sum report for each job, in each year, using the ROLLUP
grouping option.
A: SQL>
SELECT year, job, SUM(sal), COUNT(*)
FROM emp
GROUP BY ROLLUP (year, job)
SQL> /
Q: Write a query to list an
aggregation sum report for each job, in each year, using the CUBE
grouping option.
A: SQL>
SELECT year, job, SUM(sal), COUNT(*)
FROM emp
WHERE deptno = 20
GROUP BY CUBE (year, job)
SQL> /
Q: What is an object type?
A: The
object type in the Oracle database is like the class eliminate in the
C++ developer tool or any object oriented tool.
Q: What is a collection
object?
A: The
collection object in the Oracle database is like a nested table and a
variable array in a table.
Q: Create an object type
with two columns to hold the employee's child name and date of birth
and name it employee_kids .
A: SQL>
CREATE TYPE employee_kids AS OBJECT (
NAME VARCHAR2(30),
dob DATE
)
SQL> /
Q: Create a table type
using employee_kids and name it employee_kids_table.
A: SQL>
CREATE TYPE employee_kids_table
IS TABLE OF employee_kids;
Q: Create the emp_family
table containing the kid’s column with a type of employee_kids_table.
A: SQL>
CREATE TABLE emp_family
(empno NUMBER,
kids employee_kids_table)
NESTED TABLE kids STORE AS nested_employee_kids_table
SQL> /
Q: How do you insert a
record in the object type?
A: SQL>
INSERT INTO emp_family VALUES
(7902,
employee_kids_table
(employee_kids('David','08-AUG-01'),
employee_kids('Peter','10-JUN-88'),
employee_kids('Mark','30-OCT-92')
)
)
SQL> /
Q: What is the constructor?
A: The
constructor creates an empty nested table as opposed to leaving it
null. Notice that without using the constructor, it is not possible to
refer to the nested table with the "THE" clause.
Q: What is the ‘THE’
sub-query?
A: To
query a nested table you should use the "THE" clause. Also,
the "THE" sub-query is used to identify the nested table to
INSERT INTO.
Q: How do you query a
record using the ‘THE’ sub-query?
A: SQL>
SELECT name
FROM
THE(SELECT
kids FROM emp_family WHERE empno = 7788)
SQL> /
Q: What is a nested table?
A: It
is a table within a table.
Q: How do you insert a
record to a nested table?
A: SQL>
INSERT INTO
THE(SELECT
kids FROM emp_family
WHERE empno = 7900)
VALUES ('Sue','10-DEC-99');
Q: How do you update a
record to nested table?
A: SQL>
UPDATE emp_family
SET kids = employee_kids_table(
employee_kids('Sara','08-OCT-88'))
WHERE empno = 7788
SQL> /
Q: How do you add a unique
index to a nested table?
A: SQL>
CREATE UNIQUE INDEX i_nested_employee_kids_table
ON nested_employee_kids_table(nested_table_id,name)
SQL> /
Q: What is a data replica?
A: A
duplicated data in a different location.
Q: What is the difference
between a materialized view and a materialized view log?
A: The
Materialized view is a real duplicated data from a primary table but
the materialized view log is an on going logs generated due to the
table changes after the last refresh.
Q: What is an OID (Object
ID)?
A: It
is a unique ID assigned to an object by Oracle.
Q: How do you retrieve an
object ID?
A: SQL>
SELECT OWNER, TYPE_OID FROM DBA_TYPES
WHERE TYPE_NAME LIKE
'ADDRESS%';
Q: How do you use an object
ID to create an object type?
A: SQL>
CREATE OR REPLACE TYPE address_book_type_object
OID ‘XXXXXXXXXXXXXXXXXXXXX’
AS OBJECT (
id_address NUMBER(1),
address VARCHAR2(20));
Q: What is the relationship
between primary and foreign keys?
A:
The relationships between two tables are normally established by
defining primary or foreign keys. A primary key has the immutable
responsibility of serving as a unique identifier in a table. A foreign
key is a column that refers to the primary key of another table. To
join two tables, a “where clause” is used to set up a table
relationship between primary and foreign keys.
Q: What is a composite
index?
A: A
primary key can be composed of more than one column. We call it a
composite index.
Q: What is the result of
100 + NULL?
A:
NULL value.
Q: Write a query to
concatenate the customers’ last name and first name separated by
comma.
A:
SELECT last_name || ‘, ‘ || first_name
as “Full Name”
FROM customers
/
Q: Query the employees name
and their department name using the “DECODE” function. If the
department number is 10 then print "accounting.” If the
department number is 20 then print "research," or if the
department number is 30 then print "sales." Anything else
prints others.
A: SELECT
ename, DECODE (deptno, 10, 'Accounting',
20, 'Research',
30, 'Sales',
'Others') AS
"Department"
FROM emp
/
Q: Query the department
number and their total salaries that have more than 5 employees
working in their department.
A: SELECT
deptno, SUM(sal)
FROM emp
GROUP BY deptno
HAVING count(*) > 5
/
Q: query the customer names
which have more than four orders.
A: SELECT
name FROM customer c
WHERE exists (SELECT 'T'
FROM ord
WHERE custid = c.custid
GROUP BY custid
HAVING count(*) > 4)
/
Q: Create an employee table
that contains five columns:
Such as Employee Id, last
name, First name, Phone number and Department number with the
following constraints.
7. The last name and
first name should be not null.
8. Make a check
constraint to check the department number is between 9 and 100.
9. Make a primary
constraint on the employee ID column.
10. Make a foreign key
on the department number column.
11. Use the
"delete cascade" to delete all records if parent gets
deleted.
12. Use the "phone
number" as a unique key.
A: CREATE
TABLE employee
(empid NUMBER(10),
lastname VARCHAR2(20) not
null,
firstname VARCHAR2 (20) not
null,
phone_no VARCHAR2 (15),
deptno NUMBER(2) CHECK (deptno
BETWEEN 9 AND 100),
constraint pk_employee_01
PRIMARY KEY (empid),
constraint fk_dept_01
FOREIGN KEY (deptno)
references dept (deptno) ON
DELETE CASCADE,
constraint uk_employee_01
UNQUE (phone_no))
/
Q: What is the difference
between the delete statement and the truncate statement?
A: On
the DELETE statement the watermark will not change. But using the
TRUNCATE statement will change the watermark to the beginning of the
table.
Q: Copy the “EMP” table
to another table and name the new table "employee." In the
new employee table use the employee name, job, commission and
department number.
A: CREATE
TABLE employee
AS SELECT ename, job, comm,
deptno
FROM emp
/
Q: Reorganize the “employee_indx”
index table.
A: ALTER
INDEX employee_indx REBUILD
/
Q: What is the difference
between public and private synonym?
A: You
create synonym so that the users don’t need to type schema name to a
table when they query the table. The Public Synonym is available to
all database users but the Private Synonym is available only to the
owner of synonym.
Q: Can you have multiple
SQL statements in the afiedt.buf file?
A: No.
Q: How do you execute or
run a SQL script?
A: SQL>
@my_sql_script; or start
my_sql_script;
Q: Write a query to list
all the department names that their total paid salaries are more than
1/3 of the total salary of the company.
A: SQL>
WITH summary_totals AS
(SELECT dname,
SUM (sal) AS totals
FROM emp NATURAL JOIN dept
GROUP BY dname)
SELECT dname, totals
FROM summary_totals
WHERE totals > (SELECT SUM (totals)*1/3
FROM summary_totals)
ORDER BY totals DESC
SQL>/
Q: What is a Materialized
View?
A: A
materialized view (MVIEW) is a replica of a target master from a
single point in time. You use Materialized Views to create summaries
in a data warehouse environment or replicate a data in a distributed
environment. In data warehouses, you can use materialized views to
pre-compute and store aggregated data such as the sum of sales. In
distributed environments, you can use materialized views to replicate
data from a master site to other distributed sites.
Q: What does the following
SQL statement?
SQL> DELETE FROM dup_emp
WHERE ROWID IN (SELECT MAX(ROWID)
FROM dup_emp
GROUP BY empno
HAVING COUNT (empno) > 1)
SQL> /
A: Deletes duplicated records.
Q: What does the MAXVALUE
parameter mean in the data partitioning?
A: It
is the maximum possible value that can be store into a column.
Q: What does the following
SQL statement?
SQL> INSERT INTO
THE(SELECT kids FROM emp_family
WHERE empno = 7900)
VALUES ('Sue','10-DEC-99')
SQL> /
A: Inserts
a record to a nested object in a table.
|