Additional Notes:
Where clause à not allow group function such as AVG()
No Order by clause in create view or subquery
Data Dictionary :
user_col_privs_made
user_indexes
user_ind_columns
table_privileges
all_col_privs_recd
user_constraints
Basic Loop --- EXIT
NULL - is null or is not null
Constraints
CONCAT() or ||
The following information is that I had form some one:
Please pay attention to the column title display:
SELECT last_name|| ‘ ‘ || first_name “Employee”
FROM s_emp;
how the title is displayed ?
Pay attention to the Where clause, be ware of the small difference as we talked in the class.
WHERE UPPER(column_name) = UPPER(‘&name’)
Comparison with NULL value: should be IS NULL or IS NOT NULL.
Match character % and _ , and ESCAPE usage.
What is the result of :
SELECT TRUNC(‘05-JUL-91’, ‘YEAR’)
FROM dual;
ans: 01-Jan-92
Beware the usage of functions.
how to write insert statment with sequence number.
Ans: sequence_name.NEXTVAL
How to create a sequence number. Ans: sequence_name.NEXTVAL
which join is good for the condition that is not match.
Ans: outer join
how to write a comment to dictionary.
12.Which sign do you use to change line in SQL*Plus
#
*
/
-
$
Ans: -
You are asked to create table from the table already exist, the old table has 5 NN column, you only have 3 insert values, the answer would be "violate not null constraint"
Ans: yes
Find error from create table syntax: You need pay attention to column name , one start with #, the other start with number, the rule in column name should be must starting with a letter.
truncate and delete: if you do not care whether to rollback the deleted data, which one are you going to select.
16. basic loop, for loop and while loop in cursor
basic loop need exit when
for loop do not use open, fetch or close and while loop end with condition is no longer true.
17. be familiar with syntax of creating index, sequence and synonym.
18. why it cannot be executed: main query has "=" and subquery has ">30000".
19. PL/SQL error handling:
where to raise exception: ans: execution section
where to define user_defined exception: ans: declare section
20.You (the user) created synonum for a table, what did he accomplish?
21. The DBA created a table synonum, what happens?
22. Display patient_name whose age > patients average age and whose
doctor has "s" in their names.
You were given two tables, patient_table and doctor_table.
Patient_table columns: Patient_ID (PK), Patient_name, Birthday,
doctor_ID(FK referencing ID in doctor_table),
Doctor_table columns: Doctor_id, Doctor_name, and something else.
Note: Patient age is calculated using 'months_between(sysdate-birthday)'
23. You were asked to created a table according a table instance chart.
The table has a FK referencing a PK in another table_A.
But table_A has not been created yet.
For Loop syntex used with Cursor, don't need to remember the syntex but need to know the three things associated with it;
25. Basic loop needs "Exit When" to end it;
While loop: a condition is true for the loop to execute. It ends when the condition is no longer true.
27. %notfound, when it is true;
28. When can records be changed through a view.
29. Be very clear about "and", "or".
When using "Insert into....., values ......" syntax, make sure NotNull constraint is not violated on any columns when they have them.
All the information on a view must come from the underlying objects. A view can't have additional information in it if the information is not in the base tables.
The following is your work, hi, thanks!
32. Which cursor attribute evaluates to TRUE when an unsuccessful fetch
has
occurred?
A. %ISOPEN
B. %FOUND
C. %NOTFOUND
D. %ROWCOUNT
33. Evaluate thisn clause:
WHERE price 6.00 or price =5.00
which operator could be used to eliminate the OR operator from this
cclause?
A. LIKE
B. IN
C. AND
D. >=
E.
IS NULL |
