10 BranchStaffProperty.txt

2. Update the street of branchNo 1001 to MG

CREATE DATABASE propertydealers;
USE propertydealers;


CREATE TABLE Branch(
    branchno VARCHAR(10) PRIMARY KEY,
    street VARCHAR(50),
    city VARCHAR(30),
    postcode VARCHAR(10)
);

CREATE TABLE Staff(
    staffno VARCHAR(10) PRIMARY KEY,
    fname VARCHAR(30),
    lname VARCHAR(30),
    position VARCHAR(20),
    sex CHAR(1),
    dob DATE,
    salary DECIMAL(10,2),
    branchno VARCHAR(10),
    FOREIGN KEY (branchno) REFERENCES Branch(branchno)
);

CREATE TABLE PropertyForRent(
    propertyNo VARCHAR(10) PRIMARY KEY,
    street VARCHAR(50),
    city VARCHAR(30),
    postcode VARCHAR(10),
    type VARCHAR(20),
    rooms INT,
    rent DECIMAL(10,2),
    staffno VARCHAR(10),
    branchno VARCHAR(10),
    FOREIGN KEY (staffno) REFERENCES Staff(staffno),
    FOREIGN KEY (branchno) REFERENCES Branch(branchno)
);

1-- Branch table
INSERT INTO Branch VALUES
(1001,'FC Road','Pune','411005'),
(1002,'JM Road','Pune','411006'),
(1003,'Andheri','Mumbai','400050');

-- Staff table
INSERT INTO Staff VALUES
('S101','Amit','Patil','Manager','M','1985-03-12',60000,1001),
('S102','Neha','Kulkarni','Clerk','F','1990-07-22',35000,1001),
('S103','Sagar','Sharma','Salesman','M','1995-05-11',30000,1002),
('S104','Anita','Rao','Saleswoman','F','1993-10-05',32000,1002),
('S105','Ravi','Shinde','Manager','M','1982-01-15',70000,1003);

-- PropertyForRent table
INSERT INTO PropertyForRent VALUES
('P1001','FC Road','Pune','411005','Flat',3,20000,'S101',1001),
('P1002','JM Road','Pune','411006','House',4,35000,'S102',1001),
('P2001','MG Road','Pune','411006','Flat',2,15000,'S103',1002),
('P2002','Camp','Pune','411006','Flat',3,18000,'S104',1002),
('P3001','Bandra','Mumbai','400050','House',5,60000,'S105',1003);

2. Update the street of branchNo 1001 to MG
UPDATE Branch
SET street = 'MG'
WHERE branchno = 1001;

3. Staff who have salary greater than average salary of all staff
SELECT fname, lname, salary
FROM Staff
WHERE salary > (SELECT AVG(salary) FROM Staff);

4. Type and rooms of properties in Pune
SELECT type, rooms
FROM PropertyForRent
WHERE city = 'Pune';

5. Staffs who work as salesman or saleswoman
SELECT fname, lname, position
FROM Staff
WHERE position IN ('Salesman','Saleswoman');

6. Number of properties which are of type Flat
SELECT COUNT(*) AS FlatCount
FROM PropertyForRent
WHERE type = 'Flat';

7. Staff members born before 2004
SELECT fname, lname, dob
FROM Staff
WHERE dob < '2004-01-01';

8. Total number of staff members in each branch
SELECT branchno, COUNT(*) AS StaffCount
FROM Staff
GROUP BY branchno;