The solution of the queries in SQL Microsoft format.
computer science
Description
The solution of the queries in SQL Microsoft format.
///*q3-List the names of all employee in a
specific department with the count of their total
warnings that they have ----not work *//
SELECT employee.emp_fname , employee.emp_lname,department.dep_id
FROM employee,department
where
(SELECT COUNT(war_id) FROM warnings where employee.emp_id=warnings.emp_id ) and dep_id =114
;
///*q4 List the names of
all employees who had warnings more than the average warnings at
all departments not work *//
SELECT
(( SELECT COUNT( emp_id )
FROM attendance,employee
WHERE employee.emp_id =
attendance.emp-id
AND status-id=1
) / ( COUNT(emp_id ) * 1.0 )
) *100 AS Rate
FROM employee
WHERE department.dep_id =114;
///*6 List all unfinished (under process) work
assignments for all employee within a specific
department along with their names, the
assigned date, and assignment duration.
not work*///
select employee.emp_fname,
work_Assignment.emp_id, work_Assignment.assi_date, work_Assignment.assi_dur,
work_Assignment.assi_desc ,work_Assignment.work_status,
department.dep_name
from employee, work_Assignment, department
where department.dep_name ='HR' , work_Assignment.work_status= 'under process';
///* 7 Give the vacation request status for a
specific vacation request id. not ok*/////
Select vacation.req_id,
[
vacation_status.req_stat_desc]
FROM vacation join vacation_status
using([req_stat_id])
WHERE [req_id] =9;
///* 8 List all employee names, emails, phone
numbers, manager name, and department that are
late on their vacations within a
specific duration ---- not ok*///
SELECT [employee.emp_fname] ,[employee.emp_lname] ,
[employe.emp_dob]
,
[employee.emp_email] , [employee.emp_mobile] ,
[department.dep_name]
,
e.emp_fname as mangerFname ,
e.[emp_lname] as mangerLname
FROM [employee] , [department] , [employee] e , [vacation]
WHERE [employee.dep_id] = [department.dep_num]
AND e.[emp_id] = [department.dep_mgr_id]
and [vacation.emp_id]= [employee.emp_id]
and [vacation.vac_return_date] >
[vacation.vac_end_date]
///* 9
Give the average attendance rate for a department's employees.*///
SELECT
((
SELECT COUNT( emp_id )
FROM attendance
AND [employee.emp_id] =
[attendance.emp_id]
AND [status_id]=1
) / ( COUNT( ; ) * 1.0 )
) *100 AS Rate
FROM [employee]
WHERE [dep_num]
