Explanation of Solution
a.
Deleting query in the table “GUIDE”:
Public Function GuideDelete(I_GUIDE_NUM)
Dim strSQL As String
strSQL = "DELETE FROM GUIDE WHERE GUIDE_NUM = '"
strSQL = strSQL & I_GUIDE_NUM
strSQL = strSQL & "'"
DoCmd.RunSQL strSQL
End Function
Explanation:
- Create a function named as “GuideDelete” and pass an argument “I_GUIDE_NUM”.
- Set the “strSQL” string variable to “DELETE FROM GUIDE WHERE GUIDE_NUM = '” and makes everything necessary in the command up to and including the single quotation mark preceding the order number.
- The next command will concatenated previous value with the value in “I_GUIDE_NUM”.
- The final command is used to set “strSQL” to the output of the value already created, concatenated with a single quotation mark...
Explanation of Solution
b.
Updating query:
Public Function GuideUpdate(I_GUIDE_NUM, I_LAST_NAME)
Dim strSQL As String
strSQL = "UPDATE GUIDE SET LAST_NAME = '"
strSQL = strSQL + I_LAST_NAME
strSQL = strSQL + "' WHERE GUIDE_NUM = '"
strSQL = strSQL & I_GUIDE_NUM
strSQL = strSQL & "'"
DoCmd.RunSQL strSQL
End Function
Explanation:
- Create a function named as “GuideUpdate” and pass the arguments “I_GUIDE_NUM” and “I_ LAST_NAME”.
- Set the “strSQL” string variable to “UPDATE GUIDE SET LAST_NAME = '” and makes everything necessary in the command up to and including the single quotation mark preceding the order number.
- Here, the user pass two arguments, so there are two portions of the construction of the SQL command that involve variables...
Explanation of Solution
c.
Retrieving the list in the table “RESERVATION”:
Public Function FindReservations(I_CUSTOMER_NUM)
Dim rs As New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim strSQL As String
Set cnn = CurrentProject.Connection
strSQL = "SELECT RESERVATION_ID, TRIP_ID, NUM_PERSONS, TRIP_PRICE FROM RESERVATION WHERE CUSTOMER_NUM = '"
strSQL = strSQL & I_CUSTOMER_NUM
strSQL = strSQL & "'"
rs.Open strSQL, cnn, adOpenStatic, , adCmdText
Do Until rs.EOF
Debug.Print (rs!RESERVATION_ID)
Debug.Print (rs!TRIP_ID)
Debug.Print (rs!NUM_PERSONS)
Debug.Print (rs!TRIP_PRICE)
rs.MoveNext
Loop
End Function
Explanation:
- Create a function named as “Find_Reservations” and pass an argument “I_CUSTOMER_NUM”.
- The “rs” and “cnn” is used to processing the “Recordset” and “ADODB.Connection”.
- Set the “strSQL” string variable to “SELECT RESERVATION_ID, TRIP_ID, NUM_PERSONS, TRIP_PRICE FROM RESERVATION WHERE CUSTOMER_NUM = '” and makes everything necessary in the command up to and including the single quotation mark preceding the order number...
Trending nowThis is a popular solution!
Chapter 8 Solutions
A Guide to SQL
- DO THIS IN MYSQL PLEASE! Question: 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.arrow_forward1. 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, I_CUST_NAME, and I_INVOICE_DATE. 2. Create a procedure to add a row to the INVOICES table. 3. 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_DATEarrow_forwardTask 8: Create the DEL_OWNER procedure to delete the owner whose number is stored in I_OWNER_NUM (provided as a parameter).arrow_forward
- 1- List all the users and their card name using union2- List all the users and their card name using Intersection 3- List all the card numbers with owner names and scheduled transactions, in case the user hasnever used scheduled transaction show null or zero in the transaction column. (Hint use ISNULLfunction)arrow_forward5: 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. HERE'S MY QUERY -- create a procedure named GET_CREDIT_LIMIT -- use I_CUST_ID as a input parameter -- use I_CUSTOMER_NAME, I_CREDIT_LIMIT as output parameters -- select columns CONCAT(FIRST_NAME, ' ', LAST_NAME), CREDIT_LIMIT and store the values in I_CUSTOMER_NAME, I_CREDIT_LIMIT -- using where clause to match the CUST_ID to currently stored in I_CUST_ID(provided as a parameter) DELIMITER // CREATE PROCEDURE GET_CREDIT_LIMIT ( IN I_CUST_ID int, OUT I_CUSTOMER_NAME varchar(75), OUT I_CREDIT_LIMIT decimal(10,2) ) BEGIN SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME), CREDIT_LIMIT INTO I_CUSTOMER_NAME, I_CREDIT_LIMIT FROM CUSTOMER WHERE CUST_ID = I_CUST_ID; END // DELIMITER…arrow_forwardTask 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.arrow_forward
- Given a database of bike share information write a SELECT statement to retrieve the bike_number, start_date, and end_date from trip_data and the station_id and name from bikeshare_stations where the start_station in trip_data is the same as the station_id in bikeshare_stations and where the member_type equals “Casual”. Limit the results to 5 rows. Please answer this python questionarrow_forwardNeed to create two store procedures with help.arrow_forwardTask 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 294arrow_forward
- Trigger pleasearrow_forwardTask 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, I_CUST_NAME, and I_INVOICE_DATE.arrow_forwardMySQL Create the DISPLAY_PROPERTY_OWNER procedure which obtains the office location number, address, owner number, owner first name, and owner last name for the property whose property ID is currently stored in I_PROPERTY_ID (provided as a parameter). Place these values in the variables I_LOCATION_NUM, I_ADDRESS, I_OWNER_NUM, I_FIRST_NAME, and I_LAST_NAME, respectively. Output the contents of I_LOCATION_NUM, I_ADDRESS, I_OWNER_NUM, I_FIRST_NAME, and I_LAST_NAMEarrow_forward
- A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology PtrProgramming with Microsoft Visual Basic 2017Computer ScienceISBN:9781337102124Author:Diane ZakPublisher:Cengage LearningNp Ms Office 365/Excel 2016 I NtermedComputer ScienceISBN:9781337508841Author:CareyPublisher:Cengage
- Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage LearningDatabase Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781285196145Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos CoronelPublisher:Cengage Learning