Writing Illiad SQL Queries

1. Writing Illiad SQL Queries …or how I learned to stop worrying and love the result set

2. Why Write SQL? • Need data in machine readable format • Need to manipulate data • Want automated or triggered queries • Want to build a user friendly, abstracted front end to your data • SQL is a standard • It’s fun and will impress your friends at parties!

3. Tools for writing SQL queries • Query Analyzer (MS standard) • Access (GUI, but adds nonsense to your SQL) • Toad (free version! www.toadsoft.com) These programs allow you to write ad-hoc SQL queries. Often, when you have a finished query, you will be running it in a programming/scripting language outside of them. You use Enterprise Manager to administer the SQL Server (add logins/users, administer rights, etc)

4. MS SQL Server Security Basics • Two stages of authentication: • Login: used to connect to SQL Server • User: user account granted rights to database objects (tables + other fun stuff) • Users can be SQL Server Users OR NT/Win2K Domain Users • SQL Server Users: maintained in DB (live in dbo.master) • Domain Users: Users already authenticated on your windows network

5. MS SQL Server Security Basics Logins in Enterprise Manager DB Users in Enterprise Manager

6. MS SQL Server Security Basics • Choosing to use SQL Server Users (login/pass) vs Windows Domain Users depends on your needs, however Domain users are more secure, create accountability, and can be easier to maintain.

7. MS SQL Server Security Basics • Roles = fixed sets of rights that can be assigned to Users at DB level. Common ones: • Public (equivalent to “everyone” group) • db_owner = has full rights • db_dataread = SELECT rights on all tables • db_datawriter = modify rights on all tables • In additon, DBAs will often have the “sysadmin” role associated with their login (gives full rights to SQL Server)

8. Diving into SQL • SQL = Structured Query Language • ANSI compliant standard • More than just queries: can modify data through the Data Manipulation Language (DML) set: • SELECT (extract records from table) • INSERT INTO (insert new records into table) • UPDATE (update records in table) • DELETE (delete records from table)

9. SELECT Statement • Basic SELECT statement syntax: • SELECT field1, field2 FROM table_name • Simple Illiad Queries: SELECT * FROM UsersAll SELECT LastName, FirstName, EMailAddress FROM UsersAll * = wildcard for select all fields • the second query specifies only three fields to return • query returns a result set, or table of data

10. WHERE Clause • What if I want conditionality? use the WHERE clause: SELECT LastName, FirstName, Department FROM UsersAll WHERE Status = 'Faculty/Staff' Returns all users specified as ‘Faculty/Staff’ Use single quotes (‘) to denote strings, do not use for number values

11. WHERE Clause • Other operators for WHERE clause include <, >, <=, >= and LIKE (which allows wildcards): SELECT LastName, FirstName, Number FROM UsersAll WHERE Number LIKE ‘20000%’ • This query returns all users with a Number (barcode) starting with 20000 (they have an OHSU library card) • % = wildcard, use anywhere in string (can also use more than one)

12. JOINing Tables • JOINING tables (i.e.: one result set for multiple tables) • Done in WHERE clause, or in FROM clause (more ANSI compliant) • Links two or more tables utilizing a primary key (a unique row in one table) that correlates to one or more rows in another table • What follows are the table relationships of the Illiad DB…

13. JOINing Tables

14. JOINing Tables • Yeah, I know they are too small, you can view the real deals at: • http://www.atlas-sys.com/documentation/illiad/content/ILLiadDatabaseDiagram.pdf • http://www.atlas-sys.com/documentation/illiad/content/ILLiadSSSDiagram.pdf • http://www.atlas-sys.com/documentation/illiad/content/ILLiadBillingDiagram.pdf

15. JOINing Tables • JOIN example in WHERE: SELECT * FROM dbo.Transactions t, dbo.UsersAll u WHERE t.Username = u.Username • …in FROM: SELECT * FROM dbo.Transactions t INNER JOIN dbo.UsersAll u ON (t.Username = u.UserName) Notes: - Either usage tells the DB where the join occurs – in other words it creates a ‘virtual table’ for your result set combining the two tables on this column • Shortcut letter after table name makes for less typing later!

16. JOINing Demo Joining Demo: SELECT u.Status, u.Department, t.TransactionDate, t.LoanTitle, t.LoanPublisher, t.LoanDate, t.LoanEdition, t.ISSN, t.LendingLibrary FROM dbo.Transactions t INNER JOIN dbo.UsersAll u ON (t.Username = u.UserName) WHERE LOWER(t.TransactionStatus) = 'request finished' AND LOWER(t.DocumentType) = 'loan' AND LOWER(u.Status) IN ('faculty/staff', 'student', 'distanceeducation') AND ((t.TransactionDate < '1/1/06') AND (t.transactionDate >= '1/1/05'))

17. JOINing Demo • Notes from demo query: • JOIN creates a clean dataset as if the two tables were one • using LOWER function to ensure data consistency • using ANDs to logically specify multiple conditions in my WHERE clause (you can use ORs and NOTs too) • using the IN keyword instead of millions of ANDs where I have a list of values

18. ORDER BY • I am anal and want my result set sorted! • use the ORDER BY clause: SELECT u.Status, u.Department, t.TransactionDate, t.LoanTitle, t.LoanPublisher, t.LoanDate, t.LoanEdition, t.ISSN, t.LendingLibrary FROM dbo.Transactions t INNER JOIN dbo.UsersAll u ON (t.Username = u.UserName) WHERE LOWER(t.TransactionStatus) = 'request finished' AND LOWER(t.DocumentType) = 'loan' AND LOWER(u.Status) IN ('faculty/staff', 'student', 'distanceeducation') AND ((t.TransactionDate < '1/1/06') AND (t.transactionDate >= '1/1/05')) ORDER BY t.TransactionDate, u.UserName • Sorts by first field alphabetically, then second if there are more than one value for the first, etc…

19. Dupe Records • Wait a minute buddy, I am getting duplicate records! • The DISTINCT keyword tells SQL Server to only return unique rows: SELECT DISTINCT a.Type AS 'Type', u.Username as 'UserName', u.LastName as 'LastName', u.FirstName as 'FirstName', u.Status as 'Status', u.EMailAddress as 'EmailAddress' FROM UsersAll u JOIN UserAccountsLink l ON (u.UserName = l.UserName) JOIN UserAccounts a ON (l.InternalNo = a.InternalNo) WHERE u.NVTGC = 'ILL' AND u.Cleared = 'Yes' And a.Active = 'Yes' AND a.Type like 'VISA%' ORDER BY u.LastName • So, in this instance, it says only give us rows in our results set that are unique (go ahead, be elitist!)

20. Manipulating Data • INSERT, UPDATE, DELETE statements are used for manipulating data: • INSERT to add new records to table(s) • UPDATE to modify existing record(s) • DELETE to remove records.

21. INSERT INTO • INSERT example (I don’t have a tested example for use with Illiad, but here is the context): INSERT INTO table_name (column1, column2,....) VALUES (value1, value2,....) • Here we are saying insert value1, value2, … into colum1, column2, … of table table_name

22. UPDATE • UPDATE example (setting user accounts to inactive who have visa cards): UPDATE UserAccounts SET UserAccounts.Active = 'No' WHERE UserAccounts.InternalNo IN (SELECT a.InternalNo FROM UserAccounts a JOIN UserAccountsLink l ON (l.InternalNo = a.InternalNo) JOIN UsersAll u ON (u.UserName = l.Username) WHERE u.NVTGC = 'ILL' And a.Active = 'Yes' AND a.Type like 'VISA%') • The embedded SELECT query runs first returning a result set of user accounts in our site (ILL), which are Active (Yes) and the type of their account is VISA (wildcard to catch spaces and other funky characters) • …THEN the outside UPDATE query sets the field Active for each of those user account to ‘No’

23. DELETE • DELETE example (delete accounts for users with visa cards): DELETE FROM UserAccounts a2 WHERE a2.InternalNo IN (SELECT a.InternalNo FROM UserAccounts a JOIN UserAccountsLink l ON (l.InternalNo = a.InternalNo) JOIN UsersAll u ON (u.UserName = l.Username) WHERE u.NVTGC = 'ILL' And a.Active = 'Yes' AND a.Type like 'VISA%') • Here we are again using an embedded SELECT query to find all the user accounts with VISA cards in our site (same as before) • …then we use a simple delete query to remove those records from the DB

24. What do I do with my SQL? • Limitless options. Common ones: • Create an Access view (easy) and show staff how to run that view on their own (giving you more YOU time so you can work on your golf handicap) • Create a stored procedure or user defined function (UDF – more difficult). This abstracts your query (and data) and makes it simple to integrate into things like web front ends (ASP, PHP, etc) or scripting languages (visual basic, perl) • Use those applications and scripts to give users the data and reports they need from a GUI interface

25. A Few Resources Online • Resources online (I didn’t make this stuff up, I stole it): Straight forward/no nonsense SQL reference (geared towards web work): http://www.w3schools.com/sql/default.asp SQL Server security model and security best practices, tips: http://vyaskn.tripod.com/sql_server_security_best_practices.htm Should I use a view, a stored procedure, or a user-defined function? http://www.aspfaq.com/show.asp?id=2537 MSDN reference for SQL syntax, Server functions, T-SQL, etc…: http://msdn.microsoft.com/library/en-us/tsqlref/ts_tsqlcon_6lyk.asp

26. End • Contact: Nick Peterson Oregon Health & Science University Library Computer User Support Analyst Email: peterson@ohsu.edu • Questions? Insults?