SQL_RelationalAlg Flashcards
Is SQL case sensitive?
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 to Create and Drop Tables?
What are Table declaration elements?
Why add NOT NULL when creating tables?
How to declare keys?
How to add Primary Key SQL?
How to code foreign keys?
Rules concerning Foregin Keys.
Code in SQL.
Another Example
How to use value constraints?
Add value constraint that the period is an INT for period 1:4.
Another Check Constraint for the Period being an INT of values 1-4.
How to insert data to this table?
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)
);
How to Delete rows from Table.
How to update tables.
What is a Query? What is the difference between query and modifications?
What does SQL means?
How is Algebra defined in this context?
What is relational algebra?
Explain Selection (Relational Algebra).
Which operations is denoted with a sigma?
Explain Projection (Relational Algebra).
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.
