Assignment – Baan SQL


1. Assume the Suppliers (tccom020) and Purchase Order tables (tdpur040). Make the reference ‘Not mandatory’ from Purchase Order to Supplier Master table. Insert the following data from general table maintenance. Insert three suppliers in the supplier table. Insert orders for one of the suppliers. Insert few orders with supplier code that does not exist in supplier table. Write a query to fetch the records from orders and supplier using ‘refers’ and ‘referred by’. For this query note the effect of various reference modes.

 

2. Write a simple select statement. Display a message in the sections selectdo, selectempty, selecteos. Execute the query for a range of values for the key fields so that:

  • · No records are selected
  • · One record is selected
  • · More that one records are selected

Note the messages that are displayed, note the sequence of execution of the sections.

 

3. For the case in assignment 1, how can one get a list of orders with suppliers not maintained in supplier table using:

  • · Refers
  • · Sub-queries

 

4. For the case in assignment 1, how can one get a list of suppliers with no orders using:

  • · Refers
  • · Sub-queries
  • · Nested selects

 

5. Consider the order details table, the requirement is that only first five largest orders for an item should be listed. Also, note that more than one detail for an item can be present. How can this be done?  Using group functions, using ‘as set with’.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s