SQL Server Statement Exercise

Related tables:

<span style= "white-space:pre" >create table DEPT (<span style= "White-space:pre" ></span>deptno int Not NULL primary Key,<span style= "white-space:pre" ></span>dname varchar  null,<span style= " White-space:pre "></span>loc varchar () null) CREATE TABLE EMP (<span style=" White-space:pre "></ Span>empno int NOT null primary key,<span style= "White-space:pre" ></span>ename varchar (TEN) null,< Span style= "White-space:pre" ></span>job varchar (9) Null,<span style= "White-space:pre" ></span> Mgr Int Null,<span style= "white-space:pre" ></span>hiredate date null,<span style= "White-space:pre" ></span>sal float null,<span style= "white-space:pre" ></span>comm float null,<span style= " White-space:pre "></span>deptno int null foreign key references DEPT (DEPTNO),) CREATE TABLE Salgrade (<span Style= "White-space:pre" ></span>grade int null,<span style= "White-space:pre"></span>losal int Null,<span style= "white-space:pre" ></span>hisal int null,) </span> 

 query section: 
 

--Query The last three letters of the employee's name Select SUBSTRING (Ename,len (ename) -3,3) from dbo. emp--Query 10 The number of weeks that an employee enters the company, select Ename,datediff (Wk,hiredate,getdate ()) as the number of days to enter the week from dbo. EMP WHERE deptno=10--1 Queries all employees in department 30 SELECT * FROM dbo. EMP where deptno=30--2 lists all clerks (clerk) name, number and department number select ename, empno, deptno from emp where job= ' clerk '--3 find commission higher than salary employee SEL ECT * FROM dbo. The EMP WHERE comm>sal--the annual salary for each employee select Ename,sal*12+isnull (comm,0) *12 as the annual salary from dbo. Emp--4 find the employee of the Commission above the salary of 60% of the SELECT * FROM dbo. EMP WHERE comm>sal*0.6--5 Find out the details of all the clerks (clerk) in department 10 and all the manager (manager) and department 20 select * FROM dbo. EMP WHERE deptno=10 and job= ' manager ' OR deptno=20 and job= ' clerk '--6-details of all employees who are neither managers nor clerks but pay more than or equal to 2000 select * FROM Dbo. EMP WHERE job!= ' manager ' and job!= ' clerk ' and sal>=2000--7 find out the different jobs of the employees who charge the Commission select DISTINCT Job from dbo. EMP WHERE Comm is not null--8 find employees who do not charge commissions or receive commissions less than 100 of the SELECT * FROM dbo. EMP WHERE Comm is NULL OR comm<100--9 Find all employees employed on the 3rd day of the Month SELECT * FROM dbo. EMP WHERE MONTH (DATEADD (day,3,hiredate)) =month (DATEADD (month,1,hiredate))--10 find employees hired before 12 select * FROM dbo. EMP WHERE DATEDIFF (Year,hiredate,getdate ()) >12--11 displays all employees ' names in uppercase Select UPPER (substring (ename,1,1)) +lower ( SUBSTRING (Ename,2,len (ename)-1)) from EMP;--12 displays the name of the employee exactly 5 characters select ename from emp WHERE len (ename) =5--13 display without "R" The name of the employee of select ename from dbo. EMP WHERE ename '%r% '--14 displays the first three characters of all employees ' names select SUBSTRING (ename,1,3) from dbo. EMP--15 displays the names of all employees, substituting "a" for All "a" select replace (ename, ' a ', ' a ') from dbo. EMP--16 displays the name and employment date of the employee who has been in service for 10 years select Ename,hiredate from dbo. EMP WHERE DATEDIFF (Year,hiredate,getdate ()) &GT;10--17 displays employee details, sorted by name select * FROM dbo. EMP ORDER by ename--18 displays the employee's name and date of employment, according to their service life, ranked the oldest employee in front of select Ename,hiredate from dbo. EMP ORDER by DATEDIFF (Year,hiredate,getdate ()) desc--19 shows the names, jobs, and salaries of all employees, sorted in descending order of work, and if the work is the same salary sort select Ename,job, sal+ ISNULL (comm,0) as salary from dbo. EMP order BY Job, Sal+isnull (comm,0)--20 displays the names of all employees, the year and month of incorporation, sorted by the month of employment, and, if the month is the same, the employee of the earliest year is ranked in front of select Ename,year ( HireDate), MONTH (HireDate) from dbo. EMP ORDER by MONTH (HireDate), year (HireDate)--21 shows the daily salary for all employees in one months for 30 days, ignoring the remainder of select ROUND ((Sal+isnull (comm,0))/30,0) from dbo. Emp--22 Find all employees employed in (any year) February select * FROM dbo. EMP WHERE MONTH (hiredate) =2--23 for each employee, displays the number of days it joins the company select Ename, DATEDIFF (Day,hiredate,getdate ()) as the entry days from dbo. EMP--24 displays the names of all employees that contain "a" anywhere in the Name field, select Ename from dbo. EMP WHERE ename like '%a% '--25 displays the service life of all employees (approx.) SELECT STR (ROUND (DATEDIFF (Day,hiredate,getdate ())/365,0) + ' year ' + STR (ROUND (DATEDIFF (Day,hiredate,getdate ())%365/30+1,0) + ' month ' +str (DATEDIFF (Day,hiredate,getdate ())%365%30) + ' Day ' as Service life from dbo. Emp