MySQL: Ol Second Package

0.0(0)
Studied by 0 people
call kaiCall Kai
learnLearn
examPractice Test
spaced repetitionSpaced Repetition
heart puzzleMatch
flashcardsFlashcards
GameKnowt Play
Card Sorting

1/23

encourage image

There's no tags or description

Looks like no tags are added yet.

24 Terms

1
New cards

IN

An operator that is used in the WHERE clause to check if a specified column's value matches any value within a provided list; marks ‘True’ if it does.


Format:

SELECT column_name(s)
FROM table_name
WHERE column_name (TERM) (value1, value2, ...);


Example:

SELECT * FROM Customers

WHERE Country (TERM) ('Germany', 'France', 'UK');

2
New cards

NOT IN

An operator that is used in the WHERE clause to determine if a specified column’s value does not match any value within a provided list, marks ‘True’ if it doesn’t.


Format:

SELECT column_name(s)
FROM table_name
WHERE column_name (TERM) (value1, value2, ...);

3
New cards

Subquery / Subselect

A SELECT query contained within another SQL statement, commonly another SELECT, which helps create dynamic functions;


Result: The inner SELECT statement is inserted (with parentheses) into the outer statement.


Example 01:

SELECT * FROM table_A

WHERE col_A=(SELECT max(col_A) from table_A);

(Looks to see if each column value matches the maximum column value from the table)


Example 02:

SELECT * FROM Students

WHERE Score >=(SELECT AVG(Score) from Students)


(Looks for scores that are greater than or equal to the average)

4
New cards

JOIN

An operator that is used to combine rows from two or more tables, based on a related column between them.

5
New cards

Cartesian Product

The combination of every row in one table with every row of a second table.

  • The resulting table has all columns of both tables (even duplicates)

  • If the input table of a Cartesian Product has {A} and {B} number of rows, the output table has {A x B} number of rows


Sets of all ordered pairs (a, b) where a is an element of A and b is an element of B.


<p>The combination of every row in one table with every row of a second table.</p><ul><li><p>The resulting table has <strong>all columns of both tables (even duplicates)</strong></p></li><li><p>If the input table of a Cartesian Product has {A} and {B} number of rows, the output table has {A x B} number of rows</p></li></ul><p></p><p><strong>Sets of all ordered pairs (a, b) where a is an element of A and b is an element of B.</strong></p><p></p>
6
New cards

SELECT <select_list>

From Table A A

INNER JOIN in Table B B

ON A.Key = B.Key

Write what this signifies in MySQL.

<p>Write what this signifies in MySQL.</p>
7
New cards

SELECT <select_list>

From Table A A

LEFT JOIN in Table B B

ON A.Key = B.Key

Write what this signifies in MySQL.

<p>Write what this signifies in MySQL.</p>
8
New cards

Scalar Subquery

A subquery that returns a single value, which is often used in the SELECT list and WHERE clause (like finding the average employee salary)

  • The subquery is used in the WHERE clause test condition!

  • They both involve the same table!


<p>A subquery that returns a single value, which is often used in the SELECT list and WHERE clause <em>(like finding the average employee salary)</em></p><ul><li><p>The subquery is used in the WHERE clause test condition!</p></li><li><p>They both involve the same table!</p></li></ul><p></p>
9
New cards

Column Subquery

A subquery that returns a single column of values, which can be sued where a column is expected in the outer query; l typically used in combination with the IN or NOT IN operators

<p>A subquery that returns a single column of values, which can be sued where a column is expected in the outer query; l typically used in combination with the IN or NOT IN operators</p>
10
New cards

Row Subquery

A subquery that lets you return a row with multiple columns, there are less common but useful when you need to compare multiple values from a subquery


<p>A subquery that lets you return a row with multiple columns, there are less common but useful when you need to compare multiple values from a subquery</p><p></p>
11
New cards

Table Subquery

A subquery that returns an entire table, which can be used in the outer query like a regular table (often used in the FROM clause and can be handy for complex filtering and transformations that are hard to express otherwise)


(Example: Creates a temporary table of sales from last month, outer query calculates the total quantity sold fore ach product)

<p>A subquery that returns an entire table, which can be used in the outer query like a regular table (often used in the FROM clause and can be handy for complex filtering and transformations that are hard to express otherwise)</p><p></p><p><strong><em>(Example: Creates a temporary table of sales from last month, outer query calculates the total quantity sold fore ach product)</em></strong></p>
12
New cards

Correlated Subquery

A subquery that contains a reference to a table or column that is outside of the scope of the subquery:


Details about the example:

  • The outer query goes through the employee table EMP, and gives that table the nickname X

  • The inner query looks at the project table (PROJ), in which X.WORKDEPT refers to the Employee row to get the work department for each employee —> “Give me every designer whose department has at least one project under major project MA2100.”


<p>A subquery that contains a reference to a table or column that is <strong>outside</strong> of the scope of the subquery:</p><p></p><p><strong>Details about the example:</strong></p><ul><li><p>The outer query goes through the employee table EMP, and gives that table the nickname X</p></li><li><p>The inner query looks at the project table (PROJ), in which X.WORKDEPT refers to the  Employee row to get the work department for each employee —&gt; <strong>“Give me every designer whose department has at least one project under major project MA2100.”</strong></p></li></ul><p></p>
13
New cards

Noncorrelated Subquery

A subquery in which the inner and outer query are independent of each other, the inner query is evaluated once and the result is passed to the outer query as it if it was a literal (or static) value.

  • In fact, the inner query may not even be aware that the outer query exists!


<p>A subquery in which the inner and outer query are independent of each other, the inner query is evaluated once and the result is passed to the outer query as it if it was a literal (or static) value.</p><ul><li><p>In fact, the inner query may not even be aware that the outer query exists!</p></li></ul><p></p>
14
New cards

No!

Are subqueries limited to only having one inner subquery for an outer subquery?

The attached example: Selects first and last names of authors if their income percentage is greater than the average collective income percentage!

<p>Are subqueries limited to only having one inner subquery for an outer subquery?</p><p><strong>The attached example: </strong>Selects first and last names of authors if their income percentage is greater than the average collective income percentage!</p>
15
New cards

The outer subquery typically provides the returned output, while the inner subquery usually filters the data!

What are the typical roles and responsibilities of the outer query versus the inner query?

16
New cards

INNER JOIN

Returns only rows that have matching values in both tables; typically the default JOIN type.

  • Identical to a CROSS JOIN, but is intended to be used with a join condition. Functionally similar to a WHERE clause, the join condition is used to limit the rows returned from the join input

    • Difference from WHERE: A join condition intends to return rows whose columns are related in a meaningful manner


<p>Returns only rows that have matching values in both tables; typically the default JOIN type.</p><ul><li><p>Identical to a CROSS JOIN, but is intended to be used with a join condition. Functionally similar to a WHERE clause, the join condition is used to limit the rows returned from the join input</p><ul><li><p>Difference from WHERE: A join condition intends to return rows whose columns are related in a meaningful manner</p></li></ul></li></ul><p></p>
17
New cards

Recombines them back together in order to retrieve information

What is the primary mechanism of JOIN, serving its purpose since normalization predominantly breaks data into separate tables?

18
New cards

ON (ON Clause)

A fundamental component of SQL Joins, allowing you to specify the conditions for joining tables.

19
New cards

INNER JOIN

What is the following an example of?

<p>What is the following an example of?</p>
20
New cards

NATURAL JOIN

A JOIN that automatically matches and combines rows from two tables based on columns with the same name and compatible data types!

  • Unlike other joins, you don't need to specify a condition manually - SQL detects common columns and returns a result without redundant duplicate columns!



21
New cards

Equi-Joins

Join conditions that are based on the equality operator (Natural Join, Inner Join)

<p>Join conditions that are based on the equality operator (Natural Join, Inner Join)</p>
22
New cards

Non-Equi Joins

Less common join conditions that use other comparison operators in the join condition.

INCLUDES:

  • Other comparison operators such as >, >=, <, <=, <>. BETWEEN, and LIKE


<p>Less common join conditions that use other comparison operators in the join condition.</p><p><strong>INCLUDES:</strong></p><ul><li><p>Other comparison operators such as &gt;, &gt;=, &lt;, &lt;=, &lt;&gt;. BETWEEN, and LIKE</p></li></ul><p></p>
23
New cards

OUTER JOIN

A JOIN clause that keeps the matching rows and also keeps rows that have no match, in which the missing values are filled with NULL.

  • LEFT (term): Keeps every row from the left table

  • RIGHT (term): Keeps every row from the right table

  • FULL (term): Keeps every row from both tables


24
New cards