1. Create an PL/SQL anonymous block. Using a “while” loop, display on the console those rows where a quantity of shipments for a project are greater than the average quantity of shipments for that project.
2. Repeat using a FOR loop.
// TABLE INFO : SHIPMENTS(SUPPLIERNO, PARTNO, PROJECTNO, QUANTITY, SHIPDATE, ARRIVEDATE)
Using While
DECLARE
s_sno shipments.supplierno%type;
s_pno shipments.partno%type;
s_projectno shipments.projectno%type;
s_quantity shipments.quantity%type;
s_shipdate shipments.shipdate%type;
s_arriveddate shipments.arriveddate%type;
CURSOR t1cursor IS
SELECT supplierno,partno,projectno,quantity,shipdate,arriveddate
FROM shipments
WHERE quantity>10000;
BEGIN
OPEN t1cursor;
WHILE t1cursor%FOUND LOOP
FETCH t1cursor INTO s_sno,s_pno,s_projectno,s_quantity,s_shipdate,s_arriveddate;
dbms_output.putline(s_sno || s_pno || s_projectno || s_quantity || s_shipdate || s_arriveddate);
ENDLOOP;
END;
Using FOR
DECLARE
s_sno shipments.supplierno%type;
s_pno shipments.partno%type;
s_projectno shipments.projectno%type;
s_quantity shipments.quantity%type;
s_shipdate shipments.shipdate%type;
s_arriveddate shipments.arriveddate%type;
CURSOR t1cursor IS
SELECT supplierno,partno,projectno,quantity,shipdate,arriveddate
FROM shipments
WHERE quantity>10000;
recordpresent shipmnents.quantity%type;
BEGIN
OPEN t1cursor;
FOR recordpresent IN t1cursor LOOP
FETCH t1cursor INTO s_sno,s_pno,s_projectno,s_quantity,s_shipdate,s_arriveddate;
dbms_output.putline(s_sno || s_pno || s_projectno || s_quantity || s_shipdate || s_arriveddate);
ENDLOOP;
END;
1. Create an PL/SQL anonymous block. Using a “while” loop, display on the console those rows where a quantity of shipmen...
1- Create table student5 as:
2- Write PL/SQL program using cursor to display student number and names who's their salary greater than 2500?
3- Update the table students to increase the salary of each customer by 300?
PL/SQL Auction Program 1. Create a user xyz, who is the owner of the auction. Create the schema, and package. 2. Create users x1 and x2 who are the participants in the auction. They will need acces to the package. 3. Bid on the same item and record your observations. Verify all scenarios. Upload the files with the missing code and a detailed sample run. AUCTION OWNER.TXT SQL> conn / as sysdba Connected. SQL> drop user xyz cascade; User dropped....
1. Create a PL/SQL program block that determines the top students with respect to GPA. Assume that the database has four tables. Student(SSN, SName, DOB, Major) , Grade(SSN, CNo, Grade(0,1,2,3,4)) and Course table(CNo,CName, Credit Hour), Prerequisite(CNo, PreCNo); Student and couse data ae given in the following SQL statements a. Accept a number n as user input with SQL*Plus telling top n%. b. In a loop get the SName and GPA of the top n% people with respect to GPA. c....
#1) Display Last Name, First Name, DonorId, Fundname, TotalPledged from the donor’s database (you may need to use donor and pledge tables to get the information) for Donor ID 59034. (Your results window should have 3 rows) #2) Display all records from the PurchaseOrderHeader from the AdventureWorks Database that were sold by Employee 261 (Your results window should have 401 rows) #3) Display salesorderid, orderdate, totaldue, and territory name from salesorderheader and salesterritory for all totaldue that are greater than...
The purpose of this assignment is to get experience with an
array, do while loop and read and write file operations.
Your goal is to create a program that reads the exam.txt file
with 10 scores. After that, the user can select from a 4 choice
menu that handles the user’s choices as described in the details
below. The program should display the menu until the user selects
the menu option quit.
The project requirements:
It is an important part...
need help
Create or edit the SQL statements required to accomplish the following tasks. All columns should have a column name, feel free to create appropriate aliases Use screen shots to ca evidence that each task is completed; include the SQL records before and after statements are executed, as appropriate. Include all work, even if you get errors. Paste your screen shots into a single PowerPoint file pture statements output from the statements, and views of the object explorer when...
Labview question: Create a VI using a While Loop that continuously generates random numbers between 0 and 1000 until it generates a number that matches a number selected by the user. Determine how many random numbers the VI generated before the matching number. conditions to applied on above problem: 1. Convert the “random number” generator floating point numerical output to integer values to make it easier to find a match. 2. The “user selected number control” should be on the...
Exercise 3 [12.5 marks] Given the Restaurant management system database that contains the following tables (primary keys are underlined and foreign keys are preceded with #): Customer (customerID,customerFirstName,customerLastName,customerAddress) Oreder (orderID,orderDate, #customerID,#menuItemID,#staffID) MenuItem(menuItemID, menuItemName,ingredients,type,availability) Staff(staffID, staffName, staffPhoneNumber, staffRole ) OrderPayment(paymentID,paymentAmount,#orderID,#staffID) 1) Without using DISTINCT, write the SQL query equivalent to the following one:[1.5 marks] SELECT DISTINCT menuItemName FROM MenuItem WHERE type = ‘Vegetarian’ OR availability= ‘Yes’; 2) Express the following queries in SQL: a) Find the number of orders placed by...
in c++ language 1.Re-write the following for loop statement by using a while loop statement: int sum = 0; for(int i=0;i<=1000;i++){ sum+=i; } 1 (b). Following is the main () function of a program. The program asks a user to enter 150 integers and print the largest integer user entered. int main() { int score, highest; //missing portion of the program return 0; } 1(c). Find the missing portion of the following code, so the...
SQL QUERIES
1) Using the Product and PC relations, create both tables in
your database and insert all the data. Show the SQL statements to
create each table and show a representative SQL insert statement
for the data in each table (i.e. you do not need to show insert
statements for all the data).
– For the remaining questions, assume that your SQL is part of
a program function and the function provides the input needed for
your SQL query....