33 Creating Tables

• ·         Created and maintained by the Oracle Server and contains information about the database- Data dictionary
• ·         A collection of objects that are the logical structures that directly refer to the data in the database- Schema
• ·         Specifies a preset value if a value is omitted in the INSERT statement- DEFAULT
• ·         Stores data; basic unit of storage composed of rows and columns- TABLE
• ·         Command use to make a new table- CREATE TABLE

1.  Complete the GRADUATE CANDIDATE table instance chart. Credits is a foreign-key column referencing the requirements table.

Column Name	student_id	last_name	first_name	credits	graduation_date
Key Type	Primary key			foreign key
Nulls/Unique	NO/YES	NO/NO	NO/NO	NO/NO	YES/NO
FK Column				credits
Datatype	NUMBER	VARCHAR2	VARCHAR2	NUMBER	DATE
Length	6	75	75	6

For credits and student_id it could have been precision and scale rather mentioned here. I assume that when 6 is written for student_id it means NUMBER(6,0) and for credits NUMBER(5, 2)

2.  Write the syntax to create the grad_candidates table.

CREATE TABLE  graduate_candidates

   ( student_id NUMBER(6,0),

    last_name VARCHAR2(75) CONSTRAINT gcs_last_name_nn NOT NULL ENABLE,

 first_name VARCHAR2(75) CONSTRAINT gcs_first_name_nn NOT NULL ENABLE,

 credits NUMBER(5,2) CONSTRAINT gcs_credits_nn NOT NULL ENABLE,

graduation_date DATE,

 CONSTRAINT gcs_std_id_pk PRIMARY KEY (student_id),

 CONSTRAINT gcs_req_fk FOREIGN KEY(credits) REFERENCES requirements(credits) ENABLE     

   

  );

Note: Since there is no requirements table already existing for foreign key above stamen will fail. I may create this table if choose not to include this constraint.

3.  Confirm creation of the table using DESCRIBE.

DESCRIBE graduate_candidates;

4.  Create a new table using a subquery. Name the new table your last name -- e.g., smith_table. Using a subquery, copy grad_candidates into smith_table.

Even if the foreign key world have been created in problem 2, it would have been lost here in copy.

CREATE TABLE kumar_table

AS ( SELECT * FROM graduate_candidates);

see if copy worked well:

DESCRIBE kumar_table;

student_id is nullable and not a primary key here in kumar_table.

5.  Insert your personal data into the table created in question 4.

INSERT INTO kumar_table (student_id, last_name, first_name, credits, graduation_date)

Values(10,'kumar','he',999.99,NULL);

SELECT LENGTH(credits) FROM kumar_table WHERE student_id = 10;

6.  Query the data dictionary for each of the following:

• USER_TABLES

• USER_OBJECTS

• USER_CATALOG or USER_CAT

In separate sentences, summarize what each query will return.

--USER_TABLES describes the relational tables owned by the current user. Its columns (except for OWNER) are the same as those in ALL_TABLES.

SELECT * FROM user_tables;

48 rows returned in 0.64 seconds

--USER_CATALOG lists indexes, tables, views, clusters, synonyms, and sequences owned by the current user. Its columns are the same as those in "ALL_CATALOG".

SELECT * FROM user_catalog;

407 rows returned in 0.05 seconds

SELECT DISTINCT(table_type) FROM user_catalog;

3 rows returned in 0.02 seconds        

--USER_OBJECTS describes all objects owned by the current user. Its columns are the same as those in "ALL_OBJECTS".

SELECT * FROM user_objects;

117 rows returned in 0.19 seconds

SELECT DISTINCT(object_type) FROM user_objects;

6 rows returned in 0.17 seconds        

SELECT * FROM user_cat;

ORA-00942: table or view does not exist