DATABASE SQL PLEASE DO NOT COPY AND PASTE FROM PREVIOUS ANSWERS 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; -------------------------------------------------------------------------------- QUESTIONS: I ONLY NEED THE ANSWERS OF Q5 Q6 AND Q7 The tables are student, teacher, tablet, tablet_amountstatus, tech_supplier Write a query to display the birth date of the youngest and the oldest students whose taeacher is ‘reem’. 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’.
PLEASE DO NOT COPY AND PASTE FROM PREVIOUS ANSWERS
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;
--------------------------------------------------------------------------------
QUESTIONS:
I ONLY NEED THE ANSWERS OF Q5 Q6 AND Q7
The tables are student, teacher, tablet, tablet_amountstatus, tech_supplier
-
Write a query to display the birth date of the youngest and the oldest students whose taeacher is ‘reem’.
-
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
