What is SQL?

1
What is SQL?

• Query language for structural databases (esp.
  RDB)
• Structured Query Language
• Originated from Sequel 2 by Chamberlin et al
  (1976)
• at IBMs San Jose Research Lab.
• ANSI (American National Standards Institute)
  published
• an SQL standard in 1986

2
Role of SQL?

• Data Definition Language (DDL)
• Interactive data manipulation language (DML)
• Embeded data manipulation language
• View definition
• Integrity
• Transaction Control

3
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)

4
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

5
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()

6
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)

7
More on Choosing Rows of SQL?

• Based on Character Strings
• SELECT fname, lname
• FROM professor
• WHERE lname LIKE hari
• Based on Unknown values
• SELECT fname
• FROM employee
• WHERE salary IS NULL
• Based on Several Search Arguments
• SELECT fname, salary
• FROM employee
• WHERE (major LIKE CS, OR state NY)
• AND (salary gt 25,000)

• Eliminating Duplicates
• SELECT DISTINCT city
• FROM authors
• Sorting
• SELECT fname, lname, salary
• FROM employee
• ORDER BY salary DESC

8
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) gt 30

9
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