STUDENT NAME:. 25 October 2016. sql assignment. STUDENT

Added on - 22 Sep 2019

  • 12

    Pages

  • 531

    Words

  • 156

    Views

  • 0

    Downloads

Trusted by +2 million users,
1000+ happy students everyday
Showing pages 1 to 4 of 12 pages
STUDENT NAME:25 October 2016SQL ASSIGNMENT
ContentsTASK 1....................................................................................................................................................2PART A: SELECTING ALL CONSTRAINTS..............................................................................................2PART B: IMPLEMENTING THE MISSING CONSTRAINTS......................................................................2TASK 2: TRIGGERS..................................................................................................................................3PART A...............................................................................................................................................3PART C...............................................................................................................................................4PART D...............................................................................................................................................4TASK 3: VIEWS.......................................................................................................................................4PART A...............................................................................................................................................4PART B...............................................................................................................................................5PART C: EXECUTION TIME..................................................................................................................6TASK 4....................................................................................................................................................8PART A...............................................................................................................................................8TASK 5....................................................................................................................................................9PART A...............................................................................................................................................9PART B.............................................................................................................................................101|P a g e
TASK 1PART A: SELECTING ALL CONSTRAINTSANSWER:SELECT * FROM USER_CONSTRAINTS;PART B: IMPLEMENTING THE MISSING CONSTRAINTSANSWER: The statements to implement the missing constraints are:Alter table spotters add constraint FK_ORG_ID_TO_ORG_ID foreign key(organisation_id) referencesorganisations(organisation_id);Alter table sightings add constraint FK_SPOTTER_ID_TO_SPOTTER_ID foreign key(spotter_id)references spotters(spotter_id);Alter table sightings add constraint FK_BIRD_ID_TO_BIRD_ID foreign key(bird_id) referencesbirds(bird_id);alter table organisations add constraint NN_ORGANISATION_NAME check(organisation_name isNOT NULL);2|P a g e
alter table SPOTTERS add constraint NN_SPOTTER_NAME CHECK(SPOTTER_name IS not null);alter table BIRDS add constraint NN_BIRD_NAME CHECK(BIRD_name IS not null);alter table sightings add constraint CK_SIGHTING_DATE CHECK (EXTRACT(YEAR FROMSIGHTING_DATE) <= 2016);TASK 2: TRIGGERSPART ACREATE SEQUENCE SEQ_SIGHTINGSSTART WITH 300000INCREMENT BY 1;CREATE OR REPLACE TRIGGER SEQ_SIGHTINGSBEFORE INSERT ON SIGHTINGSFOR EACH ROWBEGINIF :new.SIGHTING_ID IS NULL THENSELECT SEQ_SIGHTINGS.nextval INTO :new.SEQ_SIGHTINGS FROM DUAL;END IF;END;3|P a g e
desklib-logo
You’re reading a preview
Preview Documents

To View Complete Document

Click the button to download
Subscribe to our plans

Download This Document