SQL Lecture 10: Structured Query Language - Database Management and Commands, Study notes of Calculus

Download SQL Lecture 10: Structured Query Language - Database Management and Commands and more Study notes Calculus in PDF only on Docsity!

KTH ROYAL INSTITUTE

OF TECHNOLOGY

Lecture 10

Structured Query Language

Summary from previous lecture

1. Entity-relation diagrams: entity, relation, attributes.

2. Database structure:

• Tables are known as ”Relations”
• Rows are ”Tuples”
• Columns are ”Attributes”

3. Normalisation of database.

SQL definition

SQL stands for “Structured Query Language.” The Structured

Query Language is a relational database language. By itself,

SQL does not make a DBMS. SQL is a medium which is used

to communicate to the DBMS.

Some of the features of SQL:

• SQL is a language used to interact with the database
• SQL is a data access language
• SQL is based on relational tuple calculus
• SQL is a standard relational database management language
• SQL is a “nonprocedural” or “declarative” language.

SQL coding

Most DBMS allow SQL to be used in two distinct ways:

• Interactive SQL. SQL commands can be typed at the

command line directly. The DBMS interprets and processes

the SQL commands immediately, and the results are

displayed.

• Programmatic SQL. SQL statements are embedded in a host

language such as Java, C, Python etc. The host language

provides the necessary looping and branching structures and

the interface with the user, while SQL provides the

statements to communicate with the DBMS.

SQL commands

SQL commands can be classified into three types:

1. Data Definition Language commands (DDL)

2. Data Manipulation Language commands (DML)

3. Data Control Language commands (DCL)

Create Table Command

Steps in Table Creation

1. Identify datatypes for attributes

2. Identify columns that can and cannot be null

3. Identify columns that must be unique

4. Identify primary key–foreign key mates

5. Determine default values

6. Identify constraints on columns (domain specifications)

7. Create the table

Contents

• SQL description:
  • SQL definition
  • SQL datatypes
• SQL Syntax
  • Relation Calculus
  • SQL commands

Tuple Relational Calculus

List of main operations used to manipulate Relations:

• INSERT
• DELETE
• UPDATE
• SELECT
• JOIN
• UNION

DELETE command

DELETE is a unary operation – it operates on a single

Relation and deletes a Tuple fulfilling criteria from a Relation

ID Name Grade

1 Jill D

2 Bob B

4 Lars A

ID Name Grade

1 Jill D

2 Bob B

3 Steve C

4 Lars A

DELETE tuple FROM Relation WHERE attribute# = x

UPDATE command

UPDATE is a unary operation – it operates on a single

Relation and modifies an attribute in Tuple fulfilling criteria

in a Relation

ID Name Grade

1 Jill D

2 Bob B

4 Lars A

UPDATE Relation SET t.a2=data WHERE t.a1=x

ID Name Grade

1 Jill D

2 Bob B

4 Lars E

SELECT command

Extension to SELECT command:

• SELECT * FROM R1 WHERE a1=6;
• SELECT * FROM R1 GROUP BY a1;
• SELECT * FROM R1 ORDER BY a1 (ASC, DESC);
• SELECT * FROM R1 HAVING a2>3;
• SELECT a1,a3 FROM R3 WHERE a2 IN ( value1 , value2 );

JOIN command

JOIN is a binary operation – it operates two Relations. The JOIN

operation creates a new relation R3 from relations R1 & R

based on common attributes (keys).

X

Not Normalised??

Course Professor

EH2745 Nordström

EH2751 Nordström

EJ2301 Soulard

EG2200 Amelin

Professor Office

Nordström Osquldas väg 10, floor 7

Amelin Teknikringen 33, floor 2

Soulard Teknikringen 33, floor 1

Course Professor Office

EH2745 Nordström Osquldas väg 10, floor 7

EH2751 Nordström Osquldas väg 10, floor 7

EJ2301 Soulard Teknikringen 33, floor 1

EG2200 Amelin Reknikringen 35, floor 2

SELECT R1.a1, R1.a2, R2.a2 FROM R1 JOIN R2 ON R1.A2=R2.A