Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Java > SQL 2 > Flashcards

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
Q

AND

A

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

26
Q

OR

A

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

27
Q

NOT

A

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

28
Q

IN

A

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

29
Q

BETWEEN

A

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

30
Q

ANY/SOM

A

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
Q

ALL

A

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
Q

EXISTS

A

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
Q

Functions (Built-in)

A

Scalar

Aggregate

34
Q

Scalar

A

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
Q

Aggregate

A 
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
Q

Scalar TYPE

Numeric Functions

A

Functions that accept numeric input and return numeric values

Examples: 
ABS(value) 
CEIL(value) 
FLOOR(value) 
TRUNC(value1, value2) 
ROUND(value1, value2)
37
Q

Scalar TYPE

Character or Text Functions

A

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
Q

Scalar TYPE

Date Functions

A

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
Q

Scalar TYPE

Conversion Functions

A

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
Q

SubQueries

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

Non-correlated

A

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
Q

EX OF Non-correlated

A

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
Q

Correlated

A

The inner query is not independent.

44
Q

Example of Correlated

A

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
Q

Joins

A

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

46
Q

TYPES OF JOINS

A

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
Q

Cross Join

A

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
Q

Self Join

A 
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
Q

Set Operations

A

Used for comparing Result Sets with the same number and types of columns
NOT comparing tables.

TYPES
UNION ALL
UNION
INTERSECT
MINUS
50
Q

UNION ALL

A

Adds two Result Sets to make one Result Set!

Allows for duplicates.

51
Q

UNION

A

Return distinct values from 2 result sets

52
Q

INTERSECT

A

Return rows only returned by both queries

53
Q

MINUS

A

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

54
Q

difference between join and union

A

joins combine data into new columns

Unions combine data into new rows.

Java (9 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions