1. Fill the blanks with the suitable PL/SQL statements that correct each of the errors that appear in the given PL/SQL program. DECLARE CURSOR cursorl IS SELECT * FROM Students; line cursorl %type; | BEGIN OPEN cursor1; LOOP SELECT cursorl inside line; I DBMS_OUTPUT PUT_LINE(frst name= "| line. finame || last name="line. Iname); EXIT WHEN Cursor %FOUND; END LOOP; CLOSE cursor1; END;

1. Fill the blanks with the suitable PL/SQL statements that correct each of the errors that appear in the given PL/SQL program. DECLARE CURSOR cursorl IS SELECT * FROM Students; line cursorl %type; | BEGIN OPEN cursor1; LOOP SELECT cursorl inside line; I DBMS_OUTPUT PUT_LINE(frst name= "| line. finame || last name="line. Iname); EXIT WHEN Cursor %FOUND; END LOOP; CLOSE cursor1; END;

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

how to filter based on a parameter in a sql function ?' I have the following sql function: CREATE FUNCTION Function.FunctionExample { @name UNIQUEIDENTIFIER = NULL, @date DATETIME = NULL, @seconddate DATETIME = NULL } RETURNS @returnTable TABLE { Code INT, Date DATETIME, SecondDate DATETIME } AS BEGIN INSERT INTO @returnTable { Code, Date, SecondDate } SELECT Date1 AS Date, Date1 AS SecondDate FROM Function.FunctionExample WHERE (@Date IS NULL OR CONVERT(DATE, FunctionExample.Date1) >= @Date) AND (@SecondDate IS NULL OR CONVERT(DATE, FunctionExample.Date1) >= @SecondDate) RETURN END What would I have to do If I wanted to filter based on the @name parameter ?
Develop a PL/SQL block that would go through all the employees from EMPLOYEES table who work in department id= 90 (there is suppose to be 3 employee who work in Department 90)- IN BEGIN END END SECTION; Execute 3 separate of SELECT statement one after the other. Each SELECT will pull first name. salary of each employee and store them in local variables.+ SELECT LOAD INTO v_firstname1, v_salarxlWHERE emrlexee.id=100 Then second WHERE emploxee.id3101+ SELECT first_name, salary INTO v_firstname2, v_salary2 Then third + SELECTSELECTI tirst.name, salary INTO v_firstname3, v_salary3 iin WHERE emploxee id=102 ..... You must now have 6 local variables. DECLARE all of them accordingly in DECLARE section and use them in BEGIN and END.+ Then add another SELECT statement in BEGIN-END to find company average salary SELECT xa(salary) INTO aKasal FROM EMPLOYEES; !!! Do not forget to create y avgsal local variable in DECLARE section.
QUESTION 2. Write a PL/SQL PROCEDURE that can list the Top-N customers (id and name) and the amounts spent in a specified year together with their rank. The customer rank can be obtained from the function created in the previous question. The total purchase is calculated for each customer using the quantity and price columns. The total purchased is sorted from the highest to the lowest. The procedure will have two input parameters: year (NUMBER) and TOP_N (NUMBER). Hint: • use ONE EXPLICIT CURSOR (corresponding to one select statement). • Us the topN technique (from clause subquery) and rownum to limit the customers. Expected Output for Top 3 customers in 2018 using: execute topSpenders (2018. 3) 3 Gary Jenkins s422.64 Excellent 45 Fred Fontain $359.21 Excellent 16 Ralph Foster $351.64 Excellent Expected Output for Top 10 customers in 2018 using: begin topSpenders(2018,10 end: 3 Gary Jenkins s422.64 Excellent 45 Fred Fontain $359.21 Excellent 16 Ralph Foster $351.64 Excellent 81…
Question 13 Given the following relation: Movie (MovielID, Title, Genre, Year) Write a PL'SQL block of code that performs the following tasks: i. Read a movie id provided by user at run time and store it in a variable. i. Fetch the Genre and title of the movie having the entered id and store them inside two variables i. Display the fetched movie Genre and title. T T T T Paragraph v 3 (12pt) Arial T T O f. O Mashups HTHL CSS
Fill the blanks with the suitable PL/SQL statements that correct each of the errors that appear in the given PL/SQL program. DECLARE CURSOR cursor1 IS SELECT * FROM Students; line cursorl %type: BEGIN OPEN cursor1; LOOP SELECT cursor1 inside line; DBMS_OUTPUT.PUT_LINE('first name= "|| line.fname || 'last name= '||line.Iname); EXIT WHEN Cursor %FOUND; END LOOP: CLOSE cursor1; END;
Student(Id, name, age, class, grade) Write SQL server query to retrieve all the student in class 6
*Develop a SQL Query Display all the resorts names whose star-rating ranges between 4 and 5. Sort the result set in the ascending order of star rating. RESORT MANAGEMENT SYSTEM Town Pointofinterest Manager PK townID Number managerID Number PK pointID Number PK townname Varchar(30) varchar(30) varchar(30) describe Varchar(30) name state varchar(30) address opentime varchar(10) %23 longitude varchar(30) closetime varchar(10) city varchar(20) varchar(30) Number latitude phone varchar(15) HAS FK townID Number summertemp wintertemp Number sealevel Number GIVES- Resort Guest Review PK resortiD Number guestID Number PK FK,PK guestiD Number resortName varchar(30) name varchar(30) FK,PK resortID Number street varchar(30) MANADES address varchar(30) + varchar(20) dateofreview Date townname varchar(30) BOOKS country varchar(30) email varchar(150) FK townid Number comments phone varchar(15) starRating Number FK managerID Number Booking HAS PK bookingiD Number CabinCost HAS FK guestid Number HAS…
If you write PL/SQL program contains a cursor; which of the following statements can be written inside the loop? Nots: You may have many answers. (2 Points) *: open x; close x exit when x%notfound; fetch x into rec13B x integer:=673; rec1 employees%rowtype;
QUESTION 12 Fill the blanks with the suitable PL/SQL statements that correct each of the errors that appear in the given PL/SQL program declare employeeRecord employee%TYPE: ename varchar2; begin fetch ename, salary inside employeeRecord from employee where enum= 'ES: DBMS OUTPUT.PUT_LINE(employee.ename" | employee.Salary): end:
Need help writing SQL statements for these, I'm having trouble with my returns. Explanation would help a lot.
What happens if we don’t specify the WHERE clause in an SQL DELETE FROM statement? a) statement compilation causes a “Syntax error”, and the statement is not executed. b) all the tuples of the relaton are deleted. c) none of the tuples of the relation is deleted. d) all the tuples not satisfying the condition are deleted. e) none of the above. Which one(s) of the following is(are) procedural? a) Relational Algebra b) Relational Calculus c) Tuple Relational Calculus d) Domain Relational Calculus e) SQL f) (a) and (e) e) (b) and (e).
This assignment is aimed at building an understanding of how to improve performance within databases by taking advantage the computers hardware by modifying MS SQL settings Prerequisites: Completion of Assignment 1B, reading of Module Readings and Slides. Run the following query to understand how much Memory is currently in use in the database: SELECT physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB, large_page_allocations_kb/1024 AS sql_large_page_allocations_MB, locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB, virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB, virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB, virtual_address_space_available_kb/1024 AS sql_VAS_available_MB, page_fault_count AS sql_page_fault_count, memory_utilization_percentage AS sql_memory_utilization_percentage, process_physical_memory_low AS sql_process_physical_memory_low, process_virtual_memory_low AS…