Assessment 3
Tech 1400
Database Design and Management
SQL Demonstration and analysis
Student ID:
Student Name:
Table of Contents
List of Figures
Figure 1: First Name of All Employee 3
Figure 2: Employee with the shortest tenure 4
Figure 4: Count of Employee department 7
Figure 5: Average salary by department 9
Figure 6: Employee older than 40 years 10
Figure 7: Employee with lecture tile 12
Question 1
Q
SELECT f.EmployeeFirstName FROM
Employees f JOIN
Departments p ON f.DepartmentID = p.DepartmentID WHERE
p.Location = 'Sydney' ORDER
BY f.EmployeeFirstName ASC; 
Figure 1: First Name of All Employee
Explanation:
This query helps to generate the first names of the employees, while they are working in the Sydney department. The relations of Employees, departments, and Departments are mapped with common fields to establish the relationship between employee particulars and his/her workplace. WHERE clause allows to get only the employees from Sydney and the result is sorted by name using ORDER BY.
Question 2
SELECT EmployeeFirstName, EmployeeLastName, yearHired FROM
Employees ORDER
BY yearHired DESC LIMIT
1; 
Figure 2: Employee with the shortest tenure
Explanation:
This query displays the tenure ship of all the employees by using teaching no., year hired: year hired. It orders the result by tenure in ascending manner to obtain those employees who severed the company for the least amount of time. In the case of using the keywords LIMIT 1, only the employee who has worked for the organization for the shortest duration possible will be displayed.
Question 3
SELECT EmployeeFirstName, EmployeeLastName FROM
Employees WHERE
CONCAT(YEAR(CURDATE()) - birthyear) =
REVERSE(CONCAT(YEAR(CURDATE())
- birthyear)); 
Explanation:
This query defines the age of each employee, it gets the current year using the yield current date function and minus it from the birth year that stores the birth year of each employee. Age, and then it uses the REVERSE formula in Excel to check if age is palindrome compared to the result of getting age reversed, the database designed for the age predictor is used to get the age. Employees of the given organization are searched for using keywords, among them the criteria are found for selecting employees of the place of work, the ages of whom are palindromes.
Question 4
List the departments along with the number of employees in each department.
SELECT d.DepartmentName, COUNT(e.EmployeeID) AS EmployeeCount FROM
Departments d LEFT
JOIN Employees e ON d.DepartmentID = e.DepartmentID GROUP
BY d.DepartmentName; 
Figure 4: Count of Employee department
Explanation
The result of this query is a list that gives out each specified department and the employees of the particular department. To establish the connection of an association of employees with the departments, the JOIN operation is hereby used to join the department table with the department table. The COUNT function is used for finding how many numbers of employees working in an organization and the result is obtained by using the GROUP BY of the department name.?
Question 5:
SELECT d.DepartmentName, AVG(l.Salary) AS AverageSalary FROM
Departments d JOIN
Employees e ON d.DepartmentID = e.DepartmentID JOIN
SalaryClass l ON e.SalaryID = l.SalaryID GROUP
BY d.DepartmentName; 
Figure 5: Average salary by department
Explanation
This query calculates the average or mean salary of employees of each department. Several joins are used to connect between Department, DepartmentEmployee, Employees, and SalaryClass tables about employees’ salaries. The average salary was computed by using the AVG function and then the result was split by the department by using the GROUP BY clause.
Question 6
SELECT EmployeeFirstName, EmployeeLastName, l.Salary FROM
Employees f JOIN
SalaryClass l ON f.SalaryID = l.SalaryID WHERE
(YEAR(CURDATE()) - f.birthyear) > 40; 
Figure 6: Employee older than 40 years
Explanation
This query produces the name and the salary of employees who are above forty years of age. This means that first, the function is to calculate each employee’s age, using a subtraction of the birth year and the current year while the Where clause will then remove all employees who are below 40 years. The last JOIN is used to join the Employees and SalaryClass tables to get the salary information.
Question 7
SELECT f.EmployeeFirstName, f.EmployeeLastName, m.DepartmentName,
f.Title FROM
Employees f JOIN
Departments m ON f.DepartmentID = m.DepartmentID WHERE
f.Title = 'Lecturer'; 
Figure 7: Employee with lecture tile
Explanation
This query only returns the First Name and Last name of an Employee, and the department name in case an Employee belongs to a “Lecturer” type. This query links together the Employees table with DepartmentEmployee and Departments to present employee details with their departments: But in the WHERE clause, it only focuses on the employee title that is Lecturer only.
Question 8:
Q
SELECT f.EmployeeFirstName, f.EmployeeLastName, l.Salary AS
OriginalSalary,
(l.Salary * 1.10) AS NewSalary FROM
Employees f JOIN
SalaryClass l ON f.SalaryID = l.SalaryID; 
Figure 8: Original salary and after bonus new salary
Explanation
This query displays the previous and new salary of each employee with a 10 % bonus introduced in the later. That is obtained with the JOIN between Employees and SalaryClass for the salary data to be available and the calculation of the bonus-adjusted is done by multiplying Salary by 1.10.
Question 9:
ALTER TABLE Employees ADD EmployeeInitials VARCHAR(5); 
Explanation
The above ALTER command creates a new column with the name EmployeeInitials placed in the Employees’ table. The data type VARCHAR (5) is used for the initials.
Question 10
Q
UPDATE Employees SET
EmployeeInitials = CONCAT(LEFT(EmployeeFirstName, 1),
LEFT(EmployeeLastName, 1)); 
Figure 10: Populate EmployeeInitials
Explanation
This UPDATE query populates the EmployeeInitials by cropping the first name of the first name of the employee and the last name of the employee. A period (‘.’) was used in between these letters by converting both into strings and using the CONCATENATION method to join them together.
Question 11
Validity of data in a database hence the accuracy and the consistency of data in the database. In this case, what appears to have been accomplished by this database design is that it has accomplished relational integrity through the use of the keys such as the DepartmentID in the Employees is the same as the DepartmentID in the Departments. However, there are potential issues to consider:
Normalization: The tables seem to be in at least the third normal form (3NF) where essentially the tables do not possess a couple of attributes that depend on more than one other attribute. The decision to have a separate SalaryClass table instead of embedding it in the Employees table is a good example (S 2021).
Referential Integrity: There are sharp formal restraints, which remain mutually obeyed, and Coupling between the tables is very well set for example through an Employee’s ID number with the Department’s ID number (kumawat 2024). These foreign key constraints ensure that every employee is rightly placed in a department and every department must be referenced correctly.
Data Integrity: In bonuses, the employees may face certain issues of misunderstanding how the bonuses are paid and whether or not they have been rightly applied based on the need for accommodating all employees necessary for the firm, the implementation of such a pay structure may lead to definite problems concerning the right pay for every employee (Mucci & Stryker 2024). Besides this, any number that counts an age and any other value that can be changed at any time, for instance, from birth year, should not be taken by accident.
References
?kumawat, K 2024, What is Normalization in DBMS? , Medium, retrieved 8 October 2024, <https://medium.com/@kuldeepkumawat195/what-is-normalization-in-dbms-bdcb3503ceba>.
Mucci, T & Stryker, C 2024, What is data integrity?, Ibm.com, retrieved 8 October 2024, <https://www.ibm.com/topics/data-integrity>.
?S RA 2021, SQL Normalization Explained: Organize and Secure Your Data, Simplilearn.com, Simplilearn, retrieved 8 October 2024, <https://www.simplilearn.com/tutorials/sql-tutorial/what-is-normalization-in-sql>.
?
