Write a query to display the minimum price of tablets which status is abundant. Write a query to display the total price of tablets which their amount is more than 50 and their supplier address is Amman. Write a query to display the cities and number of suppliers located in them. Determine the number of specialties for teachers without listing them. Write a query to display the supplier name and the highest tablet price that has been supplied by that supplier. Exclude any group where the minimum price is less than $15. Write a query to display the supplier ID and the average amount of tablets belongs to this supplier, exclude any tablet which price is less than ‘tablet4’.
TABLES:
drop table tech_supplier cascade constraints;
drop table teacher cascade constraints;
drop table student cascade constraints;
drop table tablet cascade constraints;
drop table tablet_amountstatus cascade constraints;
create table tech_supplier(supplier_id number(5) primary key,supplier_name varchar2(15),address varchar2(15));
insert into tech_supplier values(201,'ali','Amman');
insert into tech_supplier values(202,'omar','Zarqa');
insert into tech_supplier values(203,'hend','Irbid');
insert into tech_supplier values(204,'bana','Amman');
insert into tech_supplier values(205,'zaid',null);
-----------------------------------------------------------------------------------------------------------------------
create table teacher(id number(5) primary key,name varchar2(15),specialty varchar2(25));
insert into teacher values(121,'said','physics');
insert into teacher values(122,'reem','math');
insert into teacher values(123,'ahmad','chymistry');
insert into teacher values(124,'lina','math');
insert into teacher values(125,'saleem','physics');
-----------------------------------------------------------------------------------------------------------------------
create table student(student_id number(5) primary key,first_name varchar2(13),last_name varchar2(13),
mobile varchar2(10),bdate date, t_id number(5) references teacher(id));
insert into student values(1,'asad','ahmad','0713333331','1-feb-10',121);
insert into student values(2,'areej','wajdi','0714444442','3-oct-12',122);
insert into student values(3,'barra','saleem','0715566878','6-jan-10',122);
insert into student values(4,'fadi','fadi','0716671888','7-jan-09',122);
insert into student values(5,'fars','moneer','0711111119','11-jan-11',123);
insert into student values(6,'lama','hani','0712222225','3-jun-11',124);
insert into student values(7,'wael','wael','0713333912','7-dec-10',122);
----------------------------------------------------------------------------------------------------------------------
create table tablet(d_name varchar2(10) primary key,supplier_id number(5) references tech_supplier(supplier_id),
amount number(3), price number(3));
insert into tablet values('tablet1',201,50,300);
insert into tablet values('tablet2',201,60,150);
insert into tablet values('tablet3',203,70,100);
insert into tablet values('tablet4',204,80,200);
insert into tablet values('tablet5',204,10,250);
insert into tablet values('tablet6',204,70,300);
-------------------------------------------------------------------------------------------------------------------
create table tablet_AmountStatus(status varchar2(30) primary key,lower_amount number(3) ,
upper_amount number(3));
insert into tablet_AmountStatus values('not available',0,0);
insert into tablet_AmountStatus values('very few',1,10);
insert into tablet_AmountStatus values('available',11,40);
insert into tablet_AmountStatus values('abundant',41,150);
commit;
-------------------------------------------------------------------------
The tables are student, teacher, tablet, tablet_amountstatus, tech_supplier
-
Write a query to display the minimum price of tablets which status is abundant.
-
Write a query to display the total price of tablets which their amount is more than 50 and their supplier address is Amman.
-
Write a query to display the cities and number of suppliers located in them.
-
Determine the number of specialties for teachers without listing them.
-
Write a query to display the supplier name and the highest tablet price that has been supplied by that supplier. Exclude any group where the minimum price is less than $15.
-
Write a query to display the supplier ID and the average amount of tablets belongs to this supplier, exclude any tablet which price is less than ‘tablet4’.
Step by step
Solved in 2 steps with 6 images
