CIT178 Relational Databases Online

Livestream Office Hours Spring 2022:   Sunday 7:00pm-10:00pm,  Monday and Wednesday 5:15pm-6:15pm or by appointment

I will not be in Zoom outside of class and office hours.  If you select the link and I am not in Zoom yet, you will be placed into a waiting room.  Please be patient and wait for office hours or class to begin.

NOTE:  This is a webp file that you can download and view in your browser.

NOTE:  This is a webp file that you can download and view in your browser.

This video is part two of a four-part series on SQL Data Definition and Data Manipulation commands. In this video we focus on the ALTER TABLE statement, using it to and remove columns, and various table-based constraints like CHECK, UNIQUE and the tricky FOREIGN KEY.

This video is part three of a four-part series on SQL Data Definition and Data Manipulation commands. In this video we focus on the 4 statements that make up the data management / CRUD operations: INSERT, SELECT, UPDATE and DELETE. In addition we continue to improve the SQL script for our fictitious company, Spiffy Lube.

NOTE:  The video shows how to remove constraints in order to update tables

This video is part three of a four-part series on SQL Data Definition and Data Manipulation commands. In this video we focus on the 4 statements that make up the data management / CRUD operations: INSERT, SELECT, UPDATE and DELETE. In addition we continue to improve the SQL script for our fictitious company, Spiffy Lube.

NOTE:  The video shows how to remove constraints in order to update tables

NOTE:  If you have a current LinkedIn profile, you do NOT need to create a new one.  Just add a link to your final project repository as indicated in the instructions.   

Section #3 of milestone #3

Choice between SPs, Functions, Views, and Triggers

To see a video explaining how to use CHOOSE, watch:  https://youtu.be/pUQMnVkr71s

To see a video explaining how to use CASE with GROUPING and ROLLUP, watch:  https://youtu.be/hN-QWoorRzg

This is section #2 of milestone #3.

IMPORTANT:  Task #3 is the only one that requires subqueries!   The rest of the assignment is a review of information we have covered throughout the semester!

PLEASE READ:  After you create the testcorp database, when you query the Order table, you will need to put it in brackets to use the table because SSMS thinks it is a keyword.   Use [Order] when you refer to the table and everything should work fine.

Example:  SELECT * FROM [Order]

Milestone #3 has 3 different sections.  This is section #1.

Play the game and test your knowledge

10 points extra credit

Make sure you have gotten feedback on final project milestone #1 and incorporated any changes into your tables BEFORE you begin working on milestone #2!

With ties looks for values that match the last item in the list and displays them.  It makes  the most sense for rankings. For example, if you wanted to know the top 5 GPAs in a graduating class, you would want to know if other students had the same GPA as the 5th student (they should all have the 5th rank).   It should be noted that SQL uses the column specified in the ORDER BY clause to determine which rows have "tie" values.

Topics:  

• SQL Home
• SQL Intro
• SQL Syntax
• SQL Select
• SQL Select Distinct
• SQL Where
• SQL And, Or, Not
  
• SQL Order By
• SQL Select Top
• SQL Like
• SQL In
• SQL Between

Please print for future reference

Download the WideWorldImporters-Standard.bak  which is near the bottom of the list of files to download.

Be sure to download the OLTP version of this database backup and - for easiest access - to your SSMS backup folder. If you are unsure where this is, simply start to do a database restore (right-click Databases in SSMS Object Explorer) to find the correct folder. Save the backup there and SSMS will not have a problem with access.

Follow the instructions on this page to obtain a free LucidChart Educational Account.

Microsoft Corporation offers a free copy of MS Office 365 for students.  This includes Word, Excel, PowerPoint and Access.  

This is an excellent opportunity for students!

FreeDataGenerator.com