Question

a) Write down the SQL Statement for the below Queries

b) Supply sample tale output

c) the database Used is the University Schema downloaded at lecture

1. Write a query to display the name for those Students who gets more Tot_Cred than the Student whose ID is 55739.

2. Write a query to display the name salary, department name, instructor id for those instructors who works in the same department as the instructor works whose id is 83821.

3. Write a query to display the name salary, department id for those instructor who earn such amount of salary which is the smallest salary of any of the departments

4. Write a query to display the instructor id, instructor name for all instructor who earn more than the average salary.

5. Write a query to display the department name, name for all instructor in the Comp. Comp. Sci.

6. Write a query to display all the information of the instructors whose salary is within the range 60000 and 90000

7. Write a query to display all the information of the instructor whose salary is within the range of smallest salary and 60000

8. Write a query to display all the information for those instructor whose id is any id who earn the second highest salary

9. Write a query to display the instructor number and name for all instructors who work in a department with any instructor whose name contains a T.

10. Write a query to display the instructor id, name, and salary for all instructor who earn more than the average salary and who work in a department with any employee with a i in their name

11. Display the instructor name, id, for all instructor whose department building is Taylor.

12. Write a query to display the instructor name and department for all instructors for any existence of those employees whose salary is more than 70000.

13. Write a query to display the department name and the total salary for those departments which contains at least one instructor

Answer 1

1.      SELECT
                        name
                FROM
                        Students
                WHERE
                        Tot_Cred  > ( 
                                                        SELECT
                                                                Tot_Cred  
                                                        FROM
                                                                students
                                                        WHERE
                                                                ( id = 55739)
                                                )

2.      SELECT
                        name,
                        salary,
                        department.department,
                        instructor_id
                FROM
                        instructor
                        INNER JOIN department ON instructor.department_id = department.department_id
                WHERE
                        instructor.department_id = (
                                                                SELECT
                                                                        department_id
                                                                FROM
                                                                        instructor
                                                                WHERE
                                                                        (instructor_id = 83821)
                                                        )

3.      SELECT
                        name,
                        salary,
                        department_id 
                FROM
                        instructor
                WHERE
                        salary IN (
                                                        SELECT
                                                                MIN(salary)
                                                        FROM
                                                                instructor
                                                        GROUP BY department_id
                                                )
                                                        
 
                                
4.  SELECT
                        name,
                        instructor_id
                FROM
                        instructor
                WHERE
                        salary > (
                                                        SELECT
                                                                AVG(salary)
                                                        FROM
                                                                instructor 
                                                ) 

5.    SELECT
                        name,
                        department.department
                FROM
                        instructor
                        INNER JOIN department ON instructor.department_id = department.department_id
                WHERE
                         (department.department = 'Comp. Sci.')
                         
6.    SELECT
                        *
                FROM
                        instructor
                WHERE
                        salary  BETWEEN 60000 AND 90000
                        
7.    SELECT
                        *
                FROM
                        instructor
                WHERE
                        salary BETWEEN ( SELECT MIN(salary) FROM instructor ) AND 60000
                        
8.     SELECT
                        *
                FROM
                        instructor
                WHERE
                        salary < ( SELECT MAX(salary) FROM instructor ) 
                LIMIT 1 
                
9.        SELECT
                        instructor_no,
                        name                    
                FROM
                        instructor
                WHERE
                        department_id IN ( SELECT department_id FROM instructor WHERE name LIKE '%T%')
                        
10.       SELECT
                        instructor_id,
                        name,
                        salary
                FROM
                        instructor
                WHERE
                        department_id IN ( SELECT department_id FROM instructor WHERE name LIKE '%i%')
                        
                        AND ( salary > (
                                                        SELECT
                                                                AVG(salary)
                                                        FROM
                                                                instructor 
                                                ) )
                        
11.   SELECT
                        name,
                        instructor_id
                FROM
                        instructor
                        INNER JOIN department ON instructor.department_id = department.department_id
                WHERE
                         (department.department_building = 'Taylor')
                         
12.   SELECT
                        name,
                        department.department
                FROM
                        instructor
                        INNER JOIN department ON instructor.department_id = department.department_id
                WHERE
                         (instructor.salary > 70000)

13.   SELECT
                        department.department,
                        SUM(salary) AS TOTAL_SALARY,
                        department.department_id
                FROM
                        instructor
                        INNER JOIN department ON instructor.department_id = department.department_id
        GROUP BY 
                        department.department_id
                         

Explanation : I Write all the sql statement above and create those table namely students, Instructor and department.

I attached table data and sample output of all those statement below.