Oracle SQL and PL/SQL

If anything is not clear from the question, please include a SQL comment describing any assumptions you made to answer the question in a reasonable fashion. Instructions You have 24 hours to submit your responses to this coding test; however, we expect that this test can be completed in around two hours. Please submit your response as soon as it is complete. 1. Create a SQL sсrіpt whose name follows the following format: lastName_firstName.sql (e.g., smith_john.sql). 2. Provide well-commented answers to all of the following questions in that that SQL sсrіpt, using the format described on the next page. 3. Submit your responses as an attachment on a reply to the email you received with this document. Your responses should be valid Oracle 11gR2 or 12c SQL and PL/SQL. Oracle provides a free “Express Edition” of its RDBMS should you wish to install it. Some of these problems can alternatively be tested using SQL Fiddle.   Response format example — Question #1 select 12345 as someNum from dual; — Question #2 declare mCount pls_integer; begin — we need to determine how many rows are in dual select count(*) into mCount from dual; end; /   Reference schema for Questions 1-5 Employee Column Type Nullable? Keys employeeId number(9) not null PK name varchar2(100) null (unique) salary number(9) not null Org Column Type Nullable? Keys orgId number(9) not null PK name varchar2(100) not null (unique) managerId number(9) not null FK to Employee(employeeId) EmployeeOrg (An employee can belong to any nonnegative number of orgs.) Column Type Nullable? Keys employeeId number(9) not null Composite PK FK to Employee(employeeId) orgId number(9) not null Composite PK FK to Org(orgId)   Questions Question #1 Write a SQL query that lists all employees by name only in ascending alphabetical order. Question #2 Write a SQL query that selects the maximum salary out of all org managers. Question #3 Write a SQL query that lists all orgs by name (in ascending alphabetical order) along with the maximum salary across all employees who belong to the org. Question #4 Write a SQL query that lists the “top 10” orgs by name in ascending alphabetical order. (Assume that there are more than 10 orgs.) For example, if there are orgs A, B, …, Z, the query should print the names of A through J in alphabetical order. Question #5 Write a SQL query that lists all employees by name in ascending alphabetical order. If the employee is a manager of at least one organization, provide a row in the results with the name of each org he/she manages in ascending alphabetical order. If the employee is not the manager of any organization, then this column should have a null value for that employee, and the employee should be returned in only one row. Proceed to next page.   Question #6 Create a table called FizzBuzz to hold the results of your FizzBuzz solution (detailed below). It should have the following desсrіption: Column Type Nullable? NUM number(3) not null VAL VARCHAR2(8 CHAR) not null Write a PL/SQL block that populates FizzBuzz with exactly 100 rows. There should be one row for each integer between 1 and 100 (inclusive) – and that integer should be stored in the NUM column. VAL should be: • Fizz if NUM is a multiple of three. • Buzz if NUM is a multiple of five. • FizzBuzz if NUM is a multiple of both three and five. • NUM (as a string) otherwise. Your answer should include the following: • Statement used to create the FizzBuzz table. • Include the PL/SQL block used to populate the FizzBuzz table. • Select statement used to print out all rows of FizzBuzz order by NUM ascending. • Include the results of the above select statement. Question #7 Fix the syntax and logic errors in the PL/SQL block below and briefly summarize the errors that are contained in the block of code. The PL/SQL code is intended to perform a loop 10 times and display the counter for each iteration of the loop. set serveroutput on declare — PL/SQL should display counter for a loop — Loop should iterate 10 times — by: The Candidate (please replace “The Candidate” with your name!) — date: 09-APR-2018 (please replace with the current date!) mCounter pls_integer:＝1; mMessage varchar2(5); begin while mCounter ˂＝ 10 loop mMessage :＝ ′Counter is equal to ′|| to_char(mCounter); dbms_output.put_line(mMessage) end loop; end; / set serveroutput off

 

“Get 20% OFF on a Similar Assignment!! Place Your Order and Use this Coupon Code: SUPER20”

---

Oracle SQL and PL/SQL was first posted on March 27, 2020 at 6:28 pm.
©2020 “Buy Custom Essays“. Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement. Please contact me at ukbestwriting@gmail.com