Write SQL queries for given Database , Modify database so ‘jack’ is now live in ‘mumbai’

Question

Write SQL queries for given Database 

 

2.

Employeee(eid,empname,street,city)
Works(eid,cid,salary)
Company(cid,comapanyname,city)
Manager(eid,managername)

1. Modify database so ‘jack’ is now live in ‘mumbai’
2. Give all employess of ANZ corporation a 10% raise in salary.
3. Find all employees who lives in same citites as they work for.
4. Find highest paid employee.
 

Answer 1

To modify the database so that 'jack' is now living in 'mumbai', you can use the following SQL query:

   

UPDATE Employeee SET city = 'mumbai' WHERE empname = 'jack'; 

To give all employees of ANZ corporation a 10% raise in salary, you can use the following SQL query:

  

 UPDATE Works SET salary = salary * 1.1 WHERE cid IN (SELECT cid FROM Company WHERE companyname = 'ANZ corporation');

To find all employees who live in the same cities as they work for, you can use the following SQL query:

  

 SELECT e.empname, e.city FROM Employeee e INNER JOIN Works w ON e.eid = w.eid INNER JOIN Company c ON w.cid = c.cid WHERE e.city = c.city; 

To find the highest paid employee, you can use the following SQL query:

   

SELECT e.empname, w.salary FROM Employeee e INNER JOIN Works w ON e.eid = w.eid WHERE w.salary = (SELECT MAX(salary) FROM Works);

 

 

Sample database : 

 CREATE TABLE Employeee (
    eid INT PRIMARY KEY,
    empname VARCHAR(50) NOT NULL,
    street VARCHAR(50) NOT NULL,
    city VARCHAR(50) NOT NULL
);

CREATE TABLE Company (
    cid INT PRIMARY KEY,
    companyname VARCHAR(50) NOT NULL,
    city VARCHAR(50) NOT NULL
);

CREATE TABLE Works (
    eid INT NOT NULL,
    cid INT NOT NULL,
    salary DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (eid, cid),
    FOREIGN KEY (eid) REFERENCES Employeee (eid),
    FOREIGN KEY (cid) REFERENCES Company (cid)
);

CREATE TABLE Manager (
    eid INT PRIMARY KEY,
    managername VARCHAR(50) NOT NULL,
    FOREIGN KEY (eid) REFERENCES Employeee (eid)
);

INSERT INTO Employeee VALUES (1, 'John Doe', '123 Main St', 'New York');
INSERT INTO Employeee VALUES (2, 'Jane Smith', '456 Elm St', 'San Francisco');
INSERT INTO Employeee VALUES (3, 'Bob Johnson', '789 Oak St', 'Chicago');
INSERT INTO Employeee VALUES (4, 'Jack Black', '999 Pine St', 'Sydney');

INSERT INTO Company VALUES (1, 'ACME Corp', 'New York');
INSERT INTO Company VALUES (2, 'XYZ Inc', 'San Francisco');
INSERT INTO Company VALUES (3, 'ANZ corporation', 'Sydney');
INSERT INTO Company VALUES (4, 'ABC Ltd', 'Chicago');

INSERT INTO Works VALUES (1, 1, 50000.00);
INSERT INTO Works VALUES (2, 2, 60000.00);
INSERT INTO Works VALUES (3, 3, 70000.00);
INSERT INTO Works VALUES (4, 3, 80000.00);

INSERT INTO Manager VALUES (1, 'Alice Adams');
INSERT INTO Manager VALUES (2, 'Bill Brown');
INSERT INTO Manager VALUES (3, 'Carol Carter');

Video ::