Ask a question from expert

Ask now

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
SQL Assignment with Constraints, Triggers, Views and Queries_1
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
SQL Assignment with Constraints, Triggers, Views and Queries_2
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
SQL Assignment with Constraints, Triggers, Views and Queries_3
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
SQL Assignment with Constraints, Triggers, Views and Queries_4

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 Testing
|13
|2239
|63

MySQL DDL - A Guide to Database Creation and Management
|9
|843
|375