Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

SQL

memorize.ai (lvl 286)

(0)

21 cards

()

Section 1

Preview this deck

do you need to use a alias

Front

1 / 21

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

6 years ago

Date created

Mar 1, 2020

Cards (21)

Section 1

(21 cards)

do you need to use a alias

Front

Note that you should not include an alias when you write a subquery in a conditional statement. This is because the subquery is treated as an individual value (or set of values in the IN case) rather than as a table.

Back

WHERE vs. ON ... AND

Front

Normally, filtering is processed in the WHERE clause once the two tables have already been joined. It's possible, though that you might want to filter one or both of the tables before joining them.

Back

TRIM

Front

3 arguments 1- specify whether you want to remove characters from the beginning ('leading'), the end ('trailing'), or both ('both', as used above). 2- specify all characters to be trimmed. To be included in the single quotes 3- specify the text you want to trim using FROM.

Back

better way to write: WHEN state = 'CA' or state = 'OR' or state = 'WA'

Front

WHEN state IN ('CA', 'OR', 'WA')

Back

Set or tupple

Front

(" ", " ")

Back

COALESCE

Front

use COALESCE to replace the null values: COALESCE(col_, 'No Description') that is : if null will be replaced by 'no description' otherwise will leave the value as is

Back

[^acf]

Front

Everything but

Back

[acs]

Front

a or c or s

Back

CASE statement

Front

1- The CASE statement always goes in the SELECT clause 2- CASE must include the following components: WHEN, THEN, and END. ELSE is an optional component. 3- You can make any conditional statement using any conditional operator (like WHERE ) between WHEN and THEN. This includes stringing together multiple conditional statements using AND and OR. 4- You can include multiple WHEN statements, as well as an ELSE statement to deal with any unaddressed conditions.

Back

foreign key

Front

These relationships are sometimes called "mappings." teams.school_name and players.school_name, the two columns that map to one another, are referred to as "foreign keys" or "join keys.

Back

DATE_TRUNC

Front

date_trunc ('year', date) as year

Back

Delete all data inside a table

Front

TRANCATE TABLE table_name

Back

to add a column and fill it with a string

Front

SELECT 'string' AS new_column_name

Back

[a-f]

Front

Back

left syntax

Front

LEFT(columnName, number of characters)

Back

When use self join

Front

when you need two conditions satisfied in the same column. e.g. investors country code = GBR and JPN

Back

Add a column to a table

Front

ALTER TABLE TableName ADD ColumName TYPE

Back

EXTRACT

Front

extract ('year' from date) as year

Back

Delete Columns

Front

ALTER TABLE TableName DROP COLUMN ColumnName

Back

Create new database called testDB

Front

CREATE DATABASE testDB

Back

to change the data type to a numeric one

Front

CAST(column_name AS integer) and column_name::integer produce the same result. (the second one called convert). You could replace integer with any other data type that would make sense for that column

Back