Study your flashcards anywhere!

Download the official Cram app for free >

  • Shuffle
    Toggle On
    Toggle Off
  • Alphabetize
    Toggle On
    Toggle Off
  • Front First
    Toggle On
    Toggle Off
  • Both Sides
    Toggle On
    Toggle Off
  • Read
    Toggle On
    Toggle Off
Reading...
Front

How to study your flashcards.

Right/Left arrow keys: Navigate between flashcards.right arrow keyleft arrow key

Up/Down arrow keys: Flip the card between the front and back.down keyup key

H key: Show hint (3rd side).h key

A key: Read text to speech.a key

image

Play button

image

Play button

image

Progress

1/32

Click to flip

32 Cards in this Set

  • Front
  • Back
Proposed the relational model for database systems in 1970.
Dr. E.F. Codd
Retrieves information from the database.
SELECT Statement
Select all columns of table departments.
Select * from departments;
(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.
False

(Keywords cannot be abbreviated or split accross line.)
Difference between iSQL*Plus and SQL*Plus
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
What is a null value?
A null is a value that is unavailable, unassigned, unknown, or inapplicable.
It's not the same as zero or a blank space.
Write a query that display employees last name, commission_pct from employees. Rename the last name header as Name and commission_pct as Commission.
SELECT last_name AS "Name", commission_pct AS "Commission"
FROM employees;
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.
SELECT last_name || job_id
FROM employees;
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
Select last_name ||' is a '||job_id
AS "Employee Details"
FROM employees;
Create a query that display the department id of the employees
(note: eliminate the duplicate rows)
SELECT DISTINCT department_id
FROM employees;
Is a command language for communication with the Oracle server from any tool or application.
SQL
Is an Oracle tool that recognizes and submits SQL statements to the Oracle server for execution and contains its own command language.
iSQL*Plus
Show me the structure of table employees.
DESCRIBE employees;
Identify the errors in this statements.

SELECT empployee_id, last_name
sal x 12 ANNUAL SALARY
FROM employees;
-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.
create a query that shows employee id, last name, job id, department id.
(note: department id must equal to 90)
SELECT empployee_id,last_name, job_id, department_id
FROM employees
where department_id = 90;
Create a query that shows all employees last name and salary.Whose salary is less than or equal to 3000.
SELECT last_name, salary
FROM employees
WHERE salary <= 3000;
Create a query that returns rows from employees table for any employee whose salary is between 2500 and 3500.
SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500;
Show me the records (employee_id, last_name, salary, manager_id) of employees whose manager id are equal to 100,101,201.
SELECT empployee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100,101,201);
Show me all records of employees whose last name begins with letter A.
SELECT last_name
FROM employees
WHERE last_name LIKE 'A%';
Create a query that displays employee id, last name, job id whose job id is equal to SA_.
SELECT empployee_id, last_name, job_id
FROM employees
WHERE job_id LIKE '%SA\_%' ESCAPE '';
Create a query that shows the emplpoyee that dont have commission.
SELECT last_name, manager_id
FROM employees
WHERE manager_id IS NULL;
Create a query that uses case manipulation functions like lower, upper, initcap.
SELECT lower('ALAN'), upper('alan'),initcap('ALAN')
from dual;
display the current date.
SELECT sysdate
FROM dual;
Display thew last name and the number of weeks employed for all employees in department 90.
SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
WHERE department_id = 90;
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.
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;
Display the last name and hire date for all employees. The hire date appears as 17 June 1987
SELECT last_name,
TO_CHAR(hire_date,'fmDD Month YYYY') HIREDATE
FROM employees;
Display the last name and manager id for all employees. Replace the null value with a text string "No Manager"
SELECT last_name
NVL(TO_CHAR(manager_id),'No Manager')
FROM employees
WHERE manager_id IS NULL;
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);
LAST_NAME || SALARY || COMMISSION_PCT || INCOME
Abel || 11000 || ||.3 || SAL+COMM
Taylor || 8600 || ||.2 || SAL+COMM
Matos || 2600 || || || SAL
Create a query that uses the COALESCE Function and ORDER BY.
SELECT last_name, COALESCE(commsion_pct,salary, 10) comm
FROM employees
ORDER BY commission_pct;
When is a cartesian product is formed?
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.
Create a cartesian join. Use the employees and departments tables.
SELECT last_name, department_name
from employees, departments;
Create an Equijoins. Use the Employees and departments tables. dispaly the employee id, last name, department id and location id.
SELECT employees.last_name, employees.department_id,departments.departments_id
FROM employees, departments
WHERE employees.department_id = departments.departments_id;