SQL server statement exercises, SQL server statements

SQL server statement exercises, SQL server statements

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(14)  null,<span style="white-space:pre"></span>loc varchar(13) null)create table EMP(<span style="white-space:pre"></span>empno int not null primary key,<span style="white-space:pre"></span>ename varchar(10) 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 an employee's name: select substring (ename, LEN (ename)-3) FROM dbo. EMP -- query the number of weeks for employees in department 10 to enter the company. SELECT ename, DATEDIFF (wk, hiredate, GETDATE () AS the number of weeks to enter FROM dbo. emp where deptno = 10--1 query all employees in department 30 SELECT * FROM dbo. emp where deptno = 30--2 lists the names, numbers, and department numbers of all clerks (clers): SELECT ename, empno, deptno FROM emp WHERE job = 'cler' -- 3 find the employee whose commission is higher than the salary SELECT * FROM dbo. emp where comm> sal -- calculate the annual salary of each employee, SELECT ename, sal * 12 + ISNULL (comm, 0) * 1 2 AS annual salary FROM dbo. EMP--4 to find employees with a Commission higher than 60% of salaries SELECT * FROM dbo. emp where comm> sal * 0.6 -- 5 find the details of all managers (managers) and all clerks (clers) in department 10 SELECT * FROM dbo. emp where deptno = 10 AND job = 'manager' OR deptno = 20 and job = 'cler' -- 6 -- for all employees who are neither a manager nor a CLERK but have a salary of more than OR equal to 2000 for details, SELECT * FROM dbo. emp where job! = 'Manager' AND job! = 'Cler' AND sal> = 2000--7 find out the different jobs of the employees who receive the Commission select distinct job FROM dbo. emp where comm is not null--8 find out SELECT * FROM dbo for employees who do NOT charge commission or receive commission less than 100. emp where comm is null or comm <100--9 finds all employees employed for the last 3rd days of each month SELECT * FROM dbo. emp where month (DATEADD (DAY, 3, hiredate) = MONTH (DATEADD (MONTH, 1, hiredate) -- 10 find the employee who was hired 12 years ago SELECT * FROM dbo. emp where datediff (YEAR, hiredate, GETDATE ()> 12--11 display the names of all employees in the upper case of the first letter select upper (substring (ename, 1, 1 )) + lower (substring (ename, 2, len (ename)-1) from emp; -- 12: SELECT ename FROM emp where len (ename) = 5--13 shows the name of an employee without "R" SELECT ename FROM dbo. emp where ename not like '% R %' -- 14 displays the first three characters of the names of all employees, select substring (ename, 1, 3) FROM dbo. the EMP--15 displays the names of all employees, replacing all "a" select replace (ename, 'A', 'A') FROM dbo. the EMP--16 shows the name and employment date of the employee who has served for 10 years SELECT ename, hiredate FROM dbo. emp where datediff (year, hiredate, GETDATE ()> 10--17 displays employee details, sorted by name SELECT * FROM dbo. emp order by ename -- 18 displays the employee's name and employment date. Based on the service life, the oldest employee is placed in front of SELECT ename, hiredate FROM dbo. emp order by datediff (year, hiredate, GETDATE () The DESC--19 displays the names, jobs, and salaries of all employees, sorted in descending ORDER of jobs, SELECT ename if jobs are the same, 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 joining the company, sorted BY the month of the employment date, if the MONTH is the same, SELECT ename, YEAR (hiredate), MONTH (hiredate) FROM dbo. emp order by month (hiredate), YEAR (hiredate) -- 21 shows the situation that the MONTH is 30 days, the daily salary of all employees, ignore the remainder select round (sal + ISNULL (comm, 0)/30,0) FROM dbo. the EMP--22 finds all employees employed in (any year) February SELECT * FROM dbo. emp where month (hiredate) = 2--23 shows the number of days each employee joins the company: SELECT ename, DATEDIFF (DAY, hiredate, GETDATE () AS number of days of entry FROM dbo. the EMP--24 displays the names of all employees whose name field contains "A" SELECT ename FROM dbo. emp where ename LIKE '% A %' -- 25 shows the service life of all employees by year, month, and day (approximate) select str (ROUND (DATEDIFF (day, hiredate, GETDATE ()) /365/30) + 'Year' + str (ROUND (DATEDIFF (day, hiredate, GETDATE () % +) + 'month' + STR (DATEDIFF (day, hiredate, GETDATE () % 365% 30) + 'day' AS service life FROM dbo. EMP

After installing SQLserver2000, I want to exercise the input SQL statement.

If you are a newbie, you are advised to enter the Enterprise Manager. First, the system recognizes the following table, such as views and stored procedures.
However, we recommend that you use the database
First, import the learning database to your current database.
Then go to the Enterprise Manager and try again.
At least when you click the corresponding button, select * from table, update table set... will automatically appear .... And other simple statements

In summary, if you want to learn the system, you can buy a book to master the basic knowledge and then find a case to practice.
If you only want to deal with a specific operation, you can directly view the SQL statements of that database, such as stored procedures and views.
After the backup is done-for example, copy the SQL statement directly and paste it into the new text document and save it.
If an error occurs, you can change it back.

Who has SQL statement exercises, the more the better?

I only know the SQL Server T-SQL.
SQL Server 2000 (Chinese version) Development and Management of Application Instances
Www.china-pub.com/26313
Instances that can be used in a large number of projects
The books of csdn SQL moderators are very good.
Hope to help you

If you want to further understand the principles
Can look at the Microsoft technology series sql2005 T-SQL query and T-SQL Programming