论坛 › mysql › MySQL基础20题(续前面的20题)

爱新觉罗买买提 发表于 2024-7-31 18:26:34

MySQL基础20题(续前面的20题)

写在前面

今天继续前面的20题之后的练习，数据都是一样的，可以直接使用，来看看你的sql功底降了没。
基础20题

#1.查询每个员⼯的姓名、邮箱、职位名称以及所在部⻔名称。
        SELECT
        CONCAT(last_name,first_name) as 姓名,
        email,
        job_title,
        department_name
FROM
        employees e
        JOIN departments d ON e.department_id=d.department_id
        JOIN jobs j ON e.job_id = j.job_id ;

#2.查询⽉薪最⾼的员⼯的姓名、职位名称以及⽉薪。
        SELECT
        CONCAT(last_name,first_name) as 姓名,
        job_title,
        salary
FROM
        employees e
JOIN jobs j on e.job_id = j.job_id
WHERE        salary = (SELECT MAX(salary) FROM employees);

#3.查询每个部⻔的平均⽉薪。
#注：如果某一部门没有员工不会显示
        SELECT
        department_name,
        AVG(salary)
FROM
        employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY
        department_name;   

#4.查询部⻔中员⼯⽉薪⾼于5000的所有员⼯的姓名、⽉薪以及所在部⻔名称。
        SELECT
        CONCAT(last_name,first_name) as 姓名,
        salary,
department_name
FROM
        employees e
JOIN departments d ON e.department_id = d.department_id
WHERE
        salary > 5000;

#5.查询奖⾦率最⾼的员⼯的姓名、职位名称以及奖⾦率。
        SELECT
        CONCAT(last_name,first_name) as 姓名,
        job_title,
        commission_pct
FROM
        employees e
JOIN jobs j ON e.job_id = j.job_id
WHERE
        commission_pct = (SELECT MAX(commission_pct) FROM employees);

#6.查询每个职位的最⾼⽉薪和最低⽉薪。
        SELECT
        job_title,
        MAX(salary),
        MIN(salary)
FROM
        employees e
JOIN jobs j ON e.job_id = j.job_id
GROUP BY
        job_title;
       
#7.查询⼊职时间早于2000年的所有员⼯的姓名、⼊职⽇期以及职位名称。
        SELECT
        CONCAT(last_name,first_name) as 姓名,
        hiredate,
        job_title
FROM
        employees e
JOIN jobs j on e.job_id = j.job_id
WHERE
        YEAR(hiredate) < 2000;

#8.查询每个部⻔的员⼯⼈数。
        SELECT
        COUNT(*) as 人数,
        department_name
FROM
employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY
        department_name;

#9.查询在每个部⻔中⽉薪最⾼的员⼯的姓名、⽉薪以及部⻔名称。
#注：在使用in时可以使用（）将需要的列括起来查询
        SELECT
        CONCAT(e.last_name,e.first_name) as 姓名,
        e.salary,
        d.department_name
FROM
        employees e JOIN departments d ON e.department_id = d.department_id
WHERE
        (e.salary,d.department_id) in (
SELECT MAX(salary),department_id FROM employees GROUP BY department_id);               

#10.查询⽉薪超过部⻔平均⽉薪的员⼯的姓名、⽉薪以及所在部⻔名称。
        SELECT
        CONCAT( e1.last_name, e1.first_name ) AS 姓名,
        e1.salary,
        d.department_name
FROM
        employees e1
        JOIN departments d ON e1.department_id = d.department_id
WHERE
e1.salary > ( SELECT AVG( e2.salary ) FROM employees e2 WHERE e1.department_id = e2.department_id);

#11.查询每个职位的员⼯⼈数。
        SELECT
        COUNT(*) as 人数,
        job_title
FROM
        employees e
JOIN jobs j ON e.job_id = j.job_id
GROUP BY
        job_title;

#12.查询每个部⻔的最⾼⽉薪和最低⽉薪。
        SELECT
        department_name,
        MAX(salary),
        MIN(salary)
FROM
        employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY
        department_name;

#13.查询每个员⼯的姓名、邮箱、职位名称以及他们的上级领导的姓名。
        SELECT
        CONCAT(e1.last_name,e1.first_name) as 姓名,
        e1.email,
        j.job_title,
        CONCAT(e2.last_name,e2.first_name) as 上级领导
FROM
        employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id
JOIN jobs j ON e1.job_id = j.job_id;

#14.查询每个部⻔的员⼯平均奖⾦率。
        SELECT
        department_name,
        AVG(commission_pct) as 平均奖金率
FROM
        employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY
        department_name;

#15.查询每个城市的员⼯⼈数。
        SELECT
        COUNT(*) as 人数,
        city
FROM
        employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
GROUP BY
        city;

#16.查询每个部⻔的职位种类数。
#注：使用distinct可以去重算种类数
        SELECT
        COUNT(DISTINCT job_id) as 种类数,
        department_name
FROM
        employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY
        department_name;

#17.查询⼯资⾼于其职位平均⼯资的员⼯姓名、职位名称以及⽉薪。
        SELECT
        CONCAT(e1.last_name, e1.first_name ) AS 姓名,
        j.job_title,
        e1.salary
FROM
employees e1
JOIN jobs j ON e1.job_id = j.job_id
WHERE
        e1.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e1.job_id = e2.job_id);

#18.查询每个国家的员⼯⼈数。
        SELECT
        COUNT(DISTINCT employee_id) as 人数,
        country_id
FROM
        employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
GROUP BY
        country_id;

#19.查询没有领导的员⼯的姓名以及职位名称。
        SELECT
        CONCAT( last_name, first_name ) AS 姓名,
        job_title
FROM
        employees e
JOIN jobs j ON e.job_id = j.job_id
WHERE
        manager_id is null ;

#20.查询job_id为"IT_PROG"的员⼯的姓名、职位名称以及⽉薪。
        SELECT
        CONCAT( last_name, first_name ) AS 姓名,
        job_title,
        salary
FROM
        employees e
JOIN jobs j ON e.job_id = j.job_id
WHERE
        e.job_id = 'IT_PROG';好了，今天的分享结束了，答案仅供参考不代表最终答案，如果有更换的方法，欢迎在底下留言评论！

来源:https://www.cnblogs.com/cjybigdatablog/p/18334913
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！

查看完整版本: MySQL基础20题(续前面的20题)