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
• Its 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 lt, gt,
  lt, gt 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/cont
  ent/ILLiadDatabaseDiagram.pdf
• http//www.atlas-sys.com/documentation/illiad/cont
  ent/ILLiadSSSDiagram.pdf
• http//www.atlas-sys.com/documentation/illiad/cont
  ent/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 lt '1/1/06') AND
  (t.transactionDate gt '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 lt '1/1/06') AND
  (t.transactionDate gt '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 dont 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 didnt 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?id2537
• 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_at_ohsu.edu
• Questions? Insults?