[Solved] CSE3055 Homework #2

Rate this product

1. 1) Database Implementation.
   1. a) Implement the following database on MS SQL Server. Name your database as your InitialsOfFullName_StudentID, e.g. MAY_150120123 for a student with full name Mehmet Ali YILMAZ and student ID 150120123.
   2. b) Assign default values Istanbul and 34722 for the fields city and postalCode of table STUDENT, respectively.
   3. c) After implementing your database, use the Excel file CSE3055_fall2021_homework_2_data.xlsx to import data. In order to import data on MS SQL Server, right click on your database (e.g. MAY_150120123), point to Tasks and click on Import Data.
   4. d) Backup your database. In order to take a backup on SQL Server, right click on your database (e.g. MAY_150120123), point to Tasks and click on Back Up… Name your backup file as hw2_NameOfDatabase.bak (e.g. hw2_MAY_150120123.bak).
2. 2) 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. a) List first name, last name, birthdate and city of students.
   2. b) List first name, last name, department name, advisors first name and last name. Order the list bydepartment name ascending and students last name.
   3. c) List distinct first and last name of students whose department is Computer Engineering.
   4. d) List all attributes of the students whose first name contains at.
   5. e) 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.
   6. f) List student ID, first and last name, department name and graduation date of students who have graduated after 21.05.2010.