[مخالف - طلب حل : ]دعوة لك عن ظهر غيب || مشكلة فيٍ 3 أسئلة من الدكتور للغة SQL !

السلام عليكم !

كيف الحال أحبتي إن شاءلله بأحسن حال !

الدكتور طلب مني حل " واجب منزلي " وفي الحقيقة ما شرح لي الإسئلة إكتفى بقول من يرسله لي أكمل له درجة الإمتحان إلى 10 / 10 ! 


حليت أول 2 لكن الباقي 3 اسئلة ، ماقدرت عليهم لأن الدكتور المادة " النظرية " يغرد خارج السرب !  وفلسفه إلى أبعد نطاق نسأل الله أن يعين الطلاب !

المهم أتمنى حل لي هالـ 3 الاسئلة وشرحهم كلهم عن Subquery !  ، الإستعلام الفرعي ! 

واسأل الله أن يوفقكم دنيا وأخرة  !

اقتباس

 

هذا هو الكود : 
 

-- BEGIN OF SCRIPT -------------

 

------------- DROP TABLES -------------

DROP TABLE Register;

DROP TABLE Student;

DROP TABLE Course;

DROP TABLE Semester;

  

------------- CREATING TABLES -------------

CREATE TABLE Student(

stdNo CHAR(5) PRIMARY KEY,

lastname VARCHAR(25) NOT NULL,

givennames VARCHAR(50) NOT NULL,

Dept CHAR(4));

  

CREATE TABLE Course(

courseID CHAR(8) PRIMARY KEY,

courseTitle VARCHAR(50) UNIQUE NOT NULL,

Cost DECIMAL(6,2)CHECK(Cost>=0),

Credits INT CHECK(Credits BETWEEN 0 AND 200) DEFAULT 2);

  

CREATE TABLE Semester(

semesterID CHAR(5) PRIMARY KEY,

semesterCode INT CHECK(semesterCode BETWEEN 1 AND 4),

Year INT CHECK(Year BETWEEN 2000 AND 9999));

  

CREATE TABLE Register(

stdNo CHAR(5) REFERENCES Student(stdNo)

ON UPDATE CASCADE ON DELETE CASCADE,

courseID CHAR(8) REFERENCES Course(courseID)

ON UPDATE CASCADE ON DELETE CASCADE,

semesterID CHAR(5) REFERENCES Semester(semesterID)

ON UPDATE CASCADE ON DELETE CASCADE,

Grade CHAR(2),

Mark DECIMAL(4,2)CHECK(Mark BETWEEN 0.00 AND 100.00) DEFAULT 0.00,

PRIMARY KEY(stdNo,courseID,semesterID));

 

-- END OF SCRIPT ------------- 

 

-- BEGIN OF SCRIPT -------------

 

------------- INSERTING DATA INTO TABLES -------------

INSERT INTO Student

VALUES('S0001','Khaled','Marwan Ali','CS');

INSERT INTO Student

VALUES('S0711','Alsaleh','Abdulaziz Saleh','CS');

INSERT INTO Student

VALUES('S0201','Mosa','Ahmad Majed','IT' );

INSERT INTO Student

VALUES('S0421','Mohammad','Omar Riyadh','IT' );

INSERT INTO Student

VALUES('S0211','Alnasser','Ameen','CS' );

  

INSERT INTO Course

VALUES('IT125','Database',6453.50,3);

INSERT INTO Course

VALUES('CSC153', 'Object Oriented Programming',8480.00,4);

INSERT INTO Course

VALUES('CSC152', 'C Programming',8200.50,4);

INSERT INTO Course

VALUES('IT224', 'Java Programming',7680.00,3);

  

INSERT INTO Semester

VALUES('SM01',1,2012);

INSERT INTO Semester

VALUES('SM02',2,2012);

INSERT INTO Semester

VALUES('SM03',1,2013);

INSERT INTO Semester

VALUES('SM04',2,2013);

  

INSERT INTO Register

VALUES('S0001','CSC152','SM02','A',92);

INSERT INTO Register

VALUES('S0201','IT224','SM01','A',92);

INSERT INTO Register

VALUES('S0711' ,'CSC152','SM01','F',55);

INSERT INTO Register

VALUES('S0421','IT125','SM02','B',84);

INSERT INTO Register

VALUES('S0201','CSC153','SM01',NULL,NULL);

INSERT INTO Register

VALUES('S0201','IT125','SM02','B+',87);

INSERT INTO Register

VALUES('S0711' ,'CSC152','SM03','C+',77);

INSERT INTO Register

VALUES('S0421','IT224','SM02','F',57);

INSERT INTO Register

VALUES('S0211','CSC153','SM01',NULL,NULL);

INSERT INTO Register

VALUES('S0001','IT125','SM01','A+',96);

  

------------- Test: DATA VALUES -------------

SELECT *

FROM Student;

SELECT *

FROM Course;

SELECT *

FROM Semester;

SELECT *

FROM Register;

  

-- END OF SCRIPT ------------- 

 

-------------Lab_8--------Q3

select stdNo

from Register

where courseID in (

select r.courseID

from Student as s , Register as r

where s.stdNo = r.stdNo

and givennames = 'Marwan Ali'

)

 

---------------------------Q4

 

update course

set Cost = cost + cost * 0.1

where courseID in(

select courseID 

from Register

group by courseID

having COUNT(*) >= 2

)

 

 

---------------------------Q5

 

 

السؤال 5 : For semesters with 2 or more courses offered, find the total number of students