2.3 SQL examples in slides

-- Eg1. selection of all columns
SELECT 
    *
FROM
    Staff;

-- Eg2. selection of specific columns
SELECT 
    staffNo, fName, lName, salary
FROM
    Staff;

-- Eg3. Use of distinct
SELECT 
    propertyNo
FROM
    Viewing;

SELECT DISTINCT
    propertyNo
FROM
    Viewing;

-- Eg4. Calculated fields
SELECT 
    staffNO, fName, lName, salary / 12
FROM
    Staff;
-- we can use 'as' to rename a column
SELECT 
    staffNO, fName, lName, salary / 12 AS monthlySalary
FROM
    Staff;

-- Eg5. Comparison Search Condition
SELECT 
    staffNo, fName, lName, position, salary
FROM
    Staff
WHERE
    salary > 10000;

-- Eg6. Compound Comparison Search Condition
SELECT 
    *
FROM
    Branch
WHERE
    city = 'London' OR city = 'Glasgow';

-- Eg7. Range Search Condition
SELECT 
    staffNo, fName, lName, position, salary
FROM
    Staff
WHERE
    salary BETWEEN 20000 AND 30000;

-- Eg8. Pattern Matching
SELECT 
    ownerNo, fName, IName, address, telNo
FROM
    PrivateOnwer
WHERE
    address LIKE '%Glasgow%';

-- Eg9. NULL search Condition
SELECT 
    clientNo, viewDate
FROM
    Viewing
WHERE
    propertyNo = 'PG4' AND comment IS NULL;

-- Eg10. Single Column Ordering 
SELECT 
    staffNo, fName, lname, salary
FROM
    Staff
ORDER BY salary DESC;

-- Eg11. Multiple Column Ordering 
SELECT 
    propertyNo, type, rooms, rent
FROM
    PropertyForRent
ORDER BY type , rent DESC;

-- Eg12. Use of Count(*)
-- how many propewrties cost more than 350?
SELECT 
    COUNT(*)
FROM
    PropertyForRent
WHERE
    rent > 350;

-- version2 use as to name a colunm
SELECT 
    COUNT(*) AS myCount
FROM
    PropertyForRent
WHERE
    rent > 350;

-- Eg13. use of count(distinct)
-- how many different properties viewed in May 4th?
SELECT 
    COUNT(DISTINCT propertyNo) AS myCount
FROM
    Viewing
WHERE
    viewDate BETWEEN '2004-05-1' AND '2004-05-31';

-- Eg14. Use of Count and Sum
-- find the member of managers and their sum of salaries
SELECT 
    COUNT(staffNo) AS Manager, SUM(salary) AS salary
FROM
    Staff
WHERE
    position = 'Manager';

-- Eg15. Use of Min,Max,Avg
-- find minimum, maxmum, average salary
SELECT 
    MIN(salary) AS minSalary,
    MAX(salary) AS maxSalary,
    AVG(salary) AS avgSalary
FROM
    Staff;
    
-- Eg16. Use of group by
-- find total number of staff in each brand and their total salaries

SELECT 
    branchNo,
    COUNT(staffNo) AS Members,
    SUM(salary) AS totalSalary
FROM
    Staff
GROUP BY branchNo
ORDER BY branchNo;

-- Eg17. Use of Having
-- for each brand with more than 1 member of staff,find total number of staff in each brand and their total salaries
SELECT 
    branchNo,
    COUNT(staffNo) AS Members,
    SUM(salary) AS totalSalary
FROM
    Staff
GROUP BY branchNo
HAVING COUNT(staffNo) >= 2
ORDER BY branchNo DESC;

-- Eg18. Simple join
-- list names of all clients who have viewed a property along with any comment supplied
SELECT 
    c.clientNo, fName, IName, propertyNo, comment
FROM
    Client c,
    Viewing v
WHERE
    c.clientNo = v.clientNo;
    
    -- Eg19. Sorting a Join
SELECT 
    s.branchNo, s.staffNo, fName, lname, propertyNo
FROM
    Staff s,
    PropertyForRent p
WHERE
    s.staffNo = p.staffNo
ORDER BY s.branchNo , s.staffNo , propertyNo;

-- Eg20. Three tables join
-- for each branch,list staff who manage properties, including city in which branch is located and properties they manage
SELECT 
    b.branchNo, b.city, s.staffNo, fName, lName, propertyNo
FROM
    Staff s,
    PropertyForRent p,
    Branch b
WHERE
    s.staffNo = p.staffNo
        AND p.branchNo = b.branchNo
ORDER BY b.branchNo , s.staffNo , propertyNo;
-- method 2 using natural join
SELECT 
    p.branchNo, b.city, s.staffNo, fName, lName, propertyNo
FROM
    PropertyForRent p
        NATURAL JOIN
    Staff s,
    Branch b
WHERE
    s.branchNo = b.branchNo
ORDER BY branchNo , staffNo , propertyNo;


-- Eg21. Multiple Grouping Columns
-- find number of properties handled by each staff memeber
SELECT 
    s.branchNo, s.staffNo, COUNT(*) AS myCount
FROM
    Staff s,
    PropertyForRent p
WHERE
    s.staffNo = p.staffNo
GROUP BY s.branchNo , s.staffNo
ORDER BY s.branchNo , s.staffNo;
-- method 2
SELECT 
    branchNo, staffNo, COUNT(*) AS myCount
FROM
    PropertyForRent
        NATURAL JOIN
    Staff
GROUP BY staffNo
ORDER BY branchNo;



-- Eg22. Subquery with Equality
-- list staff who work in branch at '163 Main St'
SELECT 
    staffNo, fName, lName, position
FROM
    Staff
WHERE
    branchNo = (SELECT 
            branchNO
        FROM
            Branch
        WHERE
            street = '163 Main St');
-- method 2 
SELECT 
    staffNo, fName, lName, position
FROM
    Staff
        NATURAL JOIN
    Branch
WHERE
    street = '163 Main St';




-- Eg23. Subquery with Aggregate
-- list all staff whose salary is greater than the average and show by how much
SELECT 
    staffNo,
    fName,
    lName,
    position,
    salary - (SELECT 
            AVG(salary)
        FROM
            Staff) AS SalDiff
FROM
    Staff
WHERE
    salary > (SELECT 
            AVG(salary)
        FROM
            Staff)
ORDER BY SalDiff;

-- Eg24. Nested subquery: use of insert
-- list properties handled by staff at '163 Main St'
SELECT 
    propertyNo, street, city, postcode, type, rooms, rent
FROM
    PropertyForRent
WHERE
    staffNo IN (SELECT 
            staffNo
        FROM
            Staff
        WHERE
            branchNo = (SELECT 
                    branchNo
                FROM
                    Branch
                WHERE
                    street = '163 Main St'));
                    
-- method 2: 可以使用join using（）的语句，但是要注意隐藏问题如 某些属性是否能为空
SELECT 
    propertyNo, p.street, p.city, p.postcode, type, rooms, rent
FROM
    PropertyForRent p
        JOIN
    Branch USING (branchNo)
WHERE
    Branch.street = '163 Main St'
        AND p.staffNo IS NOT NULL;




-- Eg25. Use of Any/Some
-- find staff whose salary is larger than salary of at least one member of staff at branch B003
SELECT 
    staffNo, fName, lName, position, salary
FROM
    Staff
WHERE
    salary > SOME (SELECT 
            salary
        FROM
            Staff
        WHERE
            branchNo = 'B003');


-- Eg26. use of All
-- find staff whose salary is larger than salary of every member of staff at branch B003
SELECT 
    staffNo, fName, lName, position, salary
FROM
    Staff
WHERE
    salary > ALL (SELECT 
            salary
        FROM
            Staff
        WHERE
            branchNo = 'B003');
            
-- Eg27. Query using Exists
-- find all staff working in a London Branch 
SELECT 
    staffNo, fName, lName, position
FROM
    Staff s
WHERE
    EXISTS( SELECT 
            *
        FROM
            Branch b
        WHERE
            s.branchNo = b.branchNo
                AND city = 'London');

-- method 2
SELECT 
    staffNo, fName, lName, position
FROM
    Staff s,
    Branch b
WHERE
    s.branchNo = b.branchNo
        AND city = 'London';
        
-- method 3
SELECT 
    staffNo, fName, lName, position
FROM
    Staff
        NATURAL JOIN
    Branch
WHERE
    city = 'London';