CSE3055  Homework #4 Solved

Description

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, advisor’s first name and last name. Order the list by department name ascending and student’s 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.