SQL 2

Question 1

AWS

Answer 1

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.

Question 2

Database

Answer 2

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

Question 3

Distributed Architectures

Answer 3

keeping everything in one related model.

Question 4

Multiplicity

Answer 4

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

1-1
1-Many
Many-Many

Question 5

1-1

Answer 5

Person-to-SSN

Question 6

1-Many

Answer 6

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

Question 7

Many-Many

Answer 7

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

Question 8

WHY Referential Integrity

Answer 8

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.

Question 9

Entity Relation Diagram (ERD)

Answer 9

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

Question 10

DOES SQL AUTO COMMIT

Answer 10

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

Question 11

JDBC

Answer 11

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

Question 12

JDBC INTERFACES

Answer 12

Connection
Statement
ResultSet

Question 13

Connection

Answer 13

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..

Question 14

Statement

Answer 14

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.

Question 15

ResultSet

Answer 15

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.

Question 16

DAO Design Pattern

Answer 16

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

Question 17

Query

Answer 17

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

Question 18

Query Makeup

Answer 18

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

Question 19

GROUP BY

Answer 19

Groups your result set so far. Wide to narrow.
Aggregate functions (avg, Max,min,etc)

Question 20

HAVING

Answer 20

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

Question 21

ORDER BY

Answer 21

Default is ascending

DESC for descending

Question 22

DIFFERENCE BETWEEN WHERE AND HAVING CLAUSE

Answer 22

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

Question 23

Example of a full query

Answer 23

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

Question 24

Logical Operators

Answer 24

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

Question 25

AND

Answer 25

compares between two Booleans as expression and returns true when both expressions are true.

Question 26

OR

Answer 26

compares between two Booleans as expression and returns true when one of the expression is true.

Question 27

NOT

Answer 27

takes a single Boolean as an argument and changes its value from false to true or from true to false.

Question 28

IN

Answer 28

checks a value within a set of values separated by commas and retrieve the rows from the table which are matching.

Question 29

BETWEEN

Answer 29

tests an expression against a range. The range consists of a beginning, followed by an AND keyword and an end expression.

Question 30

ANY/SOM

Answer 30

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.

Question 31

ALL

Answer 31

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.

Question 32

EXISTS

Answer 32

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'.

Question 33

Functions (Built-in)

Answer 33

Scalar | Aggregate

Question 34

Scalar

Answer 34

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 ```

Question 35

Aggregate

Answer 35

``` 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. ```

Question 36

Scalar TYPE | Numeric Functions

Answer 36

Functions that accept numeric input and return numeric values ``` Examples: ABS(value) CEIL(value) FLOOR(value) TRUNC(value1, value2) ROUND(value1, value2) ```

Question 37

Scalar TYPE | Character or Text Functions

Answer 37

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) ```

Question 38

Scalar TYPE | Date Functions

Answer 38

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) ```

Question 39

Scalar TYPE | Conversion Functions

Answer 39

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) ```

Question 40

SubQueries

Answer 40

``` Query nested inside query (SELECT in a SELECT) Narrow down a Result Set Nested in WHERE clause Non-correlated Correlated ```

Question 41

Non-correlated

Answer 41

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

Question 42

EX OF Non-correlated

Answer 42

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'); ```

Question 43

Correlated

Answer 43

The inner query is not independent.

Question 44

Example of Correlated

Answer 44

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.

Question 45

Joins

Answer 45

Returns a result set by combining two or more tables. Classifications of Joins Equi - = Theta - >,

Question 46

TYPES OF JOINS

Answer 46

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

Question 47

Cross Join

Answer 47

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;

Question 48

Self Join

Answer 48

``` 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; ```

Question 49

Set Operations

Answer 49

Used for comparing Result Sets with the same number and types of columns NOT comparing tables. ``` TYPES UNION ALL UNION INTERSECT MINUS ```

Question 50

UNION ALL

Answer 50

Adds two Result Sets to make one Result Set! | Allows for duplicates.

Question 51

UNION

Answer 51

Return distinct values from 2 result sets

Question 52

INTERSECT

Answer 52

Return rows only returned by both queries

Question 53

MINUS

Answer 53

Returns only unique rows returned by the first queries that are NOT returned by the second query.

Question 54

difference between join and union

Answer 54

joins combine data into new columns | Unions combine data into new rows.