Question

Hi i need to write an SQL Query to list the given for these questions below

Example: SELECT emp_lname -- but it doent seem to work

--1. Give a Listing of all the employees having their first name,

last name and full name, order the report by the employee's last name

--2. Give a listing of all employees that have a last name that

begins with the letters between A-K

--3. Give a Listing of all the employees having their first name,

last name, full name and full telephone number where the area code is 412

--4. Give a Listing of all the addresses of employees not residing

in Florida

Answer 1

1. Give a Listing of all the employees having their first name, last name and full name, order the report by the employee's last name

Ans: select f_name, l_name, (f_name || ' ' || l_name) as full_name from emp ORDER BY l_name;

Note: We have considered first name column as f_name, last name column as l_name. We have derived a new column in our output with name; full_name by concatenating first name and last name.

2. Give a listing of all employees that have a last name that begins with the letters between A-K

Ans: select * from emp where l_name like 'A%' OR l_name like 'B%'  OR l_name like 'C%' OR l_name like 'D%' OR l_name like 'E%' OR l_name like 'F%' OR l_name like 'G%' OR l_name like 'H%' OR l_name like 'I%' OR l_name like 'J%' OR l_name like 'K%';

Note: In the above query, all employee details will be displayed whose last name begins with letters between A-K.

3. Give a Listing of all the employees having their first name,last name, full name and full telephone number where the area code is 412

Ans: select f_name, l_name, (f_name || ' ' || l_name) as full_name, t_no from emp where area_code=412;

4. Give a Listing of all the addresses of employees not residing in Florida

Ans: select (fname || ' ' || l_name) as full_name, address from emp where city not in ('Florida');

Please Note:- we have referred employee table to emp, first name to f_name, last name to l_name, telephone number to t_no, area code to area_code and residing city to city. Also, we have derived a new column full_name by concatenating f_name and l_name at run time.