Transcript SQL - Department of Computer Science

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
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
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
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.
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
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
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.
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.
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 [ ]
Deleting
delete from “tablename”
where “columnname”
OPERATOR “value”
[and | or “column”
OPERATOR “value”];
[ ] = optional


Delete a certain table,
column, row, etc.
Operator meaning
>,<,=, etc…
Drop


Dropping a table removes all rows and
definitions.
Example: Drop table “TableName”
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.loannumber and branch-name = ‘Perryridge’
order by customer-name

Evaluation of GroupBy with Having
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
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
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.
a)
R
S
A
A
A1
A1
A2
A2
A3
A4
A4
A5
Two tables
SQL Multiset
Operations
b)
T
A
A1
A1
A2
A2
A2
A3
A4
A5
R(A) UNION ALL
S(A)
T
c)
A
A2
A3
d)
R(A)
EXCEPT ALL
S(A)
T
A
A2
A3
R(A)
INTERSECT
S(A)
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.