DBMS that manages data as collection of tables in which all data relationships are represented by common values in related tables
Structure that contains descriptions of objects created by a user, such as base tables, views, and constraints, as part of a DB
Data Definition Language
Commands used to define a DB, including those for creating, altering, and dropping tables and establishing constraints
Data Manipulation Language
Commands used to maintain and query a DB, including those for updating, inserting, modifying, and querying data
Data Control Language
Commands used to control a DB, including those for administering priviliges and committing (saving) data
Character (CHAR): stores string values containing any characters in a character set; fixed length.
Character Varying (VARCHAR or VARCHAR2): stores string values containing any characters in a character set but of definable variable length.
Binary Large Object (BLOB): stores binary string values in a hexadecimal format; variable length.
Numeric: stores exact numbers with a defined precision and scale.
Integer (INT): stores exact numbers with a predefined precision and scale of zero.
Time Stamp: stores a moment an event occurs, using a definable fraction-of-a-second precision; adjusted for user's time zone.
CREATE CHARACTER SET, CREATE COLLATION, CREATE TRANSLATION, CREATE ASSERTION, CREATE DOMAIN
Example of CREATE TABLE command
CREATE TABLE Customer_T (CustomerID NUMBER (11,0) NOT NULL, CustomerName VARCHAR2(25) NOT NULL, CustomerAddress VARCHAR2(30)...
ADD [COLUMN] column_definition
ALTER [COLUMN] column_name SET DEFAULT default_value
ALTER [COLUMN] column_name DROP DEFAULT
DROP [COLUMN] column_name [RESTRICT] [CASCADE]
Example of ALTER TABLE command
ALTER TABLE Customer_T
ADD COLUMN CustomerType VARCHAR2(2) DEFAULT "Commercial"
INSERT command example
INSERT INTO Customer_T VALUES
(001, 'Contemporary Casuals', '1355 S. Himes Blvd.', 'Gainesville', 'FL', 32601)
Entering Null Value
When inserting data into a DB and specifying only a few selected attributes to enter data into, while excluding the remainder of attribute fields
Lists the columns from based tables, derived tables, or views to be projected into the table that will be the results of the command (lists the data you want to display); necessary
Identifies that tables, derived tables, or views from which columns will be chosen to appear in the result table and includes the tables, derived tables, or views needed to join tables to process the query; necessary
Includes the conditions for row selection within the items in the FROM clause and the conditions between tables, derived tables, or views for joining; use the AND, OR , and NOT operators to customize conditions; optional
SELECT command example
SELECT ProductDescription, ProductStandardPrice FROM Product_T WHERE ProductStandardPrice < 275
Data Manipulation Commands
UPDATE, INSERT, DELETE, SELECT;
UPDATE, INSERT, DELETE allow you to modify tables, while SELECT allows you to query the data contained in the tables.
Virtual table that is created dynamically upon request by a user; not temporary, but its definition is stored in the system catalog and the contents of the view are materialized as a result of the SQL query that uses the view