3 Using SQL

Example 3-1 Using the SQL SELECT Statement to Query All Data From a Table

-- the following uses the wildcard * to retrieve all the columns of data in
-- all rows of the employees table
SELECT * FROM employees;

-- the following uses the wildcard * to retrieve all the columns of data in
-- all rows of the departments table
SELECT * FROM departments;

Example 3-2 Using the SQL SELECT Statement to Query Data From Specific Columns

-- the following retrieves data in the employee_id, last_name, first_name columns
SELECT employee_id, last_name, first_name FROM employees;

-- the following retrieves data in the department_id and department_name columns
SELECT department_id, department_name FROM departments;

Example 3-3 Using the SQL SELECT Statement to Query Data in a View

-- the following retrieves all columns of data in all rows of the emp_details_view
SELECT * FROM emp_details_view;

-- the following retrieves data from specified columns in the view
SELECT employee_id, last_name, job_title, department_name, country_name, 
       region_name FROM emp_details_view;

Example 3-4 Using a Column Alias for a Descriptive Heading in a SQL Query

-- the following retrieves the data in employee_id, last_name, first_name columns
-- and provides column aliases for more descriptive headings of the columns
SELECT employee_id "Employee ID number", last_name "Employee last name", 
  first_name "Employee first name" FROM employees;

Example 3-5 Selecting Data With the SQL WHERE Clause to Restrict Data

-- the following retrieves data where the manager_id equals 122
SELECT * FROM employees WHERE manager_id = 122;

-- this retrieves data where the manager_id equals 122 and job_id is ST_CLERK
SELECT * FROM employees WHERE manager_id = 122 AND job_id = 'ST_CLERK';

-- this retrieves employees with managers with IDs between 122 and 125 inclusive
SELECT * FROM employees WHERE manager_id BETWEEN 122 AND 125;

-- this uses LIKE with the wildcard % to retrieve employee data 
-- where the last name contains mar somewhere in the name string
SELECT employee_id, last_name FROM employees WHERE last_name LIKE '%mar%';

-- this uses LIKE with the wildcard % to retrieve employee data 
-- from the employees table where the last name starts with Mar
SELECT employee_id, last_name FROM employees WHERE last_name LIKE 'Mar%';

-- this retrieves employee data where the commission percentage is not null
SELECT employee_id, last_name FROM employees WHERE commission_pct IS NOT NULL;

-- the following retrieves data where the employee_id equals 125, 130, or 135
SELECT employee_id, last_name, first_name FROM employees
       WHERE employee_id IN (125, 130, 135);

Example 3-6 Using Regular Expressions With the SQL SELECT Statement

-- in the following example, the REGEXP_LIKE is used to select rows where
-- the value of job_id starts with ac, fi, mk, or st, 
-- then follows with _m, and ends with an or gr
-- the metacharacter | specifies OR
-- the 'i' option specifies case-insensitive matching
SELECT employee_id, job_id FROM employees 
   WHERE REGEXP_LIKE (job_id, '[ac|fi|mk|st]_m[an|gr]', 'i');

-- in the following example, REGEXP_REPLACE is used to replace 
-- phone numbers of the format "nnn.nnn.nnnn" with 
-- parentheses, spaces, and dashes to produce this format "(nnn) nnn-nnnn"
-- digits (0-9) are denoted with the metacharacter [:digit:]
-- the metacharacter {n} specifies a fixed number of occurrences
-- the \ is used an escape character so that the subsequent metacharacter 
-- in the expression is treated as a literal, such as \.; otherwise, the 
-- metacharacter . denotes any character in the expression
SELECT phone_number, REGEXP_REPLACE( phone_number,
 '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})', '(\1) \2-\3') 
 "Phone Number" FROM employees;

-- in the following example, REGEXP_REPLACE is used to replace
-- phone numbers of the format "nnn.nnn.nnnn.nnnnnn"
-- with the format "+nnn-nn-nnnn-nnnnnn"
SELECT phone_number, REGEXP_REPLACE( phone_number,
 '([[:digit:]]{3})\.([[:digit:]]{2})\.([[:digit:]]{4})\.([[:digit:]]{6})',
 '+\1-\2-\3-\4') "Phone Number" FROM employees;

-- in the following example, REGEXP_SUBSTR returns the first substring
-- composed of one or more occurrences of digits and dashes
-- the metacharacter + specifies multiple occurrences in [[:digit:]-]+
SELECT street_address, REGEXP_SUBSTR(street_address, '[[:digit:]-]+', 1, 1) 
  "Street numbers" FROM locations;

-- in the following example, REGEXP_INSTR starts searching at the first character
-- in the string and returns the starting position (default) of the second
-- occurrence of one or more non-blank characters
-- REGEXP_INSTR returns 0 if not found
-- the metacharacter ^ denotes NOT, as in NOT space [^ ]
SELECT street_address, REGEXP_INSTR(street_address, '[^ ]+', 1, 1) 
  "Position of 2nd block" FROM locations;

Example 3-7 Selecting Data With the SQL ORDER BY Clause to Sort the Data

-- the following retrieves rows with manager_id = 122 ordered by employee_id
-- the order is the default ascending order, lowest employee_id displays first
SELECT * FROM employees WHERE manager_id = 122 ORDER BY employee_id;

-- the following retrieves rows ordered by manager_id
-- the order is specified as descending, highest manager_id displays first
SELECT employee_id, last_name, first_name, manager_id FROM employees
      ORDER BY manager_id DESC;

Example 3-8 Selecting Data From Two Tables With the SQL NATURAL JOIN Syntax

-- the following SELECT statement retrieves data from two tables
-- that have a corresponding column(s) with equal values
-- for employees and departments, matching columns are department_id, manager_id
SELECT employee_id, last_name, first_name, department_id, 
  department_name, manager_id  FROM employees
  NATURAL JOIN departments;

Example 3-9 Selecting Data From Multiple Tables WIth the SQL JOIN USING Syntax

-- the following SELECT statement retrieves data from two tables
-- that have a corresponding column (department_id)
-- note that the employees table has been aliased to e and departments to d
SELECT e.employee_id, e.last_name, e.first_name, e.manager_id, department_id, 
  d.department_name, d.manager_id FROM employees e 
  JOIN departments d USING (department_id);

-- the following SELECT retrieves data from three tables
-- two tables have the corresponding column (department_id) and 
-- two tables have the corresponding column (location_id)
SELECT e.employee_id, e.last_name, e.first_name, e.manager_id, department_id,
  d.department_name, d.manager_id, location_id, l.country_id FROM employees e
  JOIN departments d USING (department_id)
  JOIN locations l USING (location_id);

Example 3-10 Selecting Data From Multiple Tables With the SQL JOIN ON Syntax

-- the following SELECT statement retrieves data from two tables
-- that have a corresponding column department_id
-- note that the employees table has been aliased to e and departments to d
SELECT e.employee_id, e.last_name, e.first_name, e.department_id, 
  d.department_name, d.manager_id FROM employees e 
  JOIN departments d ON e.department_id = d.department_id
  WHERE e.manager_id = 122;

-- the following SELECT retrieves data from three tables
-- two tables have the corresponding column department_id and 
-- two tables have the corresponding column location_id
SELECT e.employee_id, e.last_name, e.first_name, e.department_id,
  d.department_name, d.manager_id, d.location_id, l.country_id FROM employees e
  JOIN departments d ON e.department_id = d.department_id
  JOIN locations l ON d.location_id = l.location_id
  WHERE l.location_id = 1700;

Example 3-11 Self Joining a Table With the SQL JOIN ON Syntax

-- the following SELECT statement retrieves data from the employees table
-- to display employee_id and last_name, along with manager_id and last_name
-- of the employee in a self-join
-- note that the employees table has been aliased to e and m
SELECT e.employee_id emp_id, e.last_name emp_lastname, m.employee_id mgr_id,
  m.last_name mgr_lastname
  FROM employees e 
  JOIN employees m ON e.manager_id = m.employee_id;

Example 3-12 Using SQL Outer Joins

-- the following uses a LEFT OUTER JOIN
-- all rows are retrieved from the left table (employees) even if 
-- there is no match in the right table (departments)
SELECT e.employee_id, e.last_name, e.department_id, d.department_name
  FROM employees e LEFT OUTER JOIN departments d
  ON (e.department_id = d.department_id);

-- the following uses a RIGHT OUTER JOIN
-- all rows are retrieved from the right table (departments) even if 
-- there is no match in the left table (employees)
SELECT e.employee_id, e.last_name, d.department_id, d.department_name
  FROM employees e RIGHT OUTER JOIN departments d 
  ON (e.department_id = d.department_id);

-- the following uses a FULL OUTER JOIN
-- all rows are retrieved from the employees table even if there is no match in
-- the departments table, and all rows are retrieved from the departments table
-- even if there is no match in the left table
SELECT e.employee_id, e.last_name, d.department_id, d.department_name
  FROM employees e FULL OUTER JOIN departments d
  ON (e.department_id = d.department_id);

Example 3-13 Using the SQL SYSDATE Pseudocolumn

-- the following statement displays the SYSDATE, which is the current system date
-- NOW is a column alias for display purposes
-- DUAL is a dummy table with one row simply used to complete the SELECT statement
SELECT SYSDATE "NOW" FROM DUAL;

Example 3-14 Using the SQL USER Pseudocolumn

-- display the name of the current user, the user name should be HR
SELECT USER FROM DUAL;

Example 3-15 Using the SQL ROWNUM Pseudocolumn

-- using ROWNUM < 10 limits the number of rows returned to less than 10
SELECT employee_id, hire_date, SYSDATE FROM employees WHERE ROWNUM < 10;

Example 3-16 Using SQL Arithmetic Operators

-- in the following query the commission is displayed as a percentate instead 
-- of the decimal that is stored in the database
SELECT employee_id, (commission_pct * 100) "Commission %" FROM employees;

-- in the following query, the proposed new annual salary is calculated
-- for employees who report to the manager with ID 145
SELECT employee_id, ((salary + 100) * 12) "Proposed new annual salary" 
  FROM employees WHERE manager_id = 145;

Example 3-17 Using SQL Numeric Functions

-- you can use the ROUND function to round off numeric data, in this case to
-- two decimal places
SELECT employee_id, ROUND(salary/30, 2) "Salary per day" FROM employees;

-- you can use the TRUNC function to truncate numeric data, in this case to
-- 0 decimal places; 0 is the default so TRUNC(salary/30) would be same
SELECT employee_id, TRUNC(salary/30, 0) "Salary per day" FROM employees;

-- use the MOD function to return the remainder of a division
-- MOD is often used to determine is a number is odd or even
-- the following determines whether employee_id is odd (1) or even (0)
SELECT employee_id, MOD(employee_id, 2) FROM employees;

Example 3-18 Using SQL Character Functions

-- you can use the UPPER function to display uppercase data, LOWER for lowercase
SELECT employee_id, UPPER(last_name), LOWER(first_name) FROM employees;

-- you can use the INITCAP function to display uppercase only the first letter
SELECT employee_id, INITCAP(first_name), INITCAP(last_name) FROM employees;

-- you can use RTRIM and LTRIM to remove spaces from the beginning or end of 
-- character data. Note the use of concatenation operator || to add a space
SELECT employee_id, RTRIM(first_name) || ' ' || LTRIM(last_name) FROM employees;

-- you can use TRIM to remove spaces from both the beginning and end
SELECT employee_id, TRIM(last_name) || ', ' || TRIM(first_name) FROM employees;

-- you can use RPAD to add spaces on the right to line up columns
-- in this case, spaces are added to pad the last_name output to 30 characters
SELECT employee_id, RPAD(last_name, 30, ' '), first_name FROM employees;

-- use SUBSTR to select a substring of the data, in the following only 
-- the characters from 1 to 15 are selected from the last_name
SELECT employee_id, SUBSTR(last_name, 1, 10) FROM employees;

-- use LENGTH to return the number of characters in a string or expression
SELECT LENGTH(last_name) FROM employees;

-- use REPLACE to replace characters in a string or expression
SELECT employee_id, REPLACE(job_id, 'SH', 'SHIPPING') FROM employees
   WHERE SUBSTR(job_id, 1, 2) = 'SH';

Example 3-19 Using SQL Date Functions

-- in the following statement you can use MONTHS_BETWEEN to compute months
-- employed for employees and then truncate the results to the whole month
-- note the use of the label (alias) "Months Employed" for the computed column
SELECT employee_id, TRUNC(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) "Months Employed" 
  FROM employees;

-- the following extracts displays the year hired for each employee ID
SELECT employee_id, EXTRACT(YEAR FROM hire_date) "Year Hired" FROM employees;

-- the following extracts and concatenates the year, month, and day from SYSDATE
SELECT EXTRACT(YEAR FROM SYSDATE) || EXTRACT(MONTH FROM SYSDATE) || 
   EXTRACT(DAY FROM SYSDATE) "Current Date" FROM DUAL;

-- the following adds 3 months to the hire_date of an employee
SELECT employee_id, hire_date, ADD_MONTHS(hire_date, 3) FROM employees;

-- LAST_DAY finds the last day of the month for a specific date, such as hire_date
SELECT employee_id, hire_date, LAST_DAY(hire_date) "Last day of month" 
   FROM employees;

-- the following returns the system date, including fractional seconds 
-- and time zone, of the system on which the database resides
SELECT SYSTIMESTAMP FROM DUAL;

Example 3-20 Using the SQL Character Conversion Function

-- you can convert the system date (SYSDATE) to a character string and format
-- with various format models and then display the date as follows
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY AD') "Today" FROM DUAL;
-- FM removes all leading or trailing blanks from Month
SELECT TO_CHAR(SYSDATE, 'FMMonth DD YYYY') "Today" FROM DUAL;
-- the following displays the system date and time with a format model
SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "Now" FROM DUAL;

-- you can convert and format a date column using format models 
-- for Short or Long Date format
SELECT hire_date, TO_CHAR(hire_date,'DS') "Short Date" FROM employees;
SELECT hire_date, TO_CHAR(hire_date,'DL') "Long Date" FROM employees;

-- the following extracts the year, month, and date from SYSDATE, then converts 
-- and formats the result with leading zeros and removes any leading blanks (FM)
SELECT TO_CHAR(EXTRACT(YEAR FROM SYSDATE)) || 
  TO_CHAR(EXTRACT(MONTH FROM SYSDATE),'FM09') || 
  TO_CHAR(EXTRACT(DAY FROM SYSDATE),'FM09') "Current Date" FROM DUAL;

-- the following returns the current date in the session time zone, 
-- in a value in the Gregorian calendar of datatype DATE, 
-- the returned value is converted to character and displayed with a format model
SELECT TO_CHAR(CURRENT_DATE, 'DD-MON-YYYY HH24:MI:SS') "Current Date" FROM DUAL;

-- you can convert and format numeric currency data as a character string 
-- with a format model to add a $, commas, and deciaml point
SELECT TO_CHAR(salary,'$99,999.99') salary FROM employees;

Example 3-21 Using the SQL Number Conversion Function

-- you can convert a character string to a number
SELECT TO_NUMBER('1234.99') + 500 FROM DUAL;

-- the format model must match the format of the string you want to convert
SELECT TO_NUMBER('11,200.34', '99G999D99') + 1000 FROM DUAL;

Example 3-22 Using SQL Date Conversion Functions

-- the following converts the character string to a date with 
-- the specified format model
SELECT TO_DATE('27-OCT-98', 'DD-MON-RR') FROM DUAL;

-- the following converts the character string to a date with 
-- the specified format model
SELECT TO_DATE('28-Nov-05 14:10:10', 'DD-Mon-YY HH24:MI:SS') FROM DUAL;

-- the following converts the character string to a date with 
-- the specified format model
SELECT TO_DATE('January 15, 2006, 12:00 A.M.', 'Month dd, YYYY, HH:MI A.M.')
  FROM DUAL;

-- the following converts a character stirng to a timestamp with 
-- the specified datetime format model
SELECT TO_TIMESTAMP('10-Sep-05 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF')
  FROM DUAL;

Example 3-23 Using SQL Aggregate Functions

-- you can use COUNT to count the employees with manager 122
-- note the use of a column alias Employee Count
SELECT COUNT(*) "Employee Count" FROM employees WHERE manager_id = 122;

-- count the employees grouped by manager, also sort the groups
SELECT COUNT(*) "Employee Count", manager_id  FROM employees 
  GROUP BY manager_id 
  ORDER BY manager_id;

-- count the number of employees that receive a commission 
-- this returns the count where the commission_pct is not NULL
SELECT COUNT(commission_pct) FROM employees;

-- count the number of distinct department IDs assigned to the employees
-- this returns a number that does not include duplicates
SELECT COUNT(DISTINCT department_id) FROM employees;

-- you can use MIN, MAX, and AVG to find the minimum, maximum, and average
-- salaries for employees with manager 122
SELECT MIN(salary), MAX(salary), AVG(salary) FROM employees 
  WHERE manager_id = 122;

-- this computes the minimum, maximum, and average salary by job ID groups
-- the job ID groups are sorted in alphabetical order
SELECT MIN(salary), MAX(salary), AVG(salary), job_id FROM employees 
  GROUP BY job_id 
  ORDER BY job_id;

-- the following returns the minimum and maximum salaries for employees grouped
-- by department for those groups having a minimum salary less than $7,000
SELECT department_id, MIN(salary), MAX (salary) FROM employees
   GROUP BY department_id 
   HAVING MIN(salary) < 7000
   ORDER BY MIN(salary);

-- the following uses the PERCENT_RANK function to return the percent ranking
-- for a $11,000 salary among the employees who are managers
-- in this example, a percent ranking of 0 corresponds to the highest salary
SELECT PERCENT_RANK(11000) WITHIN GROUP
   (ORDER BY salary DESC) "Rank of $11,000 among managers" 
   FROM employees WHERE job_id LIKE '%MAN' OR job_id LIKE '%MGR';

-- the following uses the RANK function to return the ranking for a $2,600 salary 
-- among the employees who are clerks, 
-- in this example a ranking of 1 corresponds to the highest salary in the group
SELECT RANK(2600) WITHIN GROUP
   (ORDER BY salary DESC) "Rank of $2,600 among clerks"
   FROM employees WHERE job_id LIKE '%CLERK';

-- the following uses RANK to show the ranking of SH_CLERK employees by salary
-- identical salary values receive the same rank and cause nonconsecutive ranks
SELECT job_id, employee_id, last_name, salary, RANK() OVER
   (PARTITION BY job_id ORDER BY salary DESC) "Salary Rank"
   FROM employees WHERE job_id = 'SH_CLERK';

-- the following uses DENSE_RANK to show the ranking of SH_CLERK employees 
-- by salary, identical salary values receive the same rank and 
-- rank numbers are consecutive (no gaps in the ranking)
SELECT job_id, employee_id, last_name, salary, DENSE_RANK() OVER
   (PARTITION BY job_id ORDER BY salary DESC) "Salary Rank (Dense)"
   FROM employees WHERE job_id = 'SH_CLERK';

-- the following computes the cumulative standard deviation of the salaries 
-- for ST CLERKs ordered by hire_date
SELECT employee_id, salary, hire_date, STDDEV(salary) 
   OVER (ORDER BY hire_date) "Std Deviation of Salary"
   FROM employees WHERE job_id = 'ST_CLERK';

Example 3-24 Using the SQL NVL Function

-- use the NVL function to substitute 0 for a NULL value in commission_pct
SELECT commission_pct, NVL(commission_pct, 0) FROM employees;

-- use the NVL function to substitute MISSING for a NULL value in phone_number
SELECT phone_number, NVL(phone_number, 'MISSING') FROM employees;

Example 3-25 Using the SQL NVL2 Function

-- use the NVL2 function to return salary + (salary * commission_pct) 
-- if commission_pct is not NULL; otherwise, if commission_pct is NULL, 
-- then return salary
SELECT employee_id , last_name, salary, 
  NVL2(commission_pct, salary + (salary * commission_pct), salary) income
  FROM employees;

Example 3-26 Using the SQL CASE Function

-- CASE can compare a column or expression or search conditions, returning
-- a result when there is a match. CASE is similar to IF_THEN-ELSE logic.
-- In the following, the value of the hire_date column is compared against various
-- dates. When there is a match, the corresponding calculated result is returned, 
-- otherwise the default calculated salary is returned.
SELECT employee_id, hire_date , salary,
  CASE WHEN hire_date < TO_DATE('01-JAN-90') THEN salary*1.20
       WHEN hire_date < TO_DATE('01-JAN-92') THEN salary*1.15
       WHEN hire_date < TO_DATE('01-JAN-94') THEN salary*1.10 
       ELSE salary*1.05 END  "Revised Salary"
  FROM employees;

Example 3-27 Using the SQL DECODE Function

-- DECODE compares a column or expression to search values, returning a result
-- when there is a match. DECODE is similar to IF_THEN-ELSE logic.
-- In the following, the value of the job_id column is compared against PU_CLERK,
-- SH_CLERK, and ST_CLERK. 
-- When there is a match, the corresponding calculated result is returned,
-- otherwise the original salary is returned unchanged.
SELECT employee_id, job_id , salary,
  DECODE(job_id, 'PU_CLERK', salary*1.05,
                 'SH_CLERK', salary*1.10,
                 'ST_CLERK', salary*1.15,
                             salary) "Revised Salary"
  FROM employees;

Example 3-28 Using the SQL INSERT Statement to Add Rows to a Table

-- the following inserts data for all the columns in a row
INSERT INTO employees VALUES 
  (10, 'Enrique', 'Borges', 'enrique.borges', '555.111.2222', 
   '01-AUG-05', 'AC_MGR', 9000, .1, 101, 110);

-- the following inserts data into the columns specified by name
-- NULLs are inserted in those columns not explicitly named 
INSERT INTO employees (employee_id, last_name, email, hire_date, job_id, salary)
  VALUES (11, 'Doe', 'jane.doe', '31-AUG-05', 'SH_CLERK', 2400); 

-- the following shows the rows that were inserted 
SELECT employee_id, last_name FROM employees 
  WHERE employee_id = 10 or employee_id = 11;

Example 3-29 Using the SQL UPDATE Statement to Update Data in a Table

SELECT salary FROM employees WHERE employee_id = 11;

-- update the salary for employee 11, multiply the salary by 105%
UPDATE employees SET salary = salary * 1.05 WHERE employee_id = 11;

-- the following should show a change in salary
SELECT salary FROM employees WHERE employee_id = 11;

Example 3-30 Using the SQL DELETE Statement to Remove Rows From a Table

DELETE FROM employees WHERE employee_id = 10 OR employee_id = 11;

-- the following query should not find any records
SELECT * FROM employees WHERE employee_id = 10 OR employee_id = 11;

Example 3-31 Using the SQL COMMIT Statement to Save Changes

-- add a row and then update the data
INSERT INTO employees (employee_id, last_name, email, hire_date, job_id, salary)
  VALUES (12, 'Doe', 'john.doe', '31-AUG-05', 'SH_CLERK', 2400); 

UPDATE employees SET salary = salary*1.10 WHERE employee_id = 12;

-- commit (save) the INSERT and UPDATE changes in the database
COMMIT;

Example 3-32 Using the SQL ROLLBACK Statement to Undo Changes

-- delete a row (record)
DELETE FROM employees WHERE last_name = 'Doe';

-- rollback the delete statement because the previous DELETE was incorrect
ROLLBACK;

-- the following is valid
SELECT * FROM employees WHERE last_name = 'Doe';

Example 3-33 Creating a Simple Table Using SQL

-- create a simple table for keeping track of birthdays
CREATE TABLE my_birthdays
  ( first_name     VARCHAR2(20),
    last_name      VARCHAR2(25),
    bday_date      DATE
  ); 

Example 3-34 Creating a Table With NOT NULL Constraints Using SQL

-- create a table with NOT NULL constraints in the HR schema
CREATE TABLE personal_info (
    employee_id         NUMBER(6,0) NOT NULL,
    birth_date          DATE NOT NULL,
    social_security_id  VARCHAR2(12) NOT NULL,
    marital_status      VARCHAR2(10),
    dependents_claimed  NUMBER(2,0) DEFAULT 1,
    contact_name        VARCHAR2(45) NOT NULL,
    contact_phone       VARCHAR2(20) NOT NULL,
    contact_address     VARCHAR2(80) NOT NULL
);

Example 3-35 Adding, Altering, and Dropping a Table Column Using SQL

-- add a new column
ALTER TABLE personal_info ADD
(contact_email VARCHAR2(30) NULL);

-- modify a column
ALTER TABLE personal_info MODIFY
(contact_email VARCHAR2(40) NOT NULL);

-- drop a column
ALTER TABLE personal_info DROP COLUMN
contact_address;

Example 3-36 Creating, Altering, and Dropping Constraints Using SQL

-- add a primary key constraint 
ALTER TABLE personal_info 
  ADD CONSTRAINT personal_info_pkey 
  PRIMARY KEY (employee_id);

-- add a foreign key constraint
ALTER TABLE personal_info 
  ADD CONSTRAINT personal_info_fkey 
  FOREIGN KEY (employee_id) REFERENCES employees (employee_id) 
  ON DELETE CASCADE;

-- add a unique constraint
ALTER TABLE personal_info 
  ADD CONSTRAINT personal_info_unique_con 
  UNIQUE (social_security_id);

-- add a check constraint
ALTER TABLE personal_info 
  ADD CONSTRAINT personal_info_check_con 
  CHECK ( dependents_claimed > 0);

-- disable a constraint
ALTER TABLE personal_info 
  DISABLE CONSTRAINT personal_info_check_con;

-- enable a constraint
ALTER TABLE personal_info 
  ENABLE CONSTRAINT personal_info_check_con;

-- drop a constraint
ALTER TABLE personal_info 
  DROP CONSTRAINT personal_info_check_con;

Example 3-37 Renaming a Table Using SQL

-- rename the my_birthdays table
ALTER TABLE my_birthdays RENAME to birthdays; 

Example 3-38 Dropping a Table Using SQL

-- drop tables from the database
-- use caution when use the DROP statement!
DROP TABLE birthdays;
DROP TABLE personal_info;

Example 3-39 Creating, Modifying, and Dropping an Index Using SQL

-- create an index on a single column to make queries faster on that column
CREATE INDEX emp_hiredate_idx ON employees (hire_date);

-- rename the index
ALTER INDEX emp_hiredate_idx 
  RENAME TO emp_hire_date_idx;

-- drop the index
DROP INDEX emp_hire_date_idx;

-- create an index on two columns to make queries faster on the first column
-- or both columns
CREATE INDEX emp_mgr_id_ix ON employees (employee_id, manager_id);
DROP INDEX emp_mgr_id_ix;

-- a function-based index precalculates the result and speeds up queries that
-- use the function for searching or sorting, in this case UPPER(last_name)
CREATE INDEX emp_upper_last_name_ix ON employees (UPPER(last_name));
DROP INDEX emp_upper_last_name_ix;

Example 3-40 Creating a View Using SQL

-- create a view to display data from departments and employees
CREATE OR REPLACE VIEW my_emp_view AS
SELECT d.department_id, d.department_name,
  e.employee_id, e.first_name, e.last_name 
  FROM employees e 
  JOIN departments d ON d.manager_id = e.employee_id;

Example 3-41 Dropping a View Using SQL

-- drop the view with the DROP VIEW statement
DROP VIEW my_emp_view;

Example 3-42 Creating a Sequence Using SQL

-- create a new sequence to use with the employees table
-- this sequence starts at 1000 and increments by 1
CREATE SEQUENCE new_employees_seq START WITH 1000 INCREMENT BY 1;

-- to use the sequence, first initialize the sequence with NEXTVAL
SELECT new_employees_seq.NEXTVAL FROM DUAL;

-- after initializing the sequence, use CURRVAL as the next value in the sequence
INSERT INTO employees VALUES 
  (new_employees_seq.CURRVAL, 'Pilar', 'Valdivia', 'pilar.valdivia',
  '555.111.3333', '01-SEP-05', 'AC_MGR', 9100, .1, 101, 110);

-- query the employees table to check the current value of the sequence
-- which was inserted used as employee_id in the previous INSERT statement
SELECT employee_id, last_name FROM employees WHERE last_name = 'Valdivia';

Example 3-43 Dropping a Sequence Using SQL

-- drop the sequence
DROP SEQUENCE new_employees_seq;

Example 3-44 Creating a Synonym Using SQL

-- create a synonym for the employees table
CREATE SYNONYM emps for HR.employees;

-- query the employees table using the emps synonym
SELECT employee_id, last_name FROM emps WHERE employee_id < 105;

Example 3-45 Dropping a Synonym Using SQL

-- drop the synonym
DROP SYNONYM emps;