CSC 455
Fall 2009
Lab 15 SQL Part 3
Continue working on the Company database and continue using the web interface for MySql called phpMyAdmin.
Log into phpmyadmin at http://otherone.cis.uncw.edu/phpmyadmin/ Ignore any security certificate warnings and proceed.
In the upper left corner, select the database that corresponds to your userid.
Go to the SQL tab and enter the SQL commands to do the following
queries. Once the query
is working, copy and paste the SQL into a document. Print the queries and hand in. If you want to save the query for review
later, you can bookmark it at the bottom of the screen.
1. Find
the names of all employees who do not have a supervisor. Sort alphabetically by last name.
2.
Show the average
salary by department number.
3.
Show the average
salary by department name instead.
4.
List Essn of
employees who work on two or more projects.
Hint: use GROUP BY
and HAVING
5.
List the first name, last name, and number
of projects of employees who work on two or more projects. Hint: same as above, but join tables first, group by Fname,
Lname
6.
Use a nested query
with IN to show the name of the employee with the highest salary.
The
schema is:
EMPLOYEE
(Fname, Minit, Lname, Ssn, Bdate, Address, Sex, Salary, Super_ssn,
Dno)
foreign key (Super_ssn)
references EMPLOYEE(Ssn),
foreign
key (Dno) references DEPARTMENT(Dnumber)
DEPARTMENT
(Dname, Dnumber, Mgrssn, Mgrstartdate)
foreign key (Mgrssn) references
EMPLOYEE(Ssn)
DEPT_LOCATIONS (Dnumber, Dlocation)
foreign key (Dnumber) references DEPARTMENT(Dnumber)
DEPENDENT (Essn, Dependent_name, Sex, Ddate, Relationship)
foreign key
(Essn) references EMPLOYEE(Ssn)
PROJECT (Pname, Pnumber, Plocation, Dnum)
foreign key (Dnum)
references DEPARTMENT(Dnumber)
WORKS_ON (Essn, Pno, Hours)
foreign key (Essn) references EMPLOYEE(Ssn),
foreign key (Pno) references PROJECT(Pnumber)