This material was prepared by Richard Charnigo on 02 and 03 December 2025.

1. If you have not already seen {CharnigoCommentsonSQL.txt}, you may want to review
that file.  The present material assumes familiarity with material from that file,
although some points below are repeated or adapted from that file.  The examples 
that I shall give below are my own, but I have relied on my learning from the 
following websites.

https://www.sqltutorial.org/
https://www.sqlservertutorial.net/

2. When I run SQL Server Management Studio, I am shown a pop-up that asks me to 
connect to the server.  In my case, the server refers to a location on the hard drive 
of my computer.  I did need to check the box "Trust server certificate" to proceed.

3. In the Object Explorer, I right clicked on Databases and selected New Database.  I 
then needed to assign a name, such as Example2.

4. Again in the Object Explorer, I expanded Databases and right clicked on Example2.  
I chose Tasks and then Import Flat File.  I identified the location of a text file on  
my computer called SampleDataforSQL3.  This is a pretend data set which contains 
information for 10 hospital patients, with one row per patient: an ID number, the 
name, the date of admission, the type of treatment, sex, and birthdate.  You can 
proceed through the pages in the Import Flat File pop-up, with the one extra step of 
checking the box to allow nulls (i.e., missing values) under Modify Columns.

5. Repeat point 4 for the second text file called SampleDataforSQL4.  This is another 
pretend data set containing additional information for the 10 hospital patients, with
up to four rows per patient corresponding to that patient's length of stay: the ID 
number, the day number within the patient's stay, and a score measuring pain on a 
scale from 1 to 10 on that day.  

6. Left click New Query.  A window will open, and you can paste in the three lines 
below.  After you left click Execute, you will see rows from the first pretend data
set for patients whose names begin with 'Jon' or end with 'Smith'.

select *
from SampleDataforSQL3
where ptname LIKE 'Jon%' OR ptname LIKE '%Smith';

7. Pasting in the three lines below and left clicking Execute, you will see rows 
from the first pretend data set for patients whose names include 'ie' and do not 
end with 'Smith'.

select *
from SampleDataforSQL3
where ptname like '%ie%' AND ptname not like '%Smith';

8. Pasting in the three lines below and left clicking Execute, you will see rows
from the second pretend data set for patient/day combinations with the lowest 
and highest possible pain scores.  Note that (1, 10) is interpreted as the two-
element set {1, 10} and not as an interval from 1 to 10.

select *
from SampleDataforSQL4
where pain in (1, 10);

9. Pasting in the three lines below and left clicking Execute, you will see rows
from the second pretend data set for patients who had at least one pain score in
excess of 5.

select *
from SampleDataforSQL4
where id in (select id from SampleDataforSQL4 group by id having max(pain) > 5);

10. Here you will combine the first and second pretend data sets, so that the
patient's information from the first data set is added to each row of the second 
data set. Thus, the combined table has multiple rows for most patients.  

select * 
from SampleDataforSQL3
left join SampleDataforSQL4 on SampleDataforSQL3.id = SampleDataforSQL4.id; 

11. Here you will combine the first pretend data set with the first day's record
(only) from the second pretend data set. Thus, the combined table has only as 
many rows as there are patients.

select * 
from SampleDataforSQL3
left join SampleDataforSQL4 on SampleDataforSQL3.id = SampleDataforSQL4.id and day=1;

12. Here you will combine the first and second pretend data sets, so that the
multiple days' pain scores become separate columns within a single row for each
patient. A length of stay variable will also be created. Thus, the combined table 
has only as many rows as there are patients.

select id, ptname, admission, treatment, sex, birthdate, pain1, pain2, pain3, pain4, lengthofstay
from SampleDataforSQL3 a
left join (select id as id1, pain as pain1, day as day1 from SampleDataforSQL4 where day=1)  b on a.id = b.id1
left join (select id as id2, pain as pain2, day as day2 from SampleDataforSQL4 where day=2)  c on a.id = c.id2
left join (select id as id3, pain as pain3, day as day3 from SampleDataforSQL4 where day=3)  d on a.id = d.id3
left join (select id as id4, pain as pain4, day as day4 from SampleDataforSQL4 where day=4)  e on a.id = e.id4
left join (select id as id5, count(day) as lengthofstay from SampleDataforSQL4 group by id)  f on a.id = f.id5;

13. Let us save the previous result.  Right click on the displayed grid of data, 
choose Save Results As, select a directory, and then type in the file name 
SQLOutput.  Also, set the file type to be text.  

14. Repeat point 4 above for SQLOutput, which was just created in point 13.

15. For some reason, NULL is being treated like a string of text (rather than
as a missing value) for pain3 and pain4.  

select * from SQLoutput;

16. The issue noted in point 15 can be corrected as follows.

update SQLOutput
set pain3 = NULLIF(pain3,'NULL');
update SQLOutput
set pain4 = NULLIF(pain4,'NULL');

select * from SQLoutput;

17. For some reason, the numbers in pain3 and pain4 are not recognized as such; 
in particular, one cannot calculate their differences. 

select pain3, pain4, painchange3to4=pain3-pain4 from SQLOutput;

18. The issue noted in point 17 can be corrected as follows.

alter table SQLOutput
alter column pain3 int;
alter table SQLOutput
alter column pain4 int;

19. You can verify that the correction worked.

select pain3, pain4, painchange3to4=pain3-pain4 from SQLOutput;

20. The following syntax will display the records from SQLOutput for patients 
whose admission dates were between 09 July and 24 July 2025 (inclusive).

select * from SQLOutput
where Admission between '07-09-2025' and '07-24-2025';

21. The following syntax will derive the discharge date for each patient.  We 
assume here that the date of discharge is the number of days minus one following 
the date of admission.  So, for instance, a person whose length of stay was two 
days is assumed to have been discharged one day after being admitted.

select *, Discharge=DATEADD(day,LengthofStay-1,Admission) from SQLOutput;

22. The following syntax will calculate the ages of the patients (rounded down
to the nearest year) at their respective dates of admission.

select *, Age=DATEDIFF(year,Birthdate,Admission)+round( (-1+sign(DATEDIFF(day,  DATEADD(year, DATEDIFF(year,Birthdate,Admission), Birthdate),Admission)))/2 ,0) from SQLOutput;

23. The following syntax will identify those patients who were born in the 
month of April.

select * from SQLOutput where DATEPART(month, Birthdate) = 4;

24. The following syntax will abbreviate Medical and Surgical designations for
treatment to Med and Sur, respectively.

update SQLoutput
set treatment = left(treatment,3);

select * from SQLOutput;