SQL DATABASE ASSIGNMENT
1. Table Structure (Schema)
To establish a rigorous relational baseline, two normalized tables are defined below: the master
'employees' table containing core corporate data, and a junction table 'employee_projects'
tracking project assignments to support many-to-many relationship structures.
Table-1 : employees
Column Name Data Type D Description
emp_id INT (Primary Key) Unique identifier assigned to
each employee.
emp_name VARCHAR(50) Full legal name of the
employee.
department VARCHAR(50) Assigned corporate business
unit / department.
salary DECIMAL(10,2) Annual monetary
compensation details.
Table -2 : employee_projects
Column Name Data Type Description
emp_id INT (Foreign Key) References
employees(emp_id) for
project tracking.
project_id INT Unique identifier for corporate
initiatives.
2. Sample Data Dataset
The following tables demonstrate the baseline transactional records populated inside the database
prior to executing operational update queries. Note that John Doe contains duplicated structural
representations under IDs 1 and 3 to test deduplication queries.
Dataset: employees
emp_id emp_name department salary
1 Shrimathi R IT 45000.00
2 Sowmiya K HR 35000.00
3 Safikul IT 45000.00
4 Raja prabha IT 85000.00
5 Vasuki Finance 60000.00
Dataset: employee_projects
emp_id project_id
1 101
1 102
2 101
4 103
4 104
4 105
Query I: Find Duplicate Records
Identifies records that share structural identity (matching strings and numeric figures) but exist
under unique database keys.
SQL Query Script:
SELECT emp_name, department, salary, COUNT(*)
FROM employees
GROUP BY emp_name, department, salary
HAVING COUNT(*) > 1;
Output Matrix Result:
emp_name department salary COUNT(*)
Shrimathi R IT 45000.00 2
Explanation:
The GROUP BY statement consolidates equivalent value configurations across specific matrix
paths. The HAVING filtering operation ensures only groupings that violate single-row counts
(>1) are outputted to the matrix interface.
Query II: Find Employees Working in Multiple Projects
Filters structural relational components across cross-referenced matrices to extract entities linked
with a multiplicity factor greater than one.
SQL Query Script:
SELECT emp_id, COUNT(project_id) AS project_count
FROM employee_projects
GROUP BY emp_id
HAVING COUNT(project_id) > 1;
Output Matrix Result:
emp_id project_count
1 2
4 3
Explanation:
Grouping transactional arrays via employee reference values enables targeted aggregate tracking
metrics. Individuals mapped against multiple independent projects are subsequently highlighted.
Query III: Find Employees with Salary Between 40k and 80k
Extracts rows matching precise statistical threshold bounds by leveraging numerical standard
evaluation syntax ranges.
SQL Query Script:
SELECT emp_id, emp_name, salary
FROM employees
WHERE salary BETWEEN 40000 AND 80000;
Output Matrix Result:
emp_id emp_name salary
1 Shrimathi R 45000.00
3 Safikul 45000.00
5 Vasuki 60000.00
Explanation:
The SQL BETWEEN command applies an inclusive logical range test, retrieving rows satisfying
specified mathematical constraints seamlessly.
Query IV: Update Salary by 10% for a Department
Performs an in-place mutation to increase compensation fields dynamically based on exact
criteria filters.
SQL Query Script:
UPDATE employees
SET salary = salary * 1.10
WHERE department = 'IT';
//Direct verification selection:
SELECT emp_id, emp_name, department, salary FROM employees WHERE department
= 'IT';
Output Matrix Result:
emp_id emp_name department salary
1 Shrimathi R IT 49500.00
3 Safikul IT 49500.00
4 Raja Prabha IT 93500.00
Explanation:
The UPDATE command modifies values instantly. The integrated WHERE statement ensures
safety policies by target-filtering alterations exclusively onto matching department metrics.
Query V: Create a View for High Salary Employees
Constructs an architectural dynamic data virtualization mask filtering records above specific
high-value boundaries.
SQL Query Script:
CREATE VIEW high_salary_employees AS
SELECT emp_id, emp_name, department, salary
FROM employees
WHERE salary > 50000;
//Executing selection check:
SELECT * FROM high_salary_employees;
Output Matrix Result:
emp_id emp_name department salary
4 Raja Prabha IT 85000.00
5 Vasuki Finance 60000.00
Explanation:
A database view establishes a virtual abstract blueprint layer over base storage assets. The
baseline values mirror original parameters prior to running any operational transactional
adjustments.