Oracle数据库实验四查询实验三（Oracle 11g）

实验要求：

1、在fruits表和suppliers表之间使用等值连接查询。（  s_id   ，s_name，f_name, f_price ）   ， 2、在fruits表和suppliers表之间，使用INNER JOIN语法进行内连接查询（  s_id   ，s_name，f_name, f_price ）   ， SELECT suppliers.s_id, s_name,f_name, f_price  FROM fruits INNER JOIN suppliers   ON fruits.s_id = suppliers.s_id; 3、．LEFT JOIN左连接 首先创建表orders，SQL语句如下： CREATE TABLE orders (   o_num  number(9)   NOT NULL,   o_date  date         NOT NULL,   c_id    number(9)    NOT NULL,   PRIMARY KEY (o_num) ) ; 插入需要演示的数据，SQL语句如下： INSERT INTO orders(o_num, o_date, c_id)VALUES(30001, '01-9月-2008', 10001); INSERT INTO orders(o_num, o_date, c_id)VALUES (30002, '12-9月-2008', 10003) ; INSERT INTO orders(o_num, o_date, c_id)VALUES (30003, '30-9月-2008', 10004) ; INSERT INTO orders(o_num, o_date, c_id)VALUES (30004, '03-10月-2008', 10005) ; INSERT INTO orders(o_num, o_date, c_id)VALUES (30005, '08-10月-2008', 10001) ; 在customers表和orders表中，查询所有客户，包括没有订单的客户 4、RIGHT JOIN右连接 在customers表和orders表中，查询所有订单，包括没有客户的订单 5、复合条件连接查询 在customers表和orders表中，使用INNER JOIN语法查询customers表中ID为10001的客户的订单信息 SELECT customers.c_id, orders.o_num  FROM customers INNER JOIN orders   ON customers.c_id = orders.c_id AND customers.c_id = 10001; 6、在fruits表和suppliers表之间，使用INNER JOIN语法进行内连接查询，并对查询结果排序 > SELECT suppliers.s_id, s_name,f_name, f_price   FROM fruits INNER JOIN suppliers  ON fruits.s_id = suppliers.s_id 7、下面定义两个表tb1和tb2： CREATE table tbl1 ( num1 INT NOT NULL); CREATE table tbl2 ( num2 INT NOT NULL); 分别向两个表中插入数据： I INSERT INTO tbl1 values(1); INSERT INTO tbl1 values(5); INSERT INTO tbl1 values(13); INSERT INTO tbl1 values(27); INSERT INTO tbl2 values(6); INSERT INTO tbl2 values(14); INSERT INTO tbl2 values(11); INSERT INTO tbl2 values(20); 8、返回tbl2表的所有num2列，然后将tbl1中的num1的值与之进行比较，只要大于num2的任何1个值，即为符合查询条件的结果。 9、返回tbl1表中比tbl2表num2 列所有值都大的值 10、在orderitems表中查询f_id为c0的订单号，并根据订单号查询具有订单号的客户c_id 11、在suppliers表中查询s_city等于“Tianjin”的供应商s_id，然后在fruits表中查询所有该供应商提供的水果的种类 12、查询所有价格小于9的水果的信息，查询s_id等于101和103所有的水果的信息，使用UNION连接查询结果 13、查询所有价格小于9的水果的信息，查询s_id等于101和103的所有水果的信息，使用UNION ALL连接查询结果 create table student( sno varchar2(10) primary key, sname varchar2(20), sage number(2), ssex varchar2(5) ); create table teacher( tno varchar2(10) primary key, tname varchar2(20) ); create table course( cno varchar2(10), cname varchar2(20), tno varchar2(20), constraint pk_course primary key (cno,tno) ); create table sc( sno varchar2(10), cno varchar2(10), score number(4,2), constraint pk_sc primary key (sno,cno) ); /*******初始化学生表的数据******/ insert into student values ('s001','张三',23,'男'); insert into student values ('s002','李四',23,'男'); insert into student values ('s003','吴鹏',25,'男'); insert into student values ('s004','琴沁',20,'女'); insert into student values ('s005','王丽',20,'女'); insert into student values ('s006','李波',21,'男'); insert into student values ('s007','刘玉',21,'男'); insert into student values ('s008','萧蓉',21,'女'); insert into student values ('s009','陈萧晓',23,'女'); insert into student values ('s010','陈美',22,'女'); commit; /******************初始化教师表***********************/ insert into teacher values ('t001', '刘阳'); insert into teacher values ('t002', '谌燕'); insert into teacher values ('t003', '胡明星'); commit; /***************初始化课程表****************************/ insert into course values ('c001','J2SE','t002'); insert into course values ('c002','Java Web','t002'); insert into course values ('c003','SSH','t001'); insert into course values ('c004','Oracle','t001'); insert into course values ('c005','SQL SERVER 2005','t003'); insert into course values ('c006','C#','t003'); insert into course values ('c007','JavaScript','t002'); insert into course values ('c008','DIV+CSS','t001'); insert into course values ('c009','PHP','t003'); insert into course values ('c010','EJB3.0','t002'); commit; /***************初始化成绩表***********************/ insert into sc values ('s001','c001',78.9); insert into sc values ('s002','c001',80.9); insert into sc values ('s003','c001',81.9); insert into sc values ('s004','c001',60.9); insert into sc values ('s001','c002',82.9); insert into sc values ('s002','c002',72.9); insert into sc values ('s003','c002',81.9); insert into sc values ('s001','c003','59'); commit; 14、查询“c001”课程比“c002”课程成绩高的所有学生的学号； 15、查询平均成绩大于60 分的同学的学号和平均成绩； 16、查询所有同学的学号、姓名、选课数、总成绩； 17、查询姓“刘”的老师的个数 18、查询没学过“谌燕”老师课的同学的学号、姓名；

代码如下：

1. select fruits.s_id,suppliers.s_name,fruits.f_name,fruits.f_price from fruits,suppliers where fruits.s_id=suppliers.s_id; 2. SELECT suppliers.s_id, s_name,f_name, f_price   FROM fruits INNER JOIN suppliers ON fruits.s_id=suppliers.s_id; 3. select customers.c_id from customers left outer join orders on (customers.c_id=orders.c_id); 4. select orders.o_num from customers right outer join orders on (customers.c_id=orders.c_id); 5. SELECT customers.c_id, orders.o_num   FROM customers INNER JOIN orders ON customers.c_id = orders.c_id AND customers.c_id = 10001; 6. SELECT suppliers.s_id, s_name,f_name, f_price    FROM fruits INNER JOIN suppliers ON fruits.s_id = suppliers.s_id; 7. CREATE table tbl1 ( num1 INT NOT NULL); CREATE table tbl2 ( num2 INT NOT NULL); INSERT INTO tbl1 values(1); INSERT INTO tbl1 values(5); INSERT INTO tbl1 values(13); INSERT INTO tbl1 values(27); INSERT INTO tbl2 values(6); INSERT INTO tbl2 values(14); INSERT INTO tbl2 values(11); INSERT INTO tbl2 values(20); 8. select num1 from tbl1 where num1>any( select num2 from tbl2 ); 9. select num1 from tbl1 where num1>all( select num2 from tbl2 ); 10. select c_id from orders where o_num in ( select o_num from orderitems where f_id='c0' ); 11. select f_id from fruits where s_id in ( select s_id from suppliers where s_city='Tianjin' or s_city='tianjin' ); 12. select * from fruits where f_price<9 union select * from fruits where s_id=101 and s_id=103; 13. select * from fruits where f_price<9 union all select * from fruits where s_id=101 and s_id=103; 14. select a.sno from sc a,sc b where a.sno=b.sno and a.cno='c001' and b.cno='c002' and a.score>b.score; 15. select sno,avg(score) from sc group by sno having avg(score)>60; 16. select a.sno,b.sname,count(a.cno),sum(a.score) from student b left outer join sc a on a.sno=b.sno   group by a.sno,b.sname;       17. select count(tname) from teacher where tname like '刘%'; 18. select sno,sname from student where sno in ( select sno from sc where cno in ( select cno from course where tno in ( select tno from teacher where tname!='谌燕' ) ) );