1. List the complete records for all employees earning $35,000 or more per year.
SELECT *FROM Employee WHERE Salary >= 35000;
2. List just the first name, middle initial and last name for employees earning $35,000 or more per year.
SELECT fname, Minit, Lname FROM Employee
Copyright By PowCoder代写 加微信 powcoder
WHERE Salary >= 35000;
3. List the first and last names and social security numbers for everyone working for the supervisor with social security number 333-44-5555.
Select fname, Lname, ssn
FROM Employee
WHERE Super_ssn=333-44-5555;
4. List the names of the departments with locations in Houston.
SELECT Dname
FROM Department AS d, Dept_Locations AS dl
WHERE d.Dnumber= dl.Dnumber AND Dlocation = ‘Houston’;
5. List the first and last names of the employees working on Product Y.
SELECT fname, Lname
FROM Employee AS e, Works_On AS w
WHERE e.ssn= w. Essn AND Pno=2;
6. List the names of ‘s dependents and their relation to him.
SELECT Dependent_name, Relationship
FROM Dependent AS d, Employee AS e
WHERE d.Essn=e.ssn AND e.ssn=333445555;
7. List the full names and salaries for everyone working for .
SELECT CONCAT(fname,Minit, Lname) AS FullName, Salary
FROM Employee
WHERE Super_ssn= 333445555;
8. List the full names and salaries for every in the Research Department.
SELECT CONCAT(fname,Minit, Lname) AS FullName, Salary
FROM Employee AS e, Department AS d
WHERE e.Dno=d.DNumber AND Dname=’Research’;
9. List the average salaries for employees working for each of the departments.
SELECT AVG(Salary)
FROM Employee
GROUP BY Dno;
10. List the total number of employees working on the ProductX project.
SELECT COUNT(*)
FROM Works_On
GROUP BY Pno= 1; (Can I do this or do I have to go directly to the Project table too and get ‘Product X’)
11. List the total number of employees working on each project.
SELECT COUNT(*)
FROM Works_On
GROUP BY Pno;
12. List the first name, last name, Supervisor’s first and last name, Departmnt number and the average salary for everyone in Administration
SELECT fname, Lname, CONCAT(fname, Lname, Super_ssn) AS Supervisor Name, AVG(Salary), Dno
FROM Employee
WHERE Dno= 4;
13. For every employee with a dependent, list the dependent’s first name, the employee’s last name, and relation to the employee.
SELECT Dependent_name, Lname, Relationship
FROM Employee AS e, Dependent AS d
WHERE d.Essn= e.ssn
GROUP BY Dependent_name, d.Essn;
14. List the name of employees with a dependent named “Alice” and the relationship to the employee.
SELECT fname,Minit, Lname, Relationship
FROM Employee AS e, Dependent AS d
WHERE Dependent_name = ‘Alice’ AND d.Essn= e.ssn;
15. List everyone who works in Research (name and salary).
SELECT fname,Minit, Lname,Salary
FROM Employee as e, Department as d
WHERE e.Dno= d.Dnumber AND Dname= ‘Research’;
16. For each project, list project name, location, department and the superior of everyone working on that project.
SELECT Pname, Plocation, Dnum, Mgr_ssn
FROM Project AS p, Department as d
WHERE p.Dnum= d.Dnumber
GROUP BY Pname;
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com