Query Languages: SQL CQL

1
Query LanguagesSQL - CQL

• HS Practical Corpus Linguistics
• Tim Ladiges
• WS 2005/ 06

2
Keywords

• Database
• Query
• Structured Query Language
• Corpus Query Language

3
What is a Database?

• Structure to hold data
• Self-describing collection of integrated records
• Administered by Database Management System (DBMS)
• Relational databases are based on tables
• DB stores metadata in data dictionary
  (description of tables, columns, indexes,)

4
What are Queries?

• A formulated request for the search tool
• e.g. to find items from a corpus
• If data in DB satisfies conditions of query, SQL
  retrieves queried data
• Important how to formulate the query that will
  produce the words

5
Database Access

• - - - - - - - - - - - - - - - - - - - - - - - - -
  - - - - - -
• - - - - - - - - - - - - - - - - - - - - - - - - -
  - - - - -
• - - - - - - - - - - - - - - - - - - - - - - - - -
  - - - - - - -

User
Application Programs/Queries
Software to Process Queries/Programs

DBMS
Software to Access Stored Data
Database Definition
Stored Database
Simplified Database Environment (Elmashi, Navathe
2000 6)
6
Structured Query Language (SQL)

• The most common data sublanguage in use today
• Used especially in client/server system
• A relational data definition and manipulation
  language
• Difference to other computer languages
• tell SQL what you want, not how to get it
• DBMS provides what has been queried
• SQL query SELECT ltattribute listgt
• FROM lttable listgt
• WHERE ltconditiongt

7
Components of SQL

• Data Definition Language (DDL)
• ? creation of relational databases
• ? containment hierarchy (tables, schemas,
  catalogs, database)
• Data Manipulation Language (DML)
• Database maintenance
• Data Control Language (DCL)
• security for database

8
Query example in SQL
Goal we want the address of each
employee Method SELECT ColumnName, ColumnName,
... FROM TableName
9
Query example in SQL

• SELECT FirstName, LastName, Address, City, State
  FROM EmployeeAddressTable
• Result of our query

10
Query Example in SQL Conditional Selection

• Goal display only certain rows, which meet query
  criteria
• Method use of WHERE clause and relational
  operators
• Relational operators

11
Query Example in SQL
Goal Last Name of employees who are from
Ohio SELECT LastNameFROM EmployeeAddressTable WH
ERE State Ohio ? any text in statement
always in single quote Result of our query
Scott, Smith 1 further example Goal in a given
table look for employees who earn 50,000 or
more SELECT EMPLOYEEIDNO FROM
EMPLOYEESTATISTICSTABLE WHERE SALARY gt 50000
Result of our query any employee ID numbers
which share a table row with a salary
entry of 50,000 or more
12
SQL on the Internet

• Advantages
• Low cost for the client
• standardized user interface
• standardized client/server relationship
• Disadvantages
• security questions arise

13
Corpus Query Languages (CQL)

• Used to retrieve data from corpora
• Corpus query tool SARA uses CQL
• Tasks
• encoding of syntactic annotations of corpus
• definition of queries
• Depending on tasks and corpora CQL may vary

14
The Corpus Query

• What are we looking for?
• Query for word, syntactic constituents, etc.
• Corpus annotation?
• Plain text, PoS-tagged, syntactically annotated,
  semantic tags
• Output of Corpus Query?
• KWIC, PoS-tags, syntactic structures, lemmas,
  etc.

15
How do I define a CQL query?

• CQL command language which a SARA client program
  uses to communicate with the SARA server
• CQL syntax
• CQL query consists of one or more atomic queries
  like the following
• a word, punctuation mark, or delimited string
  e.g. jam, ?, "Mrs."
• a word-and-POS pair, e.g. "CAN"NN1
• a phrase, e.g. "not on your life"
• a pattern
• an SGML query, that is, a search for a start- or
  end-tag. Attribute values may also be searched
  for
• the wildcard character _, which will match any
  single word.

16
How do I define a CQL query?

• Four unary operators are allowed in CQL
• case The operator makes the query which is its
  operand case-sensitive.
• header The _at_ operator makes the query which is
  its operand search within headers as well as in
  the bodies of texts.
• not The ! operator matches anything which is not
  a solution to the query which is its operand it
  makes no sense unless the query is combined with
  another.
• A CQL expression containing more than one atomic
  query may use the following binary operators
• sequence one or more blanks between two queries
  matches cases where solutions to the first
  immediately precede solutions to the second.
• disjunction The operator between two queries
  matches cases where either query is satisfied.
• join The operator between two queries matches
  cases where both queries are satisfied in the
  order specified the operator between two
  queries matches cases where both queries are
  satisfied in either order.

17
How do I define a CQL query?

• When queries are joined, the scope of the
  expression may be defined in one of the following
  ways
• SGML element A join query followed by a /
  operator and an SGML query matches cases where
  the joined query is satisfied within the scope of
  the SGML element.
• number A join query followed by a / operator and
  a number matches cases where the joined query is
  satisfied within the number of L-words specified.
• Examples
• cat _ dog finds three word phrases of which the
  first word is cat' and the last is dog
• !cat dog finds occurrences of dog' not preceded
  by cat' within the same document
• catdog finds occurrences of cat' followed
  anywhere within the same document by dog'
• catdog finds occurrences of cat' followed or
  preceded by dog' anywhere within the same
  document
• catdog/10 finds occurrences of cat' followed by
  dog' within ten words
• catdog/ finds occurrences of cat' followed by
  dog' within a single ltheadgt element

18
Resources

• Merz, Charlotte Gastreferat zur Vorlesung
  Korpuslinguistik mit und für Computerlinguistik,
  3.12.2002.
• http//www.ifi.unizh.ch/cl/volk/corpling/Vorl_07_
  Korpus-Abfrage.pdf
• Taylor, Allen G. (1998) SQL for Dummies, 3rd
  Edition, Foster City, CA, IDG Books Worldwide,
  Inc.
• Reference Guide to the SARA Windows Client
• http//www.natcorp.ox.ac.uk/getting/chap4.htmFIM
  NU
• Tutorial on SQL
• http//riki-lb1.vet.ohio-state.edu/mqlin/computec
  /tutorials/SQLTutorial.htm