Database quiz questions

do practice for each type of code question

1 create table 1 join 1 simple 1 case

In a data warehouse with three dimensions (STORE, PRODUCT, and PERIOD), how many possible rows will the fact table have if there are 2 stores, 10 products, and 10 years of quarterly sales data? 7300 800 73000 200 2400

800 2 stores * 10 peoducts * 40 periods (10 years*4 querters) 2*10*40 = 800

Assume we have already created the three tables shown in the schema above. Which of the following is the correct SQL syntax to add a field recording the birthdate of each athlete? ALTER TABLE ATHLETE ADD Birthdate DATE; ALTER TABLE TEAM ADD Birthdate DATE; UPDATE TABLE ATHLETE ADD Birthdate; MODIFY TABLE ATHLETE ADD Birthdate DATE; ADD Birthdate DATE;

ALTER TABLE ATHLETE ADD Birthdate DATE;

look at the table first on back of card Based on the TUTORING table above, what is the correct CASE statement to categorize students as either Tutor or Non-Tutor? Assume that everyone is either a tutor is is being tutored by another student. CASE WHEN Tutor IS NOT NULL THEN 'Tutor' ELSE 'Non-Tutor' END CASE WHEN Tutor=StudentID THEN 'Tutor' ELSE 'Non-Tutor' END CASE WHEN Tutor = '' THEN 'Tutor' ELSE 'Non-Tutor' END CASE WHEN Role = 'Tutor' THEN 'Tutor' ELSE 'Non-Tutor' END CASE WHEN Tutor IS NULL THEN 'Tutor' ELSE 'Non-Tutor' END

CASE WHEN Tutor IS NULL THEN 'Tutor' ELSE 'Non-Tutor' END we know this because since it is a unary relationship if you are a tutor your id is in the tutor section so when the tutor column is blank, that means you dont have a tutor so you must be a tutor

Which type of constraint is used to limit the range of values that can be stored in a column? For example, a constraint that employee birthdays must fall between the years 1900 and 2019. PRIMARY KEY DEFAULT NOT NULL CHECK UNIQUE

CHECK

You have a query that finds the company's top 5 products by sales volume (total units sold) and returns the product name and total units sold. What type of visualization would be appropriate to display the results of this query? Map chart Scatterplot Pie chart Column chart Line chart

Column chart

The set of SQL commands used to create the metadata and structure of a database is called: Data Control Language (DCL) Data Implementation Language (DIL) Data Definition Language (DDL) Data Manipulation Language (DML) Data Analysis Language (DAL)

Data Definition Language (DDL)

Which best describes an operational data store? Database system that stores data in the form of multidimensional cubes Smaller database that is derived from an enterprise data warehouse Database system that provides an enterprise-wide view of integrated, high-quality, current-valued data Database system that stores raw, unreconciled data Database system that provides an enterprise-wide view of integrated, high-quality, historical data

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

what will the following query return ? SELECT CASE WHEN LastName LIKE 'S%' THEN 'Last name starts with S' ELSE 'Last name does not start with S' FROM TUTORINGWHERE FirstName = 'Jessie'; Last name does not start with S Error message Spano NULL value Last name starts with S

Error message this is because the case statement doesn't end properly with the END

into which clause(s) of an outer SQL query can a table subquery be nested? (Select all that apply) SELECT GROUP BY WHERE FROM HAVING

FROM only here bc since it is a table subquery it has to be joined to the other tables and only do joins in the from clause.

look at query output on back Principal Belding is writing a SQL query that categorizes each student as either Tutor on Non-Tutor and produces the following output: (on back) Into which clause(s) of the SQL query should he place a CASE statement? (Select all that apply) FROM GROUP BY FETCH SELECT HAVING

GROUP BY SELECT know it is in select bc we can see it total students would then be calculated using an aggregate function meaning this query has a group by in it since group by runs before select the case statement also needs to be in there

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 DIFFERENCE INTERSECT

INTERSECT

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 Cross join Inner join Self join Right join

Inner join

Look at the schema on the back to answer the questions You are creating a database from the relational schema shown above. Which table must be created first? LEAGUE TEAM ATHLETE It does not matter Either TEAM or LEAGUE

LEAGUE league has to be created first bc it is the parent table, you can tell by the arrows pointing into it.

Which SQL operator is used to do partial text matching? LIKE STRING DISTINCT = ~

LIKE and most likely will be a string that has % in it 'hist%'

The Dean of the College of Business wants to build a database application that allows current students to make advising appointments using a simple Web form. What type of database application is most appropriate for this purpose? Logical data mart Multi-tier client/server Personal database Enterprise resource planning database Two-tier client/server

Multi-tier client/server

Based on the relational schema above, what is the most likely data type for the field TotalTeams? VARCHAR CHAR NUMBER BOOLEAN DATE

NUMBER

Look at schema on back side first Using a database created from the relational schema above, what is the correct query to return the country and total number of employees in each country? SELECT Country, COUNT(EmployeeID) AS TotalEmployees FROM DIVISION JOIN DEPARTMENT ON DIVISION.DivisionID = DEPARTMENT.Division JOIN EMPLOYEE ON DEPARTMENT.DepartmentID = EMPLOYEE.Department; SELECT Country, COUNT(EmployeeID) AS TotalEmployees FROM DIVISION, DEPARTMENT, EMPLOYEE GROUP BY Country; SELECT Country, COUNT(EmployeeID) AS TotalEmployees FROM DIVISION JOIN DEPARTMENT ON DIVISION.DivisionID = DEPARTMENT.Division JOIN EMPLOYEE ON DEPARTMENT.DepartmentID = EMPLOYEE.Department GROUP BY Country; SELECT Country, COUNT(EmployeeID) AS TotalEmployees FROM DIVISION GROUP BY Country; SELECT Country, COUNT(EmployeeID) AS TotalEmployees FROM DIVISION JOIN EMPLOYEE ON DIVISION.DivisionID = EMPLOYEE.Department GROUP BY Country;

SELECT Country, COUNT(EmployeeID) AS TotalEmployees FROM DIVISION JOIN DEPARTMENT ON DIVISION.DivisionID = DEPARTMENT.Division JOIN EMPLOYEE ON DEPARTMENT.DepartmentID = EMPLOYEE.Department GROUP BY Country;

look at schema first Using a database created from the relational schema above, what is the correct query to return the IDs for all departments that have more than 10 employees, but are not in the United States? SELECT DepartmentID FROM DEPARTMENT JOIN EMPLOYEE ON DEPARTMENT.DepartmentID = EMPLOYEE.Department GROUP BY DepartmentID HAVING COUNT(EmployeeID)>10 UNION SELECT DepartmentID FROM DEPARTMENT JOIN DIVISION ON DEPARTMENT.Division = DIVISION.DivisionID WHERE Country='United States'; SELECT DepartmentID FROM DEPARTMENT HAVING COUNT(EmployeeID) > 10 INTERSECT SELECT DepartmentID FROM DEPARTMENT WHERE Country='United States'; SELECT DepartmentID FROM DEPARTMENT JOIN EMPLOYEE ON DEPARTMENT.DepartmentID = EMPLOYEE.Department GROUP BY DepartmentID HAVING COUNT(EmployeeID)>10 INTERSECT SELECT DepartmentID FROM DEPARTMENT JOIN DIVISION ON DEPARTMENT.Division = DIVISION.DivisionID WHERE Country='United States'; SELECT DepartmentID FROM DEPARTMENT JOIN EMPLOYEE ON DEPARTMENT.DepartmentID = EMPLOYEE.Department GROUP BY DepartmentID HAVING COUNT(EmployeeID)>10 MINUS SELECT DepartmentID FROM DEPARTMENT JOIN DIVISION ON DEPARTMENT.Division = DIVISION.DivisionID WHERE Country='United States'; SELECT DepartmentID FROM DEPARTMENT JOIN EMPLOYEE ON DEPARTMENT.DepartmentID = EMPLOYEE.Department MINUS SELECT DepartmentID FROM DEPARTMENT JOIN DIVISION ON DEPARTMENT.Division = DIVISION.DivisionID WHERE Country='United States';

SELECT DepartmentID FROM DEPARTMENT JOIN EMPLOYEE ON DEPARTMENT.DepartmentID = EMPLOYEE.Department GROUP BY DepartmentID HAVING COUNT(EmployeeID)>10 MINUS SELECT DepartmentID FROM DEPARTMENT JOIN DIVISION ON DEPARTMENT.Division = DIVISION.DivisionID WHERE Country='United States'; we know its a minus bc it says but not

look at the picture of the schema to help answer the question SELECT * FROM DEPARTMENT WHERE Country='United States'; SELECT DepartmentID, Name FROM DEPARTMENT WHERE DIVISION.Country='United States'; SELECT DepartmentID, DEPARTMENT.Name FROM DEPARTMENT JOIN DIVISION WHERE Country='United States'; SELECT DepartmentID, DEPARTMENT.Name FROM DEPARTMENT JOIN DIVISION ON DEPARTMENT.Division=DIVISION.DivisionID WHERE Country='United States'; SELECT DepartmentID, Name FROM DEPARTMENT JOIN DIVISION ON DEPARTMENT.DivisionID=DIVISION.DivisionID WHERE Country='United States';

SELECT DepartmentID, DEPARTMENT.Name FROM DEPARTMENT JOIN DIVISION ON DEPARTMENT.Division=DIVISION.DivisionID WHERE Country='United States';

Which function do we use in Oracle SQL to return today's date? TO_CATE SYSDATE CURRENT_DATE TODAY DATE

SYSDATE

The following query is an example of a(n): SELECT OrderID, OrderDate, OrderTotal FROM ORDERS WHERE OrderTotal =(SELECT MAX(OrderTotal) FROM ORDERS); Table subquery Compound query Rows subquery Scalar subquery Simple query

Scalar subquery

The following code is an example of a join: SELECT C.FirstName, C.LastName, M.FirstName AS MatchFirst M.LastName AS MatchLast FROM CLIENT C JOIN CLIENT M ON C.MatchID = M.ClientID; Left Natural Cross Right Self

Self

A microbiology research group with 5 members wants to build a database that allows all members equal access to add, update, delete, and analyze research data using a SQL command line interface. What type of database application is most appropriate for this purpose? Logical data mart Personal database Multi-tier client/server Two-tier client/server Enterprise resource planning database

Two-tier client/server

Which SQL command is used to edit values stored in one or more existing rows of data in a database? ALTER UPDATE APPEND ENABLE CREATE

UPDATE

Which of the following is NOT a mathematical operator in SQL? * + / - x

X use *