Create the following tables by identifying primary and foreign keys. Specify the not null property for mandatory keys.
Suppliers (supplier_no, sname, saddress, scity)
Computer_items (item_no, supplier_no,item_name,iquantity)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Create table Supplier
SQL> create table suppliers
(supplier_no number(6) primary key,
sname varchar2(20) not null,
saddress varchar2(30) not null,
scity varchar2(15) not null);
Table created.
Display table fields
SQL> desc suppliers;
Name Null? Type
----------------------------------------------------------------
SUPPLIER_NO NOT NULL NUMBER(6)
SNAME NOT NULL VARCHAR2(20)
SADDRESS NOT NULL VARCHAR2(30)
SCITY NOT NULL VARCHAR2(15)
Insert values into table
SQL> insert into suppliers
values(101,'microtech','kulur','mangalore');
1 row created.
SQL> select * from suppliers;
SUPPLIER_NO SNAME SADDRESS SCITY
------------------------------------------------------------------------
101 microtech kulur mangalore
102 cats kavoor mangalore
103 electrotech lalbag bangalore
104 onida bejai mangalore
105 samsung PVS bangalore
Create table computer_items
SQL> create table computer_items(
item_no number(6) primary key,
supplier_no number(6)references suppliers(supplier_no),
item_name varchar2(15) not null,
iquantity number(6) not null);
Table created.
Display table fields
SQL> desc computer_items;
Name Null? Type
-----------------------------------------------------
ITEM_NO NOT NULL NUMBER(6)
SUPPLIER_NO NOT NULL NUMBER(6)
ITEM_NAME NOT NULL VARCHAR2(15)
IQUANTITY NOT NULL NUMBER(6)
Insert values into table
SQL> insert into computer_items values(
501,101,'monitor',3);
1 row created.
SQL> select * from computer_items;
ITEM_NO SUPPLIER_NO ITEM_NAME IQUANTITY
------------------------------------------------------------------------
501 101 monitor 3
502 103 keyboard 5
503 105 mouse 7
504 102 pendrive 6
506 104 floppy_disk 8
505 103 hard disk 5
507 102 joystick 3
508 104 printer 9
509 105 scanner 6
510 104 ups 9
1. Query to list item and suppliers details:
SQL> select s.supplier_no,s.sname,s.saddress,s.scity,
c.item_name
from suppliers s,computer_items c
where s.supplier_no=c.supplier_no;
SUPPLIER_NO SNAME SADDRESS SCITY ITEM_NAME
--------------------------------------------------------------------------------
101 microtech kulur mangalore monitor
103 electrotech lalbag bangalore keyboard
105 samsung PVS bangalore mouse
102 cats kavoor mangalore pendrive
104 onida bejai mangalore floppy_disk
103 electrotech lalbag bangalore hard disk
102 cats kavoor mangalore joystick
104 onida bejai mangalore printer
105 samsung PVS bangalore scanner
104 onida bejai mangalore ups
2. Query to list the names of the supplier who are supplying keyboard:
SQL> select s.supplier_no,s.sname
from suppliers s,computer_items c
where s.supplier_no=c.supplier_no and
c.item_name='keyboard';
SUPPLIER_NO SNAME
--------------------------------
103 electrotech
3. Query to display the items supplied by ‘microtech’:
SQL> select c.item_no,c.item_name,c.iquantity
from suppliers s,computer_items c
where s.supplier_no=c.supplier_no and
s.sname='microtech';
ITEM_NO ITEM_NAME IQUANTITY
---------------------------------------------
501 monitor 3
4. Query to list the items supplied by the suppliers ‘cats’ and ‘electrotech’:
SQL> select c.item_no,c.item_name,c.iquantity,s.sname
from suppliers s,computer_items c
where s.supplier_no=c.supplier_no and
s.sname in('cats','electrotech');
ITEM_NO ITEM_NAME IQUANTITY SNAME
-------------------------------------------------------------------------
502 keyboard 5 electrotech
504 pendrive 6 cats
505 hard disk 5 electrotech
507 joystick 3 cats
No comments:
Post a Comment