Generate a report to display staff full names (combination of first and last names), jobs and...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Generate a report to display staff full names (combination of first and last names), jobs and department names. Rename the column headings to "Full Name", "Job Title", and "Department Name". Order the report alphabetically by the last name. A snapshot of sample rows is given below: Full Name Ellen Abel 2 Bundar Ande 3 Mozhe Atkinson 4 David Austin 5 Hermann Baer 6 Shelli Baida Job Ti SA REP SA REP ST_CLERK IT PROG PR_REP PU CLERK Department Name Sales Sales Shipping IT Public Relations Purchasing 3- Modify question 2 to display the full name, department name, salary, and the city of all employees who earn a salary greater than 12000. See sample output below. Full Name 1 Steven King 2 Neena Kochhar DEPARTMENT_NAME SALARY CITY Executive Executive Executive 3 Lex De Haan 4 John Russell 5 Karen Partners 6 Michael Hartstein Marketing Sales Sales 24000 Seattle 17000 Seattle 17000 Seattle 14000 Oxford 13500 Oxford 13000 Toronto 2 3 4- Generate a report to display the department details (department id, name, street address, postal code, city, state province and region name) of all departments that are in regions 1 (Europe) and 4 (Middle East and Africa). A snapshot of sample rows is given below: DEPARTMENT_ID DEPARTMENT_NAME STREET ADDRESS 40 Human Resources 8204 Arthur St 70 Public Relations Schwanthalerstr. 7031 80 Sales POSTAL CODE CITY STATE_PROVINCE REGION_NAME London (null) (null) 80925 Munich Bavaria Oxford Oxford Magdalen Centre, The Oxford Science Park OX9 92B Europe Europe Europe 5- Generate a report to display the full names (combination of first and last names), email address (generated by concatenating email id with @MEGACORP.COM), department name and office address (combination of city and country) of all employees. Sort the output by the last name. Rename the columns as shown in the column headings below. Full Name 1 Ellen Abel 2 Sundar Ande 3 Mozhe Atkinson David Austin 5 Hermann Baer 6 Shelli Baida 7 Amit Banda 8 Elizabeth Bates 9 Sarah Bell 10 David Bernstein 11 Laura Bissot 12 Harrison Bloom 13 Alexis Bull 14 Anthony Cabrio 15 Nanette Cambrault 16 Gerald Cambrault 17 John Chen 18 Kelly Chung Email Department Name EABEL@MEGACORP.COM Sales SANDE MEGACORP.COM Sales MATKINSOBMEGACORP.COM Shipping DAUSTIN@MEGACORP.COM IT HBAERGMEGACORP.COM SBAIDA MEGACORP.COM Purchasing ABANDA@MEGACORP.COM Sales EBATES@MEGACORP.COM Sales SBELL@MEGACORP.COM Shipping DBERNSTE MEGACORP.COM Sales LBISSOTOMEGACORP.COM Shipping HBLOOM MEGACORP.COM Sales Shipping ACABRIO MEGACORP.COM Shipping NCAMBRAUSMEGACORP.COM Sales GCAMBRAU@MEGACORP.COM Sales JCHENGMEGACORP.COM Finance KCHUNG@MEGACORP.COM Shipping ABULL@MEGACORP.COM Full Address Oxford, United Kingdom Oxford, United Kingdom South San Francisco, United States of America Southlake, United States of America Public Relations Munich, Germany Seattle, United States of America Oxford, United Kingdom Oxford, United Kingdom South San Francisco, United States of America Oxford, United Kingdom South San Francisco, United States of America Oxford, United Kingdom South San Francisco, United States of America South San Francisco, United States of America Oxford, United Kingdom Oxford, United Kingdom Seattle, United States of America South San Francisco, United States of America 6- Modify the previous question to generate a dynamic report that displays the employee details (as listed in (5) above) for a specific country (i.e. the user should be prompted to enter a country name). Run the report by entering different countries (for example, United Kingdom). Hint: use substitution variable (&) 7- Generate a report to display the full names and department names of all employees. The report must also include all departments including those that currently do not have any employees assigned to them. Hint: Use Outer Join. A snapshot of sample rows is given below. Full Name 100 Jennifer Whalen 101 Michael Hartstein 102 Pat Fay 103 Susan Mavris 104 Hermann Baer 105 Shelley Higgins 106 William Gietz 107 108 109 110 111 112 departments with no employees DEPARTMENT_NAME Administration Marketing Marketing Human Resources Public Relations Accounting Accounting NOC Manufacturing Government Sales IT Support Benefits Shareholder Services 8- Generate a report to display the employee name and employee number along with their manager's name and manager number. Label the columns Employee Name, Employee #, Manager #, and Manager Name respectively. Hint: Use Self Join A snapshot of sample rows is given below: Employee Name 12 Den Raphaely 13 Lex De Haan 14 Neena Kochhar 15 Shelley Higgins 16 Hermann Baer 17 Susan Mavris 18 Jennifer Whalen 19 Nancy Greenberg 20 Alexander Hunold 21 Diana Lorentz Employee # 114 102 101 205 204 203 200 108 103 107 Manager # Manager Name 100 Steven King 100 Steven King 100 Steven King 101 Neena Kochhar 101 Neena Kochhar 101 Neena Kochhar 101 Neena Kochhar 101 Neena Kochhar 102 Lex De Haan 103 Alexander Hunold 9- Explain why the previous report generated the details of 106 only employees? (Note: there are a total of 107 employees in the employees table) 10- Modify question 9 to display all 107 staff members HR JOB_HISTORY employee_id start_date end_date job_id department_id JOBS job_id job_title min_salary max_salary DEPARTMENTS department_id department_name manager_id location_id EMPLOYEES employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id LOCATIONS location_id street_address postal code city state province country_id COUNTRIES country_id country_name region_id REGIONS region_id region_name 1- Display the employee first name, last name, and department name for all employees who have an A in their first name. Sample output shown below. FIRST_NAME 1 Alexander Khoo 2 Alana Walsh 3 Anthony Cabrio 4 Alexis Bull 5 Adam Fripp 6 Alexander Hunold IT Hutton Sales Banda Sales McEwen Sales Errazuriz Sales LAST_NAME 7 Alyssa 8 Amit 9 Allan 10 Alberto DEPARTMENT_NAME Purchasing Shipping Shipping Shipping Shipping Generate a report to display staff full names (combination of first and last names), jobs and department names. Rename the column headings to "Full Name", "Job Title", and "Department Name". Order the report alphabetically by the last name. A snapshot of sample rows is given below: Full Name Ellen Abel 2 Bundar Ande 3 Mozhe Atkinson 4 David Austin 5 Hermann Baer 6 Shelli Baida Job Ti SA REP SA REP ST_CLERK IT PROG PR_REP PU CLERK Department Name Sales Sales Shipping IT Public Relations Purchasing 3- Modify question 2 to display the full name, department name, salary, and the city of all employees who earn a salary greater than 12000. See sample output below. Full Name 1 Steven King 2 Neena Kochhar DEPARTMENT_NAME SALARY CITY Executive Executive Executive 3 Lex De Haan 4 John Russell 5 Karen Partners 6 Michael Hartstein Marketing Sales Sales 24000 Seattle 17000 Seattle 17000 Seattle 14000 Oxford 13500 Oxford 13000 Toronto 2 3 4- Generate a report to display the department details (department id, name, street address, postal code, city, state province and region name) of all departments that are in regions 1 (Europe) and 4 (Middle East and Africa). A snapshot of sample rows is given below: DEPARTMENT_ID DEPARTMENT_NAME STREET ADDRESS 40 Human Resources 8204 Arthur St 70 Public Relations Schwanthalerstr. 7031 80 Sales POSTAL CODE CITY STATE_PROVINCE REGION_NAME London (null) (null) 80925 Munich Bavaria Oxford Oxford Magdalen Centre, The Oxford Science Park OX9 92B Europe Europe Europe 5- Generate a report to display the full names (combination of first and last names), email address (generated by concatenating email id with @MEGACORP.COM), department name and office address (combination of city and country) of all employees. Sort the output by the last name. Rename the columns as shown in the column headings below. Full Name 1 Ellen Abel 2 Sundar Ande 3 Mozhe Atkinson David Austin 5 Hermann Baer 6 Shelli Baida 7 Amit Banda 8 Elizabeth Bates 9 Sarah Bell 10 David Bernstein 11 Laura Bissot 12 Harrison Bloom 13 Alexis Bull 14 Anthony Cabrio 15 Nanette Cambrault 16 Gerald Cambrault 17 John Chen 18 Kelly Chung Email Department Name EABEL@MEGACORP.COM Sales SANDE MEGACORP.COM Sales MATKINSOBMEGACORP.COM Shipping DAUSTIN@MEGACORP.COM IT HBAERGMEGACORP.COM SBAIDA MEGACORP.COM Purchasing ABANDA@MEGACORP.COM Sales EBATES@MEGACORP.COM Sales SBELL@MEGACORP.COM Shipping DBERNSTE MEGACORP.COM Sales LBISSOTOMEGACORP.COM Shipping HBLOOM MEGACORP.COM Sales Shipping ACABRIO MEGACORP.COM Shipping NCAMBRAUSMEGACORP.COM Sales GCAMBRAU@MEGACORP.COM Sales JCHENGMEGACORP.COM Finance KCHUNG@MEGACORP.COM Shipping ABULL@MEGACORP.COM Full Address Oxford, United Kingdom Oxford, United Kingdom South San Francisco, United States of America Southlake, United States of America Public Relations Munich, Germany Seattle, United States of America Oxford, United Kingdom Oxford, United Kingdom South San Francisco, United States of America Oxford, United Kingdom South San Francisco, United States of America Oxford, United Kingdom South San Francisco, United States of America South San Francisco, United States of America Oxford, United Kingdom Oxford, United Kingdom Seattle, United States of America South San Francisco, United States of America 6- Modify the previous question to generate a dynamic report that displays the employee details (as listed in (5) above) for a specific country (i.e. the user should be prompted to enter a country name). Run the report by entering different countries (for example, United Kingdom). Hint: use substitution variable (&) 7- Generate a report to display the full names and department names of all employees. The report must also include all departments including those that currently do not have any employees assigned to them. Hint: Use Outer Join. A snapshot of sample rows is given below. Full Name 100 Jennifer Whalen 101 Michael Hartstein 102 Pat Fay 103 Susan Mavris 104 Hermann Baer 105 Shelley Higgins 106 William Gietz 107 108 109 110 111 112 departments with no employees DEPARTMENT_NAME Administration Marketing Marketing Human Resources Public Relations Accounting Accounting NOC Manufacturing Government Sales IT Support Benefits Shareholder Services 8- Generate a report to display the employee name and employee number along with their manager's name and manager number. Label the columns Employee Name, Employee #, Manager #, and Manager Name respectively. Hint: Use Self Join A snapshot of sample rows is given below: Employee Name 12 Den Raphaely 13 Lex De Haan 14 Neena Kochhar 15 Shelley Higgins 16 Hermann Baer 17 Susan Mavris 18 Jennifer Whalen 19 Nancy Greenberg 20 Alexander Hunold 21 Diana Lorentz Employee # 114 102 101 205 204 203 200 108 103 107 Manager # Manager Name 100 Steven King 100 Steven King 100 Steven King 101 Neena Kochhar 101 Neena Kochhar 101 Neena Kochhar 101 Neena Kochhar 101 Neena Kochhar 102 Lex De Haan 103 Alexander Hunold 9- Explain why the previous report generated the details of 106 only employees? (Note: there are a total of 107 employees in the employees table) 10- Modify question 9 to display all 107 staff members HR JOB_HISTORY employee_id start_date end_date job_id department_id JOBS job_id job_title min_salary max_salary DEPARTMENTS department_id department_name manager_id location_id EMPLOYEES employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id LOCATIONS location_id street_address postal code city state province country_id COUNTRIES country_id country_name region_id REGIONS region_id region_name 1- Display the employee first name, last name, and department name for all employees who have an A in their first name. Sample output shown below. FIRST_NAME 1 Alexander Khoo 2 Alana Walsh 3 Anthony Cabrio 4 Alexis Bull 5 Adam Fripp 6 Alexander Hunold IT Hutton Sales Banda Sales McEwen Sales Errazuriz Sales LAST_NAME 7 Alyssa 8 Amit 9 Allan 10 Alberto DEPARTMENT_NAME Purchasing Shipping Shipping Shipping Shipping
Expert Answer:
Related Book For
Income Tax Fundamentals 2013
ISBN: 9781285586618
31st Edition
Authors: Gerald E. Whittenburg, Martha Altus Buller, Steven L Gill
Posted Date:
Students also viewed these computer network questions
-
Formulate a plausible mechanism for the following reaction. The product is a precursor of mediquox (shown in the margin), an agent used to treat respiratory infections in chickens (no, we are not...
-
Suppose that the spot price of the euro is currently $1.30. The 1-year futures price is $1.35. Is the interest rate higher in the United States or the euro zone?
-
On February 1, 2004, Zhang entered into a contract to buy former realtor Frank Sorichetti's Las Vegas home for $532,500. The contract listed a March clos- ing date and a few household furnishings as...
-
In the rainy season, the Amazon flows fast and runs deep. In one location, the river is \(23 \mathrm{~m}\) deep and moves at a speed of \(4.0 \mathrm{~m} / \mathrm{s}\) toward the east. The earth's...
-
On December 1, 2017, Fullerton Company had the following account balances. During December, the company completed the following transactions. Dec. 7 Received $3,600 cash from customers in payment of...
-
5) The angle of elevation of the second floor of a building from the point of view of somebody lying on the ground is 14. The angle of elevation to the third floor is 25. Find the distance between...
-
Hemming Company reported the following current-year purchases and sales for its only product. Date January 11 January 10 Activities Beginning inventory Units Acquired at Cost $13.80- Sales March 14....
-
Today Peter bought an apartment which he will rent. The market price which he paid for this apartment is 5 0 0 , 0 0 0 . Every year he will receive 3 5 , 0 0 0 from renting the apartment. Moreover,...
-
Assume that the organisation to not subsribe to any standartds, framework and best practice, how this impact the IS audit conclusion and what impact it will have in the financial sector as financial...
-
The Rick Sanchez Corp recently reported $4,678,000 of sales, $3,740,500 of operating costs other than depreciation, and $450,2200 of depreciation. The company had $533,250 of outstanding bonds that...
-
A company will make the following annual lease lease i payments over the course of a 3-year" $150, $190 $160. Rent expense in the first year? (straight-line depreciation method) A company buys a $48...
-
write a thesis for my Family Law class the topic is Divorce and the paper has to be 10 pages ?
-
How can data analytics, sentiment analysis, and social network analysis be utilized to measure the effectiveness of communication strategies, track audience engagement, and inform iterative...
-
Mr. Ahmed, an Omani, was relocated to work as a department manager at a leading drug company in the US. This relocation of work has caused a lot of anxiety on the part of Ahmed because it will be his...
-
In Problems, solve each system of equations. x + 2y + 3z = 5 y + 11z = 21 5y + 9z = 13
-
Clarita is a single taxpayer with two dependent children, ages 10 and 12. Clarita pays $3,000 in qualified child care expenses during the year. If her adjusted gross income (all from wages) for the...
-
Phil and Linda are 25-year-old newlyweds and file a joint tax return. Linda is covered by a retirement plan at work, but Phil is not. a. Assuming Phil's wages were $27,000 and Linda's wages were...
-
Fisafolia Corporation has gross income from operations of $220,000 and operating expenses of $160,000 for 2012. The corporation also has $20,000 in dividends from publicly traded domestic...
-
Stadler Corporations federal income tax rate is 34 percent. It reports $100,000 depreciation expense on its financial statements and deducts $140,000 depreciation expense on its tax return. How...
-
Which of the following items is not deductible? a. Dues for club used solely for business meetings b. Directly related business entertainment c. Business gift of less than $25 in value d. Dues for...
-
Probest Corporation (a calendar-year corporation) purchased and placed the following assets in service during the year All assets are used 100 percent for business. $100,000 of the cost of the...
Study smarter with the SolutionInn App