3. Filtering SecureIT DataSecureIT, Inc., is a small computer security contractor that provides computer securityanalysis, design, and software implementation for commercial clients. Almost all of SecureIT work requires access to classified material or confidential company documents. Consequently, all of the security personnel have clearances of either Secret or Top Secret. Some have even higher clearances for work that involves so-called black box security work. While most of the personnel information for SecureIT resides in database systems, a basic employee worksheet is maintained for quick calculations and ad hoc report generation. Because SecureIT is a small company, it can take advantage of Excel’s excellent list management facilities to satisfy many of its personnel information management needs. You have been provided with a sample worksheet, T3_Employee_Data.xls, to assist SecureIT with producing several worksheet summaries. Here is what is needed:1. One worksheet that is sorted by last name and hire data.2. One worksheet that uses a custom sort by department in this order: Marketing, Human Resources, Management, and Engineering.3. One worksheet that uses a filter to display only those employees in the Engineeringdepartment with a clearance of Top Secret (TS).4. One worksheet that uses a custom filter to display only those employees born between 1960 and 1969 (inclusive).5. One worksheet that totals the salaries by department and the grand total of all department salaries. This worksheet should be sorted by department name first. ID2956 Michael4123 Steve3117 Frederich2122 Yuan2451 Francine2896 Samuel2768 Phyllis1119 Sharad2344 Nemesha3210 Carmen2002 Edgar3015 Patti2678 Koshi2733 John3314 Svetlana4006 Alice2042 Hillary1851 Melinda1355 Barbara1173 Roberta1441 James1042 Maria1032 Hillary1009 Kevin1614 Artie2105 Luca1016 OscarFirst Name Last NameGoldsteinBallmerDepartmentEngineeringEngineeringBednarczyk EngineeringEngineeringEngineeringEngineeringEngineeringEngineeringEngineeringOrtega-Molina EngineeringRothrockEngineeringStonesiferEngineeringYamamotoEngineeringEngineeringZumkowskiKartashevRovikCushnerEnglishGrabowskiKurzweilVan HornAndrettiFlintsteelGrundiesLambrosPacioliGomezChangDetweilerGatesLeonardManispourMehtaManagementManagementManagementManagementManagementTitleEngineerEngineerEngineerEngineerEngineerHuman Resources StaffHuman Resources ManagerStaffManagerManagerManagerManagerMarketingMarketingMarketingMarketingMarketingMarketingEngineerEngineerManagerEngineerEngineerEngineerEngineerEngineerStaffCSRStaffCSRCSRStaffCSRSalaryHire DateBirth Date Gender Clearance3/7/63 M4/14/70M5/27/703/4/644/29/70M4/10/747/2/70 F2/4/69 M2/12/65 F3/28/52 F3/21/70M3/10/66 F1/24/63 F51,0004/11/9742,9005/1/0256,7005/14/9946,5409/5/9556,700 10/10/9566,30059,80054,50065,0001/28/9712/13/9610/13/9010/1/959/16/992/8/947/6/9811/16/9546,00053,30064,30049,60033,0004/18/96 12/12/72 M38,00011/5/0143,00012/1/0132,0008/26/9462,04010/1/9375,700 10/13/9263,00066,50042,50034,50038,90041,00042,30043,500MFMFFFEPASHSSH assesses CZSKROSSZZUZSz6/16/92 12/22/5812/18/923/21/903/21/9012/24/895/16/938/26/952/16/90MMF5/3/821/26/55 M5/6/612/14/564/10/65FM2/3/588/20/70 F8/22/58 F3/4/61 M7/13/70MM5/6/504/29/67 MTSNTSTSSSSSSTSNTSTSсNN

To complete the tasks, you can follow these steps: Open the T3_Employee_Data.xls file in Excel.…

3. Filtering SecureIT Data SecureIT, Inc., is a small computer security contractor that provides computer security analysis, design, and software implementation for commercial clients. Almost all of SecureIT work requires access to classified material or confidential company documents. Consequently, all of the security personnel have clearances of either Secret or Top Secret. Some have even higher clearances for work that involves so-called black box security work. While most of the personnel information for SecureIT resides in database systems, a basic employee worksheet is maintained for quick calculations and ad hoc report generation. Because SecureIT is a small company, it can take advantage of Excel’s excellent list management facilities to satisfy many of its personnel information management needs. You have been provided with a sample worksheet, T3_Employee_Data.xls, to assist SecureIT with producing several worksheet summaries. Here is what is needed: 1. One worksheet that is sorted by last name and hire data. 2. One worksheet that uses a custom sort by department in this order: Marketing, Human Resources, Management, and Engineering. 3. One worksheet that uses a filter to display only those employees in the Engineering department with a clearance of Top Secret (TS). 4. One worksheet that uses a custom filter to display only those employees born between 1960 and 1969 (inclusive). 5. One worksheet that totals the salaries by department and the grand total of all department salaries. This worksheet should be sorted by department name first.

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Concept explainers

Question

3. Filtering SecureIT Data
SecureIT, Inc., is a small computer security contractor that provides computer security
analysis, design, and software implementation for commercial clients. Almost all of SecureIT work requires access to classified material or confidential company documents. Consequently, all of the security personnel have clearances of either Secret or Top Secret. Some have even higher clearances for work that involves so-called black box security
work. While most of the personnel information for SecureIT resides in database systems, a
basic employee worksheet is maintained for quick calculations and ad hoc report generation. Because SecureIT is a small company, it can take advantage of Excel’s excellent list management facilities to satisfy many of its personnel information management needs. You have been provided with a sample worksheet, T3_Employee_Data.xls, to assist SecureIT with producing several worksheet summaries. Here is what is needed:

1. One worksheet that is sorted by last name and hire data.
2. One worksheet that uses a custom sort by department in this order: Marketing, Human
Resources, Management, and Engineering.
3. One worksheet that uses a filter to display only those employees in the Engineering
department with a clearance of Top Secret (TS).
4. One worksheet that uses a custom filter to display only those employees born between
1960 and 1969 (inclusive).
5. One worksheet that totals the salaries by department and the grand total of all department salaries. This worksheet should be sorted by department name first.

ID
2956 Michael
4123 Steve
3117 Frederich
2122 Yuan
2451 Francine
2896 Samuel
2768 Phyllis
1119 Sharad
2344 Nemesha
3210 Carmen
2002 Edgar
3015 Patti
2678 Koshi
2733 John
3314 Svetlana
4006 Alice
2042 Hillary
1851 Melinda
1355 Barbara
1173 Roberta
1441 James
1042 Maria
1032 Hillary
1009 Kevin
1614 Artie
2105 Luca
1016 Oscar
First Name Last Name
Goldstein
Ballmer
Department
Engineering
Engineering
Bednarczyk Engineering
Engineering
Engineering
Engineering
Engineering
Engineering
Engineering
Ortega-Molina Engineering
Rothrock
Engineering
Stonesifer
Engineering
Yamamoto
Engineering
Engineering
Zumkowski
Kartashev
Rovik
Cushner
English
Grabowski
Kurzweil
Van Horn
Andretti
Flintsteel
Grundies
Lambros
Pacioli
Gomez
Chang
Detweiler
Gates
Leonard
Manispour
Mehta
Management
Management
Management
Management
Management
Title
Engineer
Engineer
Engineer
Engineer
Engineer
Human Resources Staff
Human Resources Manager
Staff
Manager
Manager
Manager
Manager
Marketing
Marketing
Marketing
Marketing
Marketing
Marketing
Engineer
Engineer
Manager
Engineer
Engineer
Engineer
Engineer
Engineer
Staff
CSR
Staff
CSR
CSR
Staff
CSR
Salary
Hire Date
Birth Date Gender Clearance
3/7/63 M
4/14/70
M
5/27/70
3/4/64
4/29/70
M
4/10/74
7/2/70 F
2/4/69 M
2/12/65 F
3/28/52 F
3/21/70
M
3/10/66 F
1/24/63 F
51,000
4/11/97
42,900
5/1/02
56,700
5/14/99
46,540
9/5/95
56,700 10/10/95
66,300
59,800
54,500
65,000
1/28/97
12/13/96
10/13/90
10/1/95
9/16/99
2/8/94
7/6/98
11/16/95
46,000
53,300
64,300
49,600
33,000
4/18/96 12/12/72 M
38,000
11/5/01
43,000
12/1/01
32,000
8/26/94
62,040
10/1/93
75,700 10/13/92
63,000
66,500
42,500
34,500
38,900
41,000
42,300
43,500
MFMFFFE
PASHSSH assesses CZSKROSSZZUZSz
6/16/92 12/22/58
12/18/92
3/21/90
3/21/90
12/24/89
5/16/93
8/26/95
2/16/90
M
M
F
5/3/82
1/26/55 M
5/6/61
2/14/56
4/10/65
F
M
2/3/58
8/20/70 F
8/22/58 F
3/4/61 M
7/13/70
M
M
5/6/50
4/29/67 M
TS
N
TS
TS
S
S
S
S
S
TS
N
TS
TS
с
N
N

Expert Solution

Step 1

To complete the tasks, you can follow these steps:

Open the T3_Employee_Data.xls file in Excel.
Select the first worksheet, and then click on the "Data" tab in the top menu.
Click on the "Sort A to Z" button to sort by last name, and then click on the "Add Level" button to add a sort by hire date.
Select the second worksheet, and then click on the "Data" tab in the top menu.
Click on the "Sort" button, and then select "Custom Sort" from the drop-down menu.
In the "Sort" dialog box, under "Column," select the "Department" column.
In the "Order" column, enter "Marketing" in the first row, "Human Resources" in the second row, "Management" in the third row, and "Engineering" in the fourth row.
Click "OK" to apply the custom sort.

Trending now

This is a popular solution!

Step by step

Solved in 3 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.