BIS 345 for SQL Databases Week 8 Final Exam | Complete Solution

BIS 345 for SQL Databases 
Week 8 : Final Exam Week - Final Exam

Time Remaining:

1. (TCO 1) Which of the following sets of SQL clauses represent the minimum combination of clauses to make a working SQL statement? (Points : 5)
SELECT, WHERE
FROM, WHERE
SELECT, FROM
FROM, ORDER BY

2. (TCO 1) Which of the following would be considered a logical operator? (Points : 5)
=
>=
AND
IS NULL

3. (TCO 1) Which of the following is true about ORDER BY clauses? (Points : 5)
The default order is ascending when none is specified.
Numerical columns are sorted from highest to lowest where no sort order is specified.
Columns on which sorting must be conducted cannot be identified by their position in their table.
Character columns are evaluated from right to left.

4. (TCO 1) Which of the following would extract all rows containing the numbers 4 or 8? (Points : 5)
WHERE number = 4 or number = 8
WHERE number BETWEEN 4 and 8
WHERE number = '4 or 8'
Where number = '4' or '8'

5. (TCO 1) Assume you want to associate a TaxpayerTable with a PrincipalResidenceTable. The PrincipalResidentTable contains the address where each taxpayer lives. Assume that each taxpayer can have only one residence; however, more than one person at the address can be considered a taxpayer. What is the relationship between the TaxpayerTable and PrincipalResidenceTable? (Points : 5)
Many-To-One
One-To-Many
Many-to-Many
One-to-One

6. (TCO 1) Which of the following would display values from the city field in the Professor table, removing all duplicate city names? (Points : 5)
SELECT City DISTINCT FROM Professor
SELECT City FROM DISTINCT Professor
SELECT DISTINCT City FROM Professor
SELECT DISTINCT FROM Professor

7. (TCO 1) Assume you have a table called StudentTable. You would like to extract the lastname of all students who have not yet declared their major. Which of the following WHERE clauses would accomplish this? (Points : 5)
WHERE major = " "
WHERE major = ' '
WHERE major IS NULL
WHERE major = 'NULL'

8. (TCO 3) Which of the following statements would count the number of customers within each state?
Assume you have a table with the following columns:
(Points : 5)
SELECT State, Count(*)
FROM CustomerTable
GROUP BY State;
SELECT CustomerID, Count(State)
FROM CustomerTable
GROUP BY CustomerID;
SELECT State, Count(CustomerID)
FROM CustomerTable;
None of the above.

9. (TCO 3) Which of the following statements would the extract average income of the customers in each city? Assume you have a table with the following columns:
(Points : 5)
SELECT City, Avg(Income)
FROM CustomerTable
GROUP BY City;
SELECT State, Avg(Income)
FROM CustomerTable
GROUP BY State;
SELECT City, Average(Income)
FROM CustomerTable
GROUP BY City;
SELECT State, Average(Income)
FROM CustomerTable
GROUP BY State;

10. (TCO 4) Query ______ is the complete process from submission of a query to its actual execution. (Points : 5)
optimization
compilation
insertion
None of the above

11. (TCO 4) Assume you have two queries that must extract all rows that have a value of greater than $100 in an Income column. You are considering two ways of writing your WHERE clause. The first option is to extract all columns that are NOT less than or equal to $100. The second option is to extract all columns that are greater than $100. Which option should you choose to ensure your query runs as quickly as possible? (Points : 5)
The first option
The second option
It doesn't matter which option you pick; they will run the same no matter what.
All of the above

12. (TCO 7) Which of the file formats can be viewed using Adobe Acrobat Reader? (Points : 5)
CSV
PDF
TIFF
XLSX

13. (TCO 8) Name the security concept involves ensuring that one person doesn't have control over too many steps in a process. (Points : 5)
zroles
Separation of duties
Securing forms and reports
Using views

14. (TCO 8) _____ encryption offers a more granular level of encryption. (Points : 5)
Role based
Object-based security
Column-level
None of the above

15. (TCO 9) Which of the following reports requires the reader to read down columns to understand its meaning? (Points : 5)
Matrix
Exception
Tabular
None of the above

16. (TCO 9) Assume you are creating a report which is sorted by class, and then by student within class. The class is considered the ____ column: (Points : 5)
gross
minor
major
None of the above

17. (TCO 9) Aggregation functions help grouping scenarios by providing _____________ for groups. (Points : 5)
totals
subtotals
titles
expressions

18. (TCO 10) Which of the following represent a popular report-writing tool that is not a Microsoft application? (Points : 5)
Access Reports
SSRS
SSMS
Crystal Reports

19. (TCO 10) Which of the following is NOT one of the formats available for reports? (Points : 5)
XML
HTML
PDF
XPDF

20. (TCO 2) Use the ____ data type for columns that contain letters and special characters and for columns containing numbers that will not be used in calculations. (Points : 5)
CHAR
VARCHAR
Text
String

21. (TCO 2) Which of the following functions would you use to extract the from your records in the database? (Points : 5)
Datepart
Datediff
Dateout
Dateyear

22. (TCO 2) Which of the following functions would return the current date based on the computer clock. (Points : 5)
TodayDate()
PutDate()
FindDate()
GetDate()

23. (TCO 3) Aggregate functions ignore the ______values. (Points : 5)
character
numeric
date
null

24. (TCO 5) For the report server to use an extension, it must be ______. (Points : 5)
configured
linked to the server
added to the report
installed

25. (TCO 5) The easiest and fastest way to create a report is to use the _____ tool. (Points : 5)
report builder
report wizard
report template
report manager

26. (TCO 5) When creating the a report using Business Intelligence Development Studio, you can view the report using the ______tab. (Points : 5)
Toolbox
Design
Output box
Preview

27. (TCO 5) The data set contains all of the following except ______. (Points : 5)
reuslts of the query
SQL statement
pointer to the data source
name of data source

28. (TCO 7) The chart _____ contains the plotting area of a chart. (Points : 5)
category
data
series
areas

29. (TCO 7) _____ presents a grid layout with static columns and expands detail data data row by row. (Points : 5)
Table
Matrix
List
Dropdown

30. (TCO 6) _____ regions are items that must be associated with a data set. (Points : 5)
Processor
Report
Presentation
Data

31. (TCO 6) Which of the data source credential options is the least recommended because of security risks. (Points : 5)
Hard-code
Prompt
Windows Authentication
No Credentials

32. (TCO 6) Which of the following is not a type of data source in SSRS. (Points : 5)
embedded
shared
calculated
expression

33. (TCO 3) Which of the following functions would you use in an SQL statement to tally the number of values. (Points : 5)
Max()
Avg()
Sum()
Count()

34. (TCO 3)________functions are SQL mathematical summaries. (Points : 5)
Summation
Summary
Aggregate
Cummalative

35. (TCO 5) When using the report server project wizard the most crucial step is _______. (Points : 5)
selecting your data source
naming your project
selecting the location to save your project
selecting the correct template

36. (TCO 2) Refer to the tblCustomer table below. As a data analyst, you have been requested to select rows that meet the following requirements: For each city, list the city name in all upper case characters along with the exchange part of the customer phone number (the first 3 digits). Include only preferred customers in the list. Order the rows in alphabetical order of city. Be sure to give the calculated columns an alias.

tblCustomer:

CustID (PK)

LastNameFirstNamePhoneCityRegionOverdue Status*PreferredIncomeNumOrdersBalanceBirthdate
10

SmithBob123-4567OrlandoNorth
Paid

Y1000051001/1/1978
12RamirezJose422-1234ApexSouthPaidY50000166002/8/1970
13WashingtonTerrell951-1234SujourEast[null]Y20000257003/5/1985
15JonesBill963-7894AxeCrossingWestUnpaidY16900250012/1/1990
17MoralesSandy951-1234OrlandoNorthPaidN85000904008/3/1986
21PrincipeMichelle963-4561ApexSouthPaidN94000853007/2/1974
23MavisJohn147-4561SujourEastUnpaidY25000266003/5/1989
*Overdue Status indicates customers with a balance that is 30 or more days overdue

Part 1: Write the complete SQL statement needed to return the rows that meet these requirements.

Part 2: What rows will be returned from this query? List the customer ID only of the rows to be returned.

Part 3: What function did you use to extract the three-digit exchange? Why did you select this function? Rewrite the SQL using a different function to extract these characters. (Points : 25)

37. (TCO 3) Refer to the tblCustomer table below. As a data analyst, you have been requested to select rows that meet the following requirements. For each city, list the name of the city, and the number of customers in that city. Include only those cities that have more than 1 customer in them. Order the data by city in descending order.
tblCustomer:

CustID (PK)

LastNameFirstNamePhoneCityRegionOverdue Status*PreferredIncomeNumOrdersBalanceBirthdate
10

SmithBob123-4567OrlandoNorth
Paid

Y1000051001/1/1978
12RamirezJose422-1234ApexSouthPaidY50000166002/8/1970
13WashingtonTerrell951-1234SujourEast[null]Y20000257003/5/1985
15JonesBill963-7894AxeCrossingWestUnpaidY16900250012/1/1990
17MoralesSandy951-1234OrlandoNorthPaidN85000904008/3/1986
21PrincipeMichelle963-4561ApexSouthPaidN94000853007/2/1974
23MavisJohn147-4561SujourEastUnpaidY25000266003/5/1989
*Overdue Status indicates customers with a balance that is 30 or more days overdue

Part 1: Build your SELECT statement by choosing the certain clauses from the list below and putting the clauses in the correct order:
SELECT City, Count(*)
ORDER BY 2
WHERE Count(*) > 1
SELECT Region, Count(*)
GROUP BY City
FROM tblCustomer
ORDER BY Count(*)
HAVING Count(*) > 1
GROUP BY Region
ORDER BY 2 DESC

Part 2: Did you select any filter clause(s)? (WHERE, HAVING) Justify your choice of filter(s). (Points : 25)

38. (TCO 6) Evaluate the Supplier List report below. Management requested this report so they can monitor the value and quantity of the stock on hand; a requirement of the report is to be able to match the product to the supplier.

Part 1: Describe at least two enhancements that would make this report more readable to the user.

Part 2: Describe at least two features you would include that would add functionality to the report and improve its flexibility for the user. (Points : 25)