A Guide to SQL
9th Edition
ISBN: 9781111527273
Author: Philip J. Pratt
Publisher: Course Technology Ptr
expand_more
expand_more
format_list_bulleted
Expert Solution & Answer
Chapter 8, Problem 6CAT
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...
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
Write a stored procedure that accepts a department ID, uses BULK COLLECT to retrieve all employees in that department, and displays their first name and salary. Then write an anonymous block to run that procedure for department 100.
Below are some rows of the table INVOICE
COD
PROV_COD
DATE
TYPE
LOC
TOTAL
2910
192
2022-03-11
90
TX
1928
9301
384
2022-05-03
90
NY
2800
Overdue invoices are those whose date plus TYPE days have passed. Which of the following shows all invoices with overdue dates?
a.
SELECT *
FROM INVOICE
WHERE CURDATE() - DATE > TYPE
b.
SELECT *
FROM INVOICE
WHERE CURDATE()-TYPE >DATE
c.
SELECT *
FROM INVOICE
WHERE DATE+TYPE < CURDATE()
d.
SELECT *
FROM INVOICE
WHERE DATE+TYPE > CURDATE()
Guideline: Pls create employees table and populate it with the scripts provided for you (if the employees table does not exist)
1. PL/SQL Cursor: Using Cursor, please write PL/SQL program to retrieve data from employees table and display employee first and last name.
2. PL/SQL Exceptions: Write PL/SQL program to data from employees table and handle the following exceptions
1) NO_DATA_FOUND
2) TOO_MANY_ROWS
3) WHEN OTHERS
4) VALUE_ERROR - This exception is raised, when the value which is returned does not match with the datatype variable.
5) DUP_VAL_ON_INDEX - This exception is raised when we try to insert a duplicate value on a primary key or unique key
Chapter 8 Solutions
A Guide to SQL
Ch. 8 - Prob. 1RQCh. 8 - Prob. 2RQCh. 8 - Prob. 3RQCh. 8 - Prob. 4RQCh. 8 - Prob. 5RQCh. 8 - Prob. 6RQCh. 8 - Prob. 7RQCh. 8 - Prob. 8RQCh. 8 - Prob. 9RQCh. 8 - In PL/SQL, how do you assign variables the same...
Ch. 8 - Prob. 11RQCh. 8 - Prob. 12RQCh. 8 - Prob. 13RQCh. 8 - Prob. 14RQCh. 8 - Prob. 15RQCh. 8 - Prob. 16RQCh. 8 - Prob. 17RQCh. 8 - Prob. 18RQCh. 8 - Prob. 19RQCh. 8 - Prob. 20RQCh. 8 - Prob. 21RQCh. 8 - Prob. 22RQCh. 8 - Prob. 1TDCh. 8 - Prob. 2TDCh. 8 - Prob. 3TDCh. 8 - Prob. 4TDCh. 8 - Prob. 5TDCh. 8 - Prob. 6TDCh. 8 - Prob. 7TDCh. 8 - Prob. 8TDCh. 8 - Prob. 9TDCh. 8 - Prob. 10TDCh. 8 - Prob. 1CATCh. 8 - Prob. 2CATCh. 8 - Prob. 3CATCh. 8 - Prob. 4CATCh. 8 - Prob. 5CATCh. 8 - Prob. 6CATCh. 8 - Prob. 7CATCh. 8 - Prob. 9CATCh. 8 - Prob. 1SCGCh. 8 - Prob. 2SCGCh. 8 - Prob. 3SCGCh. 8 - Prob. 4SCGCh. 8 - Prob. 5SCGCh. 8 - Prob. 6SCGCh. 8 - Prob. 7SCGCh. 8 - SQL includes many numerical functions. Two of...
Knowledge Booster
Similar questions
- Which of the following dictionary functions returns a few consisting of two element Tuples? In python. A.get() B.keys() C.values() D.items()arrow_forwardTask 4: Use the EXISTS operator to find the ID, first name, and last name of each customer for which as invoice was created on November 15, 2021. This is incorrect: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_forward
- Write C# code to update data in the database table. The fields are Student_Id, Student_Name, Major, and PhoneNumber.arrow_forwardpl/sql Product(pid, pname, pprice, category) Use the table given above to write a PL/SQL procedure that accepts a category value as parameter and prints name and price of all products in that category sorted according to the name; prints the total price of all products on screen; handles the following exceptions by printing an error message: One relevant named exception One unnamed exception with oracle error number ORA-01555 If the price of any product is negative, treat this as a user defined exception.arrow_forwardExplain the role and purpose of the two codes provided below. Create or Replace Trigger Purchase_EntryAfter Insert or Update of Purchase_Amt on PURCHASE_AGREEMENTFor Each RowBegin if(:New.Purchase_Amt < 0) Then RAISE_APPLICATION_ERROR(‐20100, 'Cannot enter a purchase agreement amount less than zero.'); end if;End; insert into PURCHASE_AGREEMENT values (559, '27 May 2020', ‐1, 12350,'C122',1arrow_forward
- Which of the following statements DOES NOT delete the last value in the list lst? a) 1st.pop( b) 1st.remove(-1) c) 1st = lst[:-1] d) 1st.pop(len(lst)-1)arrow_forward<?php session_start(); include "db_conn.php"; if (isset($_POST['uname']) && isset($_POST['password'])) { function validate($data){ $data = trim($data); $data = stripslashes($data); $data = htmlspecialchars($data); return $data; } $uname = validate($_POST['user_name']); $pass = validate($_POST['password']); if (empty($uname)) { header("Location: index.php?error=User Name is required"); exit(); }else if(empty($pass)){ header("Location: index.php?error=Password is required"); exit(); }else{ $sql = "SELECT * FROM users WHERE user_name='$uname' AND password='$pass'"; $result = mysqli_query($conn, $sql); if (mysqli_num_rows($result) === 1) { $row = mysqli_fetch_assoc($result); if ($row['user_name'] === $uname && $row['password'] === $pass) { echo "Logged in!"; $_SESSION['user_name'] = $row['user_name'];…arrow_forwardUse My Guitar Shop Database Use Microsoft SQL Server Write a script that includes these statements coded as a transaction: INSERT Orders VALUES (3, GETDATE(), '10.00', '0.00', NULL, 4, 'American Express', '378282246310005', '04/2019', 4); SET @OrderID = @@IDENTITY; INSERT OrderItems VALUES (@OrderID, 6, '415.00', '161.85', 1); INSERT OrderItems VALUES (@OrderID, 1, '699.00', '209.70', 1); Here, the @@IDENTITY variable is used to get the order ID value that’s automatically generated when the first INSERT statement inserts an order. If these statements execute successfully, commit the changes. Otherwise, roll back the changes.arrow_forward
- Write a script that creates and calls a stored function named TotalOrders that calculates the total number of orders in the orders table. To do that, this function should not accept any parameter, and it should return the total number of orders in the orders table. Call the function from an anonymous block. Add SQL statement(s) that will display current date and your name and student ID in the format specified below. Your anonymous block must include exception handling.arrow_forwardTask 5: Use the EXISTS operator to find the ID, first name, and last name of each customer for which as invoice was created on November 15, 2021.arrow_forwardOracle language Display the employee’s name whose salary is within a range that is specified by theuser input as follows:arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr
Np Ms Office 365/Excel 2016 I NtermedComputer ScienceISBN:9781337508841Author:CareyPublisher:Cengage
Database 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
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning