Create a procedure named prc_cus_balance_update that will take the invoice number as a parameter and update the customer balance.
Q: Design a query to get the latest 1st record that is created by the user. You need to apply the order…
A: The question is to write the query for the given statement.
Q: List the customer number, customer last name, and customer first name of each customer that lives in…
A: A command to list the customer number, customer last name, and customer first name of each customer…
Q: Alter the INVOICE_LINE table by adding the INVOICE_NUM column as a foreign key referencing the…
A: Alter table : used to modify column, add column/columns, add constraints, drop column, rename, drop…
Q: Please answer briefly: Write an SQL query to fetch the EmpId and FullName of all the employees…
A: The question has been answered in step2
Q: Create the following queries and stored procedures: 3.1 A query that displays order details linked…
A: SELECT CustomerOrders.CustomerName, CustomerOrders.OrderId, OrderDetails.UnitPrice *…
Q: Create an UPDATE trigger called tr_check_qty on the OrderDetails table to prevent the updating of…
A: Creating an UPDATE trigger called tr_check_qty on the OrderDetails table. Table to prevent the…
Q: Write a query to produce the total number of hours and charges for each of the projects represented…
A: A query is a way to give instruction to the server to extract data from database. In SQL, SELECT…
Q: A query that returns all customer information
A: According to the question we need to create the following queries and stored procedures:3.2 A query…
Q: Which clause can be used to limit or filter the groups returned by a query that utilises a group…
A: To be determine: Which clause can be used to limit or filter the groups returned by a query that…
Q: Which clause can be used to restrict or filter the groups returned by a query based on a group…
A: Clause used to restrict the groups: The GROUP BY function is a SQL command that is used to group of…
Q: Create a procedure, ADD_EMPLOYEE., to insert a new employee into the EMPLOYEES table. The procedure…
A: Add another employee named Joe Harris in department 80. leaving remaining parameters with their…
Q: CREATE SEQUENCE ORD_SEQ Increment by 10 Start with 120 Maxvalue 9999 Nocycle; CREATE TABLE…
A: Nearby answer is : 1 and 3 for, 1 : An error will occur because you cannot use SYSDATE in the…
Q: Complete the query to get the EmpFname from the EmployeeInfo table in the upper case using the alias…
A: Given, We have to complete the given query to get the EmpFname from the EmployeeInfo table in the…
Q: Which line of the SELECT statement is used to group data stored in the database?…
A: Explanation: The select statement is used to group data stored in database is GROUP BY. GROUP BY…
Q: List the vendor names and address(city, state,zipcode), invoice total for all the vendors who have…
A: There are 2 table which will be joined by outer join. One table contains vendor name,…
Q: Colombia office employees started using COLOMBIA_CITY_PROPERTY and they want to update the rent of…
A: the query is given below :-
Q: List student names and total reservations for all students whose have reservad a book more then one…
A: SQL Query: select s.sname and count(r.sid) from student as s, reservation as r, book as b where…
Q: Create an UPDATE query that lowers the daily rental rate of sailboat rentals by 10%. Note: show me…
A: Solution: As per the given information in the question. The query is listed below as per the given…
Q: FROM SALESPERSON WHERE SPNUM= (SELECT SPNUM FROM CUSTOMER WHERE CUSTNUM=772): What is this subquery…
A: Given subquery SELECT SPNUM FROM CUSTOMER WHERE CUSTNUM=772
Q: Create a query to find the balance characteristics for all customers, including the total of the…
A: We can calculate the balances of table based on some grouping columns. If no grouping column is…
Q: A stored procedure that gets customer level
A: According to the question we need to Create the following queries and stored procedures: 3.5 A…
Q: Write the SQL code that will list only the distinct project numbers in the ASSIGNMENT table, sorted…
A: DISTINCT is the keyword used to get the distinct items from the Table related to the column used…
Q: Create a procedure, ADD_EMPLOYEE., to insert a new employee into the EMPLOYEES table. The procedure…
A: Call ADD_EMPLOYEE for the name Jane Harris in department 15. leaving other parameters with their…
Q: 3. Using the EMPLOYEE table that already exists, use a subquery to insert the remaining rows from…
A: Using the EMPLOYEE table that already exists, use a subquery to insert the remaining rows from the…
Q: What kind of circumstance necessitates the use of a subquery?
A: Intro Situation of using subquery A subquery is used to provide data within a database that is…
Q: •Create and load the Employee, Department and Titic Tavics: ton •Write Queries for the following:…
A: Since multiple queries have been asked to be answered, we will answer first three queries. If you…
Q: For every invoice, display the invoice number, invoice date and the total dollar amount for all…
A: Dear student your query get the error because inv_number exist in the two table so you have to…
Q: 4. Write the SQL code that will save the changes made to the EMP_1 table (if supported by your…
A: Given Information : Table name = EMP_1 Attributes : JOB_CODE EMP_NUM
Q: Need to create two store procedures with help.
A: Answer:1.DROP PROCEDURE IF EXISTS p_property;GOCREATE PROCEDURE p_property_allAS BEGIN SELECT *…
Q: Show how to define a view tot credits (year, num credits), giving the total number of credits taken…
A: SQL Query
Q: 1) Rewrite the query below to use GROUP BY CUS_CODE : SELECT CUSTOMER.CUS_CODE,…
A: In step 2, you will get the answer for 1. In step 3, you will get the answer for 2. In step 4, you…
Q: : Q1.Please write a query statement from emp table to display deptno 10’s empno, ename, sal, and a…
A: It is defined as a collection of information that is organized so that it can be easily accessed,…
Q: Create an UPDATE trigger called tr_check_qty on the OrderDetails table to prevent the updating of…
A: The orderdetails table have the details The product table have the details The trigger is created…
Q: Modify the following SQL command to make the changes below. CREATE TABLE store_reps ( rep_ID…
A: Solution Making rep_ID primary key assigning Y as the default value to the comm column. CREATE…
Q: Create a procedure, ADD_EMPLOYEE., to insert a new employee into the EMPLOYEES table. The procedure…
A: Create the ADD_EMPLOYEE procedure to add an employee to the EMPLOYEES table.The row should be added…
Q: The SQL command CREATE TABLE EMPLOYEE_BACKUP AS SELECT * FROM EMPLOYEE creates a new table named…
A: False Explanation1)SQL is a database programming language intended for accessing and managing data…
Q: Create a procedure to display manager ID and number of employees managed by each manager. Use…
A: SQL procedure to display manager details from Employee table is given below.
Q: Write the sequence of commands required to update the CHAR_FLT_CHG_HR attribute values in the…
A: Required: Write the sequence of commands required to update the CHAR_FLT_CHG_HR attribute values in…
Q: Task: UPDATE all employees salary in department 40 or 60 depending on their current salary range.…
A: To update the salary of the employee: below code below:
Q: Need assistance: Create a MySQL trigger named trg_updatecustbalance to update the CUST_BALANCE in…
A: To create: To create a MySQL trigger using the given record of values
Q: Perform the following functions using the following commands on the tables created(see tables below)…
A: CREATE TABLE product_order_tbl ( order_num varchar(9) NOT NULL, product_no VARCHAR2(3) NOT NULL,…
Q: Use KimTay Pet Supplies Database For each invoice, list the invoice number and invoice date along…
A: SQL We communicate with a relational database that use the computer language known as Structured…
Q: Select all rows from the Orders table where the CustomerID is 400 or 500
A: I have provided an answer to this question in step 2.
Q: mber in descending order and then by invoice date in ascending order. Here you will use an aggregate…
A: SQL: For every invoice, display the invoice number, invoice date and the total dollar amount for all…
Q: Select all rows from the Orders table where the CustomerID is greater than 300.
A: Syntax: SELECT column1, column2, ...FROM table_nameWHERE condition;
Create a procedure named prc_cus_balance_update that will take the invoice number as a parameter and update the customer balance.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
- A new table has been requested to support tracking automated emails sent to customers. Create the table and add data as described below. Tablename: email_log Columns: emailid (numeric), emaildate (datetime), customer# (numeric) Primary key: emailid column, define as an Identity Column Add the following data rows and display resulting rows (if any errors occur, explain why the error is expected) Emaildate = current date, customer# = 1007 Emailid = specify to use the column default value, emaildate = current date, customer# = 1008 Emailid = 25, emaildate = current date, customer# = 1009The 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.Create a trigger named tInstructors_UPDATE that checks the new value for the AnnualSalary column of the Instructors table. This trigger should raise an appropriate error if the annual salary is greater than 120,000 or less than 0.
- Create a view named INVOICE_TOTAL. It consists of the invoice number and invoice total for each invoice currently on file. (The invoice total is the sum of the number of units ordered multiplied by the quoted price on each invoice line for each invoice.) Sort the rows by invoice number. Use TOTAL_AMOUNT as the name for the invoice total.Create a stored procedure named prc_inv_amounts to update the INV_SUBTOTAL, INV_TAX, and INV_TOTAL. The procedure takes the invoice number as a parameter. The INV_SUBTOTAL is the sum of the LINE_TOTAL amounts for the invoice, the INV_TAX is the product of the INV_SUBTOTAL and the tax rate (8 percent), and the INV_TOTAL is the sum of the INV_SUBTOTAL and the INV_TAX.Create a trigger named Products_UPDATE that checks the new value for the DiscountPercent column of the Products table. This trigger should raise an appropriate error if the discount percent is greater than 100 or less than 0. If the new discount percent is between 0 and 1, this trigger should modify the new discount percent by multiplying it by 100. That way, a discount percent of .2 becomes 20. Test this trigger with an appropriate UPDATE statement.
- Task 4: KimTay Pet Supplies is running a promotion that is valid for up to 20 days after an order is placed. List the INVOICE_NUM, CUST_ID, FIRST_NAME, LAST_NAME, INVOICE_DATE, and the promotion date for each invoice as PROMOTION_DATE. The promotion date is 20 days after the invoice was placed. Task 5: Create the GET_CREDIT_LIMIT procedure to obtain the full name and credit limit of the customer whose ID currently is stored in I_CUST_ID. Place these values in the variables I_CUSTOMER_NAME and I_CREDIT_LIMIT, respectively. When the procedure is called it should output the contents of I_CUSTOMER_NAME and I_CREDIT_LIMIT. Task 6: Create the GET_INVOICE_DATE procedure to obtain the customer ID, first and last names of the customer, and the invoice date for the invoice whose number currently is stored in I_INVOICE_NUM. Place these values in the variables I_CUST_ID, I_CUST_NAME, and I_INVOICE_DATE respectively. When the procedure is called it should output the contents of I_CUST_ID,…2) Create a SELECT statement that returns the count, average, max and min of the invoices submitted by each vendor, who has submitted more than one invoice. Order by the number of invoices in descending order. Should return 12 rows. number of vendor name average_invoice_total min_invoice_total max_invoice_total invoices Federal Exoress Corporation United Parcel Service 47 93.15 10.00 739.20 2575.33 1927.54 3813.33 Zvlka Desian Pacific Bel Malov Lithoarachina Inc 313. 55 16.33 867.53 1962. 13 28.50 46.21 23978.48 10976.06 37966, 19 25.67 224.00 Roadwav Packaoe Svstem. Ic 4 10.92 6.00 Blue Cross Comouserve Incram Data Reoroductions Corp IBM |Cardinal Business Media. Inc. 188.00 116.00 9.95 9.95 9.95 2 1077.21 579. 42 1575.00 85.31 116.54 10963.66 21842.00 600.06 132.68 1083. 58 90.36 175.00Task 8: Create the UPDATE_INVOICE procedure to change the date of the invoice whose number is stored in I_INVOICE_NUM to the date currently found in I_INVOICE_DATE. Expected Results INVOICE_NUM INVOICE_DATE CUST_ID 00001 2021-11-21 294
- sql question Write an UPDATE statement that modifies the Customers table. Change the password column to “reset” for every customer in the table.Create a VIEW named MAJOR_CUSTOMER. It consists of the customer ID, first name, last name, balance, credit limit, and rep ID for every customer whose credit limit is $500 or less.Please create this two trigger and write down as Mysql statement Create a trigger named trg_charter_time that will automatically update the AIRCRAFT table when a new CHARTER row is added. Use the CHARTER table’s CHAR_HOURS_FLOWN to update the AIRCRAFT table’s AC_TTAF, AC_TTEL, and AC_TTER values. The meaning for the AIRCRAFT table columns are as follows: AC_TTAF: Total time on the air frame (before maintenance) AC_TTEL: Total time on the left engine (before maintenance, also track single engine) AC_TTER: Total time on the right engine (before maintenance) So in the trigger, you need to decrease all of them by the number of CHAR_HOUR_FLOWN Create a trigger named trg_cust_charge that will automatically update the CUSTOMER table’s CUS_BALANCE when a new CHARTER row is added. Use the CHARTER table’s CHAR_TOT_CHG as the update source (Assume that all charter charges are charged to the customer balance.) In addition to the CHAR_TOT_CHG, add a $5 service charge for every 50 miles…