SQL OR DBMS SQL QUERIES 2

Table Structure

Emp

(Empid, Ename, CompID, salary, Joindate, Gender, City)

Company

(CompID, CompName, City)

 i) Give name of companies located in those cities where TATA is located.

 Select companyName from company

where city in (select city from company where companyName = ‘TATA’)

 

 ii) Give the name of the employees living in the same city where their company is located.

Select ename from Emp e, company c

 where e.city=c.city

 

iii) Update salary of employee ‘Raj’ by giving him the salary of ‘Radha’ working in same company.

update Emp set salary = (select salary from Emp where Ename = ‘Radha’)

where Ename = ‘Raj’

and compid = (Select compid from emp where ename=’Radha’)

iv) Display how many male and female members have joined in January 2004.

 

Select count(ename) from emp

group by gender having to_char(joindate,’mon-yyyy’)=’jan-2004’

v) Display the total number of companies located in each city.

Select count(compid) from company group by city