Consider the following relational database:Student (stid, stName, st

Question

Consider the following relational database:
Student (stid, stName, stProg, Phone)
Programme (progId, prName, prDuration, prFee)
Course (courseCode, courseName, courseCredit)
CourseProgramme(progID, courseCode)
The underlined attributes form the primary key of the relations. In relation Student, the stProg is
the programme code of the student and should reference progId of the Programme table. Please
note that a course may be part of several programmes. Please note that the CourseProgramme relation has two foreign keys. Write and run the following SQL queries on the tables:
(i) Create the tables with the primary and foreign key constraints.
(ii) Insert at least 5 records in the first 3 tables and 20 records in the 4th table.
(iii) List all the Programmes of the University in the order of programme name
(iv) Find the list of all the courses of programme whose ID is “PGDCA”.
(v) Find the list of those courses that are part of more than one programme.
(vi) Find the list of courses, which are to be studied by a student whose ID is “S001”
(vii) Find the total credits of each programme.
(viii) Find the list of students in each Programme.
(ix) List the pair of students who share the same phone number.
(x) List all the students whose name starts with the alphabet ‘A’.

01 Dec 2023

Answer :

Word Count : 560

(i) Create the tables with the primary and foreign key constraints:

 CREATE TABLE Student (     stid VARCHAR(10),     stName VARCHAR(50),     stProg VARCHAR(10),     Phone VARCHAR(15),     PRIMARY KEY (stid),     FOREIGN KEY (stProg) REFERENCES Programme(progId) ); CREATE TABLE Programme (     progId VARCHAR(10),     prName VARCHAR(50),     prDuration INT,     prFee DECIMAL(10,2),     PRIMARY KEY (progId) ); CREATE TABLE Course (     courseCode VARCHAR(10),     courseName VARCHAR(50),     courseCredit INT,     PRIMARY KEY (courseCode) ); CREATE TABLE CourseProgramme (     progId VARCHAR(10),     courseCode VARCHAR(10),     FOREIGN KEY (progId) REFERENCES Programme(progId),  __________ __________ ________ __________ _________ __________ ______.
 __________ _______ _______ _____ ___ ______ _______.
 ____ _____ ___ _____ _______.
 ________ _______ ____ _________ ___ _______ _______.
 _______ ______ _________ _______ ________.
 ________ __________ _________ __________ _________ ________ _________ __________ __________ __________ ____.
 ____ ____ _____ ____ ___ ________ ____ ________.
 _______ ______ _______ _______ _______ __________ ____ ___.
 ________ ________ ___ __________ ________ ________ ______ _______.
 _______ _____ ________ _____ ____ __________ ______ _________ _________ ____.
 ___ _____ ______ _____ ___ _________ _______ _________.
 ___ _____ ___ ___ ______ __________ ___ __________ ______ _______.
 __________ ________ _____ _______ ___ _____ ________ ____ _______ ___ _____.
 __________ ______ ____ ________ __________ _________.
 _________ ______ ________ _____ _________ _______ _______ __________.
 ____ _______ ____ _______ ____ ____.
 __________ ______ ___ _________ __________ _______ ____.
 _________ _____ _____ ______ ____ _____ _________.
 _________ _______ _______ _____ _______ __________.
 ________ ________ __________ ______ __________ ____ ________ ______ _________ ______ ______.
 ____ ________ __________ ___ _________ _____ ___ ___ ______ __________ _______ ________.
 _________ ________ __________ _________ ___ _________ ________ ____ _____.
 ____ __________ _________ _________ ________ ______ __________ __________ _________ __________.
 ________ ________ ___ __________ ____ ____ _______ ____ ________ ______ ________.
 _______ ____ ____ ________ _________ ______ ______.
 _________ ___ ________ _________ _____.
 _______ ______ ____ ____ ______ ___ __________.
 ____ ________ _____ _________ ____.
 ____ ___ __________ _________ _________ ___.
 _____ ___ ________ ________ ____ __________ ________ ______.
 ___ ____ ________ __________ ___.
 ______ __________ ____ _____ ________ _______ ____ _____ ______.
 _________ _____ ________ _____ ___ ________ ______ _____ _________.
 _____ ________ ___ _______ ___ ____ ________ _______ _________ ____ _____.
 _________ ________ _______ ________ __________ _________ __________ ____ ______ _________.
 ____ __________ _______ ________ ________ ________ _____.
 __________ ___ __________ ________ _________ _______ ________ ______ _______ __________ _______ __________.
 _________ _______ _____ _____ ______ _________ __________ _____ _____ ________ __________ ___.
 ________ _____ ________ ____ _____ ____ _______ _____ _____.
 _______ _________ _____ _____ _________ ________ ______ _________.
 _________ ________ ___ ___ ________ ________ _________ ___ ____ _____ _________ ________.
 _______ ___ __________ ______ ________ _______ ________ _____ ________ __________.
 _____ __________ ___ __________ _________ _____ ______ ______.
 ________ __________ ___ _________ _______.
 ____ __________ _______ ________ ____ ____.
 ________ _______ ____ __________ _____ _____ _____ ___ _________ ____.
 ____ _________ _____ __________ __________.
 __________ ______ ___ ________ ________ _______ ______ ________ ____ __________ _____.
 ____ _____ ________ ______ ________ ___ ________ ________ ________.
 ___ ____ _________ __________ __________ ______ ______ _______ _______.
 _______ _______ ________ _____ ____ __________ ____ ________ _____ __________ _________.
 ______ _________ _____ _______ _________.
 _______ _______ ________ __________ ____.
 ____ _________ ________ _________ ______.
 ___ _______ ______ ______ _________.
 _____ ____ _______ ______.
Get Full Answer on WhatsApp

IGNOU Solved Assignments Assignments Ignou Projects Project Previous Year Solved Exam Papers Notes Notes Sample Question Papers Sample Question Papers

IGNOU NEWS

Assignment Submission Last Date Extended Till 30 June 2026 Click Here★★★IGNOU June 2026 TEE Date Sheet Released Click Here★★★