Category Archives: BIS 345

BIS 345 Final Exam Guide Set 1 latest

BIS 345 Final Exam Guide Set 1 latest

Check this A+ tutorial guideline at

http://www.assignmentclick.com/bis-345/bis-345-final-exam-guide-set-1-latest

For more classes visit

http://www.assignmentclick.com

BIS 345 Final Exam Guide Set 1 latest

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)

BIS 345 Final Exam Guide Set 2 latest

BIS 345 Final Exam Guide Set 2 latest

Check this A+ tutorial guideline at

http://www.assignmentclick.com/bis-345/bis-345-final-exam-guide-set-2-latest

For more classes visit

http://www.assignmentclick.com

BIS 345 Final Exam Guide Set 2 latest

1. (TCO 1) Which of the following portions of an SQL Statement indicate the columns that should be included in the result set?(Points : 5)

       SELECT

       FROM

       WHERE

       INSERT

Question 2.2. (TCO 1) Which of the following operators uses wildcard characters to search for patterns or characters in a character string?(Points : 5)

       SEARCH

       LIKE

       BETWEEN

       EQUAL TO

Question 3.3. (TCO 1) Which of the following characters is used to merge the output of two columns into one? (Points : 5)

       AND

       PLUS

       +

       ;

Question 4.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′

Question 5.5. (TCO 1) Assume you have a table called CourseTable. You would like to extract all courses that were developed in June and July of 2008. Which of the following operators would be of most use to you in your WHERE clause? (Points : 5)

       BETWEEN

       LIKE

       IS NULL

       None of the above

Question 6.6. (TCO 1) Given Professors table with LastName, FirstName, and MI fields, which of the following would select all fields from the table? (Points : 5)

       SELECT * FROM Professors;

     SELECT * Professors;

       SELECT Last Name, First Name, MI FROM Professors;

     SELECT * ALL

Question 7.7. (TCO 1) Which of the following would extract all rows containing the numbers 10 or 20? (Points : 5)

       WHERE number BETWEEN 10 and 20

       WHERE number = 10 or number = 20

       WHERE number = ’10 or 20′

       Where number = ’10′ or ’20′

Question 8.8. (TCO 3) Which of the following statements would extract the average price of all the products?

Assume you have a table with the following columns:

(Points : 5)

       SELECT ProductID, Avg(UnitPrice)

FROM ProductTable

GROUP BYProductID;

       SELECT Average(UnitPrice)

FROM ProductTable;

       SELECT Avg(UnitPrice)

FROM ProductTable;

       None of the above

Question 9.9. (TCO 3) Which of the following statements would the extract the total value of all the orders?

Assume you have a table with the following columns:

(Points : 5)

       SELECT Total(OrderTotal)

FROM OrderTable;

      SELECT Sum(OrderTotal)

FROM OrderTable;

       SELECT Sum(OrderTotal)

FROM OrderTable

GROUP BY OrderTotal;

       SELECT Rollup(OrderTotal)

FROM OrderTable;

Question 10.10. (TCO 4) Assume you have two queries. The first query selects its data using a join involving five tables. The second query selects its data using a join involving three tables. Which of the queries would run faster, all other things being equal? (Points : 5)

       The first query will run slower than the second query.

       The second query will run slower than the second query.

       The queries will run at the same speed.

       All of the above

Question 11.11. (TCO 4) Assume you have two select queries that must join two tables. The first query uses the INNER JOIN keyword, while the other accomplishes the join using the WHERE clause. Which query will be more likely to run faster – the first query or the second query? (Points : 5)

       The first query will run slower than the second query.

       The second query will run slower than the first query.

       The queries will run at the same speed.

       All of the above

Question 12.12. (TCO 7) Which of the following file formats are used to import data into SSRS. (Points : 5)

       CSV

       PDF

       SLN

       docx

Question 13.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

Question 14.14. (TCO 8) _____ is the process of creating ciphertext. (Points : 5)

       Passcoding

       Digital Certificate

       Symmetric

     Encryption

Question 15.15. (TCO 9) Which of the following reports requires the reader to look at data in both column and row format? (Points : 5)

       Matrix

       Exception

       Tabular

       None of the above

Question 16.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

Question 17.17. (TCO 9) Which date function will return the difference between two dates in the specified units? (Points : 5)

       DATEDIFFERENCE

       DATEPART

       DATEDIFF

       DATEADD

Question 18.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

Question 19.19. (TCO 10) Which of the following is NOT one of the formats available for reports? (Points : 5)

       XML

       HTML

       PDF

       XPDF

Question 20.20. (TCO 2) Which date function will return the difference between two dates in the specified units? (Points : 5)

       DATEDIFFERENCE

       DATEPART

       DATEDIFF

       DATEADD

Question 21.21. (TCO 2) Which of the following functions would you use to extract the year, month, or day records in the database? (Points : 5)

       Datepart

       Datediff

       Dateout

       Dateyear

Question 22.22. (TCO 2) Which of the following functions would you use to extract characters from the middle of a string. (Points : 5)

       Left()

       Right()

       Middle()

       Substring()

Question 23.23. (TCO 3) Which of the following aggregate functions can be used with any data type. (Points : 5)

       MAX

       SUM

       COUNT

       AVG

Question 24.24. (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

Question 25.25. (TCO 5) SSRS uses all of the following types of extensions except ______. (Points : 5)

       data processing

       security

     delivery

       communication

Question 26.26. (TCO 5) By default when we add a table to our report in Business Intelligence Development Studio, the table has _____ columns. (Points : 5)

       2

       3

       4

       5

Question 27.27. (TCO 5) You can use the ______ in Business Intelligence Development Studio to access the report items controls. (Points : 5)

       Design tab

       Toolbox

       Toolbar

       Preview tab

Question 28.28. (TCO 7) The chart control used in SSRS is licensed from ______ software. (Points : 5)

       Dundas

       Adobe

       Apple

       Google

Question 29.29. (TCO 7) ______ presents group data in a grid layout that’s capable of expanding both rows and columns. (Points : 5)

       Table

     Matrix

       List

       Dropdown

Question 30.30. (TCO 6) A ____ parameter are used during the processing of the query. (Points : 5)

       Multidimensional Expressions (MDX)

       query

       report

       Multivalue parameters

Question 31.31. (TCO 6) Which data source credentials option is considered an integrated security type. (Points : 5)

       Prompt for credentials

     Windows authentication

       Hard-code credentials

       No credentials

Question 32.32. (TCO 6) ______ data source is used to dynamically choose the data source at runtime. (Points : 5)

       embedded

       shared

       unique

       expression

Question 33.33. (TCO 3) Which of the following aggregate functions would not ignore null values. (Points : 5)

       Count

       AVG

       MAX

       Sum

Question 34.34. (TCO 3) Which of the following is not a valid aggregate function. (Points : 5)

      DATEDIFFERENCE

       COUNT

       MAX

       MIN

Question 35.35. (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

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

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)

Part 3: To extract the three-digit exchange I used the LEFT(Phone,3) funcion.

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:

Part 1:

SELECT City, COUNT(*) FROM tblCustomer

GROUP BY City HAVING COUNT(*)>1

ORDER BY City 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 1: The two enhancements I would make would be:

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)

Part 2: If I had to include two features to add functionality to the report and improve its flexibility they would be:

BIS 345 Quiz 1 latest

BIS 345 Quiz 1 latest

Check this A+ tutorial guideline at

http://www.assignmentclick.com/bis-345/bis-345-quiz-1-latest

For more classes visit

http://www.assignmentclick.com

BIS 345 Quiz 1 latest

1. (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

2. (TCO 2) Which of the following functions would you use to extract characters from the middle of a string.

3. (TCO 2) Which date function will return the difference between two dates in the specified units?

4. (TCO 3) If you wanted to know the highest credit line in your customer database, which if the following functions would you use in your SQL.

5. (TCO 3) Which of the following clauses filters groups

6. (TCO 3) Which of the following characters can be used with the COUNT function to return the total numbers of rows in the query

7. (TCO 2) Which of the following date functions requires a timestamp parameter when use.

8. (TCO 2) Of the following which one properly displays the correct syntax for using a date in an SQL statement.

9. (TCO 2) When using the Convert function, the _____ identifier signifies the format of the output.

10. (TCO 3) The ______clause can be used to count values once and ignore repeated occurrences

 

BIS 345 Quiz 2 latest

BIS 345 Quiz 2 latest

Check this A+ tutorial guideline at

http://www.assignmentclick.com/bis-345/bis-345-quiz-2-latest

For more classes visit

http://www.assignmentclick.com

BIS 345 Quiz 2 latest 

1. (TCO 5) Which of the following is not an SSRS tool

2. (TCO 5) Reports created using report designer or report builder are generated in which of the following reporting languages.

3. (TCO 5) Which of the following tools is built on the Visual Studio 2008 shell

4. (TCO 5) Which of the following tools would you use to manage your SQL Server connection and reporting services?

5. (TCO 5) When using the report server project wizard the most crucial step is _______.

6. (TCO 5) SQL Server Reporting Services provides the following command line utility ____ to modify encrypted connections.

7. (TCO 5) When creating reports with Visual Studio, a report data set can contain an _______ or shared data source

8. (TCO 5) When creating the a report using Business Intelligence Development Studio, you can view the report using the ______tab

9. (TCO 5) By default when we add a table to our report in Business Intelligence Development Studio, the table has _____ columns

10. (TCO 5) The easiest and fastest way to create a report is to use the _____ tool

 

BIS 345 Quiz 3 latest

BIS 345 Quiz 3 latest

Check this A+ tutorial guideline at

http://www.assignmentclick.com/bis-345/bis-345-quiz-3-latest

For more classes visit

http://www.assignmentclick.com

BIS 345 Quiz 3 latest

1. (TCO 6) Which of the following is not a type of data source in SSRS

2. (TCO 6) Which of the data source credential options is the least recommended because of security risks

3. (TCO 5) All of the following are types of query for a data set except ________.

4. (TCO 6) _____ regions are items that must be associated with a data set.

5. (TCO 6) Data regions support grouping and ______.

6. (TCO 6) _____ is the simplest report item and contains no data values

7. (TCO 7) ______ presents group data in a grid layout that’s capable of expanding both rows and columns

8. (TCO 5) Of the following four Tablix internal areas, which is a required area?

9. (TCO 7) The chart _____ contains the plotting area of a chart

10. (TCO 7) A ________ report item is a great tool to graphically display key performance indicators

 

BIS 345 Week 1 iLab latest

BIS 345 Week 1 iLab latest

Check this A+ tutorial guideline at

http://www.assignmentclick.com/bis-345/bis-345-week-1-ilab-latest

For more classes visit

http://www.assignmentclick.com

BIS 345 Week 1 iLab latest

Student Name: Name

Using Lab 1, answer the questions and provide copies of your SQL Statements and/or results for each Section listed below

Part A: Customer List (4 points)

a.What does the ORDER BY clause do?

b.Screen print your results and paste here:

Part B: Customer List with only customers In France or Germany (4 points)

Part C: Using a Calculation in a Query (5 points)

Part D: Products that are discontinued (5 points)

Part E: Practice using the BETWEEN operator (5 points)

Part F: Practice using the “IN” operator (5 points)

Part G: Understanding “NULL” (5 points)

Part H: Using a composite condition (5 points)

Part I: Using a “DATE” type variable and Concatenating Column Output (5 points)

Part J: Using the “LIKE” operator and the “%” wildcard (6 points)

BIS 345 Week 2 iLab latest

BIS 345 Week 2 iLab latest

Check this A+ tutorial guideline at

http://www.assignmentclick.com/bis-345/bis-345-week-2-ilab-latest

For more classes visit

http://www.assignmentclick.com

BIS 345 Week 2 iLab latest

Lab 2 Student Answer Sheet

Lab 2, answer the questions and provide copies of your SQL Statements and/or results for each Section listed below

Part A: Employee List (4 points)

a.What does Left function do? Is selecting the query and coping and could be pasted in a latest query if is need it.

b.Screen print your results and paste here:

Part B: Date Function (4 points)

a. How do you display the year part of a date?

b. Paste your SQL Statement here:

c. Screen print your results and paste here:

Part C: Using a Calculation in a Query (4 points)

a. Paste your SQL Statement here

Screen print your results and paste here:

Part D: Practice SUM function (5 points)

a. Paste your SQL Statement here:

b. Screen print your results and paste here:

Part E: Practice Average, Maximum, and Minimum function (5 points)

a. Paste your SQL Statement here:

b. Screen print your results and paste here:

Part F: Practice function when value is NULL (5 points)

a. Paste your SQL Statement here:

b. Screen print your results and paste here:

Part G: Using GROUP BY clause (5 points)

a. Paste your SQL Statement here:

b. Screen print your results and paste here:

Part H: Using function on WHERE clause (5 points)

a. Paste your SQL Statement here:

b. Screen print your results and paste here:

Part I: Using function to calculate date difference (5 points)

a. Paste your SQL Statement here:

b. Screen print your results and paste here:

Part J: Using conversion function (5 points)

a. What do you use to convert the numerical data into VARCHAR data type?

b. Paste your SQL Statement here:

c. Screen print your results and paste here:

 

BIS 345 Week 3 iLab latest

BIS 345 Week 3 iLab latest

Check this A+ tutorial guideline at

http://www.assignmentclick.com/bis-345/bis-345-week-3-ilab-latest

For more classes visit

http://www.assignmentclick.com

BIS 345 Week 3 iLab latest

Student Name:

Using Lab 3, answer the questions and provide copies of your SQL Statements and/or results for each Section listed below

Part A Join two tables (4 points)

a. Paste your SQL Statement here:

b. Screen print your results and paste here:

Part B: Join two tables and apply WHERE clause (4 points)

a. Paste your SQL Statement here:

b. Screen print your results and paste here:

Part C: Inner join two tables (4 points)

a. Paste your SQL Statement here:

b. Screen print your results and paste here:

Part D: Inner join two tables and apply WHERE clause – Practice # 1 (5 points)

c. Paste your SQL Statement here:

d. Screen print your results and paste here:

Part E: Inner join two tables – Practice #2 (5 points)

a. Paste your SQL Statement here:

b. Screen print your results and paste here:

Part F: Inner join two tables – Practice #3 (5 points)

a. Paste your SQL Statement here:

b. Screen print your results and paste here:

Part G: Join two tables with sub-query – Practice # 1 (5 points)

a. Paste your SQL Statement here:

b. Screen print your results and paste here:

Part H: Join two tables with sub-query -Practice #2 (5 points)

a. Paste your SQL Statement here:

b. Screen print your results and paste here:

Part I: Single table join (5 points)

c. Paste your SQL Statement here:

d. Screen print your results and paste here:

Part J: Complex join (5 points)

a. Paste your SQL Statement here:

b. Screen print your results and paste here:

 

BIS 345 Week 4 iLab latest

BIS 345 Week 4 iLab latest

Check this A+ tutorial guideline at

http://www.assignmentclick.com/bis-345/bis-345-week-4-ilab-latest

For more classes visit

http://www.assignmentclick.com

BIS 345 Week 4 iLab latest

Using Lab 4 provide copies of your SQL Statements and/or results for each Section listed below

Part A Supplier and product list (10 points)

a.Paste your SQL Statement here:

b.Screen print your results and paste here:

Part B: Order information (10 points)

a.Paste your SQL Statement here:

b.Screen print your results and paste here:

Part C: Customer and order (10 points)

a.Paste your SQL Statement here:

a.Screen print your results and paste here:

Part D: Employee and territory list (10 points)

b.Paste your SQL Statement here:

c.Screen print your results and paste here:

Part E: Order and product (10 points)

a.Paste your SQL Statement here:

b.Screen print your results and paste here:

Part F: Best customer list (10 points)

a.Paste your SQL Statement here:

b.Screen print your results and paste here:

Part G: Join Shipper list (10 points)

a.Paste your SQL Statement here:

b.Screen print your results and paste here:

Part H: Account payable (10 points)

a.Paste your SQL Statement here:

a.Screen print your results and paste here

Part I: Create a dataset for reporting (10 points)

b.Paste your SQL Statement here:

c.Screen print your results and paste here:

Part J: Report (10 points)

a.Paste a screen print of the report view:

Lab 4 Part 1: Table Joins and functions

Overview

Using the Northwind database, you have been requested to select data for specific needs. Once selected, your data will be presented to management. You will have to determine which fields are the most appropriate to be selected. Do not include any fields that management does not need to see.

Below is a screenshot of the Northwind Database schema:

Log into SQL Server and access the database engine. Once into SQL Server’s database engine, select the Northwind database as usual.

Part A

Northwind Traders deals with a number of suppliers. The Products table includes the supplier ID for each product. Go through the Products table and provide a count of the number of products from each company. The output should show the name of the supplier and a count of the number of products it supplies.

As you prepare to create this query, decide which information needs to be displayed – this will be listed in the SELECT clause. Review the tables to determine which tables to use – these will be listed in the FROM clause. This problem will require an aggregate function – which one? What fields will be in the GROUP BY? If more than one table is needed, how will these tables be joined?

1. Using the query window, type the SELECT statement needed to produce the desired results. The SQL is given below and after you’ve typed your query, click on the Execute button to run the query and see the results.

The first rows of your result set should look like this:

2. Open up the Lab 4 Student Answer Sheet located in Doc Sharing, and answer the questions related to this part of the lab.

3. You must provide copies of your SQL statement and/or results. You may be asked to take a screenshot or cut and paste the SQL into the Word document. Follow the instructions on your lab answer sheet.

(To take a screenshot, press CTRL-ALT-PRINTSCREEN. Nothing appears to happen on your screen, but this set of keystrokes places a picture of your screen on the clipboard. In Word, just put your mouse where you want the screenshot to go, and then right click and press Paste. The screenshot will appear in your Word document after a few seconds).

Part B

The “Orders” and “Order Details” tables contain the data relating to current invoices. Provide a list showing order ID, order date, and order total (don’t forget to include the discount). Round the order total to two decimals; display the order date as mm/dd/yyyy (no timestamp).

As you prepare to create this query, decide which information needs to be displayed – this will be listed in the SELECT clause. Review the tables to determine which tables to use – these will be listed in the FROM clause. This problem will require a calculated field and an aggregate function. What fields will be in the GROUP BY? If more than one table is needed, how will these tables be joined?

1. Using the query window, type the SELECT statement needed to produce the desired results. The SQL is given below:

2. Press the Execute button. A partial result set is displayed below:

3. Answer the questions under Part B of your Lab Answer Sheet, and paste the first SELECT statement and a screenshot of the result set into a Microsoft Word document. Your SQL must be cut and pasted from your SQL window, and should NOT be a screenshot. However, you should use a screenshot of the result set as you did in the previous question. The screenshot should show at least the first 10 rows of the results.

BIS 345 Week 5 iLab latest

BIS 345 Week 5 iLab latest

Check this A+ tutorial guideline at

http://www.assignmentclick.com/bis-345/bis-345-week-5-ilab-latest

For more classes visit

http://www.assignmentclick.com

BIS 345 Week 5 iLab latest

Using Lab 5, provide copies of your SQL Statements, results and reports for each Section listed below

Part A Product and Category dataset (7 points)

a. Paste your SQL Statement here:

Screen print your results and paste here:

Part B: Product and Category report (10 points)

a. Paste your report here:

Part C: Sales and Territory dataset (10 points)

a. Paste your SQL Statement here:

Screen print your results and paste here:

Part D: Sales and Territory, report (10 points)

a. Paste your report here:

Part E: Sales and Territory Report Interpretation (10 points)

Write your interpretation of the results. The interpretation must be a minimum of one paragraph (3 to 5 well-formed sentences) with no spelling or grammatical errors. Based on these results, what other data would you recommend that the business consider for further analysis?