1970 - Codd Defines Relational Database Model

1974 - 1978 IBM System/R Project

1979 - Oracle Commercial RDBMS

1981 - INGRES & IBM SQL/DS

1982 - 1986 ANSI SQL Standard Developed/Ratified

1987 - ISO SQL Standard

Many versions exist from different manufacturers

ISO/ANSI Standard contains less than 30 statement types

Grouped into:

• Data manipulation
• Data definition
• Access control
• Transaction control
• Programmatic sql

Used to specify a new relation by giving it a name and specifying each of its attributes

create table student

(stuid char (5) not null,

stln varchar(15) not null,

stfn varchar(15) not null,

major varchar(15) not null,

credits integer );

Used to delete a relation

drop table student;

Used to add an attribute to one of the existing relations in the database

alter table student add

birth_date char(9);

A query can consist of up to six clauses, the first two mandatory :

select <attribute list>

from <table list>

[where <condition>]

[group by <group attribute(s)>]

[having <group condition>]

[order by <attribute list>]

• <attribute list> is a list of attribute names whose values are to be retrieved by the query.
• <table list> is a list of the relation required to process the query.
• <condition> is a conditional (boolean) expression that identifies the tuples to be retrieved by the query.

The select - clause lists the attributes or functions to be retrieved.

The from - clause specifies all relations needed in the query but not those needed in nested queries.

The where - clause specifies the conditions for selection of tuples from theses relations.

Group by specifies grouping attributes, whereas having specifies a condition on the groups being selected rather than on the individual tuples.

The built - in aggregate functions count, sum, min, max, and avg are used in conjunction with grouping.

Order by specifies an order for displaying the result of a query.

Problem:Get names, ids and number of credits of all math majors from a university database

SQL Query:

select stfn, stln, stuid, credits

from student

where major = 'math;

p _{< stfn, stln, stuid, credits >} (s _{major = mat}(student))

Single relation name in from-clause is similar to select - project pair of relational algebra operations

Problem: Get the names of all math majors who have more than 30 credits.

SQL Query:

select stfn, stln

from student

where major = 'math and credits > 30;

Problem: Find ids and names of all students taking art103a.

SQL Query:

select enrolment.stuid, stfn, stln

from student, enrolment

where course# = 'art103a'

and enrolment.stuid = student.stuid;

select-project-join query: course# = 'art103a' selection condition;

enrolment.stuid = student.stuid - join condition.

Order by

Used to order the tuples in the result. the keyword asc can be used to specify ascending order explicitly, desc - descending order

Problem:Get names and ids of all faculty members, arranged in alphabetical order by name.

SQL Query:

select facid, facname

from faculty

order by facname;

Dealing With Ambiguous Attribute Names

• prefixing the relation name

Problem:Get a list of all courses that meet in the same room, with their schedules and room numbers

SQL Query:

select copy1.course#, copy1.sched, copy1.room, copy2.course#, copy2.sched

from class copy1, class copy2

wherecopy1.room = copy2.room

andcopy1.course# < copy2.course#;

Use of '*'

Which stands for all the attributes

Problem: Get all information about csc faculty.

SQL Query:

select *

fromfaculty

where dept = 'csc';

SQL does not treat a relation as a set.

Not automatically eliminates the duplicate tuples - expensive operation, elimination by the user wish, using aggregation functions

Key distinct, meaning that only distinct tuples should remain in the result

Problem:Find the number of credits

SQL Query:

select credits

from student;

SQL Query:

select distinct credits

from student;

Complete select query within the where-clause of another query

Problem:Get an alphabetical list of names and ids of all students in any class taught by faculty member f110.

SQL Query:

select stfn, stln, stuid

from student

where stuid in

(select stuid

from enrolment

where course# in

(select course#

from class

where facid = 'f110'))

order by stfn asc;

• count
• sum
• avg
• max
• min

Problem:Find the number of departments that have faculty in them.

SQL Query:

select count (distinct dept)

from faculty;

Problem: Find the average number of credits students have.

SQL Query:

select avg (credits)

from student;

Problem: Find the student with the largest number of credits.

SQL Query:

select stuid, stfn, stln

from student

where credits =

(select max (credits)

from student);

Problem: Find the id of the student( s ) with the highest grade in any course.

SQL Query:

select stuid

fromenrolment

wheregrade =

(select min ( grade )

from enrolment);

Problem: Assuming that each course is three credits, list, for each student, the number of courses that he or she has completed.

SQL Query:

select stuid, 'number of courses =', credits/3

from student;

Group by - clause specifies the grouping attributes, which must also appear in the select-clause, so that the value of applying. Each function on the group of tuples appears along with the value of the grouping attribute(s).

Problem: Find each course, show the number of students enrolled.

SQL Query:

select course#, count(*)

from enrolment

group by course#;

Can appear only in conjunction with group by-clause. Having provides a condition on the group of tuples associated with each value of the grouping attributes, and only the groups that satisfy the condition are retrieved in the result of the query.

Problem: Find all courses in which fewer than three students are enrolled.

SQL Query:

select course#

from enrolment

group by course#

having count(*) < 3;

SQL allows queries that check if a value is null.

Problem: Find the stuid and course# of all students whose grades in that course are missing.

SQL Query:

select course#, stuid

from enrolment

where grade is null;

• SQL supports a simple data structure, namely tables.
• SQL supports the relational algebra operators project, select, and join and operates on entire relations to generate new relations.
• SQL provides physical data independence to a large extent.
• SQL combines table creation, querying and updating, and view definition into a uniform syntax.
• SQL can be used both as a stand-alone query language and within a general purpose programming language by embedding it within a host language.
• The language can be optimised and compiled or may be interpreted and executed on-line.

• SQL does not provide a means of declaring the primary key of a relation.
• SQL does nod address the notion of foreign keys and the related notion of referential integrity at all.
• SQL does not show the meaningful relationship among tables to the user, such as those specifying referential integrity constraints; rather, all such relationships must explicitly be specified as join conditions in a SQL query.
• SQLprovides no mechanism for specifying strategies for view updates
• Although SQL is somewhat based on the relational calculus, the universal quantifier are not satisfactorily handled.
• SQL has no easy-to-use facility for saving temporary results of queries and reusing them.
• Set operators in SQL are restricted and sometimes look unnatural.

Practical Knowledge

Egs

sales

1. select area, qtysold from sales;

2. select area, qtysold from sales where value > 7000;

3. select distinct area from sales;

4. Group by creates sub-totals:-

select avg(qtysold) from sales;

select avg(qtysold) from sales group by area;

5. Having selects/rejects row groups

select avg(qtysold) from sales group by area

having avg(qtysold)<23;

Insert - adds new data

insert into table [(column-list)]

values {(insert-list) | query-spec}

Eg

insert into sales (salesman,area,qtysold,value)

values (351,08,3,675) ;

or

insert into sales values (351,08,3,675);

sales

Note values can be taken from other tables, using select clause, instead of values!

Delete - removes data

delete from table [where search-condition]

Egs

 1. All rows of a table:-

delete from sales;

2. Selected rows:-

delete from sales where salesman=351;

sales

3. Using sub-queries to select items

costcentres

delete from sales

where area =

( select sarea from costcentres

where costs > 7000 );

sales

Update - modifies data

update table set assignment-list [where search-condition]

Eg

update sales set qtysold = 23, value = 6975

where salesman = 223;

 sales

sales

Foreign Key 

costcentres

select salesman, value, manager

from sales, costcentres

where area = sarea;

Notes

1. Both tables need to be specified

2. The 'where' clause specifies a test between

primary & foreign key

3. The keys do not have to be included in the output

result

Row selection criteria may be used in conjunction:-

select salesman, value, manager

from sales, costcentres

where area = sarea

and costs > 6000;

Other query possibilities are:-

- More than two tables

- Matching several columns in related tables

- Using non-key columns for matching

- 'Non-equi joins' eg

select salesman, value, manager

from sales, costcentres

where value > costs;

Qualified Column Names

- Where column names are the same in two/more tables

tablename.columnname

Aliases

- Provide a means of joining a table to itself:-

salesreps

Eg to list all salesreps, and their manager:-

select emps.salesman, mgrs.salesman

from salesreps emps, salesreps mgrs

where emps.manager = mgrs.salesman;

Data Definition

- SQL Definition is based on three verbs:-

Create which creates a table,

Drop to delete tables

Alter to change structure

 - create table table-name ( item-list )

where item-list is repetitions of:- 

column data-type [ default { literal | user |null}]

| unique ( column-list )

| primary key ( column-name-list )

| foreign key key-list

 Eg

create table offices

( office integer not null,

city varchar(15) not null,

region varchar(10) not null default "yorkshire",

mgr integer,

target money,

sales money not null,

primary key (office),

foreign key hasmgr (mgr)

references salesreps

on delete set null,

unique (city) )

The set of relations listed below is partial description of an auto service database. Create an relational database containing exactly this set of relations.

Relation Customer

Relation Service

Relation Vehicle

Formulate each of the queries below in the sql language and run them on the database created above.

 Query 1: Find the names of all customers who live in Toronto.

Query 2: Find the make, model and year of all cars owned by Jones R.

Query 3: Find the names and addresses of all customers who own a Toyota.

Query 4 : Find the license plate number of all cars owned by Jones R.

Query 5: Find the customer name and make of car for every customer who has a 1989 car.

Query 6: Find the names of all customers who live in Toronto and own a Toyota.

Query 7: Find the names and addresses of all customers who own a Ford and who are scheduled for service on sep 23, 1992.

Query 8: Find the names of all customers who own a Chevrolet astro which was serviced on oct 29, 1992.

Query 9: Find the names of all customers whose home telephone number is the same as their business telephone number.

Query 10: Find the total service charges for Philip J.

Query 11: Find how many cars Jones R. owns.

Query 12: Find the largest service charge and the name of the customer who paid it.

Query 13: Find the lowest service charge and the name of the mechanic who did the service.

Query 14: Find the total number of cars and their licence plate numbers for cars which have not been scheduled for service.

Query 15: Find the names and telephone numbers of the owners of those cars which have been scheduled for service, but not yet serviced.

Query 16: For each customer who has a service charge, find the costumer's name and his total charge.

Query 17: For each mechanic who has repaired a car, find the mechanic's name and his total earnings.

Query 18: Find out when Gerus H. car is scheduled for service.

Query 19: Find how many cars where serviced on the same day that they were scheduled for service.

Query 20: For each city, find the total number of cars for that city in the database.

Query 21: Find which car has the highest service rate (i.e., has been serviced or scheduled for service the most times).

Query 22: For each make in the data base, find the average service rate for that make.

Query 23: Find the percentage served cars for each city in the database.