Write SQL commands for the following requirements.

$(1)$ Write a query to list visit number, visit date, doctor ID$,$ patient number, patient name, and pat balance from those visit records in the month of February $2015.$ Visit date has dd$/$mm$/$yy format. Patient name $($first name and last name$)$ should be displayed in the same column with an appropriate heading. For example, a patient with the first name, $$Gerard$$ and last name, $$Hare$$ should be displayed as $$Gerard Hare$$ in the result. The result should be sorted by pat

balance in descending order.

$(2)$ Write a query to list visit number, number of visit lines and total charges from all visit lines for each visit. The result should be sorted by total charges from minimum to maximum. The calculated columns should have appropriate headings. The total charges from all visit lines should be formatted with $$,$ a comma, and two decimal places.

$(3)$ Write a query to list doctor ID$,$ doctor first name, doctor last name, doctor start date, department number, department name, head of department employee number $($doctor ID$),$ and head of department first name and last name.

$(4)$ Write a query to list insurance code, insurance name, insurance contact person, city, and the average patient balance. The calculated column should have an appropriate heading. The result should be sorted by the number of patients from maximum to minimum.

$(5)$ Write a query to list visit number, patient number, patient first name, patient last name, doctor ID$,$ doctor first name, and doctor last name and department number. The result should only show the doctor$$s whose first name begins with the letter $$S$$ and the second letter $$h$.$ Please note that the spelling of the doctor first name 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)$ Write a query to list patient number, patient first name, patient last name, min bill charge amount, average insurance pay amount, max patient pay amount, total bill balance for each patient. The calculated columns should have appropriate headings. All charge and pay amounts should be formatted with $$,$ comma, and two decimal places.

$(7)$ Write a query to list doctor ID$,$ doctor first name, doctor last name, total bill charge amount, total insurance pay amount, and total patient pay amount. The results should be limited to those doctors with bill insurance pay amount between $500$ and $5000.$ The calculated columns should have appropriate headings. All charge and pay amounts should be formatted with $$,$ comma, and

two decimal places.

$(8)$ Write a query to list patient number, patient first name, patient last name, insurance code, insurance name, patient balance, and the difference between

patient balance and total balance from all patient records. The calculated column should have an appropriate heading.

$(9)$ Write a query to list patient first name, and the number of visits $($not the number of visit lines$)$ and the mean value of patient pay amount from all those visits. The calculated columns should have appropriate headings. The pay amounts should be formatted with $$,$ comma, and two decimal places.

$(10)$ Write a query to list visit number, visit date, bill date, and bill balance from those records that the billing date is maximum thirty days after the visit date. The calculated column should have an appropriate heading. The bill balance should

be formatted with $$,$ comma, and two decimal places.

$(11)$ Write a query to display insurance code, insurance name, insurance contact and the total charge amount received through all billing transactions for each insurance code. The calculated column should have an appropriate heading. The pay amount should be formatted with $$,$ comma, and two decimal places.

$(12)$ Write a query to list visit number, visit date and an average charge per visit. For example, if a visit number $300$ has two visit lines with $$150$ and $$250$ visit line charges respectively. The average charge per visit for the visit number $300$ is $$200.$ And only select the records where the visit date is after $1$st July $2015.$ The average charge column should have an appropriate heading and be formatted

with $$,$ comma, and two decimal places. The results should be sorted by the average charge per visit from minimum to maximum.

$(13)$ Create your own SELECT statement that joins data from two tables. The query must include the ORDER BY clause. The output must be appropriately formatted.

$(14)$ Create your own SELECT statement that joins data from at least three tables and uses at least one condition in the WHERE clause that is not a join condition. The query must include the GROUP BY clause. The output must be appropriately formatted.

$(15)$ Create your own SELECT statement that uses a subquery and joins data from two tables. You can use a WHERE subquery or IN subquery or HAVING subquery or an an attribute list subquery.