How to Create, Run, and Print Statements and Output for an SQL File
Download and install Oracle Express Edition 11g from the following link: https://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/xe-prior-releases-5172097.html
Note: You will be required to create an account.
Open Notepad and type the following at the top of the file:
spool path\project2_abc.txt'
-- Include the full path. This will start logging to the specified file.
set echo on
-- This will ensure that all input and output is logged to the file. Type all statements into a Notepad file (it is recommended that you do this a few statements at a time, testing as you go along). Please indicate the different sections of the project using comment lines, which can be created using two hyphens at the beginning of the comment text; e.g., “--Part I” or “--Part II” (without the quotations). Name the file using your initials as a suffix with an sql extension (e.g., project2_abc.sql).
Type the following at the very end of the file:
set echo off
-- This will turn off logging.
spool off
-- This will close the file. Start your database using the "Start Database" option in the Oracle Database program group (from the Windows "Start button").
Open the SQL command line from the "Run SQL Command Line" option in the Oracle Database program group (from the Windows "Start button"). When the window opens, you will see an SQL prompt. Type the following command to connect to your database: connect system/password.
where, password is the password you entered during the installation process.
Once connected, you may run your SQL file by typing the start command at the SQL prompt (include the full path). For example:
start C:\Users\sarra\Desktop\Project2_kls.sqlYou will see all of the statements and output scroll by. When it is finished, the SQL prompt will appear again.
To disconnect from the database and close your session, type the following:
exit
Ensure that all your statements and related output appear correctly in the spooled text file.
If you have mistakes, you can make the necessary corrections to the SQL statements in your notepad file. Note: If your SQL file contains Create Table statements, you will need to drop the previously-created tables before running the SQL file again. You can do this at the SQL prompt, or you can add the Drop Table statements to the beginning of the SQL file before you run it (the latter is the better option).
This project must be completed using an Oracle database with all statements manually typed into an SQL file and run as a batch in Oracle. The output must be written to a text (.txt) file. Statements and results (feedback from Oracle) must be included in the output file for submission. Directions for downloading the software and for creating and running the SQL file will be posted in Canvas.
Project 2 Relational Schema
I. A) Based on the 3NF relational schema from Project 1 (shown above), analyze the tables, their relationships, and the sample data you were given in Project 1. Create the tables shown, using the best data types based on your analysis. Follow each table name with an underscore and your first, middle, and last initials (if you do not have a middle initial, use X). For example, if your name is Alice B Chandler, you would name the Customer table Customer_abc. Also, keep in mind that foreign key relationships require the same data types on both sides (e.g., if you declare PatientID as NUMBER(5) in the Patient table, it must be declared as NUMBER(5) in the Rental table, too). All CREATE table statements and their resulting output (e.g., “Table created”) must be included in your output file.
B) Execute a DESCRIBE statement for each of the tables. All DESCRIBE statements and their resulting output (i.e., the table structure) must be included in your output file).
II. A) Insert the sample data from Project 1 into each table. Execute a COMMIT statement to permanently save your changes. All INSERT statements and COMMIT statements, along with their resulting output (e.g., “Row inserted”) must be included in your output file.
B) Execute a SELECT statement on each table to list all contents (all columns and all rows). All SELECT statements and their resulting output (i.e., the table and its data) must be included in your output file.
III. Execute the transactions below to modify/add to the data entered in the previous step. Execute a COMMIT statement to permanently save your changes. All INSERT, UPDATE, and COMMIT statements, along with their resulting output (e.g., “Row inserted”) must be included in your output file.
Patient table
Change the phone number of Patient 101 to ‘2145551234’
Add Patient 120 (Amanda Green, no phone number)
ApptStatus table
Add a new status:
ApptStatus ApptStatusDesc
X Cancelled
Appt table
Change the appointment time for Appt 110 to 11:30.
Change the appointment status for Appt 108 to Cancelled
ApptDetail table
Add the following:
ApptID ApptReasonCode BlockCode
108 NP L1
IV. Execute a SELECT statement on each table to list all contents (all columns and all rows), sorted in ascending order by its primary key (in the ApptDetail table, sort by PatientID first, then by ApptReasonCode). All SELECT statements and their resulting output (i.e., the table and its data) must be included in your output file.
Contact us for this machine learning assignment Solutions by Codersarts Specialist who can help you mentor and guide for such machine learning assignments.
If you have project or assignment files, You can send at contact@codersarts.com directly
Comments