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

Click to flip

20 Cards in this Set

  • Front
  • Back
Copying Rows from Another Table
INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';
retrieve data with a sub query in the FROM clause
SELECT a.last_name, a.salary,
a.department_id, b.salavg
FROM employees a, (SELECT department_id,
AVG(salary) salavg
FROM employees
GROUP BY department_id) b
WHERE a.department_id = b.department_id
AND a.salary > b.salavg;
Updating Two Columns with a Subquery. Update the job and salary of employee 114 to match the job of employee 205 and the salary of employee 168.
UPDATE empl3
SET job_id = (SELECT job_id
FROM employees
WHERE employee_id = 205),
salary = (SELECT salary
FROM employees
WHERE employee_id = 168)
WHERE employee_id = 114;
Use subqueries in UPDATE statements to update rows in a table based on values from another table.
UPDATE empl3
SET department_id = (SELECT department_id
FROM employees
WHERE employee_id = 100)
WHERE job_id = (SELECT job_id
FROM employees
WHERE employee_id = 200);
Explicit Default Feature. example
INSERT INTO deptm3
(department_id, department_name, manager_id)
VALUES (300, 'Engineering', DEFAULT);
DEFAULT with UPDATE example
UPDATE deptm3
SET manager_id = DEFAULT WHERE department_id = 10;
unconditional insert =
inserts line by line or row by row INSERT INTO clauses until finished
Conditional INSERT=
inserts line by line but filters WHEN a statement is true. it will not execute a false code. can use up to 127 consecutive WHEN clauses with this type of INSERT
conditional INSERT ALL =
For each WHEN clause whose condition evaluates to true, the Oracle server executes the corresponding INTO clause list.
Regardless of the result.
Conditional INSERT: FIRST
executes the FIRST, insert into 'WHEN' clause that is true. Oracle will also skip and subsequent clauses.
Conditional INSERT: ELSE Clause
For a given row, if no WHEN clause evaluates to true:
If you have specified an ELSE clause, the Oracle server executes the INTO clause list associated with the ELSE clause.
If you did not specify an ELSE clause, the Oracle server takes no action for that row.
restrictions on multiple INSERTS
You can perform multitable INSERT statements only on tables, not on views or materialized views.

You cannot perform a multitable INSERT into a remote table.

You cannot specify a table collection expression when performing a multitable INSERT.
In a multitable INSERT, all of the insert_into_clauses cannot combine to specify more than 999 target columns.
Unconditional INSERT ALL:
Select the EMPLOYEE_ID, HIRE_DATE, SALARY, and MANAGER_ID values from the EMPLOYEES table for those employees whose EMPLOYEE_ID is greater than 200.
Insert these values into the SAL_HISTORY and MGR_HISTORY tables using a multitable INSERT.
INSERT ALL INTO sal_history VALUES(EMPID,HIREDATE,SAL) INTO mgr_history VALUES(EMPID,MGR,SAL)
SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
FROM employees WHERE employee_id > 200;
Conditional INSERT ALL:
Select values from the EMPLOYEES table for those employees whose EMPLOYEE_ID is greater than 200.
If the SALARY is greater than $10,000, insert these values into the SAL_HISTORY table .
If the MANAGER_ID is greater than 200, insert these values into the MGR_HISTORY table
INSERT ALL WHEN SAL > 10000 THEN INTO sal_history VALUES(EMPID,HIREDATE,SAL) WHEN MGR > 200 THEN INTO mgr_history VALUES(EMPID,MGR,SAL)
SELECT employee_id EMPID,hire_date HIREDATE,
salary SAL, manager_id MGR
FROM employees WHERE employee_id > 200;
PIVIOTING insert example.
the original non-relational database table, SALES_SOURCE_DATA, is converted into five records for the Oracle SALES_INFO table. This operation is commonly referred to as a pivoting insert. creating a relational database table out of non-related tables.
INSERT ALL INTO sales_info VALUES (employee_id,week_id,sales_MON) INTO sales_info VALUES (employee_id,week_id,sales_TUE) INTO sales_info VALUES (employee_id,week_id,sales_WED) INTO sales_info VALUES (employee_id,week_id,sales_THUR) INTO sales_info VALUES (employee_id,week_id, sales_FRI) SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE, sales_WED, sales_THUR,sales_FRI FROM sales_source_data;
Write a query to do the following:
Retrieve the details of the employee ID, hire date, salary, and manager ID of those employees whose employee ID is less than 125 from the EMPLOYEES table.
If the salary is more than $20,000, insert the details of employee ID and salary into the SPECIAL_SAL table.
Insert the details of employee ID, hire date, and salary into the SAL_HISTORY table.
Insert the details of the employee ID, manager ID, and salary into the MGR_HISTORY table.
INSERT ALL
WHEN SAL > 20000 THEN INTO special_sal VALUES (empid, sal)
ELSE INTO sal_history VALUES (EMPID, HIREDATE,SAL) INTO mgr_history VALUES (EMPID,MGR,SAL) SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR FROM employees WHERE employee_id <125;
Write a query to do the following:
Retrieve the details of employee ID, week ID, sales on Monday through Friday. day by day from the SALES_SOURCE_DATA table.
Build a transformation such that each record retrieved from the SALES_SOURCE_DATA table is converted into multiple records for the SALES_INFO table. Hint: Use a pivoting INSERT statement.
INSERT ALL
INTO sales_info VALUES (employee_id, week_id, sales_MON)
(employee_id, week_id, sales_TUE)
(employee_id, week_id, sales_WED)
(employee_id, week_id, sales_THURS)
(employee_id, week_id, sales_FRI)
SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE, sales_WED, sales_THUR, sales_FRI FROM sales_source_data;
You have the data of past employees stored in a flat file called emp.data. You want to store the names and e-mail IDs of all employees past and present in a table. To do this, first create an external table called EMP_DATA using the emp.dat source file in the emp_dir directory.
CREATE TABLE emp_data (first_name VARCHAR2(20), last_name VARCHAR2(20), email VARCHAR2(25) UNIQUE) ORGANIZATION EXTERNAL(TYPE oracle_loader DEFAULT DIRECTORY emp_dir ACCESS PARAMETERS(records delimited by newline characterset us7aascii NOBADFILE NOLOGFILE FIELDS(first_name POSITION (1:20) CHAR, last_name POSITION (22:41) CHAR, email POSITION (43:72) CHAR)) LOCATION ('emp.dat));
Increase the size of the e-mail column to 45.
b. Merge the data in the EMP_DATA table created in the last lab into the data in the EMP_HIST table. Assume that the data in the external EMP_DATA table is the most up-to-date. If a row in the EMP_DATA table matches the EMP_HIST table, update the e-mail column of the EMP_HIST table to match the EMP_DATA table row. If a row in the EMP_DATA table does not match, insert it into the EMP_HIST table. Rows are considered matching when the employee’s first and last name are identical.
c. Retrieve the rows from EMP_HIST after the merge.
MERGE INTO EMP_HIST f USING EMP_DATA h ON (f.first_name = h.first_name AND f.last_name = h.last_name) WHEN MATCHED THEN UPDATE SET f.email = h.email WHEN NOT MATCHED THEN INSERT (f.first_name, f.last_name, f.email) VALUES (h.first_name, h.last_name, h.email);
In the EMP3 table change the department for Kochhar to 60 and commit your change. Next, change the department for Kochhar to 50 and commit your change. Track the changes to Kochhar using the Row Versions feature.
SELECT VERSIONS_STARTIME "START_DATE", VERSIONS_ENDTIME "END_DATE", DEPARTMENT_ID FROM EMP3
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE WHERE LAST_NAME = 'Kochhar';