Answer: Use LIKE 'S%' for names starting with S, salary*12 for annual salary, a three-table join with qualified columns for Accenture employees, and COUNT(*) for the total.
Schema: Employeee(eid, empname, street, city), Works(eid, cid, salary), Company(cid, companyname, city), Manager(eid, managername). (The question spells the column "comapanyname"; use whatever spelling your table has. It is written companyname below.)
1. Employees whose name starts with S
SELECT empname
FROM Employeee
WHERE empname LIKE 'S%';
% matches any number of characters, so 'S%' means "S followed by anything".
2. Annual salary of each employee (salary in Works is monthly)
SELECT e.empname, w.salary * 12 AS annual_salary
FROM Employeee e
JOIN Works w ON e.eid = w.eid;
3. Name, street and city of employees who work for Accenture and earn more than 30000
SELECT e.empname, e.street, e.city
FROM Employeee e
JOIN Works w ON e.eid = w.eid
JOIN Company c ON w.cid = c.cid
WHERE c.companyname = 'Accenture' AND w.salary > 30000;
Both Employeee and Company have a column named city, so it must be written e.city (the employee's city). Writing only city gives the error "ambiguous column name: city".
4. Total number of employees
SELECT COUNT(*) AS total_employees
FROM Employeee;
Tested on sample data (Employeee: Sneha Patil/Pune, Rahul Shah/Thane, Sameer Khan/Mumbai, Anita Rao/Pune; Works: 45000, 28000 and 52000 at Accenture, 60000 at TCS). Results:
Query 1: Sneha Patil, Sameer Khan
Query 2: Sneha Patil 540000, Rahul Shah 336000, Sameer Khan 624000, Anita Rao 720000
Query 3: Sneha Patil | MG Road | Pune
Sameer Khan | SV Road | Mumbai
Query 4: 4