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: 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_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_forwardWhich of the following statements is true? (refer to the tables in the JustLee Books database.)a. The MIN function can be used only with numeric data.b. The MAX function can be used only with date values.c. The AVG function can be used only with numeric data.d. The SUM function can’t be part of a nested function.arrow_forward
- 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 mycursorarrow_forwardHint: The top organizational count is 536. Submit You do not need to export or convert the database - simply upload the .sqlite file that your program creates. See the example code for the use of the connect() statement. Counting Organizations This application will read the mailbox data (mbox.txt) and count the number of email messages per organization (i.e. domain name of the email address) using a database with the following schema to maintain the counts. CREATE TABLE Counts (org TEXT, count INTEGER) When you have run the program on mbox.txt upload the resulting database file above for grading. If you run the program multiple times in testing or with dfferent files, make sure to empty out the data before each run. You can use this code as a starting point for your application: http://www.py4e.com/code3/emaildb.pyZ. The data file for this application is the same as in previous assignments: http://www.py4e.com/code3/mbox.txt Z. Because the sample code is using an UPDATE statement and…arrow_forwardGiven 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_forward
- MySQL 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_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_forwardWrite in SQL a command that creates the table Supplier according to the above description . Write in SQL a statement that adds a new column named comapnyID of type char(5). This column should be defined as a foreign key that relates the table Supplier to the table Company . Write in SQL a query that lists the full names (first and last names) of all suppliers arranged in ascending order according to their date of births . Write a SQL query that displays the number of suppliers who born in December . Write in SQL a query that displays the ids of the suppliers who supply the highest number of products . Create a view named ‘SupplierDetails’ to hold all the details of suppliers whose last names start with ‘bait’ and who supply between 50 and 200 products .arrow_forward
- 1) Add the following record to the OWNER table: INSERT INTO OWNER VALUES('SA100', 'Sam', 'Afyouni', '100 Hello St', 'Anytown', 'MA', '55555'); 2) Create the UPD_OWNER_LAST_NAMEprocedure to change the last name of the owner whose number is stored in I_OWNER_NUM (provided as a parameter) to the value currently found in I_LAST_NAME.arrow_forwardWhat is a NULL value?arrow_forward
- A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology PtrNp Ms Office 365/Excel 2016 I NtermedComputer ScienceISBN:9781337508841Author:CareyPublisher:CengageDatabase 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
- Database Systems: Design, Implementation, & Manag...Computer ScienceISBN:9781305627482Author:Carlos Coronel, Steven MorrisPublisher:Cengage Learning