Lecture 6- SQL Basics Flashcards
What are some features of SQL?
- Declarative = specify the properties that should hold in the result, not how to obtain the result
- Complex queries have procedural elements
- Set/Bag semantics
What is part of the data definition component?
- CREATE TABLE
- DROP TABLE
- ALTER TABLE (ADD, MODIFY, ADD constraint)
What is part of the data update component?
- INSERT INTO
- DELETE FROM
- UPDATE
What is SQL?
A query language
What is this query doing?
branch (bname, address, city, assets)
Find the names of all branches with assets greater than $2,500,000
What is this query doing?
branch (bname, address, city, assets)
Find the names of all branches in Edmonton with assets greater than $2,500,000
What is this query of two relations doing?
customer (cname, street, city)
deposit (accno, cname, bname, balance)
List the name and city of every customer who has an account with balance over $2,000
Write the query for this “find customers who have both loans and deposits” and what is the type of query?
loan (accno, cname, bname, amount)
deposit (accno, cname, bname, balance)
Queries with tuple variables
What is the evaluation strategy for this algorithm?
SELECT …
FROM R1 r1, R2 r2, …
WHERE C
- FROM clause produces cartesian product of listed tables
- WHERE clause produces table containing only rows satisfying condition
- SELECT clause retains listed columns
This is equivalent to what?
To get rid of duplicates we need to?
Use DISTINCT
Duplication elimination is not done ________ and must be _______
- Automatically
- Explicitly requested
Describe queries working with strings
- Equality and comparison operators apply to strings (based on lexical ordering) = Ex. WHERE cname < ‘P’
- Concatenate operator applies to strings = Ex. WHERE bname ||’–’|| address = …
- Expressions can also be used in SELECT clause = Ex. SELECT bname ||’–’|| address AS NameAdd FROM branch
What is partial matching?
Allows you to search for rows where a string column contains a certain pattern or portion of a string
Describe the LIKE operator
- Used to filter data based on pattern matching
- Patterns can be written using wildcard characters
- % = matches any sequence of characters (including zero characters)
- Underscore = matches exactly one character
- g _ % = matches values that start with letter g
This is an example of what?
Partial matching
These are examples of what?
Partial matching
What is this query looking for? (partial matching)
customer (cname, street, city)
Find every customer whose address starts with Computing Science
What is this query doing?
deposit (accno, cname, bname, balance)
- For every deposit holder who has over $1,000 find the customer name and the balance over $1,000
- Naming the result
What is this query doing?
branch (bname, address, city, assets)
- Find the names and assets of all branches with assets greater than $2,500,000 and order the results in ascending order of asset values
What is the default order when ordering the results? And how to chose the opposit?
- Ascending order is default
- To get descending order, use DESC keyword
Describe the three different set operators
- Set union = Q1 UNION Q2 (set of tuples in Q1 or Q2)
- Set difference = Q1 EXCEPT Q2 (The set of tuples in Q1 but not in Q2)
- Set intersection = Q1 INTERSECT Q2 (the set of tuples in both Q1 and Q2)
Q1 and Q2 must be ________ (same number/ types of attributes)
Union compatible
What is this query doing?
List deposit holders who have no loans (set difference)
What is this query doing?
List cities where there is either a customer or a branch (set union)
What is this query doing?
Find all cities that have both customers and branches (set intersection)
Describe the conceptual evaluation strategy
- Compute the cross product of the tables in the from-list
- Delete rows in the cross product that fail the qualification conditions
- Delete all columns that do not appear in the select-list
- If DISTINCT is specific, eliminate duplicate rows
What does this query do?
- Find the name and asset of every branch that has a deposit account holders who lives in Jasper
- The query first selects customers who live in the city of Jasper
- Then it looks for deposits made by those customers
- It then finds the branch where these deposits were made
- Finally, it selects the branch name and assets of the branches
What type of query is this?
Queries within the WHERE clause of an outer query (nested queries)
Should you avoid nesting in queries?
Yes
What is a subquery?
- A subquery is a query within a query
- Its an inner query placed within an outer query
- The inner query is executed first, afterwards the results is passed to the outer query
What does this query do?
- The outer query extracts details of all employees whose annual salaries are greater than the specified value
- The subquery must identify the annual salary of the assistant chef
- When executed, the subquery provides a subset of data from the employees relation
- This subset of data is then used as input for the outer query
What are the different types of nested operators?
- IN
- ALL (=, >, <, <=, >=)
- ANY (=, >, <, <=, >=)
- SOME (=, >, <, <=, >=)
- EXISTS
What is IN used for?
Used to specify multiple possible values for a column
What does this query do?
Will filter out all the student records that have a value of USA and UK
What does this query do? And an alternative to nesting?
- The query retrieves the names of customers who have deposited money in the same branch or branches where John Doe has made a deposit
- The subquery retrives the branch names (bname) where John Doe has made deposits
- It returns a list of all the branch names where John Doe has an account or deposit
- The outer query uses this list of branches from the subquery to find all the customers (cname) who have also deposited money in any of these branches
What does ALL, SOME and ANY evaluate to?
- ALL evaluates to true iff the comparison evaluates to tru form every value in the set
- SOME evaluates to true iff the comparison evaluates to true for at least one value in the set
- ANY evaluates to true iff the comparison evaluates true for any value in the set
What type of query is this?
All subquery
What does this query do?
branch (bname, address, city, assets)
Find branches that have assets greater than the assets of all branches in Calgary
What does this query do?
- Identifies employees earnings annual salary that’s less than or equal to the annual salaries earned by all employees in the following roles - Manager, Head chef, Assistant manager, Head waiter
- Outer query is to identify all employees who earn an annual salary that is less than or equal to the specified values
- A subquery that extracts the data of annual salary earned by employees who are in the specified roles
What type of query is this? And describe what it does
- EXISTS nested query
- EXISTS (SELECT …) evaluates to true iff the results of the subquery contains at least one row
- The expression is evaluated for every iteration of the outer query
- EXISTS implies its non-empty
- NOT EXISTS implies it is empty
What is this query doing?
branch (bname, address, city, assets)
customer (cname, street, city)
Find the names of customers who live in a city with no bank branches
What is division in queries?
Find the subset of items in one set that are related to all items in another set
What is this query doing?
- Division
- Find customer who have deposit accounts in all branches
- Find set A of all branches in which a particular customer c has a deposit account
- Find set B of all branches
- Output c if A ⊇ B or quivalently if B-A is empty
- Relation A contains relation B = NOT EXISTS (B EXCEPT A)
What does this query do?
- Division
- Find professors who have taught courses in all departments
- Find set A of all departments in which a particular professor p has taught a course
- Find set B of all departments
- Output p if A ⊇ B or equivalently is B-A is empty
- Relation A contains relation B = NOT EXISTS (B EXCEPT A)
SQLite vs SQL-92
- Does not support op ALL in nested queries
- Does not support RIGHT OUTER JOIN and FULL OUTER JOIN
- Views are read only
- ALTER TABLE command is very limited
- Foreign key constraints are not enforced by default
- More left to be explored
What are the basic data types in SQLite?
- INTEGER
- REAL
- TEXT
- BLOB
- NULL
- NUMERIC
