[go: up one dir, main page]
Scribd
Upload
209 views2 pages

SQL Queries for Employee Data Analysis

The document outlines SQL, Relational Algebra, Tuple Relational Calculus (TRC), and Domain Relational Calculus (DRC) queries for various employee-related scenarios. It includes queries to find employees working for First Bank Corporation, those earning over 10,000, employees living in the same city as their companies, and those not working for First Bank Corporation. Each scenario is presented with corresponding SQL, Relational Algebra, TRC, and DRC representations.

Uploaded by

dahiyaneha
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
209 views2 pages

SQL Queries for Employee Data Analysis

The document outlines SQL, Relational Algebra, Tuple Relational Calculus (TRC), and Domain Relational Calculus (DRC) queries for various employee-related scenarios. It includes queries to find employees working for First Bank Corporation, those earning over 10,000, employees living in the same city as their companies, and those not working for First Bank Corporation. Each scenario is presented with corresponding SQL, Relational Algebra, TRC, and DRC representations.

Uploaded by

dahiyaneha
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

SQL, Relational Algebra, TRC, and DRC Queries

Schemas:

employee(employee-name, street, city)


works(employee-name, company-name, salary)
company(company-name, city)
manages(employee-name, manager-name)

(a) Find the names of all employees who work for First Bank Corporation.

SQL:
SELECT DISTINCT w.employee_name
FROM works AS w
WHERE w.company_name = 'First Bank Corporation';

Relational Algebra:
πemployee-name(σcompany-name='First Bank Corporation'(works))

Tuple Relational Calculus (TRC):


{ [Link]-name | works(w) ∧ [Link]-name='First Bank Corporation' }

Domain Relational Calculus (DRC):


{ <e> | ∃ c,s (works(e,c,s) ∧ c='First Bank Corporation') }

(b) Find the names, street addresses, and cities of residence of all employees who work
for First Bank Corporation and earn more than 10,000.

SQL:
SELECT DISTINCT e.employee_name, [Link], [Link]
FROM employee AS e
JOIN works AS w ON w.employee_name = e.employee_name
WHERE w.company_name = 'First Bank Corporation' AND [Link] > 10000;

Relational Algebra:
πemployee-name, street, city( employee σσ company-name='First Bank Corporation' ∧ salary>10000(works))

Tuple Relational Calculus (TRC):


{ <[Link]-name, [Link], [Link]> | employee(e) ∧ ∃ w (works(w) ∧ [Link]-
name=[Link]-name ∧ [Link]-name='First Bank Corporation' ∧ [Link]>10000) }

Domain Relational Calculus (DRC):


{ <n, st, ct> | employee(n,st,ct) ∧ ∃ c,s (works(n,c,s) ∧ c='First Bank Corporation' ∧ s>10000) }
(c) Find all employees who live in the same cities as the companies for which they work.

SQL:
SELECT DISTINCT e.employee_name
FROM employee AS e
JOIN works AS w ON w.employee_name = e.employee_name
JOIN company AS c ON c.company_name = w.company_name
WHERE [Link] = [Link];

Relational Algebra:
πemployee-name(σ[Link] = [Link]( employee ⨝ works ⨝ company))

Tuple Relational Calculus (TRC):


{ [Link]-name | employee(e) ∧ ∃ w,c (works(w) ∧ company(c) ∧ [Link]-
name=[Link]-name ∧ [Link]-name=[Link]-name ∧ [Link]=[Link]) }

Domain Relational Calculus (DRC):


{ <n> | ∃ st,ec, cn, cc, s (employee(n,st,ec) ∧ works(n,cn,s) ∧ company(cn,cc) ∧ ec=cc) }

(d) Find all employees who do not work for First Bank Corporation.

SQL:
SELECT e.employee_name
FROM employee AS e
WHERE NOT EXISTS ( SELECT 1 FROM works AS w WHERE w.employee_name = e.employee_name
AND w.company_name = 'First Bank Corporation');

Relational Algebra:
πemployee-name(employee) - πemployee-name(σcompany-name='First Bank Corporation'(works))

Tuple Relational Calculus (TRC):


{ [Link]-name | employee(e) ∧ ¬∃ w (works(w) ∧ [Link]-name=[Link]-name ∧
[Link]-name='First Bank Corporation') }

Domain Relational Calculus (DRC):


{ <n> | ∃ st,ct (employee(n,st,ct) ∧ ¬∃ s (works(n,'First Bank Corporation',s))) }

You might also like