1 / 11

Task oriented processing

Task oriented processing. And Queries. 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. Where is my…?. A customer of Northwind wants to know Where is the order I placed last week?

nayef
Download Presentation

Task oriented processing

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 <SP_NAME>.

  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 @ • Variables must be given a data type: • e.g. Declare @cost numeric(6,2); Declare @name varchar(20);

  10. Using Variables • Variables can be assigned values using Set: Set @cost = 0; Set @cost = @cost * 1.1; • Or using Select: Declare @givenId numeric(7,0); Set @givenId = 1234567 Select @name = suppliername from Supplier where SupplierId = @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.

More Related