[Q] Oracle DBA Problem List SQL/PLSQL part.

From: Lim, Young-sang <guerbois_at_yahoo.com>
Date: 1998/11/05
Message-ID: <364125A1.21FA3A3B@yahoo.com>#1/1

I am currently preparing for the oracle DBA and I found that there's a problem set related to the sql/plsql.

If you can answer for each question , then please comment on it! It may be very helpful for me to prepare OCP exam.

Thanks

If there is any suggestion on the problem please describe your idea on this board.
And give me a proper example for each question!

1.Suggest an example using Write a SELECT statement to query the database

2.Suggest an example using arithmetic calculations
3.Suggest an example which Handle null values
4.Specify alternative column headings using aliases
5.Concatenate columns
6.Edit SQL commands in the SQL*PLUS buffer

7.Sort row ouput using the ORDER BY clause 8.Enter search criteria using the WHERE clause

9.Explain the various types of funcions available in SQL 10.Identify the basic concepts of using functions 11.Include a variety of character, number, and data functions in SELECT statements
12.Explain the conversation functions and how they might be used

13.Write SELECT statements to access data from more than one table using equality and non-equality joins
14. View data that would not normally meet a join condition by using outer joins
15. join a table to itself

16.Identify the available group functions
17.Explain the use of group functions
18.Group data using the GROUP BY clause
19.Include or exclude grouped rows by using the HAVING clause

20.Write nested subqueries to query data based on unknown criteria

21.Use subqueries in data manipulation statements 22.Order data with subqueries

23.Create a SELECT statement that prompts the user to input a value at runtime
24.Define a variable that can be automatically picked up by the SELECT statement at runtime
25.Define a variable by using the SQL*Plus ACCEPT command

26.List and define basic types of data relationships 27.Read an entity relationship diagram

28.Create a table containing integrity constraints
29.Identify table naming convertions
30.Describe the datatypes that can be used when specifying column

definitions
31.Recognize the indexed that are created automatically by constraints 32.Create a table by populaing it with rows from another table

33.Describe the datadictionary views a user may access 34.Query data from the data dictionary

35.Insert new rows into a table
36.Update existing rows in a table
37.Delete rows from a table
38.Explain transaction controls and their importance

39.Add and modify columns
40.Add, enable, disable, or remove constraints
41.Drop a table
42.Remove all rows leaving the table definition intact

43.Change object names
44.Add comments to objects and view comments from the data dictionary

45.Explain the use of sequences
46.Create a sequence
47.Use a sequence
48.Modify a sequence definition
49.Remove a sequence

50.Explain the concept of a view
51.Use data dictionary views
52.Crete simple and complex views
53.Create a view with an option to enforce constraints

54.Modify a view
55.Remove a view

56.Distinguish between the indexs that are created automatically and those that are created manually

57.Identify the uses for indexs
58.Explain the index structure and why it improves query speed
59.Create a non-unique index
60.Remove an index from the data dictionary
61.Evaluate guidelines for creating and using indexes

62.Explain the concept of the database seurity model
63.Describe system privileges
64.Set up and maintain database access by using roles
65.Identify object privileges
66.Change a password

67.Grant and revoke object privileges
68.Create synonyms for ease of table access

Overview of PL/SQL

69.Determine the benefits of accesing the oracle database with Procedural Language/SQL (PL/SQL)
70.Describe basic PL/SQL program constructs

Developing a Simple PL/SQL Block

71.Declare and use variables and constants in PL/SQL 72.Assign new values to variables within the executable section

Interacting with Oracle

73.Use SELECT, INSERT, UPDATE, and DELETE commands in PL/SQL subprograms

74.Determine the outcome of SQL statements by using implicit cursor attributes
75.Control transactions within PL/SQL

Controlling Flow in PL/SQL Blocks

76.Conditionally control processing in a PL/SQL block 77.Iterate statements by using various types of loops

Processing Queries by Using Explicit Cursors

78.Explain the differences between implicit and explicit cursors
79.Use cursors to fetch rows from the database
80.Create an explicit cursor with parameters
81.Write cursor FOR loops

Error Handling

82.Identify common exceptions
83.Describe the three basic types of exceptions
84.Write exception-handling routines

Received on Thu Nov 05 1998 - 00:00:00 CST