PostGreSQL Given the following normalized Employee database with two tables (Emp
ID: 3920309 • Letter: P
Question
PostGreSQL
Given the following normalized Employee database with two tables (Employee and Employee Department), create database tables and load them appropriately, then write a User Defined Function (UDF) with a Subquery that displays Employee Name and Department Number. Call your UDF using the data in the database below. You will first need to construct and load the database tables.
Employee Table Emp Num Emp LNameDept Num 1 OO654 100375 100164 Davis Miller Smits A55 A55 H64 D35 Violation: Mulitple Cell Values Employee Table Employee Department Table Emp_ Num* Emp LName 00654 100375 100164 Emp_Num* Davis Miller Smits 100654 100375 00375 Dept Num A55 A55 H64 D35 100164 *Denotes Primary/Foreign KeyExplanation / Answer
Creating employees table:
CREATE TABLE employees
(
Emp_Num INT PRIMARY KEY NOT NULL,
Emp_Lname TEXT
);
===========================================
creating employee_department table:
CREATE TABLE employee_department
(
Emp_Num INT REFERENCES employees(Emp_Num),
Dep_Num CHAR(3)
);
===============================================
inserting data into employee table
INSERT INTO "employees"
("Emp_Num","Emp_Lname")
VALUES
(100654,'Davis'),
(100375,'Miller'),
(100164,'Smits');
========================================
insert into employee_department table
INSERT INTO "employee_department"
("Emp_Num","Dep_Num")
VALUES
(100654,'A55'),
(100375,'A55'),
(100375,'H64'),
(100164,'D35');
=====================================================
function to find and pring employee name and his department
CREATE OR REPLACE FUNCTION printdata(EmpNumber integer)
RETURNS integer AS $myflag$
declare
myflag integer;
empname char[50];
departmentname char[3];
BEGIN
SELECT Emp_Lname into empname,Dep_Num into departmentname
FROM employees JOIN employee_department
ON
employees.Emp_Num=employee_department.Emp_Num
WHERE
employees.Emp_Num=EmpNumber;
raise info 'Employee name: %',empname;
raise info 'department name': %',departmentname;
RETURN 1;
END;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.