Goals Relational Query Languages SQL DDL and DML ..., Schemes and Mind Maps of Mathematics

Data manipulation language (DML) statements. ▫ Used for queries and data modification. ▫ INSERT, DELETE, UPDATE, SELECT. 5.

Typology: Schemes and Mind Maps

2022/2023

Uploaded on 03/01/2023

wualter
wualter 🇺🇸

4.8

(96)

287 documents

1 / 6

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
1
1
IT420: Database Management and
Organization
SQL: Structured Query
Language
(Chapter 7)
2
Goals
SQL: Data Definition Language
CREATE
ALTER
DROP
SQL: Data Manipulation Language
INSERT
DELETE
UPDATE
SELECT
3
Relational Query Languages
A major strength of the relational model:
supports simple, powerful querying of data
Queries can be written intuitively, and the
DBMS is responsible for efficient
evaluation.
4
SQL DDL and DML
SQL statements can be divided into two
categories:
Data definition language (DDL) statements
Used for creating and modifying tables, views, and
other structures
CREATE, DROP, ALTER
Data manipulation language (DML)
statements.
Used for queries and data modification
INSERT, DELETE, UPDATE, SELECT
5
Creating Tables
CREATE TABLE table_name(
column_name1 column_type1 [constraints1],
…,
[[CONSTRAINT constraint_name] table_constra int]
)
Table constraints:
NULL/NOT NULL
PRIMARY KEY (columns)
UNIQUE (columns)
CHECK (conditions)
FOREIGN KEY (local_columns) REFERENC ESf oreign_table
(foreign_columns) [ON DELETE action_d ON UPDATE act ion_u]
Specify surrogate key in SQL Server:
column_name int_type IDENTITY (seed, increment)
6
CREATE TABLE Example
CREATE TABLE Students
(StudentNumber integer NOT NULL,
StudentLastName char(18) NOT NULL,
StudentFirstName char(18) NOT NULL,
Email char(50),
PhoneNumber char(18),
MajorDepartmentName char(18),
CONSTRAINT PK_Students PRIMARY KEY (StudentNumber),
CONSTRAINT U_Email UNIQUE (Email),
CONSTRAINT FK_Dept FOREIGN KEY(MajorDe partmentName)
REFERENCES DEPARTMENTS(Dep artmentName)
ON DELETE NO ACTION ON UPDATE CASCAD E
)
pf3
pf4
pf5

Partial preview of the text

Download Goals Relational Query Languages SQL DDL and DML ... and more Schemes and Mind Maps Mathematics in PDF only on Docsity!

IT420: Database Management and

Organization

SQL: Structured Query

Language

(Chapter 7)

Goals

 SQL: Data Definition Language

 CREATE

 ALTER

 DROP

 SQL: Data Manipulation Language

 INSERT

 DELETE

 UPDATE

 SELECT

Relational Query Languages

 A major strength of the relational model:

 supports simple, powerful querying of data

 Queries can be written intuitively, and the

DBMS is responsible for efficient

evaluation.

SQL DDL and DML

 SQL statements can be divided into two

categories:

 Data definition language (DDL) statements

 Used for creating and modifying tables, views, and

other structures

 CREATE, DROP, ALTER

 Data manipulation language (DML)

statements.

 Used for queries and data modification

 INSERT, DELETE, UPDATE, SELECT

Creating Tables

CREATE TABLE table_name(

column_name1 column_type1 [constraints1],

[[ CONSTRAINT constraint_name] table_constraint]

Table constraints:

 NULL/NOT NULL

 PRIMARY KEY (columns)

 UNIQUE (columns)

 CHECK (conditions)

 FOREIGN KEY (local_columns ) REFERENCES foreign_table

( foreign_columns ) [ON DELETE action_d ON UPDATE action_u ]

Specify surrogate key in SQL Server:

column_name int_type IDENTITY (seed, increment)

CREATE TABLE Example

 CREATE TABLE Students

(StudentNumber integer NOT NULL,

StudentLastName char(18) NOT NULL,

StudentFirstName char(18) NOT NULL,

Email char(50),

PhoneNumber char(18),

MajorDepartmentName char(18),

CONSTRAINT PK_Students PRIMARY KEY (StudentNumber),

CONSTRAINT U_Email UNIQUE (Email),

CONSTRAINT FK_Dept FOREIGN KEY(MajorDepartmentName)

REFERENCES DEPARTMENTS(DepartmentName)

ON DELETE NO ACTION ON UPDATE CASCADE

FOREIGN KEY Constraints

Majors

I:SN

U:SN

D:SN

U:C

DEPARTMENTS

DepartmentName: char(18) Phone: char(18) Building: char(18) Room: integer

STUDENTS

StudentNumber: integer StudentLastName: char(18) StudentFirstName: char(18) Email: varchar(50) PhoneNumber: char(18) DepartmentName: char(18) (FK)

443-451-

410-431-

PhoneNumber

Mathematics

Computer Science

MajorDepartmentName

312 Doe Bob [email protected]

673 Doe Jane [email protected]

190 Smith John [email protected]

Student Email FirstName

Student LastName

Student Number

Computer Science 410-293-6800 Michelson Hall 340

History 410-293-2255 Sampson Hall 120

Mathematics 410-293-4573 Michelson Hall 308

DepartmentName Phone Building Room

CREATE TABLE Departments

(DepartmentName char(18),

Phone char(18) NOT NULL,

Building char(18),

Room integer,

PRIMARY KEY (DepartmentName)

FOREIGN KEY Constraints

CREATE TABLE Students

(StudentNumber integer,

StudentLastName char(18) NOT NULL,

StudentFirstName char(18) NOT NULL,

Email char(50) NULL,

PhoneNumber char(18) NULL,

MajorDepartmentName char(18) NULL,

PRIMARY KEY (StudentNumber),

UNIQUE(Email),

FOREIGN KEY (MajorDepartmentName)

REFERENCES Departments (DepartmentName)

ON DELETE SET NULL

ON UPDATE CASCADE

 4 options on

deletes and

updates:

 NO ACTION

(delete/update is

rejected)

 CASCADE

 SET NULL

 SET DEFAULT

Modifying Tables

 ALTER TABLE table_name clause

Clauses:

ADD COLUMN column_name column_type [constraints]

DROP COLUMN column_name

ALTER COLUMN / MODIFY – DBMS specific!

ADD CONSTRAINT constraint

DROP CONSTRAINT constraint_name

ALTER TABLE Examples

 ALTER TABLE Students ADD COLUMN BirthDate

datetime NULL

 ALTER TABLE Students DROP COLUMN BirthDate

 ALTER TABLE Student ADD CONSTRAINT

FK_Department

FOREIGN KEY (MajorDepartmentName)

REFERENCES Departments (DepartmentName)

ON DELETE NO ACTION

ON UPDATE CASCADE

Removing Tables

 DROP TABLE table_name

DROP TABLE Departments;

 If there are constraints dependent on table:

 Remove constraints

 Drop table

ALTER TABLE Students

DROP CONSTRAINT FK_Department;

DROP TABLE Departments;

SQL DDL and DML

 Data definition language (DDL)

statements

 Used for creating and modifying tables, views, and

other structures

 CREATE, ALTER, DROP

 Data manipulation language (DML)

statements.

 Used for queries and data modification

 INSERT, DELETE, UPDATE, SELECT

Specific Columns and Rows from

One Table

SELECT StudentNumber,

StudentLastName,

StudentFirstName

FROM Students

WHERE MajDeptName = ‘ComSci’;

673 Doe Jane

190 Smith John

Student

FirstName

Student

LastName

Student

Number

The DISTINCT Keyword

SELECT SName

FROM Students;

Doe

Doe

Smith

StudentLastName

SELECT DISTINCT

SName

FROM Students;

Doe

Smith

StudentLastName

Class Exercise

 Division(Name, Building, OfficeNb)

 Department(DeptName, ChairName, WebAddress,

DivName)

 Create tables

 Modify Department to add a FK constraint for DivName

 Create table Colleges with same structure as Division

 Insert everything from Division into Colleges

 Remove Division table

 Find the name of the Chair of the ‘Math’ Department

Students, Courses, Enrolled

312 Doe [email protected]

673 Doe [email protected]

190 Smith [email protected]

SNb SName Email

SM121 Calculus1 Math

IT340 Networks ComSci

IT420 Database ComSci

Cid CName CDept

312 IT420 Fall

190 IT340 Spring

SNb Cid Semester

SELECT SName

FROM Students S, Enrolled E

WHERE S.Snb = E.SNb AND E.Cid = ‘IT420’

Find the names of students enrolled in IT

SELECT - Conceptual Evaluation

Strategy

 Semantics of an SQL query defined in terms of

the following conceptual evaluation strategy:

 Compute the cross-product of table_names

 Discard resulting rows if they fail condition

 Delete columns that are not in column_names

 If DISTINCT is specified, eliminate duplicate rows

 This strategy is probably the least efficient way

to compute a query!

 An optimizer will find more efficient strategies to

compute the same answers.

Example Conceptual Evaluation

312 Doe [email protected] 312 IT420 Fall

312 Doe [email protected] 190 IT340 Spring

673 Doe [email protected] 312 IT420 Fall

E.SNb

IT

IT

IT

Cid

Spring

Fall

Spring

Semester

[email protected]

[email protected]

[email protected]

Email

673 Doe

190 Smith

190 Smith

S.SNb SName

SELECT SName

FROM Students S, Enrolled E

WHERE S.Snb = E.SNb AND E.Cid = ‘IT420’

Example Conceptual Evaluation

312 Doe [email protected] 312 IT420 Fall

312 Doe [email protected] 190 IT340 Spring

673 Doe [email protected] 312 IT420 Fall

E.SNb

IT

IT

IT

Cid

Spring

Fall

Spring

Semester

[email protected]

[email protected]

[email protected]

Email

673 Doe

190 Smith

190 Smith

S.SNb SName

SELECT SName

FROM Students S, Enrolled E

WHERE S.Snb = E.SNb AND E.Cid = ‘IT420’

Example Conceptual Evaluation

312 Doe [email protected] 312 IT420 Fall

312 Doe [email protected] 190 IT340 Spring

673 Doe [email protected] 312 IT420 Fall

E.SNb

IT

IT

IT

Cid

Spring

Fall

Spring

Semester

[email protected]

[email protected]

[email protected]

Email

673 Doe

190 Smith

190 Smith

S.SNb SName

SELECT SName

FROM Students S, Enrolled E

WHERE S.Snb = E.SNb AND E.Cid = ‘IT420’

Doe

SName

Modified Query

SELECT SNb

FROM Students S, Enrolled E

WHERE S.Snb = E.SNb AND E.Cid =‘IT420’

 Would the result be different with

DISTINCT?

Sorting the Results

SELECT …

FROM …

[WHERE …]

ORDER BY column_name(s) [ASC/DESC]

Example:

SELECT SNb, SName

FROM Students

ORDER BY SName ASC, SNb DESC

LIKE and Wildcards

SELECT *

FROM Students

WHERE SNb LIKE ‘9%’

 SQL 92 Standard (SQL Server, Oracle, etc.):

 _ = Exactly one character

 % = Any set of one or more characters

 MS Access

? = Exactly one character

 * = Any set of one or more characters

WHERE Clause Options

 AND, OR

 IN, NOT IN, BETWEEN

SELECT SNb

FROM Students S, Enrolled E

WHERE S.SNb = E.Nb AND

E.Cid NOT IN (‘ComSci’, ‘Math’)