Scribd Logo
Upload

Welcome to Scribd!

  • 55 views

    BDI Tema11 Danuta Laurentiu

    Uploaded by

    Laurentiu Danuta
    The document provides the steps to create tables, add and modify constraints, insert data, and disable constraints in Oracle. It includes: 1) Creating the graduate_candidates table 2) Describing the table 3) Creating a new table using a subquery and inserting a row 4) Creating the animals table with constraints 5) Inserting a row into animals and verifying 6) Adding a foreign key constraint referencing a non-existent table 7) Creating copy tables and adding primary and foreign key constraints 8) Disabling the primary key constraint and inserting into the foreign key table

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd

    BDI Tema11 Danuta Laurentiu

    Uploaded by

    Laurentiu Danuta
    55 views3 pages
    The document provides the steps to create tables, add and modify constraints, insert data, and disable constraints in Oracle. It includes: 1) Creating the graduate_candidates table 2) Describing the table 3) Creating a new table using a subquery and inserting a row 4) Creating the animals table with constraints 5) Inserting a row into animals and verifying 6) Adding a foreign key constraint referencing a non-existent table 7) Creating copy tables and adding primary and foreign key constraints 8) Disabling the primary key constraint and inserting into the foreign key table

    Original Title

    BDI_tema11_Danuta_Laurentiu

    Copyright

    © © All Rights Reserved

    Available Formats

    DOC, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document provides the steps to create tables, add and modify constraints, insert data, and disable constraints in Oracle. It includes: 1) Creating the graduate_candidates table 2) Describing the table 3) Creating a new table using a subquery and inserting a row 4) Creating the animals table with constraints 5) Inserting a row into animals and verifying 6) Adding a foreign key constraint referencing a non-existent table 7) Creating copy tables and adding primary and foreign key constraints 8) Disabling the primary key constraint and inserting into the foreign key table

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    55 views3 pages

    BDI Tema11 Danuta Laurentiu

    Uploaded by

    Laurentiu Danuta
    The document provides the steps to create tables, add and modify constraints, insert data, and disable constraints in Oracle. It includes: 1) Creating the graduate_candidates table 2) Describing the table 3) Creating a new table using a subquery and inserting a row 4) Creating the animals table with constraints 5) Inserting a row into animals and verifying 6) Adding a foreign key constraint referencing a non-existent table 7) Creating copy tables and adding primary and foreign key constraints 8) Disabling the primary key constraint and inserting into the foreign key table

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    of 3

    Tema nr.

    11
    Observaţie!
    Scrieţi rezolvarea direct în acest document!

    1. Write the syntax to create the grad_candidates table.

    CREATE TABLE graduate_candidates


    (student_id NUMBER(6) ,
    last_name VARCHAR2(20) ,
    first_name VARCHAR2(20),
    credits NUMBER(3) ,
    graduation_date DATE DEFAULT SYSDATE);

    2. Confirm creation of the table using DESCRIBE.

    DESCRIBE graduate_candidates;

    3. 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.
    CREATE TABLE hello_table
    AS ( SELECT * FROM graduate_candidates);
    4. Insert your personal data into the table created above.

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


    Values(10,'Laurentiu','Danuta',9,NULL);

    5. Using the column information for the animals table below, name constraints where applicable
    at the table level, otherwise name them at the column level. Define the primary key
    (animal_id). The license_tag_number must be unique. The admit_date and vaccination_date
    columns cannot contain null values.

    animal_id NUMBER(6) - PRIMARY KEY


    name VARCHAR2(25)
    license_tag_number NUMBER(10)- UNIQUE
    admit_date DATE- NOT NULL
    adoption_id NUMBER(5),
    vaccination_date DATE- NOT NULL

    6. Create the animals table. Write the syntax you will use to create the table.

    CREATE TABLE animals


    ( animal_id NUMBER(6,0) CONSTRAINT anl_anl_id_pk PRIMARY KEY ,
    name VARCHAR2(25),
    license_tag_number NUMBER(10,0) CONSTRAINT anl_l_tag_num_uk UNIQUE,
    admit_date DATE CONSTRAINT anl_adt_dat_nn NOT NULL ENABLE,
    adoption_id NUMBER(5,0),
    vaccination_date DATE CONSTRAINT anl_vcc_dat_nn NOT NULL ENABLE

    );

    7. Enter one row into the table. Execute a SELECT * statement to verify your input. Refer to
    the graphic below for input.

    ANIMAL_ID NAME LICENSE_TAG ADMIT_DATE ADOPTION_ID VACCINATION_


    _NUMBER DATE
    101 Spot 35540 10-OCT-04 205 12-OCT_04
    INSERT INTO animals (animal_id, name, license_tag_number, admit_date, adoption_id,
    vaccination_date)
    VALUES( 101, 'Spot', 35540, TO_DATE('10-Oct-2004', 'DD-Mon-YYYY'), 205,
    TO_DATE('12-Oct-2004', 'DD-Mon-YYYY'));
    SELECT * FROM animals;
    8. Write the syntax to create a foreign key (adoption_id) in the animals table that has a
    corresponding primary- key reference in the adoptions table. Show both the column-level and
    table-level syntax. Note that because you have not actually created an adoptions table, no
    adoption_id primary key exists, so the foreign key cannot be added to the animals table.
    ALTER TABLE animals
    MODIFY ( adoption_id NUMBER(5,0) CONSTRAINT anl_adopt_id_fk REFERENCES
    adoptions(id) ENABLE );

    ALTER TABLE animals ADD CONSTRAINT anl_adopt_id_fk FOREIGN KEY (adoption_id)


    REFERENCES adoptions(id) ENABLE;
    9. Since the tables are copies of the original tables, the integrity rules are not passed onto the
    new tables; only the column datatype definitions remain. You will need to add a PRIMARY
    KEY constraint to the copy_d_clients table. Name the primary key copy_d_clients_pk . What
    is the syntax you used to create the PRIMARY KEY constraint to the copy_d_clients.table?
    CREATE TABLE copy_d_clients
    AS ( SELECT * FROM d_clients);
    CREATE TABLE copy_d_events
    AS ( SELECT * FROM d_events);
    ALTER TABLE copy_d_clients
    ADD CONSTRAINT copy_d_clt_client_number_pk PRIMARY KEY (client_number);

    10. Create a FOREIGN KEY constraint in the copy_d_events table. Name the foreign key
    copy_d_events_fk. This key references the copy_d_clients table client_number column.
    What is the syntax you used to create the FOREIGN KEY constraint in the copy_d_events
    table?
    ALTER TABLE copy_d_events
    ADD CONSTRAINT copy_d_eve_client_number_fk FOREIGN KEY (client_number)
    REFERENCES copy_d_clients (client_number) ENABLE;
    11. Use a SELECT statement to verify the constraint names for each of the tables. Note that
    the tablenames must be capitalized.

    SELECT constraint_name, constraint_type, table_name


    FROM user_constraints
    WHERE table_name = UPPER('copy_d_events') ;
    a. The constraint name for the primary key in the copy_d_clients table is
    COPY_D_CLT_CLIENT_NUMBER_PK _____.

    b. The constraint name for the foreign key in the copy_d_events table is
    COPY_D_EVE_CLIENT_NUMBER_FK

    12. Create an ALTER TABLE query to disable the primary key in the copy_d_clients table.
    Then add the values from #6 to the copy_d_events table. Explain your results.

    ALTER TABLE copy_d_clients


    DISABLE CONSTRAINT COPY_D_CLT_CLIENT_NUMBER_PK CASCADE;

    INSERT INTO
    copy_d_events(client_number,id,name,event_date,description,cost,venue_id,package_code,them
    e_code)
    VALUES(7125,140,'John Bay Fach',TO_DATE('13-Aug-2008','dd-Mon-yyyy'),'Wedding
    day',4500,105,87,77);

    You might also like