a) [30 pts] Implement the following database on MS SQL Server. Name your database as your InitialsOfFullName_StudentID, e.g. MAY_150118123 for a student with full name “Mehmet Ali YILMAZ” and student ID “150118123”.
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 Excel file “cse3055_fall2020_homework_4_data.xlsx” to import data. In order to import data on MS SQL Server, right click on your database (e.g. MAY_150118123), point to “Tasks” and click on “Import Data…”.
d) [5 pts] Backup your database. In order to take a backup on SQL Server, right click on your database (e.g. MAY_150118123), point to “Tasks” and click on “Back Up..”. Name your backup file as “hw4_NameOfDatabase.bak” (e.g. hw4_MAY_150118123.bak).
2) [42 pts] Write the following SQL statements on your SQL Server database. For each of the following query, take a screenshot of both your SQL query and output of the query on 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.
1/2
