COP 2700 – Data Structures - SQL

1. COP 2700 – Data Structures - SQL Lecture 10 – June 10, 2015 Meta Data/Indexes/Programs Review

2. Announcements • Current Grades have been posted to • wodwhere.com/cop2700/whatsmygrade.aspx • The final exam is Wednesday • 6 to 8 short answer/essay questions (1 pt each) • 3 to 4 what’s wrong with this (1 pt each) • 3 to 4 scenarios (2 pts each) • Exam Grades should be posted by Thursday Evening • Assignment 5 due Saturday Night by 11:59 PM • Please don’t wait until Saturday to start • Grades will be posted to Registration System and WhatsMyGrade by EOD Sunday

3. Tonight • Indexes • Meta-Data • Programming With A DBMS • Review

4. A Quick Word on Indexes • An index is an additional structure in a database that allows for quick access to data based on a key field or fields. • We already know about one index • Primary Key • In SQLServer they are called “Clustered Indexes” • Indexes that point directly to the data in a row of a table • There are also Secondary Indexes • These indexes are more like an Index in a book • For each value of a column, the index points to all records that contain that value. • This may be through direct record pointers into the database or it may have the primary key of the records • There are also Unique Indexes • These are more of a “constraint” than a true index. • They specify a second field or set of fields that must be unique in each row in a table. Insert and Update will not allow a second field (or set of fields) to be stored. • Being a Foreign Key to another table DOES NOT MEAN that the Foreign Key field(s) in the child table is indexed!!

5. Set-Up of a Primary Index

6. What Secondary Indexes Might Make Sense? It all depends upon how often the data needs to be accessed by that field, how many different fields, and how many rows.

7. What’s The Cost of Each Index? • Each Insert will also have to change the index. • Each Update may have to make two changes to an index • Remove and then Insert • Some fields it just doesn’t make sense to index • Yes/No Fields for example • May be quicker to just do a “Full Table Scan”

8. Meta-Data • Meta-Data is a database of database structures • It is set up just like any other database and is used by the database engine when trying to figure out how to execute a DML command • It is how Graphical User Interfaces such as Management Studio are able to show all the information that they show about the data base • To these programs, their “database” IS the meta-database!!

9. Meta-Data • Views Into Meta-Data SQL Server • Sys.Tables • Sys.Views • Sys.Columns (link on Object_ID to Tables or Views) • Sys.Procedures • Sys.Triggers • Sys.Indexes • Let’s Poke Around

10. How can we use the Meta-Database? • We need to get a list of ALL Cities anywhere in our database SELECT 'SELECT ' + c.name + ' FROM ' + t.name + ' UNION ' FROM sys.tables T, sys.columns c WHERE t.object_id = c.object_id and c.name like '%city%‘

11. How can we use the Meta-Database? • The US government has decreed that all City’s in all databases must be at least 100 characters long to include the longest city name in the country. • We need to update out Registration Database to handle this. • Find all tables that include a field that contains City • SELECTColumn_NameFROM sys.tables TJOIN sys.columns c ON t.object_id = c.object_id WHERE c.name LIKE '%city%'

12. How can we use the Meta-Database? • Take this one step further, generate the ALTER statements automagically: SELECT 'ALTER TABLE ' + t.name + ' ALTER COLUMN ' + c.name + ' Varchar(100);' FROM sys.tables T, sys.columns c WHERE t.object_id = c.object_id and c.name like '%city%‘ • Now one can copy and paste this from the results grid to the execute area and run it!!

13. Other Meta-Data Uses • Nice to have when you Don’t have a GUI like Management Studio • Since these are views that are available to anyone, you can access them using the little SQLTool we wrote. • So you can get the definition of any table without a GUI SELECT T.Name, C.* FROM sys.Tables T join sys.Columns C ON t.object_id = c.object_id WHERE T.name = 'Book'

14. Programming Outside Transact SQL • Embedded SQL • Directly embedding SQL commands into a programming language and making use of a pre-compiler to convert the statements to API calls. To the programmer, it is somewhat “seamless”. • Microsoft LINQ • Object oriented set of modified SQL commands used to query databases or self-defining data structures • Application Programming Interfaces (API) • A set of utility subroutines and functions that accept as parameters SQL statements and return result sets, success/fail flags and/or records updated. • Web Services • Dedicated programs written to process specific database instructions used to separate the processing of the database from the program.

15. Microsoft ASP API Implementation • To illustrate the API method of executing SQL commands, I’ll go over two methods of accessing databases from a web page. • Microsoft ASP Web Pages • PHP Web Page

16. OK – Before the Final Review • Please take a few minutes to complete the Instructor Evaluation for this course • Then take a quick break!!

17. Final Exam Review

18. Our “Tools” for solving DML “Problems” • Basic Select Components • Expressions • Conditions • Aggregates • AVG, SUM, MIN, MAX, COUNT • GROUP BY • HAVING • ORDER BY • Joins • Inner • Outer • Full • Cartesian • Sub-queries • IN • Exists • Scalars • Queries on Queries • Queries in Queries • Set Operations • Union • Intersection • Except (Minus)

19. Developing Correct Select Statements • If there are multiple parts of the question or you may need a sub-query or scalar, do one part at a time. • Start with your FROM and list your tables. Set aliases if needed. • Add joins between tables or IN sub-queries. • Usually if you have N tables, you will require at least N-1 joins • THIS IS BIG. REMEMBER IT. N Tables----At Least N-1 joins • Next write down your field list including any aggregates. Will we need Distinct? • Should Union, Intersect, Except be used (or be easier to understand?) • Write any specific conditions • Add any required Group By for Aggregates • Finish it all off with an Order By if needed. • Repeat for each part and then put them together. • Because a query returns no results doesn't mean that it is wrong. No answer is as important as an answer!!

20. Special Circumstances • And with Same Column • Join a table with it self so there are two pairs of columns to compare • Use a sub-query with an AND IN • Use Intersect • All • By Counting • Count whatever the all is and get a scalar of the count. • Count the base and compare with the count of all • Not • Change the NOT part to a positive statement • Use NOT IN or EXCEPT (or NOT EXISTS) • Why can't we just use != in many cases • List Guests that have not stayed in Orlando Hotels: • Select Guest_ID From Stay JOIN Hotel On Stay.Hotel_ID = Hotel.Hotel_ID AND City != ‘Orlando’

21. Special Circumstances • Only • List all students that only got “A”s. • Counting • Except • NOT IN • And Also • Select all Students that got an “A” in a class taught in “Jupiter” vs. • Select all Students that got an “A” in a class AND ALSO took a class in “Jupiter” • Intersect or AND IN – It’s like an AND for same column • Whether or Not (Regardless, List All with a Join) • List all instructors along with their schedules for Spring 2015 • Left Outer Join

22. The Other DML Statements • INSERT • UPDATE • DELETE

23. The Basic INSERT Statement INSERT INTO Table_Name (Field1, Field2,…, FieldN) VALUES (Value1, Value2, …, ValueN) or INSERT INTO Table_Name (Field1, Field2,…, FieldN) SELECT (Field1, Field2,…, FieldN) FROM Tables/Joins/Views WHERE Conditional Statements

24. Insert into STUDENT (STUDENT_ID, LAST_NAME, FIRST_NAME, GENDER, BIRTH_DATE, CITY) Values ('Z131', 'Essen', 'Sarah', 'F', '11-OCT-1995', 'Miami'); Things to Note: Strings are enclosed in single quotes. If the string itself has a single quote, it is replaced with two single quotes in the SQL command. (So, if you want to store D'Vinci in the database, the string would look like: 'D''Vinci' Numbers are not enclosed in quotes and just sit there Dates require a special conversion function to ensure they are formatted corrected. The latest versions of Oracle and SQLServer both recognize 'DD-MON-YYYY' as a date '23-sep-2014' An Example Insert Statement

25. Ordinal vs Nominal Columns • When a table is created, each column is assigned both a column number and a column name. • In the INSERT statement, one can eliminate the Field list creating a statement that looks like this: INSERT INTO Table_Name VALUES (Value1, Value2, …, ValueN) The Values would be lined up with the ordinal columns to complete the command. • ALTHOUGH THIS IS A LOVELY THING, ALL INSERT STATEMENTS USED IN THIS CLASS AND EVERYWHERE ELSE WILL ALWAYS HAVE A FIELD LIST.

26. Insert with Identity Column • If the table to which you are inserting a new row has an Identity column, then that column is skipped on both the field list and in the Values clause. The system will automatically get the next value and update the identity field with the value. • INSERT INTO Employee (Last_Name, First_Name, Department, Boss_ID) • VALUES ('Jetson','George','Future Planning',1)

27. The Basic UPDATE Statement UPDATE Table_Name SET Field1 = Expression1, Field2 = Expression2, …,FieldN = ExpressionN WHERE Conditional Statement(s) The WHERE clause is optional.

28. Simple Update Statements UPDATE Student SET City = 'Ft Lauderdale' WHERE Student_ID = 'Z123‘ The right side of a SET is an Expression, so it can be a calculation or any other combination of stuff including possibly a scalar select. Let’s say that they have decided to double all credit hours for all courses UPDATE Course Set Credit_Hours = Credit_Hours * 2 Let’s say that they have decided to set the credit hours for all courses to the max credit hours of any course UPDATE Course Set Credit_Hours = (Select MAX(Credit_Hours) From Course)

29. The Basic DELETE Statement DELETE FROM Table_Name WHERE Conditional Statement(s)

30. Example Delete Statements Delete From Transcript Where Student_ID = 'Z123' Delete From Student Where Student_ID = 'Z123' Remember, you cannot delete rows from a table that is an active foreign key to another table.

31. WHEREs on Update and Delete • The WHERE clauses on these statements can be as complicated as those in a SELECT and include sub-queries and references to other tables. • Although the WHERE clauses can mention other tables, the UPDATE, DELETE and INSERT statements are only for one specific table. • A couple of you started using a version of the UPDATE statement that linked with another table using a FROM • OK – It is allowable, but not very likable.

32. Transactions • BEGIN TRANSACTION • Do a bunch of stuff. • The stuff may lock a row in the database, may lock a table or could lock the entire database • COMMIT or • ROLLBACK • In SQLServer you are always committing each statement UNLESS you are in a BEGIN TRANSACTION block

33. Changing the Structure of a Database • The book is rather nonchalant about the process and makes it sound like it happens all the time. • Adding columns to a table, adding tables, increasing the size of columns, and stuff like that happens all of the time as requirements change. • But, normally, once a table has been added to the database or a column has been added to a table, developers (and DBAs) choose not to actually remove them from the database or change their types. • Why do you think that is the case. • There are of course less stringent rules when a new system is being developed BEFORE a database is moved to production, but once in production…. • Does everyone always do this, NOPE.

34. Changing Database Structure • Super Delete • We've played a little bit with the Delete command and realize that: • Delete Table_Name • will delete all rows in a table. • Another way of doing this is with the use of the Truncate command • Truncate Table_Name • The big difference is that with the Delete, each row is independently deleted from the database, and the delete is recorded in log records. Also, Triggers (that we don't know what they are yet) are fired and Identity is retained. • With a Truncate, the pointer to the table is just set to the beginning of the table, no triggers are fired and Identity is reset.

35. ALTER TABLE command • Most structural changes we will be doing will probably be using the ALTER TABLE command. • There are hundreds of other DDL commands with different syntaxes for each command, and slightly different syntaxes from database to database. • The major ones start with • CREATE • ALTER • DROP • Followed by what you what to create, alter or drop • Unless you are just playing around or know exactly what you really want to do DON’T DROP

36. Add/Changing a Column Definition ALTER TABLE table_name ALTER COLUMN column_name data type and new configuration We want to change the size of the Price of a Book because $100 books are a comin’: ALTER TABLE Book ALTER COLUMN Price Decimal(5,2) NULL; • In many cases this changes the definition, but not the actual data. That gets changed the next time a row gets updated. • Making stuff bigger isn’t an issue, but you will be warned if you make things smaller and there are rows out there with bigger data. If it finds data that would be truncated, it will not allow the update to take place. • How could we make this happen if we have to?? • Adding NOT NULL when there are rows where the columns is null won’t work either!!

37. Add/Changing a Column Definition • To Add new columns we can: ALTER TABLE Table_Name ADD Column_Name1 Data Type other definitions, Column_Name2 Data Type other definitions;

38. Checking out the complete definition for a table • Different RDMSs have different functions for this • In SQLServer you can use: • sp_help Table_Name

39. What Does That DBO Mean?? • SQLServer has a way to group tables into what they call “Schemas” • They can then use this when applying security controls over who can do what • DBO is the default “Schema” that is used if you don’t create or specifically assign another one.

40. Adding a Foreign Key ALTER TABLE Table_Name WITH CHECK ADD CONSTRAINT Key_Name FOREIGN KEY(Column_Name) REFERENCES Alias_Table (Column_Name)

41. “Pausing” a Foreign Key • For some situations it may be required that a change to a primary key of a table be performed. If that key is being used as a foreign key to another table or tables, then the change may be required to be done for several tables at the same time. • In SQLServer, to pause a Foreign key, you are really disabling a Contraint: • ALTER TABLE table_name • NOCHECK CONSTRAINT fk_name; • If you did not specifically name your foreign key, you will have to get it from your Management Studio

42. “Pausing” a Foreign Key • Joy Woodworth got married to Victor Stealcost, and we need to change her Instructor_ID to Stealcost. Here's what we have to do… • Start a transaction • Disable the key • Change the Instructor Table • Change the Schedule table • Enable the key • Why would we want to do this in a transaction?

43. What is a View • A View is a stored select statement that can be given a name and then (in most cases) used like a table. • There are usually three good reasons why one creates a view: • For convenience – You have found that you continuously use the same set of joins over and over again, and are tired of typing it out. • For security – You need to able to give a limited view of the data to someone else without some sensitive information. • For legacy support – This is beyond this course, but it is possible to use views to support older table names that had been dropped from the database, but programs still exist that access the old names. • Also, for stupidity might be added. Someone that you need to access the tables doesn’t understand joins and such, so you write a View.

44. Creating a View • We need to give someone access to the Student table that is not allowed to see a student’s Birthdate: CREATE VIEW STUDENT2 AS SELECT Student_ID, Last_Name, First_Name, City FROM Student • We seem to continuously need to run queries to get a student’s current schedule: CREATE VIEW Current_Schedule AS SELECT Student.Student_ID, Last_Name, First_Name, Student.City as StudentCity,SCHEDULE.SEMESTER, SCHEDULE.COURSE_ID, SCHEDULE.SECTION, COURSE.COURSE_NAME, COURSE.CREDIT_HOURS, ROOM, CLASS_TIME, SCHEDULE.CITY as Course_City FROM STUDENT JOIN TRANSCRIPT ON STUDENT.STUDENT_ID = TRANSCRIPT.STUDENT_ID JOIN SCHEDULE ON TRANSCRIPT.SCHEDULE_ID = SCHEDULE.SCHEDULE_ID JOIN COURSE on SCHEDULE.COURSE_ID = COURSE.COURSE_ID WHERE GRADE IS NULL

45. Using a View • Once a View is created, it can be used in a SELECT statement just like it was a table. • You can even join the view with other tables or views if you need to. • There is limited support for using the Insert, Update or Delete command that will be covered in a more advanced class. • Just as an aside, one way is to use an INSTEAD OF trigger!!

46. What is missing from SQL to make it a true Programming Language? • Variables • Conditional Statements • If, Else, Switch/Case • Iterative Statements • While • Traversing a “file” (which in this case is a Query Result Set) • Communicate with Outside World

47. These items were added to a program language named Transact/SQL • SQL Server specific, although Oracle and MySQL have a similar set of programming commands. • These commands are then used to code: • Scripts – Programs stored as text files and then processed through either the GUI, or oSQL (DOS Shell Script Processor) • Functions – Small programs that accept parameter input and output a result. • Stored Procedures – Programs that can be called from other programs, Management Studio or oSQL • Triggers – Small programs “fired” when certain actions are taken. Usually involved in ensuring data integrity.

48. Variables • Variable Names in SQLServer Script language must begin with the “@” sign. • To create a variable, one uses the Declare statement along with a data type: • DECLARE @AvgCost Numeric (18,4); • A variable can be initialized in the declare • DECLARE @AvgCost Numeric (18,4) = 0; • Multiple variables can be created using one Declare statement: • DECLARE @AvgCost Numeric(18,4) = 0, @TotalCost Numeric(18,4) = 0;

49. Assigning Variables a Value • The SET command is used to assign a value to a variable • SET @AvgCost = 12.4; • SET @AvgCost = @TotalCost / @ItemCount; • The left of the = is the variable name, and the right is a valid expression that results in the same variable type • @SET @AVGCost = ‘Too Much’; Won’t work since ‘Too Much’ is a string, and @AVGCost is a Number

50. Communicating with Outside World • The Print Command can output a string to the Message Area. • It can also force a message to be displayed in the event of an error. • Print {String Expression}