0

I want to ask about selecting test data for SQL: Assume that I have 4 tables: Books(id, book_name, price, quantity, del_flg) Students(id, student_name, start_date, end_date, del_flg) Borrows(id, student_id, borrow_detail_id, del_flag) Borrow_details(id, book_id, quanity, borrow_date, return_status, del_flag) Notifications(id, student_id, borrow_id, content) And I got a requirement which I need to create a batch application that list all user borrowed some books for 2 weeks but have not returned. The command to run batch is as following:

sh NotifyOverdueUser.sh processDate (processDate has format: yyyyMMdd)

The condition to get data as following:

  • The student still has a valid card (end_date >= processingDate)
  • borrow_date + 14 < processingDate
  • return_status = 'BORROWING'
  • Only check the data within 1 month from processingDate SQL for the above condition is as:
SELECT
  br.student_id, 
  br.id, count(bd.id)
FROM
  Books bk,
  Students st,
  Borrows br,
  Borrow_details bd
WHERE
  -- JOIN
  bk.id = bd.book_id AND
  br.borrow_detail_id = bd.id AND
  br.student_id = st.id AND
  -- Valid condition
  st.end_date >= TO_DATE(processDate, 'yyyyMMdd') AND
  bd.borrow_date + 14 < TO_DATE(processDate, 'yyyyMMdd') AND
  ADD_MONTHS(bd.borrow_date, 1) > TO_DATE(processDate, 'yyyyMMdd')
  bd.return_status = 'BORROWING' AND
  -- not deleted
  bk.del_flg = '0' AND
  st.del_flg = '0' AND
  br.del_flg = '0' AND
  bd.del_flg = '0'
GROUP BY br.id, br.student_id

Which test data should I select to test for the above SQL? Someone said that return_status and del_flag aren't user input parameters, so we don't need to check for the negative case, such as OVERDUE and RETURNED (!= BORROWING) for return_status, and 1 (!= 0) for del_flg. But I think we should test for both positive case (satisfy the condition) and negative case (not satisfy the condition) for all condition of SQL.

  1. processDate (null, empty, wrongFormatInput, trueFormatInputWihNoData, trueFormatInputAndHasData)
  2. return_status (BORROWING, OVERDUE, RETURNED)
  3. del_flg for each table ( 0 or 1)
Luong Victor Lee
  • 1
  • 1
  • I'm not sure exactly what you're asking. You need to create test data with a user with 13 days return and 15 days return (or exactly 14) and make sure they are flagged as overdue. That's the main part, then you can add some other cases like del flg or processDate – Matthias Mar 14 '23 at 13:01

0 Answers0