Complete Part 1 and 2Part 1 uses install instructions and maybe the create college and animal file, while Part 2 uses LibraryManagementSystem
Oracle Homework #4: Chapter 4 Oracle Homework SQL Chapter 8: Joins You may only use any information that is given to you in the problem. Include the following comments at the top: -- Your Name -- Date Put all of the queries in one script; do not zip the file. Save the file as LASTNAME_FIRSTNAME_Ch8_ProblemSet. ch8p1. Write a query that will display the department name (or 5 dashes if NULL) and the city where the department is located. Exclude Seattle. Order as shown: ch8p2. Display a listing of animals and their volunteer’s name. If the animal does not have a volunteer, display ‘no volunteer assigned’. Display the columns as shown below: ch8p3. Using the Animal Shelter Schema, create a query that will display the name of the cats along with the name of any adopters who would like to adopt that animal and name it "Animal and Adopter". Also, display the name of the animal along with its appropriate breed name and breed type called "Animal Info". Order by column 1. ch8p4. Create the following class schedule for those students who are taking a course with either ‘Database’ or ‘Systems Analysis’ in the course name. Order by the course name, then the student’s last name. ch8p5. (Self Join) Create a query using the Self-join method that will display the id, name, and job_title of all employees that work in the same department as the person whose job_id is FI_MGR. You may not look up data manually and plug that into the SQL code. You can only use the fact that you know the job_id is FI_MGR, so don’t go looking up the person’s name or department_id. Name the column “Financial Employees”. Order by person’s name. Part II. Using your Semester project schema, working with your team member, create the follow new problems using the features introduced in this chapter. Create a new SQL file for these queries save as. Team_N_Queries_10-11.sql, where N is your Team number. Here is a list of requirements for each query in the semester project: · Include a comment that identifies the query as Query 1, Query 2, etc. · Include a comment on the next line that explains in plain English what the query does · All queries must have an ORDER BY · All columns must have an alias (Do not use SELECT * to satisfy any query) · The results of the query should be nicely formatted and has at least one row of output · Do not copy one query and modify it slightly to satisfy another query Use Snip to make a screenshot of the query (the SQL code) and the results together. Put both queries in one Word document and print the Word document. QUERY 10 must contain: · A select list that contains columns from two or more tables · An INNER JOIN QUERY 11 must contain: · An OUTER JOIN · The output with data showing at least one “orphan”. (Explanation: the output of Q11 must show how the outer join includes rows from one table that do not have a match with a row in the other table. That is, the results of this query must contain at least one orphan. You may have to insert one or two new rows using your script (from Chapter 3) so that the outer join works correctly.) Chapter 8 checklist: Upload SQL script for solutions to ch8p1 – ch8p5 to D2L | Assignments | Chapter 8 Problem Set Upload SQL script for Queries 10 - 11 and Word document containing screenshots of code and results for Queries 10 - 11 to D2L | Assignments | Semester Project | Part 4: Queries 10 - 11 GETTING STARTED WITH ORACLE DATABASE 18C EXPRESS Install Oracle Database 18c and SQL Developer (1) Installation instructions : https://www.youtube.com/watch?v=wiEsR_j36yw&t=308s (2) Here is the download link to specified in the video: https://www.oracle.com/database/technologies/appdev/xe.html (3) NOTE: IT IS IMPORTANT THAT YOU DO ALL OF THESE INSTRUCTIONS AS ADMINISTRATOR on your own computer. So when you run Setup.exe, right-click and select “Run as Administrator”. Also you cannot select a directory for installation that has any spaces in it. For example: C:\app\Lissa Pollacia will generate an error about invalid character because there is a space between my first and last name. (4) When you get to the password for SYS, SYSTEM, and PDBADMIN, (as shown below) enter the word admin123. Now is not the time to get creative and make up something else—if you ever need my help with something, I need to know the SYSTEM password. The installation may take some time (20-30 minutes). At the end you will see this screen: (5) Continue watching the video https://www.youtube.com/watch?v=wiEsR_j36yw&t=308s and follow the instructions. Note that when you first login as sysdba, it will take a minute or two, so be patient. admin123 https://www.youtube.com/watch?v=wiEsR_j36yw&t=308s https://www.oracle.com/database/technologies/appdev/xe.html https://www.youtube.com/watch?v=wiEsR_j36yw&t=308s When you get to the following command, remember that our password is admin123: So you should replace the word “oracle” with “admin123”, as shown below: connect sys/admin123@localhost:1521/XEPDB1 as sysdba NOTE: If you get the error: TNS Listener: No Listener found, or other type of error then refer to the Troubleshooting section at the end of this document. (6) At the end of the video (around the 5 minute mark), it shows you how to stop and restart the database. You don’t have to actually do this, you can just watch. (7) Type Exit to leave SQL*Plus after you have completed the instructions in this video. Perform a hard reboot of your computer now. Make sure you can run Oracle from any network In Windows, search for a file called tnsnames.ora and edit it with Notepad++. For my installation, the pathname is C:\app\lpollaci\product\18.0.0\dbhomeXE\NETWORK\ADMIN\tnsnames.ora, so yours will be something similar. Make sure you replace IP address, such as 100.04.34.1, with your computer name, and delete anything after your computer name in HOST. For example, if the host name shows DESKTOP- 6JST8BS.attlocal.net, remove .attlocal.net and leave DESKTOP-6JST8BS. Your computer’s name, found in Control Panel | System & Security | System (Only list the computer name, delete anything after that. ) Now open up Listener.ora in Notepad++, also in the same folder. It should look like this: Reboot your computer. This will allow you to use Oracle from any network you connect to. Otherwise, you will only be able to use it on the network where you originally installed it. Install SQL Developer Even though we can run Oracle SQL via command line (SQL*Plus), most of our work will be done using a modern tool called “SQL Developer”. (1) To download and install SQL Developer, view and follow the instructions on this video up through time 5:25 (you can stop watching at time 5:25) https://www.youtube.com/watch?v=9lWdEi8EYY0&ab_channel=AnilPatel (2) In SQL Developer, click the Green Plus sign under Connections to get a new connection and enter the following: Your computer’s name goes here https://www.youtube.com/watch?v=9lWdEi8EYY0&ab_channel=AnilPatel (3) You should see this for the connection: (4) Expand the MySchema connection, then expand Tables. Click the Countries table and you will see the structure of the table: (5) Now click the Data tab, to see the data in the table: (6) Download and unzip Create_college_and_Animal18c.sql. If you do not have this file, you can download it from D2L | ITEC 4200 course | CONTENT | START HERE folder. (7) In SQL Developer, click File and then Open, then browse to choose the unzipped file Create_college_and_Animal18c.sql from your computer. It will look like this in the worksheet window: (8) Now click the Run Script button. (this is the 2nd green button) (9) Click “OK” for Myschema. (10) Once the script has completed, you will see at the bottom of the screen. (11) Close the Create_College_and_Animal18c.sql tab. (12) In Oracle Connections, click the Minus sign to collapse Tables under MySchema. (13) Click Refresh button and expand Tables again. You should now see all of the tables which we will use in this class. (14) To verify that you have the data, in the MySchema Query Builder tab type: SELECT * FROM animal; (15) Click the Run command button (this is the first green button.) You should see all of the data from the Animal table as shown: Prove to Dr. Pollacia that you have completed installation: 1. In your Documents folder, make a new folder called ITEC_4200, and a sub-folder called ProblemSets. 2. In SQL Developer, click File | New, then expand Database Tier. Select Database Files, and SQL File, and click OK. 3. For the name of the problem, type LASTNAME_Install_verify. Browse to save it in ITEC_4200 / ProblemSets. 4. Type a few comments that include: -- Your Name -- Date 5. Type in the following: -- verifyp1 SELECT department_id,department_name FROM departments WHERE location_id <> 1700; 6. Click anywhere within the SELECT statement and then the Green arrow to run. Click the green arrow Enter this SQL Query. The operator in the WHERE clause is<> which means “not equal to” Note: Two dashes followed by a space is the SQL comment indicator Here is the output 7. It is often useful to see line numbers. Right click in the white area just to the left of the query and select “Toggle Line Numbers”. 8. Press enter a couple of times, and enter and run the second query, verifyp2, following verifyp1: -- verifyp2 SELECT employee_id, last_name, salary FROM employees