四、SQL语句:查询<下>


通过上一节的讲解,我们对SQL语句的基础知识有个大概的了解,下面我们接着通过一些习题来巩固SQL语句

问题如下:
1、显示所有职工的年龄
2、求出各部门的党员人数
3、显示所有职工的姓名和2004年1月份的工资
4、显示所有职工的职工号,姓名和平均工资
5、显示所有职工的职工号,姓名,部门名和2004年5月的工资,并按部门名顺序排序
6、显示各部门名和该部门所有职工的平均工资
7、显示所有平均工作高于1200的部门名和对应的平均工资
8、显示各部门的职工号,姓名和部门类型,其中财务处和人事处属于管理部门
9、如果存在职工号为10的职工,则显示其工作部门名字,否则显示相应的提示信息
10、比较男职工和女职工的平均工资

备注:为方便查看查询结果,请先运行如下基础代码,并以此作为基础数据进行下面习题的练习

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
/*1.建表*/

/*职工表 worker*/
CREATE TABLE worker
(
 职工号 INT PRIMARY KEY
,姓名 CHAR(8)
,性别 CHAR(2)
,出生日期 datetime
,党员否 VARCHAR(2) DEFAULT('否') NOT NULL
,参加工作 datetime
,部门号 INT
)

/*部门表 depart */
CREATE TABLE depart
(
 部门号 INT  
,部门名 CHAR(10)
,PRIMARY KEY(部门号)
)

/*工资表 salary*/
CREATE TABLE salary
(
 职工号 INT NOT NULL
,姓名 CHAR(8)
,日期 datetime NOT NULL  
,工资 DECIMAL(6,1)
,PRIMARY KEY(职工号,日期)
)

/*插入数据*/
INSERT INTO depart(部门号,部门名) VALUES(1,'财务处')
INSERT INTO depart(部门号,部门名) VALUES(2,'人事处')
INSERT INTO depart(部门号,部门名) VALUES(3,'市场部')

INSERT INTO worker(职工号,姓名,性别,出生日期,党员否,参加工作,部门号) VALUES(1,'孙华','男','1952-03-01','是','1970-10-10',1)
INSERT INTO worker(职工号,姓名,性别,出生日期,党员否,参加工作,部门号) VALUES(2,'陈明','男','1948-08-05',DEFAULT,'1965-01-01',2)
INSERT INTO worker(职工号,姓名,性别,出生日期,党员否,参加工作,部门号) VALUES(3,'程西','男','1980-10-06',DEFAULT,'2002-10-07',1)
INSERT INTO worker(职工号,姓名,性别,出生日期,党员否,参加工作,部门号) VALUES(4,'孙天奇','女','1965-10-03','是','1987-10-07',3)
INSERT INTO worker(职工号,姓名,性别,出生日期,党员否,参加工作,部门号) VALUES(5,'刘夫文','男','1942-03-03','是','1969-09-10',2)
INSERT INTO worker(职工号,姓名,性别,出生日期,党员否,参加工作,部门号) VALUES(6,'刘天','男','1942-03-03','是','1969-09-10',2)
INSERT INTO worker(职工号,姓名,性别,出生日期,党员否,参加工作,部门号) VALUES(10,'刘夫文','女','1942-09-21','是','1965-09-11',3)

INSERT INTO salary(职工号,姓名,日期,工资) VALUES(1,'孙华','2004-04-01',1201.5)
INSERT INTO salary(职工号,姓名,日期,工资) VALUES(2,'陈明','2004-04-01',1350.6)
INSERT INTO salary(职工号,姓名,日期,工资) VALUES(3,'程西','2004-04-01',780.5)
INSERT INTO salary(职工号,姓名,日期,工资) VALUES(4,'孙天奇','2004-04-01',900.0)
INSERT INTO salary(职工号,姓名,日期,工资) VALUES(5,'刘夫文','2004-04-01',2006.8)
INSERT INTO salary(职工号,姓名,日期,工资) VALUES(1,'孙华','2004-05-01',690.5)
INSERT INTO salary(职工号,姓名,日期,工资) VALUES(2,'陈明','2004-05-01',3100.6)
INSERT INTO salary(职工号,姓名,日期,工资) VALUES(3,'程西','2004-05-01',1563.2)
INSERT INTO salary(职工号,姓名,日期,工资) VALUES(4,'孙天奇','2004-05-01',2310.4)
INSERT INTO salary(职工号,姓名,日期,工资) VALUES(5,'刘夫文','2004-05-01',180.2)
INSERT INTO salary(职工号,姓名,日期,工资) VALUES(10,'刘夫文','2004-05-01',5000.0)

参考答案如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
/*1.显示所有职工的年龄*/
SELECT 职工号,姓名,(YEAR(getdate())-YEAR(出生日期)) AS 年龄
FROM worker

/*2.求出各部门的党员人数*/
SELECT d.部门名,COUNT(党员否) AS 党员人数
FROM worker w,depart d
WHERE  党员否 = '是'
   AND w.部门号 = d.部门号
GROUP BY d.部门名

/*3.显示所有职工的姓名和2004年1月份的工资*/
SELECT 姓名,工资
FROM salary
WHERE 日期 = '2004-04-01'

/*4.显示所有职工的职工号,姓名和平均工资*/

/*  --当职工号与姓名一一对应时,即无同名存在时,可使用该方法
select s.职工号,s.姓名,avg(工资) as 平均工资
from worker w,salary s
where  w.职工号 = s.职工号
group by s.职工号,s.姓名
*/


SELECT w.姓名,s.职工号,s.平均工资     /*子查询*/
FROM worker w ,(
    SELECT s.职工号,avg(工资) AS 平均工资
    FROM worker w,salary s
    WHERE  w.职工号 = s.职工号
    GROUP BY s.职工号) s
WHERE w.职工号 = s.职工号

/*5.显示所有职工的职工号,姓名,部门名和2004年5月的工资,并按部门名顺序排序*/
SELECT  w.职工号, w.姓名,d.部门名,s.工资 AS '2004年5月_工资'
FROM worker w,depart d,salary s
WHERE w.职工号 = s.职工号
  AND w.部门号 = d.部门号
  AND s.日期  = '2004-05-01'
ORDER BY d.部门名

/*6.显示各部门名和该部门所有职工的平均工资*/
SELECT d.部门名,avg(工资) AS '该部门平均工资'
FROM worker w,depart d,salary s
WHERE w.职工号 = s.职工号
  AND w.部门号 = d.部门号
GROUP BY d.部门名

/*7.显示所有平均工作高于1200的部门名和对应的平均工资 ---聚合函数不能放在where字句后*/
SELECT d.部门名,avg(工资) AS '该部门平均工资'
FROM worker w,depart d,salary s
WHERE w.职工号 = s.职工号
  AND w.部门号 = d.部门号
GROUP BY d.部门名
HAVING avg(工资) &gt; 1200

/*8.显示各部门的职工号,姓名和部门类型,其中财务处和人事处属于管理部门*/
SELECT w.职工号,姓名,
      CASE 部门名
    WHEN '财务处' THEN '管理部'
    WHEN '人事处' THEN '管理部'
        WHEN '市场部' THEN '市场部'
      ELSE '其他'
      END AS '部门名'
FROM worker w, depart d
WHERE w.部门号 = d.部门号

/*9.如果存在职工号为10的职工,则显示其工作部门名字,否则显示相应的提示信息*/
BEGIN
    DECLARE @woker_name INT
    SET @woker_name = 10

    IF (SELECT 职工号 FROM worker WHERE 职工号 = @woker_name) = @woker_name
        BEGIN
            SELECT '存在职工号为10的员工!'
            SELECT '他的信息为:'

            SELECT w.姓名,d.部门名
            FROM worker w,depart d
            WHERE w.部门号 = d.部门号
                  AND w.职工号 = @woker_name
        END
    ELSE
        SELECT '对不起,不存在职工号为10的员工!'
END

/*10.比较男职工和女职工的平均工资*/
BEGIN
    DECLARE @avg_male INT
    DECLARE @avg_female INT

    SET @avg_male = (
        SELECT avg(工资) AS '平均工资'
        FROM worker w,salary s
        WHERE w.职工号 = s.职工号
        GROUP BY 性别
        HAVING 性别 = '男')

    SET @avg_female = (
        SELECT avg(工资) AS '平均工资'
        FROM worker w,salary s
        WHERE w.职工号 = s.职工号
        GROUP BY 性别
        HAVING 性别 = '女')

    print '男职工的平均工资:'
    print  @avg_male

    print '女职工的平均工资:'
    print  @avg_female

    IF  (@avg_male &gt; @avg_female)
        print '男职工比女职工的工资高多了!'
    ELSE IF @avg_male / @avg_female BETWEEN 0.8 AND 1.5
        print '男职工比女职工的工资差不多!'
    ELSE   
        print '女职工比男职工的工资高多了!'
END
Tags:
文 / 甲乙丙丁
LEAVE A REPLY

loading