SQL

1
SQL

• SQL stands for Structured Query Language
• SQL allows you to access a database
• SQL is an ANSI standard computer language
• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert new records in a database
• SQL can delete records from a database
• SQL can update records in a database

2
History

• Was designed and implemented by IBM Research
  (1986)
• A joint with American National standards
  Institute (ANSI) and International Standards
  Organization (ISO) led to the standard version of
  SQL-86
• A revised and expanded in 1992 called SQL-92.
• Most recent is now SQL-99

3
Definitions and Data Types

• Uses terms table, rows, columns for the formal
  relational model terms relations, tuple, and
  attribute.
• An SQL schema is identified by a schema name,
  including authorization identifier to indicate
  user who owns it and descriptions for each
  element.
• Schema elements include tables, constraints,
  views, domains, and other constructs
• Catalog a named collection of schemas in an SQL
  environment

4
Common Data Types

• char (size) Fixed length character string. Size
  is specified in parenthesis. Max 255 bytes.
• varchar (size) Variable-length character
  string. Max size is specified in parenthesis.
• number (size) Number value with a max number of
  column digits specified in parenthesis.
• date Date value
• number (size, d) Number value with a max number
  of digits of size total, with a max number of
  d digits to the right of the decimal.

5
Creating Tables

• Create Table used to specify a new relation by
  giving it a name, and attributes with initial
  constraints.
• Example CREATE TABLE company.employee
• Company is the schema name
• Employee is the relation name

6
Creating a Table

• Create table myemployees
• (firstname varchar(30),
• lastname varchar(30),
• title varchar (30),
• age number(2),
• salary number(8, 2))

• Creates a table called myemployees
• First name, last name, and title allows 30
  characters each.
• Age is allowed 2 digits
• Salary is allowed 8 digits with 2 decimals

7
Other Functions

• Select allows you to select a certain and
  retrieve data specified.
• Example Select column1
• From TableName
• Where condition
• Select column 1 from the TableName with the
  following condition.

8
Insertion

• Insert into tablename
• (first_column, last_column)
• values (first_value,last_value)
• Insert into employee
• (first, last, age, address, city, state)
• values (James, Tran, 23, 1111 1st street,
  San Jose, California)

• Inserts into specified table name
• Specify all columns inserting to separated by a
  comma.
• Values inserted are specified afterwards
• Strings enclosed in single quotes, numbers are
  not.

9
Updating

• update tablename
• set columnname newvalue
• ,nextcolumn newvalue2
• where columnname
• OPERATOR value
• and or column
• OPERATOR value
• optional

• Update the specified table name
• Set the column specified with new value
• Add in conditionals if needed
• Optional values and input add

10
Deleting

• delete from tablename
• where columnname
• OPERATOR value
• and or column
• OPERATOR value
• optional

• Delete a certain table, column, row, etc.
• Operator meaning gt,lt,, etc

11
Drop

• Dropping a table removes all rows and
  definitions.
• Example Drop table TableName

12
Ordering

• SQL allows you to order elements in a table.
• Example orders by alphabetical
• select distinct customer-name
• from borrower, loan
• where borrower.loan-number loan.loan-number
  and branch-name Perryridge
• order by customer-name

13
Evaluation of GroupBy with Having
14
Aggregates

• More functions that allow you to operate on sets.
• COUNT, SUM, AVG, MAX, MIN
• Produces numbers, not tables.
• Not part of relational algebra
• Example Select MAX (Age)
• From Employee E

15
Grouping

• Sometimes we want to apply aggregate functions to
  subgroups of tuples in a relation.
• Such as finding the average salary of employees
  in each department or the number of employees
  that are working on each project.
• SQL has a GROUP BY clause that allows for
  specific grouping of attributes

16
Grouping Cont

• Example
• SELECT Dno, COUNT(), AVG (Salary)
• FROM EMPLOYEE
• GROUP BY Dno
• The EMPLOYEE tuples are partitioned into groups,
  each group having the same value for the grouping
  attribute Dno.
• Then the COUNT and AVG functions are applied to
  each group.

17
b)
T
T
a)
c)
S
R
A
A1
A1
A2
A2
A2
A3
A4
A5
A
A2
A3
A
A1
A2
A4
A5
A
A1
A2
A3
A4
R(A) EXCEPT ALL S(A)
d)
T
A
A2
A3
R(A) INTERSECT S(A)
Two tables
SQL Multiset Operations
R(A) UNION ALL S(A)
18
Nested Queries

• List all courses that were not taught in S2000
• SELECT C.CrsName
• FROM Course C
• WHERE C.CrsCode NOT IN
• (SELECT T.CrsCode --subquery
• FROM Teaching T
• WHERE T.Sem S2000)
• Evaluation strategy subquery evaluated once to
• produces set of courses taught in S2000. Each
  row
• (as C) tested against this set.