Transcript Document
SQL : STRUCTURED QUERY
LANGUAGE
QUERIES
Relational Algebra (formal mathematical
approach) – not required
QBE ( Query By Example) - The MS
Access approach (originally designed by
Zloof at Borland for dBase and Paradox)
SQL (Structured Query Language) –
Oracle and typical mainframe approach
SQL (STRUCTURED QUERY
LANGUAGE)
developed at IBM Research Labs at San Jose,
Ca. in 1970's
D. Chamberlin was the leader of research
group
originally spelled (and pronounced) SEQUEL
dominant data base language on mainframe
computers
but available on mini and pc based dbms
ANSI (American National Standards Institute)
approved
OUR EXAMPLE
DATABASE
student ( stuid, stuname, major, credits )
faculty ( facid, facname, dept, rank )
class ( course#, facid, sched, room )
enroll ( course#, stuid, grade )
FUNDAMENTAL SQL
VERBS FOR ACTIVITIES
to define the structure, we
CREATE TABLE (also ALTER TABLE and
DROP TABLE)
to query and manipulate the information in the
database, we
SELECT
UPDATE
INSERT
DELETE
CREATE VIEW
CREATE TABLE - THE SYNTAX
(EASIER UNDERSTOOD BY
EXAMPLES TO FOLLOW)
CREATE TABLE base-table-name (col-
name data type [NOT NULL [WITH
DEFAULT]][,col-name data type [NOT
NULL [WITH DEFAULT]] ...,[PRIMARY
KEY (col-name, [,col-name],...)],[FOREIGN
KEY (col-name,[,col-name]...)
REFERENCES (other-base-table-name)])
CREATE A TABLE
CREATE TABLE student
(stuid
CHAR(9) NOT NULL,
stuname CHAR(20) NOT NULL,
major
CHAR(20),
credits
SMALLINT,
PRIMARY KEY (stuid));
Some Data Types in SQL
• INTEGER
• SMALLINT
– Efficient way of representing integers between –215 to (215 – 1)
• DECIMAL (p,q)
– A decimal number p digits long, with q of these being decimal places; so,
DECIMAL (5,2) represents a number with three digits to the left of the
decimal, and two digits to the right of the decimal point.
• CHAR (n)
– Character string n characters long
• DATE
– Dates in the form DD-Month-YY or MM/DD/YYYY ; thus, February 16,
2010 could be represented as 16-February-2010 or 2/16/2010
CREATE A TABLE FORCING
REFERENTIAL INTEGRITY
CREATE TABLE class
(course#
CHAR(7) NOT NULL,
facid
CHAR(9),
sched
CHAR(7),
room
CHAR(4),
PRIMARY KEY (course#),
FOREIGN KEY (facid) REFERENCES
faculty);
TO ALTER A TABLE STRUCTURE
BY ADDING A FIELD ...
ALTER TABLE class
ADD
ctitle CHAR(30);
THE SELECT STATEMENT (TO
RETRIEVE DATA)
SELECT [DISTINCT] col-name [,col-name] ...
FROM table-name [,table-name]...
[WHERE predicate]
[GROUP BY col-name [,col-name] ... [HAVING
predicate]]
[ORDER BY col-name [,col-name] ...];
GET NAMES, ID'S AND
CREDITS OF ALL IT MAJORS
These attributes are all in the student table
The SELECT is similar to the project operator of relational
algebra,
but, unfortunately, is not at all like the select
operator in the relational algebra
and, also, unfortunately, does not eliminate duplicates.
------------------------------------------------------------------SELECT stuname, stuid, credits
FROM
student
WHERE major = ‘IT';
GET ALL THE ATTRIBUTES
OF IS FACULTY
{The asterisk ( * ) in SELECT gives us all
attributes}
--------------------------------------------------------------SELECT *
FROM
faculty
WHERE dept = ‘IS';
<is equivalent to>
SELECT facid, facname, dept, rank
FROM
faculty
WHERE dept = ‘IS';
GET THE COURSE# FOR ALL
COURSES IN WHICH STUDENTS
HAVE EARNED GRADES
{We will use the enroll table for actual enrollment and
grades}
SELECT
FROM
course#
enroll;
{The above will yield duplicate courses; to eliminate
duplicates, use DISTINCT}
SELECT DISTINCT course#
FROM
enroll;
GET ALL INFORMATION
ABOUT ALL STUDENTS
SELECT
FROM
*
student;
GET NAMES AND ID'S OF
FACULTY, ALPHABETIZED
SELECT facname, facid
FROM
faculty
ORDER BY facname;
<if two faculty have the identical name,
suppose we decide to order by dept.>
SELECT
facname, facid
FROM
faculty
ORDER BY facname, dept;
GET NAMES OF ALL IT
MAJORS WHO HAVE MORE
THAN 60 CREDITS
{in WHERE predicates, we can use the standard
comparison operators >, <, >=, <=, =, and the standard
logical operators AND, OR and NOT }
SELECT stuname
FROM
student
WHERE major = ‘IT' AND credits > 60;
FIND ID'S AND NAMES OF ALL
STUDENTS TAKING IS431
{We can SELECT based on multiple tables.In enroll table we
have the course#'s and stuid's. In student table, we have the stuid's
and stunames. We will do the equivalent of a “cartesian product"
in relational algebra.
Records from each table must match on common stuid field.}
SELECT
enroll.stuid, stuname
FROM
student, enroll
WHERE
course# = ‘IS431' AND
enroll.stuid = student.stuid
{Note: in SELECT line, stuid needs to be qualified by either
enroll (as we have done) or student}
FIND ID AND GRADE OF ALL
STUDENTS TAKING COURSES
FROM FAC ID 767564343 AND
ARRANGE IN ORDER BY STUID
{First, from class table, get course#s taught by
given facid. Then, from enroll table, pick records
with matching course#'s and join.}
SELECT stuid, grade
FROM
class, enroll
WHERE
facid ='767564343' AND
class.course# = enroll.course#
ORDER BY stuid ASC;
FIND COURSE#'S, STUNAME'S
AND MAJORS OF ALL STUDENTS
ENROLLED IN COURSES TAUGHT
{This requires attributes from THREE tables.}
{From class table, get course#'s taught by given facid}
{Then, from enroll table, get stuid's of students enrolled in
course#s}{Then, from student table, get names and majors of
selected students}
SELECT enroll.course#, stuname, major
FROM
class, enroll, student
WHERE class.course# = enroll.course# AND
enroll.stuid = student.stuid;
SQL'S BUILT-IN FUNCTIONS
each SQL function operates on a single column of a table
the SQL functions eliminate any null values, and work on non-
null values
each SQL function returns a single value, defined as follows:
COUNT returns the number of values in a column
SUM returns the sum of values in a column
AVG returns the average of the values in the column
MAX returns the maximum of the values in the column
MIN returns the minimum of the values in the column
COUNT (DISTINCT column-name) eliminates duplicate
values in the column
COUNT ( * ) counts the total number of rows including nulls
& duplicates
FIND THE TOTAL NUMBER OF
STUDENTS ENROLLED IN IS431
SELECT COUNT ( stuid)
FROM
enroll
WHERE course# = 'IS431';
FIND THE NUMBER OF
DEPARTMENTS WHICH HAVE
FACULTY IN THEM
SELECT COUNT (DISTINCT dept)
FROM
faculty;
FIND THE AVERAGE NUMBER
OF CREDITS STUDENTS HAVE
SELECT AVERAGE (credits)
FROM
student;
FIND THE STUID AND STUNAME
WITH THE MAXIMUM NUMBER OF
CREDITS
{First find the maximum number of credits acheived by any
student, in a subquery, then find the student(s) whose
credits equal this maximum number of credits}
SELECT
FROM
WHERE
stuid, stuname
student
credits =
( SELECT MAX (credits)
FROM
student);
FIND THE NAMES AND ID'S OF ALL
STUDENTS WHO HAVE LESS THAN THE
AVERAGE NUMBER OF CREDITS OF ALL
STUDENTS IN THE DATABASE
SELECT stuname, stuid
FROM
student
WHERE credits <
(SELECT AVG (credits)
FROM
student) ;
ASSUMING EACH COURSE IS 3
CREDITS, LIST, FOR EACH
STUDENT, THE NUMBER OF
COURSES
SELECT stuid, ' number of courses = ', credits/3
FROM student;
FOR EACH COURSE, SHOW THE
NUMBER OF STUDENTS ENROLLED
{The GROUP BY clause allows us to lump
together all the records with the identical
specified field value}
SELECT course#, COUNT (DISTINCT stuid)
FROM
enroll
GROUP BY course#;
FIND ALL COURSES HAVING
FEWER THAN 3 STUDENTS
SELECT
FROM
GROUP BY
HAVING
course#
enroll
course#
COUNT ( * ) < 3;
GET THE COURSE#,
SCHED & ROOM OF ALL
IS COURSES
{Want all IS-prefixed courses from IS118 to
IS799}
{We will use a LIKE in predicate and
wildcards in the match string}
{A " % " denotes a string of any length}
SELECT course#, sched, room
FROM
class
WHERE course# LIKE 'IS%' ;
FIND THE STUID AND COURSE#
FOR ALL STUDENTS WHOSE
GRADES ARE MISSING
{In the WHERE clause, we can have "IS
NULL" for a particular field.}
SELECT stuid, course#
FROM
enroll
WHERE grade IS NULL;
THE UPDATE OPERATOR
UPDATE table-name
SET column-name = expression
[column-name = expression] ...
[WHERE predicate];
{the SET is like an assignment statement in a HLL}
{it is not necessary to specify the current value of a field}
CHANGE THE MAJOR OF
STUDENT 123456789 TO IT
UPDATE
SET
WHERE
student
major = ‘IT'
stuid = '123456789';
UPDATE SEVERAL FIELDS IN A
RECORD - CHANGE ANNBER TO
IS DEPT AND ASSISTANT
UPDATE
SET
WHERE
faculty
dept = ‘IS'
rank = 'assistant'
facname = 'annber';
THE INSERT OPERATOR
{The
INSERT operator is used to put records
into a table}
INSERT INTO table-name [(col-name [,colname]...)] VALUES (constant [,constant]...);
{If we are inserting into all fields of a
record, we don't have to list the individual
fields}
INSERT A NEW FACULTY RECORD
FOR JONES, ID#987654321 IN
CHE AS INSTRUCTOR
INSERT
INTO
faculty
VALUES ('987654321', 'jones', 'che', 'instructor');
THE DELETE OPERATOR
DELETE FROM table-name WHERE predicate;
Aside: You must be careful when "deleting"
to not delete a primary key from one table which
is referenced by a foreign key in another table.
DELETE FROM ENROLLMENT ALL
RECORDS FOR STUDENT
135792468
DELETE
FROM
enroll
WHERE stuid = '135792468';