|
SQL Fundamental Exam Answers
NOTE:
The answers go with their sequences. If a question was not answer, that means
that it a repeating question and the answer was given by the previous questions
or it is not in the scope of this subject.
|
“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
|
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.
-
The last name and first name
should be not null.
-
Make a check constraint to check
the department number is between 9 and 100.
-
Make a primary constraint on the
employee ID column.
-
Make a foreign key on the
department number column.
-
Use the "delete
cascade" to delete all records if parent gets deleted.
-
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
synonym 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:\myscript.sql
Q: How do you open the SQL Script into the Oracle Buffer?
A: SQL> get c:\myscript.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:\myscript.sql
or start c:\myscript
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:\salary.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.
-
The last name and first name
should be not null.
-
Make a check constraint to check
the department number is between 9 and 100.
-
Make a primary constraint on the
employee ID column.
-
Make a foreign key on the
department number column.
-
Use the "delete
cascade" to delete all records if parent gets deleted.
-
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.
|