Question

Please write the SQL statement for the following. I am using the Adventurework2014 database.

1. Create a login for AdventureWorks employees. An employee login should be composed of the first letter of a person's first name combined with their last name. In addition, the login should be all lower case characters. All the required information is located in Person.Person table. Employees can be identified by "EM" value in the PersonType field. The output should include BusinessEntityID, first name (FirstName), last name (LastName), and EmpLogin alias for the login column. **Hint use the substring fuction which takes the following format:

- expression: is a character, binary, text, ntext, or image expression

-start is an integer of bigint expression that specifies where the returned characters start. (The numbering is 1 based, meaning that the first character in the expression is 1). if start is less than 1, the returned expression will begin at the first character that is specified in expression. In this case, the number of characters that are returned is the largest value of either the sum of start + length -1 or 0. If start is greater than the n umber of characters in the value expression, a zero length expression is returned.

-length - is a positive integer of bigint expression that specifies how many characters of the expression will be returned. If length is negative, an error is generated, and the statement is terminated. If the sum of start and length is greater than the number of characters in expression, the whole value expression beginning at start is returned.

2. From Sales.SalesOrderHeader table, list all orders (SalesOrderNumber, OrderDate) that were placed in June of 2011. Use date manipulation functions.

Please only answer if you know the correct answer. I will give a thumbs up if the answer is correct and down if incorrect.

Answer 1

Query 1:

Oracle:

select businessentityid,firstname,lastname,lower(substr(firstname,0,1)||lastname) as emplogin
from person
where persontype='EM';

Sql server:

select businessentityid,firstname,lastname,lower(concat(substring(firstname,1,1),lastname)) as emplogin
from person
where persontype='EM';

Output:

Query 2:

Oracle:

select salesordernumber,orderdate
from salesorderheader
where to_char(orderdate,'MM')='06'
and to_char(orderdate,'YYYY')='2011';

Sql Server:

select salesordernumber,orderdate
from salesorderheader
where datename(month,orderdate)='June'
and year(orderdate)='2011';

Output: