{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

Notes.docx - CIS332 Notes Chapter 3 Table Creation and...

Info iconThis preview shows pages 1–4. Sign up to view the full content.

View Full Document Right Arrow Icon
CIS332 Notes SQL Chapter 3 Table Creation and Management Chapter 4 Constraints Chapter 5 Data Manipulation and Transaction Control Chapter 6 Additional Database Controls 181-224 Chapter 7 User Creation and Management 225-254 Chapter 8 Restricting Rows and Sorting Data 255-294 Chapter 9 Joining Data from Multiple Tables 295-344 Chapter 10 Selected Single-Row Functions 347-400 Chapter 11 Group Functions 401-448 Chapter 12 Subqueries and MERGE Statements 449-494 Load cleanup.sql and JLDB_BUILD_5.sql into SQL Developer. Run cleanup.sql run the sql second button Run JLDB_BUILD_5.sql 1
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
CIS332 Notes SQL Chapter 3 Table Creation and Management DDL , data definition language , commands are used to create or modify database tables. A database object is a defined, self-contained structure in Oracle 12c. Commands and Clauses Description Creating Tables CREATE TABLE Creates a new table in the database. The user names the columns and identifies the type of data to be stored. To view a table, use the command DESCRIBE. CREATE TABLE. . . AS Creates a table from existing database tables, using the AS clause and subqueries Modifying Tables ALTER_TABLE. . . ADD Adds a column to a table ALTER_TABLE. . .MODIFY Changes a column size datatype or default value ALTER_TABLE. . . DROP COLUMN Deletes one column from a table ALTER_TABLE. . . SET USED or ALTER_TABLE. . . SET UNUSED COLUMNS Marks a column for deletion at a later time DROP UNUSED COLUMNS Completes the deletion of a column previously marked with SET UNUSED RENAME…TO Changes a table name TRUNCATE TABLE Deletes all table rows, but the table name and columns structure remain Deleting Tables DROP TABLE Removes an entire tables from the Oracle 12c database PURGE TABLE Permanent deletes a table in the recycle bin Recovering Tables FLASHBACK TABLE. . .TO BEFORE DROP Recovers a dropped trable if PURGE option not used when table dropped. Naming tables and columns: Can be up to 30 characters Must begin with a letter No blank spaces Numbers, the underscore symbol (_), and the number sign (#) are allowed Unique table name Unique column names in each table Reserved words such as SELECT, DISTINCT, CAR, and NUMBER cannot be used Datatype Description n maximum contains VARCHAR2(n) variable-length 4000 bytes no default minimum value must be specified can contain up to nine letters numbers or symbols CHAR(n) Fixed-length default size is 1 2000 nine letters numbers or symbols NUMBER(p, s) Numeric p – precision s – scale right of decimal 38 99999.99 column defaults to a precision of 38 digits DATE date and time Between Jan 1 4712 BC and Dec 31 9999 AD seven bytes Century, year, month, day, hour, minute, and second of a date DD-MON-YY default 2 68 58 59 59 60
Background image of page 2
CIS332 Notes SQL A datatype identifies the type of data Oracle 12c is expected to store in a column.
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 4
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}