BSYS602 Business Data Management Workshop Individual Assignment 1 SQL 2026 | AUT New Zealand

BSYS602 Business Data Management Workshop Individual Assignment 1

Use the database schema below to work on this assignment. Before you start writing queries, you need to run the sql script file “Assign1_SQL_script.sql” to create tables and insert data into those tables. Please note that this is a relatively long script, so it will likely take a few minutes to complete.

Save your queries in a file (Yourname_assign1.sql). Submit the file on Canvas.

Write SQL commands for the following requirements.

1. List patient details for those patients who have balance greater than or equal to100 and less than or equal to 1000. Customer address should be displayed in the same column with an appropriate heading. For example, a customer with the street address “92 East 52nd Avenue”, city “Catherine”, State “AL”, and zip code “36728” should be displayed as “92 East 52nd Avenue Catherine AL 36728” in the result.
2. List patient details for those with the first name beginning with the letter “b” or letter “s” and live in the states of PA, NY, DE, or KY. Please note that the spelling of the column values may have different combinations of letter case in the database, but all records should appear in the result as long as they meet the requirements regardless of the use of uppercase or lowercase.
3. List doctor details (doctor ID, doctor name, and doctor’s email address, department number) except those doctors who work in the department numbers 200, 280, 400, 550 and 600. The doctor’s first name and last name should be displayed in the same column with an appropriate heading. For example, a doctor whose first name is “Abraham” and last name is “Stahl” should be displayed as “Abraham Stahl” The result should be ordered by doctor’s first name followed by the doctor’s last name.
4. Display the total number of visits and the last date of visit for the patient number 411. The result should have appropriate headings.
5. List insurance details for those that have the word “Regularcare” or “Kiwicare” in their names. The query needs to take into consideration that these words may appear anywhere in the insurance name (e.g., Best Kiwicare or Kiwicare Best). Please note that the spelling of the column values may have different combinations of letter case in the database, but all records should appear in the result as long as they meet the requirements regardless of the use of uppercase or lowercase.
6. List billing number, billing date, billing charge amount, a combined insurance pay amount and patient pay amount, and billing balance. The result should have appropriate headings for all columns and should be ordered by billing number and billing date. Make sure to display amount values (i.e., billing charge amount, a combined insurance pay amount and patient pay amount and billing balance) with $ and two decimal places.
7. Your manager asks you to create a query that allows her to display billing number, billing date, billing charge amount, and billing balance depending on the maximum amount of combined insurance and patient pay amounts.