Docstoc

Ch 7

Document Sample
Ch 7 Powered By Docstoc
					      Ch 7

Introduction to SQL
         Learning Objectives
• The basic commands and functions of SQL

• How to use SQL for data administration (to
  create tables, indexes, and views)

• How to use SQL for data manipulation (to add,
  modify, delete, and retrieve data)

• How to use SQL to query a database to extract
  useful information
                          SQL
--a standard for relational DB
--used as DDL, DML
--portable

Benefits:
• .reduced training costs
• .enhanced productivity
• .application portability
• .multiple vendors
• .application longevity
• .cross systems communications
     CREATE TABLEs:..SEE Pg 223 [RC];
    ORACLE HANDOUT; and lesson 17 (BF)
•   define fields
•   field type
•   length
•   null/not null
•   unique
•   primary key
•   foreign key
            Create Statement
Format:
CREATE TABLE tablename
  (col name datatype col_constraints,
      .
      .
      .
  [primary key block]
  [referential constraint]
•            .
•            .
•            .);
    constraints in Create table
           statements
Constraint clause can constrain a single
  column or a group of columns in a table
• It helps maintain data integrity
  – Primary key
  – Foreign key
  – Check conditions
Two ways to specify constraints


part of col def
  – (col constraint)
at the end of the table
  – (table constraint)
             Primary key
A table can have only ONE primary key
• P.K. can not have NULL values
• for single-column primary key, it can be
  defined with a column
• For composite it must be defined as a
  constraint
WORKER (w-name, age, lodging)

Create table WORKER
 (w_name varchar(25) Primary key,
  age number,
   Lodging varchar(15));
or
Create table WORKER (
  w_name varchar(25),
  age number,
  Lodging varchar(15),
  primary key (name));
ENROLL(st_name, cl_name, grade)

PK is st_name, cl_name

Create table Enrollment
(ST_name varchar(30) not null,
  cl_name varchar(25) not null,
  grade smallint
  primary key (st_name, cl_name));

NOTE: A concatenated (composite) PK must be
 defined at the END
Foreign Key: (SEE ORACLE HANDOUT AND PAGE 241
(RC), LESSON 22 (bf)

• known as referential integrity constraint
ex:
WORKER (name, age, lodging)
LODGING (lodging_name , No_of_rooms,
  lodging_Address)
Create table WORKER
(name...
  age...
  lodging..REFERENCES LODGING);

• will automatically reference PK of
  LODGING relation
or
• foreign key(lodging) references LODGING(lodging_name)
     CHECK constraint: lesson 22
           (bf);227(RC)
format:
   col type CHECK (condition)
• condition is any valid expression that tests True/false.
ex: if age should be between 18 & 65

Create table WORKER
   (name..
   age number check (age between 18 and 65),
   lodging...);
• or age...check (age > 12),
• ex:
• class char(20) not null check (lower(class) in ('so', 'jr', 'sn') )
                   Data type
             (see ORACLE handout)
CHAR(size) May contain any printable character such as 'A', '3', '&', or ' ' (blanks).
  Maximum size: 255 characters. Default is 1 byte. Padded on right with blanks to full
  length of size.

VARCHAR2(size) Variable length character string having a maximum of size bytes (up to
  2000). NOT padded with blanks.

NUMBER For a numeric column of up to 40 digits. May be used with or without a size
  designation. You may use this type for integer fields, or for fields in which you wish to
  insert the decimal point.

NUMBER(size) For a NUMBER column of the specified size.

NUMBER(size, d) For a NUMBER column of specified size with d digits after the decimal
  point. For example, NUMBER(6,2) could contain nothing larger than 9999.99 without
  generating an error message.

DATE The DATE data type is stored in a special internal ORACLE format that includes
  not just the month, day, and year, but also the hour, minute, and second. However,
  for most tables, you will not need to be concerned about the time. For inserting a date
  into a table, use the following format: '10-OCT-06'.
ex:
ORDER(O#, O_date, Promised_date, C#)
CUSTOMER(C#, name, address, discount)

CREATE TABLE ORDER
(ORDERNO NUMBER NOT NULL,
  ORDERDATE DATE,
  PROMISED_DATE DATE,
  CUSTOMER_NO CHAR925) NOT NULL
  REFERENCES CUSTOMER,
  PRIMARY KEY(ORDERNO));
Entering Data in tables: lesson 15 (BF), p230(RC) and
oracle handout

Format:
   INSERT INTO table-name VALUES ( val1, val2, val3...)

•   values must be in the same order as the cols in the table structure
•   char must be in single quotes
•   date must be in quotes and default format (see ORACLE handout)

Ex: ORDER(O#, O_date, Promised_date, C#)

Sql>insert into order values (61396,’6-jun-06', '29-jun-06',1256);

•   you can also insert a NULL value in a column..col will be left empty for this row

Ex:
insert into order
    values(61396,'6-jun-06',null,1256);
                 SQL
SQL can be used to manipulate data (DML)
format:
Sql>SELECT col name(s)
  FROM     table name(s)
  [Where condition(s)]
  [Group BY..][HAVING.]
  [UNION\INTERSECT\MINUS
  [ORDER BY....]
        Assume following tables
Customer
( Cust_numb, Cust_name, Cust_address,
   cust_state,Cust_balance,Credit_limit, sales_numb)



Salesperson
(Sales_numb, sales_name, Sale_add, sales_state, commission_rate,
   sales)



Order
(Order_numb, Order_date, Cust_numb)
     Working with single table
List names and balances of each customer
Sql>Select cust_name, cust_balance
   From Customer;
If you want all attributes

Sql>select * from customer;
* Is a wild card and selects everything
    Remove duplicate entries
Select name of cities our customers live in

Sql>select distinct cust_state
From customer;
LOGICAL TESTS AGAINST A SINGLE VALUE:

Used with-- where clause
Creates a horizontal subsection

Mathematical operators:

=, >, >=, <, <=, <> or !=
List the name of customer whose customer
number is 9833



Sql> select cust_name
From customer
Where cust_numb = 9833;
List the names of salesperson who live in MD



Sql>select sales_name
From salesperson
Where upper(sales_state) = ‘MD’;

Ex: give the customers who ordered before
  February 15, 2007.
Sql>select cust_numb
From order where order_date <’15-Feb-2006’;
LOGICAL operators:

• And
• Or
• Not
List the names of salespeople who live in new york and earn more than
   5% as commission
Sql>select sales_name
   From salesperson
   Where upper(sales_ad) = ‘NEWYORK’ AND commission_rate is >
   5%

List the names of salespeople who do not live in new york and earn
   more than 5% as commission
      Case Sensitivity test
• Upper
• Lower
SPECIAL OPERATORS

•   In
•   Between
•   [not] in
•   [not] between

• Ex:
• WHERE CITY IN ('CHICAGO','BALTIMORE','TAMPA')
• WHERE AGE IN (20,21,22)


same as upper(city) =’CHICAGO’ OR upper(city)
  =’BALTIMORE’ OR upper(city )= ‘TAMPA’
Renaming columns with Aliases/
Can do arithmetic calculations on numeric attributes
useful when doing calculations

format:
Sql>select colname AS alias
from table name
where. ….

Ex: give commission of salespeople.

Sql>select sales_name, sales*commission_rate as commission
   From salesperson;
EX:
List the names of customers whose credit is greater
   than 2000 but less than 10000

Credit between 2000 and 10000
Calculations

+, -, /, *, ^
Order of calculations:
Parentheses,
^,
* and /,
+ and -
             Comparisons
Numeric:
Based on numeric values
Character:
Left to right based on collating sequence of
  the computer

Smithson > Smith
                Special operator
Like
Used to find patterns

        Allows you select tuples that have similar
        characters in strings
        %        Any # of char.
        -        For ONE character

        List names of customers whose names end
        with an s
        SELECT cust_name
        FROM customer
        WHERE lower(cust_name) like ‘%s’;
Find the names of salesperson that have a ‘c’
followed by an ‘s’

SORTING
output from a query can be ordered
• Must always be the last line of a query
• ORDER BY colname                {asc}
                                  {desc}
         default is ascending
Ex: sort all customers by state and descending order of their credit limit
Sql>select *
From customer
Order by cust_state, credit_limit desc;
             Exists condition
Main query
Sub query

If subquery returns values , then ONLY main query
   is executed

Format
Select col(s)
From table name
Where exists ( sub query);
BUILT-IN-FUNCTIONS:

Max
Min
Sum
Avg
Ex:
Select max(credit), cust_name from customer;
Has no meaning because ename is an array and
  max(salary) is a scalar; cannot associate names with
  max(sal)

Can not mix built-in functions with other attributes,
  exception with groups
                         Count

Used to tally number of not-null values

Format:
Count (*)
Count(distinct attributename)
Count (all attribute name)

count the number of customer in maryland or virginia
Sql> select count(*)
       from customer
       where upper(cust_state) In (‘MD’, ‘VA’);
Find maximum credit limit of customer whose
names contain at least one o

Select max(credit_limit), cust_num
From customer
Where upper(cust_name) like ‘%O%’;
Group By

think of grouping as categorizing: P260(RC), lesson 10(bf)
Allows grouping by rows and then calculate statistics on “groups”

group by state
Will group by state

Group by state, city
Will group by state first, then by city within state

Ex: get the maximum credit limit by state

Sql>select credit_limit, cust_state
From customer
Group by cust_state;
Condition on groups: Having clause
Having puts a condition on groups
Ex: select max credit limit by state where
 there are atleast two customers

Sql>select max(credit_limit), cust_state
From customer
group by cust_state
Having count(*) >=2;
         Copying Part of table
Allows you to break tables in parts
Two steps:
  1.Create NEW table
  2. Use SQL to fill up the data

Format:
Insert into target_table_name (attribute names)
From source_table
           Second method
Create new table and select rows directly
 from source table in single query

Format:
Create table target_tablename
Select attribute names
From source_table;
   Create table credit for customers
1. Method
Create table credit
(c_name char(20) primary key,
C_limit number (8,2));

Insert into credit (c_name, c_limit)
Select cust_name, cust_limit
From customer;

2nd Method:
Create table credit as
Select cust_name as c_name, cust_limit as c_limit
From customer;
Altering Table structure (see ORACLE Handout),
lesson 17, 247 (RC)

all changes in table structure are done thru
  ALTER command
• MODIFY allows changing existing column
• ADD allows adding new fields
• DROP to delete a column
             Alter format:
ALTER TABLE tablename
[ADD (col name...,
   colname...)]
or
ALTER TABLE tablename
• [MODIFY (col name..)]
 or
ALTER TABLE tablename
• [Drop (col name..)]
Rules for ADDition
can add a col anytime if not null is not
  specified
can add multiple col at one time
can add a NULL column in three steps:
  – add the col without not null
  – fill every row
  – modify col to not null
Ex: add a new column soc-sec_no to customer

alter table customer
  add (soc_sec_no char(14));
step2:
update table customer
set soc_sec_no = ‘123-32-4444”;
step 3:
alter table customer
  modify (soc_sec_no not null);
    Changing column structure
Ex: increase credit limit to 6,2

Alter table customer
Modify (credit_limit number(6,2));
 Creating views (p 262); ch 18(BF)
User’s view of data

Create [or REPLACE VIEW view-name (col
 name) AS
 SQL query
 used if this view already exists
Create a view of customers with credit limit of
>5000

Create view high_limit as
select cust_name from customer where
Credit_limit >500;
COLUMN NAMES MUST BE specified if
• ..column names are derived
• ..two or more cols have the same name

Ex: create a view that keeps track of credit averages

Create view c_avg (cust_avg, cust_name) as
Select cust_name, avg(credit_limit)
From customer
Group by cust_name);
 Drop a view/advantages of views
Format:
Drop view view_name

Advantages of views:
• provide data independence
• different users can view data differently
• provide security

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:5
posted:2/9/2012
language:English
pages:49