实验要求:
1、首先定义数据表,输入语句如下: 为了演示如何使用SELECT语句,需要插入如下数据: INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES ('bs1',102,'orange', 11.2); INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES('bs2',105,'melon',8.2); INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES ('t1',102,'banana', 10.3); INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES ('t2',102,'grape', 5.3); INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES ('o2',103,'coconut', 9.2); INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES ('c0',101,'cherry', 3.2); INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES ('a2',103, 'apricot',2.2); INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES('l2',104,'lemon', 6.4); INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES ('b2',104,'berry', 7.6); INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES ('m1',106,'mango', 15.6); INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES ('m2',105,'xbabay', 2.6); INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES ('t4',107,'xbababa', 3.6); INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES ('m3',105,'xxtt', 11.6); INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES('b5',107,'xxxx', 3.6); 2、查询fruits表中f_name列所有水果名称 3、从fruits表中获取f_name和f_price两列 4、查询价格为10.2元的水果的名称 5、查询价格小于10的水果的名称 6、s_id为101和102的记录 7、查询所有s_id不等于101也不等于102的记录 8、查询价格在2.00元到10.20元之间的水果名称和价格 9、查询价格在2.00元到10.20元之外的水果名称和价格 10、查找所有以’b’字母开头的水果 11、在fruits表中,查询以字母’y’结尾,且’y’前面只有4个字母的记录(如果要匹配多个字符,则需要使用相同个数的’_’) 12、在fruits表中查询s_id = 101,并且f_price大于等于5的水果价格和名称 13、在fruits表中查询s_id = 101或者102,且f_price大于5,并且f_name=‘apple’的水果价格和名称 14、查询s_id=101或者s_id=102的水果供应商的f_price和f_name, 15、查询fruits表中s_id字段的值,返回s_id字段值且不得重复 |
代码如下:
1. create table fruits ( f_id varchar2(10) not null, s_id number(6) not null, f_name varchar(255) not null, f_price number(8,2) not null ); 2. select f_name from fruits; 3. select f_name,f_price from fruits; 4. select f_name from fruits where f_price=10.2; 5. select f_name from fruits where f_price<10; 6. select * from fruits where s_id=101 or s_id=102; 7. select * from fruits where s_id!=101 and s_id!=102; 8. select f_name,f_price from fruits where f_price>=2.00 and f_price<=10.20; 9. select f_name,f_price from fruits where f_price<2.00 or f_price>10.20; 10. select f_name from fruits where f_name like 'b%'; 11. select f_name from fruits where f_name like '____y'; 12. select f_price,f_name from fruits where s_id=101 and f_price>=5; 13. select f_price,f_name from fruits where (s_id=101 or s_id=102) and f_price>5 and f_name='apple'; 14. select f_price,f_name from fruits where s_id=101 or s_id=102; 15. select distinct s_id from fruits; |