Query Languages: How to build or interrogate a relational database

1
Query Languages How to build or interrogate a
relational database

• Structured Query Language (SQL)

2
SQL

• SQL is a query language for relational databases.
• Contains
• Data Definition Language to define databases
• Data Manipulation Language to manipulate
  databases.
• SQL is widely accepted and is used by most
  relational DBMSs.
• Is being standardized.

3
The importance of SQL

• Since SQL is used in almost all relational
  databases, once you know SQL you can probably
  construct and manipulate databases in all RDBMs.
• Knowing SQL makes you a (beginning) ORACLE,
  Informix, SyBase, AdaBas, Postgres and so on
  programmer!

4
Functionalities of SQL

• SQL provides
• On-line and embedded use.
• Precompilation of embedded queries.
• Dynamic database definition and alteration.
• Maintenance of indexes
• View mechanism
• Authorization mechanism
• Automatic concurrency control
• Logging and database recovery
• Report formatting

5
Tables in SQL

• SQL recognizes
• Base Tables
• real tables that physically exist in the
  database. There are physically stored records
  and possibly physically stored indexes directly
  corresponding to the table
• Views
• virtual tables that do not physically exist but
  look to the user as if they do

6
Data Definition

• An SQL database consist of
• Database Spaces
• Base tables
• Indexes
• Views

7
Database Spaces

• DBSpace is a section of physical disk.
• It consists of
• Base tables
• Indices
• Views
• All can be dynamically dropped from DBSpaces.
• DBSpaces allow the DB administrator to distribute
  data accesses over different disks.

8
Indexes

• As we know, indexes can improve search
  performance.
• Cost more space needed and slower insertion.
• Indexes can be defined over any combination of
  attributes in a base table.
• Automatically maintained in SQL.
• Users never directly use an index.

9
Views

• Correspond to external schemas.
• Derived from one or more base tables or views.
• Computed dynamically.

10
Operations in SQL

• For tables
• CREATE, ALTER, DROP
• For indexes
• CREATE, DROP
• For views
• CREATE, DROP

11
Creating tables

• CREATE lttable namegt
• (ltcoldeclgt ,ltcoldeclgt,
• , ltpkdefgt , ltfkdefgt)
• ltcoldeclgt
• ltcolgtlttypegtNOT NULL
• lttypegt
• integerdecimal(p,q)
• char(n)datetime

12
Creating tables continued

• ltpkdefgt
• PRIMARY KEY (ltcolnamegt
• ,ltcolnamegt
• ltfkdefgt
• FOREIGN KEY ltfknamegt
• (ltcolnamegt,ltcolnamegt)
• REFERENCES lttablegt
• ON DELETE lteffectgt

13
More on creating tables

• lteffectgt
• RESTRICT CASCADE SET NULL
• What happens when the tuple in the referenced
  table with that value is deleted
• RESTRICT Do not delete as long as there tuples
  in other table with that foreign key value
• CASCADE Delete all tuples with that foreign key
  value
• SET NULL Set value of foreign key to NULL.
  (Note violates referential integrity).

14
Example 1

• CREATE TABLE Student
• (sid CHAR(5) NOT NULL,
• sname VARCHAR(20),
• address VARCHAR(70),
• PRIMARY KEY (sid))
• OR
• CREATE TABLE Student
• (sid CHAR(5) PRIMARY KEY,
• sname VARCHAR(20),
• address VARCHAR(70))

15
Example 2

• CREATE TABLE Enrol
• (sid CHAR(5) NOT NULL,
• cid CHAR(5) NOT NULL,
• grade INT,
• PRIMARY KEY(sid, cid),
• FOREIGN KEY (sid)
• REFERENCES Student
• ON DELETE CASCADE
• FOREIGN KEY (cid)
• REFERENCES Course
• ON DELETE RESTRICT)

16
Altering tables I

• ALTER TABLE lttable namegt
• ADD ltcoldeclgt
• ltpkdefgt
• ltfkdefgt
• ALTER TABLE Enrol
• ADD enroldate DATE
• adds a new column to the table grade. For
  existing tuples, the value is set to NULL.

17
Altering tables II

• ALTER TABLE lttable namegt
• DROP PRIMARY KEY
• ltfknamegt
• Note that care must be taken when dropping
  columns.

18
Dropping tables

• Tables can be dropped at any time.
• Dropping a table deletes both the definition and
  data.
• Also, all views, indexes and foreign key
  definitions referring to this table are dropped.
• DROP TABLE lttable namegt

19
Creating indexes

• CREATE UNIQUE INDEX
• ltindexgt ON lttablegt
• (ltcolnamegt ltordergt
• ,ltcolnamegt ltordergt)
• ltordergt ASC DESC
• Creates an index on named columns. With UNIQUE,
  no two tuples can have the same values for the
  indexes columns.
• Example
• CREATE INDEX course
• ON Enrol (cid)

20
Data manipulation

• Having created the tables, indexes and views, we
  now need to populate the database and retrieve
  information from it.
• In other words, we want to manipulate the data.

21
Retrieval

• SELECT DISTINCT ltitemsgt
• FROM lttablegt , lttablegt
• WHERE ltpredgt
• GROUP BY ltattrsgt
• HAVING ltpredgt
• ORDER BY ltattrsgt
• Corresponds to a JOIN-SELECT-PROJECT expression
  in relational algebra.

22
Predicates

• The predicate ltpredgt is a condition formed by
  parentheses and boolean operators AND, OR and
  NOT.
• A condition has the form
• ltattrgtltopgtltvaluegtltattrgt
• and an operator is one of
• lt lt gt gt !

23
WHERE clauses

• In general, WHERE clauses are constructed as in
  relational algebra, but with some additions
• LIKE string
• May contain wildcard characters , which matches
  any string, and _, which matches a single
  character.
• IN (set of values)
• Tests for set membership
• BETWEEN c1 AND c2

24
Example

• Find Student IDs and grades for those students
  who read CS35A
• SELECT sid, grade
• FROM Enrol
• WHERE cid CS35A
• Compare
• p sid, result( s cid CS35A(Enrol))

25
Example continued

• We can embellish the way in which the result
  appears by including format strings in the SELECT
• Example
• SELECT sid as student, grade
• FROM Enrol
• WHERE cid CS35A

26
DISTINCT

• DISTINCT is used to make sure that we do not get
  any duplicate values.
• Example
• SELECT DISTINCT cid
• FROM Enrol
• WHERE grade gt 70
• First, find the various course numbers that
  qualify and then remove duplicates.

27
More examples

• The use of in the SELECT returns all attributes
• SELECT
• FROM Enrol
• WHERE cid CS35A
• Find all students who obtained 60 or more for
  CS35A
• SELECT sid
• FROM Enrol
• WHERE cid CS35A
• AND grade gt 60

28
Yet more examples

• Find all results for either CS20Q or CS35A
• SELECT
• FROM Enrol
• WHERE cid IN
• (CS20Q, CS35A)
• Find results for CS courses
• SELECT
• FROM Enrol
• WHERE cid LIKE CS

29
Ordering results

• Get all results for CS20Q and CS35A order them
  by result
• SELECT sid, cid, grade
• FROM Enrol
• WHERE cid IN
• (CS20Q, CS35A)
• ORDER BY grade DESC

30
Subqueries

• Notice that the result of a SELECT clause is a
  table which can be used in another WHERE clause.
• Find course titles of the courses for which 123
  was registered
• SELECT title
• FROM Course
• WHERE cid IN
• (SELECT cid FROM Enrol
• WHERE sid 123)

31
Table labels

• Sometimes we need to interrogate the same table
  twice.
• We use table labels
• Example Get IDs from those students who did both
  CS20Q and CS35A
• SELECT DISTINCT sid
• FROM Enrol AS e1, Enrol as e2
• WHERE e1.sid e2.sid
• AND e1.cid CS20Q
• AND e2.cid CS35A

32
Table labels can usually be avoided

• We could formulate the same query as
• SELECT sid
• FROM Enrol
• WHERE cid CS20Q
• AND sid IN
• (SELECT sid
• FROM Enrol WHERE cid CS35A)

33
Use of ALL in WHERE clauses

• Queries that look at all tuples satisfying a
  particular predicate.
• Get the IDs of the students all of whose results
  are over 70.
• SELECT DISTINCT sid
• FROM Enrol as e1
• WHERE 70 lt ALL
• (SELECT grade
• FROM Enrol as e2
• WHERE e1.sid e2.sid)
• Forms of ALL
• lt ALL, lt ALL, ALL,
• gt ALL, gt ALL

34
Union

• Union allows one to union tuples from different
  tables.
• Get Student IDs for all students whose name
  starts with a J or who obtained an A for CS35A.
• SELECT sid FROM Student
• WHERE sname LIKE J
• UNION
• SELECT sid FROM Enrol
• WHERE cid CS35A
• AND grade gt 70

35
Intersect

• Allows one to intersect
• Get all IDs for students whose name begins with a
  J and who obtained an A for CS35A
• SELECT sid FROM Student
• WHERE sname LIKE J
• INTERSECT
• SELECT sid FROM Enrol
• WHERE cid CS35A
• AND grade gt 70

36
EXISTS and NOT EXISTS

• Counterpart of ALL
• Find name of students who have not obtained an A
  for any course
• SELECT sname FROM Student
• WHERE NOT EXISTS
• (SELECT FROM Enrol
• WHERE sid Student.sid
• AND grade gt 70)

37
Analysis of data

• In order to help do some primitive analysis of
  data, SQL has some built-in functions
• COUNT()
• COUNT(DISTINCT ltattrgt)
• SUM(DISTINCTltitemgt)
• where ltitemgt may be an abstraction and does not
  need to be a single attribute.
• AVG(DISTINCTltitemgt)
• MAX(ltitemgt)
• MIN(ltitemgt)

38
Some simple examples of data analysis in SQL

• How many students are registered for at least one
  course
• SELECT COUNT(DISTINCT sid)
• FROM Enrol
• Find the average grade for CS35A
• SELECT AVG(grade)
• FROM Enrol
• WHERE cid CS35A

39
Some more examples

• How many students have passed more than 5
  courses?
• SELECT COUNT()
• FROM Enrol as e1
• WHERE 5 lt
• (SELECT COUNT()
• FROM Enrol as e2
• WHERE e1.sid e2.sid)

40
Yet more examples

• How many students were above the average for
  CS35A?
• SELECT COUNT()
• FROM Enrol
• WHERE grade gt
• (SELECT AVG(grade)
• FROM Enrol
• WHERE cid CS35A)

41
Yet another example

• What is the name of the student who got the best
  mark for CS35A?
• SELECT sname
• FROM Student
• WHERE sid IN
• (SELECT sid
• FROM Enrol
• WHERE grade
• (SELECT MAX(grade)
• FROM Enrol
• WHERE cid CS35A))

42
GROUP BY

• A relation can be partitioned into groups
  according to some value. Analysis can then be
  done on these groups.
• What are the averages for the various courses?
• SELECT cid, AVG(grade)
• FROM Enrol
• GROUP BY cid

43
HAVING

• After partitioning, we can disqualify groups.
• What is average results for courses with
  enrollment of more than 10?
• SELECT cid, AVG(grade)
• FROM Enrol
• GROUP BY cid
• HAVING COUNT() gt 10
• COUNT is applied to each group separately.

44
Insertion

• INSERT INTO lttablegtltviewgt
• (ltattrgt ,ltattrgt)
• VALUES (ltitemsgt
• ltselect statementgt)
• Example
• INSERT INTO Enrol
• (cid, sid, grade)
• VALUES (CS35A, 123, 67)

45
Insertion through a SELECT statement

• For each course, get the average and insert into
  a RES table
• INSERT INTO Res (cid, average)
• SELECT cid, AVG(grade)
• FROM Enrol
• GROUP BY cid

46
Deletion

• DELETE FROM lttablegt
• ltWHERE clausegt
• Example
• DELETE FROM Enrol
• WHERE cid CS35A
• Difference between DELETE and DROP
• DELETE FROM ENROL
• DELETE empties the table but leaves the table and
  indexes.

47
Updating tables

• UPDATE lttablegt
• SET ltattrgt ltexprgt
• , ltattrgt ltexprgt
• ltWHERE CLAUSEgt
• Example Give everybody 10 extra marks for CS35A
• UPDATE Enrol
• SET grade grade 10
• WHERE cid CS35A

48
Views

• Views are derived tables whose definition is
  stored and whose content is computed.
• Can be used as base table for retrieval and view
  definition.
• Exact condition for updating an open problem.
• Currently only update iff
• derived form single base table
• and, has rows and attributes corresponding to a
  unique and distinct row in base table.

49
Advantages of views

• Views are SQLs external schemas. They are
  useful
• Users are immune to database growth
• Users are immune to database restructuring
  (logical data independence)
• Simplified user perception
• Different views of same data for different users
• Automatic security for hidden data.

50
Creation and deletion of views

• CREATE VIEW ltviewgt
• (ltcolnamegt,ltcolnamegt)
• AS select-statement
• Example
• CREATE VIEW Result
• (cid, average)
• AS SELECT cid, AVG(grade)
• FROM Enrol
• GROUP BY cid
• Deletion
• Not supported by all DBMSs
• DELETE FROM VIEW ltviewgt
• DELETE VIEW RESULT

51
The view update problem

• The view Result as defined above cannot be
  updated, as any updates cannot be translated into
  the base table.
• The DB administrator should decide whether a view
  is updatable.

52
Authorization Mechanism

• The authorization mechanism allows one to give
  other users permission to access and update data
  in a view or table.
• The owner must explicitly grant necessary
  privileges to others, as by default the owner has
  all privileges and others have none.

53
GRANT and REVOKE

• GRANT ltprivilegegt
• ON lttablegt ltviewgt
• TO ltusergt ,ltusergt
• PUBLIC
• WITH GRANT OPTION
• REVOKE ltprivilegegt
• ON lttablegt ltviewgt
• FROM ltusergt ,ltusergt
• PUBLIC

54
Grantable privileges

• These privileges are allowed
• SELECT
• INSERT
• UPDATE
• DELETE
• ALTER
• INDEX
• permission to create or drop indexes on a table.
• ALL

55
Use of SQL

• Most users, other than the database manager, will
  not directly interact with SQL.
• Typically, one sets up some graphical interfaces
  (forms) for user interaction. However,
  underlying the forms are SQL queries.