oracle interview questions part1

SQL

1:  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 data type is a format that an input data will be stored in a column.

 

2:  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).

 

3:   Describe the Entity Relationship diagram and Logical Data Model.

A:   "Entity Relationship Diagram" or "Logical Data Model" is used to establish relationships between entities.

 

4:  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.

 

5:  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.

 

6:  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.

 

7:   Query the employee names and their salaries from the employee table.

A:   SQL> SELECT ename, sal FROM emp;

 

8:   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;

 

9:   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"

           FROM emp;

 

10:   What is the result of 100 + NULL?

A:    NULL.

 

11:   Query the employee names with their commissions.

A:   SQL> SELECT ename, comm commission FROM emp;

 

12:   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

           FROM emp;

 

13:   Concatenate the customers’ last name and first name separated by comma.

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

           FROM customers;

 

14:   Query the employees name sorted by ascending order.

A:   SQL> SELECT ename

           FROM emp

           ORDER BY ename ASC;

 

15:   Query the employees name sorted by descending order.

A:   SQL> SELECT ename FROM emp

           ORDER BY ename DESC;

 

16:   Query the employee information whose employee number is 7788.

A:   SQL> SELECT *

           FROM emp

           WHERE empno = 7788;

 

17:   Query the employees name whose names start with the letter “M.”

A:   SQL> SELECT ename

           FROM emp

           WHERE ename LIKE 'M%';

 

18:   Query the employees name whose names end with the letter “R.”

A:   SQL> SELECT ename

           FROM emp

           WHERE ename LIKE '%R';

 

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

A:   SQL> SELECT ename

           FROM emp

           WHERE sal BETWEEN 2000 AND 3000;

 

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

                                                                          20, 'Research',

                                                                          30, 'Sales',

                                                                         'Others') AS "Department"

           FROM emp;

 

21:   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.

 

22:   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.

 

23:   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.

 

24:   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.

 

25:   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.

 

26:   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.

 

27:   What is a self join?

A:    When a table refers to itself in the WHERE clause, we call that join is a self-join.

 

28:   Query all the employee names and their department including all the departments with no employees.

A:   SQL> SELECT ename, dname

           FROM emp e, dept d

           WHERE e.deptno (+) = d.deptno;

 

29:   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

           WHERE mgr.empno = e.mgr

           ORDER BY mgr.ename;

 

30:   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)

           FROM emp

           GROUP BY deptno;

 

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

A:    SQL> SELECT deptno, SUM(sal)

           FROM emp

           GROUP BY deptno

           HAVING count(*) > 5;

 

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

A:   SQL> SELECT ename, deptno

           FROM emp

           WHERE deptno IN (20, 30);

 

33:   Query the employees name that work in the "accounting" department.  Assuming the department number is unknown.

A:   SQL> SELECT ename

           FROM emp

           WHERE deptno IN

              (SELECT deptno

                          FROM dept

                          WHERE dname = "ACCOUNTING");

 

34:   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

           FROM emp

           WHERE deptno = &deptno;

SQL> /

 

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

A:   SQL> SELECT name

           FROM customer c

           WHERE exists

               (SELECT 'T'

                 FROM ord

                 WHERE custid = c.custid

                 GROUP BY custid

                 HAVING count(*) > 4);

 

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

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

 

37:   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);

 

38:   Query the tables that you as a user own.

A:   SQL> SELECT table_name

           FROM user_tables

           ORDER BY table_name;

 

39:   Query the index tables that belong to the employee table and owns by the iself user.

A:   SQL> SELECT index_name, uniqueness

           FROM user_indexes

           WHERE table_name = 'EMPLOYEE';

 

40:   Change the size of the "column_name" to 30 characters logically (for display only).

A:   SQL> COLUMN column_name FORMAT a30

 

41:   Query the indexes columns of the employee table.

A:   SQL> SELECT index_name, column_name, column_position

           FROM user_ind_columns

           WHERE table_name = 'EMPLOYEE';

 

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

A:   SQL> INSERT INTO employee

          (empid, lastname, deptno, firstname, phone_no)

           VALUES (100, 'smith', 10,'joe', ‘7038212211');

 

43:   Insert a record using the column position format.

A:   SQL> INSERT INTO employee

          VALUES (200, 'KING', 'Allen', 5464327532, 10);

 

44:   How do you save the inserted transaction?

A:   COMMIT;

 

45:   Change the "last_name" column value from “Smith” to “Judd” where the "employee id" is 100.

A:   SQL> UPDATE employee

           SET lastname = 'Judd'

           WHERE empid = 100;

 

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

A:         SQL> DELETE FROM employee;

                        OR

            SQL> TRUNCATE TABLE employee;

 

47:   How do you undo a transaction?

A:   ROLLBACK;

 

48:   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.

 

49:   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

                 FROM emp;

 

50:   Add a salary column to the employee table.

A:   SQL> ALTER TABLE employee

           ADD (salary NUMBER(8,2));