Installing and testing SQL Developer; Few exercises included
This semester we will study a scripting language that was designed to be executed directly by the Oracle Database Server: PLSQL (Procedural Language/Structured Query Language). More about this scripting language will be presented in the next laboratories. PLSQL scripts can be written in any .sql file and executed as usual (by adding in SQL Plus the sign @ before the path of the .sql file). However, Oracle developed an IDE that will help us with syntax coloring, underlining misspelled words or indicating where an error appeared during verification of the script. The tool is called SQL Developer and in order to use it you have to check that:
- You have Oracle 11g express edition (XE) installed - for this just type SQL in your search bar in windows 10 and check if "SQL Command Line" is installed - if this is the case you're set for this step. if not, please download and install Oracle 11g XE from this link: http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html
- Create a student account (if you don;t already have one) as described here: https://profs.info.uaic.ro/~bd/wiki_en/index.php/Setting_the_lab_environment
- Check if you jave JDK installed (in windows command, just type "java" and press enter. If there are no errors, continue to the next step; otherwise install JDK from here: http://www.oracle.com/technetwork/java/javase/downloads/index.html
- Download SQL Developer from this link: https://profs.info.uaic.ro/~vcosmin/pagini/resurse_bd/SQLdeveloper/sqldeveloper-126.96.36.199.64-no-jre.zip (or you can find a newer one on the Oracle web site ?)
- Unzip it in a folder of your choice and start it. You will be asked where you have your JDK installed (is probably in Program Files/Java/JDK ..
- Press the green + button in the upper left corner of your IDE to create a new connection to the Oracle Database Server.
- Add the information about your account (check out this: https://profs.info.uaic.ro/~vcosmin/pagini/resurse_bd/SQLdeveloper/conexiune.png)
- If the connection cannot be established is probably because you didn't have the SQL server installed, the account created or one of these services isn't running: OracleServiceXE & OracleXETNSListener (check them in the "Services" app in Windows).
After installing SQL developer you will be able to see all tables from the previous semester (under +tables in the left pane). You need to delete all those tables. After deleting them, open this file and run it: https://profs.info.uaic.ro/~vcosmin/pagini/resurse_bd/database_deploy_script.sql . This will create a set of 6 new tables and populate them with different courses from Romanian Curricula [btw, if anyone is interested in translating the courses I would appreciate it]. The data in the tables is randomly generated so, don't expect them to be similar with the ones your other classmates have.
- Look through the creation script - where the tables are added. Observe the keys and the links between tables. Which are the entities in this database ? What about the associations ?
- Build the relational schema of the database. For this, open the Browser window (View->DataModeler->Browser) and in the "Relational Models" right click and select a new relational model. A new Canvas will appear. To populate the canvas select File->DataModeler->Import->DataDictionary and a dialog will be opened. Select the connection that you created, the schema (Probably STUDENT), and the tables that you want to be included in the diagram. Press finish and observe the newly created schema. What is the meaning of P ? F ? U ? the red dot near some fields ?
- How will you add a constraint that will not allow a student to have to grades at the same course ?
- Find the group in the faculty that has the highest cohesion (has the most friendship relations inside that group related to the number of the students of the group).
- Try to delete a student.
- Delete a student :D How would you do that automatically ?
- Display all the students having a scholarship of at least 1350.
A view is a virtual table that is obtained through a select operation over the existing tables. The views are not physical created on the drive, they are only registered in the the table "user_views". In order to create a new view you can use the following syntax:
CREATE VIEW name_of_view AS SELECT columns FROM table [WHERE some conditions...]
Let's create a view that will filter all the students that have a scholarship of at least 1350:
CREATE VIEW best AS SELECT * FROM students WHERE scholarship>1350;
Find out what are the fields in the table user_view and check out where the select is stored. Is it exactly as you expected ?
Let us now obtain all the informations in the view:
Execute the following statement:
SELECT * FROM best;
If you will modify the table, the view will also modify itself:
UPDATE students SET scholarship=1400 WHERE id=1; SELECT * FROM best;
As you can see, now in the "best" view you can also find the student havin the ID 1. If we update his scholarship to 1200 for example, it will automatically be eliminated from the view. What if we update the information directly in the view ? Try to:
- Update the scholarship of a student (based on his ID) from the view in order to be eliminated from the view. (UPDATE best SET bursa=1000 WHERE id=.... -- choose a valid ID from your view; as explained they might differ from your classmates);
- Update the scholarship of the same student (in the view) and add him a scholarship of 1400. Explain !
- Update the scholarship of the same student (in the students table) and add him a scholarship of 1400. Explain !
A View behaves exactly as a table, even though is only a virtual one. We can insert data into a view. For example, you can try to execute the following statements:
insert into best values(1998,'123AB1','f1','l1',3,'B1',200,sysdate, 'firstname.lastname@example.org', sysdate, sysdate); insert into best values(1999,'123AB2','f2','l2',2,'B2',1400,sysdate, 'email@example.com', sysdate, sysdate);
Both Operations are successfully; however, only one of the newly inserted students is displayed in the view. Why ?
Are both students in the table students (do they exist) ?
Delete the newly inserted rows (the students having IDs 1998 and 1999).
As you could see, even though you inserted the student with id 1998 into the view, he is not displayed there. In order to forbid this behavior and allow insertion only of data that will actually be in the view, you can use the "with check option;" keywords in the definition of the view (at the end):
CREATE OR REPLACE VIEW best AS SELECT * FROM students WHERE scholarship>1350 WITH CHECK OPTION;
Now try again to add the students in the view (the previous insert commands).
As you could see, to re-define a view, we used "CREATE OR REPLACE". These words are used by many definitions in PLSQL and will allow the creation (in case the entity does not exist) or replacing of the entity you are trying to define/redefine. Is faster that deleting the view and re-creating it with only the "CREATE" keyword (although that is also possible).
To delete a view, you can simply drop it (as in "DROP VIEW best;");
Try the following:
- Re-create the view to display only the fname, lname and scholarship values;
- Try inserting data into this view; What is the problem and can you find any solution ?
- Create a view over a join of all tables except friendships;