Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Database Systems > L3: SQL Basics > Flashcards

L3: SQL Basics Flashcards

1
Q

How to avoid

Tuple Variable Ambiguity (same name)

in Multi-table Operations?

A

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?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is Tuple Variable Ambiguity?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

SQL:

Joining Two Tables

A
  • 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?
1
Not at all
2
3
4
5
Perfectly
4
Q

SQL Query:

Basic Form

(SFW Query)

A

“Select From Where” Query:

SELECT <attributes></attributes>

FROM <one></one>

WHERE <conditions></conditions>

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Table Schemas:

Schema

A

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?
1
Not at all
2
3
4
5
Perfectly
6
Q

Table Schemas:

Key

A

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?
1
Not at all
2
3
4
5
Perfectly
7
Q

SQL Operations:

Creating a Table

A

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?
1
Not at all
2
3
4
5
Perfectly
8
Q

Simple SQL Query:

Eliminating Duplicate Tuples

A

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?
1
Not at all
2
3
4
5
Perfectly
9
Q

SQL Queries:

Selection

Overview

A

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?
1
Not at all
2
3
4
5
Perfectly
10
Q

SQL Queries:

Projection

A

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?
1
Not at all
2
3
4
5
Perfectly
11
Q

Order of Operations (Semantics)

in a Multitable Operation

A
  1. Put tables together:
    • FROM table1, table2…
  2. Apply Selections/Conditions
    • Filters out unwanted tuples
    • WHERE ….
  3. Apply Projection
    • ​Only get required attributes
    • SELECT …
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Simple SQL Query:

Sorting the Results

A

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?
1
Not at all
2
3
4
5
Perfectly
13
Q

Simple SQL Query:

Matching String Patterns:

  • Keyword
  • Associated Symbols
A

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?
1
Not at all
2
3
4
5
Perfectly
14
Q

Multi-Table SQL Queries:

Foreign Keys

A

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?
1
Not at all
2
3
4
5
Perfectly
15
Q

True or False:

SQL commands and values are

NOT case sensitive

A

FALSE!

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

However,

  • SQL Values ARE case sensitive:
    • ‘Seattle’ ≠ ‘seattle’
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Key Constraints

A
  • 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?
1
Not at all
2
3
4
5
Perfectly
17
Q

Tables in SQL

A

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
18
Q

Data Types

in SQL

A
  • 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
19
Q

What happens if

the Entry that corresponds to a Foreign Key

is deleted?

A

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
20
Q

Foreign Keys

as Constraints

A

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”

21
Q

Tables in SQL:

Cardinality

Arity

A
  • 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?
22
Q

What is

SQL?

A

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 )
23
Q

SQL

Data Definition Language (DDL)

Features

A
  • Defines relational schema
  • Create/alter/delete tables and attributes
24
Q

SQL

Data Manipulation Language (DML)

Features

A
  • Insert/Delete/Modify tuples in tables
  • Query one or more tables
25
Q

General Points about

Constraints

A
  • 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
26
Q

Multiset:

Definition

A

Multiset

  • An unordered list
  • A set with multiple duplicate instances allowed
27
Q

NULL

and

NOT NULL

A

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.

28
Q

Set Operations:

Top 3 Explicit Set Operations in SQL

and their Multiset Variations

A
  • 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
29
Q

SQL Aggregation Operations

A

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)

30
Q

SQL Command Structure

for a Nested Query

A
  • 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 ( <subquery></subquery>

SELECT t.a

FROM t

WHERE … )

31
Q

SQL Keywords:

Common Conditional Operation Keywords

A

ALL

ANY

EXISTS

32
Q

SQL Keywords:

Conditional Operation Keywords:

ALL

A

ALL

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

33
Q

SQL Keywords:

Conditional Operation Keywords:

ANY

A

ANY

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

34
Q

SQL Keywords:

Conditional Operation Keywords:

EXISTS

A

EXISTS

Used to check that something exists

Use:

EXISTS R

checks that tuples exist in R

35
Q

SQL Command:

Intersection of Table R and S

A

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

36
Q

Database Set Notation:

λ(X)

A

λ(X)

“Count of the tuple in multiset X”

The number of instances of a specific tuple.

37
Q

Multiset Operations:

Count Relationship

for

Intersection of Tables

A

For the intersection of tables X and Y:

Count: λ(Z) = min( λ(X) , λ(Y) )

38
Q

Multiset Operations:

Count Relationship

for

Union of Tables

A

For the intersection of tables X and Y:

Count: λ(Z) = λ(X) + λ(Y)

39
Q

SQL Command:

Union of Table R and S

A

SELECT *

FROM R

UNION

SELECT *

FROM S

40
Q

Difference between the

HAVING

and

WHERE

conditional clauses

A

HAVING

clauses contain conditions on aggregates

ex: HAVING SUM(quantity) > 100

WHERE

clauses condition on individual tuples

ex: WHERE quantity > 100

41
Q

SQL is Compositional.

What does that mean?

Why is it important?

A

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

42
Q

SQL Command:

EXCEPT

A

Remove attribute “A” of table R that are also in table S:

SELECT R.A

FROM R

EXCEPT

SELECT R.A

FROM R, S

WHERE R.A = S.A

Database Systems (18 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions