SQL

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"

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

Q: Concatenate the customers� last name and first name separated by comma.

A: SQL> SELECT last_name || ', ' || first_name AS "full name"

Q: Query the employees name sorted by ascending order.

A: SQL> SELECT ename

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 *

WHERE empno = 7788;

Q: Query the employees name whose names start with the letter �M.�

A: SQL> SELECT ename

WHERE ename LIKE 'M%';

Q: Query the employees name whose names end with the letter �R.�

A: SQL> SELECT ename

WHERE ename LIKE '%R';

Q: Query the employees name whose salaries between 2000 and 3000 dollars.

A: SQL> SELECT ename

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',

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

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

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)

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)

GROUP BY deptno

Q: Query the employees name that work for the Research or Sales department (the department number 20 or 30).

A: SQL> SELECT ename, deptno

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

(SELECT deptno

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

WHERE deptno = &deptno;

Q: Query the customer names which have more than four orders.

A: SQL> SELECT name

WHERE exists

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),

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

Q: Query the index tables that belong to the employee table and owns by the oracle user.

A: SQL> SELECT index_name, uniqueness

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

Q: Insert a record into the "employee" table using column names.

A: SQL> INSERT INTO employee

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

Q: Delete all the employee records from the "employee" table using the delete command and the truncate command.

A: SQL> DELETE FROM employee;

OR

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

Q: Add a salary column to the employee table.

A: SQL> ALTER TABLE employee

Q: Modify the "ename" column size from varchar10 to varchar15.

A: SQL> ALTER TABLE employee

Q: Rename the "employee" table to the "oracle_employee" table.

A: SQL> RENAME employee TO oracle_employee;

Q: Create a view to display the employee names of the �Accounting� department only.

A: SQL> CREATE VIEW employee_name

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

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 "oracle_data" tablespace.

A: SQL> CREATE USER newuser IDENTIFIED BY by newpass

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 oracle.oracle_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 oracle.oracle_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.

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,

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

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

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,

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:

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?

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

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

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

Q: How do you use an object ID to create an object type?

A: SQL> CREATE OR REPLACE TYPE address_book_type_object

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

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',

Q: Query the department number and their total salaries that have more than 5 employees working in their department.

A: SELECT deptno, SUM(sal)

Q: query the customer names which have more than four orders.

A: SELECT name FROM customer c

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

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

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,

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.