Scribd Logo
Upload

Welcome to Scribd!

  • 4 views

    School_Ranking_Analysis_Solution

    Uploaded by

    Sakshi Gupta

    Copyright:

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

    School_Ranking_Analysis_Solution

    Uploaded by

    Sakshi Gupta
    4 views5 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    4 views5 pages

    School_Ranking_Analysis_Solution

    Uploaded by

    Sakshi Gupta

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    of 5

    SQL Training

    Lesson-End Project Solution


    School Ranking Analysis

    1. Write a query to create a students table with the student ID, first name, last
    name, class, and age fields and ensure that the last name, first name, and
    student ID fields have the NOT NULL constraint and that the student ID field is
    a primary key

    SQL code:
    CREATE TABLE lep_5.students (
    s_id INT NOT NULL,
    s_fname varchar(45) NOT NULL,
    s_lname varchar(45) NOT NULL,
    class varchar(45) NULL,
    age INT NOT NULL,
    PRIMARY KEY(s_id));

    2. Write a query to create a marksheet table with score, year, ranking, class,
    and student ID fields

    SQL code:
    CREATE TABLE lep_5.marksheet (
    score INT NOT NULL,
    year INT NULL,
    class varchar(45) NULL,
    ranking varchar(45) NULL,
    s_id INT NOT NULL);

    3. Write a query to insert values into the students and marksheet tables

    SQL code: Students table


    INSERT INTO lep_5. students (s_id,s_fname,s_lname,class,age) VALUES
    ('01','krishna','gee','10','18');

    SQL code: Marksheet table


    INSERT INTO lep_5. marksheet (score,year,class,ranking,s_id) VALUES
    ('989','2014','10','1','1');
    4. Write a query to display the student ID and first name of every student in the
    students table whose age is greater than or equal to 16 and whose last name
    is Kumar

    SQL code:
    SELECT s_id,s_fname FROM lep_5.students WHERE s_lname = 'kumar' AND
    age>=16;

    Output:

    5. Write a query to display the details of every student from the marksheet table
    whose score is between 800 and 1000
    SQL code:
    SELECT * FROM lep_5.marksheet WHERE score BETWEEN 800 AND 1000 ;

    Output:

    6. Write a query to increase the score in the marksheet table by five and create a
    new score column to display this new score

    SQL code:
    SELECT * ,score+5 as new_score FROM lep_5.marksheet ;

    Output:
    7. Write a query to display the marksheet table in descending order of the score

    SQL code:
    SELECT * FROM lep_5.marksheet ORDER BY score DESC ;

    Output:

    8. Write a query to display the marksheet table in descending order of the


    score

    SQL code:
    SELECT * FROM lep_5.students WHERE s_fname LIKE 's%';

    Output:

    You might also like