SQL

If you want to access a _TBL from another user how would you specify it

USERNAME._TBL (where if it is in your schema it would be just _TBL)

BLOB

Binary large object like images or MP3s

Transactional control commands (general):

COMMIT (saves) ROLLBACK (undoes) SAVEPOINT (creates points within groups of transactions) SET TRANSACTION (places a name on transaction)

Eight fundamental data definition language (DDL) commands

CREATE TABLE ALTER TABLE DROP TABLE CREATE INDEX ALTER INDEX DROP INDEX CREATE VIEW DROP VIEW

Put in a constraint for PAY_RATE to be > 12.50

CREATE TABLE EMPLOYEE_PAY (EMP_ID CHAR(9) NOT NULL, POSITION VARCHAR2(15) NOT NULL, PAY_RATE NUMBBER(4,2) NOT NULL, CONSTRAINT CHK_PAY_RATE CHECK (PAY_RATE > 12.50));

Show two ways you can create a column the primary key

CREATE TABLE EMPLOYEE_TBL (EMP_ID CHAR(9) NOT NULL PRIMARY KEY, Or (column data, column data, column data, PRIMARY KEY (EMP_ID));

How to put in a unique constraint

CREATE TABLE EMPLOYEE_TBL (EMP_ID CHAR(9) NOT NULL PRIMARY KEY, EMP_NAME VARCHAR(40) NOT NULL, EMP_PHONE INTEGER(10) NULL UNIQUE); This means that each employee must have a unique phone number

Schema

Collection of database objects normally associated with one particular database username

LAN/WAN

Local Area Network/Wide Area Network

Primary Key

A column (field) that makes each row of data unique

Foreign Key

A column in a child table that references a primary key in the parent table

Code for Foreign Key Constraint

CONSTRAINT (TBL)_FK FOREIGN KEY (COLUMN) REFERENCES PARENT_TBL (COLUMN);

Code for Check Constraint

CONTRAINT CHK_(TBL_NAME)

CHAR

Constant length character

Three fundamental data manipulation language (DML) commands

INSERT UPDATE DELETE

What is the PC in the server/client relationship?

It is the client

Fields

Sub columns within a Table

You can create a copy of an existing table using a combination of the...

CREATE TABLE and SELECT statement

Check Constraint

Check the validity of the data into particular columns. Really to restrict values into a column.

TEXT

Large character string like HTML input from entries in a blog

Data administration commands (general):

START AUDIT STOP AUDIT

What does the * mean?

Selects data from all fields in the given table

The basic syntax of a TBL ends with

Semicolon as the last character

In creating a TBL the information concerning each column is:

Separated by a comma

Unique Constraints

Similar to a primary key in that each data value must be unique, but isn't actually for use as the primary key

Scale

number of digits allowed to the right of a decimal

What does DECIMAL (4,2) mean?

precision is 4 and scale is 2 so you can enter a total of 4 digits, with a constraint of 2 to the right. If you entered 12.229 into the database it would round to 12.23

Generally speaking character strings require

quotation marks, numeric does not

NULL

term to represent a missing value

CONNECT command

used to establish a database connection

data query language (DQL) base command (important)

SELECT

What is a database

collection of data

BOOLEAN value

used to make data comparisons, (TRUE, FALSE, NULL)

DISCONNECT command

SQL session is terminated

Data control language (DCL) fundamental commands:

ALTER PASSWORD GRANT REVOKE CREATE SYNONYM

Code to modify a table

ALTER TABLE TBL_NAME MODIFY CONSTRIANT (COLUMN) the full column again on what you are changing

Code to alter a table

ALTER TABLE TBL_NAME ADD/DROP CONSTRAINT (COLUMN) what you want to do

Implicit conversion

The process that converts a number into a numeric type

Why use varying length data for non-constant character strings?

To save database space. Ex: if you set CHAR(10) and you enter 5 places the remaining 5 will be spaces. If you did VARCHAR(10) and enter 5 places it will truly be 5.

What are the six main categories of SQL commands?

Data Definition Language (DDL) Data Manipulation Language (DML) Data Query Language (DQL) Data Control Language (DCL) Data administration commands Transactional control commands

Using NULL data type means that...

Data is not required in a particular field, if there is a chance that there might not always be data for a field use NULL

Using NOT NULL data type means that...

Data is required

DATE

Date and time values

Is the use of a suffix mandatory when naming database objects?

No, but may be preferred for maintaining control. For example using _TBL for tables so ORDERS_TBL, PRODUCTS_TBL, CUSTOMER_TBL...

NUMBER

Numeric value (decimal & non-decimal)

Brackets in creating the syntax of a TBL indicate:

Portions that are optional

Database administrator

Protector of the data

Records

Rows of data

What does SQL stand for?

Structured Query Language

Precision

Total length allowed for a decimal value

VARCHAR

Variable length character

SQL Session

an occurrence of a user interacting with a relational database through the use of SQL commands