Transcript No Slide Title

What is SQL?
• Query language for structural databases (esp. RDB)
Structured Query Language
• Originated from Sequel 2 by Chamberlin et al (1976)
at IBM’s San Jose Research Lab.
• ANSI (American National Standards Institute) published
an SQL standard in 1986
1/18/00
CSE 711 data mining
1
Role of SQL?
• Data Definition Language (DDL)
• Interactive data manipulation language (DML)
• Embeded data manipulation language
• View definition
• Integrity
• Transaction Control
1/18/00
CSE 711 data mining
2
Information Retrieval of SQL?
select A1, A2, . . ., An
from r1, r2, . . ., rm
where p
• The select clause is used to list the attributes desired in the
result of a query
(specifies the columns)
• The from clause lists the relations to be scanned in
the evaluation of the expression
(specifies the tables)
• The where clause consists of a predicate involving
attributes of the relations that appear in the from clause.
(specifies the rows)
1/18/00
CSE 711 data mining
3
Column Manipulation of SQL?
• Choosing Columns
SELECT fname, salary
FROM employee
• Rearranging the Order of Columns
SELECT salary, fname
FROM employee
• Insert Literals
SELECT fname, ‘Salary: ’, salary
FROM employee
• Changing Column Headings
SELECT First_name = fname, salary
FROM employee
1/18/00
CSE 711 data mining
4
Manipulating Data of SQL?
• Manipulating Numerical Data (Arithmetic op., Math. Func.)
SELECT fname, (salary * 1.1)
FROM employee
• Manipulating Character Data (String func.)
SELECT SUBSTRING(fname,1,1), salary
FROM employee
• Manipulating Datetime Data
SELECT fname, birthdate, DATEDIFF(year, birthdate, getdate())
FROM employee
• System Functions
SELECT ‘database’ = db_name(),
‘user’ = user_name(), login = suser_name()
1/18/00
CSE 711 data mining
5
Choosing Rows of SQL?
• Based on Comparisons
SELECT fname, (salary * 1.1)
FROM employee
WHERE state = ‘NY’
• Based on Ranges
SELECT fname, (salary * 1.1)
FROM employee
WHERE birthdate BETWEEN ‘1/1/1950’ and ‘12/31/1970’
• Based on Lists
SELECT fname, salary
FROM employee
WHERE major IN (‘CS’, ‘CSE’, ‘ECE’, ‘IS’)
1/18/00
CSE 711 data mining
6
More on Choosing Rows of SQL?
• Based on Character Strings
• Eliminating Duplicates
SELECT fname, lname
FROM professor
WHERE lname LIKE ‘%hari%’
• Based on Unknown values
SELECT DISTINCT city
FROM authors
• Sorting
SELECT fname
FROM employee
WHERE salary IS NULL
SELECT fname, lname, salary
FROM employee
ORDER BY salary DESC
• Based on Several Search Arguments
SELECT fname, salary
FROM employee
WHERE (major LIKE ‘CS%’, OR state =‘NY’)
AND (salary > 25,000)
1/18/00
CSE 711 data mining
7
Aggregate Functions of SQL?
• Generate Summary Values
avg, count, max, min, sum, etc.
(New “Group by” and “Having” clauses)
SELECT title_id, copies_sold = SUM(qty)
FROM sales
GROUP BY title_id
HAVING SUM(qty) > 30
1/18/00
CSE 711 data mining
8
etc. about SQL?
• Joining Tables
(Inner Joins, Cross Joins, Outer Joins,
Joins with More than Two Tables, Self Joins)
• Nesting SELECT Statements
• Subqueries
• Select Into
• UNION Operator
1/18/00
CSE 711 data mining
9