SQL_RelationalAlg Flashcards by Sammy Christensen

Q

Is SQL case sensitive?

A

Case convention
• SQL is completely case insensitive.
Upper-case or Lower-case makes no
difference. We will use case in the
following way:
– UPPERCASE marks keywords of the SQL
language.
– lowercase marks the name of an attribute.
– Capitalized marks the name of a table.

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

How to Create and Drop Tables?

A

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

What are Table declaration elements?

A

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

Why add NOT NULL when creating tables?

A

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

How to declare keys?

A

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

How to add Primary Key SQL?

A

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

How to code foreign keys?

A

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

Rules concerning Foregin Keys.

A

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

Code in SQL.

A

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

Another Example

A

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

How to use value constraints?

A

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

Add value constraint that the period is an INT for period 1:4.

A

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

Another Check Constraint for the Period being an INT of values 1-4.

A

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

How to insert data to this table?

A

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

Why is one of those illegal?

CREATE TABLE GivenCourses (
course CHAR(6) REFERENCES Courses,
period INT CHECK (period IN (1,2,3,4)),
numStudents INT,
teacher INT REFERENCES People(ssn) NOT
NULL,
PRIMARY KEY (course, period)
);

A

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

How to Delete rows from Table.

A

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

How to update tables.

A

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

What is a Query? What is the difference between query and modifications?

A

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

What does SQL means?

A

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

How is Algebra defined in this context?

A

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

What is relational algebra?

A

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

Explain Selection (Relational Algebra).

A

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

Which operations is denoted with a sigma?

A

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Q

Explain Projection (Relational Algebra).

A

How well did you know this?

1

Not at all

2

3

4

5

Perfectly

Which operation is denoted with a pi?

What are Cartesian Products?

What are Joining relations?

What does this symbol represent?

Explain Natural Join.

What does this specific symbol represent?

Explain Sets or Bags.

Differences between sets and bags?

Method for selecting from a specific table with conditions.

How does query result look?

Translate to Relation Algebra Expression.

Translate.

Translate to Relational Algebra.

Translate.

Translate.

Explain Subqueries.

Why renaming attributes?

Why renaming relations?

Explain Renaming in Relational Algebra.

Explain the following expression.

Explain Sequencing.

Explain views.

Create a view DBLectures containing room, weekday and hour from the table Lectures. The course should be TDA357 and the period should be 3. Then query the weekday from the view based on the room HC1.

Explain the WHERE clause.

How to test for membership.

What does this mean?

How do you test for existence?

What does this mean?

What is a correlated query?

What are ordinary comparisons?

Meaning?

How to do comparisons with many rows?

What does this mean?

What are string comparisons?

What does the NULL symbol means?

Can you compare values with NULL?

Explain three-valued logic.

Results?

What does it mean with don't expect usual results with three-valued logic?

E-R diagram?

Which Approach?

Which Approach?

Schema?

Schema?

E-R?

E-R?

Schema?

E-R?

Schema?

Wrong?

Wrong?

Wrong?

Can Constants be used in Projections?

Explain Aggregation.

What will happen?

What will happen?

What will happen?

What does single-value query means?

NULL in aggregations?

Query for capacity at campus?

Meaning?

Explain Grouping.

Explain Grouping in terms of Relational Algebra.

Symbol represents?

Explain specialized renaming of attributes.

What does this represents?

Explain Tests on Groups.

Resultat?

Relational Algebra Translation?

Translation?

Explain Sorting Relations.

What does this represents?

What will happen?

Describe full structure of SQL query.

Translate to relational alebgra.

Translate to SQL query.

Relational Algebra Expression?

SQL expression?

Results?

Results?

Relational Algebra Expression?

SQL Expression?

Explain Lexical vs Logical ordering.

What are Available attributes in SELECT?

Valid/Invalid.

Valid/Invalid?

Valid/Invalid?

Write Symbols for: Select Project Rename Union

Write symbols for: Intersection Difference Cross Product Join

Write symbols for: Natural Join Division Grouping

Difference between Selection and Projection?

Left Outer Join?

Right Outer Join?

Full Outer Join?

Which modifications can be made to the database?

Modifying the contents of a database: – Insertions INSERT INTO tablename VALUES tuple – Deletions DELETE FROM tablename WHERE test over rows – Updates UPDATE tablename SET attribute = value WHERE test over rows

Explain Insertions with Queries.

What are Explicit Attributes?

Describe how SQL handles Default Values.

Explain Insertion with Default Values.

Explain Policies for updates and deletions.

Explain Cascading.

Explain Set NULL.

Explain Single-attribute constraints.

Explain how to specificy that a value must be non NULL

Explain a Special Case of References.

Which are the different types of constraints?

Explain Triggers.

Explain Assertions as Triggers.

Explain the basic Trigger structure.

Explain Stored Procedures.

What does NEW in Triggers refer to?

Example of Trigger structure.

Explain Trigger events.

What does FOR EACH ROW mean?

Explain Trigger Conditions.

Explain views.

Elaborate on updating Views.

Explain Triggers on Views.

Summarize Triggers.