Transcript presentation source - Courses

SQL Commands
University of California, Berkeley
School of Information Management and
Systems
SIMS 257: Database Management
9/30/1999
SIMS 257: Database Management -- Ray Larson
Review
• Relational Algebra
• Relational Calculus
9/30/1999
SIMS 257: Database Management -- Ray Larson
Relational Algebra Operations
•
•
•
•
•
•
•
•
Select
Project
Product
Union
Intersect
Difference
Join
Divide
9/30/1999
SIMS 257: Database Management -- Ray Larson
Relational Calculus
• Relational Algebra provides a set of explicit
operations (select, project, join, etc) that can
be used to build some desired relation from
the database.
• Relational Calculus provides a notation for
formulating the definition of that desired
relation in terms of the relations in the
database without explicitly stating the
operations to be performed
• SQL is based on the relational calculus.
9/30/1999
SIMS 257: Database Management -- Ray Larson
SQL
• Structured Query Language
• SEQUEL from IBM San Jose
• ANSI 1992 Standard is current version
(SQL92)
• Basic language is standardized across
relational DBMSs. Each system may have
proprietary extensions to standard.
9/30/1999
SIMS 257: Database Management -- Ray Larson
SQL Uses
• Database Definition and Querying
– Can be used as an interactive query language
– Can be imbedded in programs
• Relational Calculus combines Select,
Project and Join operations in a single
command. SELECT.
9/30/1999
SIMS 257: Database Management -- Ray Larson
SELECT
• Syntax:
– SELECT [DISTINCT] attr1, attr2,…, attr3
FROM rel1 r1, rel2 r2,… rel3 r3 WHERE
condition1 {AND | OR} condition2 ORDER
BY attr1 [DESC], attr3 [DESC]
9/30/1999
SIMS 257: Database Management -- Ray Larson
SELECT Conditions
•
•
•
•
•
•
= equal to a particular value
>= greater than or equal to a particular value
> greater than a particular value
<= less than or equal to a particular value
<> not equal to a particular value
LIKE “*term*” (may be other wild cards in other
systems)
• IN (“opt1”, “opt2”,…,”optn”)
• BETWEEN val1 AND val2
• IS NULL
9/30/1999
SIMS 257: Database Management -- Ray Larson
Relational Algebra Selection
using SELECT
• Syntax:
– SELECT * WHERE condition1 {AND | OR}
condition2
9/30/1999
SIMS 257: Database Management -- Ray Larson
Relational Algebra Projection
using SELECT
• Syntax:
– SELECT [DISTINCT] attr1, attr2,…, attr3
FROM rel1 r1, rel2 r2,… rel3 r3
9/30/1999
SIMS 257: Database Management -- Ray Larson
Relational Algebra Join using
SELECT
• Syntax:
– SELECT * FROM rel1 r1, rel2 r2 WHERE
r1.linkattr = r2.linkattr
9/30/1999
SIMS 257: Database Management -- Ray Larson
Aggregate Functions
•
•
•
•
•
Count
Avg
SUM
MAX
MIN
9/30/1999
SIMS 257: Database Management -- Ray Larson
Using Aggregate functions
• SELECT attr1, Sum(attr2) AS name
FROM tab1, tab2 ...
• GROUP BY attr1, attr3 HAVING condition;
9/30/1999
SIMS 257: Database Management -- Ray Larson
Using an Aggregate Function
• SELECT DIVECUST.Name, Sum([Price]*[qty]) AS
Total
• FROM (DIVECUST INNER JOIN DIVEORDS ON
DIVECUST.[Customer No] = DIVEORDS.[Customer
No]) INNER JOIN DIVEITEM ON DIVEORDS.[Order
No] = DIVEITEM.[Order No]
• GROUP BY DIVECUST.Name
• HAVING (((DIVECUST.Name) Like "*Jazdzewski"));
9/30/1999
SIMS 257: Database Management -- Ray Larson
Create Table
• CREATE TABLE table-name (attr1 attrtype PRIMARYKEY, attr2 attrtype,…,attrN attr-type);
• Adds a new table with the specified
attributes (and types) to the database.
9/30/1999
SIMS 257: Database Management -- Ray Larson
Access Data Types
•
•
•
•
•
•
•
•
•
Numeric (1, 2, 4, 8 bytes, fixed or float)
Text (255 max)
Memo (64000 max)
Date/Time (8 bytes)
Currency (8 bytes, 15 digits + 4 digits decimal)
Autonumber (4 bytes)
Yes/No (1 bit)
OLE (limited only by disk space)
Hyperlinks (up to 64000 chars)
9/30/1999
SIMS 257: Database Management -- Ray Larson
Access Numeric types
• Byte
– Stores numbers from 0 to 255 (no fractions). 1 byte
• Integer
– Stores numbers from –32,768 to 32,767 (no fractions) 2 bytes
• Long Integer
• Single
(Default)
– Stores numbers from –2,147,483,648 to 2,147,483,647 (no
fractions). 4 bytes
– Stores numbers from -3.402823E38 to –1.401298E–45 for
negative values and from 1.401298E–45 to 3.402823E38 for
positive values.
4 bytes
• Double
– Stores numbers from –1.79769313486231E308 to –
4.94065645841247E–324 for negative values and from
1.79769313486231E308 to 4.94065645841247E–324 for
positive values.
15
8 bytes
• Replication ID
– Globally unique identifier (GUID)
9/30/1999
SIMS 257: Database Management -- Ray Larson
N/A
16 bytes
Oracle Data Types
•
•
•
•
CHAR (size) -- max 2000
VARCHAR2(size) -- up to 4000
DATE
DECIMAL, FLOAT, INTEGER, INTEGER(s),
SMALLINT, NUMBER, NUMBER(size,d)
– All numbers internally in same format…
• LONG, LONG RAW, LONG VARCHAR
– up to 2 Gb -- only one per table
• BLOB, CLOB, NCLOB -- up to 4 Gb
• BFILE -- file pointer to binary OS file
9/30/1999
SIMS 257: Database Management -- Ray Larson
Creating a new table from
existing tables
• Syntax:
– SELECT [DISTINCT] attr1, attr2,…, attr3
INTO newtablename FROM rel1 r1, rel2 r2,…
rel3 r3 WHERE condition1 {AND | OR}
condition2 ORDER BY attr1 [DESC], attr3
[DESC]
9/30/1999
SIMS 257: Database Management -- Ray Larson
Alter Table
• ALTER TABLE table-name ADD
COLUMN attr1 attr-type;
• … DROP COLUMN attr1;
• Adds a new column to an existing database
table.
9/30/1999
SIMS 257: Database Management -- Ray Larson
INSERT
• INSERT INTO table-name (attr1, attr4,
attr5,…, attrK) VALUES (“val1”, val4,
val5,…, “valK”);
• Adds a new row(s) to a table.
• INSERT INTO table-name (attr1, attr4,
attr5,…, attrK) VALUES SELECT ...
9/30/1999
SIMS 257: Database Management -- Ray Larson
DELETE
• DELETE FROM table-name WHERE
<where clause>;
• Removes rows from a table.
9/30/1999
SIMS 257: Database Management -- Ray Larson
UPDATE
• UPDATE tablename SET attr1=newval,
attr2 = newval2 WHERE <where clause>;
• changes values in existing rows in a table
(those that match the WHERE clause).
9/30/1999
SIMS 257: Database Management -- Ray Larson
DROP Table
• DROP TABLE tablename;
• Removes a table from the database.
9/30/1999
SIMS 257: Database Management -- Ray Larson
CREATE INDEX
• CREATE [ UNIQUE ] INDEX indexname
ON tablename (attr1 [ASC|DESC][, attr2
[ASC|DESC], ...]) [WITH { PRIMARY |
DISALLOW NULL | IGNORE NULL }]
9/30/1999
SIMS 257: Database Management -- Ray Larson
Assignment 3
• Read Chapter 9 in Kroenke
• Complete the GROUP 1 questions
• Turn in the SQL answers for questions 9.19.32
9/30/1999
SIMS 257: Database Management -- Ray Larson