[PDF] - SQL - The Language of Databases Developed by IBM in the 1970s PDF Document

SLIDE 1

1

SY306 Web and Databases for Cyber Operations SQL: Structured Query Language

2

SQL - The Language of Databases

Developed by IBM in the 1970s
Create and process database data
SQL programming is a critical skill !!!

SLIDE 2

2

3

Relational Query Languages

A major strength of the relational model:
supports simple, powerful querying of data
Ad-hoc queries
High-level (declarative) languages
Queries can be written intuitively
DBMS is responsible for efficient evaluation.

4

The SQL SELECT Statement

Basic SQL Query:

SELECT [DISTINCT] column_name(s) | * FROM table_name(s) [WHERE conditions] [ORDER BY column_name1 [ASC|DSC], …]

SLIDE 3

3

5

Selecting All Columns: The Asterisk (*) Keyword

SELECT * FROM Students;

Student Number Student LastName Student FirstName Email PhoneNumber Major 190 Smith John jsmith@usna.edu 410-431-3456 SCY 673 Doe Jane jdoe@usna.edu SCY 312 Doe Jane jdoe2@usna.edu 443-451-7865 Math 6

Specific Columns and Rows from One Table

SELECT StudentNumber, StudentLastName, StudentFirstName FROM Students WHERE Major = ‘SCY’;

Student Number Student LastName Student FirstName

190 Smith John 673 Doe Jane

SLIDE 4

4

7

The DISTINCT Keyword

SELECT StudentLastName FROM Students;

StudentLastName

Smith Doe Doe

SELECT DISTINCT StudentLastName FROM Students;

StudentLastName

Smith Doe

8

Class Exercise

Department(DeptName, ChairName, WebAddress,

DivName)

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

SLIDE 5

5

9

WHERE Clause Options

AND, OR
IN, NOT IN, BETWEEN
LIKE

Wild cards:

SQL-92 Standard (SQL Server, Oracle, etc.):
_ = Exactly one character
% = Any set of characters (zero or more)
MS Access
? = Exactly one character
* = Any set of characters (zero or more)
Example:

Students(SNb, SName, Email, Major) Find alpha and name of SCY or SCS students with SNb starting with ‘16’

SELECT SNb, SName FROM Students WHERE SNb LIKE ’16%’ AND Major IN (‘SCY’, ‘SCS’)

10

Sorting the Results

SELECT [DISTINCT] column_name(s) | * FROM table_name(s) [WHERE conditions] [ORDER BY column_name(s) [ASC/DESC]]

Example: Students(SNb, SName, Email, Major) SELECT SNb, SName FROM Students ORDER BY SName ASC, SNb DESC

SLIDE 6

6

11

Summary (partial)

SELECT [DISTINCT] column_name(s)

FROM table_name WHERE conditions ORDER BY column_name(s) [ASC/DESC]

12

SELECT from Two or More Tables

SNb SName Email 190 Smith jsmith@usna.edu 673 Doe jdoe@usna.edu 312 Doe jdoe2@usna.edu Cid CName CDept SY306 WebDbScy ComSci SY301 Data Structures ComSci SM121 Calculus1 Math SNb Cid Semester 190 SY301 Fall2015 312 SY306 Spring2015

SELECT SName FROM Students S, Enrolled E WHERE S.Snb = E.SNb AND E.Cid = ‘SY306’ Find the names of students enrolled in SY306 Students Courses Enrolled

SLIDE 7

7

13

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.

14

Example Conceptual Evaluation

S.SNb SName Email E.SNb Cid Semester 190 Smith jsmith@usna.edu 190 SY301 Fall2015 190 Smith jsmith@usna.edu 312 SY306 Spring2015 673 Doe jdoe@usna.edu 190 SY301 Fall2015 673 Doe jdoe@usna.edu 312 SY306 Spring2015 312 Doe jdoe2@usna.edu 190 SY301 Fall2015 312 Doe jdoe2@usna.edu 312 SY306 Spring2015

SELECT SName FROM Students S, Enrolled E WHERE S.Snb = E.SNb AND E.Cid = ‘SY306’

SLIDE 8

8

15

Example Conceptual Evaluation

S.SNb SName Email E.SNb Cid Semester 190 Smith jsmith@usna.edu 190 SY301 Fall2015 190 Smith jsmith@usna.edu 312 SY306 Spring2015 673 Doe jdoe@usna.edu 190 SY301 Fall2015 673 Doe jdoe@usna.edu 312 SY306 Spring2015 312 Doe jdoe2@usna.edu 190 SY301 Fall2015 312 Doe jdoe2@usna.edu 312 SY306 Spring2015

SELECT SName FROM Students S, Enrolled E WHERE S.Snb = E.SNb AND E.Cid = ‘SY306’

16

Example Conceptual Evaluation

S.SNb SName Email E.SNb Cid Semester 190 Smith jsmith@usna.edu 190 SY301 Fall2015 190 Smith jsmith@usna.edu 312 SY306 Spring2015 673 Doe jdoe@usna.edu 190 SY301 Fall2015 673 Doe jdoe@usna.edu 312 SY306 Spring2015 312 Doe jdoe2@usna.edu 190 SY301 Fall2015 312 Doe jdoe2@usna.edu 312 SY306 Spring2015

SELECT SName FROM Students S, Enrolled E WHERE S.Snb = E.SNb AND E.Cid = ‘SY306’

SName Doe

SLIDE 9

9

17

Modified Query

SELECT S.SNb FROM Students S, Enrolled E WHERE S.SNb = E.SNb AND E.Cid =‘SY306’

Would the result be different with

DISTINCT?

18

Class Exercise

Students(SNb, SName, Email)
Courses(Cid,CName, Dept)
Enrolled(SNb,Cid, Semester)
Find the student number and name for

each student enrolled in ‘Spring2015’ semester

Find the names of all students enrolled in

‘ComSci’ courses

SLIDE 10

10

Limit (MySQL specific)

Limit the number of rows in the result
SELECT [DISTINCT] column_name(s) | aggregate_expr

FROM table_name(s) WHERE row_conditions ORDER BY column_name(s) [ASC/DESC] LIMIT [offset,] row_count

Returns at most row_count rows, starting with offset

(offset of first row is 0)

19

UNION

Students(Alpha, Lname, Fname, Email)
GraduatedStudents(Alpha2, Lname2, Fname2, Email2)
List the Alpha, last name and first name of all students

(current or graduated) SELECT Alpha, Lname, Fname FROM Students UNION SELECT Alpha2, Lname2, Fname2 FROM GraduatedStudents

Kroenke, Database Processing 20

Same number of columns and data

types in the 2 select

Duplicates in results are eliminated

by default (use UNION ALL if want to keep duplicates)

SLIDE 11

11

21

Summary (partial)

SELECT [DISTINCT] column_name(s)

1

SY306 Web and Databases for Cyber Operations SQL: Structured Query Language

SQL - The Language of Databases

2

Relational Query Languages

The SQL SELECT Statement

SELECT [DISTINCT] column_name(s) | * FROM table_name(s) [WHERE conditions] [ORDER BY column_name1 [ASC|DSC], …]

3

Selecting All Columns: The Asterisk (*) Keyword

SELECT * FROM Students;

Specific Columns and Rows from One Table

4

The DISTINCT Keyword

SELECT StudentLastName FROM Students;

SELECT DISTINCT StudentLastName FROM Students;

Class Exercise

DivName)

5

WHERE Clause Options

Students(SNb, SName, Email, Major) Find alpha and name of SCY or SCS students with SNb starting with ‘16’

Sorting the Results

Example: Students(SNb, SName, Email, Major) SELECT SNb, SName FROM Students ORDER BY SName ASC, SNb DESC

6

Summary (partial)

FROM table_name WHERE conditions ORDER BY column_name(s) [ASC/DESC]

SELECT from Two or More Tables

7

SELECT - Conceptual Evaluation Strategy

the following conceptual evaluation strategy:

to compute a query!

compute the same answers.

Example Conceptual Evaluation

8

Example Conceptual Evaluation

Example Conceptual Evaluation

9

Modified Query

SELECT S.SNb FROM Students S, Enrolled E WHERE S.SNb = E.SNb AND E.Cid =‘SY306’

DISTINCT?

Class Exercise

each student enrolled in ‘Spring2015’ semester

‘ComSci’ courses

10

Limit (MySQL specific)

FROM table_name(s) WHERE row_conditions ORDER BY column_name(s) [ASC/DESC] LIMIT [offset,] row_count

(offset of first row is 0)

UNION

(current or graduated) SELECT Alpha, Lname, Fname FROM Students UNION SELECT Alpha2, Lname2, Fname2 FROM GraduatedStudents

11

Summary (partial)

FROM table_name(s) WHERE conditions ORDER BY column_name(s) [ASC/DESC] LIMIT [offset,] row_count