SQL Assignment with Constraints, Triggers, Views and Queries
12 Pages531 Words495 Views
Added on 2019-09-22
About This Document
This SQL assignment covers topics like selecting all constraints, implementing missing constraints, triggers, views, and queries. It includes SQL statements and outputs.
SQL Assignment with Constraints, Triggers, Views and Queries
Added on 2019-09-22
BookmarkShareRelated Documents
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 CONSTRAINTS ANSWER: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) references organisations(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) references birds(bird_id);alter table organisations add constraint NN_ORGANISATION_NAME check(organisation_name is NOT 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 FROM SIGHTING_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
End of preview
Want to access all the pages? Upload your documents or become a member.
Related Documents
SQL: DDL Create and Alter, DCL Grant and Revoke, Data Manipulation, Database Testinglg...
|13
|2239
|63
MySQL DDL - A Guide to Database Creation and Managementlg...