DATABASE DESIGN AND IMPLEMENTATION EXAM QUESTIONS AND CORRECT ANSWERS LATEST UPDATE 2025

DATABASE DESIGN AND IMPLEMENTATION EXAM QUESTIONS AND CORRECT ANSWERS LATEST UPDATE 2025 Evaluate the following SQL commands: CREATE SEQUENCE ord_seq Increment by 10 Start with 120 Maxvalue 9999 Nocycle; CREATE TABLE ord_items (ord_no NUMBER (4) DEFAULT ord_seq.NEXTVAL NOT NULL, Item_no NUMBER (3), qty NUMBER (3) CHECK (qty BETWEEN 100 AND 200), expiry_date date CHECK (expiry_date > SYSDATE), CONSTRAINT its_pky PRIMARY KEY (ord_no, item_no), CONSTRAINT ord_fky FOREIGN KEY (ord_no) REFERENCES orders (order#)); The command to create the table fails. What caused the failure? Rewrite the CREATE table ord_items code to correct the error. - Answers Error details : -DDL statement for ord_items table will check the expiry_date with the sysdate -Each time system date will change and hence which will not validate -Instead add a column with todays_date date default sysdate which will add the todays date into the column and then this date is checked with expiry_date Sequence : Create Sequence ord_seq Increment by 10 Start with 120 MaxValue 9999 nocycle; Table Name : ord_items create table ord_items (ord_no number(4) default ord_Val not null, Item_no number(3), qty number(3) check (qty between 100 and 200), todays_date date default sysdate, expiry_date date , constraint its_pky primary key (ord_no,Item_no), constraint ord_fky foreign key (ord_no) references orders(order#), constraint check_expiry check(expiry_date> todays_date)); In an ER model, a person, a place or a thing with characteristics to be stored in the database is referred to as? 1. Entity 2. Row 3. Attribute 4. File - Answers Entity Which of the following is an interface that allows a user to create, edit, and manipulate data in an Oracle database? 1. SQL 2. SQL*plus 3. ASCII 4. Database - Answers SQL*plus Which version of Oracle Database management systems (DBMS) is prescribed for this course? 1. Oracle 18c XE 2. Oracle 11g XE 3. Oracle 10g XE 4. Oracle enterprise edition - Answers Oracle 18c XE Based on the Justlee books database, which of the following SELECT statements display a list of customer names from the CUSTOMERS table? 1. Select customer names from customers; 2. Select names from customers; 3. Select firstname, lastname from customer; 4. Select first_name, last_name from customers; - Answers Select firstname, lastname from customer; Which of the following statements return all fields from the ORDERS table of the JustLee books database? 1. Select * from orders table; 2. select order#, customer#, orderdate, shipdate, shipstate, shipzip, shipcost from orders; 3. select order#, customer#, orderdate, shipdate, shipstreet, shipcity, shipstate, shipzip, shipcost from orders; 4. select order#, customer#, orderdate, shipdate, shipstreet, shipcity, shipstete, from orders; - Answers Select * from orders table; Which of the following symbols is used for a column alias containing spaces? 1. Single quatationmarks (' ') 2. Pipe (l l) 3. Double quotation marks (" ") 4. Percentage signs (% %) - Answers Double quotation marks (" ") Which of the following will display the new retail price of each book of the Justlee books database as 10% less the original retail price? 1. Select title, retail - 10% from books 2. Select retail - 10/100 from books 3. Select retail - 0.1*retail from books 4. Select retail, retail*1.10 from books - Answers Select title, retail - 10% from books Which of the following commands drops any columns marked as unused from a tabel named myTable? 1. Drop column from myTable where column_status = unused; 2. Alter table myTable drop unused columns; 3. Alter table myTable drop(unused); 4. Drop unused columns; - Answers Alter table myTable drop unused columns; Which of the following commands removes all data from a tabel name myTable but leaves the table's structure intact? 1. Alter tble myTable drop unused columns; 2. Truncate table myTable; 3. Delete table myTable; 4. Drop table myTable; - Answers Truncate table myTable; Which of the following commands changes a tables name from OLDNAME to NEWNAME? 1. Rename OLDNAME to NEWNAME 2. Rename table From OLDNAME to NEWNAME 3. Alter table NEWNAME modify from OLDNAME 4. Create table NEWNAME (select * from OLDNAME) - Answers Rename OLDNAME to NEWNAME Which of the following are used to enforce business rules? 1. Syntax 2. Functions 3. Constraints 4. Foreign rules - Answers Constraints Which of the following is not a constraint type in Oracle? 1. Check 2. Unique 3. Not null 4. Reference - Answers Not null Which of the following keywords must have been included during the creation of a foreign key constraint to allow a row from the parent table to be deleted even if it is referenced by a row in the child table? 1. Cascade