Data Management Sample Exam

Job(JobClass, HourlyRate) Job.JobClass primary key. Employee(EmpNo, EmpName, JobClass) Employee.EmoNo primary key. Employee.JobClass references Job.JobClass. Project(ProjNo, ProjName) Project.ProjNo primary key. ProjectEmployee(ProjNo, EmpNo, Hours) ProjectEmployee.(ProjNo, EmpNo) primary key. ProjectEmployee.ProjNo references Project.ProjNo. ProjectEmployee.EmpNo references Employee.EmpNo.

(ProjNo, EmpNo) -> (ProjName, EmpName, JobClass, HourlyRate, Hours) ProjNo -> ProjName EmpNo -> (EmpName, JobClass, HourlyRate) JobClass -> HourlyRate

true

A database administrator is a support position that specializes in managing individual databases and database management systems.

false

A database is said to be shared when multiple users can access the same database for multiple uses, but must take turns accessing the database.

true

A non-procedural language specifies what parts of a database to retrieve, not the detail of how retrieval occurs.

True

A procedural language interface is necessary because nonprocedural access and application development tools of database software are sometimes not powerful enough or do not provide the level of control necessary for significant application development.

SELECT DISTINCT Offering.CourseNo, CrsDesc FROM Course, Offering WHERE Course.CourseNo = Offering.CourseNo AND Offering.CourseNo NOT IN Note: (SELECT CourseNo FROM Enrollment, Offering, Student WHERE Enrollment.OfferNo = Offering.OfferNo AND Enrollment.StdNo = Student.StdNo AND StdClass = 'JR')

Among courses that have been offered, get course numbers (CourseNo) and course descriptions (CrsDesc) of courses that were not taken by any 'JR'-class students.

None of the above

An unknown value, or a value that does not apply to a given row of a table, is indicated by:

True

Data is said to be interrelated when data stored as separate units can be connected to provide a whole picture.

data type

For each column in a table, the ___________________ defines a set of values and permissible operations on those values.

SELECT FacDept, FacRank, FacNo, FacFirstName, FacLastName, FacSalary FROM Faculty AS F1 WHERE FacSalary = (SELECT MAX(FacSalary) FROM Faculty AS F2 WHERE F2.FacDept = F1.FacDept)

For each faculty department (FacDept), find the faculty number, first name, last name, and the salary of faculty who receive the highest salary.

SELECT StdMajor, Round(Avg(StdGPA), 1) as AvgGPA FROM Student WHERE StdClass in ('FR', 'SO') GROUP BY StdMajor HAVING Avg(StdGPA) > 3.3

Get the averageStdGPA among freshman('FR') and sophomore('SO') students by majors. Only list the majors whose average StdGPA among freshman ('FR') and sophomore ('SO')students is greater than 3.3.

SELECT DISTINCT CourseNo FROM Enrollment, Offering, Student WHERE Enrollment.OfferNo = Offering.OfferNo AND Enrollment.StdNo = Student.StdNo AND (StdMajor = 'FIN' OR StdMajor = 'ACCT') AND StdClass = 'SO'

Get the course numbers of courses taken by FIN or ACCT sophomore ('SO') students.

SELECT DISTINCT Offering.CourseNoFROM Offering INNER JOIN (Student INNER JOIN Enrollment ON Student.StdNo = Enrollment.StdNo) ON Offering.OfferNo = Enrollment.OfferNoWHERE StdClass = 'SR' and (StdMajor = 'FIN' or StdMajor = 'ACCT')

Get the course numbers of courses taken by senior (SR) students who major in FIN or ACCT.

SELECT DISTINCT F1.FacNo, F1.FacFirstName, F1.FacLastName FROM Faculty F1, Offering O1 WHERE F1.FacNo = O1.FacNo AND CourseNo Like 'IS*' AND NOT EXISTS (SELECT * FROM Offering O2 WHERE O2.FacNo = F1.FacNo AND CourseNo NOT LIKE 'IS*' )

Get the faculty numbers and names of faculty who offered onlyIS courses. (Note they did not offer any non-IS course at all.)

SELECT StdMajor, MAX(StdGPA) AS MaxGPA FROM Student WHERE StdClass = 'JR' or StdClass = 'SR' GROUP BY StdMajor HAVING AVG(StdGPA) > 3.05

Get the highest GPA of junior ('JR') or senior ('SR') students by majors. Only list the majors with average GPA greater than 3.05.

SELECT Student.StdNo, Student.StdFirstName, Student.StdLastName, count(*) as NoOfOfferings FROM Student INNER JOIN Enrollment ON Student.StdNo = Enrollment.StdNo GROUP BY Student.StdNo, StdFirstName, StdLastName

Get the number of offerings taken by every student. That is, list each student's student number, student name, and the number of offers taken by the student

SELECT DISTINCT Student.StdNo, Student.StdFirstName, Student.StdLastName FROM (Faculty INNER JOIN Offering ON Faculty.FacNo = Offering.FacNo) INNER JOIN (Student INNER JOIN Enrollment ON Student.StdNo = Enrollment.StdNo) ON Offering.OfferNo = Enrollment.OfferNo WHERE StdMajor = 'ACCT' and (FacFirstName = 'LEONARD' and FacLastName = 'FIBON')

Get the student numbers and names ofACCT-major students who took Prof. LEONARD FIBON's offering.

SELECT DISTINCT Student.StdNo, Student.StdFirstName, Student.StdLastName, Student.StdGPA FROM Offering INNER JOIN (Student INNER JOIN Enrollment ON Student.StdNo = Enrollment.StdNo) ON Offering.OfferNo = Enrollment.OfferNo WHERE CourseNo = 'FIN480' and (OffTerm = 'Spring' and OffYear = 2013)

Get the student numbers, names,and GPAs of students who took course 'FIN480' in SPRING 2013

SELECT count(*) AS NoOfOffs FROM Offering WHERE FacNo is NULL

How many class offerings have not been assigned to any faculty member?

6

How many foreign keys are defined from the following entity relationship diagram?

False

If X is a foreign key of table T referencing the same table T's primary key A, then X must be unique.

True

In the evolution of database technology, second-generation products (network databases and hierarchical databases) were the first true database management systems because they could manage multiple entity types and relationships.

True

In the set-oriented database terminology, a tuple is synonymous with a row.

False

In the set-oriented database terminology, a type is synonymous with a column.

SELECT DISTINCT FacNo, FacFirstName, FacLastName, FacDept FROMFaculty WHERE (FacDept = 'MS' or FacDept= 'FIN')and FacRank = 'PROF'

List faculty number, name, and department of full professors who belong to Management Science (MS) or Finance (FIN) department. (Note, full professors' FacRank is recorded as PROF.)

SELECT T.CourseNo, T.CrsDesc FROM (SELECT T.CourseNo, T.CrsDesc, Count(StdNo) as TotalEnrollments FROM (SELECT Course.CourseNo, CrsDesc, Offering.OfferNo, StdNoFROM Offering, Enrollment, Course WHERE Offering.OfferNo = Enrollment.OfferNo AND Course.CourseNo = Offering.CourseNoGROUP BY Course.CourseNo, CrsDesc, Offering.OfferNo, StdNo) T GROUP BY T.CourseNo, T.CrsDesc ) WHERE TotalEnrollments = (Select Max(TotalEnrollments) FROM (SELECT T.CourseNo, T.CrsDesc, Count(StdNo) as TotalEnrollmentsFROM (SELECT Course.CourseNo, CrsDesc, Offering.OfferNo, StdNo FROM Offering, Enrollment, Course WHERE Offering.OfferNo = Enrollment.OfferNo AND Course.CourseNo = Offering.CourseNo GROUP BY Course.CourseNo, CrsDesc, Offering.OfferNo, StdNo) T GROUP BY T.CourseNo, T.CrsDes ))

List the course number and course description of the course that was taken by more students than any other courses were.(Note: Do not use "Top 1"or "Limit 1"in your query.)

SELECT Offering.CourseNo, Offering.OfferNo, count(StdNo) as NoOfStds, Avg(EnrGrade) as AvgGrade FROM Offering INNER JOIN Enrollment ON Offering.OfferNo = Enrollment.OfferNo WHERE (OffTerm = 'WINTER' and OffYear = 2013) GROUP BY CourseNo, Offering.OfferNo HAVING count(StdNo) >= 5 ORDER BY CourseNo asc, Offering.OfferNo desc

List the course number, offering number, and average grade of students enrolled in WINTER 2013 course offerings in which at least 5students are enrolled. Sort the result by course number in the ascending order and next offering number in the descending order.

SELECT DISTINCT Faculty.FacNo, Faculty.FacFirstName, Faculty.FacLastName FROM Faculty, Offering WHERE Faculty.FacNo = Offering.FacNo AND CourseNo Not Like 'IS*' AND FacDept = 'FIN'AND FacRank = 'PROF'

List the faculty numbers and names of finance professors (whose FacDept is 'FIN') who did not offer any IS course at all.

SELECT FacFirstName, FacLastName,O1.CourseNoFROM Faculty, Offering O1, Offering O2WHERE Faculty.FacNo = O1.FacNoAND Faculty.FacSupervisor = O2.FacNoAND O1.OffYear = 2013 AND O2.OffYear = 2013AND O1.CourseNo = O2.CourseNo

List the names of faculty members and the course numbers for which the faculty member teaches the same course as his or her supervisor in 2013.

SELECT DISTINCT Offering.OfferNo, Offering.CourseNo, Course.CrsDesc, Offering.OffDays, Offering.OffTime FROM Course INNER JOIN Offering ON Course.CourseNo = Offering.CourseNo WHERE (CrsDesc like '*DATABASE*' or CrsDesc like '*SYSTEMS*') and OffYear = 2013

List the offering number, course number, course description, days, and time of offerings containing the words "DATABASE" or "SYSTEMS" in the course description and taught in 2013.

SELECT DISTINCT StdCity, StdStateFROM Student WHEREStdMajor = 'ACCT'

List the unique city and state combinations of ACCT-major students.

True

Nonprocedural languages do not include looping statements.

none of the above

Not to allow the same action performed on reference rows when a record is updated, you would specify the clause "ON UPDATE ________" when creating the table.

false

Suppose A1 and A2 are attributes of a table. If (A1, A2) is the composite primary key of the table, then A1 must be unique in the table.

true

Suppose A1 and A2 are attributes of a table. If (A1, A2) is the composite primary key of the table, then A1 must not be null.

False

Suppose X is a foreign key in a table. Then, X must not be null.

true

Suppose an attribute A is a foreign key in a table. Then, A may be null.

6 records

Suppose table T1 has 4 records and table T2 has 6 records. T1(c1, c2, ...) T2(c3, c1, ...) c1 not null T2.c1 references T1.c1. When "select * from T1, T2 where T1.c1=T2.c1" is processed, how many records will be displayed?

entity integrity

The ____________________ rule states that each table must have a column or combination of columns with unique values.

Data independence

The ability to add the new column without requiring changes to all of the application programs strongly relates to the following feature of modern database systems:

False

The goal of information resource management is to use information technology as a tool for processing, distributing, and integrating information for isolated functions within an organization.

False

The referential integrity rule prohibits foreign keys to have null values.

3rd normal form

The removal of transitive dependency is required for the:

True

The retrieval of summarized data to support long-range decisions is a dominant feature of data warehousing, whereas a mixture of updating and retrieving data is characteristic of databases that support the day to day operations of an organization.

Data independence

The three-schema architecture is strongly related to the following feature of database systems:

none of the above

To ensure that a database contains valid connections, the _____________________ rule states that the values of columns in one table must match the values of columns in other tables.

composite key

When more than one column in a table is needed to uniquely identify a row, this type of key is called a(n):

Select * FROM (SELECT Distinct FacDept, Avg(FacSalary) as AvgSalary FROM FacultyGroup by FacDept)WHERE AvgSalary = (SELECT Max(AvgSalary) FROM (SELECT Distinct FacDept, Avg(FacSalary) as AvgSalary FROM Faculty Group by FacDept))

Which faculty department's average faculty salary is the highest? (Get the faculty department and its average faculty salary.)

SELECT StdMajor, AVG(StdGPA) AS AvgStdGPA FROM Student GROUP BY StdMajor HAVING AVG(StdGPA) >=ALL (SELECT AVG(StdGPA) FROM Student GROUP BY StdMajor)

Which major's average student GPA is the highest? (Get the major and its average GPA.)

File orientation

Which of the following orientation features does not qualify a system as using true database technology?

Retrieving an employee hiring date

Which one of the following is the least likely to be viewed as a transaction?