1 Can a childs FK field have a different name than the paren

1. Can a child\'s FK field have a different name than the parent\'s PK field? Provide examples.

2.

Suppose you have a table ORDERS.

ORDER_ID   CUSTOMER_ID   ORDER_DATE   SHIP_DATE

-----------------------------------------------------------------------------

1233               789443345435       01-feb-2009           10-feb-2009

1234               789443345435       02-feb-2009          null

1235               543579392949       02-feb-2009           null

1236               237458685686       02-feb-2009           09-feb-2009

1237               383485868586       02-feb-2009           null

1238               789443345435       02-feb-2009           null

Set in ONE statement SHIP_DATE to today\'s date for all customer 789443345435 orders that have not been shipped.

3.

Consider the following PL/SQL block. What will be the value of y in the inserted row?

4.

Create table Employee with empID (PK), empFname, empLname, emp-salary.

Insert at least 4 rows in the Employee table

Create a trigger that will fire automatically in response to any change in salary before delete or insert or update on employee table.

Check what happens when you try any of the events (insert-update-delete)

Create at least two test cases for each event.

What to submit:

Write SQL code to create Employee table.

Write SQL code to insert values in tables.

Write Trigger code

Submit Test cases and trigger execution results with screen shots.

CREATE TABLE test. (x INT, Y INT) create or replace TRIGGER test trigger BEFORE INSERT ON test FOR EACH ROW BEGIN IF new. Y IS NULL THEH new. Y 111; END IF: THEN new. Y mew. Y END IF: EHD: INSERT INTO test. (x) VALUES (10) SELECT FROM test.

Solution

1. Can a child\'s FK field have a different name than the parent\'s PK field? Provide examples.

    The foreign key field in child table has a different name or same name are allowed.

    But the domain or data type must be same.

   

Parent table:STUDENT ,(SID IS PRIMARY KEY DATA TYPE INTEGER)

SID(pk)

SNAME

AGE

501

smith

20

502

john

22

503

peter

21

Child table: ENROLLED Student_id IS FORIGN KEY

  

Student_id(fk)

--------------

Course_id

grade

503

CS02

A

501

CS09

B

502

CS07

A

2. Suppose you have a table ORDERS.

ORDER_ID   CUSTOMER_ID   ORDER_DATE   SHIP_DATE

-----------------------------------------------------------------------------

1233               789443345435       01-feb-2009           10-feb-2009

1234               789443345435       02-feb-2009           null

1235               543579392949       02-feb-2009           null

1236               237458685686       02-feb-2009           09-feb-2009

1237               383485868586       02-feb-2009           null

1238               789443345435       02-feb-2009           null

Set in ONE statement SHIP_DATE to today\'s date for all customer 789443345435 orders that have not been

shipped.

Answer:

SQL> UPDATE ORDERS SET SHIP_DATE =SYSDATE WHERE CUSTOMER_ID= 789443345435;

3 rows updated.

output

SQL> select * from orders;

   ORDER_ID     CUSTOMER_ID ORDER_DATE   SHIP_DATE

    ----------           ----------                 ---------                       ---------

      1233         7.8944E+11          01-FEB-09            24-FEB-17   //ROW UPDATED

      1234         7.8944E+11          02-FEB-09            24-FEB-17 //ROW UPDATED

      1235         5.4358E+11          02-FEB-09            NULL

      1236         2.3746E+11          02-FEB-09            09-FEB-09

      1237         3.8349E+11          02-FEB-09            NULL

      1238         7.8944E+11         02-FEB-09            24-FEB-17   // ROW UPDATED

6 rows selected.

question3:

Step1:  

SQL> Create table test(x int, y int);

        Table created.

Step2: create a trigger.

SQL> Create or replace trigger test_trigger

              Before insert on test for each row

     Begin

            If :new.y is null then    //check the y value in table if it is null insert 111. Now y value is 111.

                :new.y :=111;

           End if;

          If :new.y =0 then

                 :new.y :=0;

           End if;

   End;

   /

Trigger created.

SQL> Insert into test (x) values(10); // Insert x values is 10 in table

1 row created.

OUTPUT

SQL> select * from test;

X              Y

    ----------   ----------

        10           111

4.

Write SQL code to create Employee table.

SQL> create table emp( emp_id int primary key,emp_Fname varchar2(15),emp_Lname varchar2(15),emp_salary int);

Table created.

Write SQL code to insert values in tables

SQL> Insert into emp4 values(1220,\'john\',\'peter\',3400);

1 row created.

SQL> Insert into emp4 values(1330,\'anil\',\'kumar \',4500);

1 row created.

SQL> Insert into emp4 values(4545,\'mohit\',\'babu\',9570);

1 row created.

SQL> Insert into emp4 values(5454,\'dave\',\'joe\',7500);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from emp;

EMP_ID EMP_FNAME EMP_LNAME EMP_SALARY

---------- --------------- ------------------------- -----------------

1220 john peter 3400

1330 anil kumar 4500

4545 mohit babu 9570

5454 dave joe 7500

SID(pk)	SNAME	AGE
501	smith	20
502	john	22
503	peter	21