Database Design SQL

database table

a database object, stores data for the database, consists of columns and rows, created and modified through data definition language (DDL) commands

Table and column names

can contain a maximum of 30 characters, no blank spaces, must begin with a letter, can contain numbers/underscore/number sign, must be unique, no reserved words are allowed

Data types

VARCHAR2, CHAR, NUMBER, DATE

defining columns

column definition list must be enclosed in parenthesis, datatype must be specified for each column, maximum of 1000 columns

data dictionary

typical component of a DBMS that maintains information about database objects, can query to verify all the tables that exist in your schema

USER_TABLES

data dictionary object that maintains information regarding all your tables

DESCRIBE

displays the structure of a specified table

modifying tables

accomplished through the ALTER TABLE command, use an ADD clause to add a column, use a MODIFY clause to change a column, use a DROP COLUMN to drop a column

modification guidelines

columns must be as wide as the data it already contains, if a NUMBER column already contains data size cannot be decreased, adding or changing default data does not affect existing data

ALTER TABLE and DROP command

can only reference one column per execution, deletion is permanent, cannot delete last remaining column in a table

RENAME TO

used to rename a table, the old name is no longer valid

TRUNCATE TABLE

command that deletes rows, structure of table remains

DROP TABLE

command that deletes table structure and contents

constraints

rules used to enforce business rules, practices, and policies; rules used to ensure accuracy and integrity of data

constraint types

PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL

creating constraints

when: during table creation, after table creation by modifying existing table

how: column level approach, table level approach

creating constraints column level approach

the constraint applies to the column specified

creating constraints table level approach

approach that cannot be used to create any constraint type except NOT NULL, required if constraint is based on multiple columns

enforcement of constraints

all constraints are enforced at the table level, if a data value violates a constraint the entire row is rejected

adding constraints to existing tables

constraints added with the ALTER TABLE command, add a NOT NULL constraint using the MODIFY clause, all other constraints added using ADD clause

PRIMARY KEY constraint

ensures that columns do not contain duplicate or NULL values, only one per table is allowed

FOREIGN KEY constraint

requires a value to exist in the referenced column of another table, NULL values are allowed, enforces referential integrity, maps to the primary key in the parent table

UNIQUE constraint

no duplicates are allowed in the referenced column, NULL values are permitted

CHECK constraint

updates and additions must meet specified condition

NOT NULL constraint

a special CHECK constraint, can only be created at column level, included in output of DESCRIBE command, can only be added to an existing table using ALTER TABLE MODIFY command

USER_CONSTRAINTS

displays constraint listing for a specific table

INSERT command

command used to add rows to existing tables; identify table in the insert into clause, specify the data in the values clause, can only add one row at a time to a table, enclose nonnumeric data in single quotes

inserting NULL value

omit column name from insert into clause column list, substitute two single quotation marks, use NULL keyword

ON NULL clause

option with a default setting for empty values

constraint violations

when you add or modify table data, the data is checked for compliance with any applicable constraints

activating the default option

include a column list in the INSERT statement ignoring the column to use the default option; use the default keyword as the value for the column

subquery

when inserting data from an existing table into a different table, the section that uses SELECT and FROM

UPDATE command

modify existing rows; can add values to an existing row by replacing null values or change existing values; SET clause identifies columns being changed and new values

substitution variables

prompts user for a value; identified by ampersand & preceding variable name, can be used to create interactive scripts;

UPDATE customers

SET region = ‘&Region’

WHERE state = ‘&State’;

DELETE command

command that removes a row from a table; omitting the WHERE clause removes all the rows;

DELETE FROM

WHERE;

transaction control statements

results of data manipulation language (DML) are not permanently updated to a table until explicit or implicit COMMIT occurs; these statements can commit data through COMMIT command, undo data changes through ROLLBACK command

COMMIT command

explicit commit occurs by executing commit; implicit commit occurs when DDL command is executed or user properly exits system; permanently updates tables and allows other users to view changes

WHERE clause

clause used to retrieve rows based on a stated condition; requires column name, comparison operator, value or column for comparison; values are case sensitive; enclose nonnumeric data in single quotes

comparison operators

operators that indicate how the data should relate to the given search value; =, >,

BETWEEN AND operator

finds values in a specified range

IN operator

operator that returns records that match a value in a specified list; list must be in parentheses, values separated by commas

LIKE operator

operator that performs pattern searches, used with wildcard characters; underscore _ for exactly one character in the indicated position; percent sign % represents any number of characters

logical operators

operators used to combine conditions; NOT, AND, OR

multiple logical operator order

resolved in order of NOT, AND, OR; use parenthesis to override the order of evaluation

resolving multiple types of operators

1. Arithmetic operators
2. Comparison operators
3. Logical operators

IS NULL operator

operator to show absence of data; cannot use = NULL since it will not return any rows

ORDER BY clause

clause that presents data in sorted order; ascending order is default, use DESC keyword to override column default; 255 columns maximum

ORDER BY clause sort sequence

in ascending order, values will be listed in following sequence: numeric values, character values, NULL values; the opposite in descending order

join

used to link tables and reconstruct data in a relational database; can be created through conditions in a WHERE clause or use of JOIN keywords in FROM clause

equality join

links rows through equivalent data that exists in both tables; created with equivalency condition in WHERE clause or NATURAL JOIN…USING or JOIN…ON in FROM clause

n-1

The number of join conditions is determined by the number of tables minus one

JOIN…USING

use this when tables have one or more columns in common

JOIN…ON

use when same named columns are not involved, or a condition is needed to specify a relationship other than equivalency

JOIN

use in order to free the WHERE clause for exclusive use in restricting rows

non-equality join

in WHERE clause use any comparison operator other than the equal sign, in FROM clause use JOIN…ON keywords

self-join

used to link a table to itself; requires the use of table aliases, requires the use of column qualifier

outer join

use to include rows that do not have a match in the other table; in WHERE clause, include this operator (+) immediately after the column name of the table with the missing rows to add NULL rows

set operators

UNION, UNION ALL, INTERSECT, MINUS

UNION

returns the results of both queries and removes duplicates

UNION ALL

returns results of both queries but includes duplicates

INTERSECT

returns only the rows included in the results of both queries

MINUS

subtracts the second query’s results if they are also returned in the first query’s result

function

predefined block of code that accepts arguments

single-row function

returns one row of results for each record processed

multiple-row function

returns one result per group of data processed

case conversion funcitons

UPPER, LOWER, INITCAP; functions that alter the case of data stored in a column or character string

character manipulation functions

SUBSTR, INSTR, LENGTH, LPAD, RPAD, LTRIM, RTRIM, REPLACE, TRANSLATE, CONCAT; manipulate data by extracting substrings, counting number of characters, replacing strings, etc

numeric functions

ROUND, TRUNC, MOD, ABS, POWER

date functions

MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, TO_DATE, ROUND, TRUNC, CURRENT_DATE

regular expressions

REGEXP_LIKE, REGEXO_SUBST

other functions

NVL, NVL2, NULLIF, TO_CHAR, DECODE, CASE, SOUNDEX, TO_NUMBER

group function

returns one result per group of rows processed (same as multiple); all these functions ignore NULL values except COUNT(\*)

GROUP BY clause

clause used to group data; must be used for any individual column in the SELECT clause with a group function; cannot reference column aliases

HAVING clause

clause that serves as the WHERE clause for grouped data

order that the clauses are evaluated

in SELECT function; WHERE, GROUP BY, HAVING