CSE3055-Homework 3-Database Implementation Solved

Description

 Homework #3

• Database Implementation.
  1.  Implement the following database on MS SQL Server. Name your database as your LASTNAME-FIRSTNAME using English letters.Assign default values “İstanbul” and “34722” for the fields city and postalCode of table STUDENT, respectively.
  2.  
     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 “hw3LASTNAME-FIRSTNAME.bak”.

      

      

      

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

      

     1.  List distinct first and last name of students whose department is Computer Engineering.

      

     1.  List all attributes of the students whose first name contains ‘at’.

      

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

      

     21.  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-LASTNAMEFIRSTNAME.zip and submit it.

      

     After implementing your database, use the MS Excel file “cse3055-fall2017-homework-3data.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…”