Font size
WorksheetsAdvance Database Pretest
Total questions: 200
Worksheet time: 2hrs 20mins
_________ is a collection of facts, figures, objects, symbols, and events from different sources.
Information
Data
Database
Key
Collection of information that is organized so that it can be easily accessed, managed and update _________.
Information
Entity
Data
Database
A data type representing whole numbers
String
Float
Integer
Date
Type of attribute, which is atomic value, means it cannot be divided further. Ex is student phone number.
Attribute
Derived attribute
Single- value attribute
Simple attribute
What is the name of the given picture?
WAMPP Server
XAMPP Server
Entity Relationship Diagram
None of the above
ERD stands for
Entity Relationship Diagram
Entire Relations Diagram
Entities Relationship Model
Entity Relationship Distribute
A data type representing numbers with fractional parts
Integer
Character
Float
Boolean
________ is an attribute or collection of attributes that uniquely identifies an entity among entity set.
Super key
Primary key
Unique
Key
Attributes that contain more than one value. For example a person, contain more than one phone number, email, etc.
Derived attributes
Multi-value attributes
Single- value attribute
Composite attribute
One entity set from set A can be associated with at most one entity set of B and vice versa is called _________.
One to many
Many to many
Many to one
One to one
Entities are represented by means of
(a)
Relationship are represented by
Diamond shape
Rectangle shape
Circle shape
Square shape
Type of database that the data is organized based on table with rows and columns.
Key value
Database
No SQL database
Relational database
Data type that stores character strings (letters, numbers and special characters)
variable
Varchar
String
Integer
__________ is a designated attribute (column) that can reliably identify and distinguish between each individual record in a table.
Primary key
Key
Candidate key
Single key
update guirreRose set guirre_strand = 'TVL' where deptno = 30;
This is rejected, why is it rejected?
Dept is not a key preserved base table.
Because there is an aggregation function
There is a check option.
SQL> create view guirreRose as
2 select empno, ename, sal, comm, dept.deptno, dname, loc
3 from emp, dept
4 where emp.deptno = dept.deptno;
View created.
The user then runs:
update guirreRose set loc = 'CASHEL' where deptno = 30;
Is the command accepted or rejected?
Accepted
Rejected
SQL> create view guirreRose as
2 select empno, ename, sal, comm, dept.deptno, dname, loc
3 from emp, dept
4 where emp.deptno = dept.deptno;
View created.
The user then runs:
update guirreRose set ename = 'ROSE' where empno = 7934;
Is this accepted or rejected?
Accepted
Rejected
Trigger is invoked before/after data row is:
deleted
updated
inserted
all answers are correct
Which of the following is the correct command to create a view?
CREATE VIEW guirreRose AS
SELECT VIEW guirreRose FROM
CREATE TABLE VIEW guirreRose AS
CREATE VIEW TABLE guirreRose FROM
CREATE FUNCTION totalPaidByCust(custID INT)
........
END
If you want to query the function, the command will be:
Given the syntax below what is the attribute name:
CREATE TABLE guirreRose. guirre_BSIS (guirre_Score INT (3) NOT NULL), ENGINE=INNODB;
guirreRose
guirre_BSIS
guirre_Score
Given the syntax below, its accepted or rejected?
ALTER TABLE dbName. table_name CHANGE ATTRIB NAME (original guirre_attrib_name new_guirre_attrib_name DATATYPE (DATA LENGTH) NULL), ENGINE=INNODB;
Accepted
Rejected
What is the codes of the output above:
INSERT INTO guirreRose.guirreCampus`
(`guirre_bpc_campus`,` guirre_course`, `guirre_year`, `guirre_section`) VALUES ('ANGAT', 'COMSEC','2','A')
CREATE guirreRose.guirreCampus`(`guirre_bpc_campus`,
` guirre_course`, `guirre_year`, `guirre_section`) VALUES ('ANGAT', 'COMSEC','2','A')
UPDATE guirreRose.guirreCampus`(`guirre_bpc_campus`,
` guirre_course`, `guirre_year`, `guirre_section`) VALUES ('ANGAT', 'COMSEC','2','A')
ALTER guirreRose.guirreCampus`(`guirre_bpc_campus`,
` guirre_course`, `guirre_year`, `guirre_section`) VALUES ('ANGAT', 'COMSEC','2','A')
Identify the attribute/s from the codes below:
UPDATE `college_applications` SET
`year` = '2' WHERE
`college_applications`.`id` = 0519;
year
id
year and id
college applications
ALTER TABLE guirreRose. guirre_bsis CHANGE guirre_sec (guirre_sec new_guirre_section VARCHAR (1) NOT NULL) ENGINE=INNODB;
What would be the new attribute name?
guirreRose
guirre_bsis
guirre_sec
guirre_section
Data Definition Language
Insert, Update, Delete, Select
Insert, Create, Delete, Select
Create, Alter, Drop, Truncate
Insert, Alter, Drop, Truncate
Data Manipulation Language (DML)
Insert
Update
Create
Select
Truncate
The following are the fields of application of database except?
Surveys
Online Transactions
Trading goods
Manufacturing
Chemical Factory of Waste Materials
Which of the following are the drawbacks of using file systems to store data. Please select all that applies.
Data redundancy
Data inconsistency
Integrity problems
Difficulty in accessing data
Data isolation
This level of abstraction describes how a record is stored. What is level is this?
Physical Level
View Level
Logical Level
This level of abstraction describes data stored in database, and the relationships among the data. What is level is this?
Physical Level
View Level
Logical Level
_____ is the logical structure of the database. What is blank refers to?
Schema
Instance
_____ is the actual content of the database at a particular point in time. What is blank refers to?
Schema
Instance
Which of the following is/are types of data models? Select all that applies.
Entity-Relationship model
Relational model
Object-oriented model
Structural model
Network model
The database schema is written in __________. What does the blank refers to?
DDL
DML
SQL means ____?
Selection Query Language
Search Query Language
Structured Query Language
What refers to a collection of operations that performs a single logical function in a database application?
Transaction Management
Database Administrator
Storage Management
Database Users
The symbol of greater than use in inserting text and numeric data.
*
>
&
Division symbol in spreadsheet.
/
+
=
Concatenation (joins text)
/
&
*
Exponentiation (raised to the power)
=
*
^
Multiplication symbol in Microsoft Excel
&
*
^
A spreadsheet is like a filing system or a calculator?
/
X
Text entry is use for titles, headings and other notes.
/
X
Status bar is where data or formulas you enter into a worksheet appear for the active cell.
/
X
The formula starts with a plus sign.
/
X
Numeric data composed of numbers that can add, subtract, multiply divide and use of formulas.
/
X
The symbol of greater than use in inserting text and numeric data.
*
>
&
When inserting a formula, always start with
-
*
+
=
To sort data in Excel, go to the __________ ribbon.
Home
Data
Insert
View
If I want to find an average of cells A1 through A25, which formula would I use?
=average(A1:A25)
=avg(A1:A25)
=average(A1-A25)
What is this icon?
Zoom
Sort and Filter
Symbol
Merge and Center
To arrange data in ascending order of age we will
Filter it
Sort it
Apply Conditional Formatting
What can you use to help you see only what you want to see by hiding everything else.
Filters
Functions
Sorts
Sandra is creating a formula to subtract the values in cells B4 and C4. Which formula should she use?
=B4+C4
=B4-C4
=B4*C4
=B4/C4
Which of these functions will you use if you wish to evaluate more than one condition?
IF()
IFS()
IFELSE()
IFELSES()
Which of these functions reverses the condition?
AND()
OR()
NOT()
NOR()
In cell A1 we have the following "CHN_123456", what function help us removing the "CHN_" from that text?
=SUBSTITUTE
(A1,"CHN_","")
=REPLACE
(A1,"CHN_","")
=CHANGE
(A1,"CHN_","")
=LEFT
(A1,"CHN_","")
If I have the text "123456 - 010" in cell A1, what is the formula that allows me to call the first 6 characters?
=LEFT(A1,3)
=RIGHT(A1,6)
=LEFT(A1,6)
=RIGHT(A1,3)
What is the function that allow us to remove unwanted spaces in a text?
=SUBSTITUTE
=REPLACE
=VALUE
=TRIM
What are the main advantages of using the INDEX and MATCH functions in Excel?
They can perform complex calculations
They can handle multiple criteria
They are faster than VLOOKUP
They create dynamic charts
When might you use the Index-Match combination instead of VLOOKUP?
When you need to perform calculations on the result
When you want to search in a single column
When you need to retrieve data from another worksheet
When working with text data only
What is the primary advantage of using XLOOKUP over VLOOKUP?
XLOOKUP is faster
XLOOKUP can handle approximate matches
XLOOKUP can handle multiple criteria
XLOOKUP is an older function
Which error-handling feature is available in XLOOKUP but not in VLOOKUP?
#N/A error handling
Custom error messages
Error propagation
None of the above
What is the primary purpose of the INDIRECT function?
To perform mathematical calculations
To create dynamic cell references
To handle text manipulation
To calculate averages
In which scenario might you use the INDIRECT function in financial models?
Calculating averages
Creating dynamic charts
Handling error messages
Extracting data from other workbooks
What is OFFSET primarily used for in Excel?
Calculating financial ratios
Creating dynamic ranges
Generating random numbers
Sorting data
How can OFFSET be used in financial modeling?
For sorting data
For calculating averages
For trend analysis and rolling summaries
For data validation
What is a notable advantage of using array formulas in Excel?
They are faster than regular formulas
They can handle only single criteria
They are easier to understand
They cannot handle irregular data sets
When might you use array formulas in financial analysis?
For simple calculations
When dealing with structured data only
When handling irregular data sets
For basic data extraction
What does VBA stand for in Excel?
Visual Basic for Application
Visual Basic for Analysis
Very Basic Automation
Virtual Business Applications
How can VBA be used in Excel for financial analysis?
To create pivot tables
To format cells
To automate repetitive tasks
To import data from external sources
What is the main benefit of using named ranges in Excel?
They make formulas more complex
They improve data accuracy
They slow down worksheet performance
They are used for text formatting
How can named ranges be used in financial modeling?
For creating custom charts
For speeding up data entry
For error reduction in formulas
For importing data from external sources
What is the primary purpose of the CHOOSE function?
To perform lookup operations
To calculate averages
To create dynamic charts
To sort data
When might you use the CHOOSE function in financial modeling?
For sorting data
For performing complex calculations
For dynamic scenario analysis
For text manipulation
How can array formulas be used in financial analysis?
For basic calculations
For data validation
For portfolio optimisation
For sorting data
What is a consideration when using array formulas?
They are always faster than regular formulas
They can only handle structured data
They may require more processing power
They are limited to simple calculations
How can VBA be used to enhance financial reporting?
To create dynamic charts
To automate data cleansing
To calculate averages
To import data from external sources
What is one of the main benefits of using VBA in financial analysis workflows?
It requires no programming knowledge
It slows down data processing
It reduces customisation options
It allows for advanced scenario analysis and modeling
9.
With SQL, how do you select a column named "FirstName" from a table named "Person"?
SELECT FirstName FROM Person
SELECT Person.FirstName
EXTRACT FirstName FROM Person
SELECT FROM Person.FirstName
What does the CREATE TABLE command do?
Adds data to a table
Defines a new table and its structure
Updates data in a table
Deletes a table
Which command did Arjun use to add a column to the employees table?
UPDATE
INSERT
ALTER TABLE
CREATE TABLE
What does the DISTINCT clause achieve in a query?
Filters data based on a condition
Eliminates duplicate values
Joins two tables
Displays all rows from a table
How did Arjun filter employees who joined in 2023?
Using the GROUP BY clause
Using the WHERE clause with a condition on YEAR(joining_date)
Using the ORDER BY clause
Using the LIKE operator
What is the default order in the ORDER BY clause?
Descending
Ascending
Random
Alphabetical
Which operator is used for pattern matching in SQL?
BETWEEN
LIKE
IN
IS NULL
What is the purpose of the JOIN operation?
Filters rows based on a condition
Combines rows from two or more tables based on common fields
Groups rows by a column
Orders rows in ascending or descending order
Which aggregate function calculates the total salary of employees?
COUNT
AVG
SUM
MAX
What does the IS NULL condition check for?
Rows with zero values
Rows with missing or undefined values
Rows that match a given pattern
Rows that meet a specific condition
What would this query achieve: SELECT department, SUM(salary) FROM employees GROUP BY department;
Filters employees in specific departments
Groups employees by department and calculates total salary per department
Joins the employees table with the department table
Updates the salary of employees in each department
Which of the following is an environment?
supermarket
staff
customer
vehicle
Which of the following is false about the entity relationship diagram?
Entities are represented by rectangles.
All simple attributes are represented with single-lined ovals.
Multi-valued attributes are represented with double-lined ovals.
A derived attribute is connected to the entity by broken lines.
Which of the following is not a notation of the entity relationship diagram?
Crow's foot
Crow's nest
Chen
UML class diagram
Which of the following is false about the entity relationship model (ERM)?
The ERM is a model representing the conceptual design of the database.
An ERM is represented by an entity relationship diagram (ERD).
An ERM needs to be translated to the data model used by the DBMS, during the logical design.
The ERM represents data in rows and columns.
Based on the diagram, which is the key attribute?
STUDENT
matrix
address
age
Based on the diagram, which is the entity?
STUDENT
matrix
address
age
Based on the diagram, which is the composite attribute?
STUDENT
matrix
address
age
Based on the diagram, which is the derived attribute?
STUDENT
matrix
address
age
Based on the diagram, how many simple attributes are there?
11
10
4
2
Based on the diagram, how many multiple-valued attributes are there?
10
9
4
2
Semantic relationships take a higher and more non-specific view of the relationship.
True
False
What is a candidate key?
Any attribute, or group of attributes, that can uniquely identify a tuple in a relation.
Any attribute, or group of attributes, that has no super key subsets.
Any attribute, or group of attributes, that has been selected to be the unique identifier of each tuple in a relation.
A combination of two fields that together can uniquely identify a record.
Complete the sentence below.
A _____ key is an attribute of a table that matches a primary key attribute in another table to create a relationship.
Candidate
Primary
Foreign
National
A composite key is a combination of two fields that together can uniquely identify a record.
True
False
Complete the sentence below.
A ____________ relationship is where a single record of a table is related to a single record of another table.
Many-to-many
Many-to-one
One-to-one
One-to-many
Complete the sentence below.
________ integrity is a rule that states that every entity must have a primary key field and the values in that field must be unique to each record.
Entity
Primary
True
False
What type of relationship should we not use in our database structure?
One-to-One
One-to-Many
Many-to-Many
Complete the sentence below.
A ______ key is any attribute or group of attributes, that can uniquely identify a tuple in a relation.
minimal
super
higher
fantastic
Referential Integrity is a rule states that every foreign key value must match the primary key value of a record in another table, or must be null.
True
False
What type of relationship is shown in this image?
One-to-One
One-to-Many
Many-to-Many
What is the primary role of a primary key in a table?
To store the most frequently accessed data
To uniquely identify each record in the table
To maintain referential integrity
To create relationships between tables
Which of the following is an example of a one-to-many relationship?
A student enrolled in multiple courses
An employee working in a single department
A product supplied by multiple suppliers
A customer placing multiple orders
What is a foreign key?
A key that provides a unique identifier for a table
A key that links two tables together
A key that allows many-to-many relationships
A key that can only be used in one table
In a many-to-many relationship, what is typically used to link two tables?
Foreign key
Junction table
Composite key
Primary key
Which of the following is a requirement for referential integrity?
Foreign key values must match the primary key in the related table
Foreign key values must match the primary key in the related table
Foreign key values must be null
Primary key values can be duplicated
Foreign key values must always be unique
What is the main function of a junction table?
To store additional information about a foreign key
To establish a one-to-one relationship between tables
To resolve many-to-many relationships between tables
To provide alternative foreign keys for a table
If a foreign key in a child table does not have a matching primary key in the parent table, what database issue arises?
Data inconsistency
Referential integrity violation
Data redundancy
Data normalization
What is a composite key?
A key composed of multiple foreign keys
A key made up of two or more columns to uniquely identify records
A key that enforces referential integrity
A key used only in junction tables
Which of the following best describes a one-to-one relationship?
One record in a table is related to many records in another table
Many records in one table are related to many records in another table
One record in a table is related to one record in another table
A single record in a table can have multiple foreign keys
In a many-to-many relationship, how many foreign keys does a junction table typically have?
One
Two
Three
None
Why is it important that foreign keys are non-null in certain cases?
It ensures every record has a matching value in the related table
It ensures referential integrity is violated
It prevents data duplication across tables
It makes foreign keys unique
Which of the following statements about primary keys is true?
Primary keys can contain null values
A table can have multiple primary keys
Primary keys must be unique for each record in the table
A primary key can be duplicated in another table
What is the main purpose of enforcing referential integrity?
To prevent data duplication within a table
To ensure related data is kept consistent between tables
To speed up query performance
To simplify database design
If a table contains two foreign keys, it is most likely to be:
A parent table in a one-to-one relationship
A junction table in a many-to-many relationship
Which of the following is NOT a valid reason for using a junction table?
A) To resolve a many-to-many relationship
B) To store metadata about the relationship between two tables
C) To provide additional data integrity checks
D) To enforce a one-to-one relationship
A primary key that consists of more than one column is called:
A) A composite key
B) A duplicate key
C) A concatenated key
D) A unique key
Which of the following describes a duplicate foreign key?
A) A foreign key that appears more than once in the same table
B) A foreign key that links the same two tables twice
C) A foreign key that exists in two tables with different values
D) A foreign key that exists in two different tables and points to the same primary key
Which of the following fields would most likely be a primary key?
A) CustomerName
B) ProductID
C) OrderDate
D) Price
Why are foreign keys important in relational databases?
A) They allow one-to-one relationships
B) They enforce referential integrity between related tables
C) They help in creating indexes
What is a flat file database?
Contains a single table
Two or more linked tables
Used to create formula's
What is a relational database?
Contains a single table
Two or more linked tables
Used to create formula's
What is a primary key?
A single unique key
A unique key used as a second key in another table
To ensure that data is secure
What is a foreign key?
A single unique key
A unique key used as a second key in another table
To ensure that data is secure
What is Customer ID in the appointment table?
A primary key
A Criteria
A foreign key
A type
What is this?
A flat file database
A source file
The relationships between tables
A query
Looking at the Order Info table, which fields are foreign keys?
Order ID
Customer ID
Book ID
Order Date
Looking at the Order Info table, which fields are primary keys?
Order ID
Customer ID
Book ID
Order Date
We can create a relational database using the software ..
MS Excel
MS Access
MS Word
All the above
The table in this picture, is shown in
design view
datasheet view
access view
The table in this picture, is shown in
design view
datasheet view
access view
To find out, or change the data types for each field in a database table, we need to open it in ..
datasheet view
design view
A field with datatype Autonumber cannot be manually inserted or edited
true
false
A field with datatype Autonumber cannot be manually inserted or edited
true
false
What is the full form of DBMS
Data and business management software
Database management system
Database arrangement system
Database align software
_________ can not be created in Microsoft access
Tables
Queries
Reports
Image
Which software air used for flat file database
MS access
MS Excel
Oracle
Database
What is the criteria for the query shown in the image
Teacher
No Degree
No
Yes Degree
What is a flat file database?
Contains a single table
Two or more linked tables
Used to create formula's
What is a relational database?
Contains a single table
Two or more linked tables
Used to create formula's
What is a primary key?
A single unique key
A unique key used as a second key in another table
To ensure that data is secure
What is a foreign key?
A single unique key
A unique key used as a second key in another table
To ensure that data is secure
What is Customer ID in the appointment table?
A primary key
A Criteria
A foreign key
A type
What is this?
A flat file database
A source file
The relationships between tables
A query
Looking at the Order Info table, which fields are foreign keys?
Order ID
Customer ID
Book ID
Order Date
Looking at the Order Info table, which fields are primary keys?
Order ID
Customer ID
Book ID
Order Date
We can create a relational database using the software ..
MS Excel
MS Access
MS Word
All the above
The table in this picture, is shown in
design view
datasheet view
access view
The table in this picture, is shown in
design view
datasheet view
access view
To find out, or change the data types for each field in a database table, we need to open it in ..
datasheet view
design view
A field with datatype Autonumber cannot be manually inserted or edited
true
false
A field with datatype Autonumber cannot be manually inserted or edited
true
false
What is the full form of DBMS
Data and business management software
Database management system
Database arrangement system
Database align software
_________ can not be created in Microsoft access
Tables
Queries
Reports
Image
Which software air used for flat file database
MS access
MS Excel
Oracle
Database
What is the criteria for the query shown in the image
Teacher
No Degree
No
Yes Degree
RULER / COUNTRY
For the following two ENTITIES, which is the appropriate relationship:
PRODUCT /PRICE
One to One
One to Many
Many to Many
ZOO / ANIMAL
For the following two ENTITIES, which is the appropriate relationship:
RECIPE/ INGREDIENT
One to One
One to Many
Many to Many
DOCTOR / PATIENT
SIM CARD / PHONE NUMBER
COUNTRY / CAPITAL CITY
For the following two ENTITIES, which is the appropriate relationship:
ACTOR / MOVIE
One to One
One to Many
Many to Many
CUSTOMER / ORDER
For the following two ENTITIES, which is the appropriate relationship:
STUDENT/SUBJECT
One to One
One to Many
Many to Many
HOUSE/POSTCODE
