Description
1) [58 pts] Database Implementation.
a) [30 pts] Implement the following database on MS SQL Server. Name your database as your
LASTNAME-FIRSTNAME using English letters.
b) [3 pts] Assign default values “İstanbul” and “34722” for the fields city and postalCode of table
STUDENT, respectively.
c) [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…”.
d) [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”.
Student ID: Student Name:
2) [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.
a) [7 pts] List first name, last name, birthdate and city of students.
b) [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.
c) [7 pts] List distinct first and last name of students whose department is Computer Engineering.
d) [7 pts] List all attributes of the students whose first name contains ‘at’.
e) [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.
f) [7 pts] List student ID, first and last name, department name and graduation date of students who
have graduated after 21.05.2010.
3) Compress your database backup file with the screenshots in a zip file. Name it as hw5-LASTNAMEFIRSTNAME.zip and submit it.
