SQL Interview Questions and Answers Part8

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.