SQL Course Section: Employee–Worksfor–Company (Joins, Filters, and Ranking)

You are given three relations: Employee (ename, street, city), Worksfor (ename, company_name, salary), and Company (Company_name, city).

The tasks below require SQL JOIN operations across foreign-key-like relationships (worksfor.ename ↔ employee.ename and worksfor.company_name ↔ company.Company_name), plus selection predicates and an order-based ranking for the second-highest salary.

We will assume a common SQL dialect (e.g., MySQL/PostgreSQL). If your DBMS differs (e.g., SQL Server), small syntactic changes may be needed, but the core logic remains the same.

2

Schema design goals (keys + data types)

A typical design uses:

• PRIMARY KEY on ename in Employee
• PRIMARY KEY on Company_name in Company
• A composite key in Worksfor (commonly (ename, company_name)) so each employee-company assignment is unique

The SQL CREATE TABLE statement defines column names and data types, and constraints like primary keys and foreign keys can be added during table creation. 2

(i) SQL: CREATE TABLE statements + suitable data types and sizes

Below is one consistent MySQL/PostgreSQL-friendly version. Column sizes are chosen to be practical for typical names/addresses.

Employee

1CREATE TABLE Employee (
2  ename  VARCHAR(50) PRIMARY KEY,
3  street VARCHAR(120) NOT NULL,
4  city   VARCHAR(80)  NOT NULL
5);

Company

1CREATE TABLE Company (
2  Company_name VARCHAR(80) PRIMARY KEY,
3  city          VARCHAR(80) NOT NULL
4);

Worksfor

1CREATE TABLE Worksfor (
2  ename         VARCHAR(50) NOT NULL,
3  company_name VARCHAR(80) NOT NULL,
4  salary        DECIMAL(12,2) NOT NULL,
5  PRIMARY KEY (ename, company_name),
6  FOREIGN KEY (ename) REFERENCES Employee(ename),
7  FOREIGN KEY (company_name) REFERENCES Company(Company_name)
8);

Notes:

• VARCHAR(n) and INT/DECIMAL are common SQL types; DECIMAL(p,s) is used for money-like values to control precision/scale.
• CREATE TABLE syntax is column-name followed by data type, and constraints can be declared in the table definition. 2

2

Sample INSERT data (so the queries have meaningful output)

1INSERT INTO Employee (ename, street, city) VALUES
2('Amit',    '221B Baker Street', 'Mumbai'),
3('Anand',   '12 MG Road',        'Bengaluru'),
4('Sarah',   '31 Park Avenue',    'Chennai'),
5('Ravi',    '77 Lake View',      'Mumbai'),
6('Karthik', '9 Residency Rd',   'Delhi'),
7('Maya',    '5 Green Street',   'Bengaluru'),
8('Vikram',  '10 Ashoka St',     'Delhi');
9
10INSERT INTO Company (Company_name, city) VALUES
11('tcs',  'Mumbai'),
12('infosys', 'Bengaluru'),
13('wipro', 'Delhi'),
14('accenture', 'Chennai');
15
16INSERT INTO Worksfor (ename, company_name, salary) VALUES
17('Amit',    'tcs',        60000.00),
18('Amit',    'infosys',    30000.00), -- to test "second highest salary overall"
19('Anand',   'infosys',    55000.00),
20('Sarah',   'accenture',  52000.00),
21('Ravi',    'tcs',        48000.00),
22('Karthik', 'wipro',      70000.00),
23('Maya',    'infosys',    65000.00),
24('Vikram',  'wipro',      40000.00);

[ii] Employees who live in the same city where they work

Use an INNER JOIN between Employee and Worksfor, then join Company to compare Employee.city with Company.city.

1SELECT DISTINCT e.ename
2FROM Employee e
3JOIN Worksfor w
4  ON e.ename = w.ename
5JOIN Company c
6  ON w.company_name = c.Company_name
7WHERE e.city = c.city;

This matches the relational-algebra idea of selecting employees whose residence city equals the company’s city via join and selection.

[iii] Employees who have salary more than Rs. 50000

Simple selection on Worksfor.salary plus projection of employee name.

1SELECT DISTINCT e.ename
2FROM Employee e
3JOIN Worksfor w
4  ON e.ename = w.ename
5WHERE w.salary > 50000;

Salary filtering uses SQL WHERE predicates, and joins connect employees to their salary records.

[iv] Employees who don't work in "tcs" company

Use NOT EXISTS to avoid null pitfalls and correctly handle the absence of a matching tcs row.

1SELECT e.ename
2FROM Employee e
3WHERE NOT EXISTS (
4  SELECT 1
5  FROM Worksfor w
6  WHERE w.ename = e.ename
7    AND w.company_name = 'tcs'
8);

An alternative is LEFT JOIN ... WHERE w.company_name IS NULL, but NOT EXISTS is typically clearer and robust.

[v] All employees whose name has second letter 'A'

In SQL, string indexing differs by DBMS:

• MySQL: SUBSTRING(ename, 2, 1)
• PostgreSQL: SUBSTRING(ename FROM 2 FOR 1) or SUBSTRING(ename,2,1)

Here’s a broadly usable form (MySQL/PostgreSQL style):

1SELECT DISTINCT ename
2FROM Employee
3WHERE SUBSTRING(ename, 2, 1) = 'A';

This is a string-position filter using WHERE and substring extraction.

[vi] Employee’s name having second highest salary

This is commonly solved using ranking. With ties handled correctly, use DENSE_RANK.

1WITH ranked AS (
2  SELECT
3    w.ename,
4    w.salary,
5    DENSE_RANK() OVER (ORDER BY w.salary DESC) AS rnk
6  FROM Worksfor w
7)
8SELECT e.ename
9FROM ranked r
10JOIN Employee e ON e.ename = r.ename
11WHERE r.rnk = 2;

Why DENSE_RANK:

• If multiple employees share the same second-highest salary, they all return as rank 2 (ties handled deterministically).