SYM 400 Introduction to Database Structures.
I have to give the records of the FirstName “John” in the Person table with the full name and the
number of records. To do this I input the following query: Employees named John were as
follows: SELECT CONCAT(FirstName, ‘ ‘, MiddleName, ‘ ‘, LastName) As
Employees_Named_John From Person. Person Where FirstName = John There were 58 results
, for employees named John altogether To get the column count I used the following query
SELECT*FROM Person. Person Where FirstName = ‘John’
How many person are there in the database, SQL query to get this is:- SELECT Count (*) From
Person. Person Where FirstName = ‘John’. Now I have to remove one record of one John T.
Moore since he was an incorrect entry and we need to remove him using business entity ID. To
do this I use the following SQL transaction: It will remove * From the Person. Person Where
BusinessEntity = ‘18327’ :Person Where BusinessEntity = ‘18327’ :
Next, the human resources manager has asked for the full list of the e-mail address of each
employee. To do this I used this query: SELECT CONCAT (FirstName, ‘ ‘, LastName) as
FullName, pe. EmailAddress From Person. This is achieved through the use of person as the
principal predicate logical path with an inner join person. EmailAddress as pe ON pp
BusinessEntityID = pe EmailAddressID. To do so I simply require finding the full name of the
employee registered with the email address ana17@adventure-works. com when properly
employing a WHERE clause. To do this I added one more line to the query used in question 3:
SELECT CONCAT (FirstName, ‘ ‘, LastName) as FullName, pe. EmailAddress From Person.
Person as pp
Inner Join Person. Having a foreign key relation with the Address EmailAddress as pe ON pp.
BusinessEntityID = pe. id WHERE EmailAddress = ‘’. com This
proves that it is the employee namely David Coleman who had the specific email address and the
addition of this WHERE clause. line saved me a lot of time otherwise they would have been used
to searching for the employee among 19167 results by going through the list one by one starting
from this email. The pictures below will provide a better understanding.
I have to give the records of the FirstName “John” in the Person table with the full name and the
number of records. To do this I input the following query: Employees named John were as
follows: SELECT CONCAT(FirstName, ‘ ‘, MiddleName, ‘ ‘, LastName) As
Employees_Named_John From Person. Person Where FirstName = John There were 58 results
, for employees named John altogether To get the column count I used the following query
SELECT*FROM Person. Person Where FirstName = ‘John’
How many person are there in the database, SQL query to get this is:- SELECT Count (*) From
Person. Person Where FirstName = ‘John’. Now I have to remove one record of one John T.
Moore since he was an incorrect entry and we need to remove him using business entity ID. To
do this I use the following SQL transaction: It will remove * From the Person. Person Where
BusinessEntity = ‘18327’ :Person Where BusinessEntity = ‘18327’ :
Next, the human resources manager has asked for the full list of the e-mail address of each
employee. To do this I used this query: SELECT CONCAT (FirstName, ‘ ‘, LastName) as
FullName, pe. EmailAddress From Person. This is achieved through the use of person as the
principal predicate logical path with an inner join person. EmailAddress as pe ON pp
BusinessEntityID = pe EmailAddressID. To do so I simply require finding the full name of the
employee registered with the email address ana17@adventure-works. com when properly
employing a WHERE clause. To do this I added one more line to the query used in question 3:
SELECT CONCAT (FirstName, ‘ ‘, LastName) as FullName, pe. EmailAddress From Person.
Person as pp
Inner Join Person. Having a foreign key relation with the Address EmailAddress as pe ON pp.
BusinessEntityID = pe. id WHERE EmailAddress = ‘’. com This
proves that it is the employee namely David Coleman who had the specific email address and the
addition of this WHERE clause. line saved me a lot of time otherwise they would have been used
to searching for the employee among 19167 results by going through the list one by one starting
from this email. The pictures below will provide a better understanding.
