Consider the following relational schema:

Student (Sid, Sname, ProgCode, DateofAdmission) – Sid is the student id (Primary Key) which is a unique identifier assigned by the University when the student takes admission into a Programme, Sname is the student name, ProgCode identifies the programme in which the student has taken admission (Foreign Key), DateofAdmission is the date when the student took admission.

Course (CourseCode, CourseName)– CourseCode is the name of the course and is the Primary Key, CourseName is the name of the course. Result (Sid, CourseCode, ExaminationDate, Marks) –Sid is the student id, CourseCode is the
identifier of the course or subject in which the student has taken admission (Sid + Coursecode are the composite Primary Key; please also note Sid is a foreign key that references Student relation and CourseCode is a foreign key that references the Course relation), ExaminationDate is the date of examination, Marks is the marks out of 100 in that examination.

(a) Create the tables using SQL for the schema given above; you must include the Primary key, foreign keys and constraints in your implementation. Enter a few sets of meaningful data in each table.

(b) Write and run SQL statements for the following queries:
(i) List the names of all the students who have been admitted to CMAD after 31st
December 2023.
(ii) List the names of all the students who have appeared for the examination of the
course whose code is “BCS092”.
(iii) Find the list of courses in which a student whose Sid is “2310009487” passed.
Assume that the minimum marks for passing a course is 40.
(iv) List the student ID and the student's name who has passed at least five courses.
(v) Find the total number of Programmes.
(vi) Find the total number of students who have appeared for the examination of the
course named “DBMS”.
Note: Make suitable assumptions, if any

(a) SQL Schema and Data Insertion:

```sql
-- Create Student table
CREATE TABLE Student (
    Sid INT PRIMARY KEY,
    Sname VARCHAR(255),
    ProgCode INT,
    DateofAdmission DATE,
    FOREIGN KEY (ProgCode) REFERENCES Programme(ProgCode)
);

-- Insert data into Student table
INSERT INTO Student (Sid, Sname, ProgCode, DateofAdmission) VALUES
(2310009487, 'John Doe', 1, '2023-01-15'),
(2310009488, 'Jane Smith', 2, '2024-02-20'),
(2310009489, 'Alice Johnson', 1, '2023-06-10');

-- Create Course table
CREATE TABLE Course (
_______ __________ __________ _______ ____ _______ __________ ________.
_________ ________ _______ ________ ______ _____ _______.
_________ _________ ____ ____ ____ ______.
_____ _______ ___ ___ ____.
_________ ______ _____ ______ ___ ________ __________ _________ _____ _________.
____ ________ __________ ___ ________.
_______ _____ ________ __________ ____.
_____ __________ __________ ______ _______ ______ ___.
______ _______ _________ _______ _______.
__________ ___ _______ _________ _________ ______ ______ _______ ______ ______.
____ ______ ________ ______ ____ ______ _______ ________ _____.
___ ______ ___ __________ _________ _________.
_____ ___ ________ _________ ________ ________ ___ _____ __________ _______ _______ __________.
_____ _______ __________ ________ _____ _________ ________ ____ ________ ______ __________ _______.
_________ __________ __________ _________ _____ ______ _________ __________ _______ ____.
________ _____ ____ _____ _____ ____.
_______ _________ ____ ___ __________.
_________ __________ ______ ___ _____.
___ _____ ____ __________ _______ ____ _________.
__________ ______ ______ ___ ______ _______ _____ ______ _______ _______ ___.
__________ _____ ______ ______ _______ _________ ________ __________ ____ _________ __________ _____.
_________ ________ _____ __________ ___ ____ ________ ____ _______ ________.
_______ _______ _____ ___ _________.
____ ___ __________ ______ _______ ______ ________ _______ __________ _____ _______.
________ _________ ____ ___ _______ ______ _____ _____ __________.
____ ____ _______ _________ _____ _________ ___ ________.
______ __________ _____ _________ __________ ____ _________ __________.
___ __________ ____ ______ ____ ______ _____ __________ ______ _______ ______.
_________ ____ _________ __________ ________ _______ __________ __________.
___ ________ _________ _____ __________.
________ ______ _________ ______ __________ _________ ______ __________ _______ _______ ________.
_________ ______ ____ _____ _______ _____ __________ _____ ___ ____ ___.
_________ _______ __________ ____ ______ ___ ___ __________ _______ ________ _______ _____.
________ _______ ____ __________ __________ ________.
_______ __________ ______ __________ ____ _________ ______ _____ ____.
_____ _____ ________ ___ __________ ________ _____ ______ _______ _______.
_____ ___ _______ ______ _________ ________.
____ __________ _____ ___ ________.
_____ _____ _______ ____ ______ __________.
__________ ____ ___ _________ _____.
____ ____ ________ __________ _______ _____ ____ ___ _____ _______ ________.
______ ____ __________ ________ _____ __________ ___ ________ _______ _____ _________.
_________ _____ ____ _________ ______.
___ _________ ______ _______ _____ ____ _____ _________ ______ ___ __________ ___.
_____ _______.
Get Full Answer on WhatsApp