SQL 入门教程:函数练习题

本文内容

  1. 我们的商店已经上线了,正在创建顾客账户。所有用户都需要登录名,默认登录名是其名称和所在城市的组合。编写 SQL 语句,返回顾客 ID(cust_id)、顾客名称(cust_name)和登录名(user_login),其中登录名全部为大写字母,并由顾客联系人的前两个字符(cust_contact)和其所在城市的前三个字符(cust_city)组成。例如,我的登录名是 BEOAK(Ben Forta,居住在 Oak Park)。提示:需要使用函数、拼接和别名。

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    
    -- DB2, PostgreSQL 
    SELECT cust_id, cust_name, 
    UPPER(LEFT(cust_contact, 2)) || UPPER(LEFT(cust_city, 3)) AS
    user_login 
    FROM customers; 
    
    -- Oracle, SQLite 
    SELECT cust_id, cust_name, 
    UPPER(SUBSTR(cust_contact, 1, 2)) || UPPER(SUBSTR(cust_city, 1, 3)) AS
    user_login 
    FROM customers; 
    
    -- MySQL 
    SELECT cust_id, cust_name, 
    CONCAT(UPPER(LEFT(cust_contact, 2)), UPPER(LEFT(cust_city, 3))) AS
    user_login 
    FROM customers; 
    
    -- SQL Server 
    SELECT cust_id, cust_name, 
    UPPER(LEFT(cust_contact, 2)) + UPPER(LEFT(cust_city, 3)) AS
    user_login 
    FROM customers;
    
  2. 编写 SQL 语句,返回 2020 年 1 月的所有订单的订单号(order_num)和订单日期(order_date),并按订单日期排序。你应该能够根据目前已学的知识来解决此问题,但也可以开卷查阅 DBMS 文档。

     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
    
    -- DB2, MariaDB, MySQL 
    SELECT order_num, order_date 
    FROM Orders 
    WHERE YEAR(order_date) = 2020 AND MONTH(order_date) = 1 
    ORDER BY order_date; 
    
    -- Oracle, PostgreSQL 
    SELECT order_num, order_date 
    FROM Orders 
    WHERE EXTRACT(year FROM order_date) = 2020 AND EXTRACT(month FROM
    order_date) = 1 
    ORDER BY order_date; 
    
    -- PostgreSQL 
    SELECT order_num, order_date 
    FROM Orders 
    WHERE DATE_PART('year', order_date) = 2020 
    AND DATE_PART('month', order_date) = 1 
    ORDER BY order_num; 
    
    -- SQL Server 
    SELECT order_num, order_date 
    FROM Orders 
    WHERE DATEPART(yy, order_date) = 2020 AND DATEPART(mm, order_date) = 1 
    ORDER BY order_date; 
    
    -- SQLite 
    SELECT order_num 
    FROM Orders 
    WHERE strftime('%Y', order_date) = '2020' 
    AND strftime('%m', order_date) = '01';
    

请参阅#

(完)

comments powered by Disqus

本文内容