Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

SQL

memorize.ai (lvl 286)

(0)

45 cards

()

Section 1

Preview this deck

SUM -Aggregate fxn

Front

1 / 45

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 (45)

Section 1

(45 cards)

SUM -Aggregate fxn

Front

Returns the sum of the numeric values in a given column

Back

Aggregate Functions(def, type)

Front

Used to compute against a "returned column of numeric data" from your SELECT statement i.e. summarize the results of a particular column of selected data MIN, MAX, SUM, AVG, COUNT, COUNT(*)

Back

SELECT statement (full)

Front

SELECT [ALL | DISTINCT] column1[,column2] FROM table1[,table2] [WHERE "conditions"] [GROUP BY "column-list"] [HAVING "conditions] [ORDER BY "column-list" [ASC | DESC] ]

Back

DELETE statement

Front

used to delete records or rows from the table delete from "tablename" where "columnname" OPERATOR "value" [and|or "column" OPERATOR "value"]; [ ] = optional

Back

INSERT INTO statement

Front

insert into "tablename" (first_column,...last_column) values (first_value,...last_value);

Back

ROUND(x)

Front

Returns the value of x rounded to the nearest whole integer

Back

COUNT(*)-Aggregate fxn

Front

Returns the number of rows in a table

Back

What does UNION do?

Front

UNION merges the contents of two structurally-compatible tables into a single combined table.

Back

HAVING clause

Front

Allows you to specify conditions on the rows for each group- in other words, which rows should be selected will be based on the conditions you specify needs to follow GROUP BY clause SELECT column1, SUM(column2) FROM "list-of-tables" GROUP BY "column-list" HAVING "condition";

Back

MIN - Aggregate fxn

Front

Returns the smallest value in a given column

Back

Exclusionary Left Outer Join

Front

Can produce set of records that exist only in Table A ( and not Table B) using a where clause.

Back

SIGN(x)

Front

Returns the sign of input x as -1,0,1

Back

Exclusionary Full Outer Join

Front

To produce the set of records unique to Table A and Table B, we perform the same full outer join, then exclude the records we don't want from both sides via a where clause.

Back

SELECT Statement (def, partial)

Front

used to query the database and retrieve select "column1" [,"column2",etc] from "tablename" [where "condition"]; [] = optional

Back

Describe a Cross Join

Front

Returns all records where each row from the first table is combined with each row from the second table (i.e., returns the Cartesian product of the sets of rows from the joined tables). Note that a CROSS JOIN can either be specified using the CROSS JOIN syntax ("explicit join notation") or (b) listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria ("implicit join notation").

Back

FLOOR(x)

Front

Returns the largest integer value that is less than or equal to x

Back

Data type: date

Front

Date value

Back

POWER(x,y)

Front

Returns the value of x raised to the power of y

Back

ABS(x)

Front

Returns the absolute value of x

Back

Describe a Full Join

Front

(or Full Outer Join) Returns all rows for which there is a match in EITHER of the tables. Conceptually, a FULL JOIN combines the effect of applying both a LEFT JOIN and a RIGHT JOIN; i.e., its result set is equivalent to performing a UNION of the results of left and right outer queries. ex: Produces the set of all records in Table A and Table B, with matching records from both sides if available. If no match, missing side will contain null.

Back

Data type: number (size,d)

Front

Number value with a maximum number of digits of "size" total, with a max number of "d" digits to the right of the decimal.

Back

UPDATE statement (def, statement)

Front

used to update or change records that match a specified criteria update "tablename" set "columnname" = "newvalue" [,"nextcolumn" = "newvalue2"...] where "columnname" OPERATOR "value" [and|or "column" OPERATOR "value"]; [] = optional

Back

ORDER BY clause

Front

optional clause which will allow you to display the results of your query in a sorted order (either ascending order or descending order) based on the columns that you specify to order by SELECT column1, SUM(column2) FROM "list-of-tables" ORDER BY "column-list" [ASC | DESC]; [ ] = optional ASC = Ascending Order - default DESC = Descending Order

Back

AVG-Aggregate fxn

Front

Returns the avg value of a given column

Back

Constraint (SQL)

Front

Rule associated with a column that the data entered into that column must follow.

Back

Describe a Right Join

Front

(or Right Outer Join) Returns all rows from the right table, and the matched rows from the left table. This is the exact opposite of a LEFT JOIN; i.e., the results will contain all records from the right table, even if the JOIN condition doesn't find any matching records in the left table. This means that if the ON clause doesn't match any records in the left table, the JOIN will still return a row in the result for that record in the right table, but with NULL in each column from the left table.

Back

DROP TABLE

Front

used to delete a table and all rows in the table drop table "tablename"

Back

GROUP BY clause (def, syntax)

Front

Will gather all of the rows together that contain data in the specified column(s) and allow aggregate functions to be performed on the one or more col's Syntax: SELECT column1, SUM(column2) FROM "list-of-tables" GROUP BY "column-list";

Back

MAX -Aggregate fxn

Front

Returns the largest value in a given column

Back

Data type: varchar(size)

Front

Variable-length character string. Max size is specified in parenthesis.

Back

SQRT(x)

Front

Returns the square-root value of x

Back

What is the difference between UNION and UNION ALL?

Front

The difference between UNION and UNION ALL is that UNION will omit duplicate records whereas UNION ALL will include duplicate records.

Back

CEILING(x), CEIL(x)

Front

Returns the smallest integer value that is greater than or equal to x

Back

CREATE TABLE statement with constraints

Front

create table "tablename" ("column1" "data type" [constraint], "column2" "data type" [constraint], "column3" "data type" [constraint]); [ ] = optional

Back

Describe an Inner Join

Front

simple joins Returns all rows for which there is at least one match in BOTH tables. This is the default type of join if no specific JOIN type is specified. i.e. If you have a Table A and a Table B SELECT * FROM TableA INNER JOIN TableB ON TableA."col" = TableB."col" produces only the set of records that match in both A and B

Back

Mathematical Functions

Front

Includes: +, -, *, /, %

Back

Describe a Left Join

Front

(or Left Outer Join) Returns all rows from the left table, and the matched rows from the right table; i.e., the results will contain all records from the left table, even if the JOIN condition doesn't find any matching records in the right table. This means that if the ON clause doesn't match any records in the right table, the JOIN will still return a row in the result for that record in the left table, but with NULL in each column from the right table. Ex: Produces a complete set of recors from Table A, with the matching records (where available) in Table B. If no match, right side will contain null.

Back

MOD(x,y)

Front

Modulo- returns the integer remainder of x divided by y (same as x%y)

Back

COUNT-Aggregate fxn

Front

Returns the total number of values in a given column

Back

Data type: char(size)

Front

Fixed-length character string. Size specified in parentheses. Max 255 bytes.

Back

TABLE JOIN

Front

SELECT "list-of-columns" FROM table1,table2 WHERE "search-condition(s)"

Back

Data type: number(size)

Front

Number value with a max number of column digits specified in parenthesis.

Back

What are the different types of JOIN clauses supported by the ANSI- standard?

Front

(1) Inner Join (2) Left Join (3) Right Join (4) Full Join (5) Cross Join

Back

ROUND(x,d)

Front

Returns the value of x rounded to the number of decimal places specified by the value d

Back

Conditional selections used in the where clause for SELECT

Front

= Equal > Greater than < Less than >= Greater than or equal <= Less than or equal <> Not equal to LIKE *See note below

Back