Connect to your database as user SYSTEM in SQL*Plus. Create a table named CUSTOM

Question

Connect to your database as user SYSTEM in SQL*Plus.

Create a table named CUSTOMERS and define the following columns and constraints:

o Customer_number (as a number of size 4) o

Customer_name (as a varchar2 of size 20) o

Primary key constraint to the CUSTOMERS table (customer_number is the primary key)

Create a second table named INVOICES and define four columns for it: o Invoice_number (as a number of size 4)

o Customer_number (as a number of size 4)

o Invoice_date (as a Date). Make this column NOT NULL

o Amount (as a Number)

o Primary key constraint to the INVOICES table (invoice_number is the primary key)

o Foreign key constraint to the INVOICES table (customer_number is a foreign key that references CUSTOMERS(customer_number))

o Check constraint to the INVOICES table (amount should be >= 0)

(1) Include the two create table statements that contain all the necessary constraints (10 points).

From an SQL prompt execute the following statements:

insert into customers values (15, 'Mary');

insert into customers values (20, 'John');

insert into customers values (21, 'Steve');

insert into customers values (22, 'Mike');

insert into customers values (23, 'Mary');

insert into invoices values (1, 15, sysdate, 100);

insert into invoices values (2, 20, sysdate, 300);

insert into invoices values (3, 21, sysdate, 200);

insert into invoices values (4, 22, sysdate, 140);

insert into invoices values (5, 23, sysdate, 340);

insert into invoices values (6, 15, sysdate-1, 200);

insert into invoices values (7, 15, sysdate-2, 100);

insert into invoices values (8, 15, sysdate-3, 100);

Demonstrate the effectiveness of the constraints (all five!) by creating statements that violates each constraint. You will need to create one insert statement and one update statement that violate exactly one constraint. In total you will need to provide 10 statements. (2) Execute the 10 statements you create as specified above in SQL*Plus and include the spool file in the assignment. Add comments that specify which constraint is violated. Make sure all statements and their corresponding ORA errors are included in the printout (40 points).

Explanation / Answer

create table customers (Customer_number number(4) primary key,Customer_name varchar2(20));
create table invoices (Invoice_number number(4) primary key ,
Customer_number number(4) REFERENCES customers (Customer_number ),
Invoice_date DATE NOT NULL,
Amount number(7,2) CHECK(Amount>=0));
                     

if we execute the above query successfully this we get this message .

Table created.

insert into customers values (15, 'Mary');
insert into customers values (20, 'John');
insert into customers values (21, 'Steve');
insert into customers values (22, 'Mike');
insert into customers values (23, 'Mary');
select * from customers;
                     
CUSTOMER_NUMBER   CUSTOMER_NAME
23 Mary
15   Mary
20   John
21   Steve
22   Mike
5 rows returned in 0.12 seconds  

insert into invoices values (1, 15, sysdate, 100);
insert into invoices values (2, 20, sysdate, 300);
insert into invoices values (3, 21, sysdate, 200);
insert into invoices values (4, 22, sysdate, 140);
insert into invoices values (5, 23, sysdate, 340);
insert into invoices values (6, 15, sysdate-1, 200);
insert into invoices values (7, 15, sysdate-2, 100);
insert into invoices values (8, 15, sysdate-3, 100);
select * from invoices;

INVOICE_NUMBER   CUSTOMER_NUMBER   INVOICE_DATE   AMOUNT
1   15   20-APR-16   100
2   20   20-APR-16   300
3   21   20-APR-16   200
4   22   20-APR-16   140
5   23   20-APR-16   340
6   15   19-APR-16   200
7   15   18-APR-16   100
8   15   17-APR-16   100
8 rows returned in 0.06 seconds       CSV Export

insert into customers values (23, 'Mary');// i am executing this constraint one more time ,so i am violating unique constraint
Error : ORA-00001: unique constraint (MAHINDRA.SYS_C006529) violated
so , i am inserting with another primary key
insert into customers values (24, 'Mary');

insert into invoices values (9, 25, sysdate, 100); // here i am violating foreign key constraint , so i got this error
Error : ORA-02291: integrity constraint (MAHINDRA.SYS_C006533) violated - parent key not found
so i am insert the above query with the foreign key values only
insert into invoices values (9, 21, sysdate, 100);

insert into invoices values (10, 15, sysdate, -100); //here i am violating check constraint , so i got this error
Error:ORA-02290: check constraint (MAHINDRA.SYS_C006531) violated
   so , i am inserting with postive values
       insert into invoices values (10, 15, sysdate, 200);
      
insert into invoices values (11, 22, null, -100);//here i am violating NOT NULL constraint , so i got this error
Error : ORA-01400: cannot insert NULL into ("MAHINDRA"."INVOICES"."INVOICE_DATE");
           so , i am inserting with date values
           insert into invoices values (11, 22, sysdate, 400);
             
           now if you want to update any column use this query
           update tableName set columnName=value where <condition>
   

if we execute one query successfully then we get this message :

1 row(s) inserted.

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.