CIS336 Study Material for Finals(1) – 25 MCQs, 10 Essay Questions – Guaranteed 100% score

Quiz Price = $18
Please feel free to send us your queries at: support@iqrajavaid.com
Problem Statement

1. (TCO 1) Most DBMS are referred to as _____________database management systems. (Points : 4)
elemental
linked
hierarchical
relational

2. (TCO 1) Data constitutes the building blocks of _____________. (Points : 4)
information
processing
applications
programming

3. (TCO 2) If a foreign key contains either matching values or nulls, the table(s) that make use of such a foreign key is/are said to exhibit __________ integrity. (Points : 4)
referential
restrictive
secondary
redundant

4. (TCO 2) In a “one-to-many” relationship, which constraint is usually added to the “many” table?
(Points : 4)
UNIQUE
PRIMARY KEY
FOREIGN KEY
NOT NULL

5. (TCO 3) A ____ occurs when a relationship is improperly or incompletely identified and, therefore, is represented in a way that is not consistent with the real world. (Points : 4)
surrogate primary keys
time-variant data
design trap
fan trap

6. (TCO 3) All of the following are true about a prime attribute except_____________. (Points : 4)
it is a key attribute
it is at least part of a key
it is not a key attribute
it may be part of a composite key

7. (TCO 3) The conflicts between design efficiency, information requirements, and processing speed are often resolved through_____________. (Points : 4)
conversion from 1NF to 2NF
conversion from 2NF to 3NF
compromises that include denormalization
conversion from 3NF to 4NF

8. (TCO 4) When a constraint is created at the ______ level with the CREATE TABLE command, the constraint definition is simply included as part of the column definition.(Points : 4)
table
column
database
row

9. (TCO 4) In Oracle, the CONSTRAINT clause is used to ___________________. (Points : 4)
create only referential constraints
define and name any constraint
create a short cut so the constraint does not have to be named
create only entity entegrity constraints

10. (TCO 4) The SQL command that lets you list the table contents is (Points : 4)
insert.
select.
commit.
update.
rollback.

11. (TCO 5) The Crow’s Foot model is more ___________-oriented than the Chen model. (Points : 4)
object
user
implementation
processor

12. (TCO 5) The ___________ model is both software and hardware dependent. (Points : 4)
conceptual
logical
condensed
physical

13. (TCO 6) Which of the following commands will add a new column named FIRSTORDERDATE to the CUSTOMERS table to store the date that the customer first placed an order with the company? (Points : 4)
CREATE COLUMN firstorderdate, DATE TO customers;
ALTER TABLE customers ADD COLUMN firstorderdate DATE;
ALTER TABLE customers ADD firstorderdate DATE;
ALTER TABLE customers ADD (firstorderdate DATE);

14. (TCO 6) When modifying the data characteristics of a column in Oracle, which of the following is correct? (Points : 4)
The column size can be increased.
The size of the column can be decreased.
Changing the default value of a column will change the values of data already in a table.
If a NUMBER column is empty, its precision and scale cannot be changed.

15. (TCO 6) Which of the followig SQL commands would alter the table DEVRY and add a foreign key that references the CITY table? (Points : 4)
ALTER table DEVRY
ADD CONSTRAINT devry_studentcity_fk references city;
ALTER table DEVRY
ADD CONSTRAINT devry_studentcity_fk FOREIGN KEY references city;
ALTER table DEVRY
ADD CONSTRAINT devry_studentcity_fk FOREIGN KEY (student_city) references city;
ALTER table DEVRY
ADD CONSTRAINT FOREIGN KEY (student_city) references city;

16. (TCO 7) Given a table named EMPLOYEE, the SQL command to sort records in a specific order would be: (Points : 4)
SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE
FROM EMPLOYEE
LIST BY EMP_LNAME, EMP_FNAME, EMP_INITIAL;
SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE
FROM EMPLOYEE
ORDER BY EMP_LNAME, EMP_FNAME, EMP_INITIAL;
SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE
FROM EMPLOYEE
DISPLAY BY EMP_LNAME, EMP_FNAME, EMP_INITIAL;
SELECT EMP_LNAME, EMP_FNAME, EMP_INITIAL, EMP_AREACODE, EMP_PHONE
FROM EMPLOYEE
SEQUENCE BY EMP_LNAME, EMP_FNAME, EMP_INITIAL;

17. (TCO 7) Which of the following subqueries returns more than one row of results to the outer query? (Points : 4)
multiple-column subquery
single-row subquery
multiple-row subquery
correlated subquery

18. (TCO 7) Which of the following keywords can be used to join two tables that do not contain a commonly named and defined column? (Points : 4)
NATURAL JOIN
JOIN…ON
JOIN…USING
JOIN…WHERE

19. (TCO 8) Based upon the contents of the BOOKS table shown below, which of the following SQL statements will return the number of categories contained in the table?

(Points : 4)
SELECT COUNT(DISTINCT category) FROM books;
SELECT DISTINCT COUNT(category) FROM books;
SELECT DISTINCT (COUNT(category)) FROM books;
SELECT DISTINCT COUNT(category) FROM books;

20. (TCO 8) The _____________ function can be used to display upper-case characters in lower-case. (Points : 4)
CONVERT
MIN
LESS
LOWER

21. (TCO 8) Which of the following statements will produce the value 21.1? (Points : 4)
SELECT ROUND(21.12, 1) FROM dual;
SELECT ROUND(21.12, -1) FROM dual;
SELECT ROUND(21.12, 0) FROM dual;
SELECT ROUND(21.12, 0+) FROM dual;

22. (TCO 9) The column to be updated by the UPDATE command is specified in the ______ clause. (Points : 4)
UPDATE
SET
WHERE
COL

23. (TCO 9) If the INSERT INTO command is used to insert data values that violate an existing constraint in a table, which of the following will happen. (Points : 4)
Oracle will raise an error and you will have to go update the incorrect data.
Oracle will raise an error and the entire row will be rejected.
Oracle will raise an error and the invalid piece of data will be rejected.
Oracle will raise and error and all current transactions will be rolled back.

24. (TCO 10) Which of the following commands is used to establish a user account? (Points : 4)
CREATE NEW USER username
IDENTIFIED BY password;
CREATE USERNAME username
IDENTITY password;
CREATE USER username PASSWORD password;
CREATE USER username IDENTIFIED BY password;

25. (TCO 10) Which command will grant a system privilege to a user? (Points : 4)
GRANT systemprivilege USERNAME username
GRANT PRIVILEGE systemprivilege TO username
GRANT systemprivilege TO
username
GRANT systemprivilege ON
username

ESSAY QUESTIONS:
1. (TCO 2) Explain what physical independence means. (Points : 15)

2. (TCO 8) Sally wants to query the EMP table and determine how many of the employees live in each of the cities the company has factories in. She writes the following query but it does not work. What is wrong with the way the query is constructed?
SELECT city, COUNT(emp_no) as “Number of Customers”
FROM emp
ORDER BY city
GROUP BY city;

3. (TCO 3) Explain in what two specific cases presented in the text composite primary keys are particularly useful. (Points : 15)

4. (TCO 7) How are comparisons done on character string data in a queries WHERE clause, and why would this be especially useful in comparing certain attributes? (Points : 15)

5. (TCO 6) Oracle will not allow the changing of a column’s data type unless the column is empty, and even then it may still be prohibited. Discuss why? (Points : 15)

6. (TCO 7) Write a join query using NATURAL JOIN that for every order in the ORDERS table will list the order number and order date along with the cusnumber, last name, and first name of the customer who placed the order.

FinalExamQueryQuestions

7. (TCO 8) Write a query that will list the part number, part description, and on-hand value (units on hand * unit price) for each part in item class AP. Give the expression an alias of “On Hand Value”.

FinalExamQueryQuestions

8. (TCO 8) Write a query that will display the customer number, first name, and last name for every customer with a balance greater than $50. Display the first and last name as a single column and provide an alias for the column.

FinalExamQueryQuestions

9. (TCO 7) Write a query that will list the customer number, last name, and first name for every customer represented by sales rep 03 or sales rep 12.

FinalExamQueryQuestions

10. (TCO 7) Write a query that will list the customer first and last name and the sales rep commission rate using JOIN ON.
FinalExamQueryQuestions

Relevant Material
Instructions
* Please click on “Buy Now” button to complete the purchase.Please note that the payment is done through PayPal.
* If you have any issues with the payment method, please let us know at support@iqrajavaid.com and that can be discussed and considered.
* Your paypal has to be pre-loaded in order to complete the purchase or otherwise please discuss it with us at support@iqrajavaid.com.
* As soon as the payment is received through PayPal, download link of the solution will automatically be sent to the address used in Paypal.
* Please check your junk mails as the download link email might go there.
* All the contents are compressed in one zip folder.
* In case if you get stuck at any point during the payment process, please immediately contact us at support@iqrajavaid.com and we will fix it with you.
* We try our best to reach back to you on immediate basis. However, please expect a response within 8 hours from our side.
* Comments/Feedbacks are truely welcomed and there might be some incentives for you for the next lab/quiz/assignment.
* In case of any query, please donot hesitate to contact us at support@iqrajavaid.com.
******************************************** Good Luck ***************************************************
Payment Details
Quiz Price = $18
Please feel free to send us your queries at: support@iqrajavaid.com

Leave a Reply