Database Tutorial

Course Tutorial Site

Site Admin

Help Section

without comments

Lab #2: Create Tables

Learn about records, tables, Data Definition Language (DDL) commands, and database constraints.

Lab Help Section

There are four key elements to creating tables in a relational database. Table names, column names, constraint names, and types are the four key elements of tables.

The name of any table must be unique within a database (a database management system may and typically does hold more than one database). Likewise, column names must be unique within any table. Constraint names are like table names because they must be unique within a database. Lastly, data types must be defined before you use them to create or modify a table definition.

You are asked to review the following preparation material:

The reading material gives you a foundation of knowledge about creating tables, constraints, indexes, and sequences.

Instructional Material →

The supplemental materials depend on your reading and understanding the preparation material qualified earlier in this lab. The coding segments below show you:

  • How to create the table_name bind variable
  • How to use the :table_name bind variable in a conditional DROP statement
  • How to create a chicken table without any constraints
  • How to query the structure of the chicken table with a :table_name bind variable
  • How to query the constraints of the chicken table with a :table_name bind variable
  • How to create a chicken_s sequence

The instructions walk you through the steps above.

  • Create & assign a bind variable

    • Define a bind variable and assign a value to the bind variable. You define the variable by using a VARIABLE keyword, a table_name identifier, and a VARCHAR2(30) data type. The data type requires that you qualify the length of the string.

    You must assign values to bind variables inside PL/SQL anonymous blocks. You must precede a table_name bind variable name with a colon when you want to reference it inside a PL/SQL block.

    -- Define a bind variable.
VARIABLE table_name VARCHAR2(30)
 
-- Assign an uppercase string to a session bind variable.
BEGIN
  :table_name := UPPER('chicken');
END;
/

    You query the value assigned to the table_name bind variable with the following SELECT statement:

    SELECT :table_name FROM dual;

    The query returns:

    :TABLE_NAME
----------------------
CHICKEN

  • Conditionally drop a chicken table

    • You can drop a chicken table with the following anonymous PL/SQL program unit:

    DECLARE
  /* Dynamic cursor. */
  CURSOR c (cv_object_name VARCHAR2) IS
    SELECT o.object_type
    ,      o.object_name
    FROM   user_objects o
    WHERE  o.object_name LIKE UPPER(cv_object_name||'%');
BEGIN
  FOR i IN c(:table_name) LOOP
    IF i.object_type = 'SEQUENCE' THEN
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name;
    ELSIF i.object_type = 'TABLE' THEN
      EXECUTE IMMEDIATE 'DROP '||i.object_type||' '||i.object_name||' CASCADE CONSTRAINTS';
    END IF;
  END LOOP;
END;
/

  • Create a chicken table

    • You can create a chicken table without any conditions. The following CREATE statement creates the chicken table:

    CREATE TABLE chicken
( chicken_id    NUMBER
, chicken_name  VARCHAR2(20));

  • Query a chicken table definition

    • You use the following SELECT statement to view the chicken table’s structure:

    SET NULL ''
COLUMN table_name   FORMAT A16
COLUMN column_id    FORMAT 9999
COLUMN column_name  FORMAT A22
COLUMN data_type    FORMAT A12
SELECT   table_name
,        column_id
,        column_name
,        CASE
           WHEN nullable = 'N' THEN 'NOT NULL'
           ELSE ''
         END AS nullable
,        CASE
           WHEN data_type IN ('CHAR','VARCHAR2','NUMBER') THEN
             data_type||'('||data_length||')'
           ELSE
             data_type
         END AS data_type
FROM     user_tab_columns
WHERE    table_name = 'CHICKEN'
ORDER BY 2;

    The query returns:

    TABLE_NAME	 COLUMN_ID COLUMN_NAME		  NULLABLE DATA_TYPE
---------------- --------- ---------------------- -------- ------------
CHICKEN 		 1 CHICKEN_ID			   NUMBER(22)
CHICKEN 		 2 CHICKEN_NAME 		   VARCHAR2(20)

    You have created the chicken table and validated the chicken table’s definition. The instructional material only demonstrates how you create an unconstrained chicken table.

    This lab has ten unit testing SQL script files, and one integration SQL script file. Each unit SQL script file contains the steps to manage table creation in a re-runnable script file. The integration SQL script file calls the unit test programs in the proper sequence necessary due to dependencies between the scripts.

    Written by michaelmclaughlin

    August 7th, 2018 at 9:18 am

    Posted in