SQL语句练习

1. 小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。
   其中纵列的 id 是连续递增的
   小美想改变相邻俩学生的座位。

   ±--------±--------+
   | id | student |
   ±--------±--------+
   | 1 | Abbot |
   | 2 | Doris |
   | 3 | Emerson |
   | 4 | Green |
   | 5 | Jeames |
   ±--------±--------+
   假如数据输入的是上表，则输出结果如下：
   ±--------±--------+
   | id | student |
   ±--------±--------+
   | 1 | Doris |
   | 2 | Abbot |
   | 3 | Green |
   | 4 | Emerson |
   | 5 | Jeames |
   ±--------±--------+

   	SELECT *
   	  FROM (SELECT ID - 1 AS ID, STUDENT
   	          FROM SEAT
   	         WHERE ID%2 = 0
   	        UNION
   	        SELECT ID + 1 AS ID, STUDENT
   	          FROM SEAT
   	         WHERE ID%2 = 1
   	           AND (ID + 1) <= (SELECT COUNT(*) FROM SEAT)
   	        UNION
   	        SELECT ID AS ID, STUDENT
   	          FROM SEAT
   	         WHERE ID%2 = 1
   	           AND (ID + 1) > (SELECT COUNT(*) FROM SEAT)) AS T1
   	 ORDER BY ID ASC;
   

   	SELECT (CASE
   	         WHEN MOD(ID, 2) = 1 AND ID = (SELECT COUNT(*) FROM SEAT) THEN
   	          ID
   	         WHEN MOD(ID, 2) = 1 THEN
   	          ID + 1
   	         ELSE
   	          ID - 1
   	       END) AS ID,
   	       STUDENT
   	  FROM SEAT
   	 ORDER BY ID;
   

2. 找出每个部门工资前三高的员工。

   SELECT D.NAME AS DEPARTMENT,E.NAME AS EMPLOYEE,E.SALARY AS SALARY
      FROM EMPLOYEE AS E
      INNER JOIN DEPARTMENT AS D
      ON E.DEPARTMENTID = D.ID
      WHERE (
        SELECT COUNT(DISTINCT SALARY)
            FROM EMPLOYEE
   	    WHERE SALARY > E.SALARY 
           AND DEPARTMENTID = E.DEPARTMENTID
      ) < 3
      ORDER BY E.DEPARTMENTID,SALARY DESC;
   

扩展：
sql的left join 、right join 、inner join之间的区别
　　left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
　　right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
　　inner join(等值连接) 只返回两个表中联结字段相等的行

3. 交换性别

   UPDATE salary
    SET
      sex = CASE sex
          WHEN 'm' THEN 'f'
          ELSE 'm'
      END;
   

SHELL脚本
4.统计词频

awk '{for(i=1;i<NF;I++){
         res[$i]+=1
         }} end{for( k in res){
          print k " " res[k]
         }}' words.txt | sort -nr -k2

5. 统计有效电话

假设 file.txt 内容如下：
987-123-4567
123 456 7890
(123) 456-7890
你的脚本应当输出下列有效的电话号码：
987-123-4567
(123) 456-7890

grep '^\(([0-9]\{3\}) \|[0-9]\{3\}-\)[0-9]\{3\}-[0-9]\{4\}$' file.txt

6.输出文本的第十行内容

awk 'NR==10{print $0}' file2.txt