What should be included in the file (see sample below): Your code for this question,...

   

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

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