keywords:
Bookmark and Share



Front Back
CREATE USER
GRANTEE CAN CREATE OTHER ORACLE USERS.
DROP USER
Grantee can drop another user.
DROP ANY TABLE
Grantee can drop a table in any schema.
SELECT ANY TABLE
Grantee can query tables, views, or materialized views in any schema.
CREATE ANY TABLE
Grantee can create tables in any schema.
SYSTEM PRIVILEGES
More than 100 distinct system privileges are available for users and roles. System privileges typically are provided by the database administrator.
CREATE USER   user
IDENTIFIED BY  password
CREATE USER   HR
IDENTIFIED BY  051865
GRANT privilege [, privilege...]
TO user [, user/ role, PUBLIC...]
An application developer, for example, may have the following system privileges:
* CREATE SESSION
* CREATE TABLE
* CREATE SEQUENCE
* CREATE VIEW
* CREATE PROCEDURE
CREATE SESSION
Connect to the database
CREATE TABLE
Create tables in the user's schema.
CREATE VIEW
Create a view in the user's schema.
CREATE PROCEDURE
Create a stored procedure, function, or package in the user's schema.
Primary Key
Uniquely identifies each row of the table
Foreign Key
Establishes and enforces a foreign key relationship between the column and a column of the referenced table.
Unique
Specifies a column or combination of columns whose values must be unique for all rows in the table.
Check
Specifies a condition that must be TRUE.
Synonyms
database objects that enable you to call a table by another name
Database tables and columns are named according to the standard rules for naming any Oracle database object. The rules include:
Table names and column names must begin with a letter and be one to thirty characters long;  Names must contain only the characters of capital and lowercase letters A to Z, numbers zero to nine, the underscore, dollar sign, and pound sign. Legal characters are allowed, but are discouraged;  Names must not duplicate the name of another object owned by the same Oracle server user;  Names must not be an Oracle server reserved word.
Sequences
Occasionally data that might not have a primary key assigned. Sequences can generate integers that can be used as primary key values. Sequence options include creating sequence, using a sequence, modifying a sequence, and dropping a sequence.  Please go to the next slide.
EQUIJOIN
SELECT last_name, job_id, departments.department_id, department_name
   FROM employees, departments
   WHERE employees.department_id = departments.department_id
   AND job_id = 'SA_MAN';
The "HAVING" CLAUSE with SubQueries
SELECT     job_id, AVG (salary)
FROM        employees
GROUP BY     job_id
HAVING AVG (salary) =
            (SELECT Min (AVG(salary))
               FROM    employees
               GROUP BY   job_id);
Executing single row SubQueries
SELECT last_name, job_id
FROM employees
WHERE job_id =
        (SELECT job_id
        FROM    employees
        WHERE   employee_id = 141)AND   salary >
       (SELECT salary
       FROM    employees
       WHERE   employee_id = 143);
Group functions in a SubQuery
SELECT last_name, job_id, salary
FROM   employees
WHERE  salary =
              (SELECT Min (salary)
               FROM   employees);
MULTIPLE QUERY

*Find one field with Multiple Columns.
SELECT last_name, job_id
FROM employees
WHERE job_id =
              (SELECT job_id
              FROM    employees
              WHERE   employee_id = 141);
NULL values in a SubQuery
SELECT    emp.last_name
FROM      employees  emp
WHERE   emp.employee_id NOT IN
         (SELECT mgr.manager_id
         FROM    employees mgr);
Search for data using Wild Card % character.
SELECT    employee_id, last_name
FROM      employees
WHERE    last_name = '%u%');
UNION OPERATOR
SELECT employee_id, job_id, department_id
FROM employees
UNION
SELECT employee_id, job_id, department_id
FROM job_history;
Updating rows based on another table.
UPDATE      copy_emp
SET         department_id =
          (SELECT   department_id
              FROM      employees
              WHERE     employee_id = 100)
WHERE       job_id        =
            (SELECT   job_id
            FROM      employees
            WHERE     employee_id = 200);
Using a SubQuery in an insert statement.
INSERT  INTO
      (SELECT   employee_id, last_name,
     email, hire_date, job_id, salary,
     department_id
     FROM      employees
     WHERE     department_id = 50)
VALUES 
       (99999, 'Taylor', 'DTAYLOR', To_Date('07-Jun-99', 'DD-MON-RR'), 'ST_CLERK', 5000, 50);
Using "ALL" operator in Multiple row SubQuery
SELECT    employee_id, last_name, job_id, salary
FROM      employees
WHERE     salary < ALL
      (SELECT   salary
      FROM     employees
      WHERE    job_id = 'IT_PROG')
AND    
       job_id <> 'IT_PROG';
Search for Salary less or equal to 3000 from employee table.
SELECT last_name, salary FROM employees WHERE salary <= 3000 ;
Using "NOT NULL" values in a SubQuery.
SELECT    emp.last_name
FROM      employees  emp
WHERE     emp.employee_id NOT IN
    (SELECT mgr.manager_id
    FROM    employees mgr
    WHERE   manager_id IS NOT NULL);
Prompts you to enter last name of employee
SELECT    last_name, hire_date
FROM      employees
WHERE     department_id =
       (SELECT department_id
        FROM    employees
        WHERE   last_name =      '&&Enter_name')
AND last_name <> '&Enter_name';
Create the DEPT table based on the example given in the table instance chart.

Confirm table creation.
CREATE TABLE dept2
 (id NUMBER(7)
 CONSTANT dept2_dept_id
 PRIMARY KEY, name
 VARCHAR2(25));

DESCRIBE dept2;
Populate the DEPT table with the data from the DEPARTMENTS table. Include only the columns that you need.
INSERT INTO dept2
SELECT department_id, department_name
FROM departments;
x of y cards Next >|