Database Management Systems Lab
Name : Vedant Raju Bhalerao
Batch & Roll.no : D4-63
PRACTICAL NO.3
Create table using DDL Script
Products (product_id Integer, product_type_id Integer, Name Varchar2(30), Price Integer)
Note: product_id is Primary Key
Tasks-01:
1. Count the number of products
2. Count the number of products and sum of price of products
3. Count the number of products_type_id
4. Count the number of distinct products_type_id
5. Calculate the average price of the product
6. Calculate the average price of the distinct product
7. Calculate maximum and minimum price of the product
8. Find the count of number of ROWID
9. Find maximum and minimum product name
10. Calculate standard deviation of price
11. Calculate variance of price
12. Calculate average price group by product_type_id
13. Calculate Variance on price group by product_type_id
14. Calculate Variance on price group by product_type_id order by Variance
15. Calculate average price group by product_type_id and having average price greater
than 1500.
16. Calculate average price of the products whose price is less that Rs.2000 and group
by product_type_id and having average price greater than 1500.
SQL> create view sailor1 as (select * from sailor where sid in(22,29,31,32,58,95));
View created.
SQL> create view sailor2 as (select * from sailor where sid in(31,32,64,71,74));
View created.
SQL> create view sailor3 as (select * from sailor where sid in(22,32,85,74));
View created.
SQL> create view reserve1 as (select * from sailor where sid in(22,64));
View created.
SQL> create view reserve2 as (select * from sailor where sid in(31,74));
View created.
SQL> select * from sailor1;
SID SNAME RATING AGE
---------- ------------------------------ ---------- ----------
22 DUSTIN 7 45
29 BRUTUS 1 33
31 LUBBER 8 55.5
32 ANDY 8 25.5
58 RUSTY 10 35
95 BOB 3 63.5
6 rows selected.
SQL> select * from sailor2;
SID SNAME RATING AGE
---------- ------------------------------ ---------- ----------
31 LUBBER 8 55.5
32 ANDY 8 25.5
64 HORATIO 7 35
71 ZORBA 10 16
74 HORATIO 9 35
SQL> select * from sailor3;
SID SNAME RATING AGE
---------- ------------------------------ ---------- ----------
22 DUSTIN 7 45
32 ANDY 8 25.5
74 HORATIO 9 35
85 ART 3 25.5
SQL> select * from reserve1;
SID SNAME RATING AGE
---------- ------------------------------ ---------- ----------
22 DUSTIN 7 45
64 HORATIO 7 35
SQL> select * from reserve2;
SID SNAME RATING AGE
---------- ------------------------------ ---------- ----------
31 LUBBER 8 55.5
74 HORATIO 9 35
SQL> select * from sailor inner join reserve on sailor.sid = reserve.sid;
SID SNAME RATING AGE SID
---------- ------------------------------ ---------- ---------- ----------
BID RDAY
---------- ---------
22 DUSTIN 7 45 22
101 10-OCT-98
22 DUSTIN 7 45 22
102 10-OCT-98
22 DUSTIN 7 45 22
103 08-OCT-98
SID SNAME RATING AGE SID
---------- ------------------------------ ---------- ---------- ----------
BID RDAY
---------- ---------
22 DUSTIN 7 45 22
104 07-OCT-98
31 LUBBER 8 55.5 31
102 10-NOV-98
31 LUBBER 8 55.5 31
103 06-NOV-98
SID SNAME RATING AGE SID
---------- ------------------------------ ---------- ---------- ----------
BID RDAY
---------- ---------
31 LUBBER 8 55.5 31
104 12-NOV-98
64 HORATIO 7 35 64
101 05-SEP-98
64 HORATIO 7 35 64
102 08-SEP-98
SID SNAME RATING AGE SID
---------- ------------------------------ ---------- ---------- ----------
BID RDAY
---------- ---------
74 HORATIO 9 35 74
103 08-SEP-98
10 rows selected.
SQL> COLUMN SID FOR 999
SQL> COLUMN BID FOR 9999
SQL> COLUMN RDAY FOR A10
SQL> COLUMN RATING FOR 999999
SQL> COLUMN SNAME FOR A12
SQL> COLUMN BNAME FOR A12
SQL> COLUMN COLOR FOR A8
SQL> COLUMN AGE FOR 99.9
SQL> SET FEEDBACK 1
SQL> SET PAGESIZE 30
SQL> select * from sailor inner join reserve on sailor.sid = reserve.sid;
SID SNAME RATING AGE SID BID RDAY
---- ------------ ------- ----- ---- ----- ----------
22 DUSTIN 7 45.0 22 101 10-OCT-98
22 DUSTIN 7 45.0 22 102 10-OCT-98
22 DUSTIN 7 45.0 22 103 08-OCT-98
22 DUSTIN 7 45.0 22 104 07-OCT-98
31 LUBBER 8 55.5 31 102 10-NOV-98
31 LUBBER 8 55.5 31 103 06-NOV-98
31 LUBBER 8 55.5 31 104 12-NOV-98
64 HORATIO 7 35.0 64 101 05-SEP-98
64 HORATIO 7 35.0 64 102 08-SEP-98
74 HORATIO 9 35.0 74 103 08-SEP-98
10 rows selected.
SQL>
SQL> set feedback 0
SQL> select * from sailor inner join reserve on sailor.sid = reserve.sid;
SID SNAME RATING AGE SID BID RDAY
---- ------------ ------- ----- ---- ----- ----------
22 DUSTIN 7 45.0 22 101 10-OCT-98
22 DUSTIN 7 45.0 22 102 10-OCT-98
22 DUSTIN 7 45.0 22 103 08-OCT-98
22 DUSTIN 7 45.0 22 104 07-OCT-98
31 LUBBER 8 55.5 31 102 10-NOV-98
31 LUBBER 8 55.5 31 103 06-NOV-98
31 LUBBER 8 55.5 31 104 12-NOV-98
64 HORATIO 7 35.0 64 101 05-SEP-98
64 HORATIO 7 35.0 64 102 08-SEP-98
74 HORATIO 9 35.0 74 103 08-SEP-98
SQL> SET FEEDBACK 1
SQL> select * from sailor inner join reserve on sailor.sid = reserve.sid;
SID SNAME RATING AGE SID BID RDAY
---- ------------ ------- ----- ---- ----- ----------
22 DUSTIN 7 45.0 22 101 10-OCT-98
22 DUSTIN 7 45.0 22 102 10-OCT-98
22 DUSTIN 7 45.0 22 103 08-OCT-98
22 DUSTIN 7 45.0 22 104 07-OCT-98
31 LUBBER 8 55.5 31 102 10-NOV-98
31 LUBBER 8 55.5 31 103 06-NOV-98
31 LUBBER 8 55.5 31 104 12-NOV-98
64 HORATIO 7 35.0 64 101 05-SEP-98
64 HORATIO 7 35.0 64 102 08-SEP-98
74 HORATIO 9 35.0 74 103 08-SEP-98
10 rows selected.
SQL> select sailor.sid,sailor.sname,reserve.bid,reserve.rday from sailor inner joinn reserve where
sailor.sid=reserve.sid;
select sailor.sid,sailor.sname,reserve.bid,reserve.rday from sailor inner joinn reserve where
sailor.sid=reserve.sid
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> select sailor.sid,sailor.sname,reserve.bid,reserve.rday from sailor inner join reserve where
sailor.sid=reserve.sid;
select sailor.sid,sailor.sname,reserve.bid,reserve.rday from sailor inner join reserve where
sailor.sid=reserve.sid
ERROR at line 1:
ORA-00905: missing keyword
SQL> select sailor.sid,sailor.sname,reserve.bid,reserve.rday from sailor inner join reserve on
sailor.sid=reserve.sid;
SID SNAME BID RDAY
---- ------------ ----- ----------
22 DUSTIN 101 10-OCT-98
22 DUSTIN 102 10-OCT-98
22 DUSTIN 103 08-OCT-98
22 DUSTIN 104 07-OCT-98
31 LUBBER 102 10-NOV-98
31 LUBBER 103 06-NOV-98
31 LUBBER 104 12-NOV-98
64 HORATIO 101 05-SEP-98
64 HORATIO 102 08-SEP-98
74 HORATIO 103 08-SEP-98
10 rows selected.
SQL> select sailor.sid,sailor.sname,reserve.bid,reserve.rday from sailor,reserve where sid=74 and
to_char(rday,'MON')='NOV';
select sailor.sid,sailor.sname,reserve.bid,reserve.rday from sailor,reserve where sid=74 and
to_char(rday,'MON')='NOV'
ERROR at line 1:
ORA-00918: column ambiguously defined
SQL> select sailor.sid,sailor.sname,reserve.bid,reserve.rday from sailor,reserve where reserve.sid=74
and to_char(rday,'MON')='NOV';
no rows selected
SQL> select sailor.sid,sailor.sname,reserve.bid,reserve.rday from sailor,reserve where sailor.sid=74
and to_char(rday,'MON')='NOV';
SID SNAME BID RDAY
---- ------------ ----- ----------
74 HORATIO 102 10-NOV-98
74 HORATIO 103 06-NOV-98
74 HORATIO 104 12-NOV-98
3 rows selected.
SQL> select * from sailor,reserve,boat where boat.bid<>101 and to_char(rday,'MON')='AUG';
no rows selected
SQL> select * from reserve,boat where boat.bid<>101 and to_char(rday,'MON')='AUG';
no rows selected
SQL> select * from reserve,boat where boat.bid<>101 and to_char(rday,'MON')='OCT';
SID BID RDAY BID BNAME COLOR
---- ----- ---------- ----- ------------ --------
22 101 10-OCT-98 102 INTERLAKE RED
22 102 10-OCT-98 102 INTERLAKE RED
22 103 08-OCT-98 102 INTERLAKE RED
22 104 07-OCT-98 102 INTERLAKE RED
22 101 10-OCT-98 103 CLIPPER GREEN
22 102 10-OCT-98 103 CLIPPER GREEN
22 103 08-OCT-98 103 CLIPPER GREEN
22 104 07-OCT-98 103 CLIPPER GREEN
22 101 10-OCT-98 104 MARINE RED
22 102 10-OCT-98 104 MARINE RED
22 103 08-OCT-98 104 MARINE RED
22 104 07-OCT-98 104 MARINE RED
12 rows selected.
SQL> select * from reserve JOIN boat where boat.bid<>101 and to_char(rday,'MON')='OCT';
select * from reserve JOIN boat where boat.bid<>101 and to_char(rday,'MON')='OCT'
ERROR at line 1:
ORA-00905: missing keyword
SQL> select * from reserve join boat on boat.bid<>101 and to_char(rday,'MON')='OCT';
SID BID RDAY BID BNAME COLOR
---- ----- ---------- ----- ------------ --------
22 101 10-OCT-98 102 INTERLAKE RED
22 102 10-OCT-98 102 INTERLAKE RED
22 103 08-OCT-98 102 INTERLAKE RED
22 104 07-OCT-98 102 INTERLAKE RED
22 101 10-OCT-98 103 CLIPPER GREEN
22 102 10-OCT-98 103 CLIPPER GREEN
22 103 08-OCT-98 103 CLIPPER GREEN
22 104 07-OCT-98 103 CLIPPER GREEN
22 101 10-OCT-98 104 MARINE RED
22 102 10-OCT-98 104 MARINE RED
22 103 08-OCT-98 104 MARINE RED
22 104 07-OCT-98 104 MARINE RED
12 rows selected.
SQL> select sailor1.sid,sailor2.sid from sailor1 left outer join sailor2;
select sailor1.sid,sailor2.sid from sailor1 left outer join sailor2
ERROR at line 1:
ORA-00905: missing keyword
SQL> select * from sailor1 left outer join sailor2 order by sailor1.sid = sailor2.sid;
select * from sailor1 left outer join sailor2 order by sailor1.sid = sailor2.sid
ERROR at line 1:
ORA-00905: missing keyword
SQL> select * from sailor1 left outer join sailor2 on sailor1.sid = sailor2.sid;
SID SNAME RATING AGE SID SNAME RATING AGE
---- ------------ ------- ----- ---- ------------ ------- -----
31 LUBBER 8 55.5 31 LUBBER 8 55.5
32 ANDY 8 25.5 32 ANDY 8 25.5
29 BRUTUS 1 33.0
22 DUSTIN 7 45.0
95 BOB 3 63.5
58 RUSTY 10 35.0
6 rows selected.
SQL> select * from sailor1 left outer join sailor2 on sailor1.sid = sailor2.sid order by sailor1.sid;
SID SNAME RATING AGE SID SNAME RATING AGE
---- ------------ ------- ----- ---- ------------ ------- -----
22 DUSTIN 7 45.0
29 BRUTUS 1 33.0
31 LUBBER 8 55.5 31 LUBBER 8 55.5
32 ANDY 8 25.5 32 ANDY 8 25.5
58 RUSTY 10 35.0
95 BOB 3 63.5
6 rows selected.
SQL> select * from sailor1 right outer join sailor2 on sailor1.sid = sailor2.sid order by sailor1.sid;
SID SNAME RATING AGE SID SNAME RATING AGE
---- ------------ ------- ----- ---- ------------ ------- -----
31 LUBBER 8 55.5 31 LUBBER 8 55.5
32 ANDY 8 25.5 32 ANDY 8 25.5
74 HORATIO 9 35.0
64 HORATIO 7 35.0
71 ZORBA 10 16.0
5 rows selected.
SQL> select * from sailor1 full outer join sailor2 on sailor1.sid = sailor2.sid order by sailor1.sid;
SID SNAME RATING AGE SID SNAME RATING AGE
---- ------------ ------- ----- ---- ------------ ------- -----
22 DUSTIN 7 45.0
29 BRUTUS 1 33.0
31 LUBBER 8 55.5 31 LUBBER 8 55.5
32 ANDY 8 25.5 32 ANDY 8 25.5
58 RUSTY 10 35.0
95 BOB 3 63.5
71 ZORBA 10 16.0
74 HORATIO 9 35.0
64 HORATIO 7 35.0
9 rows selected.
SQL> select * from sailor1 full outer join sailor2 on sailor1.sid = sailor2.sid order by
sailor1.sid,sailor2.sid;
SID SNAME RATING AGE SID SNAME RATING AGE
---- ------------ ------- ----- ---- ------------ ------- -----
22 DUSTIN 7 45.0
29 BRUTUS 1 33.0
31 LUBBER 8 55.5 31 LUBBER 8 55.5
32 ANDY 8 25.5 32 ANDY 8 25.5
58 RUSTY 10 35.0
95 BOB 3 63.5
64 HORATIO 7 35.0
71 ZORBA 10 16.0
74 HORATIO 9 35.0
9 rows selected.
SQL> select * from sailor natural join reserve;
SID SNAME RATING AGE BID RDAY
---- ------------ ------- ----- ----- ----------
22 DUSTIN 7 45.0 101 10-OCT-98
22 DUSTIN 7 45.0 102 10-OCT-98
22 DUSTIN 7 45.0 103 08-OCT-98
22 DUSTIN 7 45.0 104 07-OCT-98
31 LUBBER 8 55.5 102 10-NOV-98
31 LUBBER 8 55.5 103 06-NOV-98
31 LUBBER 8 55.5 104 12-NOV-98
64 HORATIO 7 35.0 101 05-SEP-98
64 HORATIO 7 35.0 102 08-SEP-98
74 HORATIO 9 35.0 103 08-SEP-98
10 rows selected.
SQL> select * from sailor2 cross join sailor3;
SID SNAME RATING AGE SID SNAME RATING AGE
---- ------------ ------- ----- ---- ------------ ------- -----
31 LUBBER 8 55.5 22 DUSTIN 7 45.0
32 ANDY 8 25.5 22 DUSTIN 7 45.0
64 HORATIO 7 35.0 22 DUSTIN 7 45.0
71 ZORBA 10 16.0 22 DUSTIN 7 45.0
74 HORATIO 9 35.0 22 DUSTIN 7 45.0
31 LUBBER 8 55.5 32 ANDY 8 25.5
32 ANDY 8 25.5 32 ANDY 8 25.5
64 HORATIO 7 35.0 32 ANDY 8 25.5
71 ZORBA 10 16.0 32 ANDY 8 25.5
74 HORATIO 9 35.0 32 ANDY 8 25.5
31 LUBBER 8 55.5 74 HORATIO 9 35.0
32 ANDY 8 25.5 74 HORATIO 9 35.0
64 HORATIO 7 35.0 74 HORATIO 9 35.0
71 ZORBA 10 16.0 74 HORATIO 9 35.0
74 HORATIO 9 35.0 74 HORATIO 9 35.0
31 LUBBER 8 55.5 85 ART 3 25.5
32 ANDY 8 25.5 85 ART 3 25.5
64 HORATIO 7 35.0 85 ART 3 25.5
71 ZORBA 10 16.0 85 ART 3 25.5
74 HORATIO 9 35.0 85 ART 3 25.5
20 rows selected.
SQL> select * from sailor natural join reserve on bid=103;
select * from sailor natural join reserve on bid=103
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> select * from sailor natural join reserve where bid=103;
SID SNAME RATING AGE BID RDAY
---- ------------ ------- ----- ----- ----------
22 DUSTIN 7 45.0 103 08-OCT-98
31 LUBBER 8 55.5 103 06-NOV-98
74 HORATIO 9 35.0 103 08-SEP-98
3 rows selected.
SQL> select * from sailor inner join reserve where bid=103;
select * from sailor inner join reserve where bid=103
ERROR at line 1:
ORA-00905: missing keyword
SQL> select * from sailor inner join reserve on bid=103;
SID SNAME RATING AGE SID BID RDAY
---- ------------ ------- ----- ---- ----- ----------
22 DUSTIN 7 45.0 22 103 08-OCT-98
29 BRUTUS 1 33.0 22 103 08-OCT-98
31 LUBBER 8 55.5 22 103 08-OCT-98
32 ANDY 8 25.5 22 103 08-OCT-98
58 RUSTY 10 35.0 22 103 08-OCT-98
64 HORATIO 7 35.0 22 103 08-OCT-98
71 ZORBA 10 16.0 22 103 08-OCT-98
74 HORATIO 9 35.0 22 103 08-OCT-98
85 ART 3 25.5 22 103 08-OCT-98
95 BOB 3 63.5 22 103 08-OCT-98
22 DUSTIN 7 45.0 31 103 06-NOV-98
29 BRUTUS 1 33.0 31 103 06-NOV-98
31 LUBBER 8 55.5 31 103 06-NOV-98
32 ANDY 8 25.5 31 103 06-NOV-98
58 RUSTY 10 35.0 31 103 06-NOV-98
64 HORATIO 7 35.0 31 103 06-NOV-98
71 ZORBA 10 16.0 31 103 06-NOV-98
74 HORATIO 9 35.0 31 103 06-NOV-98
85 ART 3 25.5 31 103 06-NOV-98
95 BOB 3 63.5 31 103 06-NOV-98
22 DUSTIN 7 45.0 74 103 08-SEP-98
29 BRUTUS 1 33.0 74 103 08-SEP-98
31 LUBBER 8 55.5 74 103 08-SEP-98
32 ANDY 8 25.5 74 103 08-SEP-98
58 RUSTY 10 35.0 74 103 08-SEP-98
64 HORATIO 7 35.0 74 103 08-SEP-98
71 ZORBA 10 16.0 74 103 08-SEP-98
SID SNAME RATING AGE SID BID RDAY
---- ------------ ------- ----- ---- ----- ----------
74 HORATIO 9 35.0 74 103 08-SEP-98
85 ART 3 25.5 74 103 08-SEP-98
95 BOB 3 63.5 74 103 08-SEP-98
30 rows selected.
SQL> select sailor.sname from sailor natural join reserve on bid=103;
select sailor.sname from sailor natural join reserve on bid=103
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL>
SQL>
SQL>
SQL> select distinct sailor.sname from sailor natural join reserve natural join (select * from boat
where color='RED');
SNAME
------------
LUBBER
HORATIO
DUSTIN
3 rows selected.
SQL> select boat.color from boat natural join reserve natural join (select * from sailor where
sname='LUBBER');
COLOR
--------
RED
GREEN
RED
3 rows selected.
SQL> select DISTINCT boat.color from boat natural join reserve natural join (select * from sailor
where sname='LUBBER');
COLOR
--------
RED
GREEN
2 rows selected.
SQL> select DISTINCT boat.color from boat natural join reserve natural join (select * from sailor
where sname='DUSTIN');
COLOR
--------
RED
GREEN
BLUE
3 rows selected.
SQL> select * from sailor natural join reserve on sailor.sid=reserve.sid;
select * from sailor natural join reserve on sailor.sid=reserve.sid
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> select * from sailor natural join reserve where sailor.sid=reserve.sid;
select * from sailor natural join reserve where sailor.sid=reserve.sid
ERROR at line 1:
ORA-25155: column used in NATURAL join cannot have qualifier
SQL> select * from sailor join reserve on sailor.sid=reserve.sid;
SID SNAME RATING AGE SID BID RDAY
---- ------------ ------- ----- ---- ----- ----------
22 DUSTIN 7 45.0 22 101 10-OCT-98
22 DUSTIN 7 45.0 22 102 10-OCT-98
22 DUSTIN 7 45.0 22 103 08-OCT-98
22 DUSTIN 7 45.0 22 104 07-OCT-98
31 LUBBER 8 55.5 31 102 10-NOV-98
31 LUBBER 8 55.5 31 103 06-NOV-98
31 LUBBER 8 55.5 31 104 12-NOV-98
64 HORATIO 7 35.0 64 101 05-SEP-98
64 HORATIO 7 35.0 64 102 08-SEP-98
74 HORATIO 9 35.0 74 103 08-SEP-98
10 rows selected.
SQL> select distinct sailor.sname from sailor join reserve on sailor.sid=reserve.sid;
SNAME
------------
LUBBER
HORATIO
DUSTIN
3 rows selected.
SQL> select distinct sailor.sname from sailornatural join reserve natural join (select * from boat
where color='RED' or color='GREEN');
select distinct sailor.sname from sailornatural join reserve natural join (select * from boat where
color='RED' or color='GREEN')
ERROR at line 1:
ORA-00905: missing keyword
SQL> select distinct sailor.sname from sailor natural join reserve natural join (select * from boat
where color='RED' or color='GREEN');
SNAME
------------
LUBBER
HORATIO
DUSTIN
3 rows selected.
SQL> select sailor.sid from sailor join reserve where sailor.rating>=8 or reserve.bid=103;
select sailor.sid from sailor join reserve where sailor.rating>=8 or reserve.bid=103
ERROR at line 1:
ORA-00905: missing keyword
SQL> select sailor.sid from sailor join reserve on sailor.rating>=8 or reserve.bid=103;
SID
----
22
22
22
29
29
29
31
31
31
31
31
31
31
31
31
31
32
32
32
32
32
32
32
32
32
32
58
SID
----
58
58
58
58
58
58
58
58
58
64
64
64
71
71
71
71
71
71
71
71
71
71
74
74
74
74
74
SID
----
74
74
74
74
74
85
85
85
95
95
95
65 rows selected.
SQL> select sailor.sid from sailor natural join reserve where sailor.rating>=8 or reserve.bid=103;
select sailor.sid from sailor natural join reserve where sailor.rating>=8 or reserve.bid=103
ERROR at line 1:
ORA-25155: column used in NATURAL join cannot have qualifier
SQL> select sailor.sid from sailor natural join reserve on sailor.rating>=8 or reserve.bid=103;
select sailor.sid from sailor natural join reserve on sailor.rating>=8 or reserve.bid=103
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> desc sailor
Name Null? Type
----------------------------------------- -------- ----------------------------
SID NOT NULL NUMBER(38)
SNAME NOT NULL VARCHAR2(30)
RATING NUMBER(38)
AGE FLOAT(126)
SQL> desc boat
Name Null? Type
----------------------------------------- -------- ----------------------------
BID NOT NULL NUMBER(38)
BNAME VARCHAR2(30)
COLOR VARCHAR2(10)
SQL> desc reserve
Name Null? Type
----------------------------------------- -------- ----------------------------
SID NOT NULL NUMBER(2)
BID NOT NULL NUMBER(3)
RDAY NOT NULL DATE
SQL> select distinct sailor.sid from sailor inner join reserve on sailor.rating>=8 or reserve.bid=103;
SID
----
22
29
31
95
32
85
74
71
58
64
10 rows selected.
SQL> select distinct sailor.sid from sailor inner join reserve on sailor.rating>=8 or reserve.bid=103
order by sailor.sid;
SID
----
22
29
31
32
58
64
71
74
85
95
10 rows selected.
SQL> select distinct sailor.sid from sailor join reserve on sailor.rating>=8 or reserve.bid=103 order
by sailor.sid;
SID
----
22
29
31
32
58
64
71
74
85
95
10 rows selected.
SQL> select distinct sailor.sid from sailor s , reserve r where r.sid = s.sid and s.rating>=8 or
r.bid=103;
select distinct sailor.sid from sailor s , reserve r where r.sid = s.sid and s.rating>=8 or r.bid=103
ERROR at line 1:
ORA-00904: "SAILOR"."SID": invalid identifier
SQL> select distinct sailor.sid from sailor s , reserve r on r.sid = s.sid and s.rating>=8 or r.bid=103;
select distinct sailor.sid from sailor s , reserve r on r.sid = s.sid and s.rating>=8 or r.bid=103
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> select distinct sailor.sid from sailor s , reserve r on r.sid = s.sid where s.rating>=8 or r.bid=103;
select distinct sailor.sid from sailor s , reserve r on r.sid = s.sid where s.rating>=8 or r.bid=103
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> select distinct sailor.sid from sailor s , reserve r on r.sid = s.sid where s.rating>=8 or r.bid=103;
select distinct sailor.sid from sailor s , reserve r on r.sid = s.sid where s.rating>=8 or r.bid=103
ERROR at line 1:
ORA-00933: SQL command not properly ended
SQL> select sailor
SQL> select sailor.sname from sailor natural join reserve natural join (select * from boat where
bname like '%U%') ORDER BY SAILOR.SID;
select sailor.sname from sailor natural join reserve natural join (select * from boat where bname like
'%U%') ORDER BY SAILOR.SID
ERROR at line 1:
ORA-25155: column used in NATURAL join cannot have qualifier
SQL> select sailor.sname from sailor natural join reserve natural join (select * from boat where
bname like '%U%');
no rows selected
SQL> select sailor.sname from sailor natural join reserve natural join (select * from boat where
bname like '%L%');
SNAME
------------
DUSTIN
HORATIO
DUSTIN
LUBBER
HORATIO
DUSTIN
LUBBER
HORATIO
8 rows selected.
SQL> select boat.bname from sailor natural join reserve natural join (select * from boat where bname
like '%L%');
select boat.bname from sailor natural join reserve natural join (select * from boat where bname like
'%L%')
ERROR at line 1:
ORA-00904: "BOAT"."BNAME": invalid identifier
SQL> select sailor.sname from sailor natural join reserve natural join (select * from boat where
bname like '%L%');
SNAME
------------
DUSTIN
HORATIO
DUSTIN
LUBBER
HORATIO
DUSTIN
LUBBER
HORATIO
8 rows selected.
SQL> select distinct sailor.sname from sailor natural join reserve natural join (select * from boat
where bname like '%L%');
SNAME
------------
LUBBER
HORATIO
DUSTIN
3 rows selected.
SQL> select distinct boat.bname from (select * from boat where bname like '%L%') natural join
sailor natural join reserve;
select distinct boat.bname from (select * from boat where bname like '%L%') natural join sailor
natural join reserve
ERROR at line 1:
ORA-00904: "BOAT"."BNAME": invalid identifier
SQL> select * from
SQL> selct sid from sailor where rating>(select rating from where sname='BOB');
SP2-0734: unknown command beginning "selct sid ..." - rest of line ignored.
SQL> select sid from sailor where rating>(select rating from where sname='BOB');
select sid from sailor where rating>(select rating from where sname='BOB')
ERROR at line 1:
ORA-00903: invalid table name
SQL> select sid from sailor where rating>(select rating from sailor where sname='BOB');
SID
----
22
31
32
58
64
71
74
7 rows selected.
SQL> select boat.bid , avg(sailor.age) from sailor ,boat ,reserve where reserve.sid=sailor.sid and
reserve.bid = boat.bid and sailor.age>=40 group by(sailor.age) having count(sailor.sid)>=5;
select boat.bid , avg(sailor.age) from sailor ,boat ,reserve where reserve.sid=sailor.sid and reserve.bid
= boat.bid and sailor.age>=40 group by(sailor.age) having count(sailor.sid)>=5
ERROR at line 1:
ORA-00979: not a GROUP BY expression
SQL> select boat.bid , avg(sailor.age) from sailor s,boat b ,reserve r where r.sid=s.sid and r.bid =
b.bid and s.age>=40 group by(s.age) having count(s.sid)>=5;
select boat.bid , avg(sailor.age) from sailor s,boat b ,reserve r where r.sid=s.sid and r.bid = b.bid and
s.age>=40 group by(s.age) having count(s.sid)>=5
ERROR at line 1:
ORA-00904: "SAILOR"."AGE": invalid identifier
SQL> select b.bid , avg(s.age) from sailor s,boat b ,reserve r where r.sid=s.sid and r.bid = b.bid and
s.age>=40 group by(s.age) having count(s.sid)>=5;
select b.bid , avg(s.age) from sailor s,boat b ,reserve r where r.sid=s.sid and r.bid = b.bid and
s.age>=40 group by(s.age) having count(s.sid)>=5
ERROR at line 1:
ORA-00979: not a GROUP BY expression
SQL> select b.bid , avg(s.age) from sailor s,boat b ,reserve r where r.sid=s.sid and r.bid = b.bid and
s.age>=40 group by (s.age) having count(s.sid)>=5;
select b.bid , avg(s.age) from sailor s,boat b ,reserve r where r.sid=s.sid and r.bid = b.bid and
s.age>=40 group by (s.age) having count(s.sid)>=5
ERROR at line 1:
ORA-00979: not a GROUP BY expression
SQL> spool off
SQL> select b.bid , avg(s.age) from sailor s,boat b ,reserve r where r.sid=s.sid and r.bid = b.bid and
s.age>=40 group by (b.bid) having count(s.sid)>=1;
BID AVG(S.AGE)
----- ----------
102 50.25
101 45
104 50.25
103 50.25
4 rows selected.
