[Oracle-Sql] C.4 Table Creation and Management

📋 This is my note-taking from what I learned in the class “Introduction To Database Concept”

Database Table

• A database object
• Stores data for the database
• Consists of columns and rows
• Created and modified through data definition language (DDL) commands

Table Design

Table and column names:

• Can contain a maximum 30 characters – no blank spaces
• Must begin with a letter
• Can contain numbers, underscore (_), and number sign (#)
• Must be unique
• No reserved words are allowed

Datatype	Description
VARCHAR2(n)	Variable-length character data, and the n represents the column’s maxiumum length. The maximum size is 4000 characters. There’s no default size for this datatype; a minimum value must be specified.
	Example: VARCHAR2(9) can contain up to nine letters, numbers, or symbols.
CHAR(n)	Fixed-length character column, and the n represents the column’s length. The default size is 1, and the maximum size is 2000.
	Example: CHAR(9) can contain nine letters, numbers, or symbols. However, if fewer than nine are entered, spaces are added to the right to force the data to reach a length of nine.
NUMBER(p,s)	Numeric column. The p indicates precision, the total number of digits to the left and right of the decimal position, to a maximum of 38 digits; the s, or scale, indicates the number of positions to the right of the decimal.
	Example: NUMBER(7,2) can store a numeric value up to 99999.99. If precision or scale isn’t specified, the column defaults to a precision of 38 digits.
DATE	Stores data and time between January 1, 4712 BC and December 31, 9999 AD. Seven bytes are allocated to the column to store the century, year, month, day, hour, minute, and second of a date. Oracle 11g displays the date in the format DD-MON-YY. Other aspects of a date can be displayed by using the TO_CHAR format. Oracle 11g defines the field width as seven bytes.

Extended Data Types

Introduced in Oracle 12c:

• Enables storage of additional data bytes in specific data types
• Database parameters must be enabled by an Oracle system administrator

Table Creation

CREATE TABLE [ Schema. ] tableanme
  ( columnname datatype [ DEFAULT value ]
  [ , columnname datatype [ DEFAULT value ]] );

JustLee Books’ ER Diagram

Defining Columns

• Column definition list must be enclosed in parentheses
• Datatype must be specified for each column
• Maximum of 1,000 columns

CREATE TABLE Command example

CREATE TABLE acctmanager
  ( amid CHAR(4),
    amfirst VARCHAR(12),
    amlast VARCHAR(12),
    amedate DATA DEFAULT SYSDATE,
    amsal NUMBER(8,2),
    amcomm NUMBER(7,2) DEFAULT 0,
    amearn AS (amsal + amcomm),
    region CHAR(2) );

Viewing List of Tables: USER_TABLES

• A data dictionary is a typical component of a DBMS that maintains information about database objects
• You can query the data dictionary to verify all the tables that exist in your schema
• The USER_TABLES data dictionary object maintains information regarding all your tables

SELECT table_name FROM user_tables;

Viewing Table Structures: DESCRIBE

• DESCRIBE displays the structure of a specified table

DESC acctmanager

Invisible Columns

• Oracle 12c allows hidden columns

CREATE TABLE test_invis
  ( col1 CHAR(1),
    col2 NUMBER(4) invisible );

Create Table Adding Values

-- Create Table
DROP TABLE summer_sales;
--> 만일 이미 만들었던 테이블을 삭제하고 다시 만들고 싶다면, Drop table first and then activate the table again!
CREATE TABLE summer_sales
(
SALESID NUMBER(5) PRIMARY KEY,
SPERSON VARCHAR(10),
SDATE DATE DEFAULT SYSDATE,
SCOMMISSION NUMBER(3) DEFAULT 5
);

SELECT * FROM summer_sales;
DESC summer_sales;
--> DESC: Structure of table(data)

-- Insert values
INSERT INTO summer_sales (SALESID, SPERSON) VALUES (1, 'ANNA');
INSERT INTO summer_sales (SALESID, SPERSON) VALUES (2, 'SEYEON');
INSERT INTO summer_sales (SALESID, SPERSON) VALUES (3, 'SHAI');

-- Check list of all the tables in your schema
SELECT * FROM user_tables;
--> user_tables: user defined table = system defined table
--> dual table is also system defined table!

SELECT COUNT(*) FROM user_tables;
--> give you num of tables

Table Creation through Sub-queries

• You can use sub-queries to retrieve data from an existing table
• Requires use of AS keyword
• New column names can be assigned
• In Oracle, a subquery is a query within a query. You can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause.

Master table → Transaction table:

SELECT * FROM books;
SELECT ISBN, TITLE, COST, CATEGORY FROM books;

CREATE TABLE new_book AS (SELECT ISBN, TITLE, COST, CATEGORY FROM books);
SELECT * FROM new_book;

RENAME new_book TO new_books;
SELECT * FROM new_books;
--> Now new_book is not working cuz I renamed new_book to new_books!!!

ALTER TABLE new_books ADD (SALESDATE DATE);
--> If inserted, the SALESDATE values will be null

ALTER TABLE new_books ADD (CURRENTSALES DATE DEFAULT SYSDATE);
--> If inserted, the CURRENTSALES values will be system default value

ALTER TABLE new_books DROP COLUMN SALESDATE;

ALTER TABLE new_books ADD (STUDENTDISCOUNT NUMBER(1));
ALTER TABLE new_books MODIFY (STUDENTDISCOUNT NUMBER(2));

SELECT * FROM new_books;

DESC new_books;

CREATE TABLE … AS

CREATE TABLE tablename [(columnname, ...)] AS (subquery);

CREATE TABLE … AS Command Example

CREATE TABLE cust_mkt AS (SELECT customer#, city, state, zip, referred, region FROM customers);

Modifying Existing Tables

• Accomplished through the ALTER TABLE command
• Use an ADD clause to add a column
• Use a MODIFY clause to change a column
• Use a DROP COLUMN to drop a column

ALTER TABLE Command Syntax

ALTER TABLE tablename ADD|MODIFY|DROP COLUMN columnname datatype;

ALTER TABLE … ADD Command

ALTER TABLE publisher ADD (ext NUMBER(4));

ALTER TABLE … MODIFY Command

ALTER TABLE acctmanager MODIFY (amlast VARCHAR(18));

Modification Rules for existing columns

• Column must be as wide as the data it already contains
• If a NUMBER column already contains data, size cannot be decreased
• Adding or changing default data does not affect existing data

ALTER TABLE … DROP COLUMN Command

• Can only reference one column per execution
• Deletion is permanent
• Cannot delete last remaining column in a table

ALTER TABLE publisher DROP COLUMN ext;

ALTER TABLE … SET UNUSED Command

• Once marked for deletion, a column cannot be restored
• Storage space is freed at a later time

ALTER TABLE tablename SET UNUSED (columnname);

#OR

ALTER TABLE tablename SET UNUSED COLUMN columnname;

ALTER TABLE … DROP UNUSED Command

• Frees up storage space from columns previously marked as unused

ALTER TABLE tablename DROP UNUSED COLUMNS;

Renaming a Table

• RENAME…TO is used to rename a table – the old name is no longer valid

RENAME cust_makt TO cust_mkt_092009;

Truncating a Table

TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove the table definition in addition to its data, use the DROP TABLE statement.

• TRUNCATE TABLE command – rows are deleted
• Structure of table remains

TRUNCATE TABLE cust_mkt_092009;

TRUNCATE TABLE new_books;

INSERT INTO new_books VALUES(12341, 'ORACLE', 50, 'COMPUTER', SYSDATE, 10);
INSERT INTO new_books VALUES(67887, 'DATASCIENCE', 50, 'COMPUTER', SYSDATE, 10);
INSERT INTO new_books VALUES(57963, 'PYTHON', 50, 'COMPUTER', SYSDATE, 10);

SELECT * FROM new_books;
DESC new_books;

How to delete data from sql table? → 테이블 끌어당기기

Deleting a Table

• DROP TABLE command – table structure and contents are deleted
• Shift + Delete → Permanent Delete

DROP TABLE cust_mkt_092009;

DROP TABLE without Purge Option

• Oracle 10g introduced a recycle bin
• Dropped tables can be recovered from the recycle bin

SELECT object_name, original_name FROM recyclebin;

FLASHBACK command

Use the FLASHBACK TABLE command to flash back table to a time before you performed the update to the HR. REGIONS table. SQL>flashback table hr. regions to timestamp to_timestamp(‘2016-08-11 07:30:00’, ‘YYYY-MM-DD HH:MI:SS’); Flashback complete.

You use a Flashback Query to retrieve data as it existed at some time in the past. The query explicitly references a past time using a timestamp or SCN. It returns committed data that was current at that point in time.

• The FLASHBACK command recovers a table from the recycle bin

FLASHBACK TABLE cust_mkt_092009 TO BEFORE DROP;

Use PURGE to Remove a Table from the Recycle Bin

PURGE table "BINGsfijewjqfleajfowe;fjdslafsdaf";

PURGE Option Available for DROP TABLE Command

• Using the PURGE option will permanently remove a table from the database
• The table will not be copied into the recycle bin

DROP TABLE cust_mktg_092009 PURGE;

ALTER SESSION SET RECYCLEBIN=ON;
--> Once recyclebin is on,

-- Drop table and check it in recyclebin
DROP TABLE new_books;
SELECT * FROM RECYCLEBIN;

-- Retrieve from recyclebin
FLASHBACK TABLE new_books;
TO BEFORE DROP;

SELECT * FROM new_books;

-- Permanent drop
DROP TABLE new_books PURGE;

Addtional Information

• Oracle Reserved Words → Reserved words cannot be in table
• AS is used for alias
• How to change the password

  -- To change the password
  ALTER USER COMP122_M23_vi_36 IDENTIFIED BY SEYEON;
  --> Now the password(username) is SEYEON, Not COMP122_M23_vi_36
  

• Scales number(4,2)

  -> 1000.00
  | 1000. | 00 |
  | 4     | 2  |
  

• Date Format on SQL Developer

Summary

• You create a table with the CREATE TABLE command
• Each column to be contained in the table must be defined in terms of the column name, data type, and for certain data types, the width
• A table can contain up to 1000 columns
• Each column name within a table must be unique
• You can change the structure of a table with the ALTER TABLE command
• Columns can be added, resized, and even deleted with the ALTER TABLE command
• Tables can be renamed with the RENAME…TO command
• To delete all the rows in a table, use the TRUNCATE TABLE command
• To remove both the structure of a table and all its contents, use the DROP TABLE command
• A dropped table is moved to the recycle bin and can be recovered using the FLASHBACK TABLE command
• Using the PURGE option in a DROP TABLE command permanently removes the table, meaning you cannot recover it from the recycle bin

C.4 Demo

-- Week 04 --

-- CREATE TABLE ACCTMANAGER
CREATE TABLE ACCTMANAGER
(
AMID CHAR(4),
AMFIRST VARCHAR2(12) NOT NULL,
AMLAST VARCHAR2(12) NOT NULL,
AMEDATE DATE DEFAULT SYSDATE,
REGION CHAR(2) INVISIBLE
);
--> REGION COLUMN IS INVISIBLE

-- CREATE TABLE 'CUST_MKT' BY SELECTING SPECIFIC COLUMNS FROM THE EXISTING 'CUSTOMERS' TABLE
CREATE TABLE CUST_MKT AS (SELECT CUSTOMER#, CITY, STATE, ZIP, REFERRED, REGION FROM CUSTOMERS);

-- DISPLAY TABLE
SELECT * FROM CUST_MKT;
-- DISPLAY TABLE WITH COUNT OF SPECIFIC COLUMN
SELECT COUNT(CUSTOMER#) FROM CUST_MKT;

-- ALTER TABLE & ADD NEW COLUMN 'EXT' WITH DATA TYPE NUMBER(4)
ALTER TABLE ACCTMANAGER ADD(EXT NUMBER(4));
-- ALTER TABLE & MODIFY COLUMN 'AMFIRST' WITH VARCHAR2(12)
ALTER TABLE ACCTMANAGER MODIFY(AMFIRST VARCHAR2(12));
ALTER TABLE STORE_REPS MODIFY (LAST NOT NULL, FIRST NOT NULL);
ALTER TABLE STORE_REPS MODIFY (COMM CHECK (COMM IN ('Y', 'N')));
-- ALTER TABLE & DROP(REMOVE) COLUMN 'EXT'
ALTER TABLE ACCTMANAGER DROP COLUMN EXT;
-- ALTER TABLE & CREATE A COLUMN CALLED PURCHASE DATE
ALTER TABLE SUMMER_CUSTOMER ADD(PDATE DATE DEFAULT SYSDATE);
-- ALTER TABLE & ADD NEW COLUMN WITH CHECK CONSTRAINT
ALTER TABLE STORE_REPS ADD (BASE_SALARY NUMBER(7,2) CHECK (BASE_SALARY > 0));

-- ALTER TABLE & MARK 'STATE' COLUMN AS UNUSED
--> DOESN'T REMOVE BUT MARK AS UNUSED(STILL EXIST IN TABLE METADATA BUT BE EXCLUDED FROM ANY SUBSEQUENT QUERIES OR OPERATIONS)
ALTER TABLE CUST_MKT SET UNUSED (STATE);
ALTER TABLE CUST_MKT SET UNUSED COLUMN STATE;
-- ALTER TABLE & DROP(REMOVE) ANY UNUSED COLUMNS FROM THE TABLE(NO LONGER EXIST IN THE TABLE)
ALTER TABLE CUST_MKT DROP UNUSED COLUMNS;

-- RENAME TABLE NAME
RENAME CUST_MKT TO CUST_MKT_2020;
ALTER TABLE EMPLOYEES RENAME TO JL_EMPS;

-- TRUNCATE TABLE(= REMOVE ALL ROWS FROM TABLE, BUT TABLE STRUCTURE AND CONSTRAINTS STILL EXIST)
--> DELETE ALL THE RECORDS
TRUNCATE TABLE CUST_MKT_2020;
-- DROP TABLE AND PERMANENTLY REMOVE('PURGE' OPTION: TABLE AND DATA CANNOT BE RECOVERED USING THE RECYCLE BIN)
DROP TABLE CUST_MKT_2020 PURGE;
-- PERMANENTLY REMOVE TABLE FROM RECYCLEBIN
PURGE TABLE CUST_MKT_2020;
--> DROP-PURGE: DIRECTLY DROP TABLE FROM DATABASE WITHOUT MOVING IT TO RECYCLEBIN
--> PURGE: PERMANENTLY REMOVE TABLE FROM RECYCLEBIN, CANNOT BE RECOVERED

-- ENSURE THE RECYCLE BIN FOR THE CURRENT SESSION
ALTER SESSION SET RECYCLEBIN=ON;
-- RECOVER DROPPED TABLE TO ITS STATE BEFORE IT WAS DROPPED AND RESTORE THE TABLE WITH ITS DATA INTACT(UNDO THE EFFECTS OF 'DROP TABLE')
DROP TABLE CUST_MKT_2020;
SELECT OBJECT_NAME, ORIGINAL_NAME FROM RECYCLEBIN; --> U CAN SEE 'OBJECT_NAME,' 'ORIGINAL_NAME' COLUMNS IN RECYCLEBIN
SELECT * FROM RECYCLEBIN; --> U CAN SEE CUST_MKT_2020 TABLE IN RECYCLEBIN
FLASHBACK TABLE CUST_MKT_2020 TO BEFORE DROP;
SELECT * FROM CUST_MKT_2020;
--> 'DROP TABLE FIRST' AND THEN 'SELECT RECYCLEBIN'
--> AND THEN 'FLASHBACK' AND THEN 'SELECT THAT TABLE'

-- CREATE TABLE SALES
CREATE TABLE SALES
(
SNO NUMBER(3) PRIMARY KEY,
SDATE DATE DEFAULT SYSDATE,
COMMISSION NUMBER(3) DEFAULT 100,
PRICE NUMBER(3)
);

-- INSERT NEW ROW INTO SALES TABLE WITH SPECIFIC VALUES
INSERT INTO SALES (SNO, PRICE) VALUES (1, 900);
INSERT INTO CUSTOMERS (CUSTOMER#, LASTNAME, FIRSTNAME, REGION) VALUES (1020, 'SEYEON', 'JO', 'ON');

---

Back to Top

See other articles in Category Oracle-Sql