please i need a SQL query if possible 1) List the department name with the least

Question

please i need a SQL query if possible

1) List the department name with the least number of employees.
2) List pair of employee names who are having the same qualification order by qualification id.
3) Display each employee’s name, department name, position description, and qualification
description. Which employee is missing? Why?
4)Display information for employee 128. Give a 10% raise to employee number 128. Again,
display the updated information for emploee 128.

------------------------------------------------------------------------------------------------------------------------------

"table structure"

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)

Select DeptName from dept inner join employee on dept.DeptId = employee.DeptId where count(employee.EmployeeId) = (Select min(count(EmployeeID)) from employee group by DeptId);
2)

Select e1.Fname,e1.Lname,e2.Fname,e2.Lname from employee e1 inner join employee e2 on e1.QualId = e2.QualId order by e1.QualId;
3)

Select Lname,Fname,DeptName,PosDesc,QualDesc from employee inner join dept on employee.DeptId = dept.DeptId inner join position on employee.PositionId = position.PositionId inner join qualification on employee.QualId = qualification.QualId ;

An Employee whose PositionId is null or QualId is null is missing from the output. This is because inner join will match QualId and PositionId . DeptId cannot be null.
4)

Select * from employee where EmployeeId = 128 ;

Update employee set Salary = Salary + Salary*0.1 where EmployeeId = 128;

Select * from employee where EmployeeId = 128 ;

Do ask if any doubt. Please upvote.

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.