CSI 3317 (LAB 2)

1
CSI 3317 (LAB 2)

• Data Manipulation Language

2
Working With Database

• INSERT INTO location VALUES (53, 'ADMN', '424',
  1)
•  
• INSERT INTO location VALUES (54, 'ADMN', '402',
  1)
•  
• INSERT INTO location VALUES (45, 'SITE', '101',
  150)
•  
• INSERT INTO location VALUES (46, 'SITE', '202',
  40)
•  
• INSERT INTO location VALUES (47, 'CBY', '103',
  35)
•  
• INSERT INTO location VALUES (48, 'SITE', '103',
  40)
•  
• INSERT INTO location VALUES(49, 'ADMN', '105',
  42)

3
Data Manipulation Language

• SELECT DISTINCT ALL
• ltColExpressiongt AS ltnewNamegt ,..
• FROM ltTABLENamegt
• WHERE ltconditiongt
• GROUP BY ltColListgt HAVING ltconditiongt
• ORDER BY ltColListgt
•  
• Groups are those rows with the same Column Values
•  Only SELECT and FROM are mandatory

4
Data Manipulation Language

• Select all rows and all columns
• SELECT FROM location
• Calculated Fields
• SELECT locid, bldg_code, room, capacity/2 AS
  ExamCapacity FROM location
• List all rooms whose capacity is greater than 30
•   SELECT locid, bldg_code, room, capacity FROM
  location WHERE capacity gt 30

5
Data Manipulation Language

• List all rooms whose capacity is greater than 30
  and is located in administration building
•   SELECT locid, bldg_code, room, capacity FROM
  location WHERE (capacity gt 30 AND bldg_code
  ADMN)
• List all rooms whose capacity is between 30 and
  50 and are in SITE building.
• SELECT locid, bldg_code, room, capacity FROM
  location WHERE (capacity BETWEEN 30 AND 50) AND
  (bldg_code SITE)

6
Data Manipulation Language

• List all SITE and CBY rooms (Use Set Membership)
•   SELECT locid, bldg_code, room, capacity FROM
  location WHERE bldg_code IN (SITE, CBY)
•  Can also use NOT IN, e.g NOT IN (ADMIN)

7
Data Manipulation Language

• Find all rooms with string C in their Building
  code
•   SELECT locid, bldg_code, room, capacity FROM
  location WHERE bldg_code LIKE C
• Regular expression can be made using zero or
  more chars or _ (underscore) any character,
• example K_____

8
Data Manipulation Language

• List all rooms in order of their Building Code
  and their room numbers
•   SELECT locid, bldg_code, room, capacity FROM
  location ORDER BY bldg_code, room

9
MULTI-TABLE QUERIES
10
MULTI-TABLE QUERIES

• CREATE TABLE faculty
• (fid NUMBER(5) CONSTRAINT faculty_fid_pk PRIMARY
  KEY, flname VARCHAR2(30) CONSTRAINT
  faculty_flname_nn NOT NULL,
• ffname VARCHAR2(30) CONSTRAINT faculty_ffname_nn
  NOT NULL, fmi CHAR(1),
• locid NUMBER(5) CONSTRAINT faculty_locid_fk
  REFERENCES location(locid),
• fphone VARCHAR2(10),
• frank VARCHAR2(8) CONSTRAINT faculty_frank_cc
  CHECK ((frank 'ASSO') OR (frank 'FULL') OR
  (frank 'ASST') OR (frank 'INST')),
• fpin NUMBER(4) CONSTRAINT faculty_fpin_uk
  UNIQUE, startdate DATE)

11
MULTI-TABLE QUERIES

• INSERT INTO faculty VALUES (1, 'Cox', 'Kim', 'J',
  53, '7155551234', 'ASSO', 1181,
  TO_DATE('09/15/1990', 'MM/DD/YYYY'))
• INSERT INTO faculty VALUES (2, 'Blanchard',
  'John', 'R', 54, '7155559087', 'FULL', 1075,
  TO_DATE('01/12/1972', 'MM/DD/YYYY'))
• INSERT INTO faculty VALUES (3, 'Williams',
  'Jerry', 'F', 56, '7155555412', 'ASST', 8531,
  TO_DATE('08/26/1992', 'MM/DD/YYYY'))
• INSERT INTO faculty VALUES (4, 'Perry', 'Laura',
  'M', 55, '7155556409', 'INST', 1690,
  TO_DATE('01/22/1995', 'MM/DD/YYYY'))
• INSERT INTO faculty VALUES (5, 'Brown', 'Philip',
  'E', 57, '7155556082', 'ASSO', 9899,
  TO_DATE('08/15/1985', 'MM/DD/YYYY'))

12
CARTESIAN PRODUCT
SELECT FROM Location, Faculty

• Algorithm for JOIN in SQL
• Cartesian Product of Tables (specified in the
  FROM clause)
• Selection of rows that match (e.g. predicate in
  the WHERE clause)
• Project columns specified in the SELECT Clause

13

• Cartesian Product followed by Selection
•   SELECT L., F. FROM Location L, Faculty P
• WHERE L.LOCID F.LOCID
• Order by
•   SELECT F.FPHONE, L.ROOM
• FROM Location L, Faculty P WHERE L.LOCID
  F.LOCID
• ORDER BY L.ROOM, F.FPHONE

14
SET OPERATIONS

• The Relations must be set operations
  (unionIntersectdifference) compatible
• Same number of attributes
• Identical attributes in terms of domains,
  datatypes and lengths
•  
• Only the number of tuples or rows can be
  different

15
SET OPERATIONS

• UNION
•  
• List all Locations where the Capacity is less
  than 10 or there Faculty in that room
•  
• (SELECT LOCID FROM Location WHERE capacity lt 10)
• UNION
• (SELECT LOCID FROM Faculty)

16

• Delete a record
•  
• DELETE FROM Faculty
• WHERE FID 53