Relational database

Document Sample
Relational database Powered By Docstoc
					 Lecture 4 on Structural Query Language

To study Structural Query Language (SQL)
 as a non-procedural computer language to
 access relational database in data
 definitional language such as Create,
 Drop and Alter statements and in data
 manipulation language such as Insert,
 Update and Delete statements.

1/2/2010                                1
       Structural Query Language
• SQL is a transform oriented relational language.

• SQL can be run as a query/update language by itself or SQL
  command can be embedded in application programs.

• SQL include commands for data definition, data manipulation and
  data control.

• It is non-procedural and includes no reference to access paths, links
  or navigation such that the user only need to specify what he wants
  to retrieve/update rather than how to do it.

• It has a high level of independence from physical data storage.
1/2/2010                                                              2
           SQL syntax
SELECT     [Distinct] select-list
FROM       from-list
WHERE      qualification
GROUP BY   grouping-list
HAVING     group-qualification
ORDER      attribute-list

1/2/2010                            3
The Where clause limits the rows that are
 returned from Query:

For example:

Select * from users where userid = 2

1/2/2010                                4
               Order by
This command can sort by any column type:
 alphabetical, chronological or numeric in
 either ascending or descending order by
 placing asc or desc:

For example:

Select * from users order by lname, fname
1/2/2010                                    5
The having predicate restricts the rows displayed
  by a group by (whereas the where clause
  restricts the rows that are used in the group by).

For example:

Select avg(contribution) as avg_contrib, state
from contributions
group by state
having avg(contribution) > 500

1/2/2010                                               6
           Sample database for SQL
           Student Relation
SID            NAME     MAJOR   GRADE    AGE
100            JONES    HIST    GR       21
150            PARKS    ACCT    SO       19
200            BAKER    MATH    GR       50
250            GLASS    HIST    SN       50
300            BAKER    ACCT    SN       41
350            RUSELL   MATH    JR       20
400            RYE      ACCT    FR       18
               JONES    HIST    SN       24    7
       Enrollment Relation
100         BD445   1         Class_ TIME   ROOM
150         BA200   1         NAME
200         BD445   2         BA200 MF9     SC110
200         CS250   1         BD445 MWF3 SC213
300         CS150   1
400         BA200   2         BF410 MWF8 SC213
400         BF410   1         CS150 MWF3 EA304
400         CS250   2
                              CS250 MWF1 EB210
450         BA200   3
 1/2/2010                           2       8
                     SQL Projections
The projection Student[Sid, Name, Major] is created by

• Select         Sid, Name, Major          from Student

          100         Jones        Hist
           150         Parks        Acct
           200         Baker        Math
           250         Glass        Hist
           300         Baker        Acct
           350         Russell      Math
           400         Rye          Acct
           450         Jones        Hist
1/2/2010                                                  9
   • Select statement with Unique:

       Select     Unique       Major from     Student

   =>      Hist

   •       Select statement with where clause:

       Select     * from student      where Major = „Math‟

          200    Baker        Math           Gr     50
           350    Russell      Math           Jr     20

      Note: The * in the SQL statement means that all attributes
1/2/2010 of the relation are to be obtained.                 10
Select statement with several conditions:
Select Name, Age from Student where Major = „Math‟ and Age > 21
 Baker                50

Select statement with an In clause:
Select Name from Student where Major in [„Math‟, „Acct‟]
=>      Parks

Select statement with an Not In clause:
Select Name from Student where Major not in [„Math‟, „Acct‟]
=>      Jones

1/2/2010                                                          11
           Select statement with LIKE conditions

Like operation allows a rich set of regular expressions to be
   used as patterns while searching text.

For example,

SELECT          Age
FROM            student
WHERE           Name = „R_%E‟

The only such student is Rye and his age is 18.

1/2/2010                                                   12
              SQL Built-in functions
Some standard built-in functions are available in SQL as follows:

•   Count:      computes the number of tuples in a relation
•   Sum:        totals numeric attributes
•   Avg:        computes the average value
•   Max:        obtain the maximum value of an attribute
•   Min:        obtain the minimum value of an attribute

For example:
Select Count(Major) from Student

Select Count(Unique Major) from Student

Select Sid, Name from Student where Age > Avg(Age)
 200 Baker
   250 Glass
   300 Baker
1/2/2010                                                            13
             Built-in functions and grouping
Built-in functions can be applied to groups of tuples within a relation. Such groups are
   formed by collecting tuples together that have the same value of a specific attribute.
   The SQL keyword Group By instructs the DBMS to group tuples together that have
   the same value of an attribute.

For example, the count function sums the number of tuples in each group/

     Select Major, Count(*) from Student Group By Major

=>         Hist    3
           Acct    3
           Math    2

For example, use SQL Having clause to identify the subset of groups we want to

Select Major, Avg(Age) from Student Group By Major Having Count(*)>2

 Hist 31.67
  Acct 26

      1/2/2010                                                                    14
           Union, Intersect, Except
• Union set operation is similar to “or”
  condition in where clause.

• Intersect set operation is similar to “and”
  condition in where clause.

• Except set operation is a difference set
  operation in relational algebra.
1/2/2010                                        15
Select Name
from student
where age < 20 or age > 40

Can be rewritten as

Select Name
from student
where age < 20


Select Name
from student
where age > 40

The answer is Rye, Baker, and Glass.

1/2/2010                               16
Select Name
from student
where age < 20 and Grade = SN

Can be rewritten as

Select Name
from student
where age < 20


Select Name
from student
where Grade = SN

The answer is Jones             17
Select Name
from student
where Major not = „Hist‟

Can be rewritten as

Select name
from student


Select name
from student
where Major = „Hist‟

The answer is: Parks, Baker, Rusell and Rye.
1/2/2010                                       18
     Querying multiple relations by using Subquery
Suppose we need to know the names of students enrolled in the class BD445. If we
   know that only students 100 and 200 are enrolled in this class, then the following will
   produce the correct names.

Select Name from Student Where Sid in [100, 200]

=>       Jones

Select Student_number from Enrollment where Class_name = „BD445‟

=>       100

By combining the above 2 SQL statements, the SQL subquery can be easier to
   understand as follows:

Select Name from Student where Sid in
   Select Student_number from Enrollment where Class_name=„BD445‟

=> Jones
    1/2/2010                                                                       19
Suppose we want to know the names of the students enrolled in classes on Monday, Wednesday and
   Friday at 3       o‟clock.

First, we need the name of classes that meet at that time.
Select Class.Name from Class where Time=„MWF3‟

=>         BD445

Now, what are the identifying numbers of relations in these classes? We can specify:

Select Student_number from Enrollment where Enrollment.Class_name in
    Select Class_name from Class where Time = „MWF3‟

=>         100

Now, to obtain the names of those students, we specify:

Select from Student where Student.Sid in
    Select Enrollment.Student_number from Enrollment where Enrollment.Class_name in
           Select Class_name from Class where Time = „MWF3‟

=>         Jones

1/2/2010                                                                                    20
  Querying multiple relations using Join
A join is the combination of a product operation, followed by a selection and a
   projection. For example, the From statement expresses the product of
   Student and Enrollment. The Where statement expresses the selection. The
   projection of student number, name and class name is taken.

Select Student.Sid, Student.Name, Enrollment.Class_Name
From Student, Enrollment
Where Student.Sid=Enrollment.Student_Number

=>        100   Jones           BD445
          150   Parks           BA200
          200   Baker           BD445
          200   Baker           CS250
          300   Baker           CS150
          400   Rye             BA200
          300   Rye             BF410
          400   Rye             CS250
          450   Jones           BA200
     1/2/2010                                                            21
In this example, table Student and Enrollment are joined.
Comparison of SQL Subquery and Join
Join expressions can substitute for subquery expressions, the converse is not
   true. Subqueries cannot always be substituted for joins. When using a
   subquery, the displayed attributes can come from only the relation name in
   the from expression in the first select.

For example, if we want to know the names of the students enrolled in classes
   on Monday, Wednesday, and Friday at 3 o‟clock using Join.

Select Student.Name
from Student, Enrollment, Class
Where Student.Sid = Enrollment.Student_Number
   and Enrollment.Class_Name = Class.Name
   and Class.Time = „MWF3‟

If we want to know both the names of the classes and the grade levels of the
    undergraduate students, then we must use a join. A subquery will not suffice
    because the desired results arise from two different relations of Enrollment
    and Student.

    1/2/2010                                                              22
                         Exists and Not Exists
Exists and Not Exists are logical operators; their value is either
  true or false depending on the presence of absence of tuples
  that fit qualifying conditions.

For example, suppose we want to know the numbers of students
  enrolled in more than one class. The meaning of the subquery
  expression is “Find 2 tuples in enrollment having the same
  student number but different class names”.

Select unique Student_number
from Enrollment A
where Exists
   Select *
   from Enrollment B
   where A.Student_number = B.Student_number
   and A.Class_name not = B.Class_name

=>   1/2/2010
                200                                          23
Another example is that we want to know the names of students taking all
classes. An expression is to say we want the names of students such that
there are no classes that the student did not take.

Select Student.Name
from Student
where not exists
        Select *
        from Enrollment
        where not exists
                 Select *
                 from Class
                 where Class.Name = Enrollment.Class_name
                 and Enrollment.Student_number = Student.Sid

Note: This query has three parts. In the bottom part, we try to find classes the
student did not take. The middle part determines if any classes were found
that the student did not take. If not, then the student is taking all classes, and
the student‟s name is displayed.

  1/2/2010                                                                   24
                         Inserting data
Tuples can be inserted into a relation one at a time or in groups

To insert a single tuple, we state

Insert into Enrollment [400, „BD445‟, 44]

Or if we do not know all of this data, we could say

Insert into Enrollment (Student_number, Class_name) [400, „BD445‟]

Note: If the student‟s information is not available, this insertion will cause an
  referential integrity problem such that a child relation‟s tuple can only be
  inserted if its corresponding parent relation‟s tuple exists.

  1/2/2010                                                                   25
                                 Deleting data
Tuples can be deleted one at a time or in groups.

For example, we want to delete the tuple for student number 100

Delete Student
where Student.Sid = 100

Note: If student 100 is enrolled in classes, this deletion will cause an referential integrity problem
   such that a parent relation‟s tuple can be deleted only if its corresponding child relation‟s tuple
   has been deleted.

Another example is to delete groups of tuples.

Delete Enrollment
where Enrollment.Student_number IN
   Select Student.Sid
   from Student
   where Student.Major = „ACCT‟

Delete Student
where Student.Major = „ACCT‟

Note: The order of these two deletion is important. If the order were reversed, none of the
   Enrollment tuples would be deleted because the matching Student tuples have already been
     1/2/2010                                                                                 26
                                      Modifying data
Tuples can be modified one at a time or in groups. The keyword Set is used to change an attribute value. After Set,
    the name of the attribute to be changed is specified and then the new value or way of computing the new

Update Enrollment
   Set Position_number = 44
   where Student_number = 400

Update Enrollment
   Set Position_number = Max(Position_number) + 1
   where Student_number = 400

Note: the value of the attribute will be calculated using the Max built-in function.

Another example for mass update is to change a course from BD445 to BD564. In this case, to prevent referential
    integrity problem, we perform as follows:

alter    table    Enrollment   Add     Constraint            FK      foreign     key   (Class_name)    references
     Class(Class_name) on update cascade;

Update Enrollment
   Set Class_name = „BD564‟
   where Class_name = „BD445‟

Update Class
   Set Class_name = „BD564‟
   where Class_name = „BD445‟

      1/2/2010                                                                                            27
       Database Programming

• Embedded commands:
   – Database commands are embedded in a
     general-purpose programming language
• Library of database functions:
   – Available to the host language for database
     calls; known as an API
           • API standards for Application Program Interface

1/2/2010                                                       28
  Embedded SQL in MS SQL Server
        Stored Procedure
Stored Procedure:

create proc show_course @incourse_no integer as
select * from course where course_no = @incourse_no

Execution of Stored Procedure:

execute show_course 4

 1/2/2010                                      29
               Embedded SQL
• Most SQL statements can be embedded in a general-
  purpose host programming language such as COBOL, C,
• An embedded SQL statement is distinguished from the
  host language statements by enclosing it between EXEC
  SQL or EXEC SQL BEGIN and a matching END-EXEC
  or EXEC SQL END (or semicolon)
   – Syntax may vary with language
   – Shared variables (used in both languages) usually
     prefixed with a colon (:) in SQL

 1/2/2010                                             30
            Example: Variable Declaration
                  in Language C
Variables inside DECLARE are shared and can appear
  (while prefixed by a colon) in SQL statements
SQLCODE is used to communicate errors/exceptions
  between the database and the program
  int loop;
       varchar dname[16], fname[16], …;
       char ssn[10], bdate[11], …;
       int dno, dnumber, SQLCODE, …;

 1/2/2010                                            31
               Example for SQL Commands for
               Connecting to a Oracle Database

In the “Microsoft ODBC for Oracle Setup” dialog, enter the following:

Database Source Name: ora9i
Description: <optional>
Username: <optional>
Server Name: w2ksc

Start “Oracle -> OraHome90 -> Application Development -> SQL Plus
User Name:        grp##
Password:         abcd1234
Host String:      w2ksc

 1/2/2010                                                               32
Example of a non-menu driven embedded SQL
Stored Procedure to access an Oracle Database

  create procedure upd_per (in_id in integer)
  as begin
   update person set name = 'abc' where
     id_no = in_id;

  execute upd_per(1);

  1/2/2010                                      33
            Embedded SQL in C
           Programming Examples
loop = 1;
while (loop) {
    prompt (“Enter SSN: “, ssn);
           select FNAME, LNAME, ADDRESS, SALARY
           into :fname, :lname, :address, :salary
           from EMPLOYEE where SSN == :ssn;
           if (SQLCODE == 0) printf(fname, …);
           else printf(“SSN does not exist: “, ssn);
           prompt(“More SSN? (1=yes, 0=no): “, loop);
  1/2/2010                                              34
         Embedded SQL in C
       Programming Examples
• A cursor (iterator) is needed to process
  multiple tuples
• FETCH commands move the cursor to the
  next tuple
• CLOSE CURSOR indicates that the
  processing of query results has been

 1/2/2010                                    35
                  Embedded SQL in C
           Programming Examples with Cursor
Prompt (“Enter the Department Name:”, dname);
    Select Dnumber into :dnumber
    from DEPARTMENT where Dname = :dname;
    Selec Ssn, Fname, Minit, Lname, Salary
    from EMPLOYEE where Dno = :dnumber
    FOR UPDATE OF Salary;
EXEC SQL FETCH from EMP into :ssn, :fname, :minit, :lname, :salary;
while (SQLCODE == 0) {
    printf (“Employee name is:”, Fname, Minit, Lname);
    prompt (“Enter the raise amount:”, raise);
    update EMPLOYEE
    set Salary = Salary + :raise
    where CURRENT OF EMP;
EXEC SQL FETCH from EMP into :ssn, :fname, :minit, :lname, :salary;
 1/2/2010                                                             36
           Lecture Summary
SQL is a high level relational database
 language. It is much user friendly than
 other database languages, and becomes
 the most popular database language since
 90‟s. Nevertheless, it is not an end user
 language such as Natural language, and is
 subject to be changed by individual
 relational database vendors and academic
1/2/2010                                37
              Review Question
What are the difference between Query and Nested Query in
 SQL with respect to performance and why?

How does Entity Integrity affect an Insert operation of SQL?

How does Referential Integrity affect Insert and Update
  operations of SQL?

What is the condition for using UNION, INTERSECT and
 EXCEPT clauses in SQL?

1/2/2010                                                  38
                      Tutorial Question
Given the following relations, write SQL statements to answer the questions

Customer           (Customer_id, Customer_name, Customer_Address)
Order              (Order_id, *Customer_id, Date, Cost)
Payment            (Payment_id, *Customer_id, Date, Amount)

(a) List out all the customer names who paid more than $300 on the 15th
      March 1997.
(i)   without using sub-query                                   (30%)

(ii)     using sub-query                                        (30%)

(b) Remove a customer with customer_id ######## from the customer list
     where ######## is unique student id in 8 digits

       1/2/2010                                                           39
           Reading Assignment
Chapter 8 SQL-99: Schema Definition,
 Constraints, Queries, and Views
 “Fundamentals of Database Systems” by
 Elmasri & Navathe fifth edition, Pearson,

1/2/2010                                     40