مساعدة في حل أسئلة Sql

متابعين

بتاريخ: 25 مايو 201411 سنة

ارجوكم بسرعة

Student table

student_id Sname DOB Dept

100 Salim 15-04-1985 IT

200 Aisha 12-01-1990 IT

300 Asma 17-07-1992 IT

400 Syed 15-06-1992 Media

Course table

Student_id CorseCode Marks

100 CS100 89

100 CS100 75

200 CS100 55

200 CS100 65

300 CS100 85

300 CS100 80

300 CS100 90

1- addd Student_id as primary key in student table

1- addd Student_id as refernence key in corce table

3- add acheckc constraint to check if marks is greater than or equal to 10

4add unique constraint on sname column

5 insert the data into the tables as given above

*) ansewers the following queries

a) select the details of student in IT depatment

siplay the max marks obtained in cource CS100

c) display total marks obtaines by student salim in all the subjects

d_ order the course table in descending order on mark column

e) write query to display number of couse studeied by each student

3) create a cursor to display all the details of all student

4) create trigger to check that course is registed only by once a student

اقتباس

تقديم بلاغ

بتاريخ: 25 مايو 201411 سنة

السلام عليكم

1. 
ALTER  TABLE student
ADD CONSTRAINT student_pk PRIMARY KEY (student_id);
--=============================================================================
2.
ALTER TABLE Course
ADD CONSTRAINT fk_Course
  FOREIGN KEY (student_id)
  REFERENCES student(student_id);
--=============================================================================
3.
ALTER TABLE Course
ADD CONSTRAINT Course_check CHECK (marks > 10);
--=============================================================================
4.
ALTER TABLE Course
ADD CONSTRAINT constraint_unique UNIQUE (marks);
--=============================================================================
5.
INSERT INTO student
     VALUES (100, 'Salim', 15 - 04 - 1985, 'IT');
INSERT INTO student
     VALUES (200, 'Aisha', 12 - 01 - 1990, 'IT');
INSERT INTO student
     VALUES (300, 'Asma', 17 - 07 - 1992, 'IT');
INSERT INTO student
     VALUES (400, 'Syed', 15 - 06 - 1992, 'Media');




INSERT INTO course
     VALUES (100, 'CS100', 89);
INSERT INTO course
     VALUES (100, 'CS100', 75);
INSERT INTO course
     VALUES (200, 'CS100', 55);
INSERT INTO course
     VALUES (200, 'CS100', 65);
INSERT INTO course
     VALUES (300, 'CS100', 85);
INSERT INTO course
     VALUES (300, 'CS100', 80);
INSERT INTO course
     VALUES (300, 'CS100', 90);
--=============================================================================
a.
SELECT *
  FROM student
 WHERE dept = 'IT';
--=============================================================================
b.
SELECT MAX (mark)
  FROM course
 WHERE corsecode = 'CS100';
--============================================================================= 
c.
SELECT SUM (marks)
  FROM student a, course b
 WHERE a.student_id = b.student_id AND UPPER (sname) = UPPER ('salim');
--============================================================================= 
d.
SELECT   *
    FROM course
ORDER BY mark DESC;
--=============================================================================
e.
SELECT   student_id, COUNT (*) number_of_courses
    FROM course
GROUP BY student_id;
--=============================================================================
3.
DECLARE
   CURSOR c
   IS
      SELECT student_id, sname, dob, dept
        FROM student;
BEGIN
   FOR rec IN c
   LOOP
      DBMS_OUTPUT.put_line ('Student ID ' || rec.student_id);
      DBMS_OUTPUT.put_line ('Student Name ' || rec.sname);
      DBMS_OUTPUT.put_line ('Date of Birht ' || rec.dob);
      DBMS_OUTPUT.put_line ('Departement ' || rec.dept);
   END LOOP;
END;

اقتباس