ERP004-SQL-Complex query- VFP Mini projects

Part 1- SQL Learning by Example- User Story/Problem Definition – 6 Sessions

1.SQL-Select: String & Date Functions

  • REPLICATE, SPACE, DATE Functions
  • $, LEFT, RIGHT, SUBSTR, INLIST, BETWEEN
  • CHRTRAN, STRTRAN, STUFF Translator Functions
  • AT, RAT, OCCURS, PADL, PADR, PADC
  • DESC , ASCE for each Ordering key

2.SQL-Select: Where & Having conditions

  • Combining WHERE and GROUP BY
  • Combining HAVING and ORDER BY
  • Aggregate Function MIN, MAX, AVG
  • System Analyzing / User Story 1-
  • Write a Paragraph as a Problem Definition for Five SQL Commands

3.Select-SQL: Multi Table Query

  • VFP SET RELATION & VIEWS
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN

4.Select-SQL: Unionize Queries

  • UNION
  • UNION ALL
  • Multi INNER JOIN
  • System Analyzing/User Story 2-
  • Write a Paragraph as a Problem Definition for Five SQL Commands

5.VFP VIEWS – Multi User Environment:

  • USE Table NOUPDATE, SHARE, EXCLUSIVE
  • File & Record LOCK, UNLOCK, SHARE
  • VALIDATE & DOCUMENT DATABASE
  • Data Environment Simulation
  • Parent and Childs SET Relations

6.VFP SQL by example – Select

  • DELETE & RECALL, SET DELETE, DELETED()
  • String Comparison, SET EXACT, PACK, ZAP
  • SUBQUERY, INNER QUERY, NESTED QUERY
  • Data Environment Simulation, SET FILTER, Making Sure The Result Accuracy
  • Multi Level SUBQUERY in VFP & SQL and Compare The Results

Part 2- Multi User Database Management – VFP CLASS Base Projects- 6 Sessions

7.Transaction Management

  • BEGIN/END TRANSACTION, COMMIT
  • LOCK/UNLOCK Methods / MULTILOCK
  • Combination of SELECT, UPDATE, INSERT
  • Transaction Tracing / Log
  • ROLLBACK / TABLEUPDATE / TABLEREVERT

8.Concurrency Control

  • Transaction Handling Sample Program
  • Nested Transactions
  • Transaction Level / TXNLEVEL /
  • Deadlocks / Prevent, Detect, Avoid
  • Time Stamping Methods
  • Wait/Die and Wound/Wait Schemes
  • Optimistic Methods

9.Project1: BANK Base-Table

  • Problem Description
  • Form & Objects & Table Design
  • Grid: ADD, Edit, Delete Records
  • Function Keys, PUSH/POP KEY
  • Save & Load Form Positions

10.Project2: BANK Base-Table

  • Interactive Seek / Filter
  • Reject New Repeated Record
  • Importer/Exporter DBF,CSV,XLS
  • Sort Asce/Desc on Columns
  • Resize Form / Anchor
  • Report Table Preview/Print
  • Return Current Record Values

11.Project3: Create Base-Table CLASS

  • CREATE CLASS/CLASSLIB commands
  • ADD/MODIFY/DEFINE CLASS
  • SET CLASSLIB/ RELEASE CLASSLIB
  • BROWSER CLASS-Run Time Multi Col Grid
  • Create Base-Table CLASS from Project1,2

12.Project4: Create Base-Tables From CLASS

  • Create COUNTRY Base-Table
  • Create STATE Base-Table
  • Create CITY Base-Table
  • Create ZONE Base-Table
  • Create CLASS Base-Table
  • Create GET Function for each Base-Table
  • Create A Data Entry Form for Customers