C993 - SQL

Naming Rules

-The length of the name must be at least 1 character and no more than 30 characters. -The first character in a name must be a letter. -After the first letter, names may include letters, numbers, the dollar sign ($), the underscore (_), and the pound sign (#), also known as the hash mark or hash symbol. No other special characters are allowed anywhere in the name. -Names are not case-sensitive. -Names cannot be reserved words that are set aside for use in SQL statements, such as the reserved words SELECT, CREATE, and so on. See the following complete list of reserved words from Oracle's SQL Language Reference Manual. These words are off-limits when you create names for your database objects. -While not recommended by Oracle, it is possible to create database object names that are "quoted."

Second normal form

1NF plus each data element is identified by one corresponding unique identifier—a primary key—that is not a composite and therefore cannot be subdivided into smaller bits of data

Third normal form

2NF plus all tables contain no data other than that which describes the intent of the primary key; extraneous data is placed in separate tables

Views

A "filter" through which you can search a table and interact with a table but that stores no data itself and simply serves as a "window" onto one or more tables. VIEW objects can be used to mask portions of the underlying table logic for various reasons—perhaps to simplify business logic or to add a layer of security by hiding the real source of information. A VIEW can be used to display certain parts of a table while hiding other parts of the same table.

Sequences

A counter, often used to generate unique numbers as identifiers for new rows as they are added to a table.

Explain the Relationship Between a Database and SQL

A database is a persistent store of information that continues—or persists—beyond the execution of a given application. SQL is the most widely used language for interacting with a database. SQL statements can be issued by any one or more applications to a single database. Those applications can be written in a variety of languages that support SQL calls, including software written in languages such as Java, PHP, and others.

Composite Primary Keys

A multicolumn PRIMARY KEY is based on two or more columns that collectively serve the same purpose as a single-column PRIMARY KEY

Namespace

A namespace is a logical boundary within the database that encompasses a particular set of database objects.

Schema

A schema is a collection of certain database objects, such as tables, indexes, and views, all of which are owned by a user account. You can think of a schema as being the same thing as a user account, but there is a slight difference—the user account houses the objects owned by a user, and the schema is that set of objects housed therein.

Roles

A set of one or more privileges that can be granted to a user.

Constraints

A small bit of logic defined by you to instruct a particular table about how it will accept, modify, or reject incoming data.

Tables

A structure that can store data. All data is stored in columns and rows. Each column's data type is explicitly defined.

In-line Constraint (3)

ALTER TABLE PORTS MODIFY PORT_ID PRIMARY KEY;

Synonyms

An alias for another object in the database, often used to specify an alternative name for a table or view.

Indexes

An object designed to support faster searches in a table. An INDEX performs much the same way as an index to a book, by copying a relatively small, select amount of information, sorting it for speedy reference, and tying it back to locations in the table for supporting quick lookups of rows in the source table.

Transaction Control Language SQL Examples

COMMIT ROLLBACK SAVEPOINT

Data Definition Language SQL Examples

CREATE ALTER (1) DROP RENAME TRUNCATE GRANT REVOKE FLASHBACK PURGE COMMENT

In-line Constraint (2)

CREATE TABLE PORTS (PORT_ID NUMBER CONSTRAINT PORT_ID_PK PRIMARY KEY, PORT_NAME VARCHAR2(20));

Out of Line Constraint (2)

CREATE TABLE PORTS (PORT_ID NUMBER, PORT_NAME VARCHAR2(20), CONSTRAINT PORT_ID_PK PRIMARY KEY (PORT_ID) );

Out of Line Constraint (1)

CREATE TABLE PORTS (PORT_ID NUMBER, PORT_NAME VARCHAR2(20), PRIMARY KEY (PORT_ID) );

In-line Constraint (1)

CREATE TABLE VENDORS (VENDOR_ID NUMBER, VENDOR_NAME VARCHAR2(20), STATUS NUMBER(1) NOT NULL, CATEGORY VARCHAR2(5));

RENAME

Changes the name of an existing database object

Data Definition Language (DDL)

Create, alter, and drop tables and other database objects Add comments on a particular object to be stored in the database and associated with that object Issue privileges to users to perform various tasks in the database Initiate performance analysis on objects using built-in tools

Describe the Purpose of DDL

DDL statements include CREATE, ALTER, and DROP. DDL statements are a subset of SQL and are used to create new database objects or alter the structure of existing database objects, including removing them.

COMMENT

DDL; Adds comments to the data dictionary for an existing database object

PURGE

DDL; Irrevocably removes database objects from the recycle bin

GRANT

DDL; Provides privileges, or rights, to user objects to enable them to perform various tasks in the database.

TRUNCATE

DDL; Removes all the rows—in other words, data—from an existing table in the database. TRUNCATE is something of a brute-force alternative to the DELETE statement, in that TRUNCATE gives up recovery options offered by DELETE in exchange for faster performance. These differences in approach are the reason TRUNCATE is categorized as DDL while DELETE is DML.

REVOKE

DDL; Removes privileges that have been issued with the GRANT statement

FLASHBACK

DDL; Restores an earlier version of a table or database

ALTER object_type

DDL; Used on an existing object in the database to modify that object's structure, name, or some other attribute.

CREATE object_type

DDL; Used to create a user, table, view, index, synonym, or other object in the database

DROP object_type

DDL; Used to remove a database object from the database that has already been created with the CREATE statement

Data Manipulation Language (DML)

DML refers to those statements in SQL that are used to work with data in the objects. DML statements are used to add, modify, view, and delete data in a database object, such as a table

Describe the Purpose of DML

DML statements include SELECT, UPDATE, and INSERT. DML statements are a subset of SQL and are used to work with existing database objects.

INSERT

DML; Adds data to a database table, either directly or, in some situations, through a view

SELECT

DML; Displays data of a database table or view

UPDATE

DML; Modifies existing data in a table, either directly or, in some situations, through a view

MERGE

DML; Performs a combination of INSERT, UPDATE, and DELETE statements in a single statement

DELETE

DML; Removes existing data from a table, either directly or, in some situations, through a view

FOREIGN KEY Constraints

It identifies one or more columns in the current table; let's call this the child table. For each of those columns, it also identifies one or more corresponding columns in a second table; let's call this the parent table. It ensures that the parent table already has either a PRIMARY KEY or UNIQUE constraint on those corresponding columns. It then ensures that any future values added to the FOREIGN KEY-constrained columns of the child table are already stored in the corresponding columns of the parent table.

Data types

Most data types fall under one of the general categories of numeric, character, or date.In addition to these three is a category referred to as large object, or LOB, data types.

First normal form

No repeating groups; all tables are two-dimensional

CONSTRAINTS Types

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

Data Manipulation Language SQL Examples

SELECT INSERT UPDATE DELETE MERGE

SAVEPOINT

TCL; Marks a position in a session to prepare for a future ROLLBACK to enable that ROLLBACK to restore data at a selected point in a session other than the most recent commit event

COMMIT

TCL; Saves a set of DML modifications performed in the current database session

ROLLBACK

TCL; Undoes a set of DML modifications performed during the current database session

Schema Objects

Tables Constraints Indexes Views Sequences Private synonyms

Users

The "owners" of database objects

CHECK limitations

The CHECK condition cannot include references to the following: Columns in other tables (note that other columns in the same table are accepted) Pseudocolumns CURRVAL, NEXTVAL, LEVEL, or ROWNUM Subqueries and scalar subquery expressions User-defined functions Certain functions whose value cannot be known at the time of the call: SYSDATE, SYSTIMESTAMP, CURRENT_DATE, CURRENT_TIMESTAMP, DBTIMEZONE, LOCALTIMESTAMP, SESSIONTIMEZONE, UID, USER, and USERENV

Identify the Connection Between an ERD and a Relational Database

The ERD is a logical model of an existing business. The entities in an ERD are the "things" that form the basis of a business information system and that are transformed into tables in a database. The ERD is a logical representation of a business; the relational database is the physical model in which actual data can be housed and processed in support of the business process.

Build a SELECT Statement to Retrieve Data from an Oracle Database Table

The SELECT statement is the most commonly used SQL statement. SELECT can be used to query data from a single table or join data together in two or more tables to return data that is logically connected. SELECT can be used with functions and other capabilities to transform data in various ways, which is discussed at length later in this book.

DATE

This accepts date and time information. The fields stored include year, month, date, hour, minute, and second.

TIMESTAMP(n) WITH TIME ZONE

This is a variation of TIMESTAMP that adds either a time zone region name or an offset for time zone. TIMESTAMP WITH TIME ZONE is used in tracking date information across different time zones and geographical areas. The range for n is 0-9. If n is omitted, it defaults to a value of 6.

Namespace rules

USER and ROLE objects are in their own collective namespace. PUBLIC SYNONYM objects are in their own namespace. TABLE, VIEW, SEQUENCE, PRIVATE SYNONYM, and user-defined TYPE objects have their own collective unique namespace within a given schema. (Note that I haven't previously mentioned user-defined types. They aren't on the exam. I include them in this discussion to be complete, but you won't need to understand them for the exam.) INDEX objects have their own namespace within a given schema. CONSTRAINT objects have their own namespace within a given schema.

Nonschema Objects

Users Roles Public synonyms

ON DELETE

What should be the behavior when rows are removed from the parent table for which there is a child row in a child table?

referential integrity

a FOREIGN KEY constraint on the child table, along with the PRIMARY KEY constraint on the parent table, enforces referential integrity between the two tables. This means that the constraints work to ensure that any future data that is added to one or both of the tables continues to support the ability to relate data from one table to another.

Unicode

a character set that serves as an alternative to ASCII and represents a more universal standard that supports all major languages more easily than the other implementations in use today

TIMESTAMP(n) WITH LOCAL TIME ZONE

a variation of TIMESTAMP. The TIMESTAMP WITH LOCAL TIME ZONE differs from TIMESTAMP WITH TIME ZONE in that the time zone offset is not stored with the column's value, and the value retrieved is sent to the user in the user's local session time zone; End users see the time in their own local time zone, regardless of the time zone where the database server happens to reside

VARCHAR2(n)

abbreviation for "variable character"; a variable-length alphanumeric value; The n indicates the maximum allowable length of the value stored within, but contrary to CHAR, the VARCHAR2 format will not pad its values with blanks; requires n to be specified

NULL

absence of information

NCLOB

accepts CLOB data in Unicode;

NUMBER(n,m)

accepts numeric data, including zero, negative, and positive numbers, where n specifies the "precision," which is the maximum number of significant digits (on either side of the decimal point), and m is the "scale," meaning the total number of digits to the right of the decimal point. Both n and m are optional; if both are omitted, both default to their maximum values. If n is specified and m is omitted, m defaults to zero. The value for n can range from 1 to 38; the value for m can range from -84 to 127.

TIMESTAMP(n)

an extension of DATE that adds fractional second precision. TIMESTAMP stores year, month, day, hours, minutes, seconds, and fractional seconds. The value for n specifies the precision for fractional seconds. The range for n is 0-9. If n is omitted, it defaults to a value of 6.

CHECK

attaches an expression to a constraint. In other words, it applies a small bit of code to define a particular business rule on incoming rows of data. A CHECK constraint may, for example, restrict incoming data so that all incoming values are required to be greater than some minimum value or fall within a set of predetermined optional values. A CHECK constraint can ensure that a two-character column accepts only valid abbreviations for American states, for example, or that the date entered in one column in a table is always greater than the date entered in another column in the same table. Any CHECK constraint present must evaluate to either TRUE or unknown, due to a NULL.

BLOB

binary large object; accepts large binary objects, such as image or video files; declaration is made without precision or scale

Large Object Data Type

cannot be primary keys; cannot be used with DISTINCT, GROUP BY, ORDER BY, or joins; include BLOB, CLOB, and NCLOB

CLOB

character large object; accepts large text data elements; declaration is made without precision or scale

FOREIGN KEY

constraint applies to one or more columns in a particular table and works in conjunction with a referred table's PRIMARY KEY constraint. A FOREIGN KEY is the feature that helps ensure that two tables can "relate" to each other

PRIMARY KEY

defines one or more columns in a table that will form the unique identifier for each row of data that is added to the table. The PRIMARY KEY constraint is a combination of the NOT NULL and UNIQUE constraints; A table may have only one PRIMARY KEY constraint

Character data types

known as text or string data types and they include CHAR(n) and VARCHAR2(n)

DESCRIBE

often abbreviated as DESC; a SQL*Plus statement that is unique to Oracle; they don't require a semicolon at the end; Once you have created a table successfully in the database, you can review the table's structure with the DESCRIBE statement

CHAR(n)

short for "character"; a fixed-length alphanumeric value; Any alphanumeric character is accepted as input; The n indicates how long the value will be; accepts valid input and pads any remaining unused space with blanks to ensure that the length of your value will always equal the value of n; When declaring a CHAR data type, the inclusion of n is optional; if it is omitted in the declaration, a value of 1 is assumed

NLS_DATE_FORMAT

specifies the default date format for your database; DD-MON-RR, where DD is the two-digit day, MON is the three-letter abbreviation for the month, and RR is the two-digit year

Transaction Control Language (TCL)

statements used to manage the changes made by DML statements

INTERVAL DAY(n1) TO SECOND(n2)

stores a span of time defined in days, hours, minutes, and seconds, where n1 is the precision for days, and n2 is the precision for seconds. The range of values for n1 is 0-9, and the default is 2. The value for n1 specifies how many digits are accepted in declaring the size of a number for DAY to be specified. The value for n2 is the fractional seconds precision for SECOND; the range for acceptable values is 0-9, and the default is 6

INTERVAL YEAR(n) TO MONTH

stores a span of time defined in only year and month values, where n is the number of digits used to define the YEAR value; the default for n is 2

UNIQUE

when applied to a column, ensures that any data added to the column in the future will be unique when compared to data already existing in the column. No other row will possess the same value for that particular column.

NOT NULL

when applied to a column, it ensures that for any row that is added to the TABLE, the column on which the NOT NULL constraint is applied will always be provided with a value; The NOT NULL constraint cannot be created out of line! (ALTER... MODIFY = YES, ALTER... ADD = NO)