| SQL statements or commands will be in BOLD and ITALICIZED. |
| Query results will appear in [brackets]. |
| All sql statements are assuming you are logging in to the database as user BOB. |
| UNIX commands will be underlined. |
| # | ORACLE SQL QUESTIONS | Answers |
| 1 | What is a DBA? | A DBA is a Database Administrator responsible for tuning, maintenance, setup, and design of a database. |
| 2 | What is dual? | Dual is a table with one column called dummy whose data type is VARCHAR2(1). Dual acts like a parrot; If you select user from dual; it will give you the ORACLE user you are logged in as. If you select a number from dual you will get the same number back. If you select a character array from dual, you get the same character array back.
sqlplus bob/bob SQL> select 40 from dual; [40] SQL> select 'polly want a cracker' from dual; [polly want a cracker] SQL> select user from dual; [BOB] SQL> select SYSDATE from dual; [15-APR-98] SQL> exit |
| 3 | What is an object in ORACLE? | An object in ORACLE can be a table, view, package, package body, index, constraint, sequence, etc . . . |
| 4 |
a)How do you see what tables are owned by the user_id you logged onto ORACLE with?
b) How can I see other things? c) Can I see other user's objects? |
a)select * from user_tables;
b)select * from cat; select * from user_objects; select * from user_sequences; select * from all_tables where user = 'BOB' select * from user_views; c) In most cases you can see other peoples items. Sometimes if information is not being shared you will not be able to tell what is in another person's account. select * from all_tables; select * from all_objects; |
| 5 | What is a VIEW? |
A view is a query which when called looks like a table. It is not a table. To see what views exists you can issue the following SQL statement: select * from user_views;. Or select * from all_views where owner = 'BOB'; user_views, all_tables, user_tables, all_sequences, user_sequences, user_tab_columns, all_tab_columns etc. . . are all VIEWS. |
| 6 | How do I tell what the columns in a view or a table are? | desc view_or_table_name; |
| 7 | What is a schema? | The set of objects owned by a user account is called the user's schema. |
| 8 | What is an index? | An index is used on one or more columns on a table to decrease the time it takes to find certain value(s). |
| 9 | Why might an index increase the amount of time it takes to do an insert, update, or delete. | An index may increase the time it takes to do an insert, update, or delete if the index itself needs to be updated because of the DML (data manipulation language) issued. |
| 10 | What is a constraint? | When a constraint is applied to a table, every row in the table must satisfy the conditions specified in the constraint definition. Examples of types of constraints are NOT NULL, UNIQUE, DEFAULT, and CHECK. |
| 11 | How do you see what table fields are used in indexes or constraints? | select index_owner, index_name, column_name, column_position from all_ind_columns where table_name = 'MLOAN'; select owner, table_name, constraint_name, column_name, position from all_cons_columns; The position indicates what order the column_name appears in the index or constraint. The position is important because the order of the columns determines how SQL statements should be written. It is important to note that the positions of columns in an index can change (the DBA may decide to do this). |
| 12 | What is all_tab_columns or user_tab_columns? | All_tab_columns is a view that has an entry for every column of every table of every users schema. User_tab_columns is a view that has an entry for every column of every table in a certain user's schema. When you use desc on a table to show the columns of that table the order of the columns shown is determined by the position that you can see when you look at the column_id in all_tab_columns and/or user_tab_columns. The DBA may change the order of these columns. It is very important not to assume that the columns are in a certain order and will stay that way. |
| 13 | Why do indexes and constraints have the same names? | select * from all_constraints c, all_indexes i where i.owner = c.owner and i.table_owner = c.owner and index_name = constraint_name and i.table_name = c.table_name; A UNIQUE CONSTRAINT, or NOT NULL CONSTRAINT by definition is an index; even though it is enforcing referential integrity, it also provides a fast method of looking up certain values in certain columns in a table. |
| 14 | What does it mean if an index_name or constraint_name has a "_PK_" in it? | It means that this index or constraint is on one or more columns of a table and it represents the primary key(s). |
| 15 | What is a primary key? | The primary key of a table is the column or set of columns that makes every row in that table unique. |
| 16 | What is a foreign key? | A foreign key from one table references a primary key in another table. |
| 17 |
a) What is a sequence?
b) How do you obtain the next value from a sequence? c) How do you create a sequence? d) Can you modify a sequence once you have created it? e) How do I get rid of a sequence. |
a)A sequence is like a ticket dispenser in a bakery. It usually dispense tickets starting with 1 and progressing with 2 , 3 , 4 and so on. This progression can either be ascending or descending. In the above example the increment is 1. Like the dispenser, a sequence will not give the same number to one or more people. (okay, Dispensers get new tapes and start over again with 1).
b) So, you want the next value from a sequence? The SQL command is SELECT sequence_name.nextval from dual; Once you have called for the next value from a sequence, you can use get the current value; SELECT sequence_name.currval from dual; CURRVAL can not be used before NEXTVAL in any session. c)create sequence ticket_giver; Go ahead try it!!! If you want to see where your sequence information is kept you can query the VIEW user_sequences or all_sequences: select * from user_sequences; d) In most cases the answer to this question is No. You would have to drop the sequence and recreate it with the new attributes. ; In ORACLE versions greater than 7.3.3 you may use the alter sequence sql statement to change some attributes of a sequence. e) In most cases your Database Administrator would not want you dropping sequences that he or she has created. However, if you create a sequence you may drop it. Drop sequence ticket_giver; or Drop sequence sequence_name;. |
| 18 | What is a trigger? | A trigger is like a bomb. It is specified to go off when certain events happen. A trigger may be set to check values for an insert into a row of data and alter them or cause an error. A trigger may be specified to log changes to tables or user logons, just about anything. |
| 19 | Is there an ORACLE command available through the operating system can tell you the meaning of an error number. What is it? | Yes, on UNIX you can use the command: oerr ora #. Although ORACLE errors sometimes appear to be negative numbers, they are positive numbers! |
| 20 | What can you tell me about EXP and IMP commands? |
You can call exp and imp respectively from the AIX command line to export and import data from the database. For more information about these commands type either
exp help=y or imp help=y. These commands are extremely useful for saving data sets for testing certain conditions (like proving that an error in a program has been fixed.) Exports include sequences, privledges, views, tables, packages, indexes, constraints, grants, database links, and table_data. You can choose to export a table definition and not the table data or to export all the tables or just one. Imports can be used to transfer tables, etc. from one user to another. |
| 21 | What are some basic Data Manipulation Commands (DML)? | INSERT UPDATE TRUNCATE DELETE CREATE DROP, etc . . . |
| 22 | What does DROP do? | It removes an object from the database such as a table, sequence, view, package, etc. . . Syntax: drop table table_name; drop sequence sequence_name; |
| 23 | What does ALTER do? | Alter allows you to add to or modify a database object. |
| 24 | Is ORACLE SQL case sensitive or not? |
Trick question. When you access views you have to give field values in uppercase like select * from all_tables where table_name = 'TICKET_TAKER';. When you are issuing a data modification language command you can reference a table in any case that you want. Data is case sensitive. |
| 25 | What is the difference between a select and a bind variable? | select variables are retrieved and bind variables are certain values you specifiy in the where clause. |
| 26 | How do you tell what date and time an ORACLE system thinks it is? | select sysdate from dual; |
| 27 | If you have a large number of rows in a table and you just need to know the unique values of a column what SQL statement would you use? | select distinct (some_column) from some_table; |
| 28 |
What query would you execute if you wanted to know how many times each value appears in the database? |
select some_column, count(*) from some_table group by some_column; |
| 29 |
What if you just wanted to know what value appeared the most times in a specific field in the database? |
select some_column, count(*) from some_table group by count(*) desc where rownum < 2; |
| 30 |
What if you wanted to know what values appear more than 15 times in a table? |
select some_column, count(*) from some_table group by count(*) having count(*) > 15; |
| 31 | What is something very important to remember about using GROUP BY? | Your group by items must be in the exact order of the select items. |
| 32 | What query would you use if you wanted the minimum data value in a select field in a table? | select min(some_column) from some_table; |
| 33 |
What is wrong with the following query:
select min(some_column), some_other_column from some_table where something = :something; |
The value in the some_other_column field may not come from the same row as the minimum value. |
| 34 |
What about select min(some_column), max(some_other_column) from some table where something = something; |
There is nothing wrong with this query. |
| 35 | If you wanted to convert the character string '23-JAN-98' into an ORACLE variable of type DATE what command would you use? | to_date('23-JAN-98','DD-MON-YY') |
| 36 |
If you wanted a get a date from the database in a character array format what command would you use? Specifically the format
Year month day hour min sec and am or pm. |
To_char(date,'YYYYMMDD:HH.MI.SS A.M.') |
| 37 | What if you wanted to some all of the positive integer values from some column on some table | select sum(some_field) from some_table where some_field > 0; |
| 38 | What is an outer join and how is it used? | To select columns from two or more tables whether or not data exists in one of the tables you are querying against. |
| 39 | What is dynamic SQL? Is it good or bad? Why is it good or bad? | Dynamic SQL is good only if it does not fill up the SQLAREA. If bind variables are stored as :variable it is okay. |
| 40 |
Why do we give a table another name like select * from mloan m? |
This is done for many reasons:
One reason is that we may use a table more than once in a query and the references may be distinguished by assigning different letters to them: select a.ltv_ratio, b.ltv_ratio from mloan a, mloan b where . . . . Another reason is that we may need to reference a table in the SELECT portion in an EXISTS clause: select a.sale_typ from mloan a where exists (select 'X' from loan l where l.account_nbr = a.account_nbr.) But the third reason is the most important, we do not want to fill up the SQLAREA. |
| 41 | What is the SQLAREA and why is it so important? |
The SQLAREA is an important part of the database. Every time a query is issued it is stored in the SQLAREA unless it matches exactly to a query already in the SQLAREA letter by letter case-sensitively. The only way to clear the SQLAREA is to flush it (The DBA does this.)
Using a one character letter to reference a table takes up less room in the SQLAREA. Why do we want to use the least amount of letters possible? Because if a query is already in the SQLAREA it can be re-used and it will take less time to execute because it has already been run and because it has already been parsed and stored in the SQLAREA. It is ideal to have as many re-usable queries in the SQLAREA as possible. When the SQLAREA fills up, newer queries start writing over older queries. ORACLE version 7.3 will CRASH if the SQLAREA is constantly getting written over and over again and consequently becomes fragmented; flushing the area on a regular basis will not help. It is ORACLE's position that if the SQLAREA is written over too often, let us say by issuing 50,000 almost identical SQL queries that this is not a bug, but a bad program design. |
| 42 | What keyword do you need to drop a table when other tables rely on it. | Cascade |
| 43 | Do null values in certain columns in a table effect query performance times? | YES - they increase the time it takes to perform a query!! |
| 44 | How do you concatenate two values in SQL? |
|| (double pipes) concat is not valid.
select user || sysdate from dual; [BOB15-APR-98] select user || ' foofoo ' || sysdate from dual; [BOB foofoo 15-APR-98] |
| 45 | What is rownum? | ROWNUM can be used to select , delete, update some number of rows minus one from a table. An order by clause may not be used with ROWNUM. ROWNUM must always be specified as being greater than 1. You can not where rownum > 3; It will not work. Update mloan set sale_typ = 'C' where rownum < 500; Updates 499 rows in no specific order. Sometimes the order will be the order that the rows were entered into the database. |
| 46 | What is the best manner to delete A LARGE NUMBER OF ROWS from a table? | In small portions using rownum if necessary. Deleting a large number of rows at one time takes up ORACLE resources and can increase the time it takes to delete the rows (as compared to the time it would take to delete 500 rows at a time). Issuing a commit statement after every delete will free up some of the ORACLE resources and make further deletes faster. |
| 47 | What is commit? Why do we use it? |
Commit saves the current database state. When you issue certain ORACLE commands other users can not see the changes until you issue the commit command or exit the SQL session you have opened.
If you do not issue a commit and you make a mistake, (as long as you do not truncate or drop a table) you can issue a rollback command and the database will be restored to the point it was at when you started the SQL session or issued the last commit. To restore the database to this state, use the rollback command: rollback; You can also specify a commit point to rollback to by doing commit savepoint1; then if you need to you can issue the command rollback savepoint1; |
| 48 |
a)What is the fastest way to delete ALL OF THE ROWS in a table?
b)Is this recoverable? (rollback) |
a)Truncate table some_table; This command is unrecoverable. It removes all rows from a table.
b) NO. |
| 49 | What is the fastest query you can execute for just checking to see if a value exists in a certain column on a certain table? | select 'X' from dual where exists (select 'X' from some_table where some_column = :some_value) |
| 50 | What is that 'X' thing? | 'X' represents TRUE or FALSE. You could replace 'X' with 'apple' or 'foobar' or some other entity as long as it has single quotes around it and it is not an ORACLE keyword. |
| 51 | Why do you select that 'X' thing from dual? | You select 'X' from dual when you already have all of the table values selected that you need. |
| 52 | When is it appropriate to use the ORACLE keyword UNIQUE? | It is only appropriate to use the ORACLE keyword UNIQUE if you are creating a unique index; one that will only allow the use of different values in a column on a table. DO NOT USE UNIQUE in place of DISTINCT! |
| 53 | Can you use GROUP BY and DISTINCT in the same SIMPLE query? | NO. There is not reason to. I say SIMPLE query because you can use the terms in different sub-clauses in the same query. |