The date functions remember you’re always at a number that’s the number of days you subtract the number you’re subtracting that number of days use that for days hours minutes seconds months weeks if you want months then you do your date likes estate eight months of cyst 8 commas 1 will add 1 month if you want years it’s you got to use the add months there is no eight years so you say select add months of cysts 8 comma 12 12 years come a 24 and so on ok questions anybody before lab for the lab in this chapter single row functions you’re going to use the HR schema and we’ve got five six things in the lab in the HR schema and then in the OE schemer you’ll do the lab in the back of the book ok when you get done with these first six you know those are the key when you’re done with those first six then just raise your hand, ok everyone let’s go over the solutions first thing pole you ask me a question about replacing something did that work for you pull the response.
I gave you that was Paul Fitzpatrick sorry about that Paul you there ok no response from Paul let’s check with other people Gracie you there anybody there can you hear me yes I can hear you okay okay great okay good well most of you didn’t finish this but that’s okay I’m going to go over the solutions now as I said you know we’ve got a lot of stuff to cover I’ll go over the solution so you could and you will get the solutions next week to all of the labs so you can finish this at a later point if you want and so on so I just lowered the hands with a couple of people that did finish and let’s look at the questions let me get the first question up here okay the first question was display the employee ID first name for all the first names that contain a lowercase da make it case sensitive so we said like quote % d a percent close quote but we wanted a case sensitive.
Therefore we need to do a lower of first name in order to capture that on the lower and that other if we did not do a lower a first name we would not have catch captured the David because it starts with it uppercase D and we wouldn’t have missed that so that’s fairly straightforward name one lower a first name that’s using lower or upper functions to do case-insensitive searches okay next one was number two select employees display a couple some fields their first employee ID first name and the length of the first name and the position of the DA within the first name string the search for da should be case-insensitive again so I selected here length of first name so I the first name here for Ellen is 54 sunder is 6 the length of that and so on and now we’re looking for the location of the DA well I’m going to for a location of a string you have to do an in string in string but I wanted case insensitive so I’m going to have to upper the first name and look for my DA or I could have lowered.
The first name you look for a lowercase da now notice one thing we didn’t chat about before but if it can’t find this string in here it returns a 0 there is no danl and it returns to 0 there is a DA in Sun dar and it starts in the fourth position there is a no da here there is a DA in David and it starts in the first position now looking for a DA and a name is not kind of not very meaningful but you might want to look for some special character such as space which we’re going to do in another one of the examples coming up here where you might have a name its first space last and every name that you’re getting is first base last and you want to extract the last name out of it well what you have to do is look for space and then say okay well if space is in the fifth position I’m going to add one to that’s six and then I’m going to do a substring and start in the sixth position and get all the rest of the characters so sometimes.
If you know where one character is you know where the rest of the string is you want first base last find the space start one character past that pick up the last name or find the space and subtract one from that and start in character position one for that length and you get the first name so you can pull up hot pieces of a name if you always have first space middle space last you can use that to get all p all the pieces and break them up so that’s what that’s all about alright that’s number two any questions on that nobody seems to have questions today that always bothers me when no one’s had questions you know we’re going to raise some questions we’ll find a way to get some questions here number three select all jobs in the jobs table we’re going to display the job ID the job title and give me the job title but replace the word representative with associate so here’s my job ID job title and now i’m going to do a replace it says replaced job title fine representative replace it with associate.
So here where are we going to find a representative here’s a sales representative it will become sales associate here is a marketing representative it comes becomes marketing associate and so on ok so that’s our replace what Paul had asked was is there any way to get rid of a character like if you have first dot last and you want to get or something that has a dot and he wants to get rid of the dot I think it was a number or something like that for 12 points 82 and he wanted to get for 144 1282 without the period well what you would do is you do a replace but replace the period with a null and then you’d be fine and you could even say replace and say here replace the period with the keyword null and ull or replace a period with a quote which is to know or just say replace the period and if you don’t say what you replace it with it replaces it with a null by default that was a good question number four.
So that’s the one I really could not get to run you couldn’t get that one to run okay first of all I don’t know I’m looking at your sequel and I’m looking at my secret I don’t see a difference I kept giving me error I don’t know anything what really simple to run so I this replace yeah this one yeah okay okay well let’s let’s let me copy my sickle and give it to sure why not that sounds good I’m going to get a add code in a minute we’ll show it to everybody and then we’ll see if we can figure out what went wrong good okay how come I can please describe all you got to do is solve the problem of pasting well if I can face in the window don’t be great there we go okay select job ID title from jobs oh okay it this is let’s take a look at this I’ll copy it and show it to everybody okay here’s the issue you don’t say select this from that replace this you’re going to do a select job ID job title comma and then replace job title the from goes afterwards okay gosh you’re actually displaying another column.