Solution: Vendor integration - SQL教程

- [Instructor] To solve this challenge, we'll focus on string manipulation. My solution returns some values from the employee's table, ordered by name. Without a where condition we'll get all the records and that's what we want. The first item in the challenge asked for a strictly five digit employee ID, with leading zeros to fill empty spaces at the beginning of the string. To create this, I used the LPAD or left pad function, which takes three arguments, the field to include, the number of digits to pad to, and the digit or character to use for that padding. I'll call this ID. The next item in the challenge is to provide the employee names in the format last comma first with a space in the middle. So I use the concat or concatenation function to return one string made out of the last name value, a string value comma space, and the first name value, and I'll call this name. Third, I need to generate usernames that are no longer than eight characters, and which include the employee's first initial and whatever part of their last name fits in the remaining seven characters. That all needs to be lowercase. So I start out using substring to slice a specific piece of the string's first name and last name. For first name, I start at character one and return one character. That's the first character in the string. For last name, I start at character one again and I return seven characters from that point. If a string is longer than seven characters, I just get the first seven characters. And if it's shorter than seven characters, I'll get the full string. Then I use the concat function to stick these two strings together with the first initial first. And then I use the lower function to convert that all to lowercase, and we'll call that value login. And finally, we'll generate email addresses at which our employees can be reached. Our employees have a username in our system, but unfortunately that's different than the logins that we needed to generate for them on this other system. For example, our founder and head chef have their first name as a username in our system, instead of a regular username like the other employees. So we'll use concat again to stick each user's real username to this string here that represents our email domain, and we'll call that value email. I'll run this and let's see what we get. Great, this passes the challenge. We'll often need to transform data from our databases in some way instead of just returning it verbatim.