COP 4710 SQL retrieval queries

The purpose of this assignment is to master the design of SQL retrieval queries using Company database in XAMPP.

For each of the following four queries, create one SQL query and test it on Company database:

List the location(s) of the department where the employee with ‘Michael’ as a dependent, works for. — [q1.sql]

List the last name of the manager for the project ‘Computerization’ along with the number of work assignments for this project. — [q2.sql]

List the name of project(s) managed by the ‘Research’ department with the total number of work hours assigned to each of these projects. — [q3.sql]

• List the last name of employees supervised by the manager of ‘Headquarters’ department. and the number of subordinates for each of these employees. — [q4.sql]
• Upload all four query files through Canvas along with the following declaration
•     ========================================================

      Name: _________________________

      Panther-ID: x x x -_______

      Course: COP 4710

      Assignment#: 2

      Due: Mon, Feb 20, 2023

      I hereby certify that this work is my own
      and none of it is the work of any other person.

                              Signature: ______________________

  The output for all queries of Asg2 is given below.
  To receive the correct score for your SQL queries,
   – the query must run in XAMPP
   – the result of the query must be correct (as listed here)
   – importantly, the semantics of the query must satisfy the specification
   – the query must be readable with suitable comments
   – the query should NOT use any data values that is not listed in the specification.

  ==============================================================================
  q1.sql [20]

  /* Company database  

    List the location(s) of the department
    where the employee with ‘Michael’ as a dependent, works for.

     Result: (3 rows)
     DLocation
     ———    
     Bellaire
     Houston
     Sugarland                        

  */

  =============================================================================
  q2.sql [30]

  /* Company database  

      List the last name of the manager for the project ‘Computerization’
      along with the number of work assignments for this project.

     Result: (1 row)
     LNAME     Projects
     —–     ——–
     Wallace      3

  */

  =============================================================================
  q3.sql [25]

  /* Company database  

     List the name of project(s) managed by the ‘Research’ department
     with the total number of work hours assigned to each of these projects.

     Result: (1 row)
     PNAME      TotalHours
     —–      ———-
     ProductX     52.5
     ProductY     37.5
     ProductZ     50.0     
  */

  =============================================================================
  q4.sql [25]

  /* Company database  

      List the last name(s) of employees supervised by the manager of ‘Headquarters’ department.
      and the number of subordinates for each of these employees.

     Result: (2 rows)
     LNAME     Subordinates
     —–     ————
     Wong        3
     Wallace     2

  */

  SQL queries (using XAMPP)
  Query    Points

  q1     20

  q2     30

  q3     25

  q4     25
      ——
  Total   100
  Penalty:

  – Absence of originality declaration
       Name, ID, declaration, etc ……    -10
       Signature …………………..    -10
       Comments …………………… -10