Please anyone can help me I need SQL query 1) Display all emloyee names and hire
ID: 3740923 • Letter: P
Question
Please anyone can help me I need SQL query
1) Display all emloyee names and hire dates. Display hire dates with this format ‘Thursday , 28 NOVEMBER 1996’.
2) Find employee who were hired after year specified by the user (use subtititution variable) and use 1997 as your input. Display names and hire dates.
3) Display all employee names, department names, qalification descriptions, position descritions order by department name.
4) Delete department number 30 from the department table. If it is not successful, write down your suggestion to make it work.
* PLEASE NOTE i'm using SQL Oracle 11g and a lot gived me Error Codes
*Please
"SQL Table"
rop table emplevel cascade constraints drop table position cascade constraints; drop table dept cascade constraints; drop table employee cascade constraints; drop table qualification cascade constraints; drop table dependent cascade constraints; CREATE TABLE emplevel CHAR (1), (SalaryLevel LowSalary HighSalary CONSTRAINT emplevel_levelno_pk PRIMARY KEY (SalaryLevel)); NUMBER (6), NUMBER (6), CREATE TABLE position (PositionIdCHAR (1), PosDesc VARCHAR2 (10), CONSTRAINT position-positionid-pk PRIMARY KEY (Position|d)); CREATE TABLE qualification (QualId CHAR (1), QualDesoc CONSTRAINT qualification_qualid_pk PRIMARY KEY (QualId)); VARCHAR2 (20), CREATE TABLE dept (DeptId NUMBER (2), DeptName Location EmployeeId CONSTRAINT dept_deptid_pk PRIMARY KEY (DeptId)); VARCHAR2 (12) NOT NULL, VARCHAR2 (20), NUMBER (3), CREATE TABLE employee (EmployeeId NUMBER (3), Lname Fname PositionId Supervisor HireDate Salary Comm1ssion DeptId QualId CONSTRAINT employee employeeid pk VARCHAR2 (15) CONSTRAINT employee_lname_nn NOT NULL, VARCHAR2 (15) CONSTRAINT employee_fname_nn NOT NULL, CHAR (1), NUMBER (3), DATE NUMBER (6), NUMBER (5), NUMBER (2) NOT NULL, CHAR (1), CONSTRAINT employee-positionid_fk FOREIGN KEY (Position!d) CONSTRAINT employee_deptid_fk FOREIGN KEY (DeptId) CONSTRAINT employee_qualid_fk FOREIGN KEY (QualId) PRIMARY KEY (EmployeeId), REFERENCES position (PositionId), REFERENCES dept (DeptId), REFERENCES qualification (QualId));Explanation / Answer
1) For this Oracle builtIn function TO_CHAR is used to to change the date in given specified format.
SELECT EmployeeId, Lname, Fname, TO_CHAR(HireDate, 'DAY, DD MONTH YYYY') 'HireDate'
FROM employee;
2) For this query as well TO_CHAR function is used in WHERE clause to change the date in 'YYYY' format and then matching it with specified year i.e '1997'.
SELECT EmployeeId, Lname, Fname, HireDate
FROM employee
WHERE TO_CHAR(TO_DATE(HireDate, 'YYYY')) > '1997' ;
3) For this 4 tables are joined namely employee, dept, qualification, position. Order by clause is used to sort the result based on department name.
SELECT e.EmployeeId, e.Lname, e.Fname, d.DeptName, q.QualDesc, p.PosDesc
FROM employee e
INNER JOIN dept d
ON e.DeptId = d.DeptId
INNER qualification q
ON q.QualId = e.QualId
INNER JOIN position p
ON e.PositionId = p.PositionId
ORDER BY d.DeptName;
4)
On deleting the department number 30 with the following query
DELETE FROM dept
WHERE DeptId = 30;
This command will generate an error displaying DeptId is used as reference in employee table. Deleting department also need to delete all the referencing department id in employee table.
This issue can be resolved by updating the ON DELETE CASCADE deptId in employee table.
The alter command used to add foreign key constraint for employee table would be:
ALTER TABLE employee
ADD CONSTRAINT fk_dept
FOREIGN KEY (DeptId)
REFERENCES dept (DeptId)
ON DELETE CASCADE;