Lab1答案与解析

-- Lab1
-- 1. List all staff details.
SELECT 
    *
FROM
    staff;

-- 2. List staff whose salary is between 8,000 and 20,000. Show staff name, position and salary.
SELECT 
    fName, lName, position, salary
FROM
    staff
WHERE
    salary BETWEEN 8000 AND 20000;

-- 3. List details of staff in branch B003 and B007.
SELECT 
    *
FROM
    staff
WHERE
    branchNo = 'B003' OR branchNo = 'B007';

-- 4. Display all the distinct different types of positions of staff.
SELECT DISTINCT
    position
FROM
    staff;

-- 5. List all branch offices in London, give branch number and address.
SELECT 
    branchNo, street
FROM
    Branch
WHERE
    city = 'london';

-- 6. List the employees whose first names have 'an' in them.
SELECT 
    staffNo, fName, lName
FROM
    Staff
WHERE
    fName LIKE '%an%';

-- 7. List the client numbers and names, order by client number.
SELECT 
    clientNo, fName, IName
FROM
    Client
ORDER BY clientNo;

-- 8. Find the property number and address of all properties for rent which has not been assigned to a staff.
SELECT 
    propertyNo, street
FROM
    PropertyForRent
WHERE
    staffNo IS NULL;

-- 9. How many properties for rent are managed in each branch?
SELECT 
    branchNo, COUNT(propertyNo)
FROM
    PropertyForRent
GROUP BY branchNo;

-- 10.  List all properties for rent in descending order of their rent.
SELECT 
    *
FROM
    PropertyForRent
ORDER BY rent DESC;

-- 11. Calculate the annual rent for all properties for rent (the rent in DreamHome Database is monthly rent).
SELECT 
    propertyNo, rent * 12 AS annualRent
FROM
    PropertyForRent;

-- 12. Find all the staff who is not a manager and with a salary greater than or equal to 12,000.
SELECT 
    staffNo, fName, lName, position, salary
FROM
    Staff
WHERE
    (position != 'Manager')
        AND salary >= 12000;

-- 13. Find out how many managers there are without listing them.
SELECT 
    COUNT(staffNo) AS managerNumber
FROM
    Staff
WHERE
    position = 'Manager';

-- 14. Calculate average salary for all staff.
SELECT 
    AVG(salary) AS avgSalary
FROM
    Staff;

-- 15. Calculate average salary for staff at each Branch.
SELECT 
    branchNo, AVG(salary)
FROM
    Staff
GROUP BY branchNo;

-- 16.For each branch with more than 1 member of staff, find number of staff in each branch and their average salaries.
SELECT 
    branchNo, COUNT(staffNo), AVG(salary) AS avgSalary
FROM
    Staff
GROUP BY branchNo
HAVING COUNT(staffNo) > 1;



-- 17.Find the highest salary and lowest salary of staff and the difference between them.
SELECT 
    MAX(salary),
    MIN(salary),
    MAX(salary) - MIN(salary) AS difference
FROM
    Staff;

-- 18. Count the number of properties for rent with 3 rooms.
SELECT 
    COUNT(propertyNo)
FROM
    PropertyForRent
WHERE
    rooms = 3;

-- 19. Find the average salary for assistants and managers.
SELECT 
    AVG(salary) AS avgSalary
FROM
    Staff
WHERE
    position = 'Manager'
        OR position = 'Assistant';

-- 20.Calculate the weekly rent for all properties for rent, and round the results to the nearest penny. Assuming there are 4 weeks in a month. (Use ROUND(X,D) function, this rounds the argument X to D decimal places.)
SELECT 
    ROUND(AVG(rent) / 4, 2) AS weeklyRent
FROM
    PropertyForRent;

-- 21. List the properties for rent which have not been viewed at all. (Hint: use subquery)
SELECT 
    propertyNo
FROM
    PropertyForRent
WHERE
    propertyNo NOT IN (SELECT 
            propertyNo
        FROM
            Viewing);