csi 3317 (lab 2) data manipulation language. working with database insert into location values (53,...
DESCRIPTION
Data Manipulation Language SELECT [DISTINCT | ALL] {* | [AS ]] [,..]} FROM [WHERE ] [GROUP BY ] [HAVING ] [ORDER BY ] Groups are those rows with the same Column Values Only SELECT and FROM are mandatoryTRANSCRIPT
CSI 3317 (LAB 2)
Data Manipulation Language
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);
Data Manipulation Language
SELECT [DISTINCT | ALL]{* | <ColExpression> [AS <newName>]] [,..]}
FROM <TABLEName>[WHERE <condition>][GROUP BY <ColList>] [HAVING <condition>][ORDER BY <ColList>] Groups are those rows with the same Column Values Only SELECT and FROM are mandatory
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 > 30;
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 > 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’);
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’);
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_____%’
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 ;
MULTI-TABLE QUERIESLOCID BLDG_CODE ROOM CAPACITY
45 SITE 101 15046 SITE 102 4047 CBY 103 3548 SITE 103 4049 ADMN 105 4253 ADMN 424 154 ADMN 402 1
FID FLNAME FFNAME FMI LOCID FPHONE FRANK FPIN1 Cox Kim J 53 7155551234
ASSO 1181
2 Blanchard
John R 54 7155559087
FULL 1075
3 Williams Jerry F 56 7155555412
ASST 8531
4 Perry Laura M 55 7155556409
INST 1690
5 Brown Phillip E 57 7155556082
ASSO 9899
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);
MULTI-TABLE QUERIESINSERT 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'));
CARTESIAN PRODUCT
LOCID
BLDG_CODE
ROOM
CAPACITY
FID
FLNAME
FFNAME FMI LOCID FPHONE FRANK
FPIN
35 Rows of Data
Algorithm for JOIN in SQL:
1. Cartesian Product of Tables (specified in the FROM clause)
2. Selection of rows that match (e.g. predicate in the WHERE clause)
3. Project columns specified in the SELECT Clause
SELECT * FROM Location, Faculty;
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;
SET OPERATIONS
The Relations must be set operations (union|Intersect|difference) compatible:
Same number of attributes Identical attributes in terms of domains,
datatypes and lengths Only the number of tuples or rows can be
different
SET OPERATIONSUNION List all Locations where the Capacity is less than 10 or there
Faculty in that room (SELECT LOCID FROM Location WHERE capacity < 10)UNION(SELECT LOCID FROM Faculty);
Delete a record
DELETE FROM FacultyWHERE FID = 53;