SQL Interview Questions and Answers Part5

Q: 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 datatype is a format that an input data will be stored in a
column.
Q: 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).
Q: Describe the Entity Relationship diagram and Logical Data Model.
A: "Entity Relationship Diagram" or "Logical Data Model" is used to establish
relationships between entities.
Q: 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.
Q: 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.
Q: 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.
Q: Query the employee names and their salaries from the employee table.
A: SQL > SELECT ename, sal FROM emp;
Q: Do the above query and use an “as” clause for the “salary” column aliases orcolumn headings.
A: SQL > SELECT ename, sal AS salary FROM emp;
Q: 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;
Q: What is the result of 100 + NULL ?
A: NULL.
Q: Query the employee names with their commissions.
A: SQL > SELECT ename, comm commission FROM emp;
Q: 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;
Q: Concatenate the customers’ last name and first name separated by comma.
A: SQL > SELECT last_name || ', ' || first_name AS "full name"
FROM customers;
Q: Query the employees name sorted by ascending order.
A: SQL > SELECT ename
FROM emp
ORDER BY ename ASC;
Q: Query the employees name sorted by descending order.
A: SQL > SELECT ename FROM emp
ORDER BY ename DESC;
Q: Query the employee information whose employee number is 7788.
A: SQL > SELECT *
FROM emp
WHERE empno = 7788;
Q: Query the employees name whose names start with the letter “M.”
A: SQL > SELECT ename
FROM emp
WHERE ename LIKE 'M%';
Q: Query the employees name whose names end with the letter “R.”
A: SQL > SELECT ename
FROM emp
WHERE ename LIKE '%R';
Q: Query the employees name whose salaries between 2000 and 3000 dollars.
A: SQL > SELECT ename
FROM emp
WHERE sal BETWEEN 2000 AND 3000;
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: SQL > SELECT ename, DECODE (deptno, 10, 'Accounting',
20,
'Research',
30,
'Sales',
'Others') AS "Department"
FROM emp;
Q: 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 noambiguity on their ownerships.
Q: 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.
Q: 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.
Q: 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.
Q: 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.
Q: 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.
Q: What is a self join?
A: When a table refers to itself in the WHERE clause, we call that join is a
self-join.
Q: 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;
Q: 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;
Q: 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;
Q: 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;
Q: 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);
Q: 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");
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.
A: SQL > SELECT ename
FROM emp
WHERE deptno = &deptno;
SQL > /
Q: 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);
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: 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));
Q: 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);
Q: Query the tables that you as a user own.
A: SQL > SELECT table_name
FROM user_tables
ORDER BY table_name;
Q: 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';
Q: Change the size of the "column_name" to 30 characters logically (for display
only).
A: SQL > COLUMN column_name FORMAT a30
Q: 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';
Q: 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');
Q: Insert a record using the column position format.
A: SQL> INSERT INTO employeeVALUES (200, 'KING', 'Allen', 5464327532, 10);