SQL 2 Flashcards
AWS
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.
Database
A collection of data stored as related entities or tables with entries expressed as rows.
Distributed Architectures
keeping everything in one related model.
Multiplicity
Multiplicity (a.k.a Cardinality) - description of relationships between fields and tables
1-1
1-Many
Many-Many
1-1
Person-to-SSN
1-Many
Teacher-to-students
Person-to-toes
Car-to-people (if you’re carpooling)
Many-Many
Authors-to-books
Students-to-courses
Users-to-websites
WHY Referential Integrity
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.
Entity Relation Diagram (ERD)
lets you map out the relationships between different entities in a database.
DOES SQL AUTO COMMIT
SQL Developer does not auto-commit, so if you want to commit, click the little green checkmark button.
JDBC
Java Database Connectivity
Package that all this stuff comes in is java.sql.
Exceptions: SqlException
JDBC INTERFACES
Connection
Statement
ResultSet
Connection
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..
Statement
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.
ResultSet
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.
DAO Design Pattern
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
Query
Operation that retrieves data from one or more tables or a view.
Top level SELECT statement
Query Makeup
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
GROUP BY
Groups your result set so far. Wide to narrow. Aggregate functions (avg, Max,min,etc)
HAVING
HAVING clause when used with the GROUP BY clause in a query tells the GROUP BY which groups to include in the output
Aggregate
ORDER BY
Default is ascending
DESC for descending
DIFFERENCE BETWEEN WHERE AND HAVING CLAUSE
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
Example of a full query
SELECT COUNT(EMPLOYEEID),CITY,COUNTRY FROM EMPLOYEE GROUP BY COUNTRY,CITY HAVING COUNT(EMPLOYEEID)>1;
Logical Operators
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
AND
compares between two Booleans as expression and returns true when both expressions are true.
OR
compares between two Booleans as expression and returns true when one of the expression is true.
NOT
takes a single Boolean as an argument and changes its value from false to true or from true to false.
IN
checks a value within a set of values separated by commas and retrieve the rows from the table which are matching.
BETWEEN
tests an expression against a range. The range consists of a beginning, followed by an AND keyword and an end expression.
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.
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.
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’.
Functions (Built-in)
Scalar
Aggregate
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
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.
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)
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)
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)
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)
SubQueries
Query nested inside query (SELECT in a SELECT) Narrow down a Result Set Nested in WHERE clause Non-correlated Correlated
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
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');
Correlated
The inner query is not independent.
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.
Joins
Returns a result set by combining two or more tables.
Classifications of Joins
Equi - =
Theta - >,
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
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;
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;
Set Operations
Used for comparing Result Sets with the same number and types of columns
NOT comparing tables.
TYPES UNION ALL UNION INTERSECT MINUS
UNION ALL
Adds two Result Sets to make one Result Set!
Allows for duplicates.
UNION
Return distinct values from 2 result sets
INTERSECT
Return rows only returned by both queries
MINUS
Returns only unique rows returned by the first queries that are NOT returned by the second query.
difference between join and union
joins combine data into new columns
Unions combine data into new rows.
