POS/410

SQL FOR BUSINESS

The Latest Version A+ Study Guide

**********************************************

POS 410 Entire Course Link

https://hwsell.com/category/pos-410/

**********************************************

POS 410 Week 1 Quiz

Instructions: Highlight your selected answer, save, and upload the results in the assignments section.

1. Which SQL statement is used to extract data from a database?
2. GET
3. EXTRACT
4. OPEN
5. SELECT
6. Which SQL statement is used to update data in a database?
7. SAVE AS
8. UPDATE
9. MODIFY
10. SAVE
11. Which SQL statement is used to insert new data in a database table?
12. ADD NEW
13. ADD RECORD
14. INSERT INTO
15. INSERT NEW
16. With SQL, how do you select a column named “FirstName” from a table named “Persons”?
17. SELECT Persons.FirstName
18. EXTRACT FirstName from Persons
19. SELECT FirstName FROM Persons
20. With SQL, how do you create a table named “Persons” with ID, “FirstName”, and “LastName” as columns?

POS 410 Week 2 Learning Team: Project Plan

Create the Team’s Project Plan by creating a Word document that outlines the tasks to be completed for each week.  Team members need to volunteer for tasks, and I suggest putting names by each task so that it’s clear for the team who is responsible for what each week.  Each team member is required to contribute to the team project deliverables in a meaningful way in order to get invididual credit for the team project.

Suggested tasks for week one:

Create the Team Database and name it TeamNameChartOfAccounts.

Create a table named Accounts with the following columns:

• Account No.
• Account Type
• Short Description
• Long Description
• Balance

Submit the Project Plan for feedback and evaluation to the Assignment Files tab.

POS 410 Week 2 Lab One

Download the attached Word Document Template named “Lastname – Lab 1.doc”. (change Lastname to your last name)

• Create a SQL Server Database.
• Create the following two tables using the following fields:

(Note: Supply the SQL Server data types when creating the tables. Apply a primary key to each table.)

Employee

• Social_Security_Number

  • Last_Name

  • First_Name

  • Address

  • City

  • State

  • Zip_Code

  • Telephone_Area_Code

  • Telephone_Number

  • Email_Address

  • Job_Title_Code

  • Hire_Date

  • Salary

Job_title

• Job_Title_Code

  • Job_Title

  • Exempt  (holds exempt status which is either 1 or 0)

  • Minimum_Salary

  • Maximum_Salary

• Using the SQL INSERT statement:

o   Enter ten records into the employee table.

o   Enter five records into the job_title table.

Include all SQL statements (i.e create, insert, etc used to complete each task of your assignment. Do not include screenshots – rather copy/paste the SQL scripts in the Word document template attached.

Click the Assignment Files tab to submit your assignment.

POS 410 Week 3 Learning Team Progress Report

Submit a team progress report to your instructor explaining what your team accomplished during the week and any challenges your team faced.

POS 410 Week 3 Lab Two

SQL Lab 2

Due Week 3 Day 7

Post a Microsoft Word document including these statements and named “Lastname – Lab 2.doc”. (change Lastname to your last name)

Using the database and tables from Lab One, do the following:

• Insert ten more records into each table.  Verify your inserts by selecting all rows from each table.

• Write SQL queries using BETWEEN, LIKE and UNION as follows:

1. Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection. (Use salaries to restrict the data.)

2. Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection. (Use hire dates to restrict the data.)

3. Write a SQL query that joins two tables in the example database and uses LIKE to restrict record selection. (Use telephone area codes to restrict data.)

4. Write a SQL query that joins two tables in the example database and uses LIKE to restrict record selection. (Use zip codes to restrict data.)

5. Write a SQL query that uses UNION of the two tables using at least one column from each table.

• Write queries using the SQL GROUP statement to produce the requested reports as described below.  In general, to make grouping meaningful, a function such as COUNT or SUM is used.  In the below tasks, COUNT(*) is the desired function to include in your SELECT statement along with the requested fields.

6. Display total number of employees for each job title.  Columns should include Job_Title and ‘Total Employees’. (Hint: use the “AS clause” to rename a column in the result set.)

7. Display total number of employees for each salary.  Columns should include Salary and ‘Total Employees’.

8. Display total number of employees for each salary within each job title.  Columns should include Job Title, Salary, and ‘Total Employees’.

9. Display total number of employees for each salary grouped by exempt status.  Columns should include Exempt, Salary, and ‘Total Employees’.

• Include all SQL statements (i.e create, insert, etc) used to complete each task of your assignment. Do not include screenshots, rather include the SQL scripts in the Word document. Label each script with the number of the query and use the order in which each query is listed in the assignment.

POS 410 Week 3 Quiz

Instructions: Highlight your selected answer, save, and upload the results in the assignments section.

POS 410 Week 4 Learning Team: Progress Report

Submit a team progress report to your instructor explaining what your team accomplished during the week and any challenges your team faced.

POS 410 Week 4 Lab Three

Download the attached Word Document Template named “Lastname – Lab 3.doc”. (change Lastname to your last name)

• Using the database and tables from Week Three, write queries using the SQL UPDATE statement. 

   

  • Choose a job_title_code. Increase all employees’ salaries that have that job_title_code by 10%.
  • Choose a job_title_code. Increase the Minimum_salary and Maximum_salary for this job_title_code in the job_title table.
  • Increase all employees’ salaries by 5%.
  • Choose an employee from the employee table; delete this employee.
  • Choose a job_title_code from the job_title table; delete this job_title_code.

    • Describe any differences you found between deleting a row from the employee table and deleting a row from the job_title table.

• Write SQL statements to:

  • Calculate the average salary for all employees.
  • Calculate the maximum salary for exempt employees and the maximum salary for non-exempt employees.
  • Calculate the maximum salary for all employees.
  • Calculate the minimum salary for all employees.
  • Create a stored procedure that does the following:

    • Accepts one parameter (job_title_code)
    • Based on the job_title_code passed into the procedure, return the job_title_code, job title, and the maximum salary for that job_title_code.

  • Create a view named “EmployeeSalary” and include the following fields from both the Employee and Job_title tables:  employee first name, employee last name, employee salary, job_title, minimum salary, and maximum salary.

• Include all SQL statements and results for each task in your assignment post. Do not include screenshots – rather copy/paste the SQL scripts into the attached Word document.

POS 410 Week 5 Learning Team: SQL Account Database Project

Complete and submit work on Service Request SR-kf-009, “SQL Accounting Database.”

Click the Assignment Files tab to submit your assignment.

Below is a quick summary of the items that should be included in your learning team assignment according to the syllabus.  I’ve also included the rubric I use to grade the project and a sample of what the report might look like.  I’ve put a few additional notes on items 3 thru 5 in blue below, as those relate to queries that need to be provided.

1. Summary of your team’s observationsof the “Charts of Accounts” data including recommendations on normalizing the data as well as rationale for any keys and indexes chosen. Breaking the data into more than one table is not required for this report, however your submission should include recommendations about how the data could be stored more effectively for queries outside of the report defined in SR-kf-009. Pay close attention to the data itself and consider how it should be stored versus how it’s stored in the spreadsheet today.
2. Database diagram
3. SQL statements used for creating database table(s) and any related keys and/or indexes used. These will be your create table statements for the tables you have chosen.
4. SQL statements used to load test dataThis could be a bulk insert statement, a series of manual INSERT statements, or some screenshots of how you used SQL Server Integration Services (SSIS) to import the data.  The method you use is up to your team.
5. SQL statement(s) used to query data for reportYour team has some flexibility in this area.  In some cases, I have teams that use SQL Server Reporting Services to produce the report, others use a single query, and still others use multiple queries.  I would estimate that roughly half of the teams I have taught use more than one query.  In all cases, I’ve seen a lot of variances in how teams choose to implement the report.  Let me provide some suggestions, particularly if you choose to accomplish with one query, which is definitely possible.  First, research the substring or left functions.  Either of these can help you pull the first two digits of the account number as described in the requirement.  If you GROUP BY the first two digits, that is one way of handling the “break based on the first two digits” requirement mentioned.  Secondly, research the COMPUTE function.  You can use this in conjunction with SUM to create your subtotals and total.
6. Report results

Example of using the ROLLUP and SUM functions for subtotals and a grand total:

SELECT Country, State, SUM(order total)

FROM Sales.SalesOrderHeader

GROUP BY Country, State

WITH ROLLUP