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 lab 2

introduction

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

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

installation What is MySQL? https://dev.mysql.com/downloads/ server-client model other systems, e.g. SQLite sample data http://forta.com/books/0672336073/ Download MySQL scripts (a zip file) 5

SQL

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

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

sorting SELECT prod name FROM Products ORDER BY prod name ❼ What is a clause? ❼ position of ORDER BY ❼ sorting by non-selected columns 8

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

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

more filtering ❼ IN ❼ combined WHERE clause ❼ AND ❼ OR ❼ NOT 11

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

Summary ❼ relational databases ❼ SELECT ... FROM ... ❼ WHERE , ORDER BY , LIMIT 2020.9.24 13

Questions? 13