Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

Structured Query Language

memorize.ai (lvl 286)

(0)

43 cards

()

Section 1

Preview this deck

MONTHS_BETWEEN

Front

1 / 43

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

Section 1

(43 cards)

MONTHS_BETWEEN

Front

Total number of months between two dates MONTHS_BETWEEN(sysdate, registration_date) how long ago did this student register? *Divide function by 12 to get years

Back

Scalar Subqueries

Front

Also called a single-row subquery returns a single column with one row.

Back

SYSDATE

Front

The SYSDATE function returns the computer operating system's current date and time and does not take any parameters.

Back

TO_DATE

Front

Convert string to date (helpful with WHERE clauses, etc) TO_DATE('01-27-2007', fm'MM-DD-YYYY')

Back

Subquery

Front

A Subquery or Inner query or a Nested query is a query within another SQL query and embedded within the WHERE clause. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved SELECT column_name [, column_name ] FROM table1 [, table2 ] WHERE column_name OPERATOR (SELECT column_name [, column_name ] FROM table1 [, table2 ] [WHERE])

Back

CAST

Front

The CAST function converts from one date type to another. CAST(expression AS data type) CAST('29-MAR-09' AS DATE)

Back

UNION ALL

Front

Combine both tables and show all duplicates

Back

NOT EXISTS

Front

Same as EXISTS but where the rows do not exist. Tests for null values

Back

Basic Structure of a SELECT Statement

Front

SELECT <data elements> FROM <data source> WHERE <conditions (filters data before functions)> GROUP BY ... HAVING <aggregate conditions (happens after the aggregation)> ORDER BY <data elements>

Back

TO_CHAR

Front

Convert date to string TO_CHAR(registration_date, 'mm') = '01'

Back

JOIN ANSI Notation

Front

Connecting statement in ON clause and contains a JOIN clause SELECT ... FROM Instructor i JOIN zip z ON i.zip = z.zip

Back

DUAL Table

Front

Made up table in in the database that allows for user testing. SELECT DUMMY FROM DUAL OR SELECT * FROM DUAL

Back

NULL

Front

No data is found Functions: IS NULL or IS NOT NULL (cannot use =) Replace null values with standing values by: NVL(column holding null value(s), 'replacement value')

Back

MIN

Front

A SQL aggregate function that pulls the minimum value from rows

Back

MINUS

Front

Returns the difference between two sets SELECT Student_id FROM Student MINUS SELECT student_id FROM Enrollment *Shows what students have NOT enrolled

Back

=

Front

used for text or numeric literals

Back

TRUNC

Front

TRUNC( value, precision) TRUNC(222.34501, 2) Trunc cuts off all digits beyond two digits to the right of the decimal. The TRUNC function can also take a DATE data type as an input parameter which interprets the time as midnight (12:00:00 AM). The example below shows that TRUNC has the effect of including records no matter what the time, as long as the date is Feb 7, 2007 WHERE TRUNC (enroll_date) = TO_DATE('07-FEB-2007', 'DD-MON-YYYY') TRUNC(date) ignores the hours, minutes, and seconds on the DATE data type.

Back

All Aggregate functions

Front

COUNT, SUM, AVG, MIN, MAX

Back

Coalesce

Front

The coalesce function is similar to the NVL function but instead of specifying one substitution expression for a null value, you an evaluate multiple substitution columns or substitution expressions. COALESCE(input_expression, substitution_expression_1, substitution_expression_n)

Back

INSERT

Front

Creates new data in a table. Can insert a single row or multiple rows (using a subquery) INSERT into table VALUES ... Each value must correspond with the matching columns and their data types. Values are separated by commas

Back

OUTER JOIN Oracle Notation

Front

Includes all values including NULL FROM Instructor i ,zip z WHERE i.zip = z.zip(+) + side is optional

Back

AVG

Front

A SQL aggregate function that outputs the mean average for a specified column or expression. *Does not include null values. Use NVL to include them

Back

Concatenation

Front

Connects string together to become one. Strings can be concatenated to produce a single column in the result set SELECT city || ', '|| state || ' ' || zip

Back

SUBSTR Function

Front

SUBSTR (char1, starting_position , substring_length) The third parameter is optional and if not included the function will run until the end of the string.

Back

Third Normal Form

Front

First Normal Form: all repeating groups must be removed and placed in a new table Second Normal Form: all nonkey columns must depend on the entire primary key. Only applies to tables that have composite primary keys Third Normal Form: every nonkey column must be a fact about the primary key

Back

MAX

Front

A SQL aggregate function that pulls the maximum value from rows

Back

UNION

Front

Combines two tables where data types are consistent (selects distinct values) SELECT Student_id FROM Student UNION SELECT student_id FROM enrollment

Back

Cartesian Product

Front

Usually the result of a missing join condition or a method of expanding the data of 1 table by the number of rows in the second table. Indicates either that the WHERE clause has no joining conditions or that multiple rows from one table match multiple rows in another table (many-to-many relationship) Result is number of rows in first table * number of rows in second table Solved by using (number of tables - 1) JOIN statements

Back

ADD_MONTHS

Front

Total number of days between two dates ADD_MONTHS(sysdate, 2) adds 2 months to current date with exact number of days

Back

EXTRACT

Front

Pulls out part of the date based on specifications EXTRACT(month from registration_date)

Back

LIKE vs =

Front

Use only if you need wildcard % Use = with SUBSTR instead in any possible case, especially with known indexes

Back

HAVING vs WHERE

Front

The HAVING clause is tested after the GROUP BY. You can test the aggregated values with a HAVING clause. Show the total population of those continents with a total population of at least half a billion.

Back

IN

Front

Finds rows that are within the listed conditions Can be used with subselects or static lists WHERE description IN ('Intro to Unix' ,'Basics of Unix Admin' ,'Advanced Unix Admin') Same output can be done by using = (preferably not because OR carries over to other conditions (AND is ignored) use parentheses if OR statements are needed)

Back

EXISTS

Front

Returns all results that exist in specified conditions. Tests for null values

Back

JOIN Oracle Notation

Front

Connecting statement in WHERE clause SELECT ... FROM Instructor i , zip z WHERE i.zip = z.zip

Back

COUNT

Front

A SQL aggregate function that counts occurrences of data values/rows (not SUM) COUNT(*) - counts number of rows

Back

Time/Date Syntax

Front

MM - months MI - minutes Day - Monday, Tuesday, etc. DD - Day digit fm - removes padding on result HH:MI:SS - hours:minutes:seconds HH24 - 24 hour time am - am/pm time DDD - day of the year < before date (yesterday) > after date (tomorrow)

Back

OUTER JOIN ANSI Notation

Front

Includes all values including NULL FROM Instructor i LEFT OUTER JOIN zip z ON i.zip = z.zip (LEFT side is optional)

Back

Aggregate Functions

Front

Must use GROUP BY clause to perform successfully if selecting rows without aggregate functions Nesting aggregate functions must always use GROUP BY regardless of what columns are being selected

Back

UPDATE

Front

Manipulates existing data in a table (always refers to a single table) UPDATE table SET column = value [,....] WHERE condition

Back

INTERSECT

Front

Shows the common values between two tables (same thing as JOIN) The intersect operator determines the common values between two sets. - the middle section of a Venn diagram

Back

LIKE

Front

Performs pattern matching using wildcards % and _. % character denotes multiple characters _ denotes single characters Single quotation marks last_name LIKE '_o%'

Back

NOT IN

Front

Same as IN but where rows are not within the listed conditions

Back