What should be included in the file (see sample below): Your code for this question,...
Fantastic news! We've Found the answer you've been seeking!
Question:
![What should be included in the file (see sample below): Your code for this question, and The output of that](https://dsd5zvtm8ll6.cloudfront.net/si.experts.images/questions/2023/09/64fee637f2f70_1694426678324.jpg)
![Use table LOCATIONS for this Q2. Sometimes, we do not know how the database stores the literals (either all](https://dsd5zvtm8ll6.cloudfront.net/si.experts.images/questions/2023/09/64fee64f059d7_1694426702081.jpg)
![Use table Locations and Departments for this Q6 List the department ID, department name. Address (as in](https://dsd5zvtm8ll6.cloudfront.net/si.experts.images/questions/2023/09/64fee665367d7_1694426724285.jpg)
![Write an anonymous PL/SQL block with a nested (inner) block. In the out block, declare a variable called](https://dsd5zvtm8ll6.cloudfront.net/si.experts.images/questions/2023/09/64fee68298633_1694426753690.jpg)
Transcribed Image Text:
What should be included in the file (see sample below): • Your code for this question, and • The output of that question (just the text, no screenshot); right after its code. Such as below: REM Q1. SELECT employee_id, Last_name, First_name, manager_id FROM Employees Where upper (first_name) like "ALEXS'; OUTPUT EMPLOYEE ID LAST NAME 103 Hunold REM Q2. code for question #2 OUTPUT of Q2. FIRST NAME Alexander MANAGER ID To begin with this homework assignment #1, please build the Oracle sample tables. Consider that some students may have different backgrounds in SQL, some hints are provided at the end of this file. 102 Questions #1 to #8 require the SQL select statements, carefully pay attention which tables are based for each question table: All the five tables Employees, Departments, Locations, Dept, EMP may be involved. • Use table Employees for this Q1. List the employee_id, last name, first name, manager (immediate supervisor) ID for those employees that their first name starts with "Alex". Use table LOCATIONS for this 02. Use table LOCATIONS for this Q2. Sometimes, we do not know how the database stores the literals (either all upper. Initial Capital, or all lower cases). Use the function UPPER, we may make our query case insensitive. In this question, you are required to use this technique. Display the Location's location_id, street_address, postal code, city, country_id for those location(s) that city name spells as Seattle-but we do not know how the literal letters (upper case, lower, or...) stored in database. Use table LOCATIONS for this Q3. When we design tables, it is better to use small unit as a column, not same as in our daily language. For the example of ADDRESS, we usually will define the columns of street address, city, state, state, zip code, country individually. When we output it as a composite address, we will use the concatenation operator, to merge some of these columns according to required format. Another similar case is the people's names defined with columns of first, middle, last names. Display the location ID, and its full address as one string. The format is ** street, city, state province, postal_code, country_id". Just list those locations they located in 'US' (country_id= 'us'). • Use table EMP for this Q4. Display the employees' empno, name (ename), salary (sal) and department ID for those whose immediate supervisor's name (ename) is Blake. You may refer to page 13 of Notelb_Advanced_SQL.docx to check the output. • Use table Employees and Departments for this Q5 List the department ID, department name and the number (how many) of employees that work in that department. The output will be sorted in order of number of employees in ascending order. Exclude those departments that have no employee yet. Use table Locations and Departments for this Q6 List the department ID, department name. Address (as in question 3. you may re-use some of the code from Q3) for those departments that have a department head assigned (manager_id is not null). • Use table Employees for this Q7 List the employee ID, last name, first name and salary for those whose last name is not unique among the employees. Use table Employees and Departments for this Q8 List the employee ID. last name, first name, their department ID, their immediate supervisor's ID (the manager ID in employees table) and their department head's ID. (the manager II in departments table) for those employees who work in department (ID) 60. and their immediate supervisor is not their department head/manager. Questions 9 and 10 are for PL/SQL coding. • Write an anonymous PL/SQL block. In the PL/SQL block, declare a variable called name, its data type uses anchored data type, same data type as the ename in the Emp table; also declare a variable named income, data type anchored as that of sal in Emp table. In the executable section, the program will assign the value of name as "JACKSON', salary as $1,234. Then use the DBMS OUTPUT.PUT_LINE (" ¹) command to print out the values of that name and income. Please use the function TO CHAR (income, ¹$99,999') to make the output of salary as popular money format like $1,234. • Write an anonymous PL/SQL block with a nested (inner) block. In the out block, declare a variable called "1stname" using varchar2 (25) as its data 1. f 11-11... ------- Write an anonymous PL/SQL block with a nested (inner) block. In the out block, declare a variable called "Istname" using varchar2 (25) as its data type with initial value of 'Jackson". You need to label the outer block, give it a name. In the inner block, declare two variables, one with the same name as "Istname" with data type of varchar2 (25). and assign the initial value as 'Wabash': define the second variable as "lastname" with data type of varchar2 (25), initial value as 'George'. In the executable section of inner block. print out the values of the variable "Istname" declared in the outer block and the values of the variable "Ismame" and "lastname" declared in inner block. Some hints. Q1. Use "like" clause for pattern match, such as below car Where first name like 'Alex*' ; -- this is case sensitive, Where upper (first_name) like 'ALEX Q2. Use the function: upper (column_name) 'string of upper case letters'; Q3. Use concatenation operation to combine the columns: SELECT .... street_address ||'||city ||', '||-- Q4. A subquery might make it easier in logic, such as WHERE mgr = (select empno from omp where ... AS Address What should be included in the file (see sample below): • Your code for this question, and • The output of that question (just the text, no screenshot); right after its code. Such as below: REM Q1. SELECT employee_id, Last_name, First_name, manager_id FROM Employees Where upper (first_name) like "ALEXS'; OUTPUT EMPLOYEE ID LAST NAME 103 Hunold REM Q2. code for question #2 OUTPUT of Q2. FIRST NAME Alexander MANAGER ID To begin with this homework assignment #1, please build the Oracle sample tables. Consider that some students may have different backgrounds in SQL, some hints are provided at the end of this file. 102 Questions #1 to #8 require the SQL select statements, carefully pay attention which tables are based for each question table: All the five tables Employees, Departments, Locations, Dept, EMP may be involved. • Use table Employees for this Q1. List the employee_id, last name, first name, manager (immediate supervisor) ID for those employees that their first name starts with "Alex". Use table LOCATIONS for this 02. Use table LOCATIONS for this Q2. Sometimes, we do not know how the database stores the literals (either all upper. Initial Capital, or all lower cases). Use the function UPPER, we may make our query case insensitive. In this question, you are required to use this technique. Display the Location's location_id, street_address, postal code, city, country_id for those location(s) that city name spells as Seattle-but we do not know how the literal letters (upper case, lower, or...) stored in database. Use table LOCATIONS for this Q3. When we design tables, it is better to use small unit as a column, not same as in our daily language. For the example of ADDRESS, we usually will define the columns of street address, city, state, state, zip code, country individually. When we output it as a composite address, we will use the concatenation operator, to merge some of these columns according to required format. Another similar case is the people's names defined with columns of first, middle, last names. Display the location ID, and its full address as one string. The format is ** street, city, state province, postal_code, country_id". Just list those locations they located in 'US' (country_id= 'us'). • Use table EMP for this Q4. Display the employees' empno, name (ename), salary (sal) and department ID for those whose immediate supervisor's name (ename) is Blake. You may refer to page 13 of Notelb_Advanced_SQL.docx to check the output. • Use table Employees and Departments for this Q5 List the department ID, department name and the number (how many) of employees that work in that department. The output will be sorted in order of number of employees in ascending order. Exclude those departments that have no employee yet. Use table Locations and Departments for this Q6 List the department ID, department name. Address (as in question 3. you may re-use some of the code from Q3) for those departments that have a department head assigned (manager_id is not null). • Use table Employees for this Q7 List the employee ID, last name, first name and salary for those whose last name is not unique among the employees. Use table Employees and Departments for this Q8 List the employee ID. last name, first name, their department ID, their immediate supervisor's ID (the manager ID in employees table) and their department head's ID. (the manager II in departments table) for those employees who work in department (ID) 60. and their immediate supervisor is not their department head/manager. Questions 9 and 10 are for PL/SQL coding. • Write an anonymous PL/SQL block. In the PL/SQL block, declare a variable called name, its data type uses anchored data type, same data type as the ename in the Emp table; also declare a variable named income, data type anchored as that of sal in Emp table. In the executable section, the program will assign the value of name as "JACKSON', salary as $1,234. Then use the DBMS OUTPUT.PUT_LINE (" ¹) command to print out the values of that name and income. Please use the function TO CHAR (income, ¹$99,999') to make the output of salary as popular money format like $1,234. • Write an anonymous PL/SQL block with a nested (inner) block. In the out block, declare a variable called "1stname" using varchar2 (25) as its data 1. f 11-11... ------- Write an anonymous PL/SQL block with a nested (inner) block. In the out block, declare a variable called "Istname" using varchar2 (25) as its data type with initial value of 'Jackson". You need to label the outer block, give it a name. In the inner block, declare two variables, one with the same name as "Istname" with data type of varchar2 (25). and assign the initial value as 'Wabash': define the second variable as "lastname" with data type of varchar2 (25), initial value as 'George'. In the executable section of inner block. print out the values of the variable "Istname" declared in the outer block and the values of the variable "Ismame" and "lastname" declared in inner block. Some hints. Q1. Use "like" clause for pattern match, such as below car Where first name like 'Alex*' ; -- this is case sensitive, Where upper (first_name) like 'ALEX Q2. Use the function: upper (column_name) 'string of upper case letters'; Q3. Use concatenation operation to combine the columns: SELECT .... street_address ||'||city ||', '||-- Q4. A subquery might make it easier in logic, such as WHERE mgr = (select empno from omp where ... AS Address
Expert Answer:
Answer rating: 100% (QA)
The question seems to relate to writing SQL queries for a given database schema that includes tables such as Employees Departments Locations etc Unfor... View the full 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 databases questions
-
1. Calculate how long it would take your current personal or family savings to double at the current interest rate you are being offered by your bank. What will it take for you or your family to...
-
THIRD AVENUE SOFTWARE HEALTH-CARE APP PROJECT This case is new for the ninth edition of Information Technology Project Management . The case provides an opportunity to apply agile and Scrum...
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Find all conjugates in C of the given number over the given field. 1+2 over Q
-
A certain species of European mite is capable of damaging the bark on orange trees. The following are the results of inspections done on one hundred saplings chosen at random from a large orchard....
-
It is asked if a football player can be too smart. There is minor support to the answer yes. There is an intriguing contention that too smart players can improvise on the field and can be too...
-
Buying bonds with duration equal to when the proceeds are needed (matching duration with the need for funds) __________ reinvestment rate risk.
-
A penny of mass 3.10 g rests on a small 20.0-g block supported by a spinning disk (Fig. P6.60). The coefficients of friction between block and disk are 0.750 (static) and 0.640 (kinetic) while those...
-
Problem 5-Liabilities At the financial statement date of December 31, 2014, the liabilities outstanding of the Hayes Corporation included the following: 1. Cash dividends on common stock, $55.000,...
-
Compare and contrast the leadership styles exhibited by Steve Jobs and Tim Cook.
-
Please show your work (written out). Determine the car's speed using the table shown: Time (s) Displacement (m) 0 0.0 0.61 0.25 1.24 0.50 1.85 0.75 2.49 1.00 3.12 1.25 3.73 1.50 4.35 1.75 4.94 2.00
-
Sample for a Poll There are 30,488,983 Californians aged 18 or older. If The Gallup organization randomly selects 1068 adults without replacement, are the selections independent or dependent? If the...
-
Part A: You have successfully graduated Conestoga College and have joined a public accounting firm in their tax department. You have been assigned to work on a project with Emily Wilson, one of the...
-
Write a program that gets a list of integers from input, and outputs negative integers in descending order (highest to lowest). Ex: If the input is: 10 -7 4-39 -6 12 -2 the output is: -2-6-7-39 For...
-
The manager of a division that produces add-on products for the automobile industry had just been presented the opportunity to invest in two independent projects. The first is an air conditioner for...
-
4. We are interested in the effect on test scores of the student-teacher ratio (STR). The following regression results have been obtained using the California data set. All the regressions used...
-
Question 35 2 pts Which of the following attributes would typically be included in the table that stores data about the Customer entity in a relational database? Order date Credit limit Product code...
-
How is use of the word consistent helpful in fraud reports?
-
If Charles, a 16-year-old child model, earns $50,000 a year and is completely self supporting even though he lives with his parents, can his parents claim him as a dependent? Why or why not?...
-
During 2012, Palo Fiero purchases the following property for use in his manufacturing business: Palo uses the accelerated depreciation method under MACRS, if available, and does not make the election...
-
Skyler is covered by his company's health insurance plan. The health insurance costs his company $3,500 a year. During the year, Skyler is diagnosed with a serious illness and the health insurance...
-
14. Assume in problem 12 that the city reports the work as a capital asset. Which ofthe following is true? a. Depreciation is not recorded because the city has no cost. b. Depreciation is not...
-
7. Which of the following is true for the Year 2 government-wide financial statements? a. Both expense and liability will be zero. b. Both expense and liability will be $110,000. c. Expense will be...
-
10. What liability should the city report on government-wide financial statements at the end ofYear 1? a. It depends on whether the employees work at governmental activities or business-type...
![Mobile App Logo](https://dsd5zvtm8ll6.cloudfront.net/includes/images/mobile/finalLogo.png)
Study smarter with the SolutionInn App