Introduction to SQL: Learn the Basics and Beyond
undefined
IR 101:
Beginning SQL in IR
Kelly Wahl
Director of Statistical Analysis and Assessment Coordinator
UCLA
Learning Outcomes
•
Know what SQL is
•
Read a basic SQL SELECT statement
•
Find online opportunities to learn the language
•
Recognize examples of SQL syntax and identify what they do
•
Consider how the results from a SELECT statement can be formatted to
meet your needs
What is SQL?
•
Structured Query Language (SQL)
•
Often pronounced “sequel” – because the original acronym was SEQUEL
(Structured English QUEry Language)
•
Developed by IBM in the early 70’s
•
Changed its name because another company had already trademarked
SEQUEL
How And When Do We Use SQL?
•
Its name gives away its purpose: It is a programming
language
used for
managing data and
query
ing databases. SQL has been “
structured
” to be
clear, to sequence commands, to allow options to be pursued, and to block
(i.e., to treat groups of statements as one).
•
Relational Database Management Systems (RDBMS) can use it.
•
Oracle Database, SQL Server, and MySQL are examples of database
software that use this language for data access and management.
How Can I Learn SQL on My Own?
Free Tutorials Online:
•
http://w3schools.com/sql
•
http://sqlzoo.net
•
http://sqlbolt.com
•
http://sqlteaching.com
•
http://quackit.com/sql/tutorial/
Give Me Your Info Online (But Free):
•
https://community.modeanalytics.com/sql/
•
https://www.codecademy.com/learn/learn-sql
•
https://academy.vertabelo.com
Properties of Relational Databases
•
Relational databases are for storing and retrieving related information.
•
They can be explained with diagrams showing the way the data are
“modeled.”
•
Records are stored as rows in tables that have columns for each variable
being stored per case.
A Relational Database:
ID | Name | Gender
ID | Term | Major
Major | Degree
ID | Term | Subject | Catlg_No | Grade
Term | Year
Subject | Dept
Dept | Org
ID | Ethnicity
student_table
declared_major_table
student_ethnicity_table
term_enrollment_table
major_degree_table
term_table
subj_dept_table
dept_org_table
On a SQL Console:
A List of Tables Is Provided
•
Clicking around Reveals Columns and Properties
Commands
•
•
Results
What Makes a SQL Statement Work?
•
Syntax
•
Syntax
•
Syntax
SELECT name
FROM student_table
WHERE gender = ‘Male’
c
o
l
u
m
n
s
t
a
b
l
e
f
i
l
t
e
r
How to Select Records
SELECT name
FROM student_table
WHERE gender = ‘Male’
ID | Name | Gender
How to Select Records
SELECT name
FROM student_table
WHERE gender = ‘Male’
N
a
m
e
Robert
Frank
Juan
Tongshan
Denzel
More Complicated Selections
SELECT *
FROM term_enrollment_table
WHERE
(subject = ‘Math’ or
subject = ‘Chem’) and
term = ’17F’ and
substring(catlg_no,2,1) <= ’1’
A
l
l
M
a
t
h
e
n
r
o
l
l
m
e
n
t
s
a
n
d
a
l
l
C
h
e
m
i
s
t
r
y
e
n
r
o
l
l
m
e
n
t
s
,
i
n
F
a
l
l
2
0
1
7
,
a
t
t
h
e
u
n
d
e
r
g
r
a
d
u
a
t
e
c
o
u
r
s
e
l
e
v
e
l
…
W
I
L
D
C
A
R
D
(
g
i
v
e
m
e
a
l
l
o
f
t
h
e
m
)
More Complicated Selections
SELECT *
FROM term_enrollment_table
WHERE
(subject = ‘Math’ or
subject = ‘Chem’) and
term = ’17F’ and
substring(catlg_no,2,1) <= ’1’
I
D
|
T
e
r
m
|
S
u
b
j
e
c
t
|
C
a
t
l
g
_
N
o
|
G
r
a
d
e
1
17F
Math
0010
A
2
17F
Chem
0133
B
2
17F
Chem
0141
B+
3
17F
Chem
0055A
C
3
17F
Math
0111
A
. . .
substring(catlg_no,2,1) <= ’1’
‘0032A’
‘0205’
Returns: ‘0’
Returns:
‘2’
S
e
l
e
c
t
e
d
?
Y
E
S
S
e
l
e
c
t
e
d
?
N
O
Little Aggregated Reports
A
l
i
s
t
o
f
t
h
e
s
u
b
j
e
c
t
s
o
f
f
e
r
e
d
i
n
S
p
r
i
n
g
2
0
1
8
w
i
t
h
c
o
u
n
t
o
f
e
n
r
o
l
l
m
e
n
t
s
,
i
n
d
e
s
c
e
n
d
i
n
g
o
r
d
e
r
…
SELECT subject,
COUNT(subject) enrollments
FROM term_enrollment_table
WHERE term = ’18S’
GROUP BY subject
ORDER BY enrollments desc
Little Aggregated Reports
S
u
b
j
e
c
t
|
E
n
r
o
l
l
m
e
n
t
s
Chem
4401
Math
2335
English
2011
Psych
1816
. . .
SELECT subject,
COUNT(subject) enrollments
FROM term_enrollment_table
WHERE term = ’18S’
GROUP BY subject
ORDER BY enrollments desc
Coding New Columns
SELECT
CASE WHEN subject in (‘Math’,’Chem’)
THEN ‘STEM’
ELSE ‘Not STEM’
END STEM_flag,
grade
FROM term_enrollment_table
WHERE term = ‘18F’
G
r
a
d
e
s
f
o
r
F
a
l
l
2
0
1
8
m
a
r
k
e
d
a
s
S
T
E
M
o
r
n
o
t
S
T
E
M
…
Coding New Columns
S
T
E
M
_
F
l
a
g
|
G
r
a
d
e
S
T
E
M
B
STEM
A-
STEM
C
Not STEM
A
Not STEM
B+
STEM
D
. . .
SELECT
CASE WHEN subject in (‘Math’,’Chem’)
THEN ‘STEM’
ELSE ‘Not STEM’
END STEM_flag,
grade
FROM term_enrollment_table
WHERE term = ‘18F’
CASE
WHEN subject in (‘Math’,’Chem’) THEN ‘STEM’
WHEN subject in (‘English’,’Spanish’) THEN ‘HUMANITIES’
ELSE ‘Other’ END discipline_group
S
u
b
j
e
c
t
D
i
s
c
i
p
l
i
n
e
_
G
r
o
u
p
Chem
STEM
Spanish
HUMANITIES
Math
STEM
Psych
OTHER
English
HUMANITIES
T
h
e
c
o
d
e
w
i
l
l
t
e
s
t
u
n
t
i
l
i
t
f
i
n
d
s
a
m
a
t
c
h
,
t
h
e
n
r
e
t
u
r
n
t
h
e
‘
T
H
E
N
’
v
a
l
u
e
a
n
d
g
o
t
o
t
h
e
n
e
x
t
r
o
w
.
T
h
e
n
e
w
c
o
l
u
m
n
n
a
m
e
a
p
p
e
a
r
s
a
f
t
e
r
t
h
e
‘
E
N
D
’
.
Fancier Output for Reports
What if I want something that looks like this:
S
u
b
j
e
c
t
|
E
n
r
o
l
l
m
e
n
t
1
6
F
|
E
n
r
o
l
l
m
e
n
t
1
7
F
|
E
n
r
o
l
l
m
e
n
t
1
8
F
Chem
3801
4814
5023
English
3510
3200
3001
Math
2706
2800
3056
Psych
1507
1750
2022
Spanish
1042
1201
1502
Fancier Output for Reports
Or something like this:
T
e
r
m
|
S
u
b
j
e
c
t
|
A
’
s
A
w
a
r
d
e
d
|
B
’
s
A
w
a
r
d
e
d
|
C
’
s
A
w
a
r
d
e
d
17F
Chem
1101
1121
1098
17F
English
2108
1500
220
17F
Math
520
2203
476
17F
Psych
1202
516
22
17F
Spanish
726
233
103
Fancier Output for Reports
I’d have to create a new column
…and aggregate at the same time.
A
C
A
S
E
s
t
a
t
e
m
e
n
t
,
p
l
u
s
a
G
R
O
U
P
B
Y
…
?
?
?
Term Trend of Total Course Enrollment per Subject
SELECT subject,
SUM(CASE WHEN term = ‘16F’ then 1 else 0 end) enrollment_16F,
SUM(CASE WHEN term = ‘17F’ then 1 else 0 end) enrollment_17F,
SUM(CASE WHEN term = ‘18F’ then 1 else 0 end) enrollment_18F
FROM term_enrollment_table
GROUP BY subject
ORDER BY subject
S
u
b
j
e
c
t
|
E
n
r
o
l
l
m
e
n
t
_
1
6
F
|
E
n
r
o
l
l
m
e
n
t
_
1
7
F
|
E
n
r
o
l
l
m
e
n
t
_
1
8
F
Chem
3801
4814
5023
English
3510
3200
3001
Math
2706
2800
3056
Psych
1507
1750
2022
Spanish
1042
1201
1502
Grade Counts per Subject in a Term
SELECT term, subject,
SUM(CASE WHEN grade in (‘A+’,‘A’,’A-’) then 1 else 0 end) As,
SUM(CASE WHEN grade in (‘B+’,‘B’,’B-’) then 1 else 0 end) Bs,
SUM(CASE WHEN grade in (‘C+’,’C’,’C-’) then 1 else 0 end) Cs
FROM term_enrollment_table
WHERE term = ’17F’
GROUP BY term, subject
ORDER BY term, subject
T
e
r
m
|
S
u
b
j
e
c
t
|
A
’
s
|
B
’
s
|
C
’
s
17F
Chem
1101
1121
1098
17F
English
2108
1500
220
17F
Math
520
2203
476
17F
Psych
1202
516
22
17F
Spanish
726
233
103
Learning Outcomes
•
Know what SQL is
•
Read a basic SQL SELECT statement
•
Find online opportunities to learn the language
•
Recognize examples of SQL syntax and identify what they do
•
Consider how the results from a SELECT statement can be formatted to
meet your needs
Things You Need to Do with Data
?
Happy SQLing!
Kelly Wahl
UCLA
kwahl@college.ucla.edu
Explore the world of Structured Query Language (SQL) with this comprehensive guide. Discover what SQL is, its history, usage, and how to learn it on your own through free online tutorials. Understand the properties of relational databases and get insights into designing a relational database. With SQL being a fundamental tool for managing data and querying databases, mastering it opens up a wide range of opportunities in the data-driven world.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
IR 101: Beginning SQL in IR Kelly Wahl Director of Statistical Analysis and Assessment Coordinator UCLA
Learning Outcomes Know what SQL is Read a basic SQL SELECT statement Find online opportunities to learn the language Recognize examples of SQL syntax and identify what they do Consider how the results from a SELECT statement can be formatted to meet your needs
What is SQL? Structured Query Language (SQL) Often pronounced sequel because the original acronym was SEQUEL (Structured English QUEry Language) Developed by IBM in the early 70 s Changed its name because another company had already trademarked SEQUEL
How And When Do We Use SQL? Its name gives away its purpose: It is a programming language used for managing data and querying databases. SQL has been structured to be clear, to sequence commands, to allow options to be pursued, and to block (i.e., to treat groups of statements as one). Relational Database Management Systems (RDBMS) can use it. Oracle Database, SQL Server, and MySQL are examples of database software that use this language for data access and management.
How Can I Learn SQL on My Own? Free Tutorials Online: http://w3schools.com/sql http://sqlzoo.net http://sqlbolt.com http://sqlteaching.com http://quackit.com/sql/tutorial/ Give Me Your Info Online (But Free): https://community.modeanalytics.com/sql/ https://www.codecademy.com/learn/learn-sql https://academy.vertabelo.com
Properties of Relational Databases Relational databases are for storing and retrieving related information. They can be explained with diagrams showing the way the data are modeled. Records are stored as rows in tables that have columns for each variable being stored per case.
A Relational Database: major_degree_table declared_major_table term_table Major | Degree student_table ID | Term | Major Term | Year ID | Name | Gender term_enrollment_table subj_dept_table Subject | Dept ID | Term | Subject | Catlg_No | Grade dept_org_table ID | Ethnicity Dept | Org student_ethnicity_table
On a SQL Console: A List of Tables Is Provided Clicking around Reveals Columns and Properties
Commands Results
What Makes a SQL Statement Work? Syntax Syntax Syntax columns SELECT name FROM student_table WHERE gender = Male table filter
How to Select Records SELECT name FROM student_table WHERE gender = Male ID | Name | Gender
How to Select Records Name Robert Frank Juan Tongshan Denzel SELECT name FROM student_table WHERE gender = Male
More Complicated Selections WILDCARD (give me all of them) SELECT * FROM term_enrollment_table WHERE (subject = Math or subject = Chem ) and term = 17F and substring(catlg_no,2,1) <= 1 All Math enrollments and all Chemistry enrollments, in Fall 2017, at the undergraduate course level
More Complicated Selections ID | Term | Subject | Catlg_No | Grade 1 17F Math 2 17F Chem 2 17F Chem 3 17F Chem 3 17F Math . . . SELECT * FROM term_enrollment_table WHERE (subject = Math or subject = Chem ) and term = 17F and substring(catlg_no,2,1) <= 1 0010 0133 0141 0055A 0111 A B B+ C A
substring(catlg_no,2,1) <= 1 0032A 0205 Returns: 2 Returns: 0 Selected? YES Selected? NO
Little Aggregated Reports SELECT subject, COUNT(subject) enrollments FROM term_enrollment_table WHERE term = 18S GROUP BY subject ORDER BY enrollments desc A list of the subjects offered in Spring 2018 with count of enrollments, in descending order
Little Aggregated Reports SELECT subject, COUNT(subject) enrollments FROM term_enrollment_table WHERE term = 18S GROUP BY subject ORDER BY enrollments desc Subject | Enrollments Chem Math English Psych . . . 4401 2335 2011 1816
Coding New Columns SELECT CASE WHEN subject in ( Math , Chem ) THEN STEM ELSE Not STEM END STEM_flag, grade FROM term_enrollment_table WHERE term = 18F Grades for Fall 2018 marked as STEM or not STEM
Coding New Columns SELECT CASE WHEN subject in ( Math , Chem ) THEN STEM ELSE Not STEM END STEM_flag, grade FROM term_enrollment_table WHERE term = 18F STEM_Flag | Grade STEM STEM STEM Not STEM Not STEM STEM . . . B A- C A B+ D
CASE WHEN subject in ( Math , Chem ) THEN STEM WHEN subject in ( English , Spanish ) THEN HUMANITIES ELSE Other END discipline_group The code will test until it finds a match, then return the THEN value and go to the next row. Subject Chem Spanish Math Psych English Discipline_Group STEM HUMANITIES STEM OTHER HUMANITIES The new column name appears after the END .
Fancier Output for Reports What if I want something that looks like this: Subject | Enrollment 16F | Enrollment 17F | Enrollment 18F Chem 3801 4814 English 3510 3200 Math 2706 2800 Psych 1507 1750 Spanish 1042 1201 5023 3001 3056 2022 1502
Fancier Output for Reports Or something like this: Term | Subject | A s Awarded | B s Awarded | C s Awarded 17F Chem 1101 17F English 2108 17F Math 520 17F Psych 1202 17F Spanish 726 1121 1500 2203 516 233 1098 220 476 22 103
Fancier Output for Reports I d have to create a new column and aggregate at the same time. A CASE statement, plus a GROUP BY ???
Term Trend of Total Course Enrollment per Subject SELECT subject, SUM(CASE WHEN term = 16F then 1 else 0 end) enrollment_16F, SUM(CASE WHEN term = 17F then 1 else 0 end) enrollment_17F, SUM(CASE WHEN term = 18F then 1 else 0 end) enrollment_18F FROM term_enrollment_table GROUP BY subject ORDER BY subject English Math Psych Spanish Subject | Enrollment_16F | Enrollment_17F | Enrollment_18F Chem 3801 3510 2706 1507 1042 4814 3200 2800 1750 1201 5023 3001 3056 2022 1502
Grade Counts per Subject in a Term SELECT term, subject, SUM(CASE WHEN grade in ( A+ , A , A- ) then 1 else 0 end) As, SUM(CASE WHEN grade in ( B+ , B , B- ) then 1 else 0 end) Bs, SUM(CASE WHEN grade in ( C+ , C , C- ) then 1 else 0 end) Cs FROM term_enrollment_table WHERE term = 17F GROUP BY term, subject ORDER BY term, subject Term | Subject | A s | B s | C s 17F Chem 1101 17F English 2108 17F Math 520 17F Psych 1202 17F Spanish 726 1121 1500 2203 516 233 1098 220 476 22 103
Learning Outcomes Know what SQL is Read a basic SQL SELECT statement Find online opportunities to learn the language Recognize examples of SQL syntax and identify what they do Consider how the results from a SELECT statement can be formatted to meet your needs
Happy SQLing! Kelly Wahl UCLA kwahl@college.ucla.edu
