DAT601 | Jesse Drummond | 2018

Today's lesson not many people have shown up due to our Milestone 2 being due tonight, Todd has opted to instead to an introduction to milestone 3 instead of going over important concepts. Milestone 3 integrates our previous milestones into one master document that described the whole process from conceptual to physical. As a class we are looking at the required transactions for milestone 3, these are the update queries that our system must be capable of, each of these will be separate procedures that produce the required results. Today I have wrapped up my milestone two after much deliberation, I feel that it is not in a perfect state but none-the-less I have to move on to other assignments and the next milestone for this class, my general philosophy was to produce a flexible database that is capable of the functionality the company will require. With this mindset, I have added some extra tables & relationships that other students do not have, whether this is correct or ...

Week twelve notes have been released which are summed up as: Milestone Two feedback SQL server permissions and views Introduction to milestone Three, how dig is the database? In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. Views can also be thought of as virtual tables, they behave like tables but are derived from queries themselves, views are saved within databases and indexed. This allows them to be used much in the same way as regular tables. How do I create a view? CREATE   VIEW  view_name  AS SELECT  column1, column2, ... FROM  table_name WHERE  condition; Once a view is created using this code then it is stored in the database for reuse. Views may be updated to add additional columns as required. A VIEW is generally used: To present the information data from a diffe...

 Today's lesson theme pertains to referential integrity which Todd has in part assumed we already are familiar with from our previous level 5 DAT course although he has left resources within the course content for those who need a refresher on this content. CASCADE on delete is mentioned as a form of aiding in referential integrity if a primary key is set to cascade on delete it will remove all rows from other tables that also include this key as a primary key. Constraint checks are our next topic and these are used to constrain an attribute so that an entry into its field must be between or of a specific value or set of values. After viewing a very insightful video on constrains checks we have been assigned the rest of the class has been assigned to assignment work.

Todays class was Todd drawing a conceptual model on the board and discussing with the class over solving the problems it has, this resulted in a more heated than normal environment but the discussion was insightful none the less.

Today lesson is our first lesson since last Friday due to Todd being sick for the past week, thankfully he has also released the marks. I managed to barely get an A- which I think I could have done better on but from Todd's comments I can tell I didn't pay enough attention to my conceptual ER diagram. Moving forward with the actual lecture content for today Todd is focusing on Sub Queries. A Subquery or Inner query or a Nested query is a query within another SQL query and embedded within the WHERE clause. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. ... A subquery cannot be immediately enclosed in a set function. (https://www.tutorialspoint.com/sql/sql-sub-queries.htm) 18/05/2018 Subqueries can be used in a multitude of different ways, including case statements which can intelligently determine the result of the subquery.

Today's lesson pertains to more SQL language beginning with the GROUP by clauses, little examples are being shown on the projector for us, these kind of physical examples are a lot easier to digest than writing on the whiteboard. Group by must appear after the FROM and WHERE clauses, GROUP by groups rows into subgroups based on values of columns or expressions. Todd's example is used to group columns together to show a sum of total sales for a specific region. Another example of a group by rollup which is described as a niche function which caused some debate in class as to what it actually does. GROUP BY GROUPING SETS is used to allow multiple group-by clauses to operate in a single statement, GROUP BY GROUPING SETS is equivalent to the 'union' of two or more GROUP BY operation in the same result set. Next topic is Views within SQL, which is used to present the information data in a different point of view. This can simplify the access to specific information w...

Continuing from last lesson Todd is continuing with the introduction to SQL language. Following along with MSDN material describing multiple concepts from Distinct & Order by, Joins, etc. Distinct removes repetitions within your query result. Preliminary examples are displaying how not to execute select statement it would appear, considering his on board example is missing some key words he is conveying that the table will just be a mess of cross pollinated data. Instead we want a table which is simple to read. Joins are an important part of SQL query language, these help us join related tables to each other in a multitude of styles that become integral to withdrawing data that is related to each other out of the database. Todd described a multitude of join types and how they work. The important part of this is the results you want to draw out of the datbase, it's important to visualize columns of data you want, from there you can join by the tables you require and then...

Todays classes is focused around getting into using the SQL Management studio software, Todd has described the difference between DDL and DML to us as a refresher and for those who don't know at all. Also described to us is the fundamentals of what a relationship database is with a couple examples presented through a power point presentation. This lesson appears to be highly geared towards the students not already familiar with this information, but is a welcome refresher. We are simply creating a couple tables and populating them, then using some DML to play with this data afterwards, allowing us to understand how SQL works within SQL server management studio. Following this simple task he has further exercises to perform on a more complex database.