Oracle SQL FAQ

Directory

Q1. how to create a table?

Q2. how to delete a table?

Q3. how to create a view?

Q4. how to delete a view?

Q5. how to add fields to a table?

Q6. how to delete a field in a table?

Q7. how to add constraints to a field?

Q8. how to remove the constraints on a field?

Q9. how to add a primary key to a table?

Q10. how to delete the table's primary key?

Q11. how do I add a foreign key to a table?

Q12. how to delete a foreign key of a table?

Q13. how to add check to a field?

Q14. how do I remove the check on the field?

Q15. how do I set the default value for a field?

Q16. how do I remove the default value of a field?

Q17. how to create an index?

Q18. how to delete an index?

Q19. how to create a user?

Q20. how to delete a user?

Q21. how to grant the object permission (Object privileges) to the user?

Q22. how to revoke object permissions from users?

Q23. how to grant the role permission (role privileges) to the user?

Q24. how can I revoke role permissions from a user?

Q25. how do I grant system privileges to users?

Q26. how to revoke system permissions from users?

Q27. how to create a sequence?

Q28. how to delete a sequence?

Q29. how to obtain the sequence value?

Q30. how to create a role?

Q31. how to delete a role?

Q32. how to grant the object permission (Object privileges) to the role?

Q33. how to revoke object permissions from a role?

Windows.How can I grant role privileges to a role?

Q35. how to revoke role permissions from a role?

Q36.How can I assign SYSTEM privileges to a role?

Q37. how to revoke system permissions from a role?

Q38. which of the following statements are not equal to the condition?(Problem :))

Q39.like clause usage?

Q40. could you give a simple example of where subquery?

Q41.what are common Oracle string processing functions?

Q42.what digital processing functions does Oracle Support?

Q43. how to obtain the current date and time of the database server?

Q44. how to convert a string to a date or time format?

Q45. what are common date functions?

Q46. Can you give an example of decode function usage?

Q47. Can you give an example of group by, having, and order by usage?

Q48.what common data dictionaries Does Oracle have?

Q49. how can I insert a date or time field into a date field?

Q50. can I introduce the usage of connect?

Content

Q1. how to create a table?

A. Create Table maid

(

Rm_int_field integer,

Rm_str_field varchar2 (64)

)

Create Table maid

(

Rd_int_field integer,

Rd_str_field varchar2 (32)

)

Q2. how to delete a table?

A. Drop table maid;

Q3. how to create a view?

A. Create or replace view royal_mdview

Select t1.rm _ str_field as F1, t2.rd _ str_field as F2 from Fig T1, fig T2

Where t1.rm _ int_field = t2.rm _ int_field

Q4. how to delete a view?

A. Drop view maid;

Q5. how to add fields to a table?

A. alter table maid add rm_int_field integer;

Q6. how to delete a field in a table?

A. alter table maid drop column rm_int_field;

Q7. how to add constraints to a field?

A. alter table maid modify rm_str_field not null;

Q8. how to remove the constraints on a field?

A. alter table maid modify rm_str_field NULL;

Q9. how to add a primary key to a table?

A. alter table maid add constraint pk_royal_mtable primary key (rm_int_field );

Q10. how to delete the table's primary key?

A. alter table royal_mtable drop constraint pk_royal_mtable cascade;

Q11. how do I add a foreign key to a table?

A. alter table maid add constraint fk_royal_dtable foreign key (rm_int_field) References maid (rm_int_field) on Delete cascade;

Q12. how to delete a foreign key of a table?

A. alter table maid drop constraint fk_royal_dtable;

Q13. how to add check to a field?

A. alter table royal_mtable add constraint chk_rm_str_field check (rm_str_field in ('y', 'n '));

Q14. how do I remove the check on the field?

A. alter table maid drop constraint chk_rm_str_field;

Q15. how do I set the default value for a field?

A. alter table fig ';

Q16. how do I remove the default value of a field?

A. alter table royal_dtable modify rd_str_field default null;

Q17. how to create an index?

A. create unique index idx_royal_dtable on royal_dtable (rm_int_field );

Q18. how to delete an index?

A. Drop index idx_royal_dtable;

Q19. how to create a user?

A. create user testuser identified externally default tablespace users temporary tablespace temp profile default;

Q20. how to delete a user?

A. Drop user testuser cascade;

Q21. how to grant the object permission (Object privileges) to the user?

A. Grant select, insert, update, delete, alter on royal_mtable to testuser;

Grant insert, update, delete on royal_dtable to testuser;

Grant select, alter on royal_dtable to testuser with grant option;

Q22. how to revoke object permissions from users?

A. Revoke select, insert, update, delete, alter on royal_dtable from testuser;

Q23. how to grant the role permission (role privileges) to the user?

A. Grant connect to testuser with admin option;

Grant DBA to testuser;

Q24. how can I revoke role permissions from a user?

A. Revoke DBA from testuser;

Q25. how do I grant system privileges to users?

A. Grant alter any table to testuser with admin option;

Q26. how to revoke system permissions from users?

A. Revoke alter any table from testuser;

Q27. how to create a sequence?

A. Create sequence rm_int_field_seq

Minvalue 1

Max value 999999999999999999999999999

Start with 1

Increment by 1

Cache 10

Order;

Q28. how to delete a sequence?

A. drop sequence rm_int_field_seq;

Q29. how to obtain the sequence value?

A. Select rm_int_field_seq.nextval from dual;

Q30. how to create a role?

A. Create role testrole;

Q31. how to delete a role?

A. Drop role testrole;

Q32. how to grant the object permission (Object privileges) to the role?

A. Grant select, insert, update, delete, alter on royal_mtable to testrole;

Q33. how to revoke object permissions from a role?

A. Revoke select, insert, update, delete, alter on royal_mtable from testrole;

32. How can I grant role permissions (role privileges) to a role?

A. Grant DBA to testrole;

Q35. how to revoke role permissions from a role?

A. Revoke DBA from testrole;

Q36. how to assign the system permission (SYSTEM privileges) to a role?

A. Grant create table to testrole;

Q37. how to revoke system permissions from a role?

A. Revoke create table from testrole;

Q38. which of the following statements are not equal to the condition?(Problem :))

A. Select * from Maid where rm_str_field! = 'Y ';

Select * from Maid where rm_str_field ^ = 'y ';

Select * from Maid where rm_str_field <> 'y ';

Q39.like clause usage?

A. Select * from Maid where rd_str_field like '% Y % ';

Select * from Maid where rd_str_field like '_ y % ';

Q40. could you give a simple example of where subquery?

A. Select * from Maid where rm_int_field in (select rm_int_field from Maid where rm_str_field not in ('y', 'B '));

Q41.what are common Oracle string processing functions?

A. | connects two strings; length String Length; TRIM/ltrim/rtrim truncates the left (right) specified string (including empty string); lower/Upper converts the string to small/upper case, and so on.

For example:Select rm_int_field | '--' | rm_str_field | 'yes' from royal_mtable;

Q42.what digital processing functions does Oracle Support?

A. oracle supports all common numeric functions, including (but not limited) +,-, *,/, Abs, cos, exp, LN, log, Mod, power, round, sin, Sinh, SQRT, tan, trunc, AVG, Count, Max, min, sum, greatest, and least.

For example:

Select greatest (3, 4, 5) * 4 from dual;

Select power (2, 3) from dual;

Q43. how to obtain the current date and time of the database server?

A. Select sysdate from dual;

Select to_char (sysdate, 'yyyy-MM-DD hh: MI: ss') from dual;

Q44. how to convert a string to a date or time format?

A. Select to_date ('2017-11-27 ', 'yyyy-MM-DD') from dual;

Select to_date ('2017-11-27 09:28:55 ', 'yyyy-MM-DD hh: MI: ss') from dual;

Q45. what are common date functions?

A. next_day last_day add_months months_between and so on.

For example:Select last_day (sysdate) from dual;

Q46. Can you give an example of decode function usage?

A. The format of the decode function is decode (value, if1, then1, if2, then2...., else ). HypothesisThe following data exists in the table "royal_dtable:

Rd_int_field rd_str_field rm_int_field
--------------------------------------
1 Royal 1
2 Bill 2
3 Joy 1

Observe the following SQL statement output results.
Select decode (rd_str_field, 'royal', 'royaltest', 'bill ', 'billgates', rd_str_field) as DC from royal_dtable;
DC
---------
Royaltest
Billgates
Joy

Q47. Can you give an example of group by, having, and order by usage?

A. SQL> select * From royal_mtable;

Rm_int_field rm_str_field
-------------------------
1 y
2 N
3 Y

SQL> select * From royal_dtable;

Rd_int_field rd_str_field rm_int_field
--------------------------------------
1 Royal 1
2 Bill 2
3 Joy 1

SQL> select rm_int_field, sum (rd_int_field) from Fig group by rm_int_field having sum (rd_int_field)> = 2 order by sum (rd_int_field) ASC;

Rm_int_field sum (rd_int_field)
------------------------------
2 2
1 4

Q48.what common data dictionaries Does Oracle have?

A. user_tables (tabs), user_tab_columns (Cols), user_views, user_sequences (SEQ), user_constraints, user_cons_columns, user_tab_comments, user_col_comments, user_indexes (IND) user_ind_columns, user_users, dba_users, all_users, user_tablespaces, and so on.

Example: Select * From user_constraints where constraint_name = 'fk _ royal_dtable ';

Q49. how can I insert a date or time field into a date field?

A. insert into royal_mtable (rm_int_field, rm_str_field, rm_date_field) values (9, 'y', to_date ('2017-05-23 ', 'yyyy-MM-DD '));

Insert into maid (rm_int_field, rm_str_field, rm_date_field) values (10, 'y', to_date ('2017-10-10 8:23:33 ', 'yyyy-MM-DD hh: MI: SS '));

Q50. can I introduce the usage of connect?

A. the connect by clause provides a means to traverse the "tree.

Suppose there is a table like this: Create Table royal_treetable (ID integer, parent_id integer, name varchar2 (32 ));

The table contains the following data:

Id parent_id name
-----------------
2 1 aaa
3 1 bbb
4 2 ccc
5 2 ddd
6 4 eee
8 7 ggg

If we need to start from the Record Name = 'Eee 'and look up all records with parent-child relationship, we can execute the following SQL statement:

Select * from maid start with name = 'Eee 'connect by ID = prior parent_id;

ID parent_id name
---------------
6 4 EEE
4 2 CCC
2 1 AAA