SQL 2 Flashcards

1
Q

AWS

A

Provides a platform as a service
You buy the services you need, Amazon houses and maintains it
AWS and GoDaddy are essentially the same service, but AWS is a little more robust. AWS is very granular in service, lots of configurability, load balancing. More control over what happens behind the scenes. GoDaddy is more out-of-the-box.

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

Database

A

A collection of data stored as related entities or tables with entries expressed as rows.

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

Distributed Architectures

A

keeping everything in one related model.

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

Multiplicity

A

Multiplicity (a.k.a Cardinality) - description of relationships between fields and tables

1-1
1-Many
Many-Many

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

1-1

A

Person-to-SSN

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

1-Many

A

Teacher-to-students
Person-to-toes
Car-to-people (if you’re carpooling)

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

Many-Many

A

Authors-to-books
Students-to-courses
Users-to-websites

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

WHY Referential Integrity

A

You want relationships between tables to remain consistent when entries are changed. If we delete the Checking Account in the above table, Craig’s AcctID (Foreign Key) will point to something that no longer exists.
All Foreign Keys should match to the correct value.

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

Entity Relation Diagram (ERD)

A

lets you map out the relationships between different entities in a database.

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

DOES SQL AUTO COMMIT

A

SQL Developer does not auto-commit, so if you want to commit, click the little green checkmark button.

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

JDBC

A

Java Database Connectivity
Package that all this stuff comes in is java.sql.
Exceptions: SqlException

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

JDBC INTERFACES

A

Connection
Statement
ResultSet

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

Connection

A

A session with a particular database.
Obtained from the DriverManager object.
We’re going to give our DriverManager the URL, Username, Password to our database, and it’ll give us the type of connection we need..

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

Statement

A

If we want to execute a query or use our tables.
Takes a raw string and executes it as an SQL query.
Don’t use this. It’s BAD. We’re worried about SQL injections, and Statements make it very easy to perform SQL injections. You can have malicious code injected, since it’s a raw query.
Forms the backbone for other statements.

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

ResultSet

A

Not actually a Connection, but it behaves similarly to a Connection.
Table of results returned by execution of SQL query.
Also maintains a cursor which starts off pointing to the first row.
Has a method called next() which moves the cursor to the next row. Similar to an Iterator. Does not contain a hasNext() method, but it next() returns a boolean, so it can be used as an argument within a while-loop condition.

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

DAO Design Pattern

A

Data Access Object
Modularity and loose coupling
You want to make absolutely sure that the only thing that is interacting with your database is your DAOImpl.
DAO is an open interface with multiple implementations

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

Query

A

Operation that retrieves data from one or more tables or a view.
Top level SELECT statement

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

Query Makeup

A
SELECT [column list] -Required
FROM [table list]-Required
WHERE [condition1] AND[condition2]... 
GROUP BY [column list]
HAVING [condition]
WHERE
HAVING clause places conditions on groups created by the GROUP BY clause
Aggregate
ORDER BY [column list]
Default is ascending
DESC for descending
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

GROUP BY

A
Groups your result set so far. Wide to narrow.
Aggregate functions (avg, Max,min,etc)
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

HAVING

A

HAVING clause when used with the GROUP BY clause in a query tells the GROUP BY which groups to include in the output
Aggregate

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

ORDER BY

A

Default is ascending

DESC for descending

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

DIFFERENCE BETWEEN WHERE AND HAVING CLAUSE

A

HAVING is to GROUP BY as WHERE is to SELECT
WHERE clause places conditions on the selected columns
HAVING clause places conditions on groups created by the GROUP BY clause

23
Q

Example of a full query

A
SELECT COUNT(EMPLOYEEID),CITY,COUNTRY FROM EMPLOYEE 
GROUP BY COUNTRY,CITY HAVING COUNT(EMPLOYEEID)>1;
24
Q

Logical Operators

A

Logical operators are those that are true or false. They return true or false values to combine one or more true or false values.

AND 
OR 
NOT 
IN 
BETWEEN 
ANY/SOME 
ALL
EXISTS
25
AND
compares between two Booleans as expression and returns true when both expressions are true.
26
OR
compares between two Booleans as expression and returns true when one of the expression is true.
27
NOT
takes a single Boolean as an argument and changes its value from false to true or from true to false.
28
IN
checks a value within a set of values separated by commas and retrieve the rows from the table which are matching.
29
BETWEEN
tests an expression against a range. The range consists of a beginning, followed by an AND keyword and an end expression.
30
ANY/SOM
compares a value to each value in a list or results from a query and evaluates to true if the result of an inner query contains at least one row.
31
ALL
used to select all records of a SELECT statement. It compares a value to every value in a list or results from a query. must be preceded by the comparison operators and evaluates to TRUE if the query returns no rows.
32
EXISTS
checks the existence of a result of a subquery. tests whether a subquery fetches at least one row when no data is returned then this operator returns 'FALSE'.
33
Functions (Built-in)
Scalar | Aggregate
34
Scalar
returns a single row for every row of a queried table or row. Single row to every row. One-to-one. ``` FOUR TYPES Numeric Functions Character or Text Functions Date Functions Conversion Functions ```
35
Aggregate
``` returns a single result or set on a group of rows/table Average SELECT AVG(SALARY) FROM EMPLOYEE; Averages all of the values in the salary column of EMPLOYEE and returns a single result. ```
36
Scalar TYPE | Numeric Functions
Functions that accept numeric input and return numeric values ``` Examples: ABS(value) CEIL(value) FLOOR(value) TRUNC(value1, value2) ROUND(value1, value2) ```
37
Scalar TYPE | Character or Text Functions
Functions that accept character input and can return both character and numeric values ``` Examples: LOWER(string_value) UPPER(string_value) INITCAP(string_value) LTRIM(string_value, trim_text) RTRIM(string_value, trim_text) TRIM(trim_text FROM string_value) SUBSTR(string_value, m, n) LENGTH(string_value) LPAD(string_value, n, pad_value) RPAD(string_value, n, pad_value) ```
38
Scalar TYPE | Date Functions
Functions that take values that are of datatype DATE as input and return values of datatype DATE, except for the MONTHS_BETWEEN function, which returns a number ``` Examples: ADD_MONTH(date, n) MONTHS_BETWEEN(date1, date2) ROUND(x, date_format) TRUNC(x, date_format) NEXT_DAY(date, week_day) LAST_DAY(date) SYSDATE NEW_TIME(date, zone1, zone2) ```
39
Scalar TYPE | Conversion Functions
Functions that help us to convert a value in one form to another form. ``` Examples: TO_CHAR(value2) TO_DATE(value1) NVL(value1, value2) DECODE(a, b, c, d, e, default_value) ```
40
SubQueries
``` Query nested inside query (SELECT in a SELECT) Narrow down a Result Set Nested in WHERE clause Non-correlated Correlated ```
41
Non-correlated
The inner query can execute independently of the outer query. After the inner query executes, the outer query executes independently. Correlated The inner query is not independent. Example of Correlated
42
EX OF Non-correlated
StudentDetails Table S_ID S_NAME S_MAJOR ``` SELECT S_ID,S_NAME FROM StudentDetails WHERE S_NAME IN ( SELECT S_NAME FROM StudentDetails WHERE S_MAJOR = 'History'); ```
43
Correlated
The inner query is not independent.
44
Example of Correlated
ORDER Table O_ID PRODUCT QTY PRODUCT Table P_ID P_NAME ``` SELECT P_NAME FROM PRODUCT P WHERE P_ID=( SELECT O.PRODUCT FROM ORDER O WHERE O.PRODUCT=P.PID); ``` O and P are examples of aliases. You can create the alias O “after” using it in the previous SELECT line, since it’s part of the same clause. P is referencing the outer query from the inner query, so the inner query is dependent on the outer query. For it to be independent (non-correlated), you’d have to be able to remove the alias P and have it work.
45
Joins
Returns a result set by combining two or more tables. Classifications of Joins Equi - = Theta - >,
46
TYPES OF JOINS
Inner Join - Return only rows that return a match Full Outer Join - result set contains entries from both Right Join - whatever’s on the right, including matches (relationships with left) Left Join - whatever’s on the left, including matches (relationships with right) Full Join - result set contains entries from both tables, including matches
47
Cross Join
Cartesian Product Multiplying joining columns to produce all possible combos Products and Sizes EX SELECT * FROM SHIRTS CROSS JOIN SIZES; OR SELECT * FROM SHIRTS, SIZES;
48
Self Join
``` Table joined with itself EX. SELECT A.NAME AS STUDENT, B.NAME AS LAB.PARTNER FROM STUDENT AS A JOIN STUDENT AS B ON A.LAB_PARTNER=B.ID; ```
49
Set Operations
Used for comparing Result Sets with the same number and types of columns NOT comparing tables. ``` TYPES UNION ALL UNION INTERSECT MINUS ```
50
UNION ALL
Adds two Result Sets to make one Result Set! | Allows for duplicates.
51
UNION
Return distinct values from 2 result sets
52
INTERSECT
Return rows only returned by both queries
53
MINUS
Returns only unique rows returned by the first queries that are NOT returned by the second query.
54
difference between join and union
joins combine data into new columns | Unions combine data into new rows.