382756 SQL Accounting Database

SQL Accounting Database This is the only requirements that I need: Description of Request: Design an SQL database that contains the fields found in the company's Chart of Accounts. Add a balance field to the database. Create a query that will display all of the fields of the database and run a report totaling the balance field using test data added to the database. Background of Request: Kudler's Chart of Accounts currently exists as a Microsoft Excel spreadsheet. In anticipation of new reports that management will be requesting we want to move the Chart of Accounts from the current spreadsheet to a SQL database. We want to create the database in order to facilitate decision making at the store and department levels. Expected Results/Impact when completed: An SQL database containing the fields in the current Chart of Accounts, plus a "balance" field. An SQL query that will display all of the database fields. A test of the database by means of a query by account number and a report totaling the balance field test data entered by the database design team. The query will display all fields (description short description and balance) using the account number as the key to the query. The report will display all fields with a break based on the first two digits of the account number and subtotal of the balance field at each break. A grand total of the balance field will be provided at the end of the report. The database design team will load sufficient entries into the balance field to prove their total routine is working - test balance entries are not necessary for all account numbers. Please create each step in different file: Stage 1: Create the following two tables using the following fields: Note: Supply the SQL Server data types when creating the tables. In the Employee table the Employee ID field should generate a unique number for each employee record and designate the field as the Primary Key. In the Job Title table a suitable field to use as a primary key is not present. You will need to create an additional field to use as the primary key that will generate a unique number for each job title record. The primary key from the Job Title table will appear as the foreign key in the Employee table. • Employee  Employee_ID  Last_name  First_name  Address  City  State  Telephone_area_code  Telephone_number  EEO-1 Classification  Hire_date  Salary  Gender  Race  Age  Job_Title_ID • Job_title  EEO-1 Classification  Job_title  Job description  Exempt / Non-Exempt Status Using the SQL INSERT statement: • Go to the Human Resources department in the Kudler Fine Foods intranet. Using information found in the Employee Files and the Job Classification information for the La Jolla and Encinitas stores enter records into the employee table for the following employees:  Glenn Edelman  Eric McMullen  Raj Slentz  Erin Broun  Donald Carpenter  David Esquivez  Nancy Sharp • Using the Kudler Fine Foods Job Classifications and Job Descriptions information enter records into the job_title table for the following job titles:  Accounting Clerk  Asst. Manager  Bagger  Cashier  Computer Support Specialist  Director of Finance & Accounting  Retail Asst. Bakery & Pastry  Retail Asst. Butchers and Seafood Specialists  Stocker Non-Exempt employees at Kudler Fine Foods are paid by hour wage and are required to track their working hours. Check the results by selecting all of the columns from both of your tables. Create the tables based on your design and feedback from the instructor in a SQL Server database. Prepare test data and load it into the SQL Server database. Load data from the Chart of Accounts making sure to only store the data into the relevant tables. In addition to posting your database post a MS Word document to the individual forum of the student designated as the “team poster” for this week. This MS Word document should include the SQL code used to create your database and tables as well as load the data. Your document should include screen shots of the SQL tab from SQL Server Management Studio for each SQL statement. Stage 2: data. Your document should include screen shots of the SQL tab from SQL Server Management Studio for each SQL statement. Using the database and tables from Week Two incorporate any recommended changes by the instructor and write SQL statements and enter the records into the Employee table for the workers identified in the Employee Files for the administrative offices and the Del Mar location. Check the results by selecting all of the columns from both of your tables. Write SQL queries using Between and Like: • Write a SQL query that joins two tables in the example database and uses BETWEEN to restrict record selection. (Use salary to restrict the data.) • 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.) • 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.) • Write a SQL query that joins two tables in the example database and uses LIKE to restrict record selection. (Use age to restrict data.) Using the updated database write the following queries using the SQL GROUP statement: • Group employees by job classification: Select the employees' last names and group them by EEO-1 Classification. • Group employees by salary: Select the employees' last names and group them by salary. • Group employees by salary within their job classification: Select the employees' last names and group them by salary within their EEO-1 Classification. • Select the employees' last names and group them by salary within job titles that are grouped into exempt and non-exempt. Submit your database by creating a backup of your database and posting the “.bak” file generated by SQL Server Create and test an account number query. This query should retrieve account data from multiple tables using joins to gain meaningful information from the data. In addition to posting your database post a MS Word document to the individual forum of the student designated as the “team poster” for this week which includes the SQL code used in the SQL statements above. Your document should include screen shots of the SQL tab from SQL Server Management Studio for each SQL statement. Stage 3: Using the database and tables from Week Three incorporate any recommended changes by the instructor and write SQL statements. Note: Create a backup of your database before running queries that modify data. • Choose an EEO-1 Classification: Increase all employees' salaries that have: the selected EEO-1 classification by 10%. • Increase all employees' salaries by 5%. • Choose an employee from the employee table delete this employee. Note: Restore your database before running the following queries. Using the database and tables from Week Three 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 exempt employees and the minimum salary for non-exempt employees. • Calculate the minimum salary for all employees. Submit your database by creating a backup of your database and posting the “.bak” file generated by SQL Server Submit your database by creating a backup of your database and posting the “.bak” file generated by SQL Server to your individual forum. Additionally post a MS Word document to your individual forum which includes the SQL code used in the SQL statements above. Your document should include screen shots of the SQL tab from SQL Server Management Studio for each SQL statement. Please create a backup of the database “.bak” file generated by SQL Server Stage 4: Create a new database with four tables: DEPARTMENTS, EMPLOYEES, PROJECTS, and, WORKS_ON. The fields for each table are: DEPARTMENTS: ID, Name, Description EMPLOYEES: ID, Fname, Lname, SSN, Salary, Department_ID PROJECTS: ID, Name, Department_ID WORKS_ON: Employee_ID, Project_ID You will be given INSERT queries to run to add data to the tables. In addition to posting your completed database, you must write and record the results of the following queries on a separate MS Word document: For each department retrieve the department number, the number of employees in the department and their average salary For each project retrieve the project number, project name and number of employees For each project on which more than two employees retrieve the project number, the project name and the number of employees who work on the project For each project, retrieve the project number, project name, number of employees from department 1003 who work on the project For each department having more than 5 employees, retrieve the department number and number of employees making over $40,000 Select all employees currently assigned to projects, present in alphabetical order by last name. Employees should be listed once. List in last name alphabetical order all employees not currently assigned to projects. Employees should be listed once. List in last name alphabetical order, all employees currently assigned to more than one project. Employees should be listed once. Hints: Several queries need sub-queries and the IN clause should be used instead of the EXISTS clause. Submit your database by creating a backup of your database and posting the “.bak” file generated by SQL Server to your individual forum. Additionally, post a MS Word document to your individual forum which includes the SQL code used in the SQL statements above. Your document should include screen shots of the Results window from SQL Query tab for each SQL statement. Please note for the week five database, no changes are to be made to the database's structure as designed other than adding keys (primary and foreign). Field and table names should not be changed. No additional fields or tables are necessary. I have the solution queries and all I should have to do is copy and paste the solution into the query window for your database and see results. No additional data should be added to the database other than the INSERT queries provided. Points will be deducted for altering the structure of the database, adding additional data to your database, or for not properly creating your primary and foreign keys. Only the SQL used to create your database, tables, and keys, insert the required data, and the eight queries above should be included in your MS Word document. All aspects of the SQL code used should be clearly labeled. For example: Query Number: Description of the query. SQL Code Screen shot. If a query cannot be identified, no credit will be earned for that query. Partial credit will be earned for SQL code written but incorrect results of the queries execution.