Scribd Logo
Upload

Welcome to Scribd!

  • 84 views

    ITECH - 1103 - Mid - Sem - Test

    Uploaded by

    harry
    The document describes a mid-semester test for a Big Data and Analytics course. It contains two parts - (1) drawing an ER diagram based on requirements for a Department database, and (2) writing SQL queries to retrieve information from an Employee table. The ER diagram requirements describe the relationships between Departments, Divisions, Employees, and Projects. The SQL questions ask the student to write queries to retrieve employee details based on criteria like city, hire date, number of employees in a city, and employee names matching certain patterns.

    Copyright:

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

    ITECH - 1103 - Mid - Sem - Test

    Uploaded by

    harry
    84 views3 pages
    The document describes a mid-semester test for a Big Data and Analytics course. It contains two parts - (1) drawing an ER diagram based on requirements for a Department database, and (2) writing SQL queries to retrieve information from an Employee table. The ER diagram requirements describe the relationships between Departments, Divisions, Employees, and Projects. The SQL questions ask the student to write queries to retrieve employee details based on criteria like city, hire date, number of employees in a city, and employee names matching certain patterns.

    Original Title

    ITECH_1103_mid - Sem - Test

    Copyright

    © © All Rights Reserved

    Available Formats

    DOCX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document describes a mid-semester test for a Big Data and Analytics course. It contains two parts - (1) drawing an ER diagram based on requirements for a Department database, and (2) writing SQL queries to retrieve information from an Employee table. The ER diagram requirements describe the relationships between Departments, Divisions, Employees, and Projects. The SQL questions ask the student to write queries to retrieve employee details based on criteria like city, hire date, number of employees in a city, and employee names matching certain patterns.

    Copyright:

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

    ITECH - 1103 - Mid - Sem - Test

    Uploaded by

    harry
    The document describes a mid-semester test for a Big Data and Analytics course. It contains two parts - (1) drawing an ER diagram based on requirements for a Department database, and (2) writing SQL queries to retrieve information from an Employee table. The ER diagram requirements describe the relationships between Departments, Divisions, Employees, and Projects. The SQL questions ask the student to write queries to retrieve employee details based on criteria like city, hire date, number of employees in a city, and employee names matching certain patterns.

    Copyright:

    © All Rights Reserved
    Download as DOCX, PDF, TXT or read online from Scribd
    Download as docx, pdf, or txt
    You are on page 1of 3

    ITECH 1103 BIG DATA AND ANALYTICS

    Mid- Semester Test (Test Paper 2)


    Total Marks [10]
    Duration 80 Minutes + 5 Minutes Reading

    Student Name: Student ID:

    1. ERP Diagram [50 Marks]

    Consider the following set of requirements for a Department database:

     DEPARTMENT employs many EMPLOYEES, but each employee is employed by one


    DEPARTMENT.
     Some EMPLOYEES, known as "rovers," are not assigned to any DEPARTMENT.
     A DIVISION operates many DEPARTMENTs, but each DEPARTMENT is operated by one
    DIVISION
     An EMPLOYEE may be assigned to many projects, and a project may have many EMPLOYEES
    assigned to it
     A PROJECT must have at least one EMPLOYEE assigned to it
     One of the EMPLOYEES manages each DEPARTMENT, and each DEPARTMENT is managed by
    only one EMPLOYEE.
     One of the EMPLOYEES runs each DIVISION, and each DIVISION is run by one EMPLOYEE.

    According to the information above, draw a corresponding ER diagram. Please note:

    a) You can decide the names for entities, attributes and relations, but you should use
    UPPERCASE to name your entities and use Capitalisation to name attributes of entities.[3
    Marks]
    b) Underline all primary key attributes, and put (f.k) next to all the foreign key attributes. [1
    Mark]
    c) Clearly indicate cardinalities between entities. [1 Mark]

    Answer:

    Page 1 of 3
    2. SQL [50 marks]

    Consider the following Employee Table :

    Write following SQL statements:

    1. Display the details of the employees who are living in London


    Answer: SELECT *
    FROM EMPLOYEE
    WHERE City = ‘London’;

    Page 2 of 3
    2. Display the details of the employees who were hired between 1-June-1992 and 15-december-1993
    Answer: SELECT *
    FROM EMPLOYEE
    WHERE HireDate BETWEEN ‘1992-06-01’ AND ‘1993-12-15’;

    3. Display the name of city with more than 2 employee


    Answer SELECT City, COUNT(*)

    FROM EMPLOYEE

    GROUP BY City

    HAVING COUNT (*) > 2

    4. Display the names of the employee whom last name ends with letter n.[1 mark]
    Answer: SELECT First Name, LastName

    FROM EMPLOYEE

    WHERE LastName LIKE ’%n’;

    5. Show the name of all of the employees in one column, in the format first name last name (eg Fred
    Smith) [1 mark]
    Answer SELECT CONCAT (FirstName, ‘ ‘ ,LastName )

    FROM EMPLOYEE;

    Page 3 of 3

    You might also like