Friday, 30 August 2013

How to solve these two Queries help please

How to solve these two Queries help please

I am new to SQL and I have a homework assignment. I did all questions
right but still unable to figure out this two queries so please help if
you can. I appreciate you in advance.
I have FOUR tables:
EMPLOYEE which conatin the attributes (Fname, Minit, Lname, Ssn, Bdate,
Address, Sex, Salary, Super_ssn, Dno)
Table DEPARTMENT have the columns ( Dname, Dnumber, Mgr_ssn, Mgr_start_date)
Table PROJECT have the columns ( Pname, Pnumber, Plocation, Dnum)
Table DEPENDENT (Essn, Dependent_name, Sex, Bdate, Relationship)
Q1. For the department that controls the most number of projects, list its
name? I came up with this query but still it just gives me each Department
how many Projects it control but can not get it to work as giving me just
the one that has the most :(
SELECT Dname, COUNT(distinct Pnumber) as NumberOfProjects
FROM Department, Project
WHERE Dnum = Dnumber
GROUP BY Dname;
Q2. Retrieve the names and Ssn of employee who have more dependents than
any other employees?
I came up with this but idk why it does not work. I keep on getting an error
SELECT Fname, Lname, Ssn
FROM Employee
WHERE max((SELECT COUNT(*)
FROM Dependent
WHERE Ssn = Essn));
BTW I am using MySql WorkBench 5.2 and The language is just SQL allowed to
be used

No comments:

Post a Comment