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';