Explanation of Solution
a.
Deleting query in the table “ORDERS”:
Public Function Order_Delete(I_ORDER_NUM)
Dim strSQL As String
strSQL = "DELETE FROM ORDERS WHERE ORDER_NUM = '"
strSQL = strSQL & I_ORDER_NUM
strSQL = strSQL & "'"
DoCmd.RunSQL strSQL
End Function
Explanation:
- Create a function named as “Order_Delete” and pass an argument “I_ORDER_NUM”.
- Set the strSQL string variable to “DELETE FROM ORDERS WHERE ORDER_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_ORDER_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 Order_Update(I_ORDER_NUM, I_ORDER_DATE)
Dim strSQL As String
strSQL = "UPDATE ORDERS SET ORDER_DATE = '"
strSQL = strSQL & I_ORDER_DATE
strSQL = strSQL & "' WHERE ORDER_NUM = '"
strSQL = strSQL & I_ORDER_NUM
strSQL = strSQL & "'"
DoCmd.RunSQL strSQL
End Function
Explanation:
- Create a function named as “Order_Update” and pass the arguments “I_ORDER_NUM” and “I_ORDER_DATE”.
- Set the strSQL string variable to “UPDATE ORDERS SET ORDER_DATE = '” 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 “ITEM”:
Public Function Find_Items(I_CATEGORY)
Dim rs As New ADODB.Recordset
Dim cnn As ADODB.Connection
Dim strSQL As String
Set cnn = CurrentProject.Connection
strSQL = "SELECT ITEM_NUM, DESCRIPTION, STOREHOUSE, PRICE FROM ITEM WHERE CATEGORY= '"
strSQL = strSQL & I_CATEGORY
strSQL = strSQL & "'"
rs.Open strSQL, cnn, adOpenStatic, , adCmdText
Do Until rs.EOF
Debug.Print (rs!ITEM_NUM)
Debug.Print (rs!Description)
Debug.Print (rs!STOREHOUSE)
Debug.Print (rs!PRICE)
rs.MoveNext
Loop
End Function
Explanation:
- Create a function named as “Find_Items” and pass an argument “I_CATEGORY”.
- The “rs” and “cnn” is used to processing the “Recordset” and “ADODB.Connection”...
Trending nowThis is a popular solution!
Chapter 8 Solutions
A Guide to SQL
- Task 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_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_forwardPlease written by computer source 1. For this project, you need to create the company database and populate it with the data given in the Oracle SQL PLUS environment (if you have not done so) 2. Your SQL commands should be placed in file(s) and run from the file(s). 3. Implement Triggers on all the tables to change character data to uppercase in the character data type columns. 4. Implement a Function that returns Dname from Department for any give employee. The parameter to the function would be the SSN. 5. Implement a Function that returns manager’s full name for any given department. The parameter to the function would be the department name. 6. Implement another Function that returns manager’s full name for any given department. The parameter to the function would be the department number. 7. Implement a Procedure that increases an employee salary by x%. Employee is identified by SSN and the percentage of increase is given as an input. 8. Implement a Package that contains the…arrow_forward
- Question 13 Given the following declaration of records and the name of the records, write the full program to create two records and assign values based on the following details.table parttime stname(ahmed hassan) , stadress(Dubai)stphone(052772711)CGPA(3.56) Question13 table 2 table name fulltime stname(nuha Ali)stadress(abudhabi)stphone(0542227222)CGPA(3.88)arrow_forwardcreate table patients ( patient_id VARCHAR(255),name VARCHAR(255),insurance DECIMAL(15,2),date_admitted DATE,date_checked_out DATE,PRIMARY KEY(patient_id));INSERT INTO patients (ID , name , insurance , date_admitted , date_checked_out) values (2 , 'Ali' , '22.2' , '2/1/2020' , '3/1/2020')SELECT * FROM table patients create table doctor (doctor_id VARCHAR(255),name VARCHAR(255),specialization VARCHAR(255),PRIMARY KEY (doctor_id) ); create table test (test_id VARCHAR(255),test_name VARCHAR(255),test_date DATE,test_time TIME,result VARCHAR(255),PRIMARY KEY(test_id)); create table doctor_patient( patient_id VARCHAR(255), doctor_id VARCHAR(255),PRIMARY KEY(patient_id),FOREIGN KEY(doctor_id) REFERENCES doctor(doctor_id) ); create table test_log (test_log_id varchar(255),patient_id VARCHAR(255),test_id VARCHAR(255),doctor_id VARCHAR(255),comments VARCHAR(255),PRIMARY KEY(test_log_id),FOREIGN KEY(test_id) REFERENCES test(test_id),FOREIGN KEY(patient_id) REFERENCES…arrow_forwardcreate table patients ( patient_id VARCHAR(255),name VARCHAR(255),insurance DECIMAL(15,2),date_admitted DATE,date_checked_out DATE,PRIMARY KEY(patient_id));INSERT INTO patients (ID , name , insurance , date_admitted , date_checked_out) values (2 , 'Ali' , '22.2' , '2/1/2020' , '3/1/2020')SELECT * FROM table patients create table doctor (doctor_id VARCHAR(255),name VARCHAR(255),specialization VARCHAR(255),PRIMARY KEY (doctor_id) ); create table test (test_id VARCHAR(255),test_name VARCHAR(255),test_date DATE,test_time TIME,result VARCHAR(255),PRIMARY KEY(test_id)); create table doctor_patient( patient_id VARCHAR(255), doctor_id VARCHAR(255),PRIMARY KEY(patient_id),FOREIGN KEY(doctor_id) REFERENCES doctor(doctor_id) ); create table test_log (test_log_id varchar(255),patient_id VARCHAR(255),test_id VARCHAR(255),doctor_id VARCHAR(255),comments VARCHAR(255),PRIMARY KEY(test_log_id),FOREIGN KEY(test_id) REFERENCES test(test_id),FOREIGN KEY(patient_id) REFERENCES…arrow_forward
- A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology PtrDatabase 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 LearningNp Ms Office 365/Excel 2016 I NtermedComputer ScienceISBN:9781337508841Author:CareyPublisher:Cengage