Scribd Logo
Upload

Welcome to Scribd!

  • 11 views

    Database4 CollegeDatabase

    Uploaded by

    akhilan.sivaramakrishnan.s

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd

    Database4 CollegeDatabase

    Uploaded by

    akhilan.sivaramakrishnan.s
    11 views24 pages

    Original Title

    Database4-CollegeDatabase (1)

    Copyright

    © © All Rights Reserved

    Available Formats

    PPTX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    11 views24 pages

    Database4 CollegeDatabase

    Uploaded by

    akhilan.sivaramakrishnan.s

    Copyright:

    © All Rights Reserved
    Download as PPTX, PDF, TXT or read online from Scribd
    Download as pptx, pdf, or txt
    You are on page 1of 24

    Database 4

    College Database
    STUDENT(USN, Sname, Address, Phone, Gender)
    SEMSEC(SSID, Sem, Sec)
    CLASS (USN, SSID)
    SUBJECT(Subcode, Title, Sem, Credits)
    IAMARKS (USN, Subcode, SSID,Test1,Test2,Test3,FinalIA)
    ER Diagram for College Database
    Schema Diagram
    Table Creation
    create table student create table semsec
    ( (
    usn varchar(10), ssid varchar(5),
    sname varchar(10), sem int,
    address varchar(10), sec varchar(5),
    phone bigint, primary key(ssid)
    gender char(1), );
    primary key(usn)
    );
    insert into student values('CS101','Arun',‘Ujire','9481235681','M');
    insert into student values('CS102','Pramya','Mangalore','8945689532','F');
    insert into student values('CS103','Ravi','Bangalore','9568742361','M');
    insert into student values('CS104','Ravi','Bangalore','9568742361','M');
    insert into student values('CS105','Akshatha','Bantwal','9845632147','F');
    insert into student values('CS106','Ranjan','Karwar','9485632158','M');
    insert into semsec values('CS4A', 4, 'A');
    insert into semsec values('CS5B', 5, 'B');
    insert into semsec values('CS5C', 5, 'C');
    insert into semsec values('CS7A', 7 , 'A');
    insert into semsec values('CS7B', 7 , 'B');
    insert into semsec values('CS3C', 3, 'C');
    create table class
    (
    usn varchar(10),
    ssid varchar(5),
    primary key(usn),
    foreign key(usn) references student(usn) on delete cascade,
    foreign key(ssid) references semsec(ssid) on delete cascade
    );
    create table subject
    (
    subcode varchar(8),
    title varchar(15),
    sem int,
    credits int,
    primary key(subcode)
    );
    insert into class values('CS101','CS7A');
    insert into class values('CS102','CS7A');
    insert into class values('CS103','CS7B');
    insert into class values('CS104','CS7C');
    insert into class values('CS105','CS3C');
    insert into class values('CS106','CS3C');

    insert into subject values('18CS31',‘Maths',‘3','5');


    insert into subject values('18CS32 ',DS',‘3','4');
    insert into subject values('18CS33',‘ADE',‘3','3');
    insert into subject values('17CS71',‘WT',‘7','4');
    insert into subject values('17CS72',‘ACA',‘7','4');
    insert into subject values('17CS73',‘ML',‘7','5');
    create table IAmarks
    (
    usn varchar(10),
    subcode varchar(8),
    ssid varchar(5),
    test1 int,
    test2 int,
    test3 int,
    finalIA int,
    primary key(usn,subcode,ssid),
    foreign key(usn) references student(usn) on delete cascade,
    foreign key(subcode) references subject(subcode),
    foreign key(ssid) references semsec(ssid)
    );
    insert into IAmarks values('CS101','17CS71','CS7A',19,20,18,NULL);

    insert into IAmarks values('CS102','17CS71','CS7A',16,15,12,NULL);

    insert into IAmarks values('CS103','17CS72','CS7B',09,08,12,NULL);

    insert into IAmarks values('CS104','17CS73','CS7C',03,05,08,NULL);

    insert into IAmarks values('CS105','18CS31','CS3C',10,14,16,NULL;

    insert into IAmarks values('CS106','18CS32','CS3C',13,15,20,NULL);


    Query 1: List all the student details studying in fourth semester ‘C’ section.
    Query 1: List all the student details studying in fourth semester ‘C’
    section.

    SELECT S.*, SS.SEM, SS.SEC


    FROM STUDENT S, SEMSEC SS, CLASS C
    WHERE S.USN = C.USN AND SS.SSID = C.SSID AND SS.SEM = 3 AND
    SS.SEC=”C”;
    2. Compute the total number of male and female students in each
    semester and in each section.
    2. Compute the total number of male and female students in each
    semester and in each section.

    SELECT SS.SEM, SS.SEC, S.GENDER, COUNT(S.GENDER) AS COUNT


    FROM STUDENT S, SEMSEC SS, CLASS C
    WHERE S.USN =C.USN AND SS.SSID = C.SSID
    GROUP BY S.GENDER
    ORDER BY SEM;
    3. Create a view of Test1 marks of student USN ‘CS101’ in all subjects.
    3. Create a view of Test1 marks of student USN ‘CS101’ in all subjects.

    CREATE VIEW STU_TEST1_MARKS_VIEW AS


    SELECT test1, subcode
    FROM IAmarks
    WHERE usn = ‘CS101’;
    4. Calculate the FinalIA (average of best two test marks) and update the
    corresponding table for all students.
    Syntax for Case statement with a comparison operator.

    1 CASE
    2 WHEN ComparsionCondition THEN result
    3 WHEN ComparsionCondition THEN result
    4 ELSE other
    5
    END

    Example:
    Select EmployeeName,
    CASE
    WHEN Salary >=80000 AND Salary <=100000 THEN 'Director'
    WHEN Salary >=50000 AND Salary <80000 THEN 'Senior Consultant'
    Else ‘consultant'
    END AS Designation
    from Employee
    4. Calculate the FinalIA (average of best two test marks) and update the
    corresponding table for all students.
    create view best_two_finder as
    ( select usn, subcode, greatest(test1,test2,test3) as highest,
    case
    when test1<=greatest(test1,test2,test3) and test1>least(test1,test2,test3) then test1
    when test2<=greatest(test1,test2,test3) and test2>least(test1,test2,test3) then test2
    else test3
    end as secondhighest from iamarks
    );

    select * from best_two_finder;

    create view average2 as (select subcode, ((highest+secondhighest)/2) as final from best_two_finder);

    select * from average2;


    update iamarks join average2
    on iamarks.subcode=average2.subcode
    set iamarks.finalIA=average2.final;
    5. Categorize students based on the following criterion:
    If FinalIA = 17 to 20 then CAT = ‘Outstanding’
    If FinalIA = 12 to 16 then CAT = ‘Average’
    If FinalIA< 12 then CAT = ‘Weak’
    Give these details only for 7th semester A, B, and C section students.
    5. Categorize students based on the following criterion:
    If FinalIA = 17 to 20 then CAT = ‘Outstanding’
    If FinalIA = 12 to 16 then CAT = ‘Average’
    If FinalIA< 12 then CAT = ‘Weak’
    Give these details only for 8th semester A, B, and C section students.

    SELECT S.USN, S.SNAME, S.ADDRESS, S.PHONE, S.GENDER,


    (CASE
    WHEN IA.FINALIA BETWEEN 17 AND 20 THEN 'OUTSTANDING'
    WHEN IA.FINALIA BETWEEN 12 AND 16 THEN 'AVERAGE'
    ELSE 'WEAK'
    END) AS CAT
    FROM STUDENT S, SEMSEC SS, IAMARKS IA, SUBJECT SUB
    WHERE S.USN = IA.USN AND
    SS.SSID = IA.SSID AND
    SUB.SUBCODE = IA.SUBCODE AND
    SUB.SEM = 7;
    Database 5

    Consider the schema for Company Database:


    EMPLOYEE (SSN, Name, Address, Sex, Salary, SuperSSN, DNo)
    DEPARTMENT (DNo, DName, MgrSSN, MgrStartDate)
    DLOCATION (DNo,DLoc)
    PROJECT (PNo, PName, PLocation, DNo)
    WORKS_ON (SSN, PNo, Hours)

    You might also like