Writing Illiad SQL Queries …or how I learned to stop worrying and love the result set 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! 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) 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 MS SQL Server Security Basics Logins in Enterprise Manager DB Users in Enterprise Manager 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. 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) 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) 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 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 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) 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… JOINing Tables JOINing Tables • Yeah, I know they are too small, you can view the real deals at: – http://www.atlassys.com/documentation/illiad/content/ILLi adDatabaseDiagram.pdf – http://www.atlassys.com/documentation/illiad/content/ILLi adSSSDiagram.pdf – http://www.atlassys.com/documentation/illiad/content/ILLi adBillingDiagram.pdf 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! 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')) 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 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… 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!) 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. 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, colum1, column2, … of table table_name value2, … into 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’ 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 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 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 End • Contact: Nick Peterson Oregon Health & Science University Library Computer User Support Analyst Email: [email protected] • Questions? Insults?