Simple SQL Query: Matching String Patterns: - Keyword - Associated Symbols

Use the LIKE keyword in the Condition Statement Finds strings that match a given pattern Symbols: % - matches any sequence of characters _ - matches any single character Usage: SELECT \ FROM Product WHERE Pname LIKE '%gizmo%'

A key is a _minimal subset of attibutes_ It acts as a _unique identifier_ for tuples in a relation A key is an _Implicit Constraint_ on the tuples that are allowed to be in the relation No two tuples can have the same key If two tuples have the same key, they must be the same tuple

Relations are represented by tables. A relation is a multiset of tuples, having the attributes specified by the schema. The Columns of the table represent the attributes Each row represents one tuple

L3: SQL Basics Flashcards by Emma Frost

How to avoid

Tuple Variable Ambiguity (same name)

in Multi-table Operations?

Explicitly state the table name followed by the variable name:

table.var

Select Distinct fields in the table,

use:

SELECT DISTINCT t1.var1 t2.var3, etc

How well did you know this?

Not at all

Perfectly

What is Tuple Variable Ambiguity?

When two different tables (tuples) have the same variable name.

How well did you know this?

Not at all

Perfectly

SQL:

Joining Two Tables

Returns all unique combinations of their tuples which meet some specified join condition

Two Ways:

Declare both tables in the “FROM” section:

SELECT *

FROM Product, Company

WHERE Manufacturer = CName

Explicitly declare a JOIN between the tables on a condition:

SELECT *

FROM

Product JOIN Company ON Manufacturer=CName

Both ways return a new tuple with the combinations

How well did you know this?

Not at all

Perfectly

SQL Query:

Basic Form

(SFW Query)

“Select From Where” Query:

SELECT <attributes></attributes>

FROM <one></one>

WHERE <conditions></conditions>

How well did you know this?

Not at all

Perfectly

Table Schemas:

Schema

The Schema of a table includes:

Table Name
Attributes, one or more underlined as key
Attribute types

Format:

RelationName( attr1: type, attr2: type, …);

How well did you know this?

Not at all

Perfectly

Table Schemas:

Key

The Key

of a Table Schema

is an attribute whose values are unique for each entry.

Indicated by underlining the attribute name:

Table( myKey: int, attr2: string, …)

How well did you know this?

Not at all

Perfectly

SQL Operations:

Creating a Table

CREATE TABLE Name(

param1 CHAR(20),

param2 CHAR(10),

PRIMARY KEY(param1),

FOREIGN KEY(param2) REFERENCES Table2(key)

)

Each parameter may be given a maximum size
Can define one or more parameters as a primary key
Declare foreign keys that reference other tables

How well did you know this?

Not at all

Perfectly

Simple SQL Query:

Eliminating Duplicate Tuples

Use the DISTINCT command:

If there are more than one of the same value for a parameter, only the first tuple containing it is presented.

SELECT DISTINCT Category

FROM Product

How well did you know this?

Not at all

Perfectly

SQL Queries:

Selection

Overview

Selection is the process of

filtering a relation’s tuples

on some condition

Get all the tuples that meet the condition(s)

specified by the WHERE command:

SELECT *

FROM myTable

WHERE

How well did you know this?

Not at all

Perfectly

SQL Queries:

Projection

Projection is the operation of

producing an output table

with tuples that have a subset

of their prior attibutes

The returned set of tuples only has

the attributes specified by SELECT.

Can use SELECT * to get all attributes

How well did you know this?

Not at all

Perfectly

Order of Operations (Semantics)

in a Multitable Operation

Put tables together:
- FROM table1, table2…
Apply Selections/Conditions
- Filters out unwanted tuples
- WHERE ….
Apply Projection
- Only get required attributes
- SELECT …

How well did you know this?

Not at all

Perfectly

Simple SQL Query:

Sorting the Results

Use the ORDER BY command

at the end of the query

Orders the tuples in ascending order
- Can specify descending order with DESC keyword
Ties are broken by the second attribute on the ORDERBY list

Example:

SELECT Pname, Price, Manufacturer

FROM Product

WHERE Category=’gizmo’ AND Price>50

ORDER BY Price, PName

How well did you know this?

Not at all

Perfectly

Simple SQL Query:

Matching String Patterns:

Keyword
Associated Symbols

Use the LIKE keyword

in the Condition Statement

Finds strings that match a given pattern

Symbols:

% - matches any sequence of characters
_ - matches any single character

Usage:

SELECT *

FROM Product

WHERE Pname LIKE ‘%gizmo%’

How well did you know this?

Not at all

Perfectly

Multi-Table SQL Queries:

Foreign Keys

A Foreign Key is a parameter of a tuple in a relation table.

It corresponds to the Primary Keys of another table.

Essentially, it is a reference to a tuple in another table.

How well did you know this?

Not at all

Perfectly

True or False:

SQL commands and values are

NOT case sensitive

FALSE!

SQL Commands are NOT case sensitive:
- SELECT = Select = select

However,

SQL Values ARE case sensitive:
- ‘Seattle’ ≠ ‘seattle’

How well did you know this?

Not at all

Perfectly

Key Constraints

A key is a minimal subset of attibutes
- It acts as a unique identifier for tuples in a relation
A key is an Implicit Constraint on the tuples that are allowed to be in the relation
- No two tuples can have the same key
- If two tuples have the same key, they must be the same tuple

How well did you know this?

Not at all

Perfectly

Tables in SQL

Study These Flashcards

Relations are represented by tables.

A relation is a multiset of tuples,

having the attributes specified by the schema.

The Columns of the table represent the attributes
Each row represents one tuple

Data Types

in SQL

Study These Flashcards

All attributes must have an atomic type, the tables are “flat”
Character Types:
- CHAR - set number of characters
  - CHAR(20) or CHAR(30)
- VARCHAR - variable number of characters, up to some maximum :
  - VARCHAR(50)
Numerical Values:
- INT
- SMALLINT
- BIGINT
- FLOAT
Other Common types:
- MONEY
- DATETIME

What happens if

the Entry that corresponds to a Foreign Key

is deleted?

Study These Flashcards

There are multiple options,

implementation is up to the Database Administrator

Disallow the delete operation
- This is pretty restrictive
Remove all tuples that reference that entry
- Probably a lot of unnecessary deletions
Replace the Foreign Key with NULL
- Most likely

Foreign Keys

as Constraints

Study These Flashcards

Foreign Keys can act as constraints:

Cannot insert a tuple who’s foreign key doesn’t exist as a primary key in the referenced table

Example:

A Student with sid=”123” must first exist in the “Students” table

before attempting to insert a tuple into the “Enrolled” table using sid=”123”

Tables in SQL:

Cardinality

Arity

Study These Flashcards

Cardinality
- The number of tuples/rows of the relation
- How many entries are there?
Arity
- The number of attributes/columns of the relation
- How big are the entries?

What is

SQL?

Study These Flashcards

Structured Query Language

A standard language for querying and manipulating data
A very High Level programming language
Probably the world’s most successful Parallel Programming Language
Is both:
- Data Definition Language( DDL )
- Data Manipulation Language( DML )

SQL

Data Definition Language (DDL)

Features

Study These Flashcards

Defines relational schema
Create/alter/delete tables and attributes

SQL

Data Manipulation Language (DML)

Features

Study These Flashcards

Insert/Delete/Modify tuples in tables
Query one or more tables

General Points about Constraints

* In SQL, arbitrary constraints can be specified * ex: "There cannot be 25 people in DB class" * In practice, the fewer constraints the better * More constraints lead to worse performance * Lack of constraints may lead to * more ugly, less convenient techniques * but improves performance

Multiset: Definition

Multiset * An unordered list * A set with multiple duplicate instances allowed

NULL and NOT NULL

NULL Used when a value is unknown or does not exist Placed just like a value for some attribute in a tuple NOT NULL In SQL, an attribute/column can be constrained to be NOT NULL. This prohibits NULL values from being added to the table in that column.

Set Operations: Top 3 Explicit Set Operations in SQL and their Multiset Variations

* INTERSECT * Entries existing in both tables * Removes duplicate entries * Multiset: INTERSECT ALL * This does NOT remove duplicates * UNION * All entries in both tables * Removes duplicates * Multiset: UNION ALL * Does NOT remove duplicates * EXCEPT * Entries that are in one table, not the other * Multiset: EXCEPT ALL * Does NOT remove duplicates

SQL Aggregation Operations

SUM COUNT MIN MAX AVG * Except for COUNT, all aggregations apply to a **_single_** attribute * Aggregation operations are applied in PROJECTION (meaning after SELECT): SELECT COUNT(category)

SQL Command Structure for a Nested Query

* Use the IN keyword, followed by a subquery in parenthesis * May name the table returned by a subquery which can then be referenced by the base query SELECT \* FROM s WHERE s.a **IN** ( SELECT t.a FROM t WHERE ... )

SQL Keywords: Common Conditional Operation Keywords

ALL ANY EXISTS

SQL Keywords: Conditional Operation Keywords: ALL

ALL ## Footnote Use: S \> ALL R Used to check against ALL tuples in a table The example above checks that S is larger than all tuples in R

SQL Keywords: Conditional Operation Keywords: ANY

ANY ## Footnote Used to check if any one tuple meets the condition. Use: S \< ANY R is true if any tuple in R is larger than S

SQL Keywords: Conditional Operation Keywords: EXISTS

EXISTS ## Footnote Used to check that something exists Use: EXISTS R checks that tuples exist in R

SQL Command: Intersection of Table R and S

Perform 2 Queries that get all entries in both tables, with an INTERSECT command in between the queries SELECT \* FROM R **INTERSECT** SELECT \* FROM S Usually, it should be more specific, such as checking only a single attribute

Database Set Notation: λ(X)

λ(X) "Count of the tuple in multiset X" The number of _instances_ of a _specific tuple_.

Multiset Operations: Count Relationship for Intersection of Tables

For the intersection of tables X and Y: Count: λ(Z) = min( λ(X) , λ(Y) )

Multiset Operations: Count Relationship for Union of Tables

For the intersection of tables X and Y: Count: λ(Z) = λ(X) + λ(Y)

SQL Command: Union of Table R and S

SELECT \* FROM R **UNION** SELECT \* FROM S

Difference between the HAVING and WHERE conditional clauses

HAVING clauses contain conditions on _aggregates_ ex: HAVING SUM(quantity) \> 100 WHERE clauses condition on _individual tuples_ ex: WHERE quantity \> 100

SQL is Compositional. What does that mean? Why is it important?

Meaning: Everything(inputs and outputs) is represented as _multisets_ Importance: This allows the _output_ of one query to be used as the _input_ of another. Meaning we can perform **_Nested Queries_**

SQL Command: EXCEPT

Remove attribute "A" of table R that are also in table S: ## Footnote SELECT R.A FROM R EXCEPT SELECT R.A FROM R, S WHERE R.A = S.A

L3: SQL Basics Flashcards

(42 cards)