|
SQL Fundamental Exam Questions
|
Matthew
- 5:46
For if ye love them which love you,
what reward have ye? Do not even the publicans the same?
|
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?
|
Mark
- 12:30
And thou shalt love the Lord thy God
with all thy heart, and with all thy soul, and with all thy mind, and with
all thy strength: this is the first commandment. 12:31 And the second is
like, namely this, Thou shalt love
thy neighbour as thyself. There is none other commandment greater than
these.
|
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.
|
Luke
- 6:27
But I say unto you which hear, Love
your enemies, do good to them which hate you, …6:32 For if ye love
them which love you, what thank have ye? for sinners also love
those that love them.
|
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.
|
Romans
- 13:8
Owe no man any thing, but to love
one another: for he that loveth another hath fulfilled the law.
|
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?
|
Galatians
- 5:13 For, brethren, ye have been
called unto liberty; only use not liberty for an occasion to the flesh,
but by love serve one another. 5:14
For all the law is fulfilled in one word, even in this; Thou shalt love
thy neighbour as thyself. …5:22 But the fruit of the Spirit is love,
joy, peace, longsuffering, gentleness, goodness, faith,
|
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.
|
Ephesians
- 3:17
That Christ may dwell in your hearts by faith; that ye, being rooted and
grounded in love, …
|
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?
|
Philippians
- 1:9
And this I pray, that your love may
abound yet more and more in knowledge and in all judgment; …
|
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?
|
1
Thessalonians - 3:12
And the Lord make you to increase and abound in love
one toward another, and toward all men, even as we do toward you:
|
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?
|
2
Timothy - 1:7
For God hath not given us the spirit of fear; but of power, and of love,
and of a sound mind.
|
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?
|
Hebrews
- 10:24
And let us consider one another to provoke unto love
and to good works:
|
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?
|
James
- 2:8
If ye fulfil the royal law according to the scripture, Thou shalt love
thy neighbour as thyself, ye do well:
|
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?
|
1
Peter - 1:22
Seeing ye have purified your souls in obeying the truth through the Spirit
unto unfeigned love of the brethren,
see that ye love one another with a
pure heart fervently:
|
UNION
, INTERSET
, and MINUS
statements
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?
|
2
John - 1:5
And now I beseech thee, lady, not as though I wrote a new commandment unto
thee, but that which we had from the beginning, that we love
one another. 1:6 And this is love,
that we walk after his commandments. This is the commandment, That, as ye
have heard from the beginning, ye should walk in it.
|
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> /
|
Leviticus
- 19:18
Thou shalt not avenge, nor bear any grudge against the children of thy
people, but thou shalt love thy
neighbour as thyself: I am the LORD.
|
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?
|