实验要求：

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!='谌燕'
)
)
);