I-Brain Solutions Interview Assignment
The task assigned to me had the datasets in reversed format, so with the help of VBA Excel macro and using the STRREVERSE function, I formatted the data and made it useful to carry out the desired operations. Further, I imported the files using the SQL SERVER Import and Export Data Tool in Microsoft SQL SERVER 2019. Total 5 tasks were to be carried out through out the assignment, out of which 3 tasks were to be displayed on the User Inteface while remaining 2 queries were to be solved using database. 'TaskWEbUI' folder contains the files that are to be displayed and 'Database' folder contains the Solved queries using SQL. I have used ASP.NET for creating the User Interface.
Task 1 is displayed with the file name as 'Task1.aspx' and 'Task 1 Query' should be referenced for detail inforamtion. I have used 'appointmentinfo$' table for solving the task and SQL functions to display Year and Month required in the solution.
Task 2 is displayed with the file name as 'Task2.aspx' and 'Task 2 Query' holds the SQL query. SQL JOIN function and other basic functions are used to solve the task. Here, I have used patientinfo$ and appointmentinfo$ tables for the operation.
Task 3 was to update the tables without displaying them on the UI. The file 'Task 3 Query' in the 'Database' folder should be accessed to view the solved query. I have used predefined SQL Functions to update multiple tables in a single query.The 'patientinfo$' and the 'patientagegroup$' tables reflect our updated information.
Task 4 is displayed with 'Table4.aspx' and "Task 4 Query' in the database holds the solved query. I have utilised SQL JOIN Functions to join 3 tables from our datasets viz. transactioninfo$, clinicinfo$ and appointmentinfo$, and GROUP BY clauses to sort the data.
Task 5 was to only modify the data from the table and can be referred from the "Task 5 Query' in the 'Database' folder. Here, appointmentinfo$ table is used.
I have used Visual Studio for the webpages created to display our Result onto the User Interface, using ASP.NET framework. I linked the database created with the framework using the SQL SERVER. 3 files are created as mentioned above for displaying the UI. DataGrid is used to display our result in the tabular form, while SQLResource was used to link the appropriate SQL file into the result table.