ISDS Database Exam Prep - SQL and DB Design

T/F When designing a database and a many to many relationship is formed between two tables, an accompanying third table MUST be created.

True

T/F When using the attribute "SELECT" , you select what you which table you want to use.

True

T/F When using the attribute Update, it adds data to an existing table.

True

T/F When writing query statements it is required that you use the matching camel casing used in the database.

True

T/F: A Not Null Status indicates that the field must be entered?

True

T/F: A recursive relationship can be N:M.

True

T/F: If a primary key does not meet the criteria for an ideal primary key, a DBMS-generated surrogate key should be used

True

T/F: Recursive relationships adhere to the same rules as binary relationships

True

T/F: SQL has built-in functions

True

T/F: SQL is not a standard programming language but is a data sub-language

True

T/F: The condition in WHERE clauses can refer to a set of values by using the IN operator

True

T/F: There are situations where denormalized relations are preferred

True

T/F: When using a DELETE function the table structure remains intact.

True

T/F: When using a DROP TABLE function the table structure does not remain intact

True

T/F: You can identify a Primary Key or Foreign Key by using the CONSTRAINT function

True

T/F? The order of the column names after the keyword SELECT determines the order of the columns in the resulting table.

True

TRUE or FALSE: A VarChar is a character data type that holds a variable-length amount of characters.

True

The main purpose of a resource lock is to maintain database integrity by ensuring all transactions adhere to ACID. True or False?

True

The maximum cardinality determines how a relationship is represented. (True or False)

True

The order of the COLUMNS in your results follows the order you listed them following your SELECT keyword. True/False?

True

There are two type of subtype (exclusive and inclusive)

True

To end a query statement, a semi colon is added at the end of the statement? (T/F)?

True

True or False: Each database design must be created for a particular DBMS product?

True

True or False: If ID-Dependent, then you must add a primary key of the parent entity?

True

True or False: Most DBMSs do not allow updating a view.

True

True or False: The 'Parse' button in SQL is used to determine if there are any errors in the statement.

True

True or False: When joining tables, the words inner and outer are optional.

True

True or False: a Phone Number is an example of a Text Data Type?

True

True or False? You cannot use a WHERE clause with SQL built-in functions.

True

True or false. If there are a maximum of 100 characters for VarChar, only the number of characters inputted will be stored.

True

Use left join when you only want to keep the unmatched record from the left table

True

When a lengthy text field is present we should consider using surrogate key. True/False

True

You can have an Id-dependent entity to be Id-dependent on another Id-dependent entity

True

(T/F) using the "create table" commands a primary key can be defined in two places

True, it can be created with the column constraints or the table constraints

(T/F) The following statements will DROP Table A and Display contents of Table B Drop TABLE A Drop TABLE B Select * FROM TABLE A Select * FROM TABLE B

True, it will drop one table but will not complete the second drop statement.

T/F: Recursive relationships adhere to the same rules as binary relationships

True, they can be 1:1, 1:N, and N:M

A DROP operation cannot be rolled back.

True.

You need to include the word VALUES when inserting new data to a table. (T/F)

True.

To change the data values in an existing row (or set of rows) use the ______ statement.

UPDATE

What is the proper syntax for updating a value?

UPDATE (attribute) SET (attribute) = value 2 WHERE attribute = value 1

To use the UPDATE function in SQL, The statement starts by:

UPDATE Table_Name SET Column=Value2 WHERE Column=Value1;

To change the data values in an existing row (or set of rows) use which statement?

UPDATE.

How do you change data values in existing rows for tables?

Use the Update Statement

What are Aggregate Functions?

Used in SQL to group together multiple rows for a given column to form a single value. Examples of aggregate functions used include COUNT, SUM, MIN, MAX, and AVG.

What is Data Definition Language (DDL)?

Used to change the schema of a database by creating tables, relationships, and other structures.

Define intersection table

Used when creating an N:M relationship. Has a composite key consisting of the keys of each of the tables it connects. Has no attributes besides the primary key.

How do you sort in descending order in SQL?

Using ORDER BY ColumnName DESC (DESC comes after the column name)

Denormalization can introduce modification anomalies, what are some preventative measures we can use to reduce them when creating/modifying our database?

Using database constraints or implementing validity checks.

What does UNIQUE constraint mean?

Values of a column must not use repeated values

Which takes longer to process, VarChar or Char?

VarChar

Why would you use Char instead of VarChar?

VarChar requires extra processing, so is less efficient than Char, particularly when each record is likely to have values of similar length.

Is varchar faster or slower than char?

Varchar is 50% slower

What kind of diagram is the standard method of visualizing sets of data and their overlap when considering the effect of joins?

Venn diagram

What is an intersection table with additional attributes called?

What is an intersection table with additional attributes called?

What is it called when an intersection table has columns beyond those in the primary key?

When an intersection table has columns beyond those in the primary key, the relationship is called an association relationship.

What is an association relationship?

When an intersection table has columns beyond those in the primary key. Every N:M requires an intersection table

What is deadlock?

When each user is awaiting a resource that the other person has locked, preventing the process from proceeding.

What do the IN and NOT keywords do?

When entering a query, the IN and NOT IN as a phrase will help filter your results. For example SELECT FirstName FROM EMPLOYEE WHERE Department IN ('Accounting'); This will show the first names of all employees registered in the Accounting department. SELECT FirstName FROM EMPLOYEE WHERE Department NOT IN ('Accounting'); This will show the first names of all employees NOT registered in the Accounting department.

When is a relation considered normalized

When every determinant is a candidate key

What is denormalization?

When normalization is taken too far we join back the attributes that were split.

When would you use a char over varchar?

When the data stored is always a set length

When are data constraints usually specified?

When the table is actually created in the database

When do we use the GROUP BY statement?

When we want to group a row by common values

When do you use GROUP BY clause?

When you use a combination of "aggregation" attributes and numeric functions.

When is the GROUP BY clause used?

With aggregate functions

What is the default format for Date in SQL?

YYYY-MM-DD

Can Identity columns be setup in Oracle?

Yes, but they cannot auto increment on INSERT lines. Special incrementation functions must be used.

Can ORDER BY clause sorts data by multiple columns?

Yes, it can.

Can you apply more than one condition for the WHERE clause statement?

Yes, you can create multiple conditions using the AND keyword.

The WHERE clause may include the IN keyword to specify that.....

a particular column value must be included in a list of values.

What needs to be entered at the end of every one of your SQL queries?

a semicolon

An intersection table containing additional attributes is called _________. a. Sub-query Table b. Association Table c. N:M Table d. Foreign Key Table

a. Association Table

Which SQL command allows you to group a set of records for aggregation? Select one: a. GROUP BY b. SORT BY c. CLUSTER BY d. ORDER BY

a. GROUP BY

When was SQL developed?

a. Originally developed by IBM as the SEQUEL language in the 1970s

What is the main function of DISTINCT keyword?

added to the SELECT statement to suppress the display of duplicate rows

The GROUP BY clause is often used with _________

aggregate functions (e.g. SUM, AVG)

What are some synonyms for an intersection table?

association table, bridge table, cross-reference table, crosswalk, intermediary table, join table, junction table, link table, linking table, many-to-many resolver, map table, mapping table, pairing table, or transition table

This button in SQL Server Management Studio will check the validity of the syntax of your SQL query, but only the syntax. a) Execute b) Parse c) Validate d) Run

b) Parse

Statements which are used for creating tables, relationships, and other structures. a. DML (Data Manipulation Language) b. DDL (Data Definition Language) c. DCL (Data Control Language) d. TCL (Transaction Control Language)

b. DDL (Data Definition Language)

Which statement permanently removes a database? a. Delete b. Drop c. Remove d. Obliterate

b. Drop

What data type should ZIPCODE use? Assume that you are only storing 5 digit zipcode (92645). a. Char(5) b. VarChar(5) c. Int(5) d. Double(5)

b. VarChar(5)

Which SQL keyword is used to calculate the subtotals? Select one: a. WHERE b. GROUP BY c. ORDER BY d. SELECT e. FROM

b.GROUP BY

Purpose of "Parse" button.

blue checkmark on toolbar; CTRL + F5; checks for errors in command without running data; "Parse" first in order to check for errors

The identifier for the supertype becomes which of the following for the subtype? a. primary key b. foreign key c. both primary and foreign key d. none of the above

c

Which of the following is not a valid SQL keyword? a) BETWEEN b) ORDER BY c) ENABLE d) DISTINCT

c) ENABLE

What is the proper format for inserting a comment? a. //* comment //* b. */* comment */* c. /* comment */ d. // comment //

c. /* comment */

In SQL server, keywords show up in different colors. SQL commands show up as: a. Pink b. Green c. Blue d. Gray

c. Blue

Which of one of these is a SQL data definition statement? a. Insert b. Delete c. Create d. Update

c. Create

SELECT Correct answer(s): Which of these not SQL data definition language? a. Delete b. Merge c. Create d. Update

c.Delete, Merge, and update

SQL SELECT clause specifies which ___ are to be listed in the query results.

columns

What are DDL major statement

create, alter, drop, select

_____________ are like skyscraper window washers traveling up and down the skyscraper to stop at each window. a) Indexes b) Locks c) Transactions d) Cursors

d) Cursors

Which SQL command allows you to select data from multiple tables? Select one: a. RUN ... FROM b. GROUP BY c. CONNECT d. JOIN ... ON

d. JOIN ... ON

Which SQL command sorts the results of a query? Select one: a. SORT IN (optionally using ASC or DESC) b. SORT BY (optionally using NUM or TEXT) c. ORDER IN (optionally using NUM or TEXT) d. ORDER BY (optionally using ASC or DESC)

d. ORDER BY (optionally using ASC or DESC)

SELECT * means: a. show all column values for rows that do not match specific criteria b. delete all column values for rows that match specific criteria c. delete all row values for rows that match specific criteria d. show all column values for rows that match specific criteria

d. show all column values for rows that match specific criteria

What does the DROP function do?

deletes the table and data within the table permanently

What prevents the SQL DROP TABLE command from dropping the table's structure?

does not work if table contains or could contain values needed to fulfill referential integrity constraint

The DISTINCT keyword may be added to the SELECT statement to......

inhibit duplicate rows from displaying.

What does SQL AS keyword do?

it assigns new names to columns in query

What does the "Parse" button (checkmark button) in SQL Server Management Studio do?

it determines if there are syntax errors before executing the query.

Most database management systems provide a mechanism to record activities into a/an _____________________

log file

What is intersection table or associative entity?

new table with primary key is comprised from primary keys of 2 parents

What is the difference between a varchar and nvarchar data types?

nvarchar stores data in Unicode format allow values for non-latin text like Asian languages, accent marks, and other non ASCII characters. varchar only allows ASCII (basic latin characters).

4 minimum cardinalities?

parent optional and child optional (O-O), parent mandatory and child optional (M-O), parent optional and child mandatory (O-M), or parent mandatory and child mandatory (M-M).

OUTER JOIN

record included? all additional, even if no matching found!

INNER JOIN

record included? only if match is found!

SQL WHERE clause specifies which ___ are to be listed in the query results.

rows

SQL FROM clause specifies which ___ are to be used in the query.

tables

What would be the ideal primary key?

the ideal primary key is short, numeric, and fixed.

Denormalization

the process of intentionally designing a relation that is not normalized. Done to improve performance or security.

VARCHAR(n)

variable-length character string having a maximum length of (n)

What is a SQL View?

•It is a virtual table created by DMBS-stored SELECT statement that can combine access to data in multiple tables and even in other views. •We can run a query against a view in the same way that you run a query against a table

What single character can we add to the following statement to make it functional: SELECT OwnerLastName, PetType FROM PET JOIN PET_OWNER On PET.OwnerID=PET_OWNER.OwnerID WHERE PET.PetType Like 'Do_'

"This statement is missing a semicolon(;) after the 'Do_'" The semicolon is optional.

What is the wildcard character that represents characters of text?

%

What are the two wild cards used with LIKE and how are they different?

% is a wildcard for multiple characters and _ is a wildcard for a single character

What is the symbol associated with SQL LIKE wildcard characters?

%(Percent for multiple characters) and _ (Underscore for single character)

Correlated Subquery

(INNER run many times!) **think empSalary, --> to compare AVG based on department.**

Non-correlated Subquery

(INNER run only once!) **think deptID, --> 'Account%' to generate list of employees who work in a department that begins with 'Account'.**

(T/F)In sql 'Outer' always needs to be specified

(T/F)In sql 'Outer' always needs to be specified

What does the "*" symbol mean after a SELECT statement?

* is a variable usually placed after the SELECT statement and is short for "All Columns"

How do you use the DROP/DELETE syntax?

*DELETE = getting rid of row DELETE FROM table_name WHERE condition; *DROP = getting rid of a table DROP TABLE table_name;

How are weak entities represented?

-If not ID-dependent, use same techniques as for strong entities -If ID-dependent, then must add primary key of the parent entity

What must be considered for each entity and attribute?

-Specify primary key -Specify properties for each column Data Type Null status Default value Specify data constraints

What do the basic forms of SQL entail?

-The basic form of SQL queries uses the SQL SELECT/FROM/WHERE framework. In this framework: 1. The SQL SELECT clause specifies which columns are to be listed in the query results 2. SQL FROM clause specifies which tables are to be used . 3. SQL WHERE clause specifies which rows to be listed in query. SQL comments are enclosed in symbols /* and */ . -SQL has a shorthand notation for querying all columns. It is the SQL asterisk (*) wildcard character.

What is the difference between /* */ and -- ?

/* */ is for a comment that has multiple lines while comment with -- can only has one line

What is the format for an SQL comment?

/*TEXT*/

SQL statements are divided into five categories, what are they?

1- Data Definition Language (DDL) : statements used for creating tables, relationships and other structures. 2- Data Manipulation Language (DML) : statements used for querying, inserting modifying and deleting. 3- SQL/ Presistent Stored Modules (SQL/PSM): statements, which extend SQL by adding procedural programming capabilities. 4- Transaction Control Language (TCL) : statements used to mark transaction boundaries. 5- Data Control Language (DCL): statements used to grant database permissions to users and groups.

Why is it better to define primary keys using constraints?

1- You can use constraint to define composite keys. 2- You can choose the name of the constraint that defines the primary key, controlling the name of the constraint has advantages for administrating the database. 3- It allows us to easily define surrogate keys.

Which keywords are used to place a primary key constraint when creating a table?

1. CONSTRAINT 2. TABLENAME_PK 3. PRIMARY KEY ({PrimaryKeyColumns})

The SQL data definition statements include:

1. CREATE 2. ALTER 3. DROP

What are most critical database administration functions?

1. Concurrency control 2. Security 3. Backup and recovery

What is process transitioning from relations to entities to tables

1. Create table for each entity 2. Specify primary key 3. Specify column properties (type, null status (null value allowed), default values if any, data constraints if any ) 4. Re-evaluate structure of table.

What column properties need to be considered when creating a new table?

1. Data type 2. Null status 3. Default value 4. Data constraints should be specified

There are 4 Data Constraints, please explain and give an example

1. Domain Constraint: Specify a set of values that a column may have. Ex.: a domain of day-of-week (Monday, Tuesday,... Sunday) 2. Range Constraint: Specify an interval of allowed values. Ex.: The age of the employees must be greater than 18 and less than 65 3. Intrarelation Constraint: Involve comparison among columns in the same table Ex.: The review date would be at least three months after hire date 4. Interrelation constraint: Involve comparison among columns in different tables (known as Inferential Integrity) Ex.: The workers assignment date in specific project should be later than the project start date.

What are match criteria, and multiple match criteria used in the WHERE clause?

1. Equals " = " 2. Not Equals " < > " 3. Greater than " > " 4. Less than " < " 5. Great than or equal to " > = " 6. Less than or equal to " < = "

Two Disadvantages of Surrogate Key

1. Foreign keys that are based on surrogate keys have no meaning to the users. 2. When data is shared among different databases, it is possible for two different rows, in two different databases, to have the same value of surrogate key.

Specify logic for enforcing minimum cardinality:

1. O-O relationship (No action to apply) 2. M-O relationship (easily enforced by define referential integrity constraint and make FK not null 3. O-M relationship (difficult to enforce. Requires use of triggers or other application code) 4. M-M relationship (very difficult to enforce. Triggers can lock each other out and create many problem)

List four uses of ID-dependent entities

1. Representing N:M relationships 2. Representing association relationships 3. Storing multivalued attributes 4. Representing archetype/instance relationships

Name four uses for triggers

1. Setting default values 2. Enforcing interrelation data constraints 3. Updating views 4. Enforcing referential integrity actions

Describe two disadvantages of surrogate keys?

1. The Values are no meaning to users 2. Need to set up the starting values in different databases to avoid duplicate surrogate key values. However, this is not a problem until we merged data from different databases.

What are the options for setting up primary keys for ID-dependent entities?

1. Use a composite of foreign keys of associated tables if unique 2. Use a composite of foreign keys and a qualifying column 3. Create a new simple primary key

What are the rules of representing recursive relationships?

1:1 and 1:M relationships are saved using foreign keys. M:N relationships are saved by creating an intersecting relation.

ID-Dependent relationships are always?

1:N

What relationship is considered a "parent and child" relationship?

1:N Relationship

How many character is char limited to?

255

What is a different way to express the BETWEEN operator?

<= AND >=

what is the symbol representing not equal?

<>

Which keyword is not used to delete records? A. ALTER B. INSERT C. DROP D. DELETE

A

Which should be used if you want to remove a whole table? a) DROP b) DELETE c) Both a and b d) Neither

A - DROP

In SQL Management Studio, which button will allow you run the program. a) Execute b) Parse c) Both a and b d) Neither

A - Execute

Each attribute of an entity becomes___of a table. A column B primary key C foreign key D Surrogate Key

A Column

What is a GROUP BY clause?

A GROUP BY clause can be used to retrieve categorized results. It uses other functions (such as COUNT) and groups results into columns.

What is SQL view?

A SQL View is a virtual table created by a DBMS-stored SELECT statement which can combine access to data in multiple tables and even in other views.

Which SQL keyword is used to change one or more rows in a table? A UPDATE B INSERT C SELECT D DELETE

A UPDATE

______ is used to represent a single unspecified character in SQL query. a) __ b) % c) * d) #

A ___

What's the difference between a Non-Correlated and Correlated Sub-query?

A correlated query is one where the sub-query is dependent on the value of the parent query. Hence, it cannot be ran independently. A sub-query that does not reference the objects in the parent query is called a non-correlated sub-query.

What type of language is SQL?

A data sub-language.

When do you use a GROUP BY clause?

A group by clause will gather all the rows together that has data contained in a specified column and allows aggregate function to be performed on one or more columns.

What is a data sublanguage?

A language for defining and processing a database intended to be embedded in programs witten in another language.

In an ER diagram, what does "N:M" stand for?

A many to many relationship

What is a recursive relationship

A recursive relationship is a relationship between a relation and itself.

What is a recursive relationship?

A recursive relationship occurs when a table is related to itself

Database design

A set of database specifications that can actually be implemented as a database in a DBMS.

What is Entity type?

A set of entities that have the same characteristics.

What is database design?

A set of specifications that can actually be implemented as a database in a DBMS.

What is a stored procedure?

A stored procedure is a named SQL statement that is stored in the database management system that includes one or more queries, updates, inserts, deletes, or other SQL statements.

What is Data Manipulation Language (DML) ?

A sub-language of SQL. Statements used to query, insert, modify, and delete data.

Define subtype

A subtype is an entity that contains the specialized, noncommon attributes from the several entities

When should you use a Surrogate Key

A surrogate key is used when a table does not have a unique key, or when a unique key is too long, non-numeric and may change.

What is an intersection table?

A table in many-to-many relationship that has a composite key consisting of the keys from each of the tables that it connects.

What is a trigger?

A trigger is a stored program that is executed by the DBMS whenever a specific event occurs.

What is a default value?

A value that is assigned to an attribute if there is none assigned when a new row is created in a table.

How minimum cardinalities are reflected?

A) By having foreign keys listed as "NULL" B) By having foreign keys listed as "NOT NULL" C) By having foreign keys listed as "NOT NULL" or "NULL" D) By having foreign keys listed as "NOT NULL" and "NULL"

Each attribute of an entity becomes a(n) ________ of a table. A) column B) primary key C) foreign key D) alternate key

A) Column

In a 1:N relationship, the foreign key is always set up such that the primary key of the _________ entity becomes the foreign key in the _________ entity.

A) parent, child B) child, parent C) primary, secondary D) secondary, primary

Intersection tables are used when there are which types of relationships?

A. 1:1 B. 1:N C. M:N

Multiple matching criteria may be specified using: A. AND, OR B. WITH, OR C. AND, WITH D. Neither

A. AND, OR

Which format is correct when creating tables? A. Column name, Data Type, Column Constraints B. Column Constraint, Data Type, Column Name C. Data Type, Column Name, Column Constraint

A. Column name, Data Type, Column Constraints

Which of the following creates an ID variable starting at 10000 and increasing by 1? A. CustomerID Int NOT NULL IDENTITY(10000,1), B. CustomerID VarChar(10000) NOT NULL IDENTITY(1), C. CustomerID Int NOT NULL INCREMENT(10000,1), D. CustomerID Int NOT NULL INCREMENT(10000+1),

A. CustomerID Int NOT NULL IDENTITY(10000,1),

If you want to add a new constraint to a table, which is the SQL command you should use?

ALTER

Which SQL keyword is used to change the structure, properties or constraints of a table? A) CHANGE B) CREATE C) SELECT D) ALTER E) UPDATE

ALTER

Write the statement to add the field "Breed' to the table "DOG"

ALTER TABLE DOG ADD Breed Char(20) NULL;

What are logical operations?

AND, OR, NOT

What standard is SQL based on?

ANSI/ISO Standard

You can assign a column name to the result by using the

AS Keyword

What is the default sort order when using the ORDER BY clause?

ASC

What is ASP.Net?

ASP.NET , the follow up to microsoft Active sever pages is used in web pages to create web-based database applications.

What will be the result of the following statement: DELETE FROM MyTable; ?

All rows in the table will be deleted

Choose the correct Answer The SQL data manipulation statements include: 1. Insert 2. Update 3. Delete 4. All the Above

All the Above

Explain the meaning of the expression ACID transaction

An ACID transaction is one that is Atomic, Consistent, Isolated, and Durable

Define an INNER JOIN.

An INNER JOIN combines two tables where the join on column is the same

What is an SQL view?

An SQL view is a virtual table that is constructed from other tables or views

How does an association table differ from an intersection table?

An association table has additional attributes that pertain to the relationship itself

What is the difference between an entity and a table?

An entity is a database design concept while a table is the implementation of that entity in an actual database.

What is the difference between an INNER and OUTER JOIN?

An inner join display results with only rows that have matching results in the joined tables whereas an outer join includes all rows from one of the tables in the join regardless if it matches.

What is the difference between inner and outer joins?

An inner join only displays data from the rows that match based on join conditions. An outer join shows all rows of a table in the relation whether or not they match the join condition.

What is an intersection table?

An intersection table (associative entity) is a table that is created to implement a N:M relationship.

What is an intersection table?

An intersection table has a composite key consisting of the keys from each of the tables that it connects.

What is an intersection table?

An intersection table is a table that defines many to many relationships. It acts as an intersection between two tables.

The SQL keyword(s) ________ is used with wildcards. A. LIKE only B. IN only C. NOT IN only D. IN and NOT IN

Ans. Option A

Which of the following functions can be used on both numeric as well as non-numeric data? A - COUNT B - AVG C - STDDEV D - VARIANCE

Ans. Option A

The SQL WHERE clause: A. limits the column data that are returned. B. limits the row data are returned. C. Both A and B are correct. D. Neither A nor B are correct.

Ans. option B

Which of the following is not true about removing rows from a table? A - You can use a subquery in a DELETE statement. B - Specific rows are deleted based on the WHERE clause condition. C - A statement like, DELETE , would cause deletion of the table from the database. D - All of the above.

Ans. option C

Which one is the default function in SQl server? a. On DELETE CASCADE b. ON DELETE No action c. None of the above

Answer B

Which one is correct: A. Although primary keys can be used as a column constraint, we prefer to always use it as a table constraint B. CHECK constraint is used to define referential integrity constraints C. FOREIGN KEY constraint is used to define data constraints. D. If you do not specify the null status using NULL or NOT NULL, then NOT NULL is assumed.

Answer: A

Which of the following is not true about removing rows from a table? A - You can use a subquery in a DELETE statement. B - Specific rows are deleted based on the WHERE clause condition. C - A statement like, DELETE , would cause deletion of the table from the database. D - All of the above.

Answer: C

Which of the following is true when representing a 1:1 binary relationship using the relational model? A) The key of the entity with the highest minimum cardinality must be placed in the other entity as a foreign key. B) The key of each entity must be placed in the other as a foreign key. C) The key of either entity is placed in the other as a foreign key. D) The key of the entity with the most attributes must be placed in the other entity as a foreign key. E) Both entities must have the same primary key.

Answer: C

Which one is incorrect: A.The ON UPDATE action would need to be specified for nonsurrogate data keys B.ON UPDATE NO ACTION is the default. C.ON UPDATE NO ACTION indicates that updates to the primary key for a table that has present should be prohibited D.You should define parent tables before child tables and DROP a child before a parent

Answer: C

When inserting data, you can omit the column list on the situation that: A. Providing data for all of the columns B. Data is in the same order as the columns in the table C. Have no surrogate keys D. All of them

Answer: D

Which one is not a column constraint: A. PRIMARY KEY B. NOT NULL C. CHECK D. DEFAULT Edited: Which ones is not necessarily a column constraint ?

Answer: D; Edited: Correct Answer is A/C; DEFAULT is a column constraint that applies a default value to a column when a Insert statement does not explicitly assign a particular value; CHECK can be a column level constraint if it involves single column or it can be applied to multiple columns at the table level; Same with PRIMARY KEY can be applied as a column constraint but most of the times defined as a table constraint. NOT NULL is column constraint. Also question need to be modified as Which ones are not necessarily a column constraint ?

(T/F) Char should be used when the sizes of the data entries vary considerably.

Answer: False, Char should be used when fields always have text of same length.

In a N:M relationship of two tables, an_______________need to be created, the new table has composite key consisting of the_____________from the two tables, and the relationship is known as _______________________ .

Answers: Intersection table, primary keys, association relationship

When can you use the AS clause

Anytime you are introducing a new column or database to the SQL Query

When an intersection table has columns beyond those of the primary key, what is this relationship called?

Association Relationship

What is the different between association and intersection tables? When we use an intersection table?

Association tables have user data; but intersection table do not We use intersection table when there is N:M Relationship between strong entities. Intersection table only holds the primary keys of the two tables as foreign keys and this combination keys serves as the composite primary key of the intersection table itself.

What is another term for "Associative Table"

Associative Entity

In a relational database design, all relationships are expressed by creating a/an ________. A) primary key B) foreign key C) supertype D) alternate key

B

Which should be used if you want to have minimal storage space? a) VarChar b) Char c) Both a and b d) Neither

B - Char

To which of the following actions are referential integrity constraints not applied? A Update B Create C Insert D delete

B Create

An SQL virtual table is called a ____________. a) vtable b) view c) index d) triggers

B View

1. The SQL functions SUM, AVG, MIN, MAX, and COUNT, are called: a. arithmetic b. agregate c. compound d. mathematical

B, Agregate

SQL comments are enclosed in the following symbols: A. $* and *$ B. /* and */ C. ( and ) D. %* and *%

B. /* and */

What SQL operation is used to display data obtained from more than one table? A. Subquery B. JOIN C. GROUP BY D. IN

B. JOIN

What is a casual relationship? A. Two attributes that can be merged, despite having no true relationship e.g. CarCost and Income. B. When a primary key of one table is inserted into another, without formally declaring it a foreign key, to allow for missing data. C. When either primary key is equally likely to be a foreign key candidate. D. A physical and emotional relationship between two tables without necessarily demanding or expecting the extra commitments of a more formal Primary-Foreign key relationship.

B. When a primary key of one table is inserted into another, without formally declaring it a foreign key, to allow for missing data.

What keyword did SQL provide to allow users to specify a minimum and maximum value on one line?

BETWEEN

In SQL server, SQL commands are displayed in what color?

Blue

In SQL Server, keywords show up in different colors: SQL commands are ______ Advanced functions are ____ Comments are ___ Other keywords in ____

Blue, Pink, Green, Grey

How is 1:N relationships represented with foreign keys between strong entities?

By Placing the primary key of the parent in the child as a foreign key.

How would the user define surrogate key in the create table statement?

By using IDENTITY keyword

4) Which SQL keyword is used to impose restrictions on a table, data or relationship? A Set B Create C Constraint D Alter

C Constraint

Which of the following is not true about primary keys? A) Primary keys cannot be null. B) Primary keys must be unique. C) Primary keys must be a single attribute. D) Primary keys are used to represent relationships. E) Primary keys can be defined using an SQL CONSTRAINT phrase.

C) Primary keys must be a single attribute.

Which is not a SQL built-in function? A. MIN B. MAX C. DIV D. SUM

C. DIV

Which statement would delete the STUDENT table and all its data? A. DELETE FROM STUDENT B. DELETE FROM STUDENT; C. DROP TABLE STUDENT;

C. DROP TABLE STUDENT;

Which keyword is not used to modify data in SQL? A. UPDATE B. INSERT C. JOIN D. DELETE

C. JOIN

In a query such as: WHERE C.Name LIKE 'J[^oa]n'; Which answer below best describes the result? A. Jon B. Jan c. Jen D. Both A and B

C. Jen, The carrot symbol means you are searching for things not similar to what is in the bracket

Which of the following interfaces we did not use it to write SQL in class? A. Oracle Live SQL online B. SQL*Plus C. SQLite D. SQL Developer

C. SQLite

What would the following query do: SELECT S.Major, AVG(S.GPA) AS AvgGPA FROM Student S WHERE S.Major LIKE 'B%' GROUP BY S.Major HAVING AVG(S.GPA)>3.5; A. Show Majors with an average GPA above 3.5 B. Show Majors with an average GPA above 3.5 and the Major name ends in a B C. Show Majors with an average GPA above 3.5 and the Major name starts with a B D. Show Majors that start and end with a B and with an average GPA above 3.5 E. None of the above

C. Show Majors with an average GPA above 3.5 and the Major name starts with a B

What should you do when you want to delete a table from the database? A. Use the DELETE statement B. Use the ALTER statement C. Use the DROP statement D. Use the CREATE statement

C. Use the DROP statement

When is a default value inserted into a table? A. Only when a current value is deleted. B. When a current value is deleted, or a record is inserting with NULL for that attribute. C. When a record is inserted with NULL for that attribute. D. Never, the all values for the attribute must be the default value.

C. When a record is inserted with NULL for that attribute.

Which is used for a fixed length string, CHAR or VARCHAR?

CHAR

What is the difference between CHAR and VARCHAR?

CHAR is a fixed length VARCHAR is variable-length

What is the primary difference between CHAR and VARCHAR?

CHAR is a fixed length data type while VARCHAR is a variable length datatype.

3. The _________ Constraint is used to create restriction on values that are allowed to appear in a column?

CHECK Note: FOREIGN KEY constraint also creates restriction on values that are allowed to appear in a column. However, the difference is that CHECK constraint enforces domain integrity whereas FOREIGN KEY enforces referential integrity. The difference is how database determine values are valid and allow them - FOREIGN KEY constraints obtain the list of valid values from another table, and CHECK constraints determine the valid values from a logical expression.

Which keyword is used to impose restrictions on columns?

CONSTRAINT

Syntax of primary key constraint.

CONSTRAINT TABLENAME_PK PRIMARY KEY ({PrimaryKeyColumns})

What are the five aggregate functions in SQL for data retrieval?

COUNT, MIN, MAX, SUM, AVG

SQL Built-In functions are:

COUNT, SUM, AVG, MAX, MIN which are placed in SELECT

What are some built-in- number functions?

COUNT: Counts the number of rows that match the specified criteria. MIN: Finds the minimum value for a specific column for those rows matching the criteria. MAX: Finds the maximum value for a specific column for those rows matching the criteria. SUM: Calculates the sum for a specific column for those rows matching the criteria. AVG: Calculates the numerical average (mean) of a specific column for those rows matching the criteria. STDEV: Calculates the standard deviation of the values in a numeric column whose rows match the criteria.

What are the SQL statements used for data definition language?

CREATE - create database objects ALTER - modify the structure/characteristics of database objects DROP - delete database objects

How we can update the view?

CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition

What are the elements of SQL Data Definition Language (DDL)?

CREATE TABLE, ALTER TABLE, DROP TABLE, TRUNCATE TABLE

How would you write SQL code created a query

CREATE VIEW query_name AS SELECT table

What are the SQL data define statements?

CREATE, ALTER, DROP

AVG

Calculates the numerical average of a specific column for those rows matching the criteria

SUM

Calculates the sum for a specific column for those rows matching the criteria

What data type is this an example of 'MN'?

Char

What is the difference between char and varchar? And Which One is Faster?

Char is a fixed-length data type. The storage size of the char value is equal to the maximum size for this column. Because varchar is a variable-length data type, the storage size of the varchar value is the actual length of the data entered, not the maximum size for this column. Char runs faster than varchar.

What is the difference between Char and Varchar?

Char is of a fixed length while varchar is a variable length data type.

What is the difference between char and varchar?

Char stores character strings of a fixed length while varchar stores a variable number of characters.

What is the difference between char and varchar?

Char(n) is fixed-length character string of length n. VarChar(n) is variable length character with maximum string up to n.

Name any four data types used in SQL?

Char(n), Nchar(n), Varchar(n), Nvarchar(n), Date, Time, Integer, Decimal(m,n), Numeric(m,n)

What is the difference between Char and VarChar?

Char: Fixed length, Saves processing power. Used for fields where we always have text of same length. VarChar: Variable-length character data type, Saves storage space. Used for fields having vast difference of text length.

What is the difference between char and varchar?

Char: n-byte fixed length string data. Varchar: n-bye variable length string data. Extra processing is required for Varchar columns.

What kind of constraint is on a table where it limits the value in one column based off the values in another column in the same row?

Check Constraint

In 1 : N relationship, the entity that is on the many side of the relationship is called ________ entity.

Child

When defining attributes, what do the commas tell us?

Commas tell us that the attribute is done being defined, and we can now move on to defining the next one.

COUNT

Counts the number of rows that match the specified criteria

What is the first step of transitioning from a data model to a database?

Create a table for each entity.

What are the 3 steps to transform a data model into a database design?

Create table for each entity, Place foreign Keys, Specify logic for minimum cardinality.

Which SQL keyword is used to change the structure, properties or constraints of a table? A) DROP B) CREATE C) DISPOSE D) ALTER

D

Referential integrity constraints are not applied to ______ of the listed actions. a) modify b) insert c) delete d) create

D Create

In what cases is denormalization used for? A) Sometimes normalization is taken too far, consider the modification problems B) Used for security or performance reasons C) Used if cost of modification problems is low D) All of the above

D) All of the above

Referential integrity constraints do not affect which of the following SQL keywords?. a) modify b) insert c) delete d) create

D) Create

Which SQL keyword is used to eliminate duplicate rows in the results of an SQL SELECT query? A) UNIQUE B) SORT C) ORDER BY D) DISTINCT E) GROUP BY

D) Distinct

Which of the following cannot be done using the CONSTRAINT phrase? a. Create a single attribute primary key. b. Define a foreign key. c. Establish a referential integrity constraint. d. All of these can be done using the CONSTRAINT phrase. e. None of these can be done using the CONSTRAINT phrase.

D. All of these can be done using the CONSTRAINT phrase.

Which minimum cardinality enforcement action is the most difficult to reinforce? A. O-O relationship B. M-O relationship C. O-M relationship D. M-M relationship

D. M-M relationship

Which of the following is NOT a step for transforming a data model into a relational database design? A. Create relationships by placing foreign keys B. Create a table for each entity C. Verify appropriate normalization D. Making sure the relational database design can be queried in Oracle Live SQL.

D. Making sure the relational database design can be queried in Oracle Live SQL.

Certain data types are allowed/given access by each: BCNF Constraint DBMS AIP

DBMS

The DROP keyword belongs to which set of functions in SQL?

DDL

What is DDL?

DDL is data definition language, which is used to create, edit, and delete database structures. For example, data definition statements include: - Create database objects, like create a table - Modify structure and characteristics of database objects, like alter a table - Delete database objects, like drop a table

What is the difference between DDL and DML?

DDL statements are used for creating, defining and managing the Database structure. DML statements are used for managing data within Database.

Which command is used to remove rows from table EMPLOYEE?

DELETE FROM EMPLOYEE;

DELETE

DELETE = getting rid of row: DELETE FROM table_name WHERE condition;

What is the difference between DROP and DELETE?

DELETE deletes a row or set of rows from a table; DROP drops the table structure along with all of the table's data.

What is the main difference between DELETE and DROP in SQL?

DELETE statements will delete the data within the table while the DROP statement deletes the whole table

What is the difference between DROP and DELETE?

DELETE, removes a row or set of rows from a table DROP, removes unwanted database objects from a database

To delete a row or set of rows from a table use which statement?

DELETE.

Which SQL keywords in a SELECT statement eliminates duplicate rows in a result set?

DISTINCT

Which keyword may be added to the SELECT statement to inhibit duplicate rows from displaying?

DISTINCT

Which keyword should be used to remove duplicate rows from displaying?

DISTINCT

What keyword can be added to the SELECT statement to get rid of duplicate rows from displaying?

DISTINCT Example: SELECT DISTINCT ColumnName FROM TABLE_NAME WHERE ColumnName IN (4, 6, 9);

Are Insert, Update, and Delete DML statements or DDL statements?

DML Statements as they manipulate the data

What is DML?

DML is data manipulation language, which is used to create, edit, and delete data. For example, data manipulation statement include: - Insert, to add new data to an existing database - Update, to modify data in an existing database - Delete, to delete data from the an existing database

If you want to delete a view, which command would you use to do the task?

DROP

What SQL Keyword is used to delete a database object?

DROP

Which SQL command will remove a table from a database?

DROP

Which command will remove a table from the database?

DROP

__________ will delete the database objects.

DROP

Write the statment for dropping the table titled "CUSTOMER"

DROP TABLE CUSTOMER;

What is the difference between DROP and DELETE

DROP deletes existing database object permanently DELETE deletes the rows in a table that match the specified criteria

What is the difference between the SQL DROP statement and the SQL DELETE statement?

DROP removes the entire table, while DELETE removes only the data in the table.

What is the difference between DROP and DELETE?

DROP will delete all data and the Table from the database DELETE will delete the data from the specified Table

What is the difference between DROP and TRUNCATE tables?

DROP will delete the entire table, while TRUNCATE will only delete the information in the table.

To remove unwanted database objects from the database use which statement?

DROP.

What Does DDL Stand for? Distributed Data Language Definitional Data Language Data Definition Language

Data Definition Language

What does DDL Stand for?

Data Definition Language

What does DDL stand for

Data Definition Language

What is the difference between DROP and DELETE?

Delete: delete all the data but leave the table structure DELETE FROM PROJECT WHERE Department = 'Marketing'; Drop: delete the table's structure along with all of the table data. The DROP statement will permanently remove the object and all data.

What is the difference between DROP & DELETE?

Delete: delete all the data but leave the table structure. Drop: delete the table's structure along with all of the table data. The DROP statement will permanently remove the object and all data.

What does DELETE FROM do?

Deletes specific data from the table

What is denormalization?

Denormalization occurs when you take a fully normalized entity and combine some of the separated tables. This can be useful for displaying data that would've been distributed among multiple tables.

What are concurrency reading issues?

Dirty reads, Inconsistent reads, Phantom reads

Who invented SQL?

Donald Chamberlin and Raymond Boyce invented SQL while working at IBM in the early 1970s.

What is the difference between Drop and Delete?

Drop is used to alter the table structure where Delete is used to change data.

The SQL Statement below will show which of the following results: SELECT * FROM COURSES C LEFT JOIN PROFESSOR P ON C.PID=P.PID; A. Courses that are assigned to a Professor B. Professors who are not assigned to a course C. Courses that are not assigned to a Professor D. All of the Above E. A & C

E. A & C

Which of the following is NOT a DML Command? A. UPDATE B. DELETE C. MERGE D. INSERT E. All of the Above are DML

E. All of the Above are DML

The ALTER DDL Command can NOT do which of the following? A. Add a Foreign Key Constraint to a Table B. Add a Primary Key Constraint to a Table C. Add another Attribute to a Table D. Change an Attributes Data Type E. Delete a table

E. Delete a table

Why can primary keys never be null?

Each row must have a unique identifier

What is concurrency control's role?

Ensures that one user's actions do not adversely impact another user's actions

A relation is considered normalized when:

Every determinant is a candidate key

What is the difference between implicit and explicit locks?

Explicit locks are placed by command, implicit locks are placed by the DBMS

What does XML stand for?

Extensible Markup Language

What is XML?

Extensible Markup Language

(T/F) An ascending sort by date lists items from Newest to Oldest.

F

(T/F) The % symbol represents the single character wildcard.

F

(T/F) The AND keyword represents union of data sets whereas the OR keyword represents intersection of data sets.

F

(T/F) If the DATE entity is placed on the leftmost position on the FROM clause and SALE entity is placed on the rightmost position and both entities are joined together, a RIGHT INNER JOIN will display all dates, regardless if there was a sale on that date.

FALSE

(T/F) The SQL keyword DELETE is used to delete a table's structure.

FALSE

(T/F) The primary key of the subtype tables is always different from the primary key of the supertype table.

FALSE

A DROP statement is used to change the order of the rows.

FALSE

A view definition can include an ORDER BY clause. T/F

FALSE

Functions such as AVG and SUM work on text data types. TRUE or FALSE

FALSE

In Data Modeling, N:M relationships must always always include an intersection entity.

FALSE

Maximum cardinalities determine whether a foreign key is required or not required in the database design. (True/False)

FALSE

Only two tables can be queried by using a subquery.

FALSE

T/F A nested SQL function may return multiple values

FALSE

T/F The * in SELECT * clause of an SQL statement is not a wildcard for returning all columns in a table.

FALSE

T/F The SQL function GROUP BY should always be specified after the ORDER BY function

FALSE

TRUE/FALSE is it a good practice when inserting NULL values to put them into quotes?

FALSE

True or False: The following statement is valid; TRUNCATE TABLE table_name WHERE column_name=value;

FALSE

True or False: The format to add a column is ALTER TABLE tablename ADD COLUMN column_name new_data_type;

FALSE

T/F ORDER BY clause enables user to order data in ascending or descending order on one or more columns. It comes before GROUP BY clause.

FALSE It comes after GROUP BY clause.

Columns can be added or dropped without using the ALTER TABLE statement first (TRUE or FALSE)

FALSE, ALTER TABLE TableName is first required.

The ON UPDATE CASCADE statement is used in EMPLOYEESKILL table to ensure that the deletion of employee in EMPLOYEE table is reflected in order to maintain data integrity? 1.True 2. False

FALSE, ON DELETE CASCADE

Is this the correct use of the DELETE statement? T/F DELETE TABLE;

FALSE, correct format is: DELETE FROM TABLE;

TRUE/FALSE: An explicit lock is a lock set automatically by the DBMS.

FALSE. This would be an implicit lock.

What's the difference between FULL OUTER JOIN and JOIN...ON?

FULL OUTER JOIN: Including records both of the right and left table regardless whether a match a exist. If a match exist, the record will be match together. The JOIN... ON syntax can be also be used to perform a join. It has advantage from moving the JOIN syntax into the FROM clause

(T/F) An ID-Dependent entity does not share any part of its primary key with any other entity.

False

(T/F) DISTINCT keyword is used if user wishes to see duplicate columns.

False

(T/F) In 1:1 relationships, it matters which table receives the foreign key.

False

(T/F) Primary Key constrains can only be added while the create query is run.

False

(T/F)Drop Table deletes only one row

False

(T/F)SQL Queries are default in descending order

False

Cascade deletion of records causes database inconsistency and must be avoided. (True/False)

False

Deadlock can occur when applications lock only a single resource. True or False?

False

If you want to delete the data in one single cell of a table, you can use 'delete' syntax.

False

In SQL keyword WHERE is used to specify the tables that contain the data to be retrieved. True/False

False

Left Join is different from Left Outer Join

False

Recursive relations must be eliminated by creating separate tables. (True/False)

False

SQL DROP TABLE statement will drop the table but not all of its data. True or False

False

SQL is a programming language. True or False?

False

SQL wildcard character "%" indicates a single, unspecified character in a specific location in an SQL query. (T/F)

False

T/F SQL stands for Sorted Query Language

False

T/F When a column name is italicized and not underlined, that means it is part of the primary and foreign key.

False

T/F When using the attribute " WHERE" , you input what category the information is in

False

T/F When using the attribute DROP, it only deletes part of the table

False

T/F a subquery is the same thing as a JOIN?

False

T/F: Drop keyword is used to delete a record from the table

False

True or False a Surrogate Key is a constraint

False

True or False, JOIN indicates an outer join

False

True or False: A Char data type is a variable-length character string having maximum length

False

True or False: A Null Status indicates that the field must be entered?

False

True or False: A maximum of only two subqueries may be used in an SQL statement.

False

True or False: You can use built-in aggregate function in SQL WHERE.

False

True/False: If not ID-dependent, then must add primary key of the parent entity.

False

Views are temporary tables created to store data for future analysis. (True/False)

False

You can not join more than two tables together using the JOIN function.

False

It is required to specify column names when using INSERT INTO (T/F)

False - defining column names is not required when adding values to all columns of the table.

True or False; Data can be inserted into a table before the type is specified, as long as the column has a name.

False, A table requires both a column name and type before it is initialized, and before data can be inserted.

True or False: SQL is a programing language.

False, SQL is a data sublanguage.

T/F Aggregate functions can be used in a WHERE clause

False, aggregate functions should be used with a HAVING clause

True or False: In the SELECT statement framework, most queries follow a common pattern with three keywords used together. It is SELECT/FROM/GROUP BY

False, it should be SELECT/FROM/WHERE

(True/False) The ORDER BY clause will default as descending unless specified otherwise?

False, it will sort in ascending order. The ASC keyword and DESC keyword can be use to specify ascending and descending order.

This is an accurate statement into CONTACT table: SELECT INTO CONTACT VALUES (6, 1,'Peter')

False, no semicolon ;

True or False: In the Where clause, text and integers need quotation marks around them?

False, only text requires quotation marks

(True/False) The SQL DELETE statement deletes table data AND table structure.

False, the DELETE statement only removes data from the table. The structure remains intact.

(True/False) The SQL DROP TABLE segment deletes table data, but NOT the table structure.

False, the DROP statement, deletes the table data AND the table structure when executed.

SQL is a programming language. T or F

False. It is a data sub-language.

T/F The SELECT clause, is intended to define the name of the table.

False. SELECT identifies the column name, FROM a specific table.

T/F ORDER BY sorts Descending by default?

False. Sorts Ascending

T/F In SQL, the '*' character and '%' character are wild cards for single character and strings, respectively.

False. The '_' is the single character wildcard.

T/F When using the DROP and DELETE clauses, neither of these can be undone or rolled back.

False. The DELETE clause is exempt from this and can be undone or "rolled back"

True or False? The identifier of the subtype becomes the primary and foreign key of the supertype.

False. The identifier of the SUPERTYPE becomes the primary and foreign key of the SUBTYPE.

A Dirty Read occurs when a transaction re-reads a data set and finds that the data have changed. T/F?

False. This describes an Inconsistent Read.

T/F COUNT is used to get a total for numeric columns?

False. Use SUM() to get the total of a numeric column

T/F After writing query statements you MUST select the parse option before executing.

False. While its always a good idea to check the syntax of your statements, selecting the parse button is optional before execution.

You use the Sort By clause to order result by a certain field

False. You use Order By

True or false. A strong entity and has its own unique primary key is called an ID-relationship.

False. it is a non-identifying relationship.

True or False: Microsoft Access in default mode uses ANSI-92

False: Microsoft Access in default mode uses ANSI-89, but you can change it to 92 if you go to the options menu.

T/F: Subqueries can display data from more than 1 table

False: They can only display data from the top level table. You should use a JOIN

T/F HAVING clause applies conditions before groups are formed

False; HAVING clause acts as a filter after groups are formed.

T/F Inserting values into a table can be done with double quotes " "

False; SQL only uses single quotes ' '

MIN/MAX

Finds the minimum/maximum value for a specific column for those rows matching the criteria

Where does a foreign key in a 1:N relationship go?

Foreign Key always goes on the many side.

Which type of Outer Joins shows all values from both the tables?

Full Outer Join

Subtotals may be calculated by using the ______ clause.

GROUP BY

You can use the ________ clause to group rows by common values.

GROUP BY

How does GROUP BY function work?

GROUP BY clause can retrieved results that are categorized. Example: SELECT deptId, COUNT (*) AS numberOFEmployees (result of the count function to be called as numberOfEmployees) FROM Employee GROUP BY deptId;

When do you use a GROUP BY clause? When do you use the HAVING clause?

GROUP BY clause: group row by common values. The HAVING clause may be used to restrict which data is displayed.

When do you use a GROUP BY clause? When do you use the HAVING clause?

GROUP BY: when you want to group rows together in your query results. HAVING: when you want to specify which rows are in the groupings in a GROUP BY clause.

In order to join a table by itself what must you do to it?

Give the table two different aliases

When using calculations in combination of regular attributes you must use the _______ clause or you will get an error

Group By

WHERE is to individual rows as HAVING is to _____________

Groups/aggregated data

4. This keyword is used to filter and add restriction to the results from GROUP BY clause. 1.CHECK 2.HAVING 3.WHERE 4.ORDER BY

HAVING

The _____ clause may be used to restrict which data is displayed

HAVING

When using aggregate function, which keyword that is used to replace WHERE clause?

HAVING clause is used in aggregation function to replace WHERE clause..

How is HAVING different from WHERE?

HAVING is used with groups or aggregates of rows. Also, HAVING is used instead of WHERE when you use an aggregate function (Count, Sum, etc.) with a GROUP BY clause.

Difference between having and where clause

HAVING return rows where aggregate values meet the specified conditions while WHERE applies the condition

7.40, With respect to HTTP, what does stateless mean?

HTTP is stateless. This means that it does not maintain any record of connection or session between the client and the server. Thus, if same client at a browser make simultaneous request of a Web Server using HTTP, the Web Server will treat it like a new request from a new client. No data about the past connection with the client is maintained. The serer processes the client request and sends back the results.

Which company made SQL commercially available in the early 1980s?

IBM made SQL commercially available with its IBM DB2 product.

What are the rules concerning primary keys for ID-dependent entities?

ID-dependent must add primary key of the parent entity.

In Microsoft SQL Server ___ is used to specify surrogate keys.

IDENTITY ({StartValue}, {Increment})

what is an equivalent SQL keyword used to replace multiple OR conditions in a SQL Statement? 1. IN 2. NOT IN 3. BETWEEN

IN

What is the difference between INNER JOIN and OUTER JOIN?

INNER JOIN combines the data between two tables where data exists while OUTER JOIN includes everything including null values.

What are the three types of joins?

INNER, LEFT, and RIGHT

To add a row to an existing table, use the __________ statement.

INSERT

What statement should we use to add a row to an existing table?

INSERT

You can add records to the database using which SQL statement?

INSERT

What are the SQL statements used for data manipulation language?

INSERT - add data to the database UPDATE - modify data in the database DELETE - delete data from the database

How do you insert data into a table?

INSERT INTO table_name ( Attribute (don't say data type, just name) VALUES (MatchAttribute);

How do you insert data in SQL?

INSERT INTO tablename (Attribute1, Attribute2, Attribute3) VALUES('value1', 'value2')

What are the three main SQL data manipulation statements?

INSERT, UPDATE, and DELETE

What are the 4 statements of SQL DML and what do each of them do?

INSERT: used to add rows of data to a table. Statement has a number of different options. UPDATE: is used to change values of existing rows. DBMS will enforce all referential integrity constraints when processing UPDATE command. DELETE: Delete's records from a table. MERGE: essentially combines the SQL INSERT and SQL UPDATE statements into one that can either insert or update data depending upon whether some condition is met.

What is the difference between data type INT and DOUBLE?

INT does not allow decimal, while DOUBLE does since it is a large number with a floating decimal point.

An ---- constraint limits a column's values in comparison with other columns in the same table.

INTRARELATION

The __ becomes the primary key and foreign key of subtype?

Identifier

How do you create a surrogate key in SQL?

Identity(n,n)

What is the disadvantage of optimistic locking?

If a lot of activity in a given row occurs, the transaction might have to be repeated many times until it proceeds without error. Transactions like purchasing a popular stock are poorly suited for this type of locking.

Where does a foreign key in a 1:1 relationship go?

If both sides of the relationship are optional, it does not matter where the foreign key goes. If only one side is optional, the foreign key goes in the optional side.

In what situation would a bulk INSERT of data be appropriate?

If many values found in Table A need to be added into Table B.

What are the rules concerning primary keys for ID-dependent entities? o

If not ID-dependent, use the same techniques as for strong entities. o If ID-dependent, then must add primary key of the parent entity.

What is a Full Outer Join?

In SQL the FULL OUTER JOIN combines the results of both left and right outer joins and returns all (matched or unmatched) rows from the tables on both sides of the join clause.

What is the difference between a correlated and uncorrelated subquery?

In a correlated subquery, the inner query must be run repeatedly whereas in an uncorrelated subquery it is only run once.

Data Types

In each column you indicate exactly what type of data will be stored in that column.

What are the two different types of Joins that can be used to combine data?

Inner Join (Joins only if a match occurs) Outer Join: including Left, Right, and Full Outer Joins (Joins even if data between tables does not match)

What is the difference between an INNER & OUTER JOIN?

Inner join: only displays data from the rows that matched based on join conditions, and data can be lost or at least appear to be lost. Outer join: avoid the data lost from inner join.

What is the difference between an INNER and OUTER JOIN?

Inner join: only displays data from the rows that matched based on join conditions, and data can be lost or at least appear to be lost. Outer join: avoid the data lost from inner join. SELECT FirstName, LastName, HoursWorked FROM EMPLOYEE JOIN ASSIGNMENT ON EMPLOYEE.EmployeeNumber = ASSIGNMENT.EmployeeNumber ORDER BY EMPLOYEE.EmployeeNumber, ProjectID;

What are the SQL statements that we use to manipulate data?

Insert, update, delete

Single quote is not used in one of the following data types: 1- Char 2- VarChar 3-Date 4-Int

Int is the correct answer

To create an N:M relationship, a new table is created. What is this table called?

Intersection Table

What is the name of the table that is created to replace a N:M relationship in database design?

Intersection Table

What type of table is needed to when an N:M relationship exists between 2 entities?

Intersection Table

What is the difference between Intersection Table and Association Table?

Intersection Table holds the primary keys of the parent tables as foreign keys, and this combination of keys serves as the composite primary key of the intersection table itself whereas in an Association Table along with the key data; it also contains other user data. That is it also has one or more attributes that pertain to the relationship.

To create an N:M relationship, a new table is created. What is that table called?

Intersection table

What is the meaning of a reserved word in SQL

It is a special word that is part of a SQL command.

What does the SQL TRUNCATE TABLE statement do?

It is used to remove all data from the table while leaving the table structure itself in the database while also resetting any surrogate primary key values back to the starting point.

What does the Parse button do in SQL management studio?

It validates the syntax prior to execution.

What is an SQL view ?

It's a virtual table that is constructed from other tables or views.

What are the 4 types of join?

JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN

What are the three types of outer join?

LEFT OUTER JOIN RIGHT OUTER JOIN FULL JOIN

_____ can be paired with wildcards to find rows matching a string value

LIKE

What's a data constraint

Limitations on Data values

For the IDENITY (M,N) property, what do you replace M & N with?

M is the surrogate key that will be the value for the first row created. N is the increment each additional row is increased by.

In IDENTITY(M,N), what does the M and N used for?

M is where the value will start when the first row is created and N is how much each row will increase.

•How are 1:1/1:N/M:N relationships represented with foreign keys? o

M:N: The intersection table will receive the primary keys of the two parent table and make these key (foreign keys) as it primary keys. o 1:N: By Placing the primary key of the parent in the child as a foreign key. o For the representation with foreign keys,in 1:1 relationship, if either of the entities has an optional side, then the entity having optional side contains the foreign key.

A SQL built-in function that finds the maximum value for a specific column for those rows matching the criteria.

MAX

Which statement combines INSERT and UPDATE into one statement?

MERGE statement

Which statement combines INSERT and UPDATE into one statement?

MERGE.

Which of the following is a SQL Aggregate function? MIN GROUP BY ORDER BY SELECT

MIN

What is the difference between maximum and minimum cardinalities?

Maximum cardinalities are reflected by foreign keys Minimum cardinalities reflected by having foreign keys listed as Null or NOT NULL

The ________________ determines how a relationship is represented.

Minimum and maximum cardinality

What is included in a final database design?

Modified E-R diagram, now showing primary keys, foreign keys, and all necessary intersection tables Separate table listing data types, NULL/NOT NULL, default values, and constraints List of tables in parenthetical format

What are the two wildcard's that can be paired with SQL LIKE keyword?

Multiple character wildcard character is a percent sign (%). Single character wildcard character is an underscore(_).

True or False - A 1:1 relationship is when you should create an intersection table?

N:M relationship

Intersections tables support what kind of relationships?

N:M; the intersection table contains the keys of the relationship in the relationship.

What can a criteria statement may be preceded by to say that all information will be shown except that information matching the specified criteria?

NOT

When creating a table in SQL, what is needed in the create statement to ensure that the value is required?

NOT NULL

When altering a table to add a column you cannot add?

NOT NULL. because there is no data at the time you create the column so it would violate the constraint. you need to add the data them change it to NULL.

What happens if you don't specify null status?

NULL is assumed

What attributes are needed to create a table?

Name, Data Type, Constraint (Optional)

What is netbeans IDE?

NetBeans is an integrated development environment for Java. NetBeans allows applications to be developed from a set of modular software components called modules.

Does the SQL view table have its own data?

No, it obtains data from tables or other views

When creating a new table, can we define a "ON UPDATE NO ACTION" as a constraint in Oracle?

No. It is by default NO ACTION. Oracle will throw an error.

Subqueries are divided into what two categories?

Non-Correlated and Correlated

Actions needed to be taken for optional (O-O)?

None

Name the components of the ODBC standard

ODBC Application Program: It issues requests to create and terminate a connection with a data source. It calls the Application Programming Interface (API) function which issue SQL statements. It receives the results and also processes the errors for the statements issues to the data source. ODBC driver manager: A driver manager is the intermediary between the application program and the ODBC DBMS drivers. ODBC DBMS driver: The ODBC DBMS driver plays the role of intermediary between the ODBC driver manager and various data sources. Each data source type requires different DBMS driver. Data source: A data source is the database. It also includes operating system, network protocols and DBMS that is associated with the given database.

In a 1:N relationship, the term "parent" refers to the table on which side of the relationship.

ONE

To sort the date from new to old. What query framework should be used?

ORDER BY ...DESC

What is the statement to order the by highest value to lowest?

ORDER BY FIELDNAME DESC;

The _________ syntax can be used to obtain data that exists in one table without matching data in the other table.

OUTER JOIN

In a 1:1 binary relationship where the min cardinality on one side is zero, and the min cardinality on the other side is 1, where should the foreign key go?

On the optional side. An example would be a person and a house. Its not required that person needs a house, therefore the foreign key will be places on the house, going to the person.

How do you represent Primary and foreign keys in a subtype-supertype relationship in a database design?

PK and FK are indicated with a key symbol right next to the attribute.

What are the possible "column constraints" for SQL CREATE TABLE statement?

PRIMARY KEY, NOT NULL, NULL, UNIQUE, IDENTITY, DEFAULT

What must you define before child tables?

Parent tables

Denormalization is done to improve _________ or ___________.

Performance; Security

A Constraint Key can identify a:

Primary Key or Foreign Key

What does the "Execute" button in SQL Server Management Studio do?

Produces the query results. In other words, SQL Server Management Studio will run the query against the selected database, and once processed, it will confirm whether or not the query was run successfully. (in chapter 1)

What are the pros and cons of denormalization?

Pros: • Simplicity of design • Allows to extract data without performing a join operation with another table. • Increases query performance. • SQL commands are easier to write. Cons: • Introduces modification anomalies. • Redundant data is possible. • More storage space is required.

_____ pulls information from one or more relations and create a new relation(temporarily)

Query

How do you sort rows in SQL Select Statement?

Query results may be sorted using the ORDER BY clause. Default is ascending (ASC) but can also specify explicitly or specify DESC, by including ASC or DESC keyword after the column name. SELECT * FROM EMPLOYEE ORDER BY EmpName; SELECT * FROM EMPLOYEE ORDER BY EmpName ASC;

A relationship between two attributes within the same entity is called a _________ relationship

Recursive

How are recursive relationships represented with foreign keys and intersection tables?

Recursive relationships adhere to the same rules as binary relationships. -1:1 and 1:M relationships are saved using foreign keys. -M:N relationships are saved by creating an intersecting relation.

Entities can be associated with one another in __________.

Relationships

What does SQL TRUNCATE DO?

Removes all the data from the table while leaving the table structure itself in the database.

What are the four uses for ID-Dependent entities?

Representing N:M Relationships Association Relationships Multi-valued Attributes Archetype/Instance Relationships

What does the "Execute" button in SQL Server Management Studio do?

Run the query on actual database.

Which SQL statement is used to extract data from a database?

SELECT

SQL statement is best known for retrieving information from the database that matches specified criteria.

SELECT Example: SELECT empName FROM Employee WHERE empID = 33;

What is the SQL format for selecting data?

SELECT FROM (Optional)WHERE (Optional)Order By

What SELECT statement shows all the column values for the rows?

SELECT * FROM TABLE_NAME;

What is the syntax for counting the number of rows?

SELECT COUNT(ColumnName) FROM TABLE

How do you sort columns in SQL?

SELECT ColumnName1, ColumnName2--> This sorts Column.

How do you sort rows?

SELECT CustomerID, CustomerName--> This sorts Column FROM CUSTOMER ORDER BY CustomerID --> This would sort column from ascending order if you want reverse use desc

How would you write SQL code for finding an employee who makes between 75000 and 100000

SELECT EmpName FROM EMPLOYEE WHERE Salary BETWEEN 75000 AND 100000;

Give an example of sub-query.

SELECT LastName, FirstName, Phone FROM CUSTOMER WHERE CustomerID IN (SELECT SaleID FROM SALE WHERE SaleID = 1)

How do you select a column named "Total" from a table named "INVOICE"?

SELECT Total FROM INVOICE;

Which statement will retrieve information from the database that matches the specified criteria?

SELECT statement

Which statement is an SQL DDL statement that is used to change the structure of an existing table. a. SQL CREATE b. SQL ALTER c. SQL MODIFY

SQL ALTER

What do you use to add, modify, and drop columns and constraints?

SQL ALTER TABLE statement

The _________ statements used to construct tables, define columns and column constraints, and create relationships.

SQL CREATE TABLE

What is the difference between SQL LEFT JOIN and SQL RIGHT JOIN?

SQL LEFT JOIN: read data from the first table in the query SQL RIGHT JOIN: read data from the second table in the query

SQL inner join can also be referred as?

SQL equijoin

What kind of language is SQL considered to be?

SQL is a domain-specific sub language used for managing data within a relational database. It differs from a general-purpose languages like Python or Java.

A plain text file containing SQL commands is called a ___ and usually uses a file name extension of ___.

SQL script, .sql

A ____ is a virtual table that is constructed from other tables or views.

SQL view

ANSI adopted the _____ as the national standard for Structured Query Language. 1. SQL-92 2. SQL-89 3. SQL-Server 4. SQL-Standard89

SQL-92

In the ANSI/ISO SQL standard, these procedural programming language extensions are known as __________________________________________________________

SQL/Persistent Stored Modules (SQL/PSM).

When added to an SQL SELECT statement, WHERE allows us to ___________

Select rows in a table by specifying rules. The WHERE keyword can also be used to insert, update and delete data from tables.

SELECT will retrieve information from the database that matches the specified criteria using the _________ framework.

Select/From/Where

When creating a statement, what it needed at the end of the statement?

Semi colon (;)

1:N relationship, parent required

Specify FOREIGN KEY Set foreign key NOT NULL

1:N relationship, parent optional

Specify FOREIGN KEY Set foreign key NULL

1:1 relationship, parent required

Specify FOREIGN KEY Specify foreign key UNIQUE Set foreign key NOT NULL

1:1 relationship, parent optional

Specify FOREIGN KEY Specify foreign key UNIQUE Set foreign key NULL

In a 1:N relationship where the parent is optional, what are the CREATE TABLE constraints?

Specify FOREIGN KEY constraint . . . Set foreign key NULL.

What CONSTRAINT should be enforced while creating table that 1:N relationship with parent optional

Specify FOREIGN KEY constraint. Set Foreign Key Column as NULL

In 1:1 relationship with parent table required, what are the constraints to be specified on the create table?

Specify FOREIGN KEY constraint. Specify foreign key UNIQUE constraint. Set foreign key NOT NULL.

What does SQL stand for?

Structured Query Language

What are the two ways to retrieve information from multiple tables?

Subqueries and Joins

A result of a query fed to another query is also referred as?

Subquery

It is not recommended to put CASCADE phrases when an attribute is a: Surrogate key Foreign key Gold key Last key

Surrogate key

What does SDLC represent?

Systems development life cycle

(T/F) For the representation with foreign keys,in 1:1 relationship, if either of the entities has an optional side, then the entity having optional side contains the foreign key.

TRUE

(T/F) Four properties need to be specified for each table column: null status, data type, default value, and data constraints.

TRUE

(T/F) SQL includes five built-in locations: COUNT, SUM, AVG, MAX, MIN.

TRUE

(TRUE/FALSE) The ORDER BY Clause will default as ascending?

TRUE

A constraint defining the primary key must be included when creating a table (TRUE or FALSE)

TRUE

An associative entity is an intersection table containing additional attributes. TRUE or FALSE

TRUE

Is this the correct use of a right join connecting multiple tables? T/F SELECT Attribute FROM TABLE RIGHT JOIN SALE ON CUSTOMER.CustomerID = SALE.Customer ID RIGHT JOIN SALE_ITEM ON SALE.SaleID = SALE_ITEM.SaleID RIGHT JOIN ITEM ON SALE_ITEM.ItemID = ITEM.ItemID ;

TRUE

Maximum Cardinalities are reflected by foreign keys and sometimes intersection tables. T/F

TRUE

SQL is not a programming language.

TRUE

T/F SQL is the programming language used to manipulate data and data objects in a relational database management system.

TRUE

T/F The DROP statement will completely remove the object and all data

TRUE

T/F The SQL function GROUP BY is really a DISTINCT ASC sort function

TRUE

T/F The result of a SQL statement is always a table

TRUE

TRUE or FALSE The correct identifier of a subtype will always act as its primary key AND foreign key.

TRUE

When two conditions must both be true for the rows to be selected, the conditions are separated by the SQL AND keyword.

TRUE

T/F Can you add a new column to a certain table with this ALTER TABLE statement? ALTER TABLE table_name ADD column_name datatype;

TRUE This syntax is correct ALTER TABLE table_name ADD column_name datatype;

What SQL Keyword is used to delete data from a table but leave the structure intact?

TRUNCATE

What is the difference between DROP and TRUNCATE?

TRUNCATE removes all rows from the table which cannot be retrieved back, DROP removes the entire table from the database and it cannot be retrieved back.

Is it better to define PRIMARY KEY as a table constraint or a column constraint?

Table constraint

What does NOT NULL constraint indicate?

That values are required in that column

What is the CHECK constraint and what keywords can be used?

The CHECK constraint can be used to create restrictions on the values that are allowed to appear in a column. If a value satisfies the CHECK constraint, it will be allowed to enter into the database, otherwise it will be restricted -contains keywords IN, NOT IN, LIKE, <,>

What is the Check Constraint used for?

The CHECK constraint can be used to create sets of values to restrict the values that can be used in a column.

When do we used CHECK Constraint?

The CHECK constraint is used to limit the value range that can be placed in a column.

If a column is not specified with Null status when creating the table, what does the DBMS interpret as?

The DBMS will interpret as the column value as Null value

When do you use a GROUP BY clause?

The GROUP BY clause use when there is a mix of built-in function and column named.

What is the example of the JOIN ... ON approach to inner join in comparison to the WHERE ___=____ approach?

The JOIN...ON approach moves the joining to the FROM part of the statement

Which keywords are used to place a primary key constraint when creating a table?

The Primary Key keywords

When do you use the HAVING clause?

The SQL HAVING clause is used in combination with the GROUP BY clause to restrict the groups of returned rows to only those whose the condition is TRUE. -Essentially like the WHERE keyword but for build-in functions

What is the SQL WHERE clause is used for? And what can it prevent?

The SQL WHERE clause is used to filter the results and apply conditions in a SELECT, INSERT, UPDATE, or DELETE statement. -It specifies which rows are to be listed. It prevents the command from filtering out more/all data where there is only the specific data targeted for filtering.

How do the TRUNCATE and DELETE statements treat surrogate keys differently?

The TRUNCATE statement will reset surrogate keys back to the initial value, while the DELETE statement does not.

How can you add a constraint to a column that will not let any two entries be the same?

The UNIQUE constraint enforces uniqueness on either a single column or a combination of different columns.

Find what is wrong with the SQL Query SELECT subject_code, AVG (marks) FROM students WHERE AVG(marks) > 75 GROUP BY subject_code;

The WHERE clause cannot be used to restrict groups. Instead, the HAVING clause should be used. SELECT subject_code, AVG (marks) FROM students HAVING AVG(marks) > 75 GROUP BY subject_code;

Name the components of the ODBC standard.

The components of the ODBS standard are the application program, the ODBC driver manager, the ODBC DBMS driver, and a data source.

When an SQL command of deleting a table is used what happens to the data in that table?

The data in that table is also deleted.

What is E-R model?

The data model which is based on the real time entities and the information and also the relationship among those entities.

What are the rules concerning primary keys for supertypes/subtypes?

The identifier of the supertype becomes the primary key and the foreign key of each subtype.

How are M:N relationships represented with foreign keys and intersection tables?

The intersection table will receive the primary keys of the two parent table and make these key (foreign keys) as it primary keys.

How is 1:1 relationship represented with foreign key?

The key from one relation is placed in the other as a foreign key. It does not matter which table receives the foreign key. . the specified foreign key must be constrained as UNIQUE.

What is the difference between left join and right join.

The left join returns all rows from the left table with matching rows from the right table. While the right join returns all rows from the right table with matching rows from the left table.

What is the difference between the HAVING clause and WHERE clause?

The main difference between the WHERE clause, and Having clause is that conditions specified in WHERE clause is used while fetching data (rows) from the table. The rows that do not meet the criteria in the WHERE clause will not be fetched. The HAVING clause is later used to filter summarized or grouped data.

What is the meaning of Unique constraint in SQL?

The meaning of UNIQUE Constraint in SQL is to ensure that all values in a column are different.

what is the difference between the parse button and the execute button in sql management studio?

The parse button checks if the sql statement is syntactically correct without executing it while the execute button runs the sql statement.

What was the first attempt at a relational database language called?

The precursor to SEQUEL/SQL was called Square, but it was insufficient, so Donald Chamberlin and Raymond Boyce (of Boyce-Codd normal form) invented SQL in 1973.

What are the rules concerning primary keys for ID-dependent entities?

The primary key for ID-dependent weak entities is always a composite primary key that includes the primary key of the entity that the ID-dependent entity depends on for its existence.

What are the rules concerning primary keys and foreign keys for supertypes/subtypes?

The primary key of the supertype as the primary key and foreign key of the subtype.

•What are the rules concerning primary keys for supertypes/subtypes? o

The primary key of the supertype as the primary key and foreign key of the subtype.

What are the rules concerning primary keys for supertypes/subtypes?

The primary key of the supertype will be the primary key (and a foreign key) for the subtypes

What SQL term/condition refers to the business rules (If an EMPLOYEE row is connected to any ASSIGNMENT, the EMPLOYEE row cannot be deleted. and If a PROJECT row is deleted, all ASSIGNMENT rows connected to the deleted PROJECT are also deleted)

The referential integrity constraint

When using RIGHT JOIN clause to return all records from two tables, what is the result from the left side if there is no record?

The result is NULL.

7.41, Under what circumstances does stateless pose a problem for a database processing?

The statelessness of HTTP does not create any problem for serving contents, like static Web pages, or processing the queries of a database, but while working with applications, online services, etc, where there are multiple interactions with the same client and the multiple database actions are grouped into a single transaction, this statelessness of HTTP pose a problem for database processing.

How are minimum cardinalities reflected in a database design ?

They are reflected by having the foreign keys listed as "NULL" or "NOT NULL".

What does the % and _ do?

They are wildcard characters. The underscore symbol (_) represents a single, unspecified character. The percent sign (%) is used to represent a series of one or more unspecified characters. These characters are used with the LIKE keyword.

What is a SQL reserved word?

This is a word that has a special meaning as part of a SQL command. Generally reserved words cannot be used as table or column names.

What is the purpose of keyword DISTINCT?

This keyword may be added to the SELECT statement to suppress the display of duplicate rows EX: SELECT DISTINCT DeptID FROM Employee;

What does the "Parse" button (checkmark button) in SQL Server Management Studio do?

This only validates syntax, and doesn't check that the objects you've referenced exist, that joins are valid, etc. It tests the syntax of what you write before actually run it on the real database.

What does /* (before text) and */ (after text) do?

This turns the text between /* and */ into an SQL comment.

•What is an intersection table and when is it used? Does an intersection table require attributes besides primary keys? What is an intersection table with additional attributes called?

To create an N:M relationship, a new table is created. This table is called an intersection table. o An intersection table has a composite key consisting of the keys from each of the tables that it connects.

What is the CHECK constraint used for?

To define data constraints

When do you use a GROUP BY clause?

To groups rows by common values. You can apply functions the grouped rows. Example: SELECT Apple, Cereal, Count(*) AS ShoppingGoods, TABLE STORE GROUP BY Apple, Cereal

What is the benefit of the delete/update cascade in the database?

To maintain equity and quality of the data in the database

How can you select all the even number records from a table? All the odd number records?

To select all the even number records from a table: Select * from table where id % 2 = 0 To select all the odd number records from a table: Select * from table where id % 2 != 0

BEGIN, ROLLBACK, COMMIT are what type of SQL commands?

Transaction Control Language (TCL)

'Drop' syntax is DDL language used to modify data structure, 'Delete' is DML language to delete data but not structure of a table.

True

(T/F) A CHECK constraint can be used to create sets of values to restrict the values that can be used in a column

True

(T/F) SELECT is the best known SQL statement.

True

(T/F) Supertypes share primary keys with their subtypes

True

(T/F) The primary key and foreign key of a subtype is the primary key of the supertype.

True

(T/F) Varchar should be used when the sizes of the data entries vary considerably.

True

(True or False) Like a 1:1 relationship a 1:N relationship is saved by placing the key from one table into another as a foreign key

True

(True/False) An intersection table is a child table that has two 1:N relationships that have been manufactured to replace a single N:M relationship in the data model.

True

(True/False) The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed.

True

(True/False) When using a SUM() function, you need to use GROUP BY instead of ORDER BY.

True

A cascading update occurs when a change to the parent's primary key is applied to the child's foreign key.

True

A query is a relation

True

A surrogate is auto-generated.

True

An intersection table is always ID-dependent on both of its parent tables. T/F

True

An intersection table typically has a composite key comprised of the keys from each of the table to which it is connected. (True or False)

True

Entities that are used in extended E-R model are shown as a table in the relational database design. True/False

True

If we use SQL language, we would be able to work with any database product or vendor? T or F

True

In a Venn diagram, a subset of a set is found completely within that set.

True

In every database system, every constraint will have a unique name. T or F

True

It's entirely reasonable for an intersection table to have additional attributes except composite primary keys. (T/F)

True

Null status refers to whether the column can have a null value. True OR False

True

Recursive relationships in a many to many relationships require an intersection table.

True

SQL is not a programming Language but rather than a data sublanguage

True

T/F An intersection table is used when there is a N:M relationship

True

T/F Database design occurs in the component design step of the systems development life cycle.

True

T/F In LEFT and RIGHT JOIN operations, all the rows from at least one table are always included in the result set.

True

T/F SQL is not a complete programming language

True

T/F An '*' is used in the SELECT clause when the user wants all of the columns from the table in the output.

True

T/F If you do not specify the type of JOIN in your SQL command (i.e. INNER, LEFT, RIGHT, FULL OUTER), you will perform an INNER JOIN by default.

True

T/F More than two tables can be queried by using a subquery.

True

T/F The DROP command removes a table from the database. All the tables' rows, indexes and privileges will also be removed.

True

T/F The primary key of the super type is used as the key of a sub type?

True