In sql using adventure works 2012 data base for microsoft sq

In sql using adventure works 2012 data base for microsoft sql server

while using the HR employee table, write the following statements for each

show all columns

show distinct rows for a specific column

show 4 specific columns

show 4 specific columns which (2) will have alias names

Solution

As you have given, the table name is Employee.

1. For showing all columns, (I assume this is related to showing all data), query would be:

SELECT * FROM employee; [This would select all column data from employee for all rows].

In case the intention is just to show column names, the command would be:
sp_columns [tablename]
OR

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = \'\'employee\'\'

2. For showing rows for a specific column.

SELECT DISTINCT column_name FROM employee; [This would give all rows for which column name is column_name and the value of each column is distinct so that there\'s no duplicacy].

3. Show 4 specific columns.

SELECT column_name1, column_nam2, column_nam3, column_name4 FROM employee [You can change the column names and their order as per convenience and the order in the result would vary according to that.]

4. show 4 specific columns which (2) will have alias names
Alias is a mechanism to simplify the naming conventions. For example, in the employee table, consider you have following column names to store his/her information:

FirstName, LastName, JoiningDate

Now, all these fields basically indicate the names of employees and their date of joining. You might want to rename joining date to something you understand more clearly, maybe calling it as a field \'\'FirstDay\"

SELECT FirstName, LastName, JoiningDate AS FirstDay
FROM employee

WHERE EndDate IS NOT NULL;                [Assuming EndDate is another field in employee table]

Here FirstDay is an alias and the result would be something in this format

FirstName     LastName      FirstDay
John              Doe               5 Sept 2014

Kennedy        Lincoln          6 Oct 2013

In order to clear things a bit more about aliasing, consider another example for an employee.
Suppose you have 7 columns : FirstName, LastName, BaseRate, Address, City, Role, Department

Let\'s consider following employees:

FirstName LastName BaseRate Address             City Role                         Department

Kelly          Brooks        15            XYZ Street        Wisconsin    Developer                Technology

Brendon    McCullum    10           Street 3            Wellington    DataEntryOperator Administration


For the above employees, displaying data in 8 columns is little inconvenient, so you can use aliasing to combine them up and display it in a cleaner format.

SELECT CONCAT(FirstName, \' \', LastName) AS Name, BaseRate, BaseRate * 40 AS GrossSalary, CONCAT(Address, \',\', City) AS Address, CONCAT(Role, \', \', Department) AS Role

FROM employee

[Here CONCAT method is SQL method, used to combine data from 2 columns].

Result would be something like this:
Name                           BaseRate    GrossSalary   Address                             Role

Kelly Brooks                15                600                XYZ Street,Wisconsin       Developer, Technology

Brendon McCullum      10               400                Street 3,Wellington           DataEntryOperator, Administration

So, aliasing can be used for any number of columns and also for table names to simplify the naming so that the results obtained are succinct and concise.