SLIDE 1
CISC 5500 Data Analytics Tools and Scripting
SQL: retrieving and filtering
Computer and Information Science
Fordham University
Table of contents
- 1. introduction
- 2. SQL
sorting filtering
1
CISC 5500 Data Analytics Tools and Scripting SQL: retrieving and - - PowerPoint PPT Presentation
CISC 5500 Data Analytics Tools and Scripting SQL: retrieving and - - PowerPoint PPT Presentation
CISC 5500 Data Analytics Tools and Scripting SQL: retrieving and filtering Computer and Information Science Fordham University Table of contents 1. introduction 2. SQL sorting filtering 1 review find, grep, xargs, scp homework and
SLIDE 2
SLIDE 3
review
❼ find, grep, xargs, scp ❼ homework and lab
2
SLIDE 4
introduction
SLIDE 5
background
❼ what is database
❼ DBMS vs the data themselves
❼ what is relational database
❼ possible ways of organizing data: network, hierarchical, relational (tabular)
❼ what is SQL
3
SLIDE 6
relational database
❼ tables ❼ columns and rows ❼ data types: text, numeric, date, ... ❼ keys and primary key (unique identifier) ❼ what is the schema (or ‘database’ as in MySQL)?
4
SLIDE 7
installation
What is MySQL? https://dev.mysql.com/downloads/ server-client model
- ther systems, e.g. SQLite
sample data http://forta.com/books/0672336073/ Download MySQL scripts (a zip file)
5
SLIDE 8
SQL
SLIDE 9
retrieving
SELECT prod name FROM Products; ❼ case sensitive or insensitive? ❼ newline and semicolon ❼ reserved words
6
SLIDE 10
more retrieving
❼ Retrieve multiple columns: use comma ❼ Retrieve all columns: use wildcard (asterisk) ❼ Retrieving distinct rows: DISTINCT ❼ Limiting results: LIMIT, OFFSET (not on all systems) Example SELECT prod name, prod description FROM Products LIMIT 5 comment - -
7
SLIDE 11
sorting
SELECT prod name FROM Products ORDER BY prod name ❼ What is a clause? ❼ position of ORDER BY ❼ sorting by non-selected columns
8
SLIDE 12
more sorting
❼ sorting by multiple columns ❼ sorting by column positions
❼ 1-base ❼ selected column positions, not the column positions in the table
❼ specifying sorting direction Example SELECT prod id, prod price FROM Products ORDER BY prod price DESC
9
SLIDE 13
filtering
SELECT prod name, prod price FROM Products WHERE prod price=3.49 ❼ WHERE clause ❼ operators: =, >, <, ..., BETWEEN, IS NULL
10
SLIDE 14
more filtering
❼ IN ❼ combined WHERE clause
❼ AND ❼ OR ❼ NOT
11
SLIDE 15
using wildcard filtering
❼ LIKE operator ❼ percentage sign (%) for wildcard
❼ AND ❼ OR ❼ NOT
Example SELECT prod id, prod name FROM Products WHERE prod name LIKE ✬Fish%✬;
12
SLIDE 16
Summary
❼ relational databases ❼ SELECT ... FROM ... ❼ WHERE, ORDER BY, LIMIT
2020.9.24 13
SLIDE 17
Questions?
13