SQL Fundamental Exam Questions
Lesson 01
Q:
What are the definitions of the following items?
column,
record,
table,
item,
field,
element,
primary
key,
foreign
key, and
datatype.
Q: What is the relationship between primary and foreign keys?
Q: Describe the Entity Relationship diagram and Logical
Data Model.
Q: What is a composite index?
Q: What are the responsibilities of an Oracle DBA and Oracle
Developer?
Q:
What is a Database?
Lesson 02
Q: Query the employee names and their salaries from the
employee table.
Q: Do the above query and use an “as” clause for the
“salary” column aliases or column headings.
Q: Repeat the previous query and have “Full Name” for
the ename’s column heading and “Salary” for the “sal” column heading.
Q: What is the result of 100 + NULL
?
Q: Query the employee names with their commissions.
Q: Use the (NVL
) the null value function to assign zero to any null value in the
commission column for the previous query.
Q: Concatenate the customers’ last name and first name
separated by comma.
Q: Query the employees name sorted by ascending order.
Q: Query the employees name sorted by descending order.
Q: Query the employee information whose employee number is
7788.
Q: Query the employees name whose names start with the
letter “M.”
Q: Query the employees name whose names end with the
letter “R.”
Q: Query the employees name whose salaries between 2000
and 3000 dollars.
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.
Lesson 03
Q: What is an ambiguous column?
Q: How can you resolve an ambiguous column problem?
Q: What is a Cartesian product?
Q: How can you avoid a Cartesian product?
Q: What is an inner join or equi-join?
Q: What is an outer join?
Q: What is a self join?
Q: Query all the employee names and their department
including all the departments with no employees.
Q: Query the managers’ name with their employees sorted
by the manager name.
Q: Query the department number and their total, average,
min, and max salaries for each department.
Q: Query the department no and their total salaries that
have more than 5 employees working in their department.
Q: Query the employees name that work for the Research or
Sales department (the department number 20 or 30).
Q: Query the employees name that work in the
"accounting" department. Assuming
the department number is unknown.
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.
Q: Query the customer names which have more than four
orders.
Lesson 04
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.
Q: Create a composite index on the employee table that
contains two index columns (last name and first name).
Q: Query the tables that you as a user own.
Q: Query the index tables that belong to the employee
table and owns by the iself user.
Q: Change the size of the "column_name" to 30
characters logically (for display only.
Q: Query the indexes columns of the employee table.
Q: Insert a record into the "employee" table
using column names.
Q: Insert a record using the column position format.
Q: How do you save the inserted transaction?
Q: Change the "last_name" column value from
“Smith” to “Judd” where the "employee id" is 100.
Q: Delete all the employee records from the
"employee" table using the delete command and the truncate command.
Q: How do you undo a transaction?
Q: What is the difference between the delete statement and
the truncate statement?
Lesson 05
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.
Q: Add a salary column to the employee table.
Q: Modify the "ename" column size from varchar10
to varchar15.
Q: Rename the "employee" table to the "iself_employee"
table.
Q: Create a view to display the employee names of the
“Accounting” department only.
Q: Why do you use the view?
Q: How do you compile the view?
Q: How do you delete the view?
Q: Create an index on the employee table on the ename
column only and name it employee_indx.
Q: Reorganize the “employee_indx” index table.
Q: Drop the employee_ename index table.
Q: Create a user with username “newuser” and password
"newpass." Its default
tablespace should be the "iself_data" tablespace.
Q: Grant the resource and connect roles to newuser.
Q: Change the
newuser password to "mypass".
Q: Can the above new user access to any other user tables?
Q: What is a public synonym?
Q: What is the syntax to create a public synonym?
Q: What is the difference between public and private
synonym?
Q: Create and drop a private synonym.
Q: Revoke an object privilege on a table from a user.
Lesson 06
Q: What does the LIST or ‘L’ command line editor?
Q: What does the INSERT or ‘I’ command line editor?
Q:
What does the DEL or ‘D’ command line editor?
Q: How do you change a string in the Oracle Buffer?
Q: How do you save the SQL script in the Oracle Buffer?
Q: How do you open the SQL Script into the Oracle Buffer?
Lesson 07
Q: How do you use
the notepad editor?
Q: What is
afiedt.buf?
Q: How do you change your text editor in the SQLPLUS tool?
Q: What does the ed command in the SQLPLUS tool?
Q: Can you have multiple SQL statements in the afiedt.buf
file?
Q: How do you use the notepad editor as an independent
tool in the SQLPLUS utility?
Q: How do you execute or run a SQL script?
Lesson 08
Q:
What is the SQL ANSI statement?
Q:
What is the difference between the SQL ANSI statement and Original Oracle
statement?
Q:
Is the SET command a SQL statement?
Q:
How do you change your workstation’s page size or line size?
Q:
What does the JOIN syntax in the Oracle SQL (DML) statement?
Q:
What is the difference between the JOIN syntax and the NATURAL JOIN
syntax?
Q:
What does the USING clause in the Oracle SQL statement?
Q:
When can you not use the USING clause?
Q:
What is the advantage of the NATURAL JOIN syntax?
Q:
What does the CROSS JOIN syntax in the Oracle SQL statement?
Q:
What does the IN clause in the Oracle SQL statement?
Q:
What do the OUTER JOIN, RIGHT OUTER JOIN, LEFT OUTER JOIN, and FULL OUTER
JOIN syntax in the Oracle SQL statement?
Q:
How can you perform the FULL OUTER JOIN syntax using the Original Oracle
syntax?
Lesson 09
Q: When do you use the WITH … AS clause in the SQL
statement?
Q: How does the WITH … AS clause help your performance?
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.
Q: What are the multiple columns in the SQL statement?
Where or how do you use them?
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.
Q: What is the inline view in the Oracle SQL statement?
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.
Q: What does the MERGE statement in the SQL statement?
Q: Can you update, insert, or delete any records while you
are using the MERGE statement?
Lesson 10
Q:
What is a Materialized View?
Q:
What are the Materialized View types?
Q:
Write the difference between ROWID and PRIMARY KEY in the Materialized
View.
Q:
What is the difference between a Materialized View and View?
Q:
When or why do you use a Materialized View?
Q:
What is a materialized view log?
Q:
What are the PRIMARY KEY and ROWID in the Materialized View Log?
Q:
What does the USER_SNAPSHOT_LOGS view contain?
Q:
Create a materialized view that contains the
department number, number of employees, and total salaries paid to
employees by department.
Q: Who can create a materialized view?
Q: What does the USER_MVIEWS view contain?
Q: How do you refresh a materialized view?
Q: What parameter should be used to update the
materialized view every month automatically without human intervention?
Q: What does the USER_JOBS view contain?
Q: How do you remove a job from the Oracle Job Queue?
Q: How do you drop a materialized view log and a
materialized view?
Lesson 11
Q:
What does the BREAK ON clause in SQLPLUS?
Q:
What do the REPHEADER and REPFOOTER commands in SQLPLUS?
Q:
What does the following commands?
COLUMN
sal HEADING 'Salary' FORMAT $99,999.99
COLUMN
ename HEADING 'Employee' FORMAT a20
REPHEADER
BREAK
ON
dname
SKIP 1
COMPUTE
SUM
OF
sal ON dname
SPOOL
c:\salary.out
SPOOL OFF
REPFOOTER
CLEAR
BUFFER
CLEAR COLUMNS
CLEAR COMPUTE
Q:
What does the CLEAR command in SQLPLUS?
UNION
, INTERSET
, and MINUS
statements
Lesson 12
Q: What does the UNION statement in the SQL statement?
Q: What does the INTERSET statement in the SQL statement?
Q: What does the MINUS statement in the SQL statement?
Lesson 13
Q: Why it is important to eliminate duplicate records?
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> /
Lesson 14
Q: What is a data partitioning in the Oracle database?
Q: When should you use data partitioning?
Q: What is the advantage of using a data partitioning?
Q: What is a partition key?
Q: What is a local index in the data partitioning?
Q: What is a global index in the data partitioning?
Q: What are the differences between local and global
indexes?
Q: How does the ‘VALUE LESS THAN’ method work in the
data partitioning?
Q: Why do you need multiple tablespaces?
Q: Create a range-based partitioning table named p_emp. Be
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.
Q: What does the MAXVALUE parameter mean in the data
partitioning?
Q: How do you analyze a partition table?
Q: What does the USER_TAB_PARTITIONS view contain?
Q: Write a query to list the accounting employees from the
partition table. Use the partition option.
Q: Write a query to list employee number 7900 from the
sales department.
Q:
How do you create a local partition index?
Q:
How do you analyze a partition table index?
Q: What does the USER_IND_PARTITIONS view contain?
Lesson 15
Q:
What does the ROLLUP operator?
Q:
What does the CUBE operator?
Q: What are the differences between the CUBE and ROLLUP
functions?
Q: What environments may use the CUBE and ROLLUP functions
most?
Q:
Write a query to list an aggregation sum
report for each job, in each year, using the ROLLUP
grouping
option.
Q:
Write a query to list an aggregation sum
report for each job, in each year, using the CUBE grouping option.
Lesson 16
Q:
What is an
object type?
Q:
What is a collection object?
Q:
Create an object type with two columns to hold
the employee's child name and date of birth and name it employee_kids .
Q:
Create a table type using employee_kids and name it
employee_kids_table.
Q:
Create the emp_family table containing
the kid’s column with a type of employee_kids_table.
Q:
How do you insert a record in the object type?
Q:
What is the constructor?
Q:
What is the ‘THE’ sub-query?
Q:
How do you query a record using the ‘THE’ sub-query?
Q:
What is a nested table?
Q:
How do you insert a record to a nested table?
Q:
How do you update a record to nested table?
Q:
How do you add a unique index to a nested table?
Replicating
data with a NESTED TABLE
collection type
Lesson 17
Q:
What is a data replica?
Q:
What is the difference between a materialized view and a materialized
view log?
Q:
What is an object ID?
Q:
How do you retrieve an object ID?
Q:
How do you use an object ID to create an object type?
|