Question

Write a SELECT statement that returns six columns, based on the eqpTotalValue field in the EQUIPMENT table:

a. The eqpDescription should display in the first column

b. The original eqpTotalValue should display in the second column

c. Use the CAST function to return the eqpTotalValue in the third column as data type decimal with 2 digits to the right of the decimal point

d. Use the CAST function to return the eqpTotalValue in the fourth column as a varchar

e. Use the CONVERT function to return the eqpTotalValue in the fifth column as decimal with 2 digits to the right of the decimal point

f. Use CONVERT to return the eqpTotalValue in the sixth column as varchar, using style 1

g. Use a $ to display on the left of the value where possible (HINT: concatenation)

h. Use appropriate descriptive column alias names

i. The highest eqpTotalValue should display at the top of the result set

10. (10 pts) Write a SELECT statement that returns six columns based on the empFirstName, empLastName and EmpDateOfBirth fields in the EMPLOYEE table:

a. The employee first and last names concatenated with a space between the name parts

b. Use the CAST function to return the first column as a data type varchar

c. Use EmpDateOfBirth and the CONVERT function to return the second, third and fourth columns as a varchar, using style 1, 7 and 10 respectively.

d. Use the EmpDateOfBirth and the CAST function to return the fifth column as data type real

e. Display the unformatted empDateOfBirth as the sixth column

f. Use appropriate descriptive column alias names

g. The oldest employee should be listed at the top of the result set

Answer 1

Certainly! For the first part involving the `EQUIPMENT` table:

```sql

SELECT

eqpDescription AS 'Equipment Description',

eqpTotalValue AS 'Original Total Value',

CAST (eqpTotalValue AS DECIMAL(12, 2)) AS 'Total Value as Decimal',

CAST (eqpTotalValue AS VARCHAR) AS 'Total Value as Varchar',

CONVERT (DECIMAL(12, 2), eqpTotalValue) AS 'Converted Decimal Value',

CONCAT ('$', CONVERT(VARCHAR, eqpTotalValue, 1)) AS 'Converted Varchar Value',

FROM

EQUIPMENT

ORDER BY

eqpTotalValue DESC;

```

For the second part involving the `EMPLOYEE` table:

```sql

SELECT

CONCAT (empFirstName, ' ', empLastName) AS 'Full Name',

CONVERT (VARCHAR, EmpDateOfBirth, 1) AS 'Date of Birth (Style 1)',

CONVERT (VARCHAR, EmpDateOfBirth, 7) AS 'Date of Birth (Style 7)',

CONVERT (VARCHAR, EmpDateOfBirth, 10) AS 'Date of Birth (Style 10)',

CAST (DATEDIFF(YEAR, EmpDateOfBirth, GETDATE()) AS REAL) AS 'Age',

EmpDateOfBirth AS 'Unformatted Date of Birth'

FROM

EMPLOYEE

ORDER BY

EmpDateOfBirth ASC;

```

Step-by-step explanation:

The first SQL query retrieves information from the 'EQUIPMENT' table. It displays six columns: 'Equipment Description,' 'Original Total Value,' 'Total Value as Decimal,' 'Total Value as Varchar,' 'Converted Decimal Value,' and 'Converted Varchar Value.' The 'eqpDescription' and 'eqpTotalValue' columns are shown directly, while the third and fourth columns convert 'eqpTotalValue' to decimal and varchar data types, respectively. The fifth and sixth columns use the CONVERT function to represent 'eqpTotalValue' as a decimal with two digits to the right of the decimal point and as a varchar with a dollar sign concatenated on the left. The results are ordered by 'eqpTotalValue' in descending order.

The second SQL query operates on the 'EMPLOYEE' table, showcasing six columns: 'Full Name,' 'Date of Birth (Style 1),' 'Date of Birth (Style 7),' 'Date of Birth (Style 10),' 'Age,' and 'Unformatted Date of Birth.' It concatenates the first and last names, displays the date of birth in three different styles using the CONVERT function, calculates the age based on the birthdate, and shows the unformatted birthdate. The results are ordered by 'EmpDateOfBirth' in ascending order, listing the oldest employees first.