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