iSelfSchooling.com  Since 1999     References    .Job Openings      . FREE Online Oracle Materials 

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision     .Services     .Biography     .Acknowledgement      .Links... 

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

 

SQL QUESTIONS

We are against multiple choice questions. 

Online Oracle Training

SQL Fundamental Exam Questions

Basic Introduction

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?

 

Selecting Data (SQL)

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.

 

Advanced Data Selection

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.

 

Create the Database objects

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.

  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.

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?

 

Creating more Database Objects

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.

   

Command Line editing

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?

 

Notepad Editing

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? 

 

Oracle9i: SQL ANSI statements

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?

 

Oracle9i: More SQL New Features

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?

 

Materialized Views  – The view that stores data

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?

 

The BREAK ON clause

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?

 

Troubleshooting Deleting duplicated records

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> / 

 

Data Partitioning

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?

 

The ROLLUP and CUBE function

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.

 

Defining Objects and Collection Types

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?

 

 

Google
 
Web web site