Bcis 4660

Page | 1

BCIS 4660

Introduction to Data Warehousing Assignment 1

Objectives

1. Become familiar with the SQL Server environment

2. Understand T-SQL fundamentals

Instructions (please read for each assignment)

1. Create a new Word document. Do not use the current document.

2. Submit a Word document with your name on the cover page

o To create a cover page, click Insert, Cover Page, and select a

cover page)

3. Clearly identify your name and assignment name on the cover page

4. The Word document should contain the requested screenshots and

answers to any questions

5. Each screenshot image must include a caption (e.g., Screenshot1: Create

DB)

6. Professional quality work is expected. Points will be deducted for unprofessional work

Material

• NA

Assignment Details:

Be sure to read the instructions above. This assignment is to practice basic T-SQL syntax and

become generally familiar with SQL Server environment.

1 SQL Basics- Create Database, Table, Views

1.1 Create two (2) databases.

The first DB should be named, “your home town” and the second DB should be named, “your last

name”. The databases should be created with a syntax (not GUI), using the ‘master’ database, and

ensure that there is not already a database with the same name in the system. The syntax should

follow the conventions learned in class. HINT: Use command, IF NOT EXISTS, etc.

Page | 2

Screenshot 1 must show the SQL syntax for both databases, the result, object explorer with both DBs,

and your username (5pts). Please remove sample image before submitting.

1.2 Create Tables

1.2.1 Create Three Tables

Please use the following information (table name, column names, data type and sizes) below to create

four tables your “hometown” database.

Students Courses Instructor Movies

StudentId -Int (PK) CourseId – Int (PK) InstructorID (PK) MovieID (PK)

FirstName - Varchar 50 CourseName - Varchar 50 FirstName - Varchar 50 Title - Varchar 50

LastName - Varchar 50 CourseCode - Varchar 50 LastName - Varchar 50 Genre- Varchar 50

FavPerson - Varchar 50 InstructorID- Int Rating - Varchar 20

Email - Varchar 50

AdmitDate - Date

Screenshot 2 must show the SQL syntax for all tables, the result, object explorer with three tables, and

your username (5pts).

1.2.2 Change the names of the tables Student, Course, Movie

Write and execute T-SQL syntax to change the names of the tables (Student, Course, Movie)

Screenshot 3 must show the SQL syntax for all tables, the result, object explorer with three tables, and

your username (5pts).

Page | 3

1.2.3 Insert data into the tables

Write and run the T-SQL to insert the following data values into the [Student] table

StudentID FirstName LastName FavPerson Email AdmitDate

46601 Britney Dooley Pat McGrath [email protected] 2014/07/16

46602 Jamie Carter Ed Norton [email protected] 2015/05/10

46603 Chizu Orji Lesa B. Roe [email protected] 2018/05/10

46604 Mary Chen John Sims [email protected] 2015/03/12

46605 David Spade Ed Norton [email protected] 2015/03/11

46606 Ken Smith Neal Smatresk [email protected] 2017/04/15

46607 Juan Lorenzo Marie Curie [email protected] 2020/05/11

46609 Sri Ram Albert Einstein [email protected] 2020/04/09

46610 Sam Worth Albert Einstein [email protected] 2021/01/02

46611 Jane Lolo Ed Norton [email protected] 2021/01/02

Screenshot 4 must show the SQL syntax for the task, the result, object explorer with the object(s), and

your username (5pts).

Write and run the T-SQL to insert the following data values into the [Course] table

CourseID CourseName CourseCode InstructorID

466011132 Introduction to Data Warehousing BCIS 4660 678901

450199021 Object-Oriented Programming for Business BCIS 3630 981122

361011132 Basic Information Systems BCIS 3610 872389

Screenshot 5 must show the SQL syntax for the task, the result, object explorer with the object(s), and

your username (5pts).

Write and run the T-SQL to insert the following data values into the [Instructor] table

InstructorID FirstName LastName

678901 Obi Ogbanufe

981122 Ling Ge

872389 Tony Gerth

Screenshot 6 must show the SQL syntax for the task, the result, object explorer with the object(s), and

your username (5pts).

1.2.4 Create Two (2) Views

Write and run the T-SQL to create two (2) views that are based on Student and Course tables.

Name the Views: StudentView, CourseView

Screenshot 7 must show the SQL syntax for the task, the result, object explorer with the object(s), and

your username (2.5pts).

Page | 4

1.2.5 Drop the Views/Table

• Write and run the T-SQL to drop the two views

• Write and run the T-SQL to drop the Movie Table

Screenshot 8 must show the SQL syntax for the task, the result, object explorer with the object(s), and

your username (2.5pts).

2 SQL Basics – Data Retrieval

1. From the [Student] table, write a query to retrieve the student first name, last name, and how

long they’ve been in college.

2. From the [Student] table, write a query to retrieve the students whose favorite person is Ed

Norton.

3. From the [Student] table, write a query to retrieve the students whose IDs are 46605 and 46606

Screenshot 9 must show the SQL syntax for the task, the result, and your username (5pts).

4. From the [Course] table, write a query to retrieve the course taught by Tony Gerth

Screenshot 10 must show the SQL syntax for the task, the result, and your username (5pts).

3 SQL Basics – Filtering /Deleting

1. From the [Student] table, write a query to retrieve student’s first name and last name. Filter and

retrieve only students who have been in school for more than 4 years.

2. From the [Student] table, write a query to delete rows from the table of students who were

admitted in 2021

Screenshot 11 must show the SQL syntax for the task, the result, and your username (5pts).