Once the ERD is approved, the next step is to create the objects (attributes, entities & relationships etc) as defined in the ERD using SQL DDL statements which will include the following:
1) Drop statements for all objects in the lab project (drop existing objects first so that you can rerun your script without error).
2) Create statements for all tables and keys of your project.
3) Create indexes on natural key columns, foreign keys, and other columns that will be frequently used as query filters (i.e., Columns in the "WHERE" clause).
4) Create at least two views.
5) Create at least two sequences.
6) Create at least two triggers.
7) Check content of the catalog/data dictionary. Item 7 (above) should contain SQL SELECT statements to query DBMS catalog/data dictionary (such as user_objects or user_tables) to demonstrate all objects are created successfully.
The deliverable would be two separate files: one plain text file (.txt or .sql file) with your SQL statements only; and the other document (doc/docx/pdf) include both your SQL statements and the output (copy and paste text or screen shots).
The instructor and TA should be able to run your plain text source file as script and generate the same output as shown in your result document.
Question 1:
Display all information about all courses
Query:
Select * from course;
Question 2:
Add a new course "Relational Database Systems" with course number 651. Use your user name as created_by/modified_by and current date as created_date/modified_date
Query:
INSERT INTO course (COURSE_NO, DESCRIPTION,CREATED_BY,CREATED_DATE,MODIFIED_BY, MODIFIED_DATE) VALUES (651,'Relational Database Systems','username',SYSDATE,'username',SYSDATE);
Question 3:
List the course number for "Database System Principles"
Query:
SELECT COURSE_NO FROM course WHERE DESCRIPTION LIKE '%Database System Principles%';
Question 4:
Set "Database System Principles" as the prerequisite for "Relational Database Systems"
Query:
UPDATE course SET PREREQUISITE=(SELECT COURSE_NO FROM course WHERE DESCRIPTION LIKE '%Database System Principles%') WHERE DESCRIPTION LIKE '%Relational Database Systems%';
Question 5:
List all courses that have "Database System Principles" as prerequisite
Query:
Select * from course where PREREQUISITE=(SELECT COURSE_NO FROM course WHERE DESCRIPTION LIKE '%Database System Principles%');
Output:
Question 6:
List total number of courses that have no prerequisite
Query:
Select count(*) from course where PREREQUISITE IS NULL;
Question 7:
Delete course "Relational Database Systems"
Query:
delete from course where DESCRIPTION LIKE '%Relational Database Systems%';
Question8:
Display total number of courses Lower cost of all courses by 1100
Query:
Select count(*) from course where COST < 1100;
Question 9:
List all course cost (show course description and cost only) by the order of cost from highest to lowest
Query:
select description, cost from course order by COST desc;
Attachment:- Assignment.rar