Question

Using the tables at the bottom, write a query to produce the results needed to answer each question below.

1. Display the query you would write to update the salary for John Jones

2. Display the query you would write to update all salaries by 5%

3. Display the query you would write to update all salaries by 5% for only the employees who live in Arizona

4. Write a query to insert a new record into the empinfo table (Hint: you are adding a full record - a new piece of information for each field/column)

5. Write a query to add a new record into the empinfo table - BUT, you only have the employee's first and last names along with their age at this point.

Sample Table: empinfo
first	last	id	age	city	state
John	Jones	99980	45	Payson	Arizona
Mary	Jones	99982	25	Payson	Arizona
Eric	Edwards	88232	32	San Diego	California
Mary Ann	Edwards	88233	32	Phoenix	Arizona
Ginger	Howell	98002	42	Cottonwood	Arizona
Sebastian	Smith	92001	23	Gila Bend	Arizona
Gus	Gray	22322	35	Bagdad	Arizona
Mary Ann	May	32326	52	Tucson	Arizona
Erica	Williams	32327	60	Show Low	Arizona
Leroy	Brown	32380	22	Pinetop	Arizona
Elroy	Cleaver	32382	22	Globe	Arizona
Sample Table: empsal
id	suprvsr	salary	bonus	bonus amt	office
99980	92001	45600	Y	3000	Arizona
99982	92001	47000	Y	3000	Arizona
88232	92001	52500	N		California
88233	92001	49300	Y	3000	Arizona
98002	92001	52200	Y	3000	Arizona
92001	88888	65000	Y	5000	California
22322	32380	46800	Y	3000	Arizona
32326	32380	50900	N		Arizona
32327	32380	48300	Y	3000	Arizona
32380	32380	53400	Y	3000	Arizona
32382	32380	55600	Y	3000	Arizona

Previous

Answer 1

Answer 1 > If we add up the Salary for John Jones & Bonus, we will get the total Salary for him .....i:e: 45,600+3,000 = 48,600.

Hence the Query created for John Jones is Salary + Bonus = Total Salary.

Answer 2 > Query to update all salaries by 5% is :- Salary + Bonus = Total Salary + Total Salary*5/100 = Total of Updated all salaries by 5%.

Answer 3 > Query to update all salaries for Arizona only is :- Salary + Bonus = Total Salary + Total Salary*5/100, Also we will use the Find Function as =FIND("ARIZONA", The entire Range for the state Column section fixed and freeze into excel ).......The cell into which numbers will appear, they belong to Arizona only and the cell in which #VALUE shows it is not for Arizona, Hence we will give salaries for Arizona only where the numbers are appearing.

Answer 4 > In order to add a complete new record in EMP INFO section is by pressing CTRL+ keys together just below the heading, then write the name of the employee then the Last Name. Then we need to enter Employee ID, then enter the Age, then city from where the employee belongs and at last the State.

Answer 5 > If we only have the information about Employee Name, Last Name & Age, then we can easily calculate the rest of the columns by using the V-Look up function. Taking the same criteria into consideration, I will write the query as per the table above. If we need to calculate the Employee ID, then formula will be :- =vlookup(entire range of Unique Names,entire range from Unique Names till ID's section,3,0). This way we will get the entire ID's of all the employees. But first we need to Concatenate the first name and last name so that we can get the unique and accurate name for V-look up.

Then by using the Unique Names Section column which we get by using the Concatenate formula, we can get the City and State Columns by using same V-Look up formula. For both the ranges, we need to use the concatenate formula so that Vlook up formula works and we get the Unique names ID on both the sections.

How to calculate ID's :-

=Vlookup(Complete range of Unique Names column, complete range from Unique Names till complete ID's column range,3,0).

How to calculate City names:-

=Vlookup(Complete Range of Unique Names Column,complete range from Unique Names till complete City column range,5,0).

How to calculate State names :-

=Vlookup(Complete range of Unique Names column, complete range from Unique Names till complete State column range,6,0).