[Solved] CSE3055 Homework #4

1. 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.
2. Assign default values stanbul and 34722 for the fields city and postalCode of table STUDENT, respectively.
3. 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.
4. 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) 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.

1. List first name, last name, department name, advisors first name and last name. Order the list by department name ascending and students last name.
2. List distinct first and last name of students whose department is Computer Engineering.
3. List all attributes of the students whose first name contains at.
4. 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.
5. List student ID, first and last name, department name and graduation date of students who have graduated after 21.05.2010.