INFS2200/7903 – Relational Database Systems

Semester 2 - 2011

FAQ

 

SQL* Plus Questions

Q: My query returns too many records and I can see only part of the results in SQL * Plus. How to see the entire result?

A:  There are two ways to solve this:

-          Dump the output to a text file, use the SPOOL command (google for Oracle SPOOL command)

-          Increase the command buffer, in SQL Plus go to Options/Environments and look for screenbuffer. This way works only if you are in the labs.Will not work if you are connecting to Oracle remotely via SSH.

Q. Trigger has created but with complication erros?

A: you can tell SQL * Plus to show the detailed error message with the command SQL> SHOW ERRORS

Q: I have successfully connected to SQL* Plus remotely via my laptop. I want to execute a script file which is located on my local harddisk but I encountered the error SP2-0310: unable to open file

A: You need to upload the script file to the server first. You can upload using any FTP transfer clients. E.g using Putty: Windows Start --> Putty SSH --> PSFTP, or using OpenSSH: click the button New File Transfer Window
Once the file is uploaded, you can execute it:
SQL> @yourfile.sql
that command will look for the file in your student folder

 

Table Definition Questions

Q: I have a previous version of the assignment sheet, and in that version the database figure shows that EmpNo is mapped to ClientNo in the PURCHASE Table and ClientNo in the CLIENT Table is mapped to ServedBy in the PURCHASE Table. Is this correct?

A: They should be interchanged, that’s our mistake. Sorry.

Q: How to name a NOT NULL constraint?  My SQL command is ALTER TABLE  EMP MODIFY (EName NOT
NULL); but this command does not give any option to specify the constraint name.

A:  There are many ways to define a NOT NULL constraint. The alternative ways below would allow you to specify a constraint name

ALTER TABLE  EMP ADD CONSTRAINT ABC CHECK (EName IS NOT NULL);

OR

ALTER TABLE EMP MODIFY (EName  VARCHAR2(20) CONSTRAINT ABC NOT  NULL);

Q: How to see what columns and data types defined for a table?

A: use the DESC command. Example: SQL> DESC EMP

Q: When I try to select existing constraints from USER_CONSTRAINTS on a table, it shows “no rows selected”, but my command is correct.

A: Even though the command is correct, the table name must be in upper case as follows:

select * from user_constraints where table_name = ‘DEPT’;

 

Index Questions

Q: I believe Tasks 4a and 5a hint are hinting to me that I should be using self-joins so that we can show performance improvement in Tasks 4b and 5b. The problem is, my queries do not use self-joins; they still produce the correct output as required by Tasks 4a and 5a, but they cannot show performance improvement in tasks 4b & 5b . What should I do?

A: Yes we expected students to use self-joins.  It’s our mistake we didn't clearly state that students MUST use a self-join. Therefore, we decided that you will get the full marks no matter if you use a self-join or not, as long as your query produce the correct output. In case your query output is correct, and the query does not show the performance improvement after creating the index because it's not a self-join, you will also get the full marks. However, note that you still need to show the query details in your report (i.e. your queries, query outputs, commands to create the indexes, execution time before and after creating the indexes).