Question

Workshop: SQL Simple Retrieval For this workshop, you are to submit the SQL statements. Do not submit the output produced by running the query. However, you may wish to examine the output to ensure the correctness of the query. When writing a query, do not rely exclusively on the current content of the database. Remember that when working with a real-world company’s database there can be far too many rows to allow you to visually examine the content of a table and validate your query. The following exercises all pertain to the FACULTY table. This table has the following columns: FNO, FNAME, FADDR, FHIRE_DATE, FNUM_DEP, FSALARY, and FDEPT. The table is owned by the user STUDENT.

1. Display the entire FACULTY table.

2. Display all information about any employee whose yearly salary is less than $40000.

3. Display all information about any employee who is employed by the Computer and Information Systems (CIS) Department.

4. Display the department, faculty number, and name of all faculty members.

5. Display the name and date of hire for any faculty member whose department alphabetically precedes THEO.

6. Display the departments to which faculty are assigned. Do not show duplicate values.

7. Display the name and salary of any employee earning less than $36000. Present the result set such that the highest paid employee is listed first, the second highest is listed next, and so on - that is, show the result with salaries in descending sequence.

8. Display the department, employee name, and salary for every employee. The output should be presented such that rows are arranged by salary within department. (Employees assigned to the same department will appear next to each other, but these rows will be sorted by salary.)

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

This demonstration is using SQL Server.

Question 1:

SQL Query :

select * from faculty;

*********************************

Question 2:

SQL Query :

select * from faculty where FSALARY*12 < 40000;

*********************************

Question 3:

SQL Query :

select * from faculty where FDEPT='CIS';

*********************************

Question 4:

SQL Query :

select FDEPT,FNO,FNAME from faculty;

*********************************

Question 5:

SQL Query :

select FNAME,FHIRE_DATE from faculty where FDEPT='EHTO%';

*********************************

Question 6:

SQL Query :

select distinct(f.FDEPT) from faculty f,faculty f1
where
f.FNO=f1.FNO;

*********************************

Question 7:

SQL Query :

select FNAME,FSALARY from faculty where FSALARY <36000 order by FSALARY desc;

*********************************

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.