Writing Illiad SQL Queries by sst10642

VIEWS: 14 PAGES: 26

									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.atlas-
    sys.com/documentation/illiad/content/ILLi
    adDatabaseDiagram.pdf
  – http://www.atlas-
    sys.com/documentation/illiad/content/ILLi
    adSSSDiagram.pdf
  – http://www.atlas-
    sys.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,           value2, …   into
     colum1, column2, … of table table_name
                                                           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: peterson@ohsu.edu


• Questions? Insults?

								
To top