Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

SQL

memorize.ai (lvl 286)

(0)

67 cards

()

Section 1

Preview this deck

Union

Front

1 / 67

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

Section 1

(50 cards)

Union

Front

combines and returns the result-set retrieved by two or more SELECT statements

Back

Entity

Front

a real-world object, either tangible or intangible, that can be easily identifiable

Back

Intersect

Front

combines the result-set fetched by the two SELECT statements

Back

CHECK

Front

SQL constraint that verifies that all values in a field satisfy a condition.

Back

ORDER BY

Front

used to sort the records based on some field(s)

Back

Cursor Open

Front

Open cursor to initialize the result set

Back

Cross Join

Front

a cartesian product (product of two sets) of the two tables included in the join

Back

Left Outer Join

Front

Retrieves all the records/rows from the left and the matched records/rows from the right table

Back

Many-to-Many

Front

used in cases when multiple instances on both sides are needed for defining a relationship

Back

Alias

Front

a temporary name assigned to the table or table column for the purpose of a particular SQL query

Back

SQL

Front

- Structured Query Language - used to communicate with a database

Back

Clustered Index

Front

Arranged in a special order to make retrieval of information faster with direct access to the information - Table can only have on

Back

Relationships

Front

Relations or links between entities that have something to do with each other

Back

Relational Database Management System (RDBMS)

Front

stores data in the form of a collection of tables and relations can be defined between the common fields of these tables

Back

Minus

Front

used to remove duplicates from the result-set obtained by the second SELECT query from the result-set obtained by the first SELECT query and then return the filtered results from the first

Back

Tabels

Front

organized collection of data stored in the form of rows and columns

Back

Join

Front

used to combine records (rows) from two or more tables based on a related column

Back

Unique Index

Front

- help maintain data integrity by ensuring that no two rows of data in a table have identical key values -

Back

Subquery

Front

a query within another query, also known as nested query or inner query

Back

One-to-One

Front

the relationship between two tables where each record in one table is associated with the maximum of one record in the other table

Back

HAVING

Front

used to filter records in combination with the GROUP BY clause

Back

Query

Front

a request for data from a database

Back

Cursor Declare

Front

DECLARE a cursor after any variable declaration. The cursor declaration must always be associated with a SELECT Statement

Back

Cursor

Front

a control structure that allows for traversal of records in a database

Back

One-to-Many

Front

a record in a table is associated with multiple records in the other table

Back

INDEX

Front

SQL Constraint that indexes a field providing faster retrieval of records

Back

DEFAULT

Front

SQL constraint that automatically assigns a default value if no value has been specified for a given field

Back

Records

Front

The rows in a table

Back

Right Outer Join

Front

Retrieves all the records/rows from the right and the matched records/rows from the left table

Back

NOT NULL

Front

SQL constraint that restricts null value from being inserted into a column

Back

Data Integrity

Front

- the assurance of accuracy and consistency of data over its entire life-cycle - defines integrity constraints to enforce business rules on the data

Back

Database Management System (DBMS)

Front

software responsible for the creation, retrieval, modification and management of a given database

Back

Non-Clustered Index

Front

- Non-clustered indexes are not copies of the table but a sorting of the columns you specify that "point" back to the data pages in the clustered index. - Add non-clustered indexes for queries that return smaller result sets - Can have multiple in a table

Back

View

Front

a virtual table based on the result-set of an SQL statement

Back

Inner Join

Front

Retrieves records that have matching values in both tables involved in the join

Back

Cursor Deallocate

Front

statement to delete the cursor definition and release the associated resources

Back

WHERE

Front

used to filter records that are necessary, based on specific conditions

Back

Full Outer Join

Front

Retrieves all the records where there is a match in either the left or right table

Back

Cursor Close

Front

statement to deactivate the cursor

Back

GROUP BY

Front

sed to group records with identical data and can be used in conjunction with some aggregation functions

Back

FOREIGN KEY

Front

single or collection of fields in a table that essentially refers to the PRIMARY KEY in another table

Back

Fields

Front

The columns in a table

Back

PRIMARY KEY (Pk)

Front

- Uniquely identifies each record in a table - Must contain UNIQUE values and NOT NULL constraint

Back

SQL Constraints

Front

rules concerning data in the table

Back

UNIQUE

Front

SQL constraint that ensures that all values in a column are different

Back

Cursor Fetch

Front

to retrieve and move to the next row in the result set.

Back

Self Join

Front

- a table is joined to itself based on some relation between its own column(s) - uses the INNER JOIN or LEFT JOIN clause

Back

Index

Front

a data structure that provides quick lookup of data in a column or columns of a table

Back

Database

Front

organized collection of data, stored and retrieved digitally

Back

Non-unique Index

Front

- not used to enforce constraints on the tables with which they are associated - used solely to improve query performance by maintaining a sorted order of data values that are used frequently

Back

Section 2

(17 cards)

Normalization

Front

- the way of organizing structured data in the database efficiently - includes creation of tables, establishing relationships between them, and defining rules for those relationships - used to eliminate or reduce redundancy in database tables

Back

Delete

Front

used to delete rows from a table.

Back

Third Normal Form (3NF)

Front

satisfies the conditions for second normal form and there is no transitive dependency between the non-prime attributes

Back

Aggregate Function

Front

performs operations on a collection of values to return a single scalar value - AVG() - Calculates the mean of a collection of values. - COUNT() - Counts the total number of records in a specific table or view. - MIN() - Calculates the minimum of a collection of values. - MAX() - Calculates the maximum of a collection of values. - SUM() - Calculates the sum of a collection of values. - FIRST() - Fetches the first element in a collection of values. - LAST() - Fetches the last element in a collection of values.

Back

Online Transaction Processing (OLTP)

Front

- a class of software applications capable of supporting transaction-oriented programs - ability to maintain concurrency - designed for a large number of users who conduct short transactions

Back

Distinct

Front

Used to return unique values in result set

Back

TRUNCATE

Front

used to delete all the rows from the table

Back

User Defined Functions

Front

are like functions in any other programming language that accept parameters, perform complex calculations, and return a value - Scalar function return a single scalar value - able-valued functions return a table as output

Back

First Normal Form (1NF)

Front

- every attribute in a given relation is a single-valued attribute - If violated, it can be resolved by separating attributes in to individual records for each value

Back

Denormalization

Front

normalized schema is converted into a schema which has redundant information

Back

Sclar Function

Front

returns a single value based on the input value - LEN() - Calculates the total length of the given field (column). - UCASE() - Converts a collection of string values to uppercase characters. - LCASE() - Converts a collection of string values to lowercase characters. - MID() - Extracts substrings from a collection of string values in a table. - CONCAT() - Concatenates two or more strings. RAND() - Generates a random collection of numbers of given length. - ROUND() - Calculates the round off integer value for a numeric field (or decimal point values). - NOW() - Returns the current data & time. - FORMAT() - Sets the format to display a collection of values.

Back

Online Analytical Processing

Front

- relatively low frequency of online transactions - used for data mining or maintaining aggregated, historical data

Back

Drop

Front

- used to remove an object from the database - If you drop a table, all the rows in the table is deleted and the table structure is removed from the database

Back

Pattern Matching

Front

- pattern search in data if you have no clue as to what that word should be - uses wildcards to match a string pattern, rather than writing the exact word

Back

Second Normal Form (2NF)

Front

first normal form and does not contain any partial dependency (it has no non-prime attribute that depends on any proper subset of any candidate key of the table)

Back

Create empty tables

Front

Creating empty tables with the same structure can be done smartly by fetching the records of one table into a new table using the INTO operator while fixing a WHERE clause to be false for all records

Back

Collation

Front

set of rules that determine how data is sorted and compared

Back