Jimmy Chen

A Programmer

使用HR数据库进行练习之日期和时间函数

使用HR数据库进行练习之日期和时间函数

Practice 1

  编写一条SQL查询语句,显示当前月份前三个月的月份的第一天(datetime格式)

例如当前:2017-12-08
期望结果:2017-09-01

SELECT CAST(CONCAT(YEAR(CURRENT_DATE()), '-', MONTH(CURRENT_DATE())-3, '-', '01') AS DATE);

Practice 2

  编写一条SQL查询语句,显示当前月份前三个月的月份的最后一天(datetime格式)

SELECT LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 3 MONTH));

Practice 3

  编写一条SQL查询语句,显示今年的第一天

SELECT MAKEDATE(EXTRACT(YEAR FROM CURRENT_DATE()), 1);

Practice 4

  编写一条SQL查询语句,显示今年的最后一天

SELECT (STR_TO_DATE(CONCAT(12, 31, YEAR(CURRENT_DATE())), '%m%d%Y'));

Practice 5

  编写一条SQL查询语句,显示你的年龄

SELECT YEAR(CURRENT_TIMESTAMP) - 
         YEAR("1992-04-09") - 
             (RIGHT(CURRENT_TIMESTAMP, 5) < 
                  RIGHT("1992-04-09", 5)) as age;

Practice 6

  编写一条SQL查询语句,按照如下格式显示日期

示例日期:2014-09-04

输出日期:September 4,2014

SELECT DATE_FORMAT(CURRENT_DATE(), '%M %e, %Y');

Practice 7

  编写一条SQL查询语句,获取使用Thursday September 2014的格式显示当前时间

SELECT DATE_FORMAT(CURRENT_DATE(), '%W %M %Y');

Practice 8

  编写一条SQL查询语句,获取当前时间的准确年份

SELECT YEAR(CURRENT_DATE());

Practice 9

  编写一条SQL查询语句,获取从一个给定的值获取DATE值

示例值:730677

输出日期:2000-07-11

SELECT FROM_DAYS(730677);

Practice 10

  编写一条SQL查询语句,获取雇用日期在‘1987-06-01’到‘1987-07-30’间的employee的first name和hire datge

SELECT first_name, hire_date FROM employees
    WHERE hire_date BETWEEN '1987-06-01' AND '1987-07-30';

Practice 11

  编写一条SQL查询语句,按照下面的示例格式显示当前日期

输出示例:Thursday 4th September 2014 00:00:00

SELECT DATE_FORMAT(CURRENT_TIMESTAMP(), '%W %D %M %Y %T') ;

Practice 12

  编写一条SQL查询语句,按照下面的格式显示当前时间

输出示例:05/09/2014

SELECT DATE_FORMAT(CURRENT_DATE(), '%d/%m/%Y');

Practice 13

  编写一条SQL查询语句,按照如下格式显示当前时间

SELECT DATE_FORMAT(CURRENT_TIMESTAMP(), '%h:%i %p %b %e, %Y');

Practice 14

  编写一条SQL查询语句,显示六月入职的employee的first name,last name

SELECT first_name, last_name FROM employees
    WHERE MONTH(hire_date) = 6;

Practice 15

  编写一条SQL查询语句,显示超过10个employees加入的年份

SELECT YEAR(hire_date) FROM employees
    GROUP BY YEAR(hire_date) HAVING COUNT(*) > 10;

Practice 16

  编写一条SQL查询语句,显示在1987年加入的employee的first name

SELECT first_name FROM employees WHERE YEAR(hire_date) = 1987;

Practice 17

  编写一条SQL查询语句,显示工作经验超过5年的manager的department名字,manager的名字和薪酬

SELECT department_name, first_name, salary FROM departments d
    JOIN employees e ON d.manager_id = e.manager_id
    WHERE DATEDIFF(CURRENT_DATE(), hire_date)/365 > 5;

Practice 18

  编写一条SQL查询语句,显示employee的ID,last name和第一份薪水的日期

SELECT employee_id, last_name, hire_date,  LAST_DAY(hire_date)
    FROM employees;

Practice 19

  编写一条SQL查询语句,显示employee的first name,雇用如期和工作经验

SELECT employee_id, first_name, hire_date, DATEDIFF(CURRENT_DATE(), hire_date)/365 experience
    FROM employees;

Practice 20

  编写一条SQL查询语句,显示department ID,年份和当年加入的employee数量

SELECT department_id, YEAR(hire_date), COUNT(employee_id)
    FROM employees GROUP BY department_id, YEAR(hire_date)
    ORDER BY department_id;

发表评论

电子邮件地址不会被公开。 必填项已用*标注

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d 博主赞过: