SQL语言学习笔记--使用函数处理数据

实战题目：

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

--DB2,postgreSQL
SELECT cust_id,cust_name,upper(LEFT(cust_contact,2)) || UPPER(LEFT(cuat_city,3)) as user_login
from customers;

--oracle,sqllite
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），并按订单日期排序。

--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;

--sqlserver
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';