2. Introduction to SQL

Question 1

Features

Answer 1

Data Manipulation Language:

-Query data
-Insert/modify / remove data

Data Definition Language :

-Create/ Modify tables
-Define triggers
-Create constraints

Question 2

What are the main data manipulation tasks?

Answer 2

Requesting data
Inserting data
Deleting data
Changing data

Question 3

What are the main data definition tasks?

Answer 3

Create tables
Deleting tables
Changing tables

Question 4

What does it mean for SQL to be strongly types?

Answer 4

Error if you use the wrong data types

Question 5

What does it mean for SQL to be statically typed?

Answer 5

You cannot modify the type mid program

Question 6

What are the main data types in SQL?

Answer 6

Boolean - T/F
Integer - integers
Decimal - exact fixed point number
Double - 64 bit floating point numbers
Date - date
DATETIME - a date plus time
CHAR(n) - fixed length character string
VARCHAR(n) - variable length character string
TEXT - string of text
BLOB - binary storage

Question 7

What database design feature does strong typing help solve?

Answer 7

Data integrity –> overall accuracy and consistency of data

Question 8

Restriction

Answer 8

Condition: boolean expression for which rows to include

Made of :
- Column names from referenced tables
- Literals : numbers, strings….
- Operators: =, <> , >, >=, <, <=
- Combining terms: AND, OR
- Missing values: IS NULL, IS NOT NULL
- Within a subquery: IN, NOT IN

Question 9

Ordering Output

Answer 9

ORDER BY

Question 10

Cross Join (Cartesian product)

Answer 10

Produces all possible combinations of the rows of 2 tables (does not care if there are crossovers that are false)

DO NOT USE THS

Question 11

Inner Join (Natural Join)

Answer 11

Keeps only rows with matching common columns

Eliminates rows from both tables that do not satisfy the join condition –> gives us the INTERSECTION of two tables ( think stats)

Most common join

Question 12

Outer Join

Answer 12

Returns matched values and unmatched values from either or both tables

3 Types:

1) LEFT JOIN: returns only unmatched rows from the left table, as well as matched rows in both tables

2) RIGHT JOIN: returns only unmatched rows from the right table, as well as matched rows in both tables

3) FULL OUTER JOIN: returns unmatched rows from both tables, as well as matched rows in both tables

THINK OF THE JOINS IN TERMS OF PROBABILITY CONCEPTS

Question 13

Aliasing tables

Answer 13

Tool for abbreviating reference to table name –> useful for referring to commonly used tables

Syntax:

<table> AS <alias>

EX:
Select d.dname, e.salary
FROM Department AS d, EMPLOYEE as e
WHERE d.id = e.id
</alias></table>