Database questions

[Picture 1] Assume that we have already created the three tables shown in the schema above. What will the SQL command look like if we want to add a field recording the birthdate of each employee? ALTER TABLE DIVISION ADD Birthdate DATE; MODIFY TABLE EMPLOYEE ADD Birthdate DATE; ALTER TABLE EMPLOYEE ADD Birthdate DATE; ADD Birthdate DATE; UPDATE TABLE EMPLOYEE ADD Birthdate;

ALTER TABLE EMPLOYEE ADD Birthdate DATE;

Based on the data tables shown above, which is the correct result of the following SQL query: SELECT CASE WHEN (SYSDATE - Birthdate)/365 < 30 THEN '20 to 30 years' WHEN (SYSDATE - Birthdate)/365 < 40 THEN '30 to 40 years' WHEN (SYSDATE - Birthdate)/365 < 50 THEN '40 to 50 years' ELSE '50 years and older' END AS AgeBracket, COUNT(DISTINCT CLIENT.ClientID) AS Total1, COUNT(APPOINTMENT.ClientID) AS Total2 FROM CLIENT JOIN APPOINTMENT ON CLIENT.ClientID = APPOINTMENT.ClientID GROUP BY CASE WHEN (SYSDATE - Birthdate)/365 < 30 THEN '20 to 30 years' WHEN (SYSDATE - Birthdate)/365 < 40 THEN '30 to 40 years' WHEN (SYSDATE - Birthdate)/365 < 50 THEN '40 to 50 years' ELSE '50 years and older' END;

Age Bracket | Total | Total 2 20 to 30 years | 2 | 3 30 to 40 years | 2 | 4 50 years or older | 1 | 1

Which SQL command is used to delete both the data and metadata in a table? DELETE UNDO REDACT DROP UNWRITE

DROP

The set of SQL commands used to update and query a database are called: Data Definition Language (DDL) Data Editing Language (DEL) Data Manipulation Language (DML) Data Control Language (DCL) Data Analysis Language (DAL)

Data Manipulation Language

[Picture 1] You are creating a database from the relational schema shown above. Which table must be created first? DIVISION DEPARTMENT EMPLOYEE Either DEPARTMENT or DIVISION It does not matter

Division

The following code is an example of a(n): SELECT CUSTOMER.CustomerID, ORDER.OrderID, ORDER.OrderDate FROM CUSTOMER, ORDER WHERE CUSTOMER.CustomerID = ORDER.CustomerID; Natural join Right join Self join Left join Equi join

Equi Join

Which part of a SQL query is executed first? SELECT FROM WHERE GROUP BY ORDER BY

FROM

Which set operator is used to return all of the rows resulting from the first query that also appear in the results of the second query? UNION MINUS JOIN INTERSECT DIFFERENCE

INTERSECT

The Dean of the College of Business has a database where she tracks historical enrollments in order to make decisions about course offerings, facuty/staff hiring, and facilities. This is an example of: Real-time data warehouse Operational database system Dependent data mart Independent data mart Informational database system

Informational database system

The Pine Valley Furniture Company want to build a website that allows customers to place orders online through a simple form. Which type of database application is the most appropriate for this purpose? Personal database Two-tier client/server database Multi-tier client/server database Enterprise resource planning database NoSQL database

Multi-tier client/server database

In the University of Iowa Hospitals and Clinics database system, patients log-in to view their own health and appointment data. Doctors can log-in to view data for all of their patients. This is an example of: Public key encryption Database firewall Role-based security Integrity constraints Database auditing

Role-based security

[Picture 2] Based on the data tables shown above, which SQL query will return the ID, name, and age of each client? SELECT ClientID, ClientFirst, ClientLast, (SYSDATE-Birthdate)/365 AS Age FROM CLIENT; SELECT ClientID, ClientFirst, ClientLast, SYSDATE-Birthdate AS Age FROM CLIENT; Correct Answer SELECT ClientID, ClientFirst, ClientLast, FLOOR((SYSDATE-Birthdate)/365) AS Age FROM CLIENT; SELECT ClientID, ClientFirst, ClientLast, Birthdate FROM CLIENT; SELECT ClientID, ClientFirst, ClientLast, FLOOR(SYSDATE-Birthdate/365) AS Age FROM CLIENT;

SELECT ClientID, ClientFirst, ClientLast, FLOOR((SYSDATE-Birthdate)/365) AS Age FROM CLIENT;

Based on the data tables shown above, what is the correct result of the following SQL query? SELECT STYLIST.StylistID, StylistFirst, StylistLast, COUNT(DISTINCT ClientID) AS Total FROM STYLIST JOIN APPOINTMENT ON STYLIST.StylistID = APPOINTMENT.StylistID GROUP BY STYLIST.StylistID, StylistFirst, StylistLast;

Stylist ID | Stylist First | Stylist Last | Total S0001 | Gina | Norris | 3 S0002 | Lynn | Silverstone | 2 S0003 | Calvin | Palmer | 1

What does non-updatable mean in the context of data warehouses? The data warehouse is never updated The data warehouse is updated whenever a new transaction occurs End users can add data to the data warehouse The data warehouse is updated periodically None of the above

The data warehouse is updated periodically

[Picture 1] Based on the relational schema shown above, what is the most likely data type of the field Country? STRING CHAR DATE NUMBER VARCHAR

VARCHAR

Which best describes the operational data store? a.Database system that provides an enterprise-wide view of integrated, high-quality, current-valued data b.Database system that stores unreconciled data c.Database system that provides an enterprise-wide view of integrated, high-quality, historical data d.Smaller database that is derived from an enterprise data warehouse

a. Database system that provides an enterprise-wide view of integrated, high-quality, current-valued data

Which SQL command is used to edit one or more rows of data stored in the database? a.UPDATE b.CREATE c.APPEND d.ALTER

a. UPDATE

The following code is an example of a(n): SELECT CLIENT.FirstName, CLIENT.LastName, M.FirstName AS MatchFirst M.LastName AS MatchLastFROM CLIENT JOIN CLIENT M ON CLIENT.MatchID = M.ClientID; a.Left join b.Self join c.Right join d.Natural join

b. Self Join

The Dean of the College of Business has a database of historic student enrollments. She wants to use this data to understand large-scale trends and create visualizations and reports. This is an example of which type of analytics: a.Visual analytics b.Prescriptive analytics c.Descriptive analytics d.Predictive analytics

c. Descriptive analytics

Which clause of a SQL query is used to return only the top-k or bottom-k results? a.SELECT b.FROM c.WHERE d.FETCH

d. FETCH

The set of tools that provides users with multidimensional views of data is called: a.OLTP b.Business intelligence c.Association mining d.OLAP

d. OLAP