Ask a question to Desklib · AI bot
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.
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
Found this document preview useful?
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...
|9
|843
|375