2. Write a script that creates and calls a stored procedure named invoice_balance. This stored procedure should use two variables to store (1) the count of all of the invoices in the Invoices table that have a balance due and (2) the sum of the balances due for all of those invoices. If that total balance due is greater than or equal to $30,000, the stored procedure should display a result set that displays the values of both variables. Otherwise, the procedure should display a result set that displays a message like this: Total balance due is less than $30,000.
Q: Name Null? Type --------- ----- ------------ GIFT VARCHAR2(15)…
A: The PRIMARY KEY constraint means that each record in a table is unique. This means that primary keys…
Q: Which of the following will select the Id and Status fields for records whose Status field contains…
A: The attribute which are to be present in output should be present in SELECT clause. The condition…
Q: Task 3: Create(Enter) new product by using a Procedure with IN Parameters Follow these steps to…
A: the answewr is :2. a. --> CREATE PROCEDURE PROD_ADD_SP ( product_name IN VARCHAR2 , description…
Q: please use store procedures to answer the following question Create a procedure to update Job Title…
A: CREATE TABLE job(p_job_id varchar(2) primary key,p_new_job_title varchar(20)); /* Create few records…
Q: The HR department wants you to create SQL statements to insert, update, and delete employee data. As…
A: DML statements are used to insert, update or delete data on tables. Syntax for insert statement:-…
Q: can tell how the restriction on ‘cSalary’ is violated. Whenever the value of ‘cSalary’ is beyond the…
A: Given : Create table Cleaner(cNo varchar2(5), cName varchar2(20), cSalary number(6,2), dNo…
Q: Create a stored procedure named sp_InsertAthleteDetails, which enters details into the Athletics…
A: Here have to determine stored procedure for giving problem statement.
Q: Write a script that creates and calls a stored procedure named spInsertInstructor that inserts a row…
A: Stored procedure : It can be used again and again whenever needs, also you can pass parameters to…
Q: In Kali, login to DVWA, set security level to low , then click SQL Injection on left pane .Write…
A: 1. find all database names %' or 0=0 union select null, user() # 2. find the current database name…
Q: 1. Create the GET_INVOICE_DATE procedure to obtain the customer ID, first and last names of the…
A: A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over…
Q: 13. Which of the following column properties specifies whether or not cells in a column must contain…
A: Given that, 13. Which of the following column properties specifies whether or not cells in a column…
Q: What clause is used to perform intermediate processing that is not possible in RETURN clause? Enter…
A: Answer: EXECUTE
Q: Assume you need to create a table that contains a column called CreditLimit. Which of the following…
A: In the given question, queries have two types of options that includes: ADD(): In SQL, the command…
Q: Problem:5 Create a simple interface for Reminders that consists of Four buttons. The three buttons…
A: The design for all needs supplied with the question is shown below: Below find the Solution: Design:…
Q: Which one of the following constraint is used along with the DROP COLUMN clause? O a. DISABLE…
A: Answer: d. DROOP CONSTRAINTS
Q: Create a trigger named trg_pic_hours that automatically updates the PILOT table when a new CREW row…
A: Triggers are stored programs which are automatically fired when some event occur. Syntax for…
Q: You are creating a part of a spreadsheet that will automatically find customer information based on…
A: in below question we create a serch box to extract the data.…
Q: Create the GET_INVOICE_DATE procedure to obtain the customer ID, first and last names of the…
A: Thanks for the task CREATE TRIGGER UPDATE_COMMISSION_TRIGBEFORE UPDATEON CUSTOMER FOR EACH…
Q: Read the following text and choose the correct option: The IN clause before a nested SELECT requires…
A: oprion 2 is correct
Q: Create a trigger called Trig_Enroll_BI on the ENROLLMENT table that fires before an INSERT…
A: Solution:Create the trigger with name of “Trig_Enroll_BI” for ENROLLMENT table.The followings…
Q: ?When creating a table, which of the following statements is correct The schema of the table must be…
A: Answer : While creating a table the correct statement is : a column list must be enclosed in…
Q: Create a function to calculate a shopper’s total spending, excluding shipping and tax amount, with…
A: Given requirement, Create a function to calculate a shopper’s total spending, excluding shipping and…
Q: Create a trigger named tInstructors_UPDATE that checks the new value for the AnnualSalary column of…
A:
Q: In this Assignment, you will build a supermarket product management app that persists da offline.…
A: It is defined as programs can be asked from control statements, array, string, oops etc. Java basic…
Q: I am working on Task 4. Task 4: Create the DISPLAY_OWNER procedure which obtains the first name and…
A: A procedure is an SQL code that can be used over again and again. It has a syntax as: CREATE…
Q: You do not have the Store schema tables installed in your schema. The Store schema tables are…
A: Solution :: 1A. Result - -> SELECT c.country_name, r.region_name FROM storedb.Country as c,…
Q: 13. The SELECT command below should retrieve productid, productname, and productprice increased by…
A: Here the table name is product and the attribute it has are Product ID, productname,productprice
Q: Task 9: Create the DISP_PROPERTYS procedure to retrieve and output the office number, address,…
A: Given Procedure name is DISP_PROPERTYS Suppose the relation name is PROPERTY and the attribute…
Q: On the Loan worksheet, in cell C9, enter a PMT function to calculate the monthly payment for the…
A: See step 2 for formula
Q: Problem 31 Create a trigger named trg_line_total to write the LINE_TOTAL value in the LINE table…
A: Explanation: The below query is used to create a trigger named “TRG_LINE_TOTAL” to add the product…
Q: Question 3 Takes(studentld, courseCode, mark) Course(courseCode, title, credits,deptno)…
A:
Q: Task 5: Create the GET_CREDIT_LIMIT procedure to obtain the full name and credit limit of the…
A: Given : Task 5: Create the GET_CREDIT_LIMIT procedure to obtain the full name and credit limit of…
Q: Create a function to calculate a shopper’s total spending, excluding shipping and tax amount, with…
A: Introduction Create a function to calculate a shopper’s total spending, excluding shipping and tax…
Q: The given commad ALTER TABLE test MODIFY id NOT NULL; The command will always be executed…
A: ALTER TABLE command is used to add, delete or modify columns in an existing table. The basic syntax…
Q: procedure that accepts a country_id as a parameter and displays the name of the country and its…
A: It is defined as a Structured Query Language which is a computer language for storing, manipulating…
Q: please use sql to answer the following question an erd has been provide Task 1: Creating a Logon…
A: The home page of the Brewbean’s Web site has an option for members to log on with their IDs and…
Q: Create a stored procedure named sp_InsertAthleteDetails, which enters details into the Athletics…
A: CREATE PROCEDURE sp_InsertAthleteDetails @AthleteID INT,@First_name VARCHAR(40),@Last_name…
Q: Create(Enter) new product by using a Procedure with IN Parameters Follow these steps to create a…
A: It is defined as a collection of information that is organized so that it can be easily accessed,…
Q: Create a trigger named trg_cust_charge that will automatically update the CUSTOMER table’s…
A: ANSWER:-
Q: Create a trigger named trg_line_total to write the LINE_TOTAL value in the LINE table every time you…
A: Triggers are stored programs which are automatically fired when some event occur. Syntax : CREATE…
Q: 2. Complete the stored procedure below. The procedure will be used for deleting a row from the PART…
A: Find the required procedure given as below :
Q: Create a trigger that will prefix the values of item category with ‘dev-‘ when a new row ins…
A: Actually, SQL is a structured query language which is used to insert, delete ,modify and delete the…
Q: Create a procedure named DDCKPAY_SP that confirms whether a monthly pledge payment is the correct…
A: Create a procedure named DDCKPAY_SP that confirms whether a monthly pledge payment is the correct…
Q: Create an anonymous block which will convert a number grade to a letter grade. Prompt for a number…
A: Please check the step 2 for solution.
Q: Create a stored procedure that will change the price of an item with a given item number. Test this…
A: Stored Procedure A stored procedure can be used along with SELECT, UPDATE, INSERT, and DELETE…
Q: Create a trigger named trg_line_total to write the LINE_TOTAL value in the LINE table every time…
A: Given : Create a trigger named trg_line_total to write the LINE_TOTAL value in the LINE tableevery…
Q: page - The combo box values are loaded from salutation database table The radio buttons values are…
A: Please check the step 2 & 3 for solution
Q: Create a trigger named trg_char_hours that automatically updates the AIRCRAFT table when a new…
A: TRIGGER: Following is the trigger definition which performs the above required task: CREATE OR…
Q: Produce a script file with 15 queries: At least two using join At least three using where clause At…
A: The JOIN clause is used to combine more than one different table based on the condition which is…
Q: Which of the following is a correct statement?a. If you attempt to add a record that violates a…
A: The correct statement is - a. If you attempt to add a record that violates a constraint for one of…
MYSQL
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
- The SERVICE_REQUEST table uses the CHAR data type for the DESCRIPTION and STATUS fields. Is there an alternate data type that could be used to store the values in these fields? Justify your reason for choosing an alternate data type or for leaving the data type as CHAR.Write a script that creates and calls a stored procedure named spInsertDepartment. First, code a statement that creates a procedure that adds a new row to the Departments table. To do that, this procedure should have one parameter for the department name. Code at least two EXEC statements that test this procedure. (Note that this table does not allow duplicate department names.)Write a script that creates and calls a stored procedure named spInsertInstructor that inserts a row into the Instructors table. This stored procedure should accept one parameter for each of these columns: LastName, FirstName, Status, DepartmentChairman, AnnualSalary, and DepartmentID. This stored procedure should set the DateAdded column to the current date. If the value for the AnnualSalary column is a negative number, the stored procedure should raise an error that indicates that this column doesn’t accept negative numbers. Code at least two EXEC statements that test this procedure.
- Write a script that creates and calls a stored procedure named spUpdateInstructor that updates the AnnualSalary column in the Instructors table. This procedure should have one parameter for the instructor ID and another for the annual salary. If the value for the AnnualSalary column is a negative number, the stored procedure should raise an error that indicates that the value for this column must be a positive number. Code at least two EXEC statements that test this procedure.9. Write a SELECT statement that uses aggregate window functions to calculate the total due for all vendors and the total due for each vendor. Return 3 Columns: The vendor id from the Invoices table. The balance due (invoice_total-payment_total-credit_total) for each invoice in the invoices table with a balance greater than 0. The total balance due for all vendors in the Invoices table. Modify the column that contains the balance due for each vendor so it contains a cumulative total by balance due."Create a stored procedure named spPriceRange that accepts two optional parameters. Theprocedure should return a result set consisting of Product Id, Product Name, ProductDescription, and List Price from the product and product description tables for each productwithin the price range, sorted with largest price first. The parameter @PriceMin and @PriceMaxare parameters used to specify the requested range of prices. If the minimum price is notprovided use the minimum non-zero price in the product table. If the maximum price is notprovided use the maximum price in the product table."
- The following code would include: CREATE OR REPLACE TRIGGER TRG_LINE_PROD AFTER INSERT ON LINE FOR EACH ROW BEGIN UPDATE PRODUCT SET P_QOH = P_QOH - :NEW.LINE_UNITS WHERE PRODUCT.P_CODE = LINE.P_CODE; END; Group of answer choices A) Create a trigger named trg_line_prod that will automatically increase the product quantity on hand for each product sold after a new LINE row is added. B) Create a trigger named trg_line_prod that will automatically update the product quantity on hand for each product sold after the LINE table is modified. C) Create a trigger named trg_line_prod that will automatically reduce the product quantity on hand for each product sold after a new LINE row is added. D) This query will not work as it has syntax errors.Produce a script file with 15 queries: At least two using join At least three using where clause At least three using order by At least two using single-row functions At least two using group by and group functions at least one nested query At least 4 queries from chapter 1 - 6 (exclude Create Table, Insert queries) ------- TABLE 1 create TABLE ProCoaches(pro_co_ID INTEGER PRIMARY KEY,pro_coachName Varchar(50) NOT NULL,pro_coachLocation Varchar(20)); INSERT INTO ProCoaches VALUES (1, 'John', 'Missoula'); INSERT INTO ProCoaches VALUES (2, 'Joe', 'Bozeman');INSERT INTO ProCoaches VALUES (3, 'Tara','Butte'); select * FROM ProCoaches;select LENGTH('Missoula') AS LengthOfLocation;select pro_coachName FROM ProCoaches where pro_coachLocation='Missoula'; select COUNT(pro_co_ID), pro_coachLocation FROM ProCoaches GROUP BY pro_coachLocation; TABLE 2 create TABLE ProCyclist(pro_cy_ID VARCHAR(10) PRIMARY KEY,pro_cyclist_Name VARCHAR(50) NOT NULL,pro_cyclist_Gender Varchar(20),pro_cyclist_DOB…Create a trigger called Trig_Enroll_BI on the ENROLLMENT table that fires before an INSERT statement. The trigger must generate a proper error message if the student id or section id or both invalid. Test your code for all possible options. Make sure that all columns that have NOT NULL values (Use system date for all date fields, user id for all created and modified by fields). Display the inserted record from the ENROLLMENT table after a successful enrollment transaction.
- If you are using Access 2016, do the following. Add a field to the Client table named AvailableCredit. Set the field to currency. Create a data macro associated with the After Update event for the Client table. When a user updates the Balance field, the macro should subtract the new balance from the credit limit and place that value in the AvailableCredit field. Create a data macro associated with the After Delete event for the OrderLine table. When a user deletes a record because it is complete or cancelled, the macro should look up the order number in the WorkOrders table and delete it there as well. Use the For Each Record in the WorkOrders table. Hint: You can use the OLD keyword in the Where Condition: [Old].[OrderNum] = [WorkOrders].[OrderNum]. Test the data macro by deleting a record from the OrderLine table and ensuring that the record also is deleted from the WorkOrders table.Write a SELECT statement returns these columns from the orders table: The invoice_id column as Invoice_ID The invoice_date column as Invoice_Date The invoice_due_date column as Invoice_Due_Date The payment_date column in the format 'Mon/DD/YY' as Formatted_Payment_Date The invoice_due_date column in the format 'Weekday, Month Day of month with suffix, YYYY' (ex: 'Tuesday, April 23rd, 2019') as Long_Invoice_Due_Date The invoice_due_date plus 60 days as Expected_Payment_Date (Column must remain a date type) The number of days between the invoice_due_date and the payment_date as Time_To_Payplease use store procedures to answer the following question Create a procedure to update Job Title for a given JOB_ID This procedure is called Update_Job_Title will have two INPUT parameters. p_job_id as VARCHAR2 as INPUT parameter and also p_new_job_title as VARCHAR2 as INPUT parameter. In BEGIN and END executable section Execute UPDATE command on JOBS table to change title of a JOB_ID Here is the command that you can use in BEGIN and END UPDATE JOBS SET job_title= p_new_job_title WHERE job_id= p_job_id; Test your code by executing procedure for below job_id. Currently in Jobs table you have Job_id = ‘MK_MAN’ and job_title=’ Marketing Manager’ Change title from Marketing Manager to Marketing Sr. Manager execute Update_Job_Title(‘MK_MAN’, ‘Marketing Sr. Manager’ )