CISC 5500 Data Analytics Tools and Scripting SQL: retrieving and - - PowerPoint PPT Presentation

cisc 5500 data analytics tools and scripting
SMART_READER_LITE
LIVE PREVIEW

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-1
SLIDE 1

CISC 5500 Data Analytics Tools and Scripting

SQL: retrieving and filtering

Computer and Information Science

Fordham University

slide-2
SLIDE 2

Table of contents

  • 1. introduction
  • 2. SQL

sorting filtering

1

slide-3
SLIDE 3

review

❼ find, grep, xargs, scp ❼ homework and lab

2

slide-4
SLIDE 4

introduction

slide-5
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
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
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
SLIDE 8

SQL

slide-9
SLIDE 9

retrieving

SELECT prod name FROM Products; ❼ case sensitive or insensitive? ❼ newline and semicolon ❼ reserved words

6

slide-10
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
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
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
SLIDE 13

filtering

SELECT prod name, prod price FROM Products WHERE prod price=3.49 ❼ WHERE clause ❼ operators: =, >, <, ..., BETWEEN, IS NULL

10

slide-14
SLIDE 14

more filtering

❼ IN ❼ combined WHERE clause

❼ AND ❼ OR ❼ NOT

11

slide-15
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
SLIDE 16

Summary

❼ relational databases ❼ SELECT ... FROM ... ❼ WHERE, ORDER BY, LIMIT

2020.9.24 13

slide-17
SLIDE 17

Questions?

13