Sql Fundamentals Ii (Must Know Select Statements Prior)

Front 1

Here is an example table

Back 1

CREATE TABLE employees
( employee_id NUMBER(6)
CONSTRAINT emp_employee_id PRIMARY KEY
, first_name VARCHAR2(20)
, last_name VARCHAR2(25)
CONSTRAINT emp_last_name_nn NOT NULL
, email VARCHAR2(25)
CONSTRAINT emp_email_nn NOT NULL
CONSTRAINT emp_email_uk UNIQUE
, phone_number VARCHAR2(20)
, hire_date DATE
CONSTRAINT emp_hire_date_nn NOT NULL
, job_id VARCHAR2(10)
CONSTRAINT emp_job_nn NOT NULL
, salary NUMBER(8,2)
CONSTRAINT emp_salary_ck CHECK (salary>0)
, commission_pct NUMBER(2,2)
, manager_id NUMBER(6)
, department_id NUMBER(4)
CONSTRAINT emp_dept_fk REFERENCES
departments (department_id));

Front 2

Name some common constraints

Back 2

Constraint Description:
NOT NULL
Specifies that the column cannot contain a null value

UNIQUE Specifies a column or combination of columns whose values must be unique for all rows in the table

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

CHECK Specifies a condition that must be true

Front 3

Create a table with an employee ID 8 numbers long and a hire date that is from the system server that day.

Back 3

CREATE TABLE hire_dates (id NUMBER(8), hire_date DATE DEFAULT SYSDATE);

Front 4

descripbe this table.........
CREATE TABLE dept (deptno NUMBER(2),
dname VARCHAR2(14),
loc VARCHAR2(13),
create_date DATE DEFAULT SYSDATE);

Back 4

DESCRIBE dept

Front 5

how does the NUMBER function work.

Back 5

NUMBER (#ofDigits,#ofDecimals)
Example...create table empID
id NUMBER(9,2);
....a number that is 9 digits and 2 decimals.

Front 6

INTERVAL YEAR [(year_precision)] TO MONTH

Back 6

Examples
INTERVAL '123-2' YEAR(3) TO MONTH
Indicates an interval of 123 years, 2 months

Front 7

Interval example that Indicates 4 days, 5 hours, 12 minutes, 10 seconds, and 222 thousandths of a second.

Back 7

INTERVAL '4 5:12:10.222' DAY TO SECOND(3)

Front 8

INTERVAL that
Indicates 4 days, 5 hours, and 12 minutes

Back 8

INTERVAL '4 5:12' DAY TO MINUTE

Front 9

CREATE TABLE time_example3
(day_duration INTERVAL DAY (3) TO SECOND);

INSERT INTO time_example3 (day_duration)
VALUES (INTERVAL '180' DAY(3));

SELECT sysdate + day_duration "Half Year"
FROM time_example3; -

Back 9

gives the output -today’s date is 17-Mar-2009

Front 10

Example using a foreign key....in this example there are two tables and both have a 'department_id' field

Back 10

CREATE TABLE employees(
employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(25),
salary NUMBER(8,2),
hire_date DATE NOT NULL,
.......
department_id NUMBER(4),
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments(department_id),
CONSTRAINT emp_email_uk UNIQUE(email));

Front 11

create a CHECK constraint that the employees salary is greater than 0.

Back 11

CREATE TABLE employees
(...
salary NUMBER(8,2) CONSTRAINT emp_salary_min
CHECK (salary > 0),

Front 12

example of a completed table with multiple CONSTRAINTS

Back 12

CREATE TABLE employees
( employee_id NUMBER(6)
CONSTRAINT emp_employee_id PRIMARY KEY
, first_name VARCHAR2(20)
, last_name VARCHAR2(25)
CONSTRAINT emp_last_name_nn NOT NULL
, email VARCHAR2(25)
CONSTRAINT emp_email_nn NOT NULL
CONSTRAINT emp_email_uk UNIQUE
, phone_number VARCHAR2(20)
, hire_date DATE
CONSTRAINT emp_hire_date_nn NOT NULL
, job_id VARCHAR2(10)
CONSTRAINT emp_job_nn NOT NULL
, salary NUMBER(8,2)
CONSTRAINT emp_salary_ck CHECK (salary>0)
, commission_pct NUMBER(2,2)
, manager_id NUMBER(6)
, department_id NUMBER(4)
CONSTRAINT emp_dept_fk REFERENCES
departments (department_id));

Front 13

Create a table rows of another table....(create a table using a subquery)

Back 13

CREATE TABLE dept80
AS  SELECT employee_id, last_name,
salary*12 ANNSAL,
hire_date FROM employees WHERE department_id = 80;

Front 14

Create a View called EMPVU80, which contains details of the 'employees' table in department 80:

Back 14

CREATE VIEW empvu80
AS
SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;

Front 15

Modify the EMPVU80 view by using a CREATE OR REPLACE VIEW clause. Add an alias for each column name:

Back 15

CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS SELECT employee_id, first_name, last_name, salary, department_id
FROM employees
WHERE department_id = 80;

Front 16

You cannot remove a row or update a row if the view contains the following:

Back 16

Group functions
A GROUP BY clause
The DISTINCT keyword
The pseudocolumn ROWNUM keyword.
the distinct keyword, and NOT NULL are in the base table an not selected

Front 17

with CREATE VIEW viewname AS
SELECT 'you can create almost any complex select statement as learned in previous chapters ';

Back 17

however when deleting, Dropping, or sometimes modifying you will get errors with child directories, or permissions.

Front 18

WITH CHECK OPTION is for views. what does it do?

Back 18

The WITH CHECK OPTION clause specifies that INSERTs and UPDATEs performed through the view cannot create rows that the view cannot select, and therefore it enables integrity constraints and data validation checks to be enforced on data being inserted or updated.

Front 19

example of a view WITH CHECK OPTION that prevents any inserts or updates to the department_id.

Back 19

CREATE OR REPLACE VIEW empvu20
AS SELECT *
FROM employees
WHERE department_id = 20
WITH CHECK OPTION CONSTRAINT empvu20_ck ;

Front 20

the WITH READ ONLY view option allows no changes to the table. In this example column headings from the 'employees' table in department 80 are also given nick names

Back 20

CREATE OR REPLACE VIEW empvu10
(employee_number, employee_name, job_title)
AS SELECT employee_id, last_name, job_id
FROM employees
WHERE department_id = 10
WITH READ ONLY ;

Front 21

Create a sequence

Back 21

CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];

Front 22

Sequences are used to give numbers to items....for instance department numbers assigned to department names....or employee numbers generated for employee names.....example on next slide

Back 22

CREATE SEQUENCE sequence_dept_80
INCREMENT BY 1, NOMAXVALUE, NOCYCLE, CACHE 50;

Front 23

NEXTVAL and CURRVAL Pseudocolumns

Back 23

After you create your sequence, it generates sequential numbers for use in your tables. Reference the sequence values by using the NEXTVAL and CURRVAL

Front 24

When you reference a sequence.CURRVAL, the last value returned to that user’s process is displayed.

Back 24

The CURRVAL pseudocolumn is used to refer to a sequence number that the current user has just generated.

Front 25

Insert a new department named “Support” in location ID 2500:

Back 25

INSERT INTO departments(department_id,
department_name, location_id)
VALUES (dept_deptid_seq.NEXTVAL,
'Support', 2500);

Front 26

View the current value for the DEPT_DEPTID_SEQ sequence:

Back 26

SELECT dept_deptid_seq.CURRVAL
FROM dual;

Front 27

modify a sequence

Back 27

ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE;

Front 28

Indexes speed up searches. create an index for the employees table where a last_name search will be expedited

Back 28

CREATE INDEX emp_last_name_idx
ON employees(last_name);

Front 29

delete an index called emp_last_name_idx;

Back 29

DROP INDEX emp_last_name_idx;
**note: in real life To drop an index, you must be the owner of the index or have the DROP ANY INDEX privilege.

Front 30

Create a public or private synonym, when typing the entire name something in a search is tedious.

Back 30

CREATE [PUBLIC] SYNONYM synonym
FOR object;

Front 31

create a synonym for dept_sum_vu users do not want to type this in for every search

Back 31

CREATE SYNONYM d_sum
FOR dept_sum_vu;

Front 32

delete the d_sum synonym you just created

Back 32

DROP SYNONYM d_sum;

Front 33

The database administrator can create a public synonym that is accessible to all users. The following example creates a public synonym named DEPT for Alice’s DEPARTMENTS table or view:

Back 33

CREATE PUBLIC SYNONYM dept
FOR alice.departments;
Synonym created.

Front 34

Viewing the Data Dictionary (the dictionary is a descriptive table of other Views available to you)...V$, DBA, USER, ALL, are all views

Back 34

DESCRIBE DICTIONARY;.....

or try the code below...

SELECT *
FROM dictionary
WHERE table_name = 'USER_OBJECTS';

Front 35

Comments can be added to queries. You can write queries to search the COMMENTS column for a word or phrase

Back 35

SELECT table_name
FROM dictionary
WHERE LOWER(comments) LIKE '%columns';

Front 36

Any name listed in the data dictionary must be searched for in ____ ____

Back 36

UPPER CASE

Front 37

USER OBJECTS VIEW example

Back 37

SELECT object_name, object_type, created, status
FROM user_objects
ORDER BY object_type;

Front 38

The CAT View

Back 38

This view contains only two columns: TABLE_NAME and TABLE_TYPE. It provides the names of all your INDEX, TABLE, CLUSTER, VIEW, SYNONYM, SEQUENCE, or UNDEFINED objects.

Front 39

Column Information view example

Back 39

SELECT column_name, data_type, data_length,
data_precision, data_scale, nullable
FROM user_tab_columns
WHERE table_name = 'EMPLOYEES';

Front 40

constraint infromation view

Back 40

SELECT constraint_name, constraint_type,
search_condition, r_constraint_name,
delete_rule, status
FROM user_constraints
WHERE table_name = 'EMPLOYEES';

Front 41

Describe User_sequences;

detailed view on next slide

Back 41

SELECT sequence_name, min_value, max_value,
increment_by, last_number
FROM user_sequences;

Front 42

DESCRIBE user_synonyms

Back 42

SELECT *
FROM user_synonyms;

Front 43

Adding Comments to a Table

Back 43

COMMENT ON TABLE employees
IS 'Employee Information';
Comment created.

Front 44

The comment is stored in the data dictionary and can be viewed in one of the following data dictionary views in the COMMENTS column:
ALL_COL_COMMENTS
USER_COL_COMMENTS
ALL_TAB_COMMENTS
USER_TAB_COMMENTS

Back 44

COMMENT ON TABLE table | COLUMN table.column
IS 'text';

Front 45

summary of all Dictionary views

Back 45

DICTIONARY
USER_OBJECTS
USER_TABLES
USER_TAB_COLUMNS
USER_CONSTRAINTS
USER_CONS_COLUMNS
USER_VIEWS
USER_SEQUENCES
USER_TAB_SYNONYMS