Oracle Tutor

ER Diagram - Entity Keys

2011-10-03T20:20:00.001-07:00

Well... An entity is defined by its attributes. Furthermore, each entity occurrence can be uniquely identified, by using an attribute or a combination of attributes as a key.

The primary key is the attribute (or group of attributes) that serve to uniquely identify each entity occurrence. Consider the problem that might arise if the name and address of an individual were used as the primary key for identifying the patients within a hospital.

Take the example of a patient called David Smith living at 23 Acacia Avenue. He has a son also called David Smith living at the same address.

Name and Address would not necessarily provide a unique identifier and confusion could easily arise, potentially creating a mix up with the patient records.

For this reason, in a hospital system patients each have a Patient Number as their primary key.

ER Diagram - Entity Naming

2011-10-03T20:19:00.000-07:00

Entity names are normally single words and the name chosen should be one familiar to the users. The entity name can include a qualifier in order to clarify their meaning. However, if different names are currently used to describe a given entity in different areas of the organization then a new one should be chosen that is original, unique and meaningful to all of the users.

ER Diagram - Notation Symbol

2011-10-03T20:18:00.000-07:00

Entity relationship diagramming uses a standard set of symbols to represent each of these defined data groups and then proceeds by establishing the relationships between them. The first of these symbols is the soft-box entity symbol.
An entity is something about which data will be stored within the system under consideration. In this example the data group invoice can be identified as a system entity.

The other main component on a data model is the relationship line. A Relationship is an association between two entities to which all of the occurrences of those entities must conform.
The relationship is represented by a line that joins the two entities, to which it refers. This line represents two reciprocal relationships:That of the first entity with respect to the second, and that of the second entity with respect to the first.

Entity relationship diagramming is all about identifying entities and their relationships and then drawing a diagram that accurately depicts the system. This applies equally to the design of a new system or the analysis of an existing one.

The end result of entity relationship diagramming should be a clear picture of how information is stored and related within a proposed, or existing, system.

What is Entity relationship diagram?

2011-10-03T20:17:00.000-07:00

What is Entity relationship diagram?

Entity relationship diagramming is a technique that is widely used in the world of business and information technology to show how information is, or should be, stored and used within a business system.

The success of any organization relies on the efficient flow and processing of information.

Creating Combined Queries in Oracle SQL

2011-05-02T03:54:00.000-07:00

SQL queries are combined using the UNION operator. Using UNION, multiple SELECT statements can be specified, and their results can be combined into a single result set.

Using UNION
Using UNION is simple enough. All you do is specify each SELECT statement and place the keyword UNION between each.

Let's look at an example. You need a report on all your customers in Illinois, Indiana, and Michigan. You also want to include all Fun4All locations, regardless of state. Of course, you can create a WHERE clause that will do this, but this time you'll use a UNION instead.

As I just explained, creating a UNION involves writing multiple SELECT statements. First look at the individual statements:

SELECT cust_name, cust_contact, cust_email

FROM Customers

WHERE cust_state IN ('IL','IN','MI');

cust_name cust_contact cust_email

----------- ------------- ------------

Village Toys John Smith sales@villagetoys.com

Fun4All Jim Jones jjones@fun4all.com

The Toy Store Kim Howard NULL

Now Combne

SELECT cust_name, cust_contact, cust_email

FROM Customers

WHERE cust_state IN ('IL','IN','MI')

UNION

SELECT cust_name, cust_contact, cust_email

FROM Customers

WHERE cust_name = 'Fun4All';

cust_name cust_contact cust_email

--------- ------------ ----------

Fun4All Denise L. Stephens dstephens@fun4all.com

Fun4All Jim Jones jjones@fun4all.com

Village Toys John Smith sales@villagetoys.com

The Toy Store Kim Howard NULL

what is Cursors in Oracle , Cursors and Web-Based Applications

2011-05-02T03:52:00.000-07:00

SQL retrieval operations work with sets of rows known as result sets. The rows returned are all the rows that match a SQL statement—zero or more of them. Using simple SELECT statements, there is no way to get the first row, the next row, or the previous 10 rows. This is an integral part of how a relational DBMS worksDifferent DBMSs support different cursor options and features. Some of the more common ones are:

The capability to flag a cursor as read-only so that data can be read but not updated or deleted

The capability to control the directional operations that can be performed (forward, backward, first, last, absolute position, relative position, and so on)

The capability to flag some columns as editable and others as not editable

Scope specification so as to be able to make the cursor accessible to a specific request that created it (a stored procedure, for example) or to all requests

Instructing the DBMS to make a copy of the retrieved data (as opposed to pointing to the live data in the table) so that data does not change between the time the cursor is opened and the time it is accessed

Cursors and Web-Based Applications Cursors are rather useless when it comes to Web-based applications (ASP, ColdFusion, PHP, and JSP, for example). Cursors are designed to persist for the duration of a session between a client application and a server, but this client/server model does not fit in the Web application world because the application server is the database client, not the end user. As such, most Web application developers avoid the use of cursors and re-create the functionality themselves if needed.

chk_date Function in Oracle SQL

2011-05-02T03:50:00.000-07:00

execute chk_date('14-jun-2000');

Where SQL server user names and passwords are stored in SQL server?

2011-04-11T21:37:00.000-07:00

They get stored in System Catalog Views sys.server_principals and sys.sql_logins

If we have multiple AFTER Triggers on table how can we define the sequence of the triggers ?

2011-04-11T21:36:00.000-07:00

sp_settriggerorder

sp_setorder

None

What is the maximum Storage Size of the Varchar Datatype?

2011-04-11T21:35:00.000-07:00

4000 Bytes

7000 Bytes

8000 Bytes

1000 Bytes

What are the two system stored procedures for creating the lnked server?

2011-04-11T21:34:00.000-07:00

sp_addlinkedserver,sp_addlinkedsrvlogin

sp_addlinkedserver,sp_addlogin

sp_addlinkedsrv,sp_addlinkedsrvlogin

addlinkedsrv,sp_addsrvlogin

How can you find out the sqlserver last restarted date?

2011-04-11T21:33:00.000-07:00

SQL Query for sqlserver last restarted date.

Select Answer:

select create_date from sys.databases where name='master'

select create_date from sys.databases where name='tempdb'

select create_date from sys.databases where name='model'

select create_date from sys.databases where name='msdb'

What is Full form of DMV?

2011-04-11T21:32:00.000-07:00

Data Management version

Dynmaic Management version

Dynamic Management View

Data Management view

Which function is used for Identifying the numeric value?

2011-04-11T21:31:00.000-07:00

isnumeric

isnumber

is_number

is_numeric

How do you find out how many tables created today in particular database in SQL?

2011-04-11T21:28:00.000-07:00

Select Answer:

select count(*) from sys.tables where create_date=getdate()

select count(*) from sys.tables where create_date=convert(varchar,getdate(),101) (Your selection was wrong)

select count(*) from sys.tables where convert(varchar,create_date,101)=getdate()

select count(*) from sys.tables where convert(varchar,create_date,101)=convert(varchar,getdate(),101)

What is the difference between a Subquery and a Join?

2011-04-11T21:10:00.000-07:00

Subquery will work without having relation between tables, where as join will not work

Subquery will be used for more than one result set where Join is used for one result set

Join can be used for multiple tables, Where as Sub query cannot be used for multiple tables

None of the above

All of the above

Which one of is following is a System defined stored proecdure in SQL Server?

2011-04-11T20:42:00.001-07:00

sp_test

sp_myuser

sp_who5

sp_spaceused

sp_gettriggers

What type Index is available in Sql server?

2011-04-11T20:41:00.002-07:00

Search index

Clustered Index

Normalized index

All of the above

none of the above

What are the types of joins available in SQL Server?

2011-04-11T20:41:00.001-07:00

Inner join, Outer join, Cross join, Loop join

Cross Join, Inner Cross Join, Left outer Join, Right Outer Join

Inner Join, Outer Join, Cross Join, Inner Cross Join

Inner Join Outer Join, Left join, half join

Inner Join ,Outer Join, Cross Join, Self Join

Declare @datalength varchar(50); set @datalength=' Dot net spider ' select datalength(@datalength),len(@datalength) What is the output for the followi

2011-04-11T20:40:00.003-07:00

1)15,15

2)17,17

3)15,17

4)17,15

4)17,16

What is the use of UPDATE_STATISTICS in SQL?

2011-04-11T20:40:00.001-07:00

To Remove the indexes after modifying huge data

To update the indexes after modifying huge data

To Create new indexes after modifying huge data

How much level Stored Procedure nesting is possible in SQL 2008?

2011-04-11T20:39:00.000-07:00

None

2011-04-11T09:31:00.000-07:00

Select Answer:

This controls the flow of data from the server to the user's browser. When the buffer is set to true, which it is by default, data isn't sent to the browser until the server completely processes the page. When the buffer is set to false, data is sent to th

Use this when the buffer is set to true and you want to get rid of everything processed up to where the Clear() is.

BOTH

NONE

Whats is new in SQL Server 2008 Top operators?

2011-04-11T09:29:00.000-07:00

TOP operators can be used in Create and Modify statements

TOP operators can be used in Insert, Update and Delete Statements

TOP operators can be used in DDL and as well as DML

None of the above

Which is not true about CTE?

2011-04-11T09:28:00.000-07:00

Select Answer:

It is used for Temporary results

It is similar to Derived table

It can be used in Multiple SQL statements

It can be used in Single SQL statements

It will be available for the duration of the query

What is the use of MERGE statement in SQL Server 2008?

2011-04-11T09:27:00.002-07:00

Select Answer:

It is used to perform multiple DDL operations

It is used to perform multiple DML operations

It is used to perform Only Insert and Delete operations

It will be processed more than one

Which of the following is not true about Merge statement?

2011-04-11T09:27:00.001-07:00

It is used to merge INSERT, UPDATE and DELETE

Merged data will be processed at once

Semi colon is not mantatory after the merge statement

All of the Above

None of the above

Which clause is necessary to have an ORDER BY clause in a View?

2011-04-11T09:26:00.003-07:00

SELECT

DISTINCT

TOP

How many non-clustered index can be created in a Sql Server 2008 table?

2011-04-11T09:26:00.001-07:00

Select Answer:

249

255

999

What is a Collation in SQL Server?

2011-04-11T09:25:00.001-07:00

It is used to compare different data types in SQL server

It is used to define rules for comapring 2 different data types in SQL Server

It is used to define rules to compare and how the data is stored

All of the above

None of the Abive

Which is not true about the Sub Query in SQL Server?

2011-04-11T09:24:00.002-07:00

Select Answer:

A subquery is a select query which is nested within another T-SQL statement

A subquery will always return a single result

A subquery can be used with GROUP BY, HAVING

A subquery can be used as a parameter to a function call

Can you call procedure in functions?

2011-04-11T09:24:00.001-07:00

Select Answer:

Yes

We cant say

How many nested Transaction can possible in SQL SERVER?

2011-04-11T09:23:00.002-07:00

Select Answer:

None of these

Why Normalization is used?

2011-04-11T09:23:00.001-07:00

Select Answer:

To minimize redundancy

Easy maintenance and up keeping of the data

Both 1 and 2

2011-04-11T09:21:00.001-07:00

Select Answer:

It is to insert a string between strings

It is used to overwrite the existing string

It is used to overwrite the existing characters

All of the above

2011-04-11T09:09:00.002-07:00

Which are the new Data types introduced in SQL Server 2008?

Select Answer:

Datetime Geography and Time (Your selection was wrong)

Geometry, Time and Date

Geometry, Time, Date, Datetime and DateTime2

All of the above

What is the advantage of using DATETIME2 data type in SQL Server 2008?

2011-04-11T09:09:00.001-07:00

It is used to Convert date to Time

It is used to cover a wide range of years

It is used to convert the time to fraction

All of the above

Can we use Sql express edition 2008 on dot net windows application?

2011-04-11T09:07:00.000-07:00

You can use this windows application.

Use server uthentication with id and password.

Then select users and grant permission

Second Option:

You can use the Sql Server 2008 for the database that is in the shared drive, you need to provide the authentication information.

Microsoft SQL Server SQL Server 2008

2011-04-11T09:05:00.000-07:00

SQL Server Management Studio is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. SQL Server Management Studio combines a broad group of graphical tools with a number of rich script editors to provide access to SQL Server to developers and administrators of all skill levels.

SQL Server Management Studio combines the features of Enterprise Manager, Query Analyzer, and Analysis Manager, included in previous releases of SQL Server, into a single environment. In addition, SQL Server Management Studio works with all components of SQL Server such as Reporting Services, Integration Services, and SQL Server Compact 3.5 SP1. Developers get a familiar experience, and database administrators get a single comprehensive utility that combines easy-to-use graphical tools with rich scripting capabilities.

How to use Management Studio ?

2011-04-11T09:04:00.000-07:00

The left side panel shows a tree structure. The first node says "(local)\SQL2005". Here, I have connected to an instance SQL Server called "SQL2005" on the local computer. Since I have SQL Server 2000 and SQL Server 2005 installed on my computer, I used the name "SQL2005" when I installed SQL 2005.

When you expand the server name, you will see few nodes depending on the number of features you have installed:

1. Databases
2. Security
3. Server Objects
4. Replication
5. Management
6. Notification Services
etc.

The first node "Databases" is the one that you will use most of the time. This node includes all the databases created in this instance of the database server. The security node allows you to manage user accounts and permissions.

configuring and Connecting to SQL Server Database using Management Studio

2011-04-11T09:03:00.000-07:00

As you learned in one of the previous chapters, SQL Management Studio can be used to manage multiple instances of SQL Server. However, when you open the Management Studio, it will prompt you to specify the login information to any one of the instances.
Server Type

The first dropdown in the above screenshot represents the "Server Type". Depending on the number of features you have installed, there will be various options here. When you are trying to connect to the database server, you have to choose "Database Engine".

Server Name

Second dropdown is to select the database server name. If you are connecting to the SQL Server installed on local computer, you can use one of the following (assuming DevServer1 as the computer name and 192.168.2.100 as IP Address):

(local)
. (dot)
localhost
127.0.0.1
DevServer1
192.168.2.100

The first 4 options are used to represent the local computer. If you are connecting to an SQL Server installed on a different computer, you can use only IP Address or Computer name.

If you are connecting to a named instance of SQL Server instead of the default instance, then you must append a backslash and instance name to the above mentioned names. Example: 192.168.2.100\SQLInstance2

Authentication

This dropdown gives two options:

1. Windows Authentication
2. SQL Server Authentication

If you select Windows Authentication, the login and password you used while logging in to your computer will be used to connect to the database server. If you are an administrator on the local computer or if you have configured the database server to use your windows account, you can use this option.

SQL Server Authentication can be used if you have created a user name and password in SQL Server. In that case, you must select this option and specify the user name and password below the drop down.

Which are the SQL Management Studio Editions

2011-04-11T09:02:00.002-07:00

SQL Management Studio comes in two flavours:

1. SQL Management Studio

This edition comes with all versions of SQL Server 2005 except SQL Server 2005 Express Edition. This edition has various advanced features like import data from other database systems, export data to other databases etc.

2. SQL Management Studio Express

SQL Management Studio Express is part of SQL Server 2005 Express Advanced edition. You can download Management Studio Express as a separate tool also. Some of the key features like Import data, export data etc are missing in the Express edition of Management Studio.

What is SQL Management Studio?

2011-04-11T09:02:00.001-07:00

As you already learned, the core part of SQL Server system is the 'server' which runs as a service. The service is a hidden application that runs in the background and it is hard to deal with this service directly.

SQL Server 2005 comes with a tool called 'Management Studio' which will help you manage your SQL Server. SQL Management Studio allows you to perform various actions on SQL Server including:

1. Create/Delete databases
2. Backup/restore databases
3. Attach/detach databases
4. Design tables and edit data
5. Execute queries against any database

SQL Management studio has a lot more features than explained above. Some of the main functionalists mentioned above are explained in coming chapters.

SQL Management Studio allows you to manage multiple instances of SQL Server installed on same computer or different computers. To manage a specific instance of SQL Server, you have to first connect to the instance using appropriate login and password. You need to install management studio only once even if you install multiple instances of SQL Server.

Selecting authentication mode while installing SQL Windows Authentication and SQL Authentication

2011-04-11T09:01:00.000-07:00

SQL Server (and SQL Server Express) supports two authentication modes:

1. Windows Authentication mode
2. Mixed Mode (Windows Authentication and SQL Authentication)

If you select "Windows Mode", you have to use your windows user id and password to connect to SQL Server. Microsoft recommends "Windows Mode" for security reasons.

If you select "Mixed Mode", you have both the options - either use your windows account or create users in SQL Server.

For learning purpose, let us select "Mixed Mode" when you install SQL Server. When you select this mode, a default administrative account called "sa" will be created on SQL Server. You have to select a password for this account.

Enter a secure password in the above dialog and proceed. Don't forget this password. This is your administrative password and you should use this account to create new users in SQL Server.

Your Administrative Account in SQL Server: sa
Administrative password:

If you installed SQL Server Express Advanced Edition, then it will also install SQL Server Management Studio automatically. SQL Management Studio Express is a tool that is used to view, edit, create and manage databases.

If you installed regular version of SQL Server Express, then you must install Management Studio Express separately. Proceed to next chapter for installation instructions on Management Studio Express.

Installing SQL Server Express

2011-04-11T09:00:00.000-07:00

The first few steps are very obvious. You can accept the terms and conditions and proceed.

In the feature selection dialog, by default, only the database services is selected. Click on the second item (Client Components) and select the option "Will be installed on local hard drive". This will install all client components also in addition to the database services.

Click NEXT to proceed.

In the next screen, you have to select the INSTANCE NAME. You have 2 options:

1. Select default instance
2. Select an instance name

The default name shown will be "SQLExpress". You may leave it as it is, or change it to "default instance". The point is, you have to remember what option you selected.

If you choose the "default instance", then you will be refering to your SQL Server instance with the name (local) or localhost or 127.0.0.1.

If you choose "SQLExpress", then then you will be refering to your SQL Server instance with the name (local)\SQLExpress or localhost\SQLExpress or 127.0.0.1\SQLExpress.

Download SQL Server Express 2005

2011-04-11T08:59:00.002-07:00

Microsoft SQL Server Express edition is a replacement for MSDE and it is absolutely free to download and distribute. You can download SQL Server Express from Microsoft web site.

Before installing SQL Server Express, you must download and install .NET Framework 2.0.

Visit the URL http://msdn.microsoft.com/vstudio/express/sql/download/ to download SQL Server Express.

There are two different editions of SQL Server Express available for free download:

SQL Server 2005 Express Edition

SQL Server 2005 Express Edition with Advanced Services

The Advanced editions has mainly 3 additional features:

1. SQL Server Management Studio Express, a graphical management tool based on SQL Server Management Studio that makes it easy to manage and administer SQL Server Express databases.

2. Reporting Services, an integrated report creation and design environment to create reports.

3. Full-Text Search, a powerful search engine for searching text-intensive data.

Download appropriate version depending on your needs. If you do not need advanced features like reporting services and full-text search, it is a good idea to install only SQL Server 2005 Express Edition (not advanced edition) and then install the SQL Server Management Studio Express separately. Advanced edition will create additional services that can slow down your system.

Download Links

Option 1:

Download Microsoft SQL Server 2005 Express Edition

Download SQL Server Management Studio Express

Option 2:

Download SQL Server Express Advanced Edition

Manage SQL Server services using SQL Service Manager

2011-04-11T08:59:00.001-07:00

In one of the previous chapters, you learnt how to use Windows Service Control Manager to start/stop SQL Server services.

Microsoft provides a special tool called 'SQL Server Service Manager'. This tool does almost the same thing - allow you to Start, Pause or Stop the SQL Server services.

The SQL Service Control Manager can manage SQL Servers installed onany computer in the same network. (Windows Service Control displays all services installed on the same computer).

In the Server drop down, you can type or select the IP Address of any SQL Server in your network. When you select an IP Address, it will display all SQL Servers installed on that machine. Then you can choose the options like Start or Stop.

The icon will have a green arrow when the currently selected SQL Server is running. If the currently selected SQL Server is stopped, it will have a red color arrow.

If the SQL Service COntrol Manager is not running, you can start the it from the programs menu > Microsoft SQL Server -> Service Manager.

How to start and stop SQL Server service ?

2011-04-11T08:58:00.001-07:00

A "service" is an application that can start automatically when the computer starts.

There are two start up modes:

1. Automatic - the service starts automatically when the computer starts.
2. Manual - the service is installed, but will not start until somebody manually start it.

By default, SQL Server is configured as "Automatic". So, SQL Server service starts when you start your computer. However, some developers will change it to "Manual" mode for various reasons. One example is, if you have a very slow computer, you may want to stop all unwanted applications running. So, you can go and stop the SQL Server service. Also, you can change it to "manual" mode so that it won't start automatically everytime you reboot your computer. However, when you want to run an application that need to access SQL Server, you have to go to the service control manager and start the service.

Service Control manager

"Service control manager" is a windows tool that allow you manage all services (including SQL Server service) installed on the computer.

To open the service control manager, go to Control Panel -> Administrative Tools -> Services.

You can see the list of all installed services and their current status. Right click on any service name and see the context menu. You can see various options like Start, stop, restart, pause etc.

The above image shows several service names starting with the word "SQL Server...". They are various services related to SQL Server. You can right click on the service name and select any task like Start or Stop.

Limitations of SQL Server Express Edition

2011-04-11T08:57:00.000-07:00

1. Number of CPUs supported

SQL Server Express uses only one CPU at a time. It can be installed on a server with multiple CPUs, but it will use only one CPU at a time.

2. Maximum memory used

SQL Server Express uses a maximum of 1 GB memory for it's data buffer. So, if your server has severaql GB memeory, SQL Server Express cannot take advantage of it.

4. Database size limit

Maximum database size is limited to 4 GB (log file size is not counted)

5. Profiler in SQL Server Express

Profiler tool is not included with SQL Server Express editions.

6. Job Scheduler

Job Scheduling service is not available with SQL Server Express.

7. Import/Export

Data import and export feature is not available with SQL Server Express.

Which situation requires a before update statement level trigger on the table?

2010-02-25T04:22:00.000-08:00

Which situation requires a before update statement level trigger on the table?

A. When you need to populate values of each updated row into another table.
B. When a trigger must fire for each row affected by the triggering statement.
C. When you need to make sure that user making modifications to the table as necessary privileges.
D. When you need to store the information of the use who successfully modified tables and in audit table.

ANSWER : C

Examine this function.

2010-02-25T01:10:00.001-08:00

Examine this function.

CREATE OR REPLACE FUNCTION CALC_PLAYER_AVG
(V_ID IN PLAYER_BAT_STAT. PLAYER_ID%TYPE)
RETURN NUMBER
IS
V_AVG NUMBER;
BEGIN
SELECT HITS/AT_BATS
INTO V_AVG
FROM PLAYER_BAT_STAT
WHERE PLAYER_ID=V_ID;
RETURN(V_AVG);
END;

Which statement will successfully invoke this function in SQL Plus?
A. SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT;
B. EXECUTE CALC_PLAYER_AVG(31);
C. CALC_PLAYER('RUTH'):
D. CALC_PLAYER_AVG(31);
E. START CALC_PLAYER_AVG(31)

Answer: B

Which system privileges must you have to manually recompile a stored procedure owned by another application developer?

2010-02-25T01:09:00.000-08:00

A. ALTER PROCEDURE
B. ALTER ANY PROCEDURE
C. ALTER ALL PROCEDURE
D. COMPILE ANY PROCEDURE

Answer: B

You want to send a message to another session connected to the same instance. Which Oracle supplied package will you use to achieve this task?

2010-02-24T22:21:00.000-08:00

A. DBMS_JOB
B. DBMS_PIPES
C. DBMS_OUTPUT
D. DBMS_MESSAGE
E. SEND MESSAGE

Answer: B

Which part of a database trigger determines the number of times the trigger body executes?

2010-02-24T22:20:00.000-08:00

Which part of a database trigger determines the number of times the trigger body executes?

A. TRIGGER TYPE
B. TRIGGER BODY
C. TRIGGER EVENT
D. TRIGGER TIMING

Answer: A

When creating procedures, local variables should be placed after which key words?

2010-02-24T22:19:00.000-08:00

When creating procedures, local variables should be placed after which key words?

A. IS
B. BEGIN
C. DECLARED
D. PROCEDURE

Answer : A

There is a customer table in the schema that has a public synonym and you are granted all object privileges on it. You have a process_customer that pr

2010-02-24T22:18:00.000-08:00

There is a customer table in the schema that has a public synonym and you are granted all object privileges on it. You have a process_customer that processes customer information that is in the public synonym customer table. You have just created a new table called customer within your schema. Which statement is true?

A. Creating the table has no effect and procedure process_custoiner still access data from public synonym customer table.
B. If the structure of your customer table is the same as the public synonym customer table then the procedure process_customer is invalidated and gives compilation errors.
C. The structure of your customer table is entirely different from the public synonym customer table, then the procedure process_customer successfully recompiles and accesses your customer table.
D. If the structure of your customer table is the same as the public synonym customer table then the procedure process_custoiner successfully recompiles when invoked and accesses your customer table.

Answer: D

You are about to change the arguments of the CALC_TEAM_AVG function. Which table can you query to determine the names of the procedures and functions?

2010-02-24T22:17:00.000-08:00

You are about to change the arguments of the CALC_TEAM_AVG function. Which table can you query to determine the names of the procedures and functions that invoke the CALC_TEAM_AVG function?

A. USER_PROC_DEPENDS
B. USER_DEPENDENCIES
C. USER_REFERENCES
D. USER SOURCE

Answer: B

When creating a function in a SQL Plus You receive this message "Warning: function created with compilation errors". Which command can you issue ?

2010-02-24T22:16:00.000-08:00

When creating a function in a SQL Plus You receive this message "Warning: function created with compilation errors". Which command can you issue to see the actual error message?

A. SHOW FUNCTION_ERRORS
B. SHOW USER_ERRORS
C. SHOW ERRORS
D. SHOW ALL ERRORS

Answer: C

Examine this trigger.

2010-02-24T22:14:00.000-08:00

CREATE OR REPLACE TRIGGER I PD_TEAM_SALARY
AFTER INSERT OR UPDATE OR DELETE ON PLAYER
FOR EACH ROW
BEGIN
UPDATE TEAM
SET TOT_SALARY=TOT_SALARY+:NEW SALARY.
WHERE ID=:NEW:TEAM_ID;

You will be adding additional coat later but for now you want the current block to fire when updated the salary column. Which solution should you use to verify that the user is performing an update on the salary column?

A. ROWUPDATEC SALARY')
B. UPDATING('SALARY')
C. CHANGING('SALARY')
D. COLUMN_UPDATE(' SALARY')

Answer: B

You need to drop a table from within a stored procedure. How do you implement this?

2010-02-24T22:13:00.002-08:00

A. You cannot drop a table from a stored procedure.
B. Use the DROP command in the procedure to drop the table.
C. Use the DBMS_DDL packaged routines in the procedure to drop the table.
D. Use the DBMS_SQL packaged routines in the procedure to drop the table.
E. Use the DBMS_DROP packaged routines in the procedure to drop the table.

Answer: D

Which Oracle supply package allows you to run jobs at use defined times?

2010-02-24T22:13:00.001-08:00

A. DBMS_JOB
B. DBMS_RUN
C. DBMS_PIPE
D. DBMS_SQL

Answer: A

What is the name of the private procedure in the package?

2010-02-24T22:12:00.000-08:00

A. CALCJTAX
B. INSERT_EMP
C. UPDATE_SAL
D. DELETE_EMP
E. UPDATE_EMP
F. MANAGE EMP

Answer: C

code successfully calculates tax?

2010-02-24T22:11:00.000-08:00

A. CREATE OR REPLACE PROCEDURE calc (p_no IN NUMBER)
RETURN tax IS
V_sal NUMBER:
Tax NUMBER;
BEGIN
SELECT sal rNTO v_sal
FROM emp
WHERE EMPNO=p_no;
Tax:=v_sal * 0. 05;
END;

B. CREATE OR REPLACE FUNCTION calctax(p_no NUMBER)
RETURN NUMBER IS
V_sal NUMBER:
BEGIN
SELECT sal rNTO v_sal
FROM emp
WHERE empno =p_no;
RETURN(v_sal* 0. 05);
END;

C. CRETAE OR REPLACE FUNCTION calctax(p_no NUMBER) RETURN NUMBER IS
V_sal NUMBER:
Tax NUMBER;
BEGIN
SELECT sal INTO v_sal
FROM emp
WHERE empno =p_no;
Tax:=v_sal * 0. 05;
END:

D. CREATE OR REPLACE FUNCTION calctax(p_no NUMBER)IS V_sal NUMBER:
Tax NUMBER;
BEGIN
SELECT sal rNTO v_sal
FROM emp
WHERE empno =p_no;
Tax :=v_sal * 0. 05;
RETURN(tax);
END:

Answer: B

Under which situation do you create a server side procedure?

2010-02-24T22:09:00.000-08:00

A. When the procedure contains no SQL statements.
B. When the procedure contains no PL SQL commands.
C. When the procedure needs to be used by many client applications accessing several remote databases.
D. When the procedure needs to be used by many users accessing the same schema objects on a local database.

Answer: D

Under which situation do you create a server side procedure?

2010-02-24T08:51:00.000-08:00

You need to create a stored procedure, which deletes rows from a table. The name of the table from which the rows are to be deleted is unknown until r

2010-02-24T08:50:00.002-08:00

You need to create a stored procedure, which deletes rows from a table. The name of the table from which the rows are to be deleted is unknown until run time. Which method do you implement while creating such a procedure?

A. Use SQL command delete in the procedure to delete the rows.
B. Use DBMS_SQL packaged routines in the procedure to delete the rows.
C. Use DBMS_DML packaged routines in the procedure to delete the rows.
D. Use DBMSDELETE packaged routines in the procedure to delete the rows.
E. You cannot have a delete statement without providing a table name before compile time.

Answer: B

In order for you to create run a package \IAINTAIN_DATA which privilege do you need?

2010-02-24T08:50:00.001-08:00

A. EXECUTE privilege on the MAINTAIN_DATA package.
B. INVOKE privilege on the MAINTAINJ3ATA package.
C. EXECUTE privilege on the program units in the MAINTAINED ATA package.
D. Object privilege on all of the objects that the MAINTAIN_DATA package is accessing.
E. Execute privilege on the program units inside the MAINTAIN_DATA package and execute privilege on the MAINTAIN_DATA package.

Answer: A

You need to recompile several program units you have recently modified through a PL/SQL program. Which statement is true?

2010-02-24T08:48:00.002-08:00

A. You cannot recompile program units using a PL SQL program.
B. You can use the DBMS_DDL. REOMPELE package procedure to recompile the program units.
C. You can use the DBMS_ALTER. COMPILE packaged procedure to recompile the program units.
D. You can use the DBMS_DDL.ALTER_COMPILE packaged procedure to recompile the program units.
E. You can use the DBMS_SQL.ALTER_COMPILE packaged procedure to recompile the program units.

Answer: D

Which two tables are fused track object dependencies? (Choose two)

2010-02-24T08:48:00.001-08:00

A. USERJDEPENDENSES.
B. USERJDEPTREE.
C. IDEPTREE.
D. USER_DEPTREE.
E. USER DEPENDS.

Answer: A, C

You need to remove database trigger BUSINESS_RULE. Which command do you use to remove the trigger in the SQL*Plus environment?

2010-02-24T08:46:00.000-08:00

A. DROP TRIGGER business rale;
B. DELETE TRIGGER businessrale;
C. REMOVE TRIGGER business_rale;
D. ALTER TRIGGER business rale;
E. DELETE FROM USERJRIGGER
F. WHERE TRIGGER_NAME= 'BUSINESS_RULE':

Answer: A

When creating store procedures and functions which construct allows yon to transfer values to and from the calling environment?

2010-02-24T02:03:00.000-08:00

A. Local variables.
B. Formal arguments.
C. Boolean variables.
D. Substitution variables.
Answer: B

Why do stored procedures and functions improve performance? (Chose two)

2010-02-24T01:58:00.000-08:00

They reduce network round trips.
They postpone PL SQL parsing until run tune.
They allow the application to perform high speed processing locally.
Thev reduce the number of calls to the database and decrease network traffic by bundling commands.
Thev reduce the number of calls to the database and decrease network traffic by using the local PL SQL engine.

B & D

Which type of package construct must be declared both within the package specification and package body?

2010-02-23T09:28:00.001-08:00

A. All package variables.
B. Boolean variables.
C. Private procedures and functions.
D. Public procedures and functions.

Answer: D

The number of cascading triggers is limited by which data base initialization parameter?

2010-02-23T08:46:00.000-08:00

The number of cascading triggers is limited by which data base initialization parameter?

A. CASCADE_TRIGGER_CNT.
B. OPEN_CURSORS.
C. OPEN_TRIGGERS.
D. OPEN DB TRIGGERS.

Answer is : B

Exams Mcse Mcp Microsoft Oracle Ocp Ccsa Ccse Solaris Dump Display

2010-02-23T08:41:00.000-08:00

1. The number of cascading triggers is limited by which data base
initialization parameter?

a. CASCADE_TRIGGER_CNT
b. OPEN_CURSORS ****
c. OPEN_TRIGGERS
d. OPEN_DB_TRIGGERS

2. Which type of package construct must be declared both within the
package specifications and package body?

a. All package variables.
b. Boolean variables.
c. Private procedures and functions.
d. Public procedures and functions. ****

3. Why do stored procedures and functions improve performance?
(choose two)

a. They reduce network round trips. ****
b. They postpone PL/SQL parsing until run time. ****
c. They allow the application to perform high speed processing
locally.
d. They reduce the number of calls to the database and decrease
network traffic by bundling commands.
e. They reduce the number of calls to the database and decrease
network traffic by using the local PL/SQL engine.

4. When creating stored procedures and functions, which construct
allows you to transfer values to and from the calling environment?

a. Local variables.
b. Formal arguments. ****
c. Boolean variables.
d. Substitution variables.

5. You need to remove the database trigger, BUSINESS_RULE. Which
command do you use to remove the trigger in the SQL*Plus
environment?

a. DROP TRIGGER business_rule; ****
b. DELETE TRIGGER business_rule;
c. REMOVE TRIGGER business_rule;
d. ALTER TRIGGER business_rule;
e. DELETE FROM USER_TRIGGER
WHERE TRIGGER_NAME= ‘BUSINESS_RULE’;

6. Which two tables are fused track object dependencies?

a. USER_DEPENDENSIES. ****
b. USER_IDEPTREE.
c. IDEPTREE. ****
d. USER_DEPTREE.
e. USER_DEPENDS.

7. The QUERY_PRODUCT procedure directly references the product
table. There is a NEW_PRODUCT_VIEW view created based on the NOT
NULL columns of the table. The ADD_PRODUCT procedure updates the
table indirectly by the way of NEW_PRODUCT_VIEW view. Under which
circumstances does the procedureADD_PRODUCT get invalidated but
automatically get complied when invoked?

a. When the NEW_PRODUCT_VIEW is dropped.
b. When rows of the product table are updated through SQL Plus.
c. When the internal logic of the QUERY_PRODUCT procedure is
modified.
d. When a new column that can contain null values is added to the
product table. ****
e. When a new procedure is created that updates rows in the product
table directly.

8. You need to recompile several program units you have recently
modified through a PL/SQL program. Which statement is true?

a. You cannot recompile program units using a PL/SQL program.
b. You can use the DBMS_DDL. REOMPILE package procedure to recompile
the program units.
c. You can use the DBMS_ALTER. COMPILE packaged procedure to
recompile the program units.
d. You can use the DBMS_DDL.ALTER_COMPILE packaged procedure to
recompile the program units. ****
e. You can use the DBMS_SQL.ALTER_COMPILE packaged procedure to
recompile the program units.

9. Which type of argument passes a value from a calling environment?

a. VARCHER2
b. BOOLEAN
c. OUT ****
d. IN

10. You need to create a trigger on the EMP table that monitors
every row that is changed and places this information into the
AUDIT_TABLE. Which type of trigger do you create?

a. Statement-level trigger on the EMP table.
b. For each row trigger on the EMP table. ****
c. Statement-level trigger on the AUDIT_TABLE table.
d. For each row statement level trigger on the EMP table.
e For each row trigger on the AUDIT_TABLE table.

11. In order for you to create a run package, MAINTAIN_DATA, which
privilege do you need?

a. EXECUTE privilege on the MAINTAIN_DATA package. ****
b. INVOKE privilege on the MAINTAIN_DATA package.
c. EXECUTE privilege on the program units in the MAINTAIN_DATA
package.
d. Object privilege on all of the objects that the MAINTAIN_DATA
package is accessing.
e. Execute privilege on the program units inside the MAINTAIN_DATA
package and execute privilege on the MAINTAIN_DATA package.

12. You have created a script file EMP_PROC.SQL that holds text to
create a procedure, PROCESS_EMP. You have compiled the procedure for
the SQL Plus environment by running the script file EMP_PROC.SQL.
What happens if there are syntax errors in the procedure
PROCESS_EMP?

a. The errors are stored in the EMP_PROC.ERR file.
b. The errors are displayed to the screen when the script file is
run.
c. The errors are stored in the PROCEDURE_ERRORS data dictionary
view.
d. You need to issue the SHOWERRORS command in the SQL Plus
environment to see the errors. ****
e. You need to issue the DISPLAY ERRORS command in the SQL Plus
environment to see the errors.

13. Which statement about local dependent objects is true?

a. They are on different nodes.
b. They are in a different database.
c. They are on the same node in the same database. ****
d. They are on the same node in a different database.

14. You need to create a stored procedure that deletes rows from a
table. The name of the table from which the rows are to be deleted
is unknown until run time. Which method do you implement while
creating such a procedure?

a. Use SQL command DELETE in the procedure to delete the rows.
b. Use DBMS_SQL packaged routines in the procedure to delete the
rows. ****
c. Use DBMS_DML packaged routines in the procedure to delete the
rows.
d. Use DBMSDELETE packaged routines in the procedure to delete the
rows.
e. YOU cannot have a delete statement without providing a table name
before compile time.

15. Under which situation do you create a server-side procedure?

a. When the procedure contains no SQL statements.
b. When the procedure contains no PL/SQL commands.
c. When the procedure needs to be used by many client applications
accessing several remote databases.
d. When the procedure needs to be used by many users accessing the
same schema objects on a local database. ****

16. Examine this procedure:

CREATE OR REPLACE PROCEDURE ADD_PLAYER
(V_ID IN NUMBER, V_LAST_NAME VARCHER2)
IS
BEGIN
INSERT INTO PLAYER(ID,LAST_NAME).
VALUES(V_ID,V_LAST_NAME);
COMMIT;
END;

This procedure must invoke the UPD-STAT procedure and pass a
parameter. Which statement will successfully invoke this procedure?

a. EXECUTE UPD_BAT_STAT(V_ID);
b. UPD_BAT_STAT(V_ID); ****
c. RUN UPD_BAT_STAT(V_ID);
d. START UPD_BAT_STAT(V_ID);

17. Match the purity levels to their correct definitions:

Terms
RNTS
RNPS
WNDS
WNPS

Definitions
The function cannot modify the database tables.
The function cannot change the values of the package variables.
The function cannot query database tables.
The function cannot reference the value of public packaged
variables.

18. Examine this function:

CREATE OR REPLACE FUNCTION CALC_PLAYER_AVG
(V_ID in PLAYER_BAT_STAT. PLAYER_ID%TYPE)
RETURN NUMBER
IS
V_AVG NUMBER;
SELECTS HITS/AT_BATS
INTO V_AVG
FROM PLAYER_BAT_STAT
WHERE PLAYER_ID_V_ID;
RETURN(V_AVG);
END;

This function must be moved to a package. Which additional statement
must be added to the function to allow you to continue using the
function in the GROUP BY clause of a SELECT statement?

a. PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, WNDS, WNPS); ****
b. PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, WNPS);
c. PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, RNPS, WNPS);
d. PRAGMA RESTRICT_REFERENCES (CALC_PLAYER_AVG, ALLOW_GROUP_BY);

19. A programmer develops a procedure, ACCOUNT_TRANSACTION, and has
left your company. You are assigned a task to modify this procedure.
You want to find all the program units invoking the
ACCOUNT_TRANSACTION procedure. How can you find this information?

a. Query the USER_SOURCE data dictionary view.
b. Query the USER_PROCEDURES data dictionary view.
c. Query the USER_DEPENDENCIES data dictionary views. ****
d. Set the SQL Plus environment variable trade code=true and run the
ACCOUNT_TRANSACTION procedure.
e. Set the SQL Plus environment variable DEPENDENCIES=TRUE and run
the Account_Transaction procedure.

20. Examine this package.

CREATE OR REPLACE PACKAGE BB_PACK
IS
V_MAX_TEAM_SALARY NUMBER(12,2);
PROCEDURE ADD_PLAYER(V_ID NUMBER,V_LAST_NAME
VARCHER2,V_SALARY NUMBER);
END BB_PACK;
/
CREATE OR REPLACE PACKAGE BODY BB_PACK
IS
PROCEDURE UPD_PLAYER_STAT
(V_ID IN NUMBER,V_AB_IN NUMBER DEFAULT4,V_HITS IN NUMBER)
IS
BEGIN
UPDATE PLAYER_BAT_STAT
SET AT_BATS+V_AB,
HITS=HITS+V_HITS
WHERE PLAYER_ID=V_ID;
COMMIT;
END UPD_PLAYER_STAT;
PROCEDURE ADD_PLAYER
(V_ID IN NUMBER,V_LAST_NAME VARCHER2,V_SALARY NUMBER)
IS
BEGIN
INSERT INTO PLAYER(ID,LAST_NAME,SALARY);
UPD_PLAYER_STAT(V_ID,0,0);
END ADD PLAYER;
END BB_PACK;

Which statement successfully assigns $75000000 to the
V_MAX_TEAM_SALARY variable from within a stand alone procedure?

a. V_MAX_TEAM_SALARY := 75000000;
b. BB_PACK.ADD_PLAYER. V_MAX_TEAM_SALARY := 75000000;
c. BB_PACK.V_MAX_TEAM_SALARY := 75000000; ****
d. This variable cannot be assigned a value from outside the
package.

21. Which two statements about the overloading feature of packages
are true?

a. Only local or packaged sub programs can be overloaded. ****
b. Overloading allows different functions with the same name that
differ only in their return types.
c. Overloading allows different subprograms with the same number,
type and order of the parameter.
d. Overloading allows different subprograms with the same name and
same number or type of the parameters.
e. Overloading allows different subprograms with the same name but
different in either number or type or order of parameters. ****

22. Examine this package:

CREATE OR REPLACE manag emps
IS
Tax_rate CONSTANT NUMBER(5,2):= . 28:,
V_id NUMBER;
PROCEDURE insert_emp(p-deptno NUMBER,p-sal NUMBER);
PROCEDURE delete_emp;
PROCEDURE update_emp:
FUNCTION calc_tax(o_sal NUMBER)
RETURN NUMBER;
END manag_emps;
/
CREATE REPLACE PACKAGE BODY manage_emps
IS
BEGIN
Update emp.
SET sal=|sal+p-raise_amt)+sal
WHERE empno= v_id;
END;
PROCEDURE insert_emp
(p_deptno NUMBER, p-sal NUMBER)
IS
BEGIN
INSERT INTO emp(empno, deptno,sal)
VALUES(v_id, p_deptno, p_sal);
END insert emp;
PROCEDURE delete_emp
IS
BEGIN
DELETE FROM emp
WHERE empno=v_id
END delete_emp;
PROCEDURE. Update_emp.
IS
V_sal NUMBER (10,2);
V_raise NUMBER(10,2);
BEGIN
SELECT Sal
INTO v_sal
FROM emp
WHERE empno=v_id;
IF v_sal<500>
V_raise:=0. 05;
ELSIF v_sal<1000>
V_raise:=0. 07;
ELSE
V_raise:=0. 04
END IF;
Update_sal(v_raise);
END update_emp.
FUNCTION calc_tax
(p_sal NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN p_sal* tax-rate;
END calc_tax;
END MANAGE_emp;
/

What is the name of the private procedure in the package?

a. CALC_TAX
b. INSERT_EMP
c. UPDATE_SAL ****
d. DELETE_EMP
e. UPDATE_EMP
f. MANAGE_EMP

23. Examine the code:

CREATE OR REPLACE TRIGGER update_emp
AFTER UPDATE ON emp
BEGIN
INSERT INTO audit_table (who, audited)
VALUES(USER, SYSDATE);
END;

You issue an update command on the EMP table that results in
changing ten rows. How many rows are inserted into the AUDIT_TABLE?

a. 1 ****
b. 10
c. none
d. Value equal to the number of rows in the EMP table

24. All users currently have INSERT privileges on the PLAYER table.
You want only your users to insert into this table using the
ADD_PLAYER procedure. Which two actions must you take?

a. GRANT SELECT ON ADD_PLAYER TO PUBLIC;
b. GRANT EXECUTE ON ADD_PLAYER TO PUBLIC; ****
c. GRANT INSERT ON PLAYER TO PUBLIC;
d. GRANT EXECUTE, INSERT ON ADD_PLAYER TO PUBLIC;
e. REVOKE INSERT ON PLAYER FROM PUBLIC; ****

25. Which oracle supply package allows you to run jobs that use
defined times?

a. DBMS_JOB ****
b. DBMS_RUN
c. DBMS_PIPE
d. DBMS_SQL

26. You need to drop a table from within a stored procedure. How do
you implement this?

a. You cannot drop a table from a stored procedure.
b. Use the DROP command in the procedure to drop the table.
c. Use the DBMS_DDL packaged routines in the procedure to drop the
table.
d. Use the DBMS_SQL packaged routines in the procedure to drop the
table. ****
e. Use the DBMS_DROP packaged routines in the procedure to drop the
table.

27. Which data dictionary view gives you the names and the source
code of all the procedures you have created?

a. USER_SOURCE ****
b. USER_OBJECTS
c. USER_PROCEDURES
d. USER_SUBPROGRAMS

28. Examine this package:

CREATE OR REPLACE PACKAGE BB_PACK
IS
V_MAX_TEAM_SALARY NUMBER(12,2);
PROCEDURE ADD_PLAYER(V_ID IN NUMBER, V_LAST_NAME)
VARCHAR2(V_SALARY NUMBER);
END BB_PACK;
/
CREATE OR REPLACE PACKAGE BODY BB_PACK
IS
V_PLAYER_AVG NUMBER(4,3);
PROCEDURE UPD_PLAYER_STAT
V_ID IN NUMBER, V_AB IN NUMBER DEFAULT4, V_HITS IN NUMBER)
IS
BEGIN
UPDATE PLAYER_BAT_STAT
SET ADD_BAT=ADD_BATS+V_AB,
HITS=HITS+V_HITS
WHERE PLAYER_ID=V_ID;
COMMIT;
VALIDATE_PLAYER_STAT(V_ID);
END UPD_PLAYER_STAT;
PROCEDURE ADD_PLAYER
(V_ID IN NUMBER, V_LAST_NAME, VARCHAR2, V_SALARY IN NUMBER);
IS
BEGIN
INSERT INTO PLAYER (ID, LAST_NAME, SALARY)
VALUES(V_ID, V_LAST_NAME, V_SALARY);
UPD_PLAYER_STAT(V_ID,0,0);
END ADD_PLAYER;
END BB_PACK;

Which kind of packaged variable is V_MAX_TEAM_SALARY?

a. PRIVATE
b. PUBLIC ****
c. IN
d. OUT

29. Examine this trigger:

CREATE OR REPLACE TRIGGER UPD_TEAM_SALARY
AFTER INSERT OR UPDATE OR DELETE ON PLAYER
FOR EACH ROW
BEGIN
UPDATE TEAM
SET TOT_SALARY=TOT_SALARY+:NEW SALARY.
WHERE ID=:NEW:TEAM_ID;

You will be adding additional code later but for now you want the
current block to fire when updating the salary column. Which
solution should you use to verify that the user is performing an
update on the salary column?

a. ROW_UPDATE(‘SALARY’)
b. UPDATING(‘SALARY’) ****
c. CHANGING(‘SALARY’)
d. COLUMN_UPDATE(‘SALARY’)

30. Examine this package:

CREATE OR REPLACE PACKAGE discounts IS
G_ID NUMBER:=7839;
DISCOUNT_RATE NUMBER O. 00;
PROCEDURE DISPLAY_PRICE (V_PRICE NUMBER);
END DISCOUNTS;
/
CREATE OR REPLACE PACKAGE BODY discounts
IS
PROCEDURE DISPLAY_PRICE (V_PRICE_NUMBER)
IS
BEGIN DBMS_OUTPUT.PUT_LINE(‘DISCOUNTED||2_4
(V_PRICE*NVL(DISCOUNT_RATE, 1)))
END DISPLAY_PRICE;
BEGIN DISCOUNT_RATE;=0. 10;
END DISCOUNTS;
/

Which statement is true?

a. The value of DISCOUNT_RATE always remain 0.00 in a session.
b. The value of DISCOUNT_RATE is set to 0.10 each time the package
is invoked in a session.
c. The value of DISCOUNT_RATE is set to 1 each time the procedure
DISPLAY_PRICE is invoked.
d. The value of DISCOUNT_RATE is set to 0.10 when the package is
invoked for the first time in a session. ****

31. Examine this package:

CREATE OR REPLACE PACKAGE BB_PACK
V_MAX_TEAM_SALARY NUMBER(12,2);
PROCEDURE ADD_PLAYER(V_ID IN NUMBER, V_LAST_NAME
VARCHAR2, V_SALARY NUMBER);
DB_PACK;/ CREATE OR REPLACE PACKAGE BODY BB_PACK
IS
V_WHERE_AVG NUMBER(4,3);
PROCEDURE UPD_PLAYER_STAT
(V_ID IN NUMBER, V_AVG IN NUMBER DEFAULT 4,V_HITS IN NUMBER)
IS
BEGIN
UPDATE PLAYER_BAT_STAT
SET AT_BATS=AT_BATS+V_AB,
HITS=HITS+V_HITS
WHERE PLAYER_ID=V_ID;
COMMIT;
VALIDATE_PLAYER_STAT(V_ID);
END UPD_PLAYER_STAT;
PROCEDURE ADD-PLAYER
(V_ID IN NUMBER, V_LAST_NAME VARCHAR2, V_SALARY NUMBER)
IS
BEGIN
INSERT INTO PLAYER(ID, LAST_NAME, SALARY)
VALUES(V_ID, V_LAST_NAME, V_SALARY);
UPD_PLAYER_STAT(V_ID,0,0);
END ADD_PLAYER;
END BB_PACK;

An outside procedure VALIDATE_PLAYER_STAT is executed from this
package. What will happen when this procedure changes?

a. The package specification is dropped.
b. The package specification is invalidated.
c. The package is invalid to begin with.
d. The package body is invalidated ****

Returning Nulls in the Resulting Set of a Subquery

2010-01-04T07:29:00.000-08:00

The SQL statement on the slide attempts to display all the employees who do not have any subordinates.

Logically, this SQL statement should have returned 12 rows. However, the SQL statement does not return any rows. One of the values returned by the inner query is a null value, and hence the entire query returns no rows. The reason is that all conditions that compare a null value result in a null. So whenever null values are likely to be part of the results set of a subquery, do not use the NOT IN operator. The NOT IN operator is equivalent to <> ALL.

Notice that the null value as part of the results set of a subquery is not a problem if you use the IN operator. The IN operator is equivalent to =ANY. For example, to display the employees who have subordinates, use the following SQL statement:

SELECT emp.last_name FROM employees emp WHERE emp.employee_id IN
(SELECT mgr.manager_id
FROM employees mgr);

Alternatively, a WHERE clause can be included in the subquery to display all employees who do not have any subordinates:

SELECT last_name FROM employees
WHERE employee_id NOT IN
(SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL);

Illegal Queries Using Group Functions

2010-01-04T07:27:00.000-08:00

The WHERE clause cannot be used to restrict groups. The SELECT statement on the slide results in an error because it uses the WHERE clause to restrict the display of average salaries of those departments that have an average salary greater than $8,000.

You can correct the slide error by using the HAVING clause to restrict groups.

SELECT department_id, AVG(salary)
FROM employees
HAVING AVG(salary) > 8000
GROUP BY department_id;

Creating Groups of Data: The GROUP BY Clause Syntax

2010-01-04T07:26:00.001-08:00

The GROUP BY Clause
You can use the GROUP BY clause to divide the rows in a table into groups. You can then use the group functions to return summary information for each group.
In the syntax:

group_by_expression specifies columns whose values determine the basis for
grouping rows Guidelines

• If you include a group function in a SELECT clause, you cannot select individual results as well, unless the individual column appears in the GROUP BY clause. You receive an error message if you fail to include the column list in the GROUP BY clause.
• Using a WHERE clause, you can exclude rows before dividing them into groups.
• You must include the columns in the GROUP BY clause.
• You cannot use a column alias in the GROUP BY clause.
• By default, rows are sorted by ascending order of the columns included in the GROUP BY list.

You can override this by using the ORDER BY clause.

Using the DISTINCT Keyword

2010-01-04T07:24:00.002-08:00

Use the DISTINCT keyword to suppress the counting of any duplicate values within a column.

The example on the slide displays the number of distinct department values in the EMPLOYEES table

All group functions ignore null values in the column. In the slide example, the average is calculated based only on the rows in the table where a valid value is stored in the COMMISSION_PCT column.

The average is calculated as the total commission paid to all employees divided by the number of employees receiving a commission (four).

Using the AVG and SUM Functions

2010-01-04T07:24:00.001-08:00

Group Functions

You can use AVG, SUM, MIN, and MAX functions against columns that can store numeric data. The example on the slide displays the average, highest, lowest, and sum of monthly salaries for all sales representatives.

Creating Three-Way Joins with the ON ClauseCreating Three-Way Joins with the ON Clause

2010-01-04T07:21:00.000-08:00

A three-way join is a join of three tables. In SQL: 1999 compliant syntax, joins are performed from left to right so the first join to be performed is EMPLOYEES JOIN DEPARTMENTS. The first join condition can reference columns in EMPLOYEES and DEPARTMENTS but cannot reference columnsin LOCATIONS. The second join condition can reference columns from all three tables.

This can also be written as a three-way equijoin:

SELECT employee_id, city, department_name
FROM employees, departments, locations
WHERE employees.department_id = departments.department_id
AND departments.location_id = locations.location_id;

SELECT e.employee_id, l.city, d.department_name
FROM employees e JOIN departments d USING (department_id)
JOIN locations l USING (location_id);

Creating Joins with the ON Clause

2010-01-04T07:18:00.000-08:00

• The join condition for the natural join is basically an equijoin of all columns with the same name.
• To specify arbitrary conditions or specify columns to join, the ON clause is used.
• The join condition is separated from other search conditions.
• The ON clause makes code easy to understand.

The ON Condition

Use the ON clause to specify a join condition. This lets you specify join conditions separate from any search or filter conditions in the WHERE clause.

Retrieving Records with the USING Clause

2010-01-04T07:16:00.000-08:00

The example shown joins the DEPARTMENT_ID column in the EMPLOYEES and DEPARTMENTS tables, and thus shows the location where an employee works.

This can also be written as an equijoin:

SELECT employee_id, last_name,
employees.department_id, location_id
FROM employees, departments
WHERE employees.department_id = departments.department_id;

Creating Joins with the USING Clause

2010-01-04T07:15:00.000-08:00

• If several columns have the same names but the data types do not match, the NATURAL JOIN
clause can be modified with the USING clause to specify the columns that should be used for an equijoin.
• Use the USING clause to match only one column when more than one column matches.
• Do not use a table name or alias in the referenced columns.
• The NATURAL JOIN and USING clauses are mutually exclusive.

Natural joins use all columns with matching names and data types to join the tables. The USING clause can be used to specify only those columns that should be used for an equijoin. The columns referenced in the USING clause should not have a qualifier (table name or alias) anywhere in the SQL statement.

For example, this statement is valid:
SELECT l.city, d.department_name
FROM locations l JOIN departments d USING (location_id)
WHERE location_id = 1400;

This statement is invalid because the LOCATION_ID is qualified in the WHERE clause:

SELECT l.city, d.department_name
FROM locations l JOIN departments d USING (location_id)
WHERE d.location_id = 1400;

ORA-25154: column part of USING clause cannot have qualifier

The same restriction applies to NATURAL joins also. Therefore columns that have the same name in both tables have to be used without any qualifiers.

Retrieving Records with Natural Joins

2010-01-04T07:11:00.000-08:00

In the example on the slide, the LOCATIONS table is joined to the DEPARTMENT table by the LOCATION_ID column, which is the only column of the same name in both tables. If other common columns were present, the join would have used them all.

Equijoins

The natural join can also be written as an equijoin:

SELECT department_id, department_name,
departments.location_id, city
FROM departments, locations
WHERE departments.location_id = locations.location_id;

Natural Joins with a WHERE Clause Additional restrictions on a natural join are implemented by using a WHERE clause. The example below limits the rows of output to those with a department ID equal to 20 or 50.

SELECT department_id, department_name,
location_id, city
FROM departments
NATURAL JOIN locations
WHERE department_id IN (20, 50);

Creating Natural JoinsCreating Natural Joins

2010-01-04T07:10:00.001-08:00

• The NATURAL JOIN clause is based on all columns in the two tables that have the same name.

• It selects rows from the two tables that have equal values in all matched columns.

• If the columns having the same names have different data types, an error is returned.

Joining a Table to Itself

2010-01-04T07:08:00.000-08:00

Sometimes you need to join a table to itself. To find the name of each employee’s manager, you need to join the EMPLOYEES table to itself, or perform a self join. For example, to find the name of Whalen’s manager, you need to:

• Find Whalen in the EMPLOYEES table by looking at the LAST_NAME column.

• Find the manager number for Whalen by looking at the MANAGER_ID column. Whalen’s manager number is 101.

• Find the name of the manager with EMPLOYEE_ID 101 by looking at the LAST_NAME column. Kochhar’s employee number is 101, so Kochhar is Whalen’s manager.

In this process, you look in the table twice. The first time you look in the table to find Whalen in the LAST_NAME column and MANAGER_ID value of 101. The second time you look in the EMPLOYEE_ID column to find 101 and the LAST_NAME column to find Kochhar.

Returning Records with No Direct Match with Outer Joins

2010-01-04T07:07:00.000-08:00

If a row does not satisfy a join condition, the row will not appear in the query result. For example, in the equijoin condition of EMPLOYEES and DEPARTMENTS tables, employee Grant does not appear because there is no department ID recorded for her in the EMPLOYEES table. Instead of seeing 20 employees in the result set, you see 19 records.

SELECT e.last_name, e.department_id, d.department_name FROM employees e, departments d WHERE e.department_id = d.department_id;

The DECODE Function

2010-01-04T07:04:00.000-08:00

The DECODE function decodes an expression in a way similar to the IF-THEN-ELSE logic used in various languages. The DECODE function decodes expression after comparing it to each search value. If the expression is the same as search, result is returned.

If the default value is omitted, a null value is returned where a search value does not match any of the result values.

In the preceding SQL statement, the value of JOB_ID is tested. If JOB_ID is IT_PROG, the salary increase is 10%; if JOB_ID is ST_CLERK, the salary increase is 15%; if JOB_ID is SA_REP, the salary increase is 20%. For all other job roles, there is no increase in salary.

The same statement can be expressed in pseudocode as an IF-THEN-ELSE statement:

IF job_id = ’IT_PROG’ THEN salary = salary*1.10
IF job_id = ’ST_CLERK’ THEN salary = salary*1.15
IF job_id = ’SA_REP’ THEN salary = salary*1.20
ELSE salary = salary

Using the COALESCE Function

2010-01-04T07:02:00.000-08:00

• The advantage of the COALESCE function over the NVL function is that the COALESCE function can take multiple alternate values.

• If the first expression is not null, it returns that expression; otherwise, it does a COALESCE of the remaining expressions.

The COALESCE Function.

The COALESCE function returns the first non-null expression in the list.

Syntax
COALESCE (expr1, expr2, ... exprn)

In the syntax:

expr1 returns this expression if it is not null
expr2 returns this expression if the first expression is null and this expression is not null exprn returns this expression if the preceding expressions are null

The TO_NUMBER and TO_DATE Functions (continued)

2010-01-04T07:00:00.000-08:00

Display the names and hire dates of all the employees who joined on May 24, 1999. Because the fx modifier is used, an exact match is required and the spaces after the word ‘May’ are not recognized.

SELECT last_name, hire_date FROM employees
WHERE hire_date = TO_DATE(’May 24, 1999’, ’fxMonth DD, YYYY’);

Sorting by Multiple Columns

2010-01-04T06:59:00.000-08:00

You can sort query results by more than one column. The sort limit is the number of columns in the given table.

In the ORDER BY clause, specify the columns, and separate the column names using commas. If you want to reverse the order of a column, specify DESC after its name. You can also order by columns that are not included in the SELECT clause.

Example

Display the last names and salaries of all employees. Order the result by department number, and then in descending order by salary.

SELECT last_name, salary FROM employees ORDER BY department_id, salary DESC;