My Certification

Front 1

Proposed the relational model for database systems in 1970.

Back 1

Dr. E.F. Codd

Front 2

Retrieves information from the database.

Back 2

SELECT Statement

Front 3

Select all columns of table departments.

Back 3

Select * from departments;

Front 4

(true or False)
-SQL statements are not case sensitive
-SQL statements can be one or more lines
-clauses are usually placed on separate lines.
-Keywords can be abbreviated or split accross line.

Back 4

False

(Keywords cannot be abbreviated or split accross line.)

Front 5

Difference between iSQL*Plus and SQL*Plus

Back 5

iSQL*Plus:
-default heading justification: center
-default heading display:Uppercase

SQL*Plus:
-characterand date column headings are left justified.
-number column headings are right-justified
-default heading display:Uppercase

Front 6

What is a null value?

Back 6

A null is a value that is unavailable, unassigned, unknown, or inapplicable.
It's not the same as zero or a blank space.

Front 7

Write a query that display employees last name, commission_pct from employees. Rename the last name header as Name and commission_pct as Commission.

Back 7

SELECT last_name AS "Name", commission_pct AS "Commission"
FROM employees;

Front 8

Create a query that display the last_name and job_id of the employees.
Put the last name and job id on the same column.

Back 8

SELECT last_name || job_id
FROM employees;

Front 9

Create a query of this one:

Employee Details
King is a AD_PRES
Kochhar is a AD_VP
De Haan is a AD_VP
Hunold is a IT_PROG

Back 9

Select last_name ||' is a '||job_id
AS "Employee Details"
FROM employees;

Front 10

Create a query that display the department id of the employees
(note: eliminate the duplicate rows)

Back 10

SELECT DISTINCT department_id
FROM employees;

Front 11

Is a command language for communication with the Oracle server from any tool or application.

Back 11

SQL

Front 12

Is an Oracle tool that recognizes and submits SQL statements to the Oracle server for execution and contains its own command language.

Back 12

iSQL*Plus

Front 13

Show me the structure of table employees.

Back 13

DESCRIBE employees;

Front 14

Identify the errors in this statements.

SELECT empployee_id, last_name
sal x 12 ANNUAL SALARY
FROM employees;

Back 14

-put semi colon after last_name
-no field name sal.
-x is not a arithmetic operators
-use double quotes in giving renaming headings that contains spaces.

Front 15

create a query that shows employee id, last name, job id, department id.
(note: department id must equal to 90)

Back 15

SELECT empployee_id,last_name, job_id, department_id
FROM employees
where department_id = 90;

Front 16

Create a query that shows all employees last name and salary.Whose salary is less than or equal to 3000.

Back 16

SELECT last_name, salary
FROM employees
WHERE salary <= 3000;

Front 17

Create a query that returns rows from employees table for any employee whose salary is between 2500 and 3500.

Back 17

SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;

Front 18

Show me the records (employee_id, last_name, salary, manager_id) of employees whose manager id are equal to 100,101,201.

Back 18

SELECT empployee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100,101,201);

Front 19

Show me all records of employees whose last name begins with letter A.

Back 19

SELECT last_name
FROM employees
WHERE last_name LIKE 'A%';

Front 20

Create a query that displays employee id, last name, job id whose job id is equal to SA_.

Back 20

SELECT empployee_id, last_name, job_id
FROM employees
WHERE job_id LIKE '%SA\_%' ESCAPE '';

Front 21

Create a query that shows the emplpoyee that dont have commission.

Back 21

SELECT last_name, manager_id
FROM employees
WHERE manager_id IS NULL;

Front 22

Create a query that uses case manipulation functions like lower, upper, initcap.

Back 22

SELECT lower('ALAN'), upper('alan'),initcap('ALAN')
from dual;

Front 23

display the current date.

Back 23

SELECT sysdate
FROM dual;

Front 24

Display thew last name and the number of weeks employed for all employees in department 90.

Back 24

SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
WHERE department_id = 90;

Front 25

Display the employee number, hire date, number of months employed, six-months review date, first friday after hire date, and last day of the month when hired for all employees employed for fewer than 36 months.

Back 25

SELECT employee_id, hire_date,
MONTHS_BETWEEN(SYSDATE,hire_date)
TENURE, ADD_MONTHS(hire_date,6)
REVIEW, NEXT_DAY(hire_date,
'FRIDAY'), LAST_DAY(hire_date)
FROM employees
WHERE MONTHS_BETWEEN(sysdate, hire_date)
< 36;

Front 26

Display the last name and hire date for all employees. The hire date appears as 17 June 1987

Back 26

SELECT last_name,
TO_CHAR(hire_date,'fmDD Month YYYY') HIREDATE
FROM employees;

Front 27

Display the last name and manager id for all employees. Replace the null value with a text string "No Manager"

Back 27

SELECT last_name
NVL(TO_CHAR(manager_id),'No Manager')
FROM employees
WHERE manager_id IS NULL;

Front 28

What is the output of this one:

SELECT last_name, salary, commission_pct, NVL2(commission_pct, 'SAL+COMM','SAL') income
FROM employees
WHERE department_id IN (50,80);

Back 28

LAST_NAME || SALARY || COMMISSION_PCT || INCOME
Abel || 11000 || ||.3 || SAL+COMM
Taylor || 8600 || ||.2 || SAL+COMM
Matos || 2600 || || || SAL

Front 29

Create a query that uses the COALESCE Function and ORDER BY.

Back 29

SELECT last_name, COALESCE(commsion_pct,salary, 10) comm
FROM employees
ORDER BY commission_pct;

Front 30

When is a cartesian product is formed?

Back 30

A cartesian product is formed when:
-a join condition is omitted
-a join condition is invalid
-all rows in the first table are joined to all rows in the second table.

Front 31

Create a cartesian join. Use the employees and departments tables.

Back 31

SELECT last_name, department_name
from employees, departments;

Front 32

Create an Equijoins. Use the Employees and departments tables. dispaly the employee id, last name, department id and location id.

Back 32

SELECT employees.last_name, employees.department_id,departments.departments_id
FROM employees, departments
WHERE employees.department_id = departments.departments_id;