Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

SQL

memorize.ai (lvl 286)

Section 1

Preview this deck

Jumping through three tables to find a correlative piece of data

Front

1 / 23

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

1 year ago

Date created

Mar 14, 2020

Cards (23)

Section 1

(23 cards)

Jumping through three tables to find a correlative piece of data

Front

EXAMPLE: SELECT * FROM actor WHERE act_it IN (SELECT act_id FROM movie_cast WHERE mov_id IN (SELECT mov_id FROM movie WHERE mov_title='Annie Hall'

Back

Ordering data

Front

ORDER BY '......' - ORDER BY '.....' desc = is everything most recent to least recent (highest to lowest)

Back

Different Keys

Front

Primary: A column of values that uniquely identifies a given row (no null values allowed) Foreign: Maintains the integrity of the links between data in two different tables (i.e. the foreign key in table b references the primary key in table a) Unique: Uniquely identifies one row in the table. However, multiple and null values are allowed

Back

Syntax to show output for two sub-queries contained within the macro query

Front

1. select * from nobel_win 2. where (SUBJECT= 'Physics' and YEAR=1970) 3. UNION (select * from nobel_win where (SUBJECT='Economics' and YEAR='1971'))

Back

Syntax to select specific data from a table

Front

select ..... from .....;

Back

Select data from a range

Front

- .....>/<.... - .....>=1965 and .....<=1975

Back

Syntax to select specific data from a table that belongs to a specific category

Front

select ..... from ..... where ..... = '......';

Back

WHERE vs. HAVING

Front

Main difference is when using GROUP BY - WHERE filters rows before grouping and HAVING excludes rows after grouping

Back

Query data that is everything but a certain parameter/type

Front

NOT LIKE '....' - I.E. everything that doesn't start with 'P' = NOT LIKE 'P%') also <>

Back

Show results from all but some categories

Front

... NOT IN ('.....', '.........')

Back

Select a specific number of rows

Front

LIMIT x (starting row), x (number of rows to count after that starting row) - the FIRST ROW in the database is always ZERO

Back

Show output for two different data queries in the same output

Front

UNION

Back

Joining data rows from two data sets based on a commonality

Front

JOIN Example: select Track.title, Genre.name FROM Track JOIN Genre ON Track.genre_id = Genre.genre_id

Back

Only choose distinct values

Front

SELECT DISTINCT

Back

Set operations

Front

1. UNION: Combines two tables without any duplicates 2. UNION ALL: Combines two tables and includes duplicates 3. INTERSECT: Only returns duplicate rows in the two tables 4. MINUS: Only returns unique rows in the first table

Back

INNER vs. LEFT vs. RIGHT JOINS

Front

LEFT: All of table 1 with the overlap from table 2 RIGHT: All of table 2 with the overlap from table 1 INNER: Only the overlaps from table 1 and 2

Back

How to group/display data using one column

Front

GROUP BY (after from clause)

Back

Choose data without any repeats

Front

DISTINCT

Back

Query data that includes/starts with a certain parameter/type

Front

LIKE '.....' - I.E. LIKE 'Louis%'

Back

Syntax to select specific data from a table that belongs to more than one specific category

Front

select ..... from ..... where ..... = '......' AND ..... = .....;

Back

Selecting the minimum/maximum value in a list

Front

where 'xxxx'=(select MIN/MAX(xxxx) from 'table');

Back

Selecting a set of rows based on multiple ID numbers

Front

SELECT xxxx FROM xxxx WHERE xxx_id in (xxxx, xxxx, xxxx); - The "in" is the big addition

Back

JOINING

Front

- When looking for terms, always name what you're looking for first, and then use the joined tabled to find it

Back