Description

5/5 - (2 votes)
  • [58 pts] Database Implementation.

 

  1. [30 pts] Implement the following database on MS SQL Server. Name your database as your LASTNAME-FIRSTNAME using English letters.

 

 

 

  1. [3 pts] Assign default values “İstanbul” and “34722” for the fields city and postalCode of table STUDENT, respectively.

 

 

  1. [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…”.

 

 

  1. [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”.

 

  • [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.

 

  1. [7 pts] List first name, last name, birthdate and city of students.

 

 

  1. [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.

 

 

  1. [7 pts] List distinct first and last name of students whose department is Computer Engineering.

 

 

  1. [7 pts] List all attributes of the students whose first name contains ‘at’.

 

 

  1. [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.

 

 

  1. [7 pts] List student ID, first and last name, department name and graduation date of students who have graduated after 21.05.2010.

 

 

 

  • Compress your database backup file with the screenshots in a zip file. Name it as hw5-LASTNAME-FIRSTNAME.zip and submit it.