Introductjon to SQL Part 1 Single-Table Queries By Michael Hahsler - PowerPoint PPT Presentation

Introductjon to SQL Part 1 – Single-Table Queries By Michael Hahsler based on slides for CS145 Introductjon to Databases (Stanford)

Overview 1. SQL introductjon & schema defjnitjons 2. Basic single-table queries 2

1. SQL INTRODUCTION & DEFINITIONS 3

What you will learn about in this sectjon 1. What is SQL? 2. Basic schema defjnitjons 3. Keys & constraints intro 4. Actjvitjes: CREATE TABLE statements 4

Basic SQL 5

SQL Introductjon SQL stands for SQL stands for S tructured Q uery L anguage S tructured Q uery L anguage • SQL is a standard language for querying and manipulatjng data. • SQL is a high-level, declaratjve programming language. • SQL executjon is highly optjmized and parallelized. • Many standards out there: – Standardized in 1986/87 – ANSI SQL/ SQL-86, SQL92 (a.k.a. SQL2), SQL99 (a.k.a. SQL3), SQL:2011 – Vendors support various subsets (e.g., SQLite implements most of the SQL-92 standard)

SQL is a… • Data Defjnitjon Language (DDL) – Defjne relatjonal schemata – Create/alter/delete tables and their atuributes • Data Manipulatjon Language (DML) – Insert/delete/modify tuples in tables – Query one or more tables 7

Tables in SQL Product A relatjon or PName Price Manufacturer table is a Gizmo $19.99 GizmoWorks multjset of tuples having the Powergizmo $29.99 GizmoWorks atuributes SingleTouch $149.99 Canon specifjed by the schema MultiTouch $203.99 Hitachi This is where the name “relatjonal” databases comes from. This is where the name “relatjonal” databases comes from. 8

Tables in SQL Product An aturibute (or column ) is a PName Price Manufacturer typed data entry Gizmo $19.99 GizmoWorks present in each tuple in the Powergizmo $29.99 GizmoWorks relatjon SingleTouch $149.99 Canon Atuributes must have an Atuributes must have an MultiTouch $203.99 Hitachi atomic type in standard atomic type in standard SQL, i.e. not a list, set, SQL, i.e. not a list, set, etc. etc. 10

Tables in SQL Product A tuple or row is a single entry in PName Price Manufacturer the table having Gizmo $19.99 GizmoWorks the atuributes specifjed by the Powergizmo $29.99 GizmoWorks schema SingleTouch $149.99 Canon Sometjmes also referred to MultiTouch $203.99 Sometjmes also referred to Hitachi as a record as a record 11

Data Types in SQL SQLite uses: SQLite uses: integer, text integer, text • Atomic types: and real and real – Characters: CHAR(20), VARCHAR(50) – Numbers: INT, BIGINT, SMALLINT, FLOAT – Others: MONEY, DATETIME, … • Every aturibute must have an atomic type Why? Why? 13

Table Schemas • The schema of a table is the table name, its atuributes, and their types: Product(Pname: text , Price: real , Product(Pname: text , Price: real , Category: text , Manufacturer: text ) Category: text , Manufacturer: text ) • A key is an aturibute (combinatjon) that identjfjes a tuple uniquely. Product(Pname: text , Price: real , Product(Pname: text , Price: real , Category: text , Manufacturer: text ) Category: text , Manufacturer: text ) 14

Key constraints A key is a minimal subset of atuributes that acts as a A key is a minimal subset of atuributes that acts as a unique identjfjer for tuples in a relatjon unique identjfjer for tuples in a relatjon Students(sid: text, Students(sid: text, A key is an implicit constraint name: text, name: text, gpa: real) gpa: real) on which tuples can be in the relatjon 1. Which would you select as a key? 2. Is a key always guaranteed to exist? i.e., if two tuples agree on the 3. Can we have more than one key? values of the key, then they (key candidates and primary key) must be the same tuple!

NULL and NOT NULL • To say “don’t know the value” we use NULL Students(sid:text, name:text, gpa: real) Students(sid:text, name:text, gpa: real) sid name gpa 123 Bob 3.9 Say, Jim just enrolled in his Say, Jim just enrolled in his 143 Jim NULL fjrst class. fjrst class. In SQL, we may constrain a column to be NOT NULL, e.g., In SQL, we may constrain a column to be NOT NULL, e.g., “name” in this table “name” in this table

Actjvitjes • SQLite data types: htup://www.tutorialspoint.com/sqlite • DB Browser PName Price Category Manufacturer – Create a database – Create a Gizmo $19.99 Gadgets GizmoWorks “Product” table Powergizmo $29.99 Gadgets GizmoWorks – Add the shown SingleTouch $149.99 Photography Canon data MultiTouch $203.99 Household Hitachi 19

2. SINGLE-TABLE QUERIES 20

What you will learn about in this sectjon 1. The SFW query 2. Other useful operators: LIKE, DISTINCT, ORDER BY 3. Actjvitjes: Single-table queries 21

SQL Query • Basic form (there are many many more bells and whistles) SELECT <attributes> SELECT <attributes> FROM <one or more relations> FROM <one or more relations> WHERE <conditions> WHERE <conditions> Call this a SFW Call this a SFW query. query. 22

Simple SQL Query: Selectjon Selectjon is the Selectjon is the PName Price Category Manufacturer operatjon of operatjon of Gizmo $19.99 Gadgets GizmoWorks fjltering a fjltering a Powergizmo $29.99 Gadgets GizmoWorks relatjon’s tuples on relatjon’s tuples on some conditjon some conditjon SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi SELECT * SELECT * FROM Product FROM Product WHERE Category = ‘Gadgets’ WHERE Category = ‘Gadgets’ PName Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks 23 Powergizmo $29.99 Gadgets GizmoWorks

Simple SQL Query: Projectjon PName Price Category Manufacturer Projectjon is the Projectjon is the operatjon of operatjon of Gizmo $19.99 Gadgets GizmoWorks producing an output producing an output Powergizmo $29.99 Gadgets GizmoWorks table with tuples that table with tuples that have a subset of their have a subset of their SingleTouch $149.99 Photography Canon prior atuributes prior atuributes MultiTouch $203.99 Household Hitachi SELECT Pname, Price, Manufacturer SELECT Pname, Price, Manufacturer FROM Product FROM Product WHERE Category = ‘Gadgets’ WHERE Category = ‘Gadgets’ PName Price Manufacturer Gizmo $19.99 GizmoWorks Powergizmo $29.99 GizmoWorks 24

Notatjon Product(PName, Price, Category, Product(PName, Price, Category, Input schema Input schema Manfacturer) Manfacturer) SELECT Pname, Price, Manufacturer SELECT Pname, Price, Manufacturer FROM Product FROM Product WHERE Category = ‘Gadgets’ WHERE Category = ‘Gadgets’ Answer(PName, Price, Answer(PName, Price, Output schema Output schema Manfacturer) Manfacturer) 25

A Few Details • SQL commands are case insensitjve: – Same: SELECT, Select, select – Same: Product, product • Values are not: – Difgerent: ‘Seatule’, ‘seatule’ • Use single quotes for text constants: – ‘abc’ - yes – “abc” - no 26

DISTINCT: Eliminatjng Duplicates Category SELECT DISTINCT Category SELECT DISTINCT Category Gadgets FROM Product FROM Product Photography Household Versus Category Gadgets SELECT Category SELECT Category Gadgets FROM Product FROM Product Photography Household 27

COUNT COUNT is an aggregatjon functjon that returns the number of elements. Example: Find the number of products with a price of $20 or more. SELECT COUNT(*) FROM product WHERE price >= 20 SELECT COUNT(*) FROM product WHERE price >= 20 Syntax: COUNT([ALL | DISTINCT] expression) 28

ORDER BY: Sortjng the Results SELECT PName, Price, Manufacturer SELECT PName, Price, Manufacturer FROM Product FROM Product WHERE Category=‘gizmo’ AND Price > 50 WHERE Category=‘gizmo’ AND Price > 50 ORDER BY Price, PName ORDER BY Price, PName Ties are broken Ties are broken Ordering is Ordering is Text is ordered Text is ordered by the second by the second ascending, unless ascending, unless alphabetjcally. alphabetjcally. aturibute on the aturibute on the you specify the you specify the ORDER BY list, ORDER BY list, DESC keyword. DESC keyword. etc. etc. 29

LIMIT Clause Used to limit the data amount returned by the SELECT statement. Example: Find the 5 most expensive products SELECT * FROM product SELECT * FROM product ORDER BY price DESC ORDER BY price DESC LIMIT 5 LIMIT 5 Syntax: LIMIT [no of rows] OFFSET [row num] Note: LIMIT is not standard SQL (e.g., MS SQL Server uses SELECT TOP) 30

Operators Some of the operators supported by SQL are: =, == equal !=, <> not equal <, <= less than (or equal) >, >= greater than (or equal) +, -, /, * arithmetjc operators AND, OR, NOT logic operators IS NULL, IS NOT NULL checks for NULL values Example: Find products and their price + 8% sales tax for gadgets that cost at least $100 SELECT pname, price * 1.08 AS Price_with_tax SELECT pname, price * 1.08 AS Price_with_tax FROM product, FROM product, WHERE category = ‘Gadgets’ AND price >= 100 WHERE category = ‘Gadgets’ AND price >= 100 31