SQLSyBase Programming

1
SQL/SyBase Programming
2
A Database is...

• A collection of related tables containing data
  and definitions of database objects.
• The table is a paradigm which describes the
  data stored within the database.

3
The Relational Model

• Codds 12 Rules
• Information rule - everything is a table
• Data independence
• Comprehensive data sublanguage
• Support relational operations (select, project,
  join)
• View updating rule
• Systematic null value support
• Integrity independence

4
Tables

• Tables contain
• Rows Records
• Columns Fields
• Primary Key Unique Identifier
• Data Elements Value

5
Occupation Table
6
Two Kinds of Tables

• User Tables
• contain user loaded data
• primary query tables
• created, modified, maintained by user
• System Tables
• contain database and table descriptions
• maintained by database system
• can be queried like any other table

7
Structured Query Language(SQL)

• SQL specifies syntax features for retrieval
  update and definition of the database.
• SELECT - query data in the database
• INSERT - Add a single row
• UPDATE - Alter attribute values in rows
• DELETE - Delete rows of data from a table

8
Select Statement

• Its syntax is
• SELECT (select-list - attributes or derived data)
• FROM (table name or names)
• WHERE (sets up conditions)
• SELECT and FROM are required
• SELECT Name FROM OCCUPATIONS
• WHERE ID 101

9
Select Examples
PART
SELECT FROM PART Selects all column values for
all rows SELECT PARTNO, PNAME FROM PART
WHERE PRICE gt 0.2 Selects rows where
price gt .2 SELECT PARTNO, PNAME FROM PART
WHERE PNAME LIKE (CAR') Selects rows
where pname has a value starting with CAR
10
Insert Statement

• This command allows data to be inserted, one row
  at a time, into an existing table
• Syntax Insert into lttablenamegt (list of
  attributes) values (list of values)
• Note The list of attributes can be ignored
  providing the order of attribute values, and
  completeness of attribute instances, is as per
  the table list.
• example insert into emp(name, sal, byear)
• values(Jones, Bill,
  45000,1967)
• or(see note) insert into emp values(Jones,
  Bill, 45000,1967)

11
Update Statement

• UPDATE tablename
• SET colname expression , colname
  expression
• WHERE search_condition
• Replaces values of the specified columns with
  expression values for all rows satisfying the
  search-condition.
• Expressions in the set clause may be constants or
  column values from the UPDATE tablename or FROM
  tablename
• Example UPDATE PART
• SET price price 1.1
• WHERE price lt 20

12
Delete Statement

• DELETE FROM tablename
• WHERE search-condition
• Delete one or many rows in a table. In general
  search-condition and qualification may not
  involve a sub select on tablename.
• DELETE FROM PART WHERE qoh lt 4.00

13
Joining Tables
EMP
DEP
SELECT e.empno AS Number, e.ename AS Name,

d.dname AS Department FROM emp e,
dep d WHERE e.deptno d.deptno
14
A Search and Join Condition
For each prime minister born in or after 1900,
give his name, birth year and party.
SELECT P.PM_NAME, BIRTH_YR, PARTY FROM
PRIME_MINISTER P, MINISTRY M WHERE
P.PM_NAME M.PM_NAME AND BIRTH_YR gt 1900
PM_NAME BIRTH_YR PARTY Holt H E
1908 Liberal Holt
H E 1908
Liberal McEwen J 1900
Country Gorton J G 1911
Liberal Gorton J G
1911 Liberal Gorton J G
1911 Liberal