sql project | sql portfolio project | data science sql project | Part 2

Hello friends good morning good afternoon  in this session we are going to see how to insert the data in tables and select the data  from the tables ,means there are some joints in the select query so we will see that first  of all I am using HIMS database that set no count, on set no account on is used for to hide the number of Records I mean suppose this is the print message or suppose I am  inserting This Record into the patient category correct so one rows affected ,two rows  affected so th

at will give us the message .so if we have to hide that then we can use the set no count  on. I executed it and print Master data , insert so it is used for our acknowledgment means  right now I am where so we will get the message using the print statement like the print hello or  something like that then what I will do I will first of all I will check how many  records in the patient category because in last session we inserted for records  so no need to insert that correct self companies, st

aff ,staff dependent then next  one is the department we you can execute this script in a one stroke as well and so you will  get complete data in the table but for your explanation means for your understanding  I am executing this script one by one table. so I am executing data for the Department  table. and you will get 24 records means I am inserted 24 records in it .and there is  a clinical department and non-clinical Department as I said there are some Department which  are related to the

doctor specialty means Cardiology doctor, Gynecology ,medicine ,Nephrology  Ophthalmology, such are the clinical Department and some of the Departments are non-clinical  so we are considering it as a zero value and Administration, reception, billing ,store , account, maintenance ,because in hospital there are multiple departments so all the Departments  are listed at here then next one is the gender as you know means male and female are  inserted in it and first thing first actually added by

and updated by, added by this who  added that particular record and updated by Means who updated that record and just for your  clarification I am opening the script of it so which table Department correct so yes here the department , Department ID is the  integer column and I am setting it as a identity column and so no need to provide the value  to the department ID then we have to provide only this value Means name clinical and added by  and updated by because added by and updated by and na

me clinical these are the  not null values correct so anyhow we have to provide that and added by means who added that  record updated by who updated that record and we are creating primary key on Department ID  column. so those are arranged in a sequential order and after that yes controller to press to  make a full screen so this is the insurance company records star Health, vidal Health Care , Aditya birla,HDFC so different companies are available in the hospital means they are giving us

the  help like I mean suppose one patient having the category is so you will get the benefit from  the Aditya Villa health insurance because they are having a health insurance policy that's why  and self patient treatments there is a no any company buy into it or there is a no any CGHS,ECHS and sttaff dependence so we can consider that patient as a self patient and next is the  nationality Means Indian I added it and next thing relation I mean suppose admit while admitting  the patient we have

to specify the relation means a patient having with patient there is a  spouse then child, friend, father , mother so we have to specify that particular relationship  so I added that records here and service category . service category it means as I said there are  some Services there come into the pathology category means and some of the services  are related in the nursing department so those are categories in a different way that's  why I created the table as a service category means what ar

e the test , x-ray test but then sonography tests are coming to the Radiology . so I am inserting that records so  service category you will see here these are the records  Cardiology , Gynecology , medicine so you are getting this record in a table .unit means  in one city, there are five units or in a different units and all the data at one location so with  the help of application we are getting on data . correct so and we are inserting into the one  database but there unit wise data is diff

erent means like the their bills are different or their  visits are different admissions are different for that we are segregating that  records with the help of unit let's say unit is the master so  you need one ,two ,three, four, five and next is the ward means there  are different number of wards in the hospital General ward, male ward then  special word so I'm inserting that record here you will get the ward  information I inserted twice so what I will do ,I will delete duplicate  records f

rom the table ,delete from ward where ID Ward ID greater than 7. so you could see there is a seven  ward and there is a room with no records first of all we will check  the records in it and then insert .Room means there are multiple rooms in the  hospital so for that I have created room as a master table so if you select it  then you will get the data from that particular table so room one assigned to the  particular ward number as a five how this ward five select start  from ward, where ward

ID is equal to 5. correct and which I executed so both the queries are at a time so it is  the special ward means special ward having the different rules and so you will get they are  the seven rooms which are bind to the special word and after that this is the  bed related information I mean suppose you see there is a nothing recording it and bed are  related to the ward as well as room so let's see I created the 20 records for the ward and five records for the room so you can  modify this nu

mber as well means I am creating the 20 records you could do 10 records ,five records based on your net and here is the command completed  successfully correct because in at the top we set set no count on if we set it as a no count off then you will get how much records are inserted during the execution so  set no count on off and first of all we will see the data in the bed table and from the next  time you will get the how much records are affected at here and yes bed means these beds are 

bind to the ward these after that bed one , two bed 20 are affected to the related to the  ward 2 means while I do doing the admission at that time we have to select the ward means  there is a female patient or a male patient there is General ward so we will select the one  first and then you will get the number of beds available in the ward and there is a room ID means  suppose there is a room ID as a one means there is a special ward and in that special word  there is a room and room contai

n the five beds same as per the room  ID as 2 okay next one is the you can insert this script in a one stork as well  so you will get the complete data and or you can execute based on the table wise insert into the  nationality so it will insert one record as I said there is a how much records are  affected we are getting these information because set no count off and next one is the  country means how much records inserted into the country table one record then State one  record then in City

there are one one means there are five records are inserted so let's  see till the country, state, city, nationality, nationality you could see two  records because I inserted twice intelligence are not coming because  I commented that line that's why nationality then country correct where  is Country India the nationality ID is one and in state right now country ID is  one correct so Maharashtra State and in Maharashtra State there are different  cities right and state ID is as a one so state

ID is one and we are getting  five cities in it and Status means suppose in future that records need to be delete so what  I will do I am not delete the record from the table so it is nothing but the soft delete so we  have to make the status as 0 instead of the one and in the select query you have to select only  those records who having the status as one so in the result set you fifth city is not  appeared correct and next one is the service type means suppose we are providing the some servi

ces  and what is the category of it so OPD ,IPD basis and on IPD basis there are General category  or special category based on that their rates are different correct so that's why I created the  service type and let's see service type table means we covered about  all the master table data and let's see about the stop patient right now  there is a nothing record in the stop table R so what it does it insert the data  in the stop table so let's start first of all I am inserting this data and 

yeah there are stop data inserted and uh 120 records are inserted into  the Stop table so I created the test data here means stop first name 13. 5.1 13  is the department ID 14 is the department ID means um stop providing the help to the patient or  different services to the other department as well because account department or a payroll department  they are providing help to the other departments as well means in terms of the salary in terms of  the record maintenance so these are the departme

nt ID correct and uh next is the middle name s aim  name then gender ID means his male female date of birth and contact number contact number two  then whatever that email ID of them address line City means which belong to which city then  status so let's see how I created that data first of all I uh I received minimum  Department ID from the Department whose category is clinical agency rule so let's say  select star means I am getting all the all the roads all the columns I can say columns  whi

ch is all the columns of the particular Department table means Department ID name and what  I am selecting I am selecting only Department ID because I have to insert that department ID  into the stop table correct and then next one is clinical as a zero and these are the some  Department means Administration reception Billings so for each department I inserted 10 records  that's why I mentioned uh 10 here it's here and minimum Department to the maximum Department  means minimum value from uh let

's say 13 to the 24 means it will start from 13 and it will  end at 24 correct and uh insert into the stuff so here these are the values I am minimizing  it for your better understanding Department ID correct and Department ID as I said there is a 13 14 15 so  such like that we are getting the department ID and the stop ID it is the  identity column and next one means s name then L name so if we are getting that value  dynamically I I paint it at here then gender means runtime one or two and her

e is the order by new  ID means we will get different values at different when we execute that script how that so let's see  I'm giving you the first of all simple practical um okay I think uh okay I'm  good new select star from Department first of all I'm using the  name column at here okay and uh execute it so you will get the complete list correct right now cardiologic  Gynecology medicine are at the top order by new ID new ID is the unique identifier  value we are getting and let's say I am

executing it so Ophthalmology will be the top  one so I am pick up that value at the runtime nursing Administration so when we execute uh  the next time then new value will generate new value will generate it means I am getting  that value dynamically correct okay and what the I did here actually I have to insert  the date of birth correct so I uh added some values to it so first of all I am giving you the  small explanation for it first of all select get it get it means we will get the current

date is  30th of May and how much is the time correct and let's say I am minus 10 000 let's say I am  minus i j one so what we will get is 29 day minus 1 correct if I said there is a um 10  10 days and here is the random function means random function give us the value uh first of  all yeah till the here we have to execute it and before of that this is the two digit number and random function give us the uh single  digit number but zero point something so we have to make as a two three if I am 

making as a uh five digit number so you will get five digit number and it will change  that particular number dynamically as well so three years sorry 10 000 means 20 years back  records we required correct so 20 25 means right now there is a 23 and this 23 Miss 28  years back records we will get miss their uh stop Edge is 28 or less than 28 means plus minus  so this is the dynamically we are getting the data then this is the simple number I added  as a contact number and then same like um I con

verted at here because this is the uh  integer value and we have to convert into the string that's why I added the convert function  otherwise we can't append that number to the string okay and this is also a dynamic number and  I had appended the gmail.com for the Gmail ID and yes there is a city uh column means there are  the top one cities means there are five cities in it and I am uh randomly I am speakup uh top one  city from the city table ordered by new ID so yeah that is the explanation

for the Miss how that  data is inserted into the um stop table correct yeah we are at here let me select start from stop yes we will get 120 records from this  table and next one is the doctor correct I'm inserting per Department three doctors you  can execute as a five as well means let's say execute it same thing means whatever logic I  return for the Stop same logic written at here means get date function then uh ctid gender  ID so you have to just execute it so how much record you will get t

hree Miss per department Five  records so how much department so there are um 12 departments 12 into 5 is a 60  correct per department Five doctors so I added that data correct and next one is the yeah service service data insert means we  are giving the some consultation follow or procedures one day procedure or when patiently  admitted into the hospital at that time different procedures operation anesthesis then OT charges  so I have to um Loop this for each service I have to insert for each s

ervice category means  Suppose there is a consultation is done for the OPD so what I will do I will mention service  type category ID service type add a as a OPD and it is first of all one by one we can see  what is the service category table and what is the service type table  correct so you will get easily service type and service category service type Miss Suppose there is a x-ray  is the service correct and uh so what is the category of it  Radiology let's start first of all that service bel

ong to which department so  you will get that particular service category at here and that service is belongs  to which uh ipd level OPD level so based on the service type this is nothing but the  different service type because on OPD there is a rate is different on ipd rate is different on  special what that patient is admitted then that rates are different correct that's why we are  segregated into the different Services service types and means pathology service different  Radiology different

services are different so suppose I want one report means at the end means  which services are available in the Radiology so I will get that records easily or I have  means I required how much uh revenue generated from the radiology department so I can calculate  easily with the help of that particular service category ID correct and on OPD basis how much  income so with the help of service type we can calculate that yeah and there is a service name  so these are the some Services name which are

used for the OPD that's why I hard coded them as  a service type ID I'd say one you can see here this is the service this is the name belongs  to service name service category ID and I mean here is the variable is the I  added as a minimum Department ID yes here we can make it as a service actually I uh just copy paste it that's why but  here is the minimum service category ID and what is the rate so 200 is the rate service type ID  I'd say one I mentioned for the OPD that's why you can change

that variable to the  service category ID as well I uh right now I mentioned only the uh minimum  Department ID yeah but it's fine no worries and right now these Services means consultation  and follow procedures operation theater these are not the pathology Radiology nursing services  correct means Suppose there is a medicine department or Orthopedic Department we can do the  consultation but that consultation not applicable for the pathology radiology and nursing so that's  why I executed uh t

he not in command at here what I will do I will create the  timetable first temp table means for temporary point of view we have to  store the value in it so we can use the temp table or table variable as well I will  explain that temporable table variable in the next session I mean in the separate  session I can say and I will insert the service records so let's see  and select start from services the table yeah means consultation service category  means for ENT oncology Ophthalmology these are

the different category and how much rate 200 actually  this data is getting from the UI as well right now I am inserted from the backend SQL that's why  I am not making the for each service there is a different rate in the one stock I am adding 200  in front of them correct and uh if you wanna see service category service type then select  this command I used join in between this three table so yeah consultation is the service  of OPD department and Cardiology Department having this we can filt

er out that means how much  Services how much different category is and how much is the rate so you will get that information  easily yeah next one same thing for the deleting all the records from the timetable and in IDT it's also preset  with the help of the target command these are the some services on the  basis of ipd because bed charges nursing charges are applicable  only for the ipd and consultation follow-up consultation are applicable for the  OPD that's why I made the different Loop f

or them and same for the Department IDM is you can specify  the service category ID as well and yeah as I said there is a I mentioned hard-coded value in the  service type ID value means whatever that rate then ipd Special Value there is a three value  correct so I will insert that records for the ipd correct so previously 72 records and right  now if you select it then there is a 264 records IP General having the different  departments like correct and belt charges May support one patient admit

ted into the  orthopedic department and with whatever that particular bed or award or a Miss room and under  the orthopedic doctor specialization of the doctor is the orthopedic and they are added in the ipd  general so how much is the rate so we are getting with the help of this service table same with the pathology because there are  different tests means in the practically there are uh Mr uh 300 400 different tests right  now I am inserted only the 12 or 15 records in it and yeah service type

miss this pathology tests  are available for the OPD ipd means a general or special word so I made the three records at  here and uh yes this is the service category ID yeah as I said there is a department ID previously  so you can do the service category ID this is the variable means pathology uh Department we are  receiving the um service category ID so select it yeah 13 for 13 uh category ID I am inserted  uh inserting number of Records correct so first of all translate that particular table

  and insert the data in it and next one is the we have to execute the loop and if you select that  query then you will get yeah ipd and there is a different test correct same for the ipd special  word as well I pretty special is the category and in that there is a Pathology Department and  Pathology Department having the different text and same for the Radiology so I am executing it right okay and how much records are  affected yeah three records one record and let's see execute this so in Radi

ology also you will get the different  services like you could see pathology and yeah this is the X-ray Miss Radiology if we  arrange it by the order by the second column then you will get the supports St dot name I added  that here so instead of that I have I required service category so you have to mention that here  and execute it so Cardiology ringtone Gynecology medicine and after the pathology you will get yeah  Radiology means x-ray is applicable for OPD ipd and ipd general special what s

o CT scan sonography  such like that okay yeah next is the is we covered almost 18 to 20 tables at here and right now we  are moving towards the uh transactional data or we can say some these are the master data and after  that we will see the transactional data and uh right now here is the patient table correct  so how to insert the data in it so let start controller and I am inserting 100  records in it and ID is begin from 1. correct and same logic I applied for the uh stop  name and Doctor n

ame so same logic I applied for the patient as well you will get dynamically  that values so I am inserting 100 records in it execute it yeah num records are inserted  and yeah these are the 100 records are in it this patient first name middle name and last  name as I said this data you will get from the application side UI point of view right now  I am inserting from the back end that's why correct I can insert 50 000 records in it but  first of all you have to modify here means uh 100 records

means there is a ID as a 100 I  inserted already that's why I started it from the one zero one till the 50 000 so execute it so what  it does it will insert 50k records in the table yeah in the message you will get the message  like the one rose affected wonders affected yes so let's wait for the few second yeah means within 41 seconds we got  the data in the patient table means 50 thousand records we got in this table so it  will start from the one zero one as you see correct and uh yeah I thin

k you got the  clear picture about the how generally come how that particular email come correct so I  am moving towards the next table is it fine okay uh I inserted only 50k records you can insert  one lakh record 10 lock records right here nothing limit based on your hard disks yeah okay then next  is the visit means in visit what table it's saying means the patient is come into the hospital  at that time first of all he visit to the so we can mark that particular records as a visit in  the vi

sit table means first of all he will patient ID consult with the doctor correct so we have to  mention the doctor ID and which you need so we have to mention that unit as well and visit that  means on which date he visited to the hospital and OPD number for every record there is a different  opinion number it supports one patient is visited 10 times so different opinion number for him means  one two three up to the ten and for year wise different OPD number managed in the um Hospital  based on t

he government regulations same OPD number and I will apply the same rule for  the ipd number as well I am I am inserting the dynamic records in it means patient category  patient ID doctor ID I am select selecting random doctor ID and there is a unit ID as well  and get that yeah okay so what I will do um I am inserting Android records so let's start execute it and let's see the visit table yeah Miss patient ID 36636 so uh this  patient having the patient category ID as a poor means stop depende

nt and 51 is the  doctor ID and he visited into the unit ID 2 and what is the visited this is the 19 to 2022  I reverted that date that's why it is in the past and uh OPD number what is the OPD number  so what I will do next time I will insert the 10 000 records so I have to begin this  loop from the uh one zero one to the 10 000 means in OPD I will get uh OPD visit  table means visit table I will get 10K records it is a little bit slow as compared to the  patient staff and Doctor record because

we are dynamically getting that value  so it will take few seconds but it is just the little bit more fraction of  second as compared to the previous table because we are getting the value dynamically  and I am inserting 10K records in it and simultaneously you can see the count  of that record size will means let's say count of one yeah 3700 correct yeah it is inserting and same approach  you have to follow if you have to insert 10 lakh records or Miss more than 10 log one CR records  then you

can easily insert that records in this table remaining scripts means  there are four to five table scripts are remaining so we will see in the next session thank you very much for watching this video if you like it please share it and subscribe  my channel thank you very much bye bye