Skip to content
doubtly

Write SQL queries for given Database , Find name of employee having first letter ‘S’ in their names

Write SQL queries for given Database 1.

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

1. Find name of employee having first letter ‘S’ in their names
2. Display Annual salary of employee
3. Find name,street,city of employee who work for “Accenture” and
Earn more than 30000
4. Give total no of employees 

MrRam Asked Updated Student doubt sql queries Save WhatsApp Report

1 answer

Best answer

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
Doubtly · 23 Apr 2023 Report
Was this answer helpful?

Your answer

Log in or create a free account to answer this question.