Test 1
Identify the two types of VIEWs - Dynamic - DML - DDL - JOIN - Materialized
- Dynamic - Materialized
Identify two advantages of dynamic views - It can simplify large queries into shorter forms of views. - It can facilitate access control by allowing selected rows and/or columns in a view. - It can override primary and foreign key constraints. - It can replace a data administrator and database administrator. - It can draw and ERD automatically without a diagramming tool.
- It can simplify large queries into shorter forms of views. - It can facilitate access control by allowing selected rows and/or columns in a view.
Identify the advantages and characteristics of a Materialized View. Select 4 options. - Materialized views do not need refreshing. - Materialized views do not occupy hard disk space. - Less recent data is obtained with the advantage of saving CPU time. - Materialized view need refreshing each time an updated result is required. - Queries that are CPU intensive can be run periodically instead of running them each time a report is needed. - The result from a Materialized view will always show the most updated result set, i.e. each time the table is updated the new data is shown in the Materialized view. - Materialized views occupy hard disk space.
- Less recent data is obtained with the advantage of saving CPU time. - Materialized view need refreshing each time an updated result is required. - Queries that are CPU intensive can be run periodically instead of running them each time a report is needed. - Materialized views occupy hard disk space.
Select three correct choices for the Inventory Database - It is possible to issue an invoice without knowing the customer id, Cust_Id - It is possible to have an entry of a product in the Product_T table even if the product price Prod_Price is not known. - The attribute Invoice_Number is unique in the database. - Invoice_Number in the Invoice_T table can be termed as a surrogate key. - A customer can buy the same product more than once in the same invoice
- The attribute Invoice_Number is unique in the database. - Invoice_Number in the Invoice_T table can be termed as a surrogate key. - A customer can buy the same product more than once in the same invoice
Consider the two definitions for fixed character length and variable character length. PostCode CHAR( 6 ), City VARCHAR( 20 ) Select two correct options that are valid: - The field PostCode will take 6 characters on the storage medium, even if there is no data entered by the user for that row. - The field City can exceed 20 characters but the field PostCode cannot exceed 6 character. - The field City will take as many characters on the storage medium, as entered by the user entered by the user for that row. - The field City will take 20 characters on the storage medium, even if there is no data entered by the user for that row.
- The field PostCode will take 6 characters on the storage medium, even if there is no data entered by the user for that row. - The field City will take as many characters on the storage medium, as entered by the user entered by the user for that row.
Study the query and chose two correct answers. It refers to the world database SELECT City_T.Name, District, City_T.Population FROM City AS City_T WHERE CountryCode = 'CAN' AND ( Population > ( SELECT AVG( Population ) FROM City WHERE City.District = City_T.District ) ) ORDER BY District; - The above query is an example of a non-correlated sub query. - The above query is an example of a correlated sub query. - The query is invalid and will give an error. - The query will list the average of all cities that have population greater than the population of all cities in CountryCode CAN - The query will list the average of all cities that have population greater than the population of all cities in each District in CountryCode CAN
- The query will list the average of all cities that have population greater than the population of all cities in each District in CountryCode CAN - The above query is an example of a correlated sub query.
Identify two items that Metadata should not have. - sample data - name of attribute - description of data - data type - processing logic - source of data - constraints
- sample data - processing logic
A NULL value can be used. Chose three options. - when a value of a data element is not known - in place of a zero or space - when a value is not entered in the database, it may be known - interchangeably with a space - when a value is not appropriate or not possible - interchangeably with a zero
- when a value is not appropriate or not possible - when a value is not entered in the database, it may be known - when a value of a data element is not known
Chose three options for the following question. A NULL value can be used when: - when a value is not entered in a database, even though a value is known - when a value of a data element is not known - when a value is not appropriate or when a value is not possible - interchangeably with a space - interchangeably with either a space or a zero - interchangeably with zero
- when a value is not entered in a database, even though a value is known - when a value of a data element is not known - when a value is not appropriate or when a value is not possible
Which of the two ERD's is drawn first, which is drawn second. Order the two ERD's according to their creation in the Systems Development Life Cycle (SDLC). Logical ERD Physical ERD
1 - Logical ERD 2 - Physical ERD
How many rows does the Practice table have? 12 * 2 = 24 2 12 12 / 2 = 6
12
How many columns does the Practice table have? 12 12 * 2 = 24 24 12 / 2 = 6 2
2
How many rows does the country table contain? 400 240 239 329
239
How many Canadian cities are listed? (before you inserted data for Victoria BC) 49 23 18 3
49
A very popular development technique used by database professionals for database design is known as ________. A) entity-relationship data modeling B) normalization C) database extraction D) data models
A) entity-relationship data modeling
Refer table 4.6: Ingredients and Measurements. Identify the prime key in the table: A A, Cups B A, B, Cups B, Cups A, B Cups
A, B
With reference to table 4.10 Numbers, identify the prime key. A D A,B C A,C B C,D
A, C
What is the average Life Expectancy of the countries listed in the country table? Round your answer to two decimal places. 66.94 37.21 66.49 83.54
66.49
Which of the following statements is not true for primary keys in a relational database system? A) They must contain numeric data. B) They can be used to create relationships between tables. C) They uniquely identify a row in a table. D) They can be part of relational integrity constraints.
A) They must contain numeric data.
A DELETE statement and a DROP statement can be used interchangeably with the same result True False
False
A database administrator decides to DROP a Materialized view. The table from which the view is created will also be dropped. True False
False
Refer table 4.6: Ingredients and Measurements. Is the prime key composite True False
True
Referential integrity constraints are rules about what data values are allowed in certain columns. True False
True
The database management system (DBMS) is responsible for enforcing referential integrity constraints. True False
True
A database administrator decides to DROP a dynamic view. The data in the original table will also be deleted because the tables will also be dropped. True False
False
The database management system (DBMS) is responsible for inserting, modifying, reading, and deleting data. True False
True
Refer the ERD for the Inventory Database and the DDL statements. The attribute Prod_Code in the table Product_T can have a NULL value - True - False
False
Rows of a relation must not be interchanged and must be stored in a certain sequence. True False
False
The SELECT statement is a transaction. True=Yes False=No True False
False
A database engineer using a modeling tool to create an ERD and then uses the tool to create DDL statements. This process is called: Forward Engineering Reverse Engineering
Forward Engineering
Identify the SQL clause that will provide a summary of rows using aggregate functions like AVG, SUM, COUNT. - NATURAL JOIN - OUTER JOIN - ORDER BY - GROUP BY
GROUP BY
Identify the clause that qualifies the GROUP BY clause and filters the number of rows displayed. - WHERE - ORDER BY - HAVING - FROM
HAVING
What is the command to add data to a table? INSERT DATA ADD INSERT ADD DATA
INSERT
The description of a database's structure that is stored within the database itself is called the metadata. True False
True
The notation != implies not equal to True False
True
The relational model was first proposed in 1970 by E. F. Codd at IBM. True False
True
Using an index reduces search and retrieval times, but increases time taken for updates. True False
True
When a Relational Database is used a M:M relationship must be resolved in a Physical ERD. - False - True
True
With reference to table 4.10 Numbers. Is the prime key composite? True False
True
Identify the query that will list all parts made from BRASS. - SELECT * FROM Practice WHERE Material = 'BOLT'; - SELECT * FROM Practice WHERE Material = 'BRASS'; - SELECT * FROM Practice WHERE Part = 'BRASS'; - SELECT * FROM Practice WHERE Material != 'BRASS';
SELECT * FROM Practice WHERE Material = 'BRASS';
The primary purpose of using indexes is to improve: database performance during updates database performance during search and retrieval presentation of data in forms, reports and queries
database performance during search and retrieval
Identify the SQL statement that displays countries with name ending in the letter m. SELECT * FROM country WHERE name LIKE 'm%'; SELECT * FROM country WHERE name LIKE '%m'; SELECT * FROM country WHERE name ENDS IN "%m"; SELECT * FROM country WHERE name CONTAINS "%m";
SELECT * FROM country WHERE name LIKE '%m';
Identify the query that will list all SCREWs. SELECT * FROM Practice WHERE Part = 'WASHER'; SELECT * FROM Practice WHERE Part = 'BOLT'; SELECT * FROM Practice WHERE Material = 'SCREW'; SELECT * FROM Practice WHERE Part = 'SCREW';
SELECT * FROM Practice WHERE Part = 'SCREW';
Identify the query that will show the average cost of all screws. SELECT AVG( Cost ) FROM Part_T WHERE Part = 'SCREW'; SELECT AVG( Cost ) FROM Part_T WHERE SCREW = 'Part'; SELECT AVG( Part ) FROM Part_T WHERE SCREW = 'Cost'; SELECT AVG( Part ) FROM Part_T WHERE Cost = 'SCREW';
SELECT AVG( Cost ) FROM Part_T WHERE Part = 'SCREW';
At the time of data entry of an invoice, the application automatically inserts the current date in the data entry form. The intention is to minimize data entry and reduce human errors. What is the term used to define this feature. range NULL value default value NOT NULL CONSTRAINT
default value
Identify the query that will show the average cost of all screws. - SELECT AVG( Cost ) FROM Part_T WHERE Part = 'SCREW'; - SELECT AVG( Cost ) FROM Part_T WHERE SCREW = 'Part'; - SELECT AVG( Part ) FROM Part_T WHERE SCREW = 'Cost'; - SELECT AVG( Part ) FROM Part_T WHERE Cost = 'SCREW';
SELECT AVG( Cost ) FROM Part_T WHERE Part = 'SCREW';
Some data needs to be deleted from a table. Other relevant data is lost in the delete operation. Identify the term that describes this scenario. foreign key constraint insertion anomaly update anomaly deletion anomaly normalization
deletion anomaly
Count the number of parts made from STEEL SELECT COUNT( * ) FROM Practice WHERE Part = 'STEEL'; SELECT COUNT( * ) FROM Practice WHERE Part = 'STEEL' AND Material = 'STEEL'; SELECT COUNT( * ) FROM Practice WHERE Material = 'STEEL'; SELECT COUNT( * ) FROM Practice WHERE STEEL = 'Material';
SELECT COUNT( * ) FROM Practice WHERE Material = 'STEEL';
An index can be created on more than one column, for example Question options: CREATE INDEX Customer_L_F_Name_IDX ON Customer( LastName, FirstName ) is a valid statement. True False
True
Data is recorded facts and figures; information is knowledge derived from data. True False
True
For a table to qualify as a relation: Every row must be unique, i.e. two rows cannot have the same values in all their fields: True False
True
In a database, each table stores data about a different type of thing. Question options: True False
True
Indexes are data structures that speed database searches. True False
True
Integrated tables store both data and the relationships among the data. True False
True
It is possible to update more than one data element in an SQL UPDATE statement True False
True
Refer Table 4.8: Day, Patient & Mood. Is the primary key composite? True False
True
What does the following statement do INSERT INTO Practice( Part, Material ) VALUES( 'WASHER', 'ALUMINIUM' ); It removes data from the Practice tables Adds data to the Practice Table. It adds WASHER as Material and ALUMINIUM as Part It creates a table called Practice Adds data to the Practice Table. It adds WASHER as Part and ALUMINIUM as Material.
Adds data to the Practice Table. It adds WASHER as Part and ALUMINIUM as Material.
An executive summary that tells the reader about the project and what the reader should expect from the document. Identify the term that best describes the above description. - Entity - Processing Logic - Metadata - Business Rule - An abstract
An abstract
Which country has the highest LifeExpectancy? USA Canada Andorra Malawi China
Andorra
Refer Table 4.7: Animal, Diet & Weight. Identify the prime key in the table: There is no prime key possible in this table Animal Weight Diet, Weight Animal, Weight Animal, Diet, Weight Animal, Diet Diet
Animal, Diet
The industry standard supported by all major DBMSs that allows tables to be joined together is called ________. A) Standard Question Language (SQL) B) Structured Query Language (SQL) C) Structured Question Language (SQL) D) Sequential Query Language (SQL)
B) Structured Query Language (SQL)
A database consists of integrated tables, which store ________. A) reports B) data and relationships among the data C)forms D) Both reports and forms are correct
B) data and relationships among the data
In database systems, the DBMS enforces rules about which data can be written in certain columns. These rules are known as ________. A) concurrency control B) referential integrity constraints C) data insertion control D) data modification control
B) referential integrity constraints
In one of the the tables in the database at Ottawa Civic Hospital, X-Ray, MRI scans and other digital images are stored. Identify the datatype for storage of these images. - BLOB - NUMBER - CHAR - VARCHAR - CLOB
BLOB
In one of the the tables in the database at Ottawa Civic Hospital, X-Ray, MRI scans and other digital images are stored. Identify the datatype for storage of these images: CHAR CLOB BLOB VARCHAR NUMBER
BLOB
Database professionals use a set of principles called ________ to guide and assess database design. A) data models B) entity-relationship data modeling C) normalization D) database extraction
C) normalization
What operation is performed by the statement? SELECT CountryCode, Country.Name, City.Name FROM City, Country; CARTESIAN PRODUCT (also called CROSS JOIN) LEFT OUTER JOIN SELF JOIN NATURAL JOIN
CARTESIAN PRODUCT (also called CROSS JOIN)
Identify the best data type to store Canada's postal code of 6 characters in length. The length is unlikely to change for at least 10 years. There are no arithmetic calculations to be performed on the postal code. A code to be valid must be exactly 6 characters in length. - TEXT - INTEGER - CHAR - CLOB - VARCHAR
CHAR
Identify the best data type to store an Algonquin's Student Number. The number should be exactly 9 digits in length. The length is unlikely to change for at least 15 years. There are no arithmetic calculations to be performed on the Student Number. All 9 elements of the student number are numerical. - VARCHAR - TEXT - CLOB - INTEGER - CHAR
CHAR
Identify the data type that accepts fixed length character data of up to 2000 characters. - VARCHAR - NUMBER - BLOB - CLOB - CHAR
CHAR
Identify the statement that make changes to a database permanent. COMMIT DROP ROLLBACK CREATE
COMMIT
Which of the following operations are not JOIN operations. Chose two options SELF JOIN LEFT JOIN and RIGHT JOIN CROSS JOIN (also called Cartesian Product) UNION INNER JOIN OUTER JOIN
CROSS JOIN (also called Cartesian Product) UNION
Which query will combine each row from the first table with each row from the second table? OUTER JOIN SELF JOIN CROSS JOIN or CARTESIAN PRODUCT INNER JOIN
CROSS JOIN or CARTESIAN PRODUCT
Rows in a relation: Must be inserted in reverse alphabetical Must be inserted in chronological order Must be inserted in a way to ensure efficient retreival Must be inserted in alphabetical order Can be inserted in any order
Can be inserted in any order
A primary key: cannot be NULL can be NULL can be NULL if the database is in Third Normal Form can be NULL if the database is in Second Normal Form can be NULL if the database is in First Normal Form
Cannot be NULL
A database stores ________. A) data B) relationships C) applications D) Both data and relationships are correct
D) Both data and relationships are correct
A program whose job is to create, process and administer databases is called a ________. A) data business model system B) relational model manager C) database modeling system D) database management system
D) database management system
Refer Table 4.8: Day, Patient & Mood. Identify the prime key in the table. Patient Patient, Mood Mood Day, Mood It is not possible to identify a prime key in this table Day, Patient, Mood Day Day, Patient
Day, Patient
virtual table or logical view are other names for : - Dynamic View - Index Table - NATURAL JOIN - Materialized View
Dynamic View
Study the following SQL statement. Then select the three correct options. SELECT * FROM Practice WHERE Part = 'WASHER'; FROM in the SELECT clause specifies the tables that should be used for the query The * in the SELECT clause implies all columns WHERE in the SELECT clause specifies the condition to filter rows in the query WHERE in the SELECT clause specifies the condition to filter columns in the query FROM in the SELECT clause specifies the attributes that should be used for the query The * in the SELECT clause implies all tables in the database
FROM in the SELECT clause specifies the tables that should be used for the query The * in the SELECT clause implies all columns WHERE in the SELECT clause specifies the condition to filter rows in the query
Identify the SQL statement that will add a row to the Part_T table with the following data values, Part->HAMMER Material->STEEL Size->SMALL Cost->7.15 INSERT INTO Part_T( 'HAMMER', 'STEEL', 'SMALL', 7.15 ) VALUES( Part, Material, Size, Cost ); INSERT INTO Part_T( Part, Material, Size, Cost ); INSERT INTO Part_T( Part, Material, Size, Cost ) VALUES ( 'HAMMER', 'STEEL', 'SMALL', 7.15 ); ADD INTO Part_T( Part, Material, Size, Cost ) VALUES ( 'HAMMER', 'STEEL', 'SMALL', 7.15 );
INSERT INTO Part_T( Part, Material, Size, Cost ) VALUES ( 'HAMMER', 'STEEL', 'SMALL', 7.15 );
Identify the SQL statement that will add a row to the Part_T table with the following data values: Part->HAMMER Material->STEEL Size->SMALL Cost->7.15 - INSERT INTO Part_T( Part, Material, Size, Cost ) VALUES ( 'HAMMER', 'STEEL', 'SMALL', 7.15 ); - INSERT INTO Part_T( 'HAMMER', 'STEEL', 'SMALL', 7.15 ) VALUES( Part, Material, Size, Cost ); - INSERT INTO Part_T( Part, Material, Size, Cost ); - ADD INTO Part_T( Part, Material, Size, Cost ) VALUES ( 'HAMMER', 'STEEL', 'SMALL', 7.15 );
INSERT INTO Part_T( Part, Material, Size, Cost ) VALUES ( 'HAMMER', 'STEEL', 'SMALL', 7.15 );
Which of the following is not a characteristic of a good business rule. - Atomic - Declarative - Expressible - Inconsistent
Inconsistent
Refer the ERD for Inventory database. Which of the following is an associative entity: - Invoice_Line_T - Customer_T - Invoice_T - Product_T
Invoice_Line_T
What does the following statement do CREATE TABLE Practice( Part VARCHAR( 20 ), Material VARCHAR( 20 ) ); Adds data to the Practice Table. It adds WASHER as Material and ALUMINIUM as Part. It creates a table called Practice with two columns Part and Material Adds data to the Practice Table. It adds WASHER as Part and ALUMINIUM as Material. It removes data from the Practice tables It removes the table Practice from the database.
It creates a table called Practice with two columns Part and Material
Which one the following view will use disk space for data. Virtual Table Dynamic View Logical View Materalized View
Materalized View
The order of logical operators is - NOT, OR, AND - NOT, AND, OR - OR, AND, NOT - AND, NOT, OR
NOT, AND, OR
Relations that have anomalies are decomposed, to smaller, well-structured relations. This process is called: Drawing Logical ER diagrams Normalization Mapping entities to relations Reverse Engineering Denormalization
Normalization
A join in which rows that do not have matching values in common columns are still included in the result table. CROSS JOIN OUTER JOIN SELF JOIN NATURAL JOIN
OUTER JOIN
A join in which rows that do not have matching values in common columns are still included in the result table. SELF JOIN CROSS JOIN NATURAL JOIN OUTER JOIN
OUTER JOIN
Refer table 4.9: Pet & Food. Identify two conclusions that can be determined from the data set. Pet & Food is a table but not a relation. The Prime key is Pet, Food, it is a composite key The data set is neither a table nor a relation. A prime key cannot be determined from the given data set. Pet & Food is a relation but not a table.
Pet & Food is a table but not a relation. A prime key cannot be determined from the given data set.
A reverse engineered ERD from a Relational Database is always a: - Physical ERD - Conceptual ERD - Logical ERD
Physical ERD
Which of the two ERD's have more detail. - Physical ERD - Logical ERD
Physical ERD
Consider the two definitions for fixed character length and variable character length. PostCode CHAR( 6 ), City VARCHAR( 20 ) Select one correct option that is valid: - The maximum length cannot exceed 9 characters for CHAR but can exceed exceed 20 characters for VARCHAR. - The maximum length can exceed 9 characters for CHAR but cannot exceed exceed 20 characters for VARCHAR. - The maximum length can exceed 9 characters for CHAR and also can also exceed 20 characters for VARCHAR. - PostCode will take 6 bytes on the storage medium, even if no data is storedthough The maximum length cannot exceed 9 characters for CHAR and also cannot exceed 20 characters for VARCHAR.
PostCode will take 6 bytes on the storage medium, even if no data is storedthough The maximum length cannot exceed 9 characters for CHAR and also cannot exceed 20 characters for VARCHAR.
Identify the statement that undoes (reverses) a transaction: DROP COMMIT CREATE ROLLBACK
ROLLBACK
The database is processing a set of 10 SQL statements. The first 4 statements run without error. The fifth statement has a problem with referential integrity. What is the best course of action. ROLLBACK the entire set of transactions. The first 4 statements are undone. The database is in a state as if no statements were run. Complete all the other statements now. Then run the single statement that is giving errors; the problem should go away. Restart the RDBMS, the problem should go away. Let the first four statements run, i.e. COMMIT them. Flag an error, let the user resolve the problem and then attempt to complete the transactions again.
ROLLBACK the entire set of transactions. The first 4 statements are undone. The database is in a state as if no statements were run.
Identify the rule that maintains consistency among the rows of two relations: Entity Integrity Rule Integrity rule Referential Integrity Rule NULL values
Referential Integrity Rule
Often DDL statements are written manually by a database designer. He uses a modeling tool to draw an ERD from this set of DDL statements, this process is called Forward Engineering Reverse Engineering
Reverse Engineering
Identify the query that will list all parts not made from BRASS SELECT * FROM Practice WHERE Material != 'BRASS'; SELECT * FROM Practice WHERE Material != 'ALUMINIUM'; SELECT COUNT(*) FROM Practice WHERE Part = 'ALUMINIUM' AND Material = 'ALUMINIUM'; SELECT * FROM Practice WHERE Part != 'ALUMINIUM';
SELECT * FROM Practice WHERE Material != 'BRASS';
Identify the query that will list all SCREWs, all WASHERSs. SELECT * FROM Practice WHERE Part = 'SCREW' OR Part = 'NUT'; SELECT * FROM Practice WHERE Part = 'SCREW' AND Part = 'WASHER'; SELECT * FROM Practice WHERE Part = 'SCREW' OR Material = 'WASHER'; SELECT * FROM Practice WHERE Part = 'SCREW' OR Part = 'WASHER';
SELECT * FROM Practice WHERE Part = 'SCREW' OR Part = 'WASHER';
Refer to the world database. Identify the query that will list countries that have no cities listed in the city table SELECT Country.Code, Country.Name FROM Country LEFT JOIN City ON Country.Code = City.CountryCode WHERE City.CountryCode IS NULL; SELECT Country.Code, Country.Name FROM Country LEFT JOIN City ON Country.Name = City.CityID WHERE City.CountryCode IS NULL; SELECT Country.Code, Country.Name FROM Country LEFT JOIN City ON Country.Code = City.CityID WHERE City.CountryCode IS NULL; SELECT Country.Code, Country.Name FROM Country LEFT JOIN City ON Country.Code = City.CountryCode WHERE City.CountryCode IS NOT NULL;
SELECT Country.Code, Country.Name FROM Country LEFT JOIN City ON Country.Code = City.CountryCode WHERE City.CountryCode IS NULL;
Which of the following queries will list Country Code, Country Name and City Name for each city in the city table. SELECT Country.Code, Country.Name, City.Name FROM Country INNER JOIN City ON Country.Code = City.Countrycode SELECT * FROM Country INNER JOIN City ON Country.Name = City.Name; SELECT Country.Code, Country.Name, City.Name FROM Country SELF JOIN Country ON Country.Code = City.CountryCode; SELECT Country.Code, Country.Name, City.Name FROM Country;
SELECT Country.Code, Country.Name, City.Name FROM Country INNER JOIN City ON Country.Code = City.Countrycode
Identify the SQL statement that displays name and surface area from country table, with the country with largest surface area first. The country with the smallest area is displayed last. DISPLAY Name, SurfaceArea FROM country ORDER BY SurfaceArea DESC; SELECT Name, SurfaceArea FROM country ORDER BY SurfaceArea DESC; SELECT Name, SurfaceArea FROM country REVERSE ORDER BY SurfaceArea; DISPLAY Name, SurfaceArea FROM country SORT BY SurfaceArea REVERSE ORDER;
SELECT Name, SurfaceArea FROM country ORDER BY SurfaceArea DESC;
Identify the query that will list Part, Material and Cost from the table. - SELECT ALL FROM Part_T; - SELECT Part OR Material OR Cost FROM Part_T; - SELECT Part AND Material AND Cost FROM Part_T; - SELECT Part, Material, Cost FROM Part_T;
SELECT Part, Material, Cost FROM Part_T;
Identify the query that will list Part, Material and Cost from the table. SELECT Part AND Material AND Cost FROM Part_T; SELECT Part OR Material OR Cost FROM Part_T; SELECT Part, Material, Cost FROM Part_T; SELECT ALL FROM Part_T;
SELECT Part, Material, Cost FROM Part_T;
Identify the query that will list Part, Size and Cost for Parts that cost greater than or equal to $2.10 SELECT Part, Material, Cost FROM Part_T WHERE Cost GREATER THAN 2.10; SELECT Part, Size, Cost FROM Part_T WHERE Part <= 2.10; SELECT Part, Size, Cost FROM Part_T WHERE Cost >= 2.10; SELECT Part, Size, Cost FROM Part_T WHERE Cost GREATER THAN OR EQUAL TO 2.10;
SELECT Part, Size, Cost FROM Part_T WHERE Cost >= 2.10;
Identify the query that will list Part, Size and Cost for Parts that cost greater than or equal to $2.10 - SELECT Part, Size, Cost FROM Part_T WHERE Cost GREATER THAN OR EQUAL TO 2.10; - SELECT Part, Size, Cost FROM Part_T WHERE Cost >= 2.10; - SELECT Part, Size, Cost FROM Part_T WHERE Part <= 2.10; - SELECT Part, Material, Cost FROM Part_T WHERE Cost GREATER THAN 2.10;
SELECT Part, Size, Cost FROM Part_T WHERE Cost >= 2.10;
Identify the query that will list the first five letters of the column Material from the Part_T Table. - SELECT CHAR( Material FROM 1 FOR 5 ) FROM Part_T; - SELECT CHAR( Material FROM 5 FOR 1 ) FROM Part_T; - SELECT CHAR( 1,5 ) ( Material FROM 1 FOR 5 ) FROM Part_T; - SELECT SUBSTRING( Material FROM 1 FOR 5 ) FROM Part_T;
SELECT SUBSTRING( Material FROM 1 FOR 5 ) FROM Part_T;
Identify the SQL statement that displays code, name and continent in alphabetical order? SELECT code, name, continent FROM country SORT aplhabetically BY name; SELECT code, name, continent ORDER BY name; SELECT code, name, continent FROM country ORDER BY name; DISPLAY code, name, continent FROM country SORT BY name;
SELECT code, name, continent FROM country ORDER BY name;
Identify the SQL statement that displays code, name and continent from the country table is? DISPLAY code, name, continent; DISPLAY code, name, continent FROM country; SELECT code, name, continent FROM country;
SELECT code, name, continent FROM country;
An operation to join a table to itself is called a: EQUI JOIN SELF JOIN OUTER JOIN NATURAL JOIN INNER JOIN
SELF JOIN
An operation to join a table to itself is called a: OUTER JOIN SELF JOIN CROSS JOIN NATURAL JOIN
SELF JOIN
An operation to join a table to itself is called a: SELF JOIN CROSS JOIN NATURAL JOIN OUTER JOIN
SELF JOIN
Which city has the second largest population? Mumbai (Bombay) Shanghai Seoul Ottawa
Seoul
SELECT Country.Code, Country.Name FROM Country WHERE Country.Code NOT IN( SELECT CountryCode FROM CountryLanguage ); In the above query, the statement SELECT CountryCode FROM CountryLanguage is a: Sub query UNION Statement NATURAL JOIN INNER JOIN
Sub query
Referring to the world database, what will the following SQL statement do? SELECT CountryCode, SUM( Population ) FROM City GROUP BY CountryCode; - Summarize the population, by adding the population of all cities, in each country using CountryCode in the City Table - Summarize the population, by taking the average of the population of all cities, in each country using CountryCode in the City Table - Will count the number of cities in each country as listed in the City Table. - Summarize the population of all the countries, by adding the population of all countries in the Country Table. It will give one number as a result.
Summarize the population, by adding the population of all cities, in each country using CountryCode in the City Table
The traditional method used to plan, analyse, design, implement and maintain an information is called: Systems Development Life Cycle (SDLC) Prototyping Rapid Application Development (RAD) Agile
Systems Development Life Cycle (SDLC)
The requirement is to total the number of cities in the City table and the query is constructed. The following SQL statement will give an error. What is the cause of the error? SELECT CountryCode, COUNT( CountryCode ) FROM City GROUP BY District; - COUNT cannot be used in a GROUP BY statement, only SUM and AVG can be used. - GROUP BY clause cannot exist by itself, it needs a HAVING clause. - The GROUP BY clause must contain CountryCode.
The GROUP BY clause must contain CountryCode.
Consider the two definitions for fixed character length and variable character length. CHAR( 9 ) VARCHAR( 20 ) Select one correct option that is valid: - The maximum length can exceed 9 characters for CHAR and also can also exceed 20 characters for VARCHAR. - The maximum length cannot exceed 9 characters for CHAR and also cannot exceed 20 characters for VARCHAR. - The maximum length can exceed 9 characters for CHAR but cannot exceed exceed 20 characters for VARCHAR. - The maximum length cannot exceed 9 characters for CHAR but can exceed exceed 20 characters for VARCHAR.
The maximum length cannot exceed 9 characters for CHAR and also cannot exceed 20 characters for VARCHAR.
Study the query and chose one correct answer. SELECT ProcedureCode, cost FROM VisitProcedure_T WHERE cost > ( SELECT AVG( cost ) FROM VisitProcedure_T ); - The query will first determine the average cost of a procedure, then list all costs that are greater than the average cost of all procedures - The query will first determine the average cost of a procedure, then list all costs that are less than the average cost of all procedures - The query will first list all procedures, then it will continue to list all the procedures that are equal to the average cost. - The query will list the average costs of all procedures.
The query will first determine the average cost of a procedure, then list all costs that are greater than the average cost of all procedures
Study or run the query below and answer the question SELECT * FROM Part_T WHERE Cost = ( SELECT MAX( Cost ) FROM Part_T ); The query will show all parts that are the highest cost. The query will show all parts that are the lowest cost. The query will show all parts that are discountinued.
The query will show all parts that are the highest cost.
Study or run the query below and answer the question SELECT * FROM Part_T WHERE Cost = ( SELECT MAX( Cost ) FROM Part_T ); - The query will show all parts that are the lowest cost. - The query will show all parts that are discountinued. - The query will show all parts that are the highest cost.
The query will show all parts that are the highest cost.
Study or run the query below and answer the question SELECT * FROM Part_T WHERE Cost = ( SELECT MIN( Cost ) FROM Part_T ); The query will show all parts that are the highest cost. The query will show all parts that are not in the inventory. The query will show all parts that are the lowest cost.
The query will show all parts that are the lowest cost.
Study or run the query below and answer the question SELECT * FROM Part_T WHERE Cost = ( SELECT MIN( Cost ) FROM Part_T ); - The query will show all parts that are the highest cost. - The query will show all parts that are the lowest cost. - The query will show all parts that are not in the inventory.
The query will show all parts that are the lowest cost.
Study or run the query below and answer the question SELECT AVG( Cost ) FROM Part_T WHERE Part = 'BOLT'; The query will show the average size of all BOLTs in the Part_T table. The query will show the average cost of all parts in the Part_T table. The query will show the average cost of all BOLTs in the Part_T table.
The query will show the average cost of all BOLTs in the Part_T table.
Study or run the query below and answer the question SELECT AVG( Cost ) FROM Part_T WHERE Part = 'BOLT'; - The query will show the average cost of all parts in the Part_T table. - The query will show the average cost of all BOLTs in the Part_T table. - The query will show the average size of all BOLTs in the Part_T table.
The query will show the average cost of all BOLTs in the Part_T table.
DELETE FROM Customer_T WHERE Cust_ID = 'C002'; Study the above statement and chose one answer from the choices. The statement relates to the Inventory Lab you have done. The statement will delete the customer with Cust_ID = C002 if there are no constraints for that customer in the child table. The statement will remove all customers from the database with Cust_ID = C002 The statement will delete all customers with Cust_ID = C002 regardless of any referential integrity constraint.
The statement will delete the customer with Cust_ID = C002 if there are no constraints for that customer in the child table.
DELETE FROM Product_T; Study the above SQL DELETE statement and chose one option from the list below The statement will remove all rows from the Product_T table The statement will remove the table Product_T The statement will remove the column Product_T from the table The statement will give an error because the list of attributes is missing.
The statement will remove all rows from the Product_T table
What is the result of the following statement on the city Table in the world database. UPDATE City SET Population = 500000; The statement is incomplete it will give an error. The statement will set the population of Ottawa to 500000. The statement will set the population of all the cities in the city table to 500000. The statement will give an error, because the population of a city cannot be exactly 500000.
The statement will set the population of all the cities in the city table to 500000.
A table has a composite key and repeating data items have been resolved. Identify the correct choice based on the above statement: Because it has the key defined it will be in second normal form. The table may be in first, second or third normal form based on rules. The table can never be in second normal form, it will always have partial functional dependency because it has a composite key The table can never be in second normal form unless the composite key is resolved into a primary key that have only one attribute.
The table may be in first, second or third normal form based on rules.
A JOIN operation combines two tables into one on the basis of common values in a common column. True False
True
A SQL DELETE statement will remove row(s) from a table, it cannot remove the table from the database True False
True
A database management system (DBMS) creates, processes and administers databases. True False
True
A database shows data in tables as well as the relationships among the rows in those tables. True False
True
A key is defined as: a combination of one or more attributes that uniquely identifies rows in a relation True False
True
All database applications send and receive database data by sending SQL statements to the DBMS. True False
True
An entity that is used to resolve a m:m relation is called an associative entity True False
True
Identify the SQL operator that satisfies the following conditions: 1. Combines the result sets of two or more SELECT statements 2. Each SELECT statement must have the same number of columns 3. All columns must have the same data type 4. All columns must be in the same order 5. The result set will display DISTINCT values by default, the ALL keyword is used to display duplicate values. FULL OUTER JOIN NATURAL JOIN SELF JOIN CROSS JOIN UNION
UNION
Which operation matches this description: 1. Returns a result set that includes data from all tables. 2. All tables, or views, must have the same attributes. UNION NATURAL JOIN OUTER JOIN CROSS JOIN SELF JOIN
UNION
The population of the city Pinar del Rio in Cuba needs to change to 190532. The city ID for Pinar del Rio is 2419. You may use the world database to verify your answer. Identify the command that will make the change. UPDATE City SET ID = 190532 WHERE Population = 2419; MODIFY City SET Population = 190532 WHERE ID = 2419; UPDATE City SET Population = 190532 WHERE ID = 2419; UPDATE City SET 'Pinar del Rio' = 190532 WHERE ID = 2419;
UPDATE City SET Population = 190532 WHERE ID = 2419;
The life expectancy of Ukraine needs to change from 66.0 to 72.5, the country code for Ukraine is UKR. The country code is represented as CODE in the country table in the world database. You may use the world database to verify your answer. Identify the command that will make the change. UPDATE Country or City SET 'Ukraine' = 190532 WHERE ID = 'UKR'; UPDATE City SET LifeExpectancy = 72.5 WHERE Code = 'UKR'; MODIFY Country, City SET URK = 'Ukraine' WHERE LifeExpectancy = 66.0; UPDATE Country SET LifeExpectancy = 72.5 WHERE Code = 'UKR';
UPDATE Country SET LifeExpectancy = 72.5 WHERE Code = 'UKR';
Two data items need to change in the Country table for Mexico. 1. The life expectancy of Mexico needs to change from 71.5 to 72.0 and 2. The head of state needs to change to Enrique Pena Nieto The country code for Mexico is MEX. The country code is represented as CODE in the country table in the world database. You may use the world database to verify your answer. Identify the command that will make the change. UPDATE Country SET( LifeExpectancy, HeadOfState ) = ( 72.0, 'Enrique Pena Nieto' ) WHERE Code = 'MEX'; UPDATE Country SET( LifeExpectancy, HeadOfState ) = ( 72.0, 'Enrique Pena Nieto' ); UPDATE City SET( LifeExpectancy, HeadOfState ) = ( 72.0, 'Enrique Pena Nieto' ) WHERE Code = 'MEX';
UPDATE Country SET( LifeExpectancy, HeadOfState ) = ( 72.0, 'Enrique Pena Nieto' ) WHERE Code = 'MEX';
An ERD is a pictorial representation of - The Database Model - Systems Development Life Cycle (SDLC) - Metadata - Users Data
Users Data
Identify the data type that accepts character data; only the required number of bytes are used in the storage medium. - CHAR - NUMBER - VARCHAR - CLOB - BLOB
VARCHAR
Study the file Practice-DDL-DML.sql What is the data type for Part. DECIMAL DATE VARCHAR INTEGER CHAR
VARCHAR
Referring to the world database, what will the following SQL statement do? SELECT CountryCode, COUNT( CountryCode ) FROM City GROUP BY CountryCode; - Will count the number of cities in each country as listed in the City Table. - Summarize the population, by adding the population of all cities, in each country using CountryCode in the City Table - Summarize the population, by taking the average of the population of all cities, in each country using CountryCode in the City Table - Summarize the population of all the countries, by adding the population of all countries in the Country Table. It will give one number as a result.
Will count the number of cities in each country as listed in the City Table.
Choose three options. The entity integrity rule states that: a primary key must have more than one attribute a primary key cannot have a NULL value primary key can be null a primary key must have only one attribute each primary key must be unique each entity must have a primary key
a primary key cannot have a NULL value each primary key must be unique each entity must have a primary key
A Composite Key is a primary key that consists more than one attribute an attribute that stores more than one data item an attribute that stores more than one data item an alternate name used for an attribute
a primary key that consists more than one attribute
Refer the ERD for Inventory Database and the DDL statements. The attribute Cust_Id in the Customer_T table is: - a foreign key - neither a prime key nor a foreign key - a prime key
a prime key
The purpose of the attribute Invoice_Line in the table Invoice_Line_T is to - allow more than one product in the same invoice - allow more than one invoice for the same customer - allow more than one customer in the same invoice
allow more than one product in the same invoice
From the ERD it is possible to deduce that: - An invoice can have only one customer. - A customer can buy only one product in one invoice. This is because our database is small. - A product can be bought only once by a customer, i.e. a product cannot be sold again to the same customer. This is because our database is small. - A customer can have only one invoice.
an invoice can have only one customer
Identify the term used to describe the database state described below. Either all statements in a transaction execute or none of the statements in the transaction execute. atomicity protocol modularity algorithm granularity
atomicity
A join operation: causes two tables with a common attribute to be combined into a single table or view combines data from two different fields combines two tables with a common attribute into a single table or view, the common attribute must be a prime key in both tables is used to combine indexing operations
causes two tables with a common attribute to be combined into a single table or view
Identify the query where the inner query depends on the outer query for data - OUTER JOIN - correlated query - INNER JOIN - non correlated query
correlated query
What will the following SQL statement do? CREATE VIEW Asia_V AS SELECT * FROM Country WHERE CONTINENT = 'Asia'; create a materalized view create a table called Asia_V create a dynamic view
create a dynamic view
In an organization which one of the two changes less frequently - data models - business rules
data models
Choose three options. The objective of Normalization is to: simplify printing reports simplify the enforcement of referential integrity constraints ensure data storage efficiency on magnetic media ensure data is easily maintained simplify queries and display data derive relations that are free of anomalies
derive relations that are free of anomalies ensure data is easily maintained simplify the enforcement of referential integrity constraints
Identify the term used for a key that is unique in an organization's database, and its value is unique across all relations: foreign key surrogate key composite key enterprise key primary key
enterprise key
An index does not occupy any additional hard disk space. True False
false
Smallest unit of data in a database is: bit byte field record table
field
Refer the ERD for Inventory Database and the DDL statements. The attribute Cust_Id in the table Invoice_T is a - prime key - foreign key - both a prime key and a foreign key - neither a prime key nor a foreign key
foreign key
Indexing on many columns: has no effect on UPDATE operations has no effect on hard disk space usage has a tradeoff because each INSERT statement in the table needs to update the index as well
has a tradeoff because each INSERT statement in the table needs to update the index as well
Identify the file organization where random retrieval of the key is the fastest. index hash sequential file
hash
Name given to an algorithm that is used to determine an address for data: index sequence hash
hash
The relationship between tables Invoice_T and Invoice_Line_T is - identifying relationship - both identifying and non-identifying relationship - non-identifying relationship
identifying relationship
Choose two options. A database is in Second Normal Form if: every non-key attribute is fully functionally dependent on the primary key it has no transitive dependencies primary key has been defined every non-key attribute is fully functionally dependent on other non-key attributes in first normal form repeating groups have been resolved
in first normal form every non-key attribute is fully functionally dependent on the primary key
A data structure used to determine the location of records in the actual table that satisfies a condition: normalization de-normalization index table space extent
index
In an non correlated sub query, which query runs first - outer query - both will run at the same time - inner query
inner query
Data needs to be added to a table. Two situations arise 1. The user is forced to add data to other fields that are in the table. 2. Data is not available for all fields, the user is forced to add NULL values to other fields . Identify the term that describes this scenario. normalization constraint deletion anomaly insertion anomaly update anomaly
insertion anomaly
The term aggregate - is used when a single result is calculated from a collection of input values - is used when different data values are separated from each other
is used when a single result is calculated from a collection of input values
Choose two options. A database is in Third Normal Form if: repeating groups have been resolved every non-key attribute is fully functionally dependent on other non-key attributes it is in second normal form primary key has been defined it has no transitive dependencies every non-key attribute is fully functionally dependent on the primary key
it is in second normal form it has no transitive dependencies
A primary key is: at least two attributes that are required to identify at a row in a relation minimum number of attributes required to uniquely identify a row in a relation the number of attributes required to identify at least two rows in a relation maximum number of attributes required to uniquely identify a row in a relation
minimum number of attributes required to uniquely identify a row in a relation
Transaction Management is needed when: there is more than one database user in the system a user reports to more than one manager multiple SQL commands needs to be run as a single statement a transaction consists of a single statement
multiple SQL commands needs to be run as a single statement
Identify the query where the inner query does not depend on data from the outer query. - INNER JOIN - non correlated - correlated - OUTER JOIN
non correlated
Identify the type of query where the inner query is executed only once for the outer query - OUTER JOIN - correlated - INNER JOIN - non correlated
non correlated
A join operation is performed on two tables. The common field that is used for the join operation has a few NULL values in each of the two tables. The join operation will: match NULL values from the second table but not from the first table not match NULL records from any of the two tables match NULL values from the first table but not from the second table match NULL values from both tables
not match NULL records from any of the two tables
An INNER JOIN operation is performed on two tables. The common field that is used for the join operation has a few NULL values in each of the two tables. The join operation will: match NULL values from the second table but not from the first table match NULL values from the first table but not from the second table not match NULL records from any of the two tables match NULL values from both tables, since it is a JOIN operation
not match NULL records from any of the two tables
Order of columns in a relation is: important, prime key must be displayed first, all other columns must be in alphabetical order important, the prime key must be displayed and inserted first not relevant, columns can be in any order important, the prime key must be displayed and inserted last
not relevant, columns can be in any order
A non-prime key is dependent on part of the prime key. This is called: full functional dependency composite key a repeating group transitive dependency partial functional dependency
partial functional dependency
Choose two options. A database is in First Normal Form if: repeating groups have been resolved every non-key attribute is fully functionally dependent on the primary key primary key has been defined every non-key attribute is fully functionally dependent on other non-key attributes a table has no transitive dependencies every non-key attribute is fully functionally dependent on the primary key
primary key has been defined repeating groups have been resolved
A composite key is: an attribute that stores more than one data item a primary key that consists of more than one attribute an attribute that has different names but the same meaning an alternative name used for an attribute
primary key that consists of more than one attribute
Refer the ERD for Inventory Database and the DDL statements. The attribute Prod_Code in the table Product_T is a: - foreign key - prime key - a prime key and also a foreign key
prime key
A piano competition is open to students between the age of 12 to 15 years. The programmer designs the form to accept birth dates of participants such that only students whose age is within the allowed limit can register. What is the term used to define this feature. range NULL default value NOT NULL
range
An association between one, two or three entities is called: relationship table relation entity
relationship
A single value returned from an SQL query is called either scalar or vector vector scalar
scalar
Identify the file organization that is least efficient when records are inserted, deleted and modified. index sequential file hash
sequential file
Which file organization is the most efficient to search on fields that are sorted: index sequential file hash
sequential file
Identify the file organization. The average number of comparisons to search a key is n/2, the maximum number of comaprisons is n, n is the number of records. index sequential file hash sequential, unsorted file
sequential, unsorted file
Names of entities should always be - plural - singular for parent entity, plural for child entity - singular
singular
A unit of work that changes the state of a database: stored procedure transaction trigger query
transaction
A non-key is dependent on another non-key. This is called: full functional dependency transitive dependency partial functional dependency
transitive dependency
A table has three attributes called Son, Sun and Pun. An administrator created the following four indexes on the table Son_IDX on Son Sun_IDX on Sun Sun_Pun_IDX an index on Sun and Pun Son_Pun_IDX an index on Son and Pun The hard disk space occupied by all four indexes will be greater than the table itself. True False
true
Indexes are useful for large tables, they may not have much benefit for tables with few rows. True False
true
Queries can become more complex when database is normalized: True False
true
A table needs some rows to be updated. The user is forced to update other rows after the update is done. Identify the term that describes this scenario. normalization insertion anomaly forward engineering update anomaly deletion anomaly
update anomaly
Multiple aggregate values returned from an SQL query is called a scalar either scalar or vector vector
vector
