Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

Intro to SQL (Pt. I)

memorize.ai (lvl 286)

(0)

96 cards

()

Section 1

Preview this deck

Catalog

Front

1 / 96

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

7 years ago

Date created

Mar 1, 2020

Cards (96)

Section 1

(50 cards)

Catalog

Front

A set of schemas that constitute the description of a database

Back

TRUE or FALSE: Adding or changing a default value will enforce changes to new and existing records

Front

FALSE; Adding or changing a default value takes effect on new records ONLY

Back

DROP TABLE

Front

Deletes a table and its content (i.e., rows)

Back

What clauses can be used to set optional restrictions for data integrity controls?

Front

ON DELETE clause ON UPDATE clause (NOT supported by Oracle SQL)

Back

TRUE or FALSE: You can update a foreign key value in a child table as long as the updated value refers to a row in the parent table

Front

TRUE

Back

If p and s are not specified, what is the default precision of the NUMBER(p,s) data type?

Front

38 digits

Back

CREATE TABLE

Front

Defines a new table and its columns

Back

CASCADE

Front

If you delete a row in the parent table, associated rows in the child table will be deleted automatically

Back

TRUE or FALSE: You can only insert a row into a child table whose value that matches one of the primary key values in the parent table

Front

FALSE; You can only insert a row into a child table whose foreign key is either null OR a value that matches one of the primary key values in the parent table

Back

TRUE or FALSE: You can delete a row from a parent table if there is a row in the child table that refers to it

Front

FALSE; you can only do this if you have set up ON DELETE rules that define what happens to referencing rows in a child table when the referenced parent row is deleted

Back

What happens if fewer than n characters are entered for a CHAR(n) data type?

Front

Spaces are added to the right

Back

List the steps of Table creation for a Relational Database

Front

1. Identify data type for attributes 2. Identify columns that cannot be null 3. Identify columns that need to be unique 4. Identify primary/foreign key mates 5. Determine default values 6. Identify additional constraints on columns (domain specifications) 7. Create the table

Back

DML is used in what phase(s) of the development process?

Front

Implementation

Back

TRUE or FALSE: You can decrease the size of a number column

Front

FALSE; You can ONLY decrease the size of a number column if it does not have any data stored

Back

DML

Front

Data Manipulation Language; commands that maintain and query a database

Back

TRUE or FALSE: Parent table has to be created before the child table that references it

Front

TRUE

Back

ON DELETE

Front

Defines what happens to referencing rows in the child table in response to deletion of a referenced row in the parent table

Back

DATE

Front

Data column

Back

TRUE or FALSE: The ON UPDATE clause is an optional restriction for data integrity using Oracle SQL

Front

FALSE; ON UPDATE is not supported by Oracle SQL, which only supports ON DELETE

Back

A modified column's length cannot be than of existing data

Front

Less... largest width

Back

CHAR(n)

Front

Fixed-length character string, where n represents the column's length

Back

TRUE or FALSE: Drop column can reference only one column

Front

TRUE

Back

What is the main DDL SQL statement?

Front

CREATE

Back

DDL is used in what phase(s) of the development process?

Front

Physical Design and Maintenance

Back

SET NULL

Front

If you delete a row in the parent table, foreign key values of associated rows in the child table will be set to NULL automatically (this is only valid if foreign key allows null values)

Back

TRUNCATE TABLE

Front

Deletes content (i.e., rows) but the table itself remains

Back

TRUE or FALSE: You cannot update the foreign key in a referencing table to a different primary key value in the parent table

Front

FALSE; You can update the value as long as it is included as a primary key value in the referenced table

Back

CREATE SCHEMA

Front

Define a portion of the database owned by a particular user

Back

Which foreign key constraint for the ON DELETE clause is not supported by Oracle SQL

Front

SET DEFAULT

Back

TRUE or FALSE: You can reverse a CREATE statement

Front

TRUE

Back

What is the default size the CHAR(n) data type?

Front

1

Back

SET DEFAULT

Front

If you delete a row in the parent table, foreign key values of associated rows in the child table will be set to default value (must be defined) automatically

Back

SQL

Front

Structured Query Language

Back

What clause is used to set default restrictions for data integrity controls?

Front

REFERENCES clause

Back

DCL is used in what phase(s) of the development process?

Front

Maintenance and Implementation

Back

CREATE VIEW

Front

Defines a logical table from one or more tables or view

Back

Schema

Front

A structure that contains descriptions of objects created by a user (base tables, views, constraints, domains)

Back

How can you reverse CREATE statements?

Front

By using DROP (ALTER) statements

Back

DCL

Front

Data Control Language; commands that control a database, including administering privileges and committing data

Back

TRUE or FALSE: You can update any attribute other than primary key of a row in a parent table

Front

TRUE

Back

VARCHAR2(n)

Front

Variable-length character string, where n represents the column's maximum length

Back

When inserting a row of data into a table, single quotes must be used to enclose ____ data types

Front

Nonnumeric

Back

What is the default date format used by Oracle?

Front

MM-DD-YYYY

Back

Referential Integrity

Front

Rule that ensures that foreign key values of a child must match primary key values of its parent table in a 1:M relationship, or may be null

Back

TRUE or FALSE: You cannot update primary key value of a row in the parent table if there is a row in the child table that refers to it

Front

TRUE

Back

INTEGER

Front

Numeric column with no decimal part

Back

DDL

Front

Data Definition Language; commands that define a database, including creating, altering and dropping tables and establishing constraints

Back

NUMBER(p,s)

Front

Numeric column, where p indicates precision (total number of digits) and s indicates scale (number of digits to the right of the decimal point)

Back

ALTER TABLE

Front

Statement that makes changes to tables

Back

What is the default size the VARCHAR2(n) data type?

Front

There is none

Back

Section 2

(46 cards)

TRUE or FALSE: Identity columns can take on any data type

Front

FALSE; Identity columns must either be a number with scale zero (i.e., NUMBER(p,0)) or an integer

Back

How can you show a column name in your results table with lowercase letters?

Front

TRUE; if you don't want to see the column name in all caps, you must enclose a column alias in quotation marks (" ")

Back

When there is ____, inserting in to a table does not require explicit value entry or field list

Front

Identity column

Back

HAVING

Front

Specifies conditions for group selection

Back

ALIAS

Front

An alternative column or table name

Back

Every ____ statement returns a result table

Front

SELECT

Back

TRUE or FALSE: With aggregate functions you can't have a column that returns a set value in the SELECT clause

Front

TRUE; You can only do this if the results are included in the GROUP BY clause

Back

Oracle interprets the alphabet such that it sorts before

Front

Numbers ... Letters

Back

COUNT(column_name)

Front

Function that counts only rows that contain a value for the specified column. Null values are not counted.

Back

TRUE or FALSE: A column alias must use the AS keyword

Front

FALSE; A column alias can use the AS keyword, but this is optional

Back

Identity Column

Front

A numeric column whose value is generated automatically when a row is added to the table

Back

ORDER BY

Front

A SQL clause that is useful for ordering the output of a SELECT query (for example, in ascending or descending order).

Back

When using the SELECT statement, how do you identify a column that is stored in multiple table?

Front

Add a qualifier to indicate which table from which the data will come

Back

*

Front

Used to display all columns from all items in the FROM clause

Back

What constraints (if any) are automatically applied to Identity columns?

Front

NOT NULL and UNIQUE constraints

Back

In which clauses can you use a column alias?

Front

Following the SELECT and ORDER BY clauses

Back

FROM

Front

Identifies the table(s) or view(s) from which data will be obtained

Back

LIKE '%value'

Front

Matches all strings that have any number of characters preceding the word value

Back

MOD(m,n)

Front

SQL function that returns the remainder of n divided by m; if n is 0, returns n

Back

LIKE '_-value'

Front

Matches all strings that have a specific character before the string '-value'; the underscore wildcard specifies a single position in which any character can occur

Back

WHERE

Front

When used with the UPDATE clause, this identifies the rows that will be changed

Back

TRUE or FALSE: COUNT(*), MIN & MAX functions can only be used with numeric columns

Front

FALSE; COUNT(*), MIN and MAX can be used with any data type

Back

A null value means that a column is ; it does NOT Mean that the value is or .

Front

Missing a value... zero...blank

Back

List the three approaches you can when only inserting data in to SOME of the columns in a database

Front

1. Use two single quotes (without any blank space) to mean a null value should be stored for an empty column 2. Enter NULL (without single quotes) for empty column 3. Specify only those columns to which you are adding data (see image)

Back

DELETE

Front

Removes rows from a table

Back

COUNT(*)

Front

Function that counts all rows selected by a query, regardless of whether any of the rows contain null values

Back

DISTINCT Keyword

Front

Keyword that prevents duplicate (identical) rows from being included in the result set.

Back

TRUE or FALSE: A table alias can use the optional AS keyword

Front

FALSE; A table alias CANNOT use the AS keyword

Back

When removing record data from a table, can be rolled back but cannot.

Front

DELETE... TRUNCATE

Back

TRUE or FALSE: Multiple identity columns can be defined for a table

Front

FALSE; Only one identity table can be defined for a table

Back

What clause is used to add an Identity Column to a table?

Front

ColumnTable [INTEGER(n) | NUMBER(p,0)] GENERATED ALWAYS AS IDENTITY (... ...),

Back

GROUP BY

Front

Categorizes rows in an intermediate result table to groups

Back

NEXT_DAY

Front

Returns the date of the first weekday named by CHAR that is later than the date (DATE). In the attached example, it asks for the next Tuesday after Feb. 2, 2001, which is a Friday.

Back

SELECT Statement

Front

The SELECT statement is used to select data from a database. Syntax: SELECT column_name, column_name FROM table_name; SELECT * FROM table_name;

Back

UPDATE

Front

Modifies data in existing rows

Back

TRUE or FALSE: The equal sign (=) cannot be used when searching for a NULL value

Front

TRUE; When searching for a null value, you must use 'IS' instead.

Back

What are the aggregate functions?

Front

COUNT, MIN, MAX, SUM, AVG

Back

LIKE

Front

Used in the WHERE clause to specify conditions when an exact match isn't possible

Back

You can use ____ to manipulate the chosen rows of data from the table

Front

Stored Functions

Back

When using expressions in a SELECT statement, what are the precedence rules for order of evaluations (operations)?

Front

1. Expressions in parentheses 2. Multiplication / division (from left to right) 3. Addition / subtraction (from left to right)

Back

TRUE or FALSE: When modifying row data, you can add multiple columns names to the SET clause to change values in more than one column

Front

TRUE

Back

INSERT INTO

Front

Inserts new data into a database

Back

COUNT(DISTINCT column_name)

Front

Function that counts only rows that contain a different value for the specified column. Null values are ignored.

Back

SET

Front

When used with the UPDATE clause, this identifies column to be changed and the value which it will take

Back

What order are SELECT statements processed in?

Front

1. FROM - Identify involved tables 2. WHERE - Find rows meeting stated conditions 3. SELECT - Identify columns to be projected

Back

MONTHS_BETWEEN

Front

Returns the number of months between two dates. If the first date is later than the second date, the result is positive and vice versa.

Back

Intro to SQL (Pt. I)

Preview this deck

Catalog

0.0

Cards (96)

Catalog

A set of schemas that constitute the description of a database

TRUE or FALSE: Adding or changing a default value will enforce changes to new and existing records

FALSE; Adding or changing a default value takes effect on new records ONLY

DROP TABLE

Deletes a table and its content (i.e., rows)

What clauses can be used to set optional restrictions for data integrity controls?

ON DELETE clause ON UPDATE clause (NOT supported by Oracle SQL)

TRUE or FALSE: You can update a foreign key value in a child table as long as the updated value refers to a row in the parent table

TRUE

If p and s are not specified, what is the default precision of the NUMBER(p,s) data type?

38 digits

CREATE TABLE

Defines a new table and its columns

CASCADE

If you delete a row in the parent table, associated rows in the child table will be deleted automatically

TRUE or FALSE: You can only insert a row into a child table whose value that matches one of the primary key values in the parent table

FALSE; You can only insert a row into a child table whose foreign key is either null OR a value that matches one of the primary key values in the parent table

TRUE or FALSE: You can delete a row from a parent table if there is a row in the child table that refers to it

FALSE; you can only do this if you have set up ON DELETE rules that define what happens to referencing rows in a child table when the referenced parent row is deleted

What happens if fewer than n characters are entered for a CHAR(n) data type?

Spaces are added to the right

List the steps of Table creation for a Relational Database

1. Identify data type for attributes 2. Identify columns that cannot be null 3. Identify columns that need to be unique 4. Identify primary/foreign key mates 5. Determine default values 6. Identify additional constraints on columns (domain specifications) 7. Create the table

DML is used in what phase(s) of the development process?

Implementation

TRUE or FALSE: You can decrease the size of a number column

FALSE; You can ONLY decrease the size of a number column if it does not have any data stored

DML

Data Manipulation Language; commands that maintain and query a database

TRUE or FALSE: Parent table has to be created before the child table that references it

TRUE

ON DELETE

Defines what happens to referencing rows in the child table in response to deletion of a referenced row in the parent table

DATE

Data column

TRUE or FALSE: The ON UPDATE clause is an optional restriction for data integrity using Oracle SQL

FALSE; ON UPDATE is not supported by Oracle SQL, which only supports ON DELETE

A modified column's length cannot be ____ than ____ of existing data

Less... largest width

CHAR(n)

Fixed-length character string, where n represents the column's length

TRUE or FALSE: Drop column can reference only one column

TRUE

What is the main DDL SQL statement?

CREATE

DDL is used in what phase(s) of the development process?

Physical Design and Maintenance

SET NULL

If you delete a row in the parent table, foreign key values of associated rows in the child table will be set to NULL automatically (this is only valid if foreign key allows null values)

TRUNCATE TABLE

Deletes content (i.e., rows) but the table itself remains

TRUE or FALSE: You cannot update the foreign key in a referencing table to a different primary key value in the parent table

FALSE; You can update the value as long as it is included as a primary key value in the referenced table

CREATE SCHEMA

Define a portion of the database owned by a particular user

Which foreign key constraint for the ON DELETE clause is not supported by Oracle SQL

SET DEFAULT

TRUE or FALSE: You can reverse a CREATE statement

TRUE

What is the default size the CHAR(n) data type?

1

SET DEFAULT

If you delete a row in the parent table, foreign key values of associated rows in the child table will be set to default value (must be defined) automatically

SQL

Structured Query Language

What clause is used to set default restrictions for data integrity controls?

REFERENCES clause

DCL is used in what phase(s) of the development process?

Maintenance and Implementation

CREATE VIEW

Defines a logical table from one or more tables or view

Schema

A structure that contains descriptions of objects created by a user (base tables, views, constraints, domains)

How can you reverse CREATE statements?

A modified column's length cannot be than of existing data

Oracle interprets the alphabet such that it sorts before

A null value means that a column is ; it does NOT Mean that the value is or .