Task oriented processing

1
Task oriented processing

• And Queries

2
How do I want my information?

• A customer should not be required to remember
• An order number
• A product code
• An account code
• except for security purposes

3
Where is my?

• A customer of Northwind wants to know
• Where is the order I placed last week?
• How much do I pay for Chai?
• How much more does Chai cost than Chang?
• What is the least expensive drink?
• What is the fanciest (most expensive) cheese?

4
How do I manage ?

• A line manager wants to know
• Are there any products that are out of stock?
• Are there any customers on the record that have
  never placed an order?
• Are there any employees who have never made a
  sale?

5
How do I save ?

• A managing director wants to know
• What is our best selling product?
• What is our highest earning product?
• Who is our highest earning employee?
• Who is the employee who sells the most units?

6
Formulating queries

• Ask the question the user would ask
• Avoid code-dependent queries go for name-based
  or point and pick based queries.
• Once the query has been stated
• Consult the ERD
• Check the enquiry access path
• Try to fill the query using joins rather than
  separate queries.

7
Stored Procedures

• Saving your queries in your database

8
Simple Stored Procedures

• Create the query and check it in the Query
  Analyser.
• Using the Enterprise Manager, Add a new stored
  procedure to your database.
• Name your Stored Procedure appropriately.
• Paste in your query, check syntax and click OK.
• Run your stored procedure from the query analyser
  using EXEC ltSP_NAMEgt.

9
Using variables

• Variables can be used in SQL Server using either
  the Query Analyser or Stored Procedures.
• All variables must begin with the symbol _at_
• Variables must be given a data type
• e.g.
• Declare _at_cost numeric(6,2)
• Declare _at_name varchar(20)

10
Using Variables

• Variables can be assigned values using Set
• Set _at_cost 0
• Set _at_cost _at_cost 1.1
• Or using Select
• Declare _at_givenId numeric(7,0)
• Set _at_givenId 1234567
• Select _at_name suppliername from Supplier where
  SupplierId _at_givenId

11
Parameters in Stored Procedures

• Parameters can be passed to stored procedures by
  reference.
• If the stored procedure is expected to return a
  significant value, then it should be explicitly
  marked as an output parameter.
• To use parameters
• Define the formal parameters in the Stored
  Procedure.
• Define the actual parameters and give them values
  in the Query Analyser.