Scribd Logo
Upload

Welcome to Scribd!

  • 1 views

    Lab 4_DBI202

    Uploaded by

    datvipzpro1802

    Copyright:

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

    Lab 4_DBI202

    Uploaded by

    datvipzpro1802
    1 views4 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    1 views4 pages

    Lab 4_DBI202

    Uploaded by

    datvipzpro1802

    Copyright:

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

    LAB 4 – Simple SQL statements

    Suppose having the database (provided in Lab4_ScriptDB.sql) with the following database diagram, write SQL statements
    to do below tasks.
    For the submission of your work:
    - For each question, you are required to write a database script. Create a file with the name corresponding to the index of
    the question. For example, for question 1, we will create a file named Q1.sql and create a file Q2.sql for question 2, and so
    on.
    - At the beginning of each question, please add the following comment with your name and your roll number
    /* Student Name _ RollNumber */
    - Do not use any commands having the database name such as create database, alter database, use [database name], etc.
    - Your response must contain only necessary commands for answering the question. Do not include any other command.
    For example, if you are required to create a trigger/procedure, then your response should contain only commands for creating
    the corresponding trigger/procedure; all commands for testing the created trigger/procedure are forbidden.
    - Do not use GO command in your code
    If any of the previous requirements is not respected, your mark will be 0.
    Question 1. (Q1.sql) Write a query to select StudentID, CourseID, Year, Mark of all results of course AI01 and DS01
    in the year 2017 and having Mark at least 4,5 as follows:

    Question 2. (Q2.sql) Select all male students of the three departments named ‘Computer Science’, ‘Network and
    Communication’ and ‘Software Engineering’ as in the following figure. Note that the information of student should be
    NULL if the department has no male student.

    Question 3. (Q3.sql) Select StudentID, LastName, FirstName, Sex, DeptID, CourseName, Year, Semester, Mark of the
    results of all students in the department named ‘Information Systems’; display the results by ascending order of
    LastName then by ascending order of FirstName as follows:

    Question 4. (Q4.sql) Select DeptID, DepartmentName, StudentID, LastName, FirstName, DateOfBirth, PlaceOfBirst,
    CourseName, Year, Semester, Mark of the results corresponding to courses having credits = 3 of all students of the
    department named ‘Software Engineering’; display the results by ascending order of PlaceOfBirth then by descending
    order of DateOfBirth as follows:
    Question 5. (Q5.sql) Calculate the number of students studied each course in 2018; show the results in descending order
    of NumberOfStudents as follows:

    Question 6. (Q6.sql) Calculate the number of courses leaned by each student in 2017, show the results in descending
    order of NumberOfCourses as follows:

    Question 7. (Q7.sql ) Update AverageScore for each student as his/her average score in 2017 so that for each course,
    we take only his/her highest Mark in 2017 and the AverageScore of the student is calculated as the average of highest mark
    in 2017 of all courses. The table Students after updating should be as follows:

    Question 8. (Q8.sql) Insert into table Results the results for StudentID = S001, Year = 2019, Semester = 1, Mark = Null
    and Grade = Null for all courses which were not studied by the student ‘S001’. After inserting these rows, the table Results
    is as follows:

    You might also like