一、在客户表customers中提取以下数据
1、输出客户全名(First_name+last_name)具有最多英文字母数的客户资料
select * from customers
where length(first_name||last_name)=(
select max(length(first_name||last_name))
from customers);
2、输出所有客户的资料,如果生日为空,则用1970年1月1日代替,如果电话为空,则用0代替。
select customer_id,first_name,last_name,
nvl(dob,to_date('19700101','yyyymmdd')),nvl(phone,'0')
from customers;
3、查询First_name中第2个字母是元音字母(aeiou)的客户全名
select first_name||' '||last_name
from customers
where lower(substr(first_name,2,1)) in ('a','e','i','o','u');
4、输出所有1970年6月1日之前出生的客户资料,其中生日(dob)的输出形式改成“xxxx 年xx月xx日”
select customer_id,first_name,last_name,
to_char(dob,'yyyy')||'年'||to_char(dob,'mm')
||'月'||to_char(dob,'dd')||'日',
phone
from customers
where dob 5、统计年龄大于17000天的客户数量 select count(*) from customers where sysdate-dob>17000; 6、统计年龄大于45岁的客户数量 select count(*) from customers where months_between(sysdate,dob)/12>45; 7、输出客户名字、客户生日以及客户生日所在月份有多少天 select first_name||' '||last_name,dob,to_char(last_day(dob),'dd') from customers; 8、提取客户名字、客户生日所在的月份 select first_name||' '||last_name,to_number(to_char(dob,'mm')) from customers; 二、在商品表products中提取以下数据 1、有几种不同的商品类型(product_type_id)?(不统计商品类型为空的商品) select count(distinct product_type_id) from products where product_type_id is not null; 2、商品描述信息(description)里带music的商品数量 select count(*) from products where description like'%music%'; select count(*) from products where instr(description,'music')>0; 3、查询描述信息(description)里带alien的商品价格总和。其中alien中的任意字母可以是大写或者小写 select sum(price) from products where lower(description) like'%alien%'; 4、输出所有商品资料,把其中的描述信息(description)改成全部大写 select product_id,product_type_id,name,upper(description),price from products; 5、输出所有商品资料,把其中的描述信息(description)改成首字母小写,其他字母大写select product_id,product_type_id,name, lower(substr(description,1,1))||upper(substr(description,2)), price from products; 6、输出所有商品资料,把其中的描述信息(description)中的小写字母a全部改成大写字母A,其他字符不变。 select product_id,product_type_id,name, replace(description,'a','A'), price from products; 7、输出所有商品资料,把其中的描述信息(description)中的小写字母a全部改成大写字母A,大写字母A全部改成小写字母a,其他字符不变。 select product_id,product_type_id,name, replace(replace(replace(description,'a','_'),'A','a'),'_','A'), price from products; 8、输出最贵的商品和最便宜的商品之间相差多少钱。 select max(price)-min(price) from products; 9、根据商品类型(product_type_id)统计每类商品的平均价格,四舍五入到小数点后两位。select product_type_id,round(avg(price),2) from products group by product_type_id; 10、根据商品类型(product_type_id)统计每类商品总数。 select product_type_id,count(*) from products group by product_type_id; 11、根据商品类型(product_type_id)统计价格总和大于40的商品类型(product_type_id)、商品总数和商品总价 select product_type_id,count(*),sum(price) from products group by product_type_id having sum(price)>40; 12、输出价格最接近平均价格的商品名字 select name from products where abs(price)-(select avg(price) from products)=( select min(abs(price)-(select avg(price) from products)) from products); 13、输出所有小于平均价格的商品资料,并按价格从低到高排序 select * from products where price<(select avg(price) from products) order by price; 14、统计价格最高的商品名字 select name from products where price=(select max(price) from products); 15、根据商品类型分组统计价格最高的商品名字 select product_type_id,name from products p where price=( select max(price) from products where product_type_id=p.product_type_id); 三、在客户表customers、购买记录表purchases中提取以下数据 1、按客户id归类统计客户id、客户名字、客户的购买记录总数 select customer_id,first_name||' '||last_name,count(*) from customers inner join purchases using(customer_id) group by customer_id,first_name||' '||last_name; 2、按客户id归类统计客户id、客户名字、客户购买的商品数量(quantity)总数select customer_id,first_name||' '||last_name,sum(quantity) from customers inner join purchases using(customer_id) group by customer_id,first_name||' '||last_name; 四、在购买记录表purchases、商品表products中提取以下数据 1、按商品id归类统计商品id、商品名字、客户购买的商品数量(quantity)总数select product_id,name,sum(quantity) from products inner join purchases using(product_id) group by product_id,name; 2、提取至少被4个不同客户购买过的商品id和商品名称 select product_id,name from products where product_id in ( select product_id from purchases group by product_id having count(distinct customer_id)>=4); 五、在购买记录表purchases、商品表products、商品类型表product_types中提取以下数据 1、以商品类型分类(product_type_id),输出销量最好的商品类型和对应的类型名字select product_type_id,name from product_types where product_type_id in ( select product_type_id from purchases inner join products using(product_id) group by product_type_id having sum(quantity)=( select max(sum(quantity)) from purchases inner join products using(product_id) group by product_type_id)); 2、以商品类型分类(product_type_id),输出销量前3位的商品类型和对应的类型名字select product_type_id,name from product_types where product_type_id in ( select product_type_id from ( select product_type_id from purchases inner join products using(product_id) group by product_type_id order by sum(quantity) desc) where rownum<=3); 3、以商品类型分类(product_type_id),输出销量后3位的商品类型和对应的类型名字select product_type_id,name from product_types where product_type_id in ( select product_type_id from ( select product_type_id from purchases inner join products using(product_id) group by product_type_id order by sum(quantity)) where rownum<=3); 六、在商品表products和商品类型表product_types中提取以下数据 1、输出最贵的商品对应的商品类型名称(product_https://www.wendangku.net/doc/0610832740.html,) select name from product_types where product_type_id=( select product_type_id from products where price=(select max(price) from products)) 2、输出平均价格最高的商品类型名称(product_https://www.wendangku.net/doc/0610832740.html,) select name from ( select product_type_id,product_https://www.wendangku.net/doc/0610832740.html, from products inner join product_types using(product_type_id) group by product_type_id,product_https://www.wendangku.net/doc/0610832740.html, order by avg(price) desc) where rownum<=1; 3、输出比Book类商品平均价格高的商品类型名称(product_https://www.wendangku.net/doc/0610832740.html,),并按平均价格从高到低排序 select name from ( select product_type_id,product_https://www.wendangku.net/doc/0610832740.html,,avg(price) avgprice from products inner join product_types using(product_type_id) group by product_type_id,product_https://www.wendangku.net/doc/0610832740.html, having avg(price)>( select avg(price) from products inner join product_types using(product_type_id) where product_https://www.wendangku.net/doc/0610832740.html,='Book')) order by avgprice desc; 4、输出商品数量最多的商品类型名称(product_https://www.wendangku.net/doc/0610832740.html,) select name from ( select product_type_id,product_https://www.wendangku.net/doc/0610832740.html,,count(*) from products inner join product_types using(product_type_id) group by product_type_id,product_https://www.wendangku.net/doc/0610832740.html, order by count(*) desc) where rownum<=1; 七、在员工表employees和工资等级表salary_grades中提取以下数据 1、提取工资等级为2的员工资料 select employees.* from employees inner join salary_grades on (salary>=low_salary and salary<=high_salary) where salary_grade_id=2;