Assignment-2
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.5q]]
List the last name of employees supervised by the manager of Headquarters’ department. and the number of subordinates for each of these employees.
[a4sql]
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
nple output for Asg2 queries =:
The output for all queries of Asg2 is given below.
To receive the co
ect score for your SQL queries,
- the query must run in XAMPP
- the result of the query must be co
ect (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.
es]
Ja SEE
List the location(s) of the department
where the employee with ‘Michael’ as a dependent, works for.
Result: (3 rows)
presse
Bellaire
ee
pratt]
=
a2.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)
Se
3
=
es]
PARC ELE EEE
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)
RES
52.5
37.5
50.0
=
Pes]
* 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)
NAME Subordinates
XAMPP (Apache, MariaDB, PHP, Perl)
==================================
Download the xampp.7z from the course website to C:\ and then unzip it
using 7-zip open source tool (extract to C:\).
Note that the xampp folder must be placed at the root folder of the drive.
This XAMPP folder is not bound to the Windows registry.
To uninstall, simply delete C:\xampp directory.
To start xampp software (to automatically install apache web server and php server),
double click on C:\xampp\xampp_start.exe file. This will open a DOS command window and
an xampp session will be initiated.
To close the xampp session, double click on C:\xampp\xampp_stop.exe file.
When an xampp session is active, visit the URL http:
localhost:88/phpmyadmin
The Apache web server maps this URL to C:\xampp\htdocs\course folder.
This URL
ings the database environment where you can run SQL queries
on the following databases: company, henry (books), premiere (products).
To find the status of all services, double click on C:\xampp\xampp_control.exe file.
For details of these databases, visit the co
esponding folders in
C:\xampp\htdocs\course\scripts\ directory.
If you are already running a separate webserver for some other purpose
and if the web server already listening to port 88,
it will not allow xampp to listen at port 88.
To determine this condition, run C:\xampp\xampp-control.exe
and if the status port 88 is being used, you need to configure xampp as below:
Edit C:\xampp\apache\conf\httpd.conf file and change
Lines 59, 60 and 228 as
Listen 8080
and save it.
Execute C:\xampp\xampp_start.exe
and then visit http:
localhost:8080/phpmyadmin to access the databases.
9/24/2020