lab3+lab4

--LAB3--

SELECT * FROM propertyforrent; --l. List full details of all properties.

SELECT * FROM propertyforrent WHERE TYPE='House'; --2. List full details of all properties that are only house type.

SELECT * FROM client;
SELECT clientno AS "client number",
        fname AS "first name",
        lname AS "last name",
        telno AS "telephone number"
    FROM client WHERE preftype='Flat'; --3. List the client number, first name, last name and telephone number of all clients whose preferred property type are Flat.

SELECT * FROM viewing;
SELECT clientno, viewdate FROM viewing WHERE propertyno = 'PG21' AND comments IS NOT NULL; --4. List the client number and view date of all viewings on property PG21 where the comments are provided.

SELECT DISTINCT TYPE FROM propertyforrent; --5. List all type of properties that are for rent.  The result should eliminate the duplicate records. //distinct=different

SELECT * FROM staff;
SELECT staffno AS "staff number",
        fname AS "first name",
        lname AS "last name",
        salary AS "salary"
        FROM staff WHERE sex='M'AND salary>10000 ; --6. List the staff number, first name, last name and salary of all staffs who are males with salary greater than 10,000.

SELECT * FROM staff;
SELECT staffno AS "staff number",
        fname AS "first name",
        lname AS "last name",
        dob AS "birth date"
        FROM staff WHERE dob>'1-JAN-1960' ORDER BY dob; --7. List the staff number, first name, last name and birth date of all staffs who were born after January 1, 1960. Arrange the result in ascending order by birth date.

SELECT * FROM privateowner;
SELECT ownerno AS "owner number",
        fname||lname AS "name",
        address AS "address"
        FROM privateowner WHERE address NOT LIKE '%Glasgow%'; --8. List the owner number, name (first name concatenated with last name), and address of all private owners who do not live in the Glasgow city.

SELECT * FROM branch;
SELECT street,city,postcode
        FROM branch WHERE city IN ('Aberdeen','Bristol'); --9. List the address of all branch offices in Aberdeen or Bristol.

        SELECT * FROM staff;
SELECT branchno AS "branch no",
        salary AS "salary"
        FROM staff ORDER BY branchno, salary DESC; --10. List the branch no and salary of all staffs. The result should be arranged by branch no, in ascending order, and within branch no, in descending order of salary.

--LAB4--
SELECT * FROM branch;
SELECT branchno AS "branch number"
        FROM branch
        WHERE  city IN ('Aberdeen')
        OR street LIKE '%Main Street%'; --1. Show the branch number of all branches that locate in Aberdeen city or on Main street.

SELECT * FROM branch WHERE city <> 'London'; --2. List the branches that are not in London // != or <> or not like

SELECT * FROM client WHERE preftype = 'Flat' AND maxrent <=400;
--3. Show clients, whose preferred property type are Flat, can rent the property maximum 400.

SELECT * FROM client;
SELECT * FROM client WHERE telno LIKE '%77'; --4. Show clients with phone number ending in 77.

SELECT * FROM propertyforrent;
SELECT propertyno AS "property number", rent AS "rent cost" FROM propertyforrent WHERE TYPE = 'Flat' AND city = 'Glasgow' AND rooms BETWEEN 3 AND 4; --in (3,4);
--5. Show property number and the rent cost of the flat properties in Glasgow city with at least 3 and up to 4 rooms.

SELECT propertyno FROM propertyforrent WHERE ownerno LIKE 'CO87' OR ownerno LIKE 'CO40';
--6. Display the property number of properties with owner number is CO87 or owner number is CO40.

SELECT * FROM propertyforrent WHERE staffno IS  NULL;
--7. Show properties that do not have staff number provide. --is null

SELECT * FROM staff WHERE POSITION = 'Manager' ORDER BY salary DESC;
--8. Show the staffs with the position Manager and sort the staffs’ information in descending order of salary.

SELECT * FROM staff;
SELECT staffno,POSITION,dob FROM staff WHERE dob LIKE ('%JUN%') ORDER BY dob DESC;
--9. Show staff number, position and day of birth of staffs who have birthdate in June. The result should be arranged by birthdate in descending order.