ssd7exam1.txt

SQL语句初级

1.
(1) select A from R 

(2) select * from R where B = 13 

(3) select A , B from R , S  
where R.C = S.D 

2.
(1)select * from r 
where dept = 'Admin' and project <> 'Audit' 


(2)
select r.ename , r.dept
from r 
union 
select s.enmae , s.dept
from s 


(3)
select r.dept 
from r 
except 
select s.dept 
from s

4.
(1)
ALTER TABLE ORDERS 	
ADD CONSTRAINT RE_NOTNULL
set Received not null

(2)
ALTER TABLE ORDERS 
 ADD CONSTRAINT Ship_Great
 CHECK ((Shipped IS NOT NULL) AND (Shipped > Received))


5.
(1)
select Pno from PART 
where UnitPrice between 10 and 25  

(2)
select sum(Qty) 
from ORDERS o , INVOICE i 
where o.Ono = i.Ono and 
(o.Received between to_date('1998/01/01', 'YYYY/MM/DD') and to_date('1998/12/31', 'YYYY/MM/DD'))
group by i.Ono
order by sum(i.BilledPrice) asc  

(3)
select p.* 
from PART p 
where p.Pno in 
	(select Pno from INVOICE i , ORDERS o 
	 where i.Ono = o.Ono and o.Received < to_date('1998/01/01','YYYY/MM/DD') 
	 or o.Received > to_date('1998/12/31', 'YYYY/MM/DD')) 

6.
ALTER TABLE EMPLOYEE 
 ADD CONSTRAINT EMPLOYEE_FK
 FOREIGN KEY (dname) REFERENCES DEPARTMENT(name)