The solution of the queries in SQL Microsoft format.

computer science


 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 *//


FROM attendance,employee

WHERE employee.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,



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


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] >


///*  9 Give the average attendance rate for a department's employees.*///


SELECT COUNT( emp_id )

FROM attendance


AND [employee.emp_id] =


AND [status_id]=1

) / ( COUNT( ; ) * 1.0 )

) *100 AS Rate

FROM [employee]

WHERE [dep_num]


Related Questions in computer science category

The ready solutions purchased from Library are already used solutions. Please do not submit them directly as it may lead to plagiarism. Once paid, the solution file download link will be sent to your provided email. Please either use them for learning purpose or re-write them in your own language. In case if you haven't get the email, do let us know via chat support.