Oracle FAQ (2)

Oracle Q & A (2) Oracle Q & A (1) http://www.bkjia.com/database/201309/242497.html 11. What is the use of nvl functions? NVL (string1, replace_with) function: If string1 is NULL, The NVL function returns the value of replace_with; otherwise, the value of string1 is returned. Note: string1 and replace_with must be of the same data type unless the TO_CHAR function is explicitly used. For example, NVL (TO_CHAR (numeric_column), 'some string') Where numeric_column represents a numerical value. 12. What is the difference between the database name and the database instance name? A database name is an internal identifier used to distinguish a database. It is a parameter stored in the database control file in binary mode. This parameter cannot be modified after the database is created. After the database is created, it is written to the database parameter file pfile or Spfile. The format is as follows: db_name = "orcl" db_domain = dbcenter. toys. cominstance_name = orclservice_names = orcl.dbcenter.toys.com the database instance is the entity for operating the database. You can interact with the database through the instance. The instance name is used to identify the database instance. After the database is created, the Instance name can be modified. It is also in the database parameter file pfile or Spfile. The format is as follows: instance_name = orclservice_names = orcl.dbcenter.toys.com the Database Name and Instance name can be the same. When a database corresponds to an instance, it is set to the same easy to identify database. 13. Briefly describe the role of profile? Profile is a set of command sets for password restrictions and resource restrictions. When a database is created, oracle automatically creates a profile named default. If the profile option is not specified for the created user, oracle assigns the default option to the user. Account locking: Overview: specifies the maximum number of times that a user can enter a password when logging on to the account. You can also specify the user's locking time (days). Generally, this command is executed as a dba. Example: If scott is specified, the user can only log on three times at most, and the lock time is 2 days, let's see how to implement it. Create profile file SQL> create profile lock_account limit failed_login_attempts 3 password_lock_time 2; SQL> alter user scott profile lock_account; unlock an account: SQL> alter user tea lock_account unlock; 14. briefly describe the usage of stored procedures? Create: create or replace procedure a_proc (a_name IN varchar2, a_num OUT number) isnum_1 number; num_2 number; beginselect stu_id, age into num_1, num_2 from stuinfo where stu_name = a_name; a_num: = num_1 * num_2; end; call: SQL> set serveroutput onSQL> declare tname varchar (20); 2 tnum number; 3 begin 4 tname: = 'gggzz '; 5 a_proc (tname, tnum); 6 dbms_output.put_line (tnum); 7 end; 8/15. how do I display the first three characters of all employees' names in uppercase? 16. How can I display the employee name in lowercase after the first letter? 17. How can I display the name and employee ID of an employee who has been in service for 10 years? 18. How do I display the name and employee ID of the employee who joined the last ten days of the month? 19. What common shortcut keys are available in the cmd command window? Then return the previous command, then return the next command, → return the last command one by one, F8 return the previous command one by one, however, when the cursor is in the first character F3, only the previous command F7 is returned. In the pop-up command history window, press the ↑ ↓ button to select the command and press enter to execute the selected command. For commands that are too long, you can use → keys to view them in the cmd window. Switch home to the First Command, end to the last one, and view PageDown and PageUp pages. F9 enter the command number, which is defined by the Command record in F7. If you want to execute a command multiple times and it is a long command, you can use this technique. It may be more efficient. Alt + PrintScreen for the cmd window. Insert converts the cursor to the insert state. When you type a character, the current character of the cursor can be replaced. 20. How do I query duplicate records in a table? A. Search for redundant duplicate records in the Table. duplicate records are based on a single field (peopleId) select * from people where peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1) B. Delete unnecessary duplicate records in the table, repeat records are determined based on a single field (peopleId). Only the records with the smallest rowid are retained: delete from people where peopleId in (select peopleId from people group by leleid having count (peopleId)> 1) and rowid not in (select min (rowid) from people group by peopleId having count (peopleId)> 1) c. Search for redundant duplicate records in the table (multiple fields) select * from vitae awhere (. peopleId,. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1) d. Delete unnecessary duplicate records (multiple fields) in the table ), delete from vitae awhere (. peopleId,. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1) and rowid not in (select min (rowid) from vitae group by peopleId, seq having count (*)> 1) e. Search for redundant duplicate records (multiple fields) in the table, excluding the records with the smallest rowid select * from vitae awhere (. peopleId,. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1) and rowid not in (select min (rowid) from vitae group by peopleId, seq having count (*)> 1)