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

Info icon This 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
Image of page 1

Info icon This 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
Image of page 2
CIS332 Notes SQL A datatype identifies the type of data Oracle 12c is expected to store in a column.
Image of page 3

Info icon This 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 ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern