ADAMA SCINCE AND TECHNOLOGY UNIVERSITY
SCHOOL OF ENGINEERING & INFORMATION TECHNOLOGIES
DEPARTMENT OF INFORMATION TECHNOLOGIES
IT 2206 – Database Programming
Makeup/Supp Examination
July 17, 2012 Duration: 2 hrs
Name:__________________________________________
ID:_____________________________________________
Notes:
1. This exam booklet has 8 pages including cover page.
2. This is a closed-book, closed-notes exam. Your answer should be clear and accurate.
3. Make sure your cell phone is switched off!
FOR INSTRUCTORS’ USE ONLY
Part Score Remarks
I
II
Total
Part I: Multiple choice questions. [30 points, 1 each]
Choose your answers and write on the space provided.
Note: In cases where the question might have more than one answers you will be asked to choose
more than one answer.
_____1. Which one of the following factor is not important to choose between different DBMS
providers?
a. Scalability features
b. Security features
c. Performance of Query execution.
d. Amount of disk space requirement during installation.
e. None of the above.
_____2. A view is which of the following?
a. A virtual table that can be accessed via SQL commands
b. A virtual table that cannot be accessed via SQL commands
c. A base table that can be accessed via SQL commands
d. A base table that cannot be accessed via SQL commands
_____3. The command to eliminate a table from a database is:
a. REMOVE TABLE CUSTOMER;
b. DROP TABLE CUSTOMER;
c. DELETE TABLE CUSTOMER;
d. UPDATE TABLE CUSTOMER;
_____4. ON UPDATE CASCADE ensures which of the following?
a. Normalization
b. Data Integrity
c. Materialized Views
d. All of the above.
_____5. Which of the following is valid SQL for an Index?
a. CREATE INDEX ID;
b. CHANGE INDEX ID;
c. ADD INDEX ID;
d. REMOVE INDEX ID;
_____6. The SQL keyword(s) ________ is used with wildcards.
a. LIKE only
b. IN only
c. NOT IN only
d. IN and NOT IN
_____7. The HAVING clause does which of the following?
a. Acts like a WHERE clause but is used for groups rather than rows.
b. Acts like a WHERE clause but is used for rows rather than columns.
c. Acts like a WHERE clause but is used for columns rather than groups.
d. Acts EXACTLY like a WHERE clause.
_____8. To remove duplicate rows from the results of an SQL SELECT statement, the ________ qualifier
specified must be included.
a. ONLY
b. UNIQUE
c. DISTINCT
d. SINGLE
2|Page
_____9. Find the SQL statement below that is equal to the following: SELECT NAME FROM CUSTOMER
WHERE STATE = 'VA';
a. SELECT NAME IN CUSTOMER WHERE STATE IN ('VA');
b. SELECT NAME IN CUSTOMER WHERE STATE = 'VA';
c. SELECT NAME FROM CUSTOMER WHERE STATE LIKE 'VA*';
d. SELECT NAME FROM CUSTOMER WHERE STATE IN ('VA');
_____10. Which one of the following sorts rows in SQL?
a. SORT BY
b. ALIGN BY
c. ORDER BY
d. GROUP BY
_____11. The SQL keyword BETWEEN is used:
a. for ranges.
b. to limit the columns displayed.
c. as a wildcard.
d. None of the above is correct.
_____12. The command to remove rows from a table 'CUSTOMER' is:
a. REMOVE FROM CUSTOMER ...
b. DROP FROM CUSTOMER ...
c. DELETE FROM CUSTOMER WHERE ...
d. UPDATE FROM CUSTOMER ...
_____13. The SQL WHERE clause:
a. limits the column data that are returned.
b. limits the row data are returned.
c. Both A and B are correct.
d. Neither A nor B are correct.
_____14. You want to retrieve all employees, whether or not they have matching departments in the
departments table. Which query would you use?
a. SELECT LAST_NAME, DEPARTMENT_NAME
FROM EMPLOYEES E LEFT OUTER JOIN DEPARTMENTS D
ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID);
b. SELECT LAST_NAME, DEPARTMENT_NAME
FROM EMPLOYEES E RIGHT OUTER JOIN DEPARTMENTS D
ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID);
c. SELECT LAST_NAME, DEPARTMENT_NAME
FROM EMPLOYEES E FULL OUTER JOIN DEPARTMENTS D
ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID);
d. None
_____15. Which two statements about views are true? (Choose two.)
a. A view can be used to update tables.
b. A view can be created as a join on two or more tables.
c. A view cannot have an ORDER BY clause in the SELECT statement.
d. A view cannot be created with a GROUP BY clause in the SELECT statement.
e. A view must have aliases defined for the column names in the SELECT statement
_____16. Evaluate this SQL statement:
SELECT E.EMPLOYEE_ID, E.DEPARTMENT_ID, D.DEPARTMENT_NAME,E. SALARY
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;
Which SQL statement is equivalent to the above SQL statement?
a. SELECT EMPLOYEE_ID, DEPARTMENT_ID, DEPARTMENT_NAME, SALARY
FROM EMPLOYEES
3|Page
WHERE DEPARTMENT_ID
IN ( SELECT DEPARTMENT_ID
FROM DEPARTMENTS);
b. SELECT EMPLOYEE_ID, DEPARTMENT_ID, DEPARTMENT_NAME, SALARY
FROM EMPLOYEES
NATURAL JOIN DEPARTMENTS;
c. SELECT EMPLOYEE_ID, D.DEPARTMENT_ID, DEPARTMENT_NAME, SALARY
FROM EMPLOYEES E
JOIN DEPARTMENTS D
ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
d. SELECT EMPLOYEE_ID, DEPARTMENT_ID, DEPARTMENT_NAME, SALARY
FROM EMPLOYEES
JOIN DEPARTMENTS
USING (E.DEPARTMENT_ID, D.DEPARTMENT_ID);
_____17. In which case would you use a FULL OUTER JOIN?
a. Both tables have NULL values.
b. You want all unmatched data from one table.
c. You want all matched data from both tables.
d. You want all unmatched data from both tables.
e. One of the tables has more data than the other.
f. You want all matched and unmatched data from only one table.
_____18. Evaluate this SQL statement:
SELECT e.EMPLOYEE_ID,e.LAST_NAME,e.DEPARTMENT_ID, d.DEPARTMENT_NAME
FROM EMP e, DEPARTMENT d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID;
In the statement, which capabilities of a SELECT statement are performed?
a. Selection, projection, join
b. Difference, projection, join
c. Selection, intersection, join
d. Intersection, projection, join
e. Difference, projection, product
_____19. What is true about joining tables through an equijoin?
a. You can join a maximum of two tables through an equijoin.
b. You can join a maximum of two columns through an equijoin.
c. You specify an equijoin condition in the SELECT or FROM clauses of a SELECT statement.
d. To join two tables through an equijoin, the columns in the join condition must be
primary key and foreign key columns.
e. You can join n tables (all having single column primary keys) in a SQL statement by
specifying a minimum of n-1 join conditions.
_____20. In which three cases would you use the USING clause? (Choose three.)
a. You want to create a nonequijoin.
b. The tables to be joined have multiple NULL columns.
c. The tables to be joined have columns of the same name and different data types.
d. The tables to be joined have columns with the same name and compatible data types.
e. You want to use a NATURAL join, but you want to restrict the number of columns in the
join condition.
_____21. Examine the structure of the EMPLOYEES and DEPARTMENTS tables:
4|Page
EMPLOYEES
EMPLOYEE_ID: NUMBER
DEPARTMENT_ID: NUMBER
MANAGER_ID: NUMBER
LAST_NAME :VARCHAR2(25)
DEPARTMENTS
DEPARTMENT_ID: NUMBER
MANAGER_ID: NUMBER
DEPARTMENT_NAME: VARCHAR2(35)
LOCATION_ID: NUMBER
You want to create a report displaying employee last names, department names, and locations.
Which query should you use to create an equi-join?
a. SELECT LAST_NAME, DEPARTMENT_NAME, LOCATION_ID
FROM EMPLOYEES , DEPARTMENTS ;
b. SELECT EMPLOYEES.LAST_NAME, DEPARTMENTS.DEPARTMENT_NAME,
DEPARTMENTS.LOCATION_ID
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID =D.DEPARTMENT_ID;
c. SELECT E.LAST_NAME, D.DEPARTMENT_NAME, D.LOCATION_ID
FROM EMPLOYEES E, DEPARTMENTS D
WHERE MANAGER_ID =MANAGER_ID;
d. SELECT E.LAST_NAME, D.DEPARTMENT_NAME, D.LOCATION_ID
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID =D.DEPARTMENT_ID;
_____22. In which two cases would you use an outer join? (Choose two.)
a. The tables being joined have NOT NULL columns.
b. The tables being joined have only matched data.
c. The columns being joined have NULL values.
d. The tables being joined have only unmatched data.
e. The tables being joined have both matched and unmatched data.
f. Only when the tables have a primary key/foreign key relationship.
_____23. A SELECT statement can be used to perform these three functions:
Choose rows from a table.
Choose columns from a table.
Bring together data that is stored in different tables by creating a link between them.
Which set of keywords describes these capabilities?
a. difference, projection, join
b. selection, projection, join
c. selection, intersection, join
d. intersection, projection, join
e. difference, projection, product
5|Page
_____24. Consider the following data for table PROMOTIONS
You need to display all promo categories that do not have 'discount' in their [Link]
two SQL statements give the required result? (Choose two.)
a. SELECT promo_category FROM promotions MINUS SELECT promo_category
FROM promotions WHERE promo_subcategory = 'discount'
b. SELECT promo_category FROM promotions INTERSECT SELECT promo_category FROM
promotions WHERE promo_subcategory = 'discount'
c. SELECT promo_category FROM promotions MINUS SELECT promo_category FROM
promotions WHERE promo_subcategory <> 'discount'
d. SELECT promo_category FROM promotions INTERSECT SELECT promo_category FROM
promotions WHERE promo_subcategory <> 'discount'
_____25. Given the structure for the table PROMOTIONS
Which two SQL statements would execute successfully? (Choose two.)
a. UPDATE promotions SET promo_cost = promo_cost+100 WHERE
TO_CHAR(promo_end_date,'yyyy'>'2000';
b. SELECT promo_begin_date FROM promotions WHERE
TO_CHAR(promo_begin_date,'mon dd yy')='jul 01 98';
c. UPDATE promotions SET promo_cost = promo_cost+100 WHERE promo_end_date >
TO_DATE(SUBSTR('01-JAN-200',8));
6|Page
d. SELECT TO_CHAR(promo_begin_date,'dd/month') FROM promotions WHERE
promo_begin_date IN (TO_DATE('JUN 01 98',TO_DATE('JUL 01 98'));
_____26. Which two statements are true about sequences created in a single instance database? (Choose
two.)
a. The numbers generated by a sequence can be used only for one table
b. DELETE <sequencename> would remove a sequence from the database
c. CURRVAL is used to refer to the last sequence number that has been generated
d. When the MAXVALUE limit for a sequence for reached, you can increase the
MAXVALUE limit by using the ALTER SEQUENCE statement
e. When a database instance shuts down abnormally, the sequence numbers that have
been cached but not used would be available once again when the database instance is
restarted
_____27. Which two statements are true regarding working with dates? (Choose two.)
a. The default internal storage of dates is in the numeric format
b. The default internal storage of dates is in the character format
c. The RR date format automatically calculates the century from the SYSDATE function and
does not allow the user to enter the century
d. The RR date format automatically calculates the century from the SYSDATE function but
allows the user to enter the century if required
_____28. Which two statements are true regarding views? (Choose two.)
a. A subquery that defines a view cannot include the GROUP BY clause
b. A view is created with the subquery having the DISTINCT keyword can be updated
c. A view that is created with the subquery having the pseudo column ROWNUM keyword
cannot be updated
d. A Data Manipulation Language (DML) operation can be performed on a view that is
created with the subquery having all the NOT NULL columns of a table
_____29. Examine the structure of the MARKS table:
Which two statements would execute successfully? (Choose two.)
a. SELECT student_name, subject1 FROM marks WHERE subject1 > AVG(subject1);
b. SELECT student_name,SUM(subject1) FROM marks WHERE student_name LIKE 'R%';
c. SELECT SUM (subject1+subject2+subject3) FROM marks WHERE student_name IS NULL
d. SELECT SUM (DISTINCT NVL(subject1,0)),MAX(subject1) FROM marks WHERE subject1 >
subject2;
_____30. NEW_CUSTOMERS is a new table with the columns CUST_ID, CUST_NAME and CUST_CITY that
have the same data types and size as the corresponding columns in the CUSTOMERS table.
Evaluate the following INSERT SQL statement:
7|Page
The INSERT statement fails when executed. What could be the reason?
a. The VALUES clause cannot be used in an INSERT with a subquery
b. Column names in the NEW_CUSTOMERS and CUSTOMERS tables do not match
c. The WHERE clause cannot be used in a subquery embedded in an INSERT statement
d. The total number of columns in the NEW_CUSTOMERS table does not match the total
number of columns in the CUSTOMERS table
Part II: Work out. [20 points]
Given the following table structure for storing the results of Grade 8 Elementary School students:
Student(Name, Stud ID, Sex, BirthDate,Rank) --StudID is primary Key
Subject(SubjectName, SubjectID)—SubjectID is primary Key
Result(StudID,SubjectID, Mark) – StudID and SubjectID are foreign keys
Constraints:
Sex is either M or F
Mark is between 0 and 100
If StudID is deleted from Student table related rows in result table should also be deleted.
If StudID is updated in student table related rows in result table should also be updated.
Write an SQL query to accomplish the following
1. Write SQL query to Create all tables with the constraints.(6points)
8|Page
2. Write SQL query to Insert one row in Student table.( Abebe, 001, Male, June 10, 1995)[2point]
3. Write SQL query to display name and StudID of all students.[4point]
4. Write SQL query to display total number of male and female students.[2point]
5. Write SQL query to display the names of the students who scored more than 85 for the subject
Mathematics.[2point]
9|Page
6. Write SQL query to query to display the name(s) of the student(s) who scored the maximum for the
subject Mathematics.[2point]
7. Create a view Average(StudID, Avarege) that calculates the Average of each student.[2point]
8. Using the View average update the Rank column of the Student table.[2point]
10 | P a g e