Cp363 Database Midterm Exam Latest Update

Cp363 Database Midterm Exam Latest Update 2024-2025 Questions and Verified Correct Answers Guaranteed A+

3 types that data can be? - CORRECT ANSWER: - static (part #, SIN)

• Dynamic (quantity, balance)
• Quasi-static (salary)

A relation schema R of degree n is represented as ? - CORRECT ANSWER:

R(A1,A2,....,An)

An n tuplet t n a relation r(R) is denoted by ? - CORRECT ANSWER: t = where vi is the value of attribute Ai

Approximate numeric (floating point) data types? - CORRECT ANSWER: SQL-92

supports:

• REAL (single precision floating point with implementation dependent precision)
• DOUBLE PRECISION (implementation dependent double precision number
• FLOAT(p) provides binary precision greater than or equal to p

Can you use logical operators in sql? - CORRECT ANSWER: yes

precedence NOT AND OR

SELECT studentName, GPA FROM student WHERE (GPA >= 10 AND Degree = 'B.C.S') OR (GPA >- 10.5 AND Degree = 'B.A');

• this lists all students in the BCS program with a gpa of 10 or more or those in BA with

gpa greater than 10.5

Character strings data types? - CORRECT ANSWER: fixed size string

CHAR(size): size characters long

• use CHAR for a single character

Variable length string VARCHAR(size) no blank padding is done

• / 3

CREATE TABLE T means? - CORRECT ANSWER: T here is nto a table, it is a relation

(table) variable whose values are relations, it is different relations at different times

Database hardware - CORRECT ANSWER: - hardware components of database

system consist of disks in which data are stored to perform:

• direct access to subset portions of data
• Rapid I/O

Database system - software - CORRECT ANSWER: between physically stored data

and users of the systems there is a layer of software:

• database manager
• database server
• database management system

Describe the types of attributes in an ER database? - CORRECT ANSWER: - simple or composite

• single valued or multi valued
• stored/based or derived

an attribute can be key or non key an attribute can have a null value

example of a query that lists course# and student#s in descending and ascending

orders - CORRECT ANSWER: SELECT courseNo, studetNo

FROM enrolled ORDER BY courseNo DESC, studentNo ASC;

• do not need to include ASC its default

example of an SQL statement with order by - CORRECT ANSWER: SELECT *

FROM student WHERE GPA >= 10

ORDER BY GPA DESC;

• DESC means descending order of GPAs
• ASC means ascending order or alphabetical
• ASC is default

example of an sql statment that uses same attribute name in different tables -

CORRECT ANSWER: SELECT student.studentNo, studentName

FROM enrolled, student WHERE profName = 'zia" AND courseNo = 8203 AND enrolled.studentNo = student.StudentNo ORDER BY studentNo ASC;

• / 3

-last condition specifies the join condition

• use table name to identify the attribute as in student.studentNo

example of how to use count aggregate function in sql statement - CORRECT

ANSWER: SELECT courseNo, COUNT( DISTINCT studentNo) AS Number_enrolled

FROM enrolled GROUP BY courseNo;

• this counts the number of students by student number in the enrolled table of data to

see how many students are enrolled

• uses distinct to make sure every student number is counted once

example of sql statement using HAVING - CORRECT ANSWER: SELECT degree,

AVG(GPA)

FROM student GROUP BY degree HAVING AVG(GPA) > (SELECT AVG(GPA) FROM student;

• this gives a list of degree programs that maintain above average GPA

example sql statement with NOT EXISTS - CORRECT ANSWER: SELECT profName

FROM can_teach ct WHERE courseNo. =102

AND NOT EXISTS

(SELECT *

FROM teaches t WHERE t.courseNo = 102 AND ct.profName = t.profName);

example: list student names and their GPAs for all students in a student database? - CORRECT ANSWER: SELECT StudentNae, 'Overall GPA is: ', GPA FROM student;

Explain a candidate key in an ER? - CORRECT ANSWER: - minimal subset of

attributes that uniquely identifies an entity ex; employee #, SIN

explain a foreign key when creating tables? - CORRECT ANSWER: - a combination of columns of one relation that references primary key attributes of a secnd relation

• also known as referential integrity constraint

CREATE TABLE teaches ( courseNo NUMBER REFERENCES course(courseNo));

• courseNo is our foreign key
• / 3