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 6SCG
Explanation of Solution
a.
Deleting query in the table “OWNER”:
Public Function Owner_Delete(I_OWNER_NUM)
Dim strSQL As String
strSQL = "DELETE FROM OWNER WHERE OWNER_NUM = '"
strSQL = strSQL & I_OWNER_NUM
strSQL = strSQL & "'"
DoCmd.RunSQL strSQL
End Function
Explanation:
- Create a function named as “Owner_Delete” and pass an argument “I_OWNER_NUM”.
- Set the strSQL string variable to “DELETE FROM OWNER WHERE OWNER_NUM = '” and make everything necessary in the command including the single quotation mark preceding the order number...
Explanation of Solution
b.
Updating query:
Public Function Owner_Update(I_OWNER_NUM, I_LAST_NAME)
Dim strSQL As String
strSQL = "UPDATE OWNER SET LAST_NAME = '"
strSQL = strSQL & I_LAST_NAME
strSQL = strSQL & "' WHERE OWNER_NUM = '"
strSQL = strSQL & I_OWNER_NUM
strSQL = strSQL & "'"
DoCmd.RunSQL strSQL
End Function
Explanation:
- Create a function named as “Owner_Update” and pass the arguments “I_OWNER_NUM” and “I_LAST_NAME”.
- Set the strSQL string variable to “UPDATE OWNER SET LAST_NAME = '” and make everything necessary in the command including the single quotation mark preceding the order number...
Explanation of Solution
c.
Retrieving the list in the table “CONDO_UNIT”:
Public Function Find_Condos(I_SQR_FT)
Dim rs As New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim strSQL As String
Set cnn = CurrentProject.Connection
strSQL = "SELECT LOCATION_NUM, UNIT_NUM, CONDO_FEE, OWNER_NUM FROM CONDO_UNIT WHERE SQR_FT = "
strSQL = strSQL & I_SQR_FT
rs.Open strSQL, cnn, adOpenStatic, , adCmdText
Do Until rs.EOF
Debug.Print (rs!LOCATION_NUM)
Debug.Print (rs!UNIT_NUM)
Debug.Print (rs!CONDO_FEE)
Debug.Print (rs!OWNER_NUM)
rs.MoveNext
Loop
End Function
Explanation:
- Create a function named as “Find_Condos” and pass an argument “I_SQR_FT”...
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
Use 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.
1. 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_DATE
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.
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_forwardThis kind of code gives me errors i have more info below. My codeDELIMITER //CREATE PROCEDURE GET_INVOICE_DATE (in I_INVOICE_NUM CHAR(5))DECLARE I_CUST_ID CHAR(5),DECLARE I_CUST_NAME VARCHAR(41),DECLARE I_INVOICE_DATE DATE BEGIN SELECT c.CUST_ID, CONCAT(c.FIRST_NAME, ' ', c.LAST_NAME), i.INVOICE_DATE INTO I_CUST_ID, I_CUST_NAME, I_INVOICE_DATE FROM INVOICES i INNER JOIN CUSTOMER c ON i.CUST_ID = c.CUST_ID WHERE i.INVOICE_NUM = I_INVOICE_NUM; END // Delimiter ; Helpfull info Task #6 – same process as Task #5• Procedure name is GET_INVOICE_DATE with I_INVOICE_NUM in parentheses with 5 characters• There should be 3 declare statements for I_CUST_ID CHAR(5); I_CUST_NAME VARCHAR(41); andI_INVOICE_DATE DATE is datatype• SELECT statement should include the CUSTOMER.CUST_ID, CONCAT for first_name andlast_name AS customer_name, invoice_date• INTO the 3 “I” fields in the declare statement• From should include the CUSTOMER and INVOICES tables• Where clause to join CUSTOMER.CUST_ID =…arrow_forwardBelow is my code and an image of the prompt. The code runs but according to my professor I cant use built in functions such as in Line 4[clientList.append(fullName)], Line 17[clientList.insert(index, fullName)], & Line 46[combined_data.sort(reverse=True)]. I also cannot use these other functions such as Sort,Max,Min,Append,Insert & Remove. Im suppose to replace the built in function and create my own functions but im not sure how and hopefully someone can solve it for me. My Code(Python) import sys def addClient(clientList, balanceList, fullName, balance): clientList.append(fullName) balanceList.append(balance) def removeClient(clientList, balanceList, fullName): if fullName in clientList: index = clientList.index(fullName) clientList.pop(index) balanceList.pop(index) else: print("Client not found.") def insertClient(clientList, balanceList, fullName, index, balance): if index >= 0 and index <=…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_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_forwardComputer Science Write a script that creates four login IDs based on the contents of a new table named NewLogins: CREATE TABLE NewLogins (LoginName varchar(128)); INSERT NewLogins VALUES ('BBrown'), ('CChaplin'), ('DDyer'), ('EEbbers'); Use dynamic SQL and a cursor to perform four actions for each row in this table: (1) create a login with a temporary password that's based on the first four letters of the login name followed by “99999”; (2) set the default database to the AP database; (3) create a user for the login with the same name as the login; and (4) assign the user to the PaymentEntry role you created in exercise 1..arrow_forward
- 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 keyarrow_forwardWrite 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_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_forward
- Complete the following JavaScript code in order to validate repeated students based on their ID within the “postStudent” function. Block Code: estControl.postStudent = (req,res)=>{ const {id, name,lastName} = req.body; if(!id || !name || !lastName){ res.status(400).send("Incomplete data {id, name, lastName}"); return; } const Student= { id, name, lastName } db.Students.push(Student); db.updateDB(); res.send('Student registered'); }arrow_forwardProgram should follow in term (Contacts Database) Study the books.sql script provided in the ch17 examples folder's sql subfolder. Save the script as addressbook.sql and modify it to create a single table named contacts. The table should contain an auto-incremented id column and text columns for a person's first name, last name and phone number. make a Python script demonstrating: 1. insert contacts into the database, 2. query the database to list all the contacts and contacts with specific last name, 3. update a contact and 4. delete a contact. books.sql code: import panda as pdimport sqlite3connection = sqlite3.connect('books.db')pd.options.display.max_columns = 10pd.read_sql("SELECT * FROM authors",connection) df = pd.read_sql( "SELECT * FROM authors",connection)DROP TABLE IF EXISTS author_ISBN;DROP TABLE IF EXISTS titles;DROP TABLE IF EXISTS authors;CREATE TABLE authors ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, first TEXT NOT NULL, last TEXT NOT NULL);CREATE TABLE…arrow_forwardRewrite the following code snipped using the jQuery function $.get. $("#display").load("greetings.html");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
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
Np Ms Office 365/Excel 2016 I NtermedComputer ScienceISBN:9781337508841Author:CareyPublisher:Cengage
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
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
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage