Database Systems Homework #3 Solution



  1. [58 pts] Database Implementation.

    1. [30 pts] Implement the following database on MS SQL Server. Name your database as your LASTNAME-FIRSTNAME using English letters.

  1. [3 pts] Assign default values “İstanbul” and “34722” for the fields city and postalCode of table STUDENT, respectively.

  1. [20 pts] After implementing your database, use the MS Excel file “cse3055-fall2017-homework-3-data.xlsx” to import data. In order to import data on MS SQL Server, right click on your database (LASTNAME-FIRSTNAME), point to “Tasks” and click on “Import Data…”.

  1. [5 pts] Backup your database. In order to take a backup on MS SQL Server, right click on your database (LASTNAME-FIRSTNAME), point to “Tasks” and click on “Back Up..”. Name your backup file as “hw3-LASTNAME-FIRSTNAME.bak”.

  1. [42 pts] Write the following SQL statements on your MS SQL Server database. For each of the following query, take a screenshot of both your SQL query and output of the query on MS SQL Server.

    1. [7 pts] List first name, last name, birthdate and city of students.

    1. [7 pts] List first name, last name, department name, advisor’s first name and last name. Order the list by department name ascending and student’s last name.

    1. [7 pts] List distinct first and last name of students whose department is Computer Engineering.

    1. [7 pts] List all attributes of the students whose first name contains ‘at’.

    1. [7 pts] List staff ID, first name and last name of married managers who is older than 40 and have at least 2 children. Order the list by birthdate.

    1. [7 pts] List student ID, first and last name, department name and graduation date of students who have graduated after 21.05.2010.

  1. Compress your database backup file with the screenshots in a zip file. Name it as and submit it.