T-SQL procedure A: obtain the name and credit limit of the customer whose number currently is stored in I_CUSTOMER_NUM. Place these valuse in the variables I_CUSTOMER_NAME and I_CREDIT_LIMIT, respectively. Output the content of I_CUSTOMER_NAME and I_CREDIT_LIMIT. B: obtain the order date, customer number and name for the order whose number currently is stored in I_ORDER_NUM. place these values in the variables I_ORDER_DATE, I_CUSTOMER_NUM and I_CUSTOMER_NAME , respectively. output the contents of I_ORDER_DATE, I_CUSTOMER_NUM , and I_CUSTOMER_NAME.

T-SQL procedure A: obtain the name and credit limit of the customer whose number currently is stored in I_CUSTOMER_NUM. Place these valuse in the variables I_CUSTOMER_NAME and I_CREDIT_LIMIT, respectively. Output the content of I_CUSTOMER_NAME and I_CREDIT_LIMIT. B: obtain the order date, customer number and name for the order whose number currently is stored in I_ORDER_NUM. place these values in the variables I_ORDER_DATE, I_CUSTOMER_NUM and I_CUSTOMER_NAME , respectively. output the contents of I_ORDER_DATE, I_CUSTOMER_NUM , and I_CUSTOMER_NAME.

Oracle 12c: SQL

3rd Edition

ISBN:9781305251038

Author:Joan Casteel

Publisher:Joan Casteel

Chapter12: Subqueries And Merge Statements

Section: Chapter Questions

Problem 19MC

See similar textbooks

Similar questions

What is a NULL value?
SQL Database Write PL/SQL or T-SQL procedures to accomplish the following tasks: Obtain the first name and last name, separated by a space, of the guide whose number currently is stored in I_GUIDE_NUM. Place these values in the variables I_FIRST_NAME and I_LAST_NAME. Output the contents of I_GUIDE_NUM, I_FIRST_NAME, and I_ LAST_NAME. Obtain the number of persons, customer number, and customer last name for every reservation whose ID currently is stored in I_RESERVATION_ID. Place these values in the variables I_NUM_PERSONS, I_CUSTOMER_NUM, and I_LAST_NAME, respectively. Output the contents of I_NUM_PERSONS, I_CUSTOMER_NUM, and I_LAST_NAME. Add a row to the GUIDE table. Change the last name of the guide whose number is stored in I_GUIDE_NUM to the value currently found in I_LAST_NAME. Delete the guide whose number is stored in I_GUIDE_NUM
T-SQL procedure SQL SERVER ONLY A: obtain the name and credit limit of the customer whose number currently is stored in I_CUSTOMER_NUM. Place these valuse in the variables I_CUSTOMER_NAME and I_CREDIT_LIMIT, respectively. Output the content of I_CUSTOMER_NAME and I_CREDIT_LIMIT.
Write PL/SQL or T-SQL procedures to accomplish the following tasks: A: obtain the name and credit limit of the customer whose number currently is stored in I_CUSTOMER_NUM. Place these valuse in the variables I_CUSTOMER_NAME and I_CREDIT_LIMIT, respectively. Output the content of I_CUSTOMER_NAME and I_CREDIT_LIMIT. B: obtain the order date, customer number and name for the order whose number currently is stored in I_ORDER_NUM. place these values in the variables I_ORDER_DATE, I_CUSTOMER_NUM and I_CUSTOMER_NAME , respectively. output the contents of I_ORDER_DATE, I_CUSTOMER_NUM , and I_CUSTOMER_NAME. C: add a row to the ORDERS table. D: change the date of the whole number is stored in I_ORDER_DATE to the date currently found in I_ORDER_DATE. E: delete the order whose number is stored in I_ORDER_NUM.
Create a function name BALANCE that returns the status of outstanding amount for each customer. Use the following criteria for your function: Outstanding_amt less than 5000 , returns ‘SMALL AMOUNT’ Outstanding_amt between 5000 and 10000 , returns ‘AVERAGE AMOUNT’ Outstanding_amt greater than 10000 , returns ‘LARGE AMOUNT’ Use function BALANCE in your SQL statements that display the cust_code, cust_name, Outstanding_amt and function BALANCE.
T-SQL procedure for MICROSOFT SQL SERVER A: obtain the name and credit limit of the customer whose number currently is stored in I_CUSTOMER_NUM. Place these values in the variables I_CUSTOMER_NAME and I_CREDIT_LIMIT, respectively. Output the content of I_CUSTOMER_NAME and I_CREDIT_LIMIT. HINT use cursor instructions as a template for the problem. Instructions goes as follows CREATE PROCEDURE usp_DISP_REP_CUST @repnum char(2) AS DECLARE@custnum char(3) DECLARE@custname char(35) DECLARE mycursor CURSOR READ_ONLY FOR SELECT CUSTOMER_NUM, CUSTOMER_NAME FROM CUSTOMER WHERE REP_NUM = @repnum OPEN mycursor FETCH NEXT FROM mycursor INTO @custnum, @custname WHILE @@FETCH_STATUS=0 BEGIN PRINT@custnum+' '+@custname FETCH NEXT FROM mycursor INTO @custnum, @custname END CLOSE mycursor DEALLOCATE mycursor
USE NORTHWIND DATABASE Write a SELECT statement using the CASE function. The statement returns two columns basedon how many orders the customer has in the database. The first column is the customer name andthe second column, Membership Type, is based on which case applies to the customer:If the customer has more than 25 orders they are a Diamond Customer.If the customer has more than 15 orders they are a Gold Customer.If the customer has more than 10 orders they are a Silver Customer.If the customer has more than 5 orders they are an Iron Customer.Otherwise the customer is a Valued Customer.
SBN Title Author 12345678 The Hobbit J.R.R. Tolkien 45678912 DaVinci Code Dan Brown Your student ID DBS311 Your Name use the following statement to Write a PL/SQL Function that accepts 4 parameters, 2 strings representing students names, and 2 integers representing the marks they received in DBS311. The function will determine which student had the higher mark and return the name of the student. If the marks were the same, then return the word "same" and return "error" if an error occurs or the calculation can not be determined.
Instructor (Instructor_Id, Instructor_Name, specialization, Address, #Department_Id)Write in PL/SQL a function named NumberInstructor_department that takes the id of a department as an input parameter and returns the number of instructors who are working in that department.
Write SQL code for the following: Create a stored procedure “setRelocationFee” to set the relocation fee for a givenemployee. If the employee’s office is in San Francisco, the relocation fee is $10000; ifthe employee’s office is in Boston, the relocation fee is $8000; if the employee’s officeis in London, the relocation fee is $20000; if the employee works in other offices, therelocation fee is $15000. (see image for table structure) I am stuck for the part that I have to select employeeNumber and officeCode and set the relocationFee to be able to obtain the result but I do not know how. Bellow is what I have gotten. Delimiter| CREATE PROCEDURE setRealocationFee(IN EmployeeID INT(11), OUT realocationFee INT(5))
Write three functions in SQL which does the following: The first function should take an integer and convert it into a hexadecimal number. It could also take a hexadecimal number and convert it into an integer. The second function should take an integer and convert it into an octal number. It could also take an octal number and convert it to an integer. The third function should take an integer and convert it into a binary number. It should then take the binary number and convert it into an integer.
The following figure is part of a university database. LNAME and FNAME stands for last name and first name, respectively. Both CNUM and CNO stand for course number. CNAME stands for course name. Specify the following queries in both relational algebra expression and SQL state- ments: 1) List the names and majors of all students who live in the city of Fullerton. 2) List the course number (CNUM), course name (CNAME) and number of units (UNITS) of each course that the student "Mary Willson" has enrolled. 3) For the course number "CPSC332", list the names of the studens who enrolled in the course. 4) For the students who live in in the city of either Fullerton or Brea, count the number of students in each city. 5) List the names of all students who live in Fullerton and do not enroll in any courses. STUDENT SSN FNAME LNAME DBIRTH ADRRESS CITY MAJOR ENROLL SNO CNO GRADE COURSE CNUM CNAME ТЕXTBOOK UNITS DEPARTMENT