السلام عليكم ورحمة الله وبركاته

 

 

create table customers(

custumer_id Number(5) Primary Key,

custumer_name Varchar2(30),

customer_details Varchar2(30));

 

 

create table Employee(

Employee_id Number(5) primary Key,

Employee_Name Varchar2(30),

Phone Number(10));

 

 

 

 

 

create table Service_class(

Service_class_code Number(5) Primary Key,

Serivce_class_desc Varchar2(30));

 

 

   

 

 

create table Delivery_Status(

Delivery_status_code Number(5) Primary Key,

Delivery_status_desc  Varchar2(30));

 

 

 

create table Delivery_Location(

Location_code Number(5) Primary Key,

Location_name Varchar2(30),

Location_address Varchar2(30),

Site_details Varchar2(30));

 

 

 

 

 

 

create table Package_status(

Package_status_code Number(5) Primary Key,

Package_status_desc Varchar2(30));

 

 

 

 

 

 

create table Package(

Package_id Number(10) primary Key,

Package_details Varchar(30),

custumer_id Number(5),

Employee_id Number(5),

Package_status_code Number(5),

Foreign key (custumer_id) references customers,  

Foreign key (Employee_id) references Employee, 

Foreign key (Package_status_code) references Package_status);

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

create table package_Delivery_Time_Card(

Delivery_Stage_date date,

Other_details Varchar2(30),

Delivery_status_code Number(5),

Service_class_code Number(5),

Package_id Number(10),

Location_code Number(5),

Foreign key (Delivery_status_code) references Delivery_Status,  

Foreign key (Service_class_code) references Service_class, 

Foreign key (Package_id) references Package,

Foreign key (Location_code) references Delivery_Location);

 

 

 

INSERT INTO Customers VALUES ('1111','Ahmed','Aldreez');

INSERT INTO Customers VALUES ('1112','Ismaiil','Alainin');

INSERT INTO Customers VALUES ('1113','Ali','Murtafaa');

INSERT INTO Customers VALUES ('1114','Jawad','Alnahda');

 

 

 

INSERT INTO Employee VALUES ('0012','Jon','99554445');

INSERT INTO Employee VALUES ('0013','Davied','99111111');

INSERT INTO Employee VALUES ('0014','Khalid','99882288');

INSERT INTO Employee VALUES ('0015','Drwish','99000011');

 

 

 

 

 

INSERT INTO Service_class VALUES ('2511','Kit1-R2');

INSERT INTO Service_class VALUES ('2512','Kit1-R2');

INSERT INTO Service_class VALUES ('2513','Kit2-R1');

INSERT INTO Service_class VALUES ('2514','Kit2-R1');

 

 

 

 

 

INSERT INTO Delivery_Status VALUES ('10001','Delivered');

INSERT INTO Delivery_Status VALUES ('10002','Still');

INSERT INTO Delivery_Status VALUES ('10003','Still');

INSERT INTO Delivery_Status VALUES ('10004','Delivered');

 

 

 

 

 

  

INSERT INTO Package_status VALUES ('30001','Done-ToDelivery');

INSERT INTO Package_status VALUES ('30002','Still');

INSERT INTO Package_status VALUES ('30003','Done-ToDelivery');

INSERT INTO Package_status VALUES ('30004','Done-ToDelivery');

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

INSERT INTO Delivery_Location VALUES ('2020','Aldreez','IBRI','NearAldreez Masjid');

INSERT INTO Delivery_Location VALUES ('2010','Alainin','IBRI','Near The Fort');

INSERT INTO Delivery_Location VALUES ('2090','Murtafaa','IBRI','Near The Pharamasy');

INSERT INTO Delivery_Location VALUES ('2060','Alnahda','IBRI','Near PloiceStation 5');

 

 

 

 

 

 

 

INSERT INTO Package VALUES ('5515','Burger','1111','0012','30001');

INSERT INTO Package VALUES ('5514','Pizza','1112','0013','30002');

INSERT INTO Package VALUES ('5513','Pizza-chi','1113','0014','30003');

INSERT INTO Package VALUES ('5512','Kufta','1114','0015','30004');

 

 

 

 

 

INSERT INTO package_Delivery_Time_Card VALUES ('12-12-2015','Ahmed-aldreez'  ,'10001','2511','5515','2020');

INSERT INTO package_Delivery_Time_Card VALUES ('13/2/2015','Ismail-alainain','10002','2512','5514','2010');

INSERT INTO package_Delivery_Time_Card VALUES ('14/2/2015','Ali-almurtfa'   ,'10003','2513','5513','2090');

INSERT INTO package_Delivery_Time_Card VALUES ('15/2/2015','Jawad-alnahda'  ,'10004','2514','5512','2060');

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

المطلوب مني :
 

10. a query using a subquery

11. a query using a correlated subquery

12. a query involving COUNT(*)

13. a query involving another aggregate function


 

 

 

 

 

 

 select custumer_id, from Employee where custumer_id IN( select custumer_name, customer_details from customers where custumer_id='1112'); //missing expression error

 

 

 

select Package_details as package_Info from package where custumer_id in (select custumer_name from customers where custumer_id= '1112');  //no row selected

 

 

 

select Serivce_class_desc, avg(count(Serivce_class_code)) from Service_class group by Serivce_class_desc; //invild idintifr error

الاكواد مع الأخطاء

مرفق مع ال ERD

al salam alaykom.

 

first query you have comma after customer id and you can not say customer_id in customer_name,customer_details it suould be in customer_id.it should be like this:

 select custumer_id from Employee where custumer_id IN( select custumer_id from customers where custumer_id='1112');

 

 

second query give no record because there is no matching data because you said that customer_id in (customer_name)  and this is wrong, it should be like this:

select Package_details as package_Info from package where custumer_id in (select custumer_id from customers where custumer_id= '1112');

 

 

third query Serivce_class_code is not exist in your table  Service_class ,it should be like this:

select Serivce_class_desc, avg(count(Serivce_class_code)) from Service_class group by Serivce_class_desc;