Database Administration ISQA 436

Database AdministrationISQA 436 SQL Review, Oracle Objects and Data Types, SQL*Plus

SQL • Structured Query Language • Declaritive language vs. procedural • Three types of SQL statements • Data Manipulation Language (DML) • SELECT, INSERT, UPDATE, DELETE • Data Definition Language (DDL) • CREATE/ALTER/DROP TABLE, VIEW, INDEX • Data Control Language (DCL) • GRANT, REVOKE

Data Manipulation Commands (DML) • SELECT • Retrieve data from the database • INSERT • Add new rows to the database • UPDATE • Modify data in the database • DELETE • Remove rows from the database

SELECT • SELECT, columns, FROM and table names are mandatory. The rest are optional. • SELECT * will select all columns. SELECT col1, col2, …FROM table1, table2, … [ WHERE search_condition AND search_condition OR search_condition] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ]]

Joins • Joins are queries that select from two or more tables • A table may be joined with itself in a self-join • Tables are joined on one or more columns to combine similar rows from each • The join is done in the WHERE clause of the SELECT statement

Join - Example SELECT first_name, last_name, department_name FROM employees, departments WHERE employees.department_id = departments.department_id; FIRST_NAME LAST_NAME DEPARTMENT_NAME ------------ ------------ --------------- Nancy Greenberg Finance Daniel Faviet Finance John Chen Finance Ismael Sciarra Finance Jose Manuel Urman Finance Luis Popp Finance Shelley Higgins Accounting William Gietz Accounting

Join Example select a.first_name, a.last_name, b.last_name as manager from employees a, employees b where a.manager_id = b.employee_id order by b.last_name; FIRST_NAME LAST_NAME MANAGER ------------ ------------ ------------------------- William Smith Cambrault Elizabeth Bates Cambrault Sundita Kumar Cambrault Alexander Hunold De Haan Clara Vishney Errazuriz Danielle Greene Errazuriz

Subqueries • Subquery is a query within a query • Subqueries are necessary when a condition can’t be adequately defined in the WHERE clause • Example, find employee with the highest salary: select first_name, last_name, salary from employees where salary = (select max(salary) from employees);

Correlated Subquery • A correlated subquery joins the subquery (inner query) with the outer query. • Example, find employees making more than their department average: SELECT first_name, last_name, salary FROM employees a WHERE a.salary > (select avg(salary) FROM employees e, departments d WHERE e.department_id = d.department_id AND e.department_id = a.department_id);

Subquery Exists Example • Combine subquery with EXISTS to determine conditions of existence or non-existence • Find all products that have not sold: SELECT product_id, product_name FROM product_information p WHERE NOT EXISTS (SELECT order_id FROM order_items o WHERE o.product_id = p.product_id);

Inline View • Oracle provides an optimization to the subquery • The query can be included in the FROM clause as an inline view. SELECT first_name, last_name, salary FROM employees e, (SELECT avg(salary) avg_sal, d.department_id FROM departments d, employees e where d.department_id = e.department_id GROUP BY d.department_id) dept_avg WHERE e.salary > dept_avg.avg_sal AND e.department_id = dept_avg.department_id;

INSERT INSERT INTO table (col1, col2,…) VALUES (val1, val2, …) • UPDATE UPDATE table SET col1 = val1, col2 = val2, … WHERE [condition] • DELETE DELETE FROM table WHERE [condition]

INSERT • Two styles: with or without column names: • Style 1 – insert values in order of columns • as defined by CREATE TABLE – see object browser. • All columns must be included or NULL INSERT INTO item VALUES (21, 'Camoflage Pants', 'C', 'Khaki'); • Style 2 – indicate columns explicitly. (Not all columns need appear, but must match up with values) INSERT INTO item (itemno, itemname, itemtype) VALUES (22, 'Hunting Vest', 'C');

UPDATE • Modify data in a table • UPDATE locks rows during update • Without a WHERE clause, updates ALL rows! • Give all employees a 10% raise: UPDATE emp SET empsalary = 1.10*(empsalary); • Move all employees in Personnel to Marketing: UPDATE emp SET deptname = 'Marketing' WHERE deptname = 'Personnel';

DELETE • Remove entire rows from table • Again, without WHERE clause, deletes ALL rows! DELETE FROM employees; DELETE FROM employees WHERE employee_id = 195;

Three options for deleting a table • DELETE FROM emp; • Safest: Logs the delete transactions in a log file for undo • Does not delete the table • Does not reclaim space • TRUNCATE TABLE emp; • Faster than delete, does not log transactions • Does not delete the table • Reclaims space • DROP TABLE emp; • Fast, does not log deletions • Deletes the table as well as the rows • Reclaims all space

Data Definition Language (DDL) • CREATE • TABLE • VIEW • INDEX • ALTER • TABLE • VIEW • INDEX • DROP • TABLE • VIEW • INDEX

CREATE TABLE • Creates a base table CREATE TABLE table_name (col1_name col1_type DEFAULT constraints, col2_name col2_type DEFAULT constraints, …, PRIMARY KEY (pk_col1, pk_col2, …), FOREIGN KEY (fk_col1, fk_col2, …) REFERENCES ref_table (ref_col1, ref_col2,…), CHECK (check conditions));

Constraints • Primary key CONSTRAINT pk_stock PRIMARY KEY(stkcode) • Foreign key CONSTRAINT fk_stock_nation FOREIGN KEY(natcode) REFERENCES nation • Unique CONSTRAINT unq_stock_stkname UNIQUE(stkname) • Check CONSTRAINT item_color_cons CHECK (itemcolor IN ('White', 'Brown', 'Khaki'))

Index • An index is a sorted list of rows from a table • Only a subset of one or more columns is stored, along with the address of each row. • Data retrieval is much faster with an index. • Types of index • B-tree (most common) • bitmap • reverse • hash • cluster

Views - virtual tables • An imaginary table constructed by the DBMS when required – not a base table. • Only the definition of the view is stored, not the result • Usually can’t be updated except in special circumstances CREATE VIEW view_name (col1, col2, …) AS select statement;

Create View example CREATE VIEW emp_sal AS (SELECT first_name, last_name, salary FROM employees); SELECT * FROM emp_sal; FIRST_NAME LAST_NAME SALARY -------------------- ------------------------- ---------- Donald OConnell 2600 Douglas Grant 2600 Jennifer Whalen 4400 Michael Hartstein 13000 Pat Fay 6000 Susan Mavris 6500 Hermann Baer 10000

ORACLE DATABASE OBJECTS • Table • Index • View • Materialized View • Synonym • Sequence • Procedure • Function • Package • Trigger

Tables • Tables contain rows of data with a column for each datum. • Each column is of a specific data type • Columns without data are NULL • Each table has a primary key – a column that has unique values that identify rows • Columns may have foreign key references to a column in another table. The value in the foreign table must exist and be identical.

DUAL Table • ORACLE provides a special dummy table for using select on data not contained in a table, such as SYSDATE SELECT SYSDATE FROM DUAL; SYSDATE --------- 08-FEB-04 SELECT 'This is a test' AS message FROM DUAL; MESSAGE -------------- This is a test

DUAL Table • The DUAL table has just one row with one column, who’s value is ‘X’; SELECT * FROM DUAL; D - X

SYSDATE Function • The SYSDATE function returns the current date and time: SELECT SYSDATE FROM DUAL; SYSDATE --------- 08-FEB-04

ROWNUM Column • Every Oracle query has a pseudo-column named “rownum” • rownum can be used to limit output to a certain number of rows SELECT * FROM customers WHERE rownum < 5; CUST_ID CUST_FIRST_NAME CUST_LAST_NAME ---------- -------------------- -------------------- 10 Abigail Kessel 20 Abner Everett 30 Abraham Odenwalld 40 Absolom Sampson 4 rows selected.

Materialized Views • Oracle has an object called a materialized view. It is a view that actually contains data. • Data is updated periodically • Useful for derived data, such as statistical data

SYNONYMS • Synonyms are aliases for objects, a name that can be referred to without the schema name. • Synonyms are public or private • public = all users can use it • private = only the owner can use it • Example: CREATE PUBLIC SYNONYM employees FOR employees; • Now anyone can select from the employees table SELECT * FROM employees;

Sequence • A sequence generates a sequential integer value • Note – sequence numbers may be skipped. • Useful for serial numbers CREATE SEQUENCE customers_seq START WITH 1000 INCREMENT BY 1; • Use nextval to get the next incremental number INSERT INTO employees VALUES (employees_seq.nextval, 'John', 'Doe', 'jdoe', '555-1212', TO_DATE(SYSDATE), 'PU_CLERK', 2500, null, null, 30);

SCHEMA • Generally: A data model diagram • Oracle: a collection of database objects belonging to a single user • The schema has the name of the owner • Normally, only the owner has access to the schema • Other users can access using the user name prefix if they have been granted privileges: select * from hr.employees;

Miscellaneous SQL notes • In Oracle, SQL statements are terminated with a semi-colon ; • By convention, uppercase SQL reserved words and lowercase data-specific words (not mandatory): SELECT last_name FROM employees WHERE employee_id = 197; • Object names are not case-sensitive, but string literals are: SELECT employee_id FROM EMPLOYEES WHERE last_name = ‘Smith’;

ORACLE DATATYPES

ORACLE DATATYPES - NUMBER • NUMBER(p,s) • precision is number of digits to store • scale is number of digits to the right of the decimal place • p is an integer between 1 and 38 (inclusive) • s is an integer between -84 and 127 (inclusive) • s = 0 is a decimal integer • negative s pads zeroes to the left of the decimal point • specifying neither p nor s assumes maximum values

NUMBER EXAMPLES

ORACLE DATE • Dates and times are stored internally as a number in Oracle • To insert or select a date or time in a way that is readable, it has to be converted to or from a string using a date format. • Example: 09-FEB-2004 is in date format DD-MON-YYYY

ORACLE DATE • You can insert a date as a literal string • Use date format DD-MON-YYYY (day-month-year) • Can’t insert the time this way • Time will be 12:00:00 am INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id) VALUES (208, 'Brown', 'John', 'jbrown@yahoo.com', '28-JAN-2004', 'SA_REP');

ORACLE DATE • DATE type includes date and time information. • To insert a date, use the TO_DATE function and specify the date, time and date format as strings: TO_DATE(’08-FEB-2004’, ‘DD-MON-YYYY’) TO_DATE(’08-FEB-2004 13:44:00’, ‘DD-MON-YYYY HH24:MI:SS’) insert into employees (employee_id, first_name, last_name, email, hire_date, job_id) values (207, 'Brown', 'John', 'jb@yahoo.com', TO_DATE('27-JAN-2004', 'DD-MON-YYYY'), 'SA_REP');

ORACLE DATE • Likewise, when selecting a date from a table, you can display the date in different formats using the TO_CHAR function. select TO_CHAR(SYSDATE, 'DD/MM/YY HH:MI:SS PM') as "current time" FROM DUAL current time -------------------- 08/02/04 02:37:05 PM

DATE FORMAT ELEMENTS See Table 8-6 on page 224 of textbook for complete listing

DATE FORMAT ELEMENTS • Examples SELECT TO_CHAR(SYSDATE, 'DAY MONTH DD, YYYY HH:MI:SS PM') AS "current time" FROM DUAL; current time ---------------------------------------- SUNDAY FEBRUARY 08, 2004 04:38:05 PM SELECT TO_CHAR(SYSDATE, 'MM+DD+RR HH24:MI:SS') AS "current time" FROM DUAL; current time ----------------- 02+08+04 16:40:16

Oracle Date Functions • TO_CHAR converts date to a character string • TO_DATE converts a character string to a date • TRUNC returns date with no time (time is set to 00:00:00. Use this when you want to compare the date and not the time • ADD_MONTHS, returns date incremented by a specified number of monts • Addition: adding integers to a date adds that many days to the date See Table 8-5 on page 220 for complete list

TRUNC example insert into emp (first_name, last_name, email, job_id, hire_date) values ('Mark', 'Freeman', 'markf@pdx.edu', 50, '06-OCT-2004'); 1 row created. SELECT * FROM emp WHERE hire_date = SYSDATE; no rows selected SELECT * FROM emp WHERE hire_date = trunc(SYSDATE); FIRST_NAME LAST_NAME… -------------------- ------------------------- Mark Freeman… 1 row selected.

Date Arithmetic Examples SELECT SYSDATE FROM DUAL; SYSDATE --------- 06-OCT-04 SELECT ADD_MONTHS(SYSDATE, 3) FROM DUAL; ADD_MONTH --------- 06-JAN-05 SELECT SYSDATE + 7 AS "Next Week" FROM DUAL; Next Week --------- 13-OCT-04

PL/SQL Procedures • PL/SQL stands for procedural language. • A procedures is a group of SQL statements with programming logic • A function is a procedure that returns a value • A trigger is a procedure that fires when a database event occurs, such as an insert or update • A package is a named group of procedures and functions

SQL*Plus • Oracle’s command-line SQL interpreter • Two kinds of commands • SQL commands • SQL*Plus commands • SQL*Plus commands are special formatting and control commands that are not part of SQL • SQL*Plus commands are not case-sensitive and can be abbreviated

SQL*Plus Transactions • By default SQL*Plus does not run in autocommit mode. • DELETE’s, UPDATE’s and INSERT’s do not become permanent until a COMMIT statement is explicitely executed • This means you can use ROLLBACK to undo changes • If you end a session with out committing, Oracle will automatically rollback your changes • Autocommit can be enaabled with the SET AUTOCOMMIT ON comand

SQL*Plus Commands - DESCRIBE • DESCRIBE • Lists the description of an Oracle object • Can be abbreviated as ‘DESC’ desc move_ccuser PROCEDURE move_ccuser Argument Name Type In/Out Default? -------------- ----------------------- ------ -------- CCUSER VARCHAR2 IN TSPACE VARCHAR2 IN

SQL*Plus Commands - DESCRIBE DESCRIBE employees Name Null? Type ---------------- -------- ------------- EMPLOYEE_ID NOT NULL NUMBER(6) FIRST_NAME VARCHAR2(20) LAST_NAME NOT NULL VARCHAR2(25) EMAIL NOT NULL VARCHAR2(25) PHONE_NUMBER VARCHAR2(20) HIRE_DATE NOT NULL DATE JOB_ID NOT NULL VARCHAR2(10) SALARY NUMBER(8,2) COMMISSION_PCT NUMBER(2,2) MANAGER_ID NUMBER(6) DEPARTMENT_ID NUMBER(4)

Database Administration ISQA 436