### Instruction: CREATE TABLE employeedetails (EmployeeId numeric,UserId int,UserName varchar,EmpCode varchar,title varchar,FirstName nvarchar,MiddleName nvarchar,FatherName varchar,Phone nvarchar,CellPhone nvarchar,
IsMobileNumberPublic bit,OfficePhone nvarchar,Extension varchar,Email nvarchar,IsEmailIdPublic bit,AlternateEmailId varchar,QuitDate datetime,DeptName varchar,DesignationName varchar,Religion varchar,Nationality varchar,PlaceOfBirth nvarchar,BirthDate datetime,JoiningDate datetime,Gender varchar,HousePhone nvarchar,MaritalStatusName varchar,BranchName varchar,ReportingManagerName nvarchar,EmployeeGrade int,IsEmployeeExit bit,ExitDate datetime,ReasonToLeave nvarchar,IsLeavedJob bit,SocialSecurityNo nvarchar,LeaveJobDate datetime,Status int,CreatedOn datetime,UpdatedOn datetime,PFNo nvarchar,ESINo nvarchar,CTC decimal,CreatedMachineIP varchar,UpdatedMachineIP varchar,IsTDSapplicable bit,GrossPay decimal,WorkerType int,EmpExitType nvarchar,JobLevelName varchar,companyid int,branchid int)'' CREATE TABLE Employee (EmployeeId numeric, UserId int, ApplicationId int, RoleId int, ShiftId int, UserName varchar, EmpCode varchar, FirstName nvarchar, LastName nvarchar, MiddleName nvarchar, FatherName nvarchar, ImageFilePath nvarchar, Phone nvarchar, CellPhone nvarchar, IsMobileNumberPublic bit, OfficePhone nvarchar, Extension nvarchar, Email nvarchar, IsEmailIdPublic bit, AlternateEmailId nvarchar, QuitDate datetime, DepartmentId int, CompanyID int, BranchID int, DesignationId int, ReligionId int, NationalityId int, BloodTypeId int, BloodType nvarchar, MaritalStatusId int, PlaceOfBirth nvarchar, BirthDate datetime, JoiningDate datetime, Gender varchar, HousePhone nvarchar, IsLeavedJob bit, LeaveJobDate datetime, LeaveJobReason nvarchar, TaxID int, SocialSecurityNo int, Status nvarchar, ReportingToEmpId int, CreatedOn datetime, UpdatedBy int, CreatedBy int, UpdatedOn datetime, EmpStatusId int, ReportingToHrId int, EmployeeStatus varchar, title nvarchar, PFNo int, ESINo varchar, SeriesTypeID bit, EmpCodeValue decimal, IsEmployeeExit bit, IsfullFinalsettelement bit, ExitDate datetime, RepotingToEMPCode int, CTC int, CreatedMachineIP nvarchar, UpdatedMachineIP nvarchar, IsTDSapplicable bit, GrossPay decimal, IsExcludePayroll bit, ReportingToFunctionalManager bit, SubDepartmentId int, Sub_SubDepartmentId int, WorkerType int, EmployeeGrade int, EmpExitType int, JobLevelId int, PayTypeId int, ClientId int)'' CREATE TABLE tblShortLeaves(ShortLeaveId int,EmployeeId int,DateOfShortLeave date,DateOfApplication date,LeaveStatus nvarchar,LeaveType nvarchar,Reason nvarchar,IsActive bit,HalfDayType nvarchar,ApprovedById nvarchar,DeclineById nvarchar,CreatedOn date,CreatedBy int,CreatedMachineIp nvarchar,UpdatedOn date,UpdatedBy int,UpdatedMachineIp nvarchar) --Using valid MS SQL Server, answer the following questions for the tables provided above. -- Short leave details, current year in Germany branch, department - Operations and are Married? 
### Response: SELECT e.EmpCode, e.FirstName + ' ' + ISNULL(e.MiddleName, '') AS EmployeeName, e1.EmpCode AS ApprovedByCode, e1.FirstName + ' ' + ISNULL(e1.MiddleName, '') AS ApprovedBy, CAST(CONVERT(DATE, [DateOfShortLeave], 101) AS VARCHAR) AS [DateOfShortLeave], CAST(CONVERT(DATE, [DateOfApplication], 101) AS VARCHAR) AS [DateOfApplication], [LeaveStatus], [LeaveType], [Reason], CASE WHEN [HalfDayType] = 'FH' THEN 'FirstHalf' WHEN [HalfDayType] = 'SH' THEN 'SecondHalf' ELSE [HalfDayType] END AS [HalfDayType] FROM [dbo].[tblShortLeave] C INNER JOIN employee e ON c.employeeid = e.employeeid LEFT JOIN employee e1 ON c.[ApprovedById] = e1.employeeid WHERE YEAR([DateOfShortLeave]) = YEAR(GETDATE()) AND e.UserName = ('" + {Username} + "') AND EXISTS (SELECT 1 FROM employeedetails empd WHERE empd.employeeid = e.employeeid AND empd.BranchName = 'Germany' AND MaritalStatusName = 'Single' AND DeptName = 'Operations')''