Comprehensive Flashcards for Elements of Databases Exam 1

What is the difference between a relational and a non-relational database?

Relational databases organizes data into one or more tables or relations made up of columns and rows. Each table is one entity, rows represent instances of that entity type, and the columns represent values attributed to those instances

What is the meaning of the word 'schema'?

It is how data is organized

Can you give an example of a one-to-many entity relationship?

Primary key in one table can be the foreign key in another table

Can you give an example of a many-to-many entity relationship?

Breaks the single-value requirement, enlists a junction or a bridge table to relate tables

What method is used to make a many-to-many relationship conform to the relational model?

Uses a junction or a bridge table

What is a bridge table? What is its use?

It is a table with PKs and FKs to relate two or more tables in a relational database allowing for many-to-many relationship

True/False: SQL always applies the WHERE clause before the HAVING clause.

True

Can we have the table aliases before the WHERE clause?

Yes; but not in a WHERE clause

Which clause results in the appropriate columns being 'printed' out of the query? In a SQL SELECT statement, which of the following clauses indicates the column(s) to be retrieved into the result set of rows?

SELECT

Which clause indicated the table name(s) to select? In a SQL SELECT statement, which of the following clauses indicates the table or tables from which to retrieve rows?

FROM

What is the sequence of execution among FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT?

FROM, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT

Is a query's syntax order the same as the execution order?

No

Give two examples of conditions that rows should meet for them to be selected?

WHERE, HAVING

What are the types of relationships seen in the ERDs?

One-to-one, one-to-many, many-to-many

Which of the following is the correct way to search for a null value? = 'NULL' or=NULL or IS NULL

IS NULL

What are examples of MySQL numeric datatypes?

integer, floating-point

What are the types of JOINs? What does each of them do?

Inner joins, self joins, outer joins (left, right, full), cross, and natural

Can you use an 'IN' and a 'NOT IN' clause properly in an SQL query? Give an example

Use with WHERE clause

What does a CROSS JOIN do? Where is it useful?

Join that produces cartesian product of the named tables, producing every permutation of their rows. (Generates a paired combination of each row of the first table with each row of the second table)

What does this clause search for: WHERE last_name LIKE "Pa%"?

Searches for rows in a database where the last_name column starts with the letters "Pa". This is a wildcard character that represents zero or more characters. It allows for any sequence of characters (including none) to follow "Pa".

What does this clause search for: WHERE last_name LIKE "P2__"? // there aretwo underscores there

Searches for rows in a database where the last_name column starts with "P2" and is followed by exactly two additional characters. Each underscore (_) represents a single character. So, two underscores mean that exactly two characters must follow "P2".

What are the shortcuts that can be used for the GROUP BY clause?

Group By # refers to the # listed row in SELECT

Between a subquery and its parent query, which is executed first? Why?

A subquery executes first, before its parent query, so that the results of the subquery can be passed to the parent (outer) query

Is it okay to delete the FK in a table if the corresponding key appearing as a PK in another take is kept untouched? How about the other way around? Why?

Deleting the FK is fine as long as you are okay with losing the referential integrity between tables. Deleting the PK is not okay if there are existing FK constraints that reference it, as it will break the relationship and cause data integrity issues.

What happens if the data types in the ON clause of a JOIN do not match?

May try to convert; may also result in no matches or errors

When does the sequence of three tables JOINed does not matter? When does it matter?

Order does not matter when only INNER JOINs are used, as all the joined tables must match and contribute to the result. Order matters when OUTER JOINs (LEFT, RIGHT, FULL) are used, as they retain unmatched rows from one side; when multiple OUTER JOINs are combined, the sequence affects which rows are preserved; and when filters or cardinality differences exist

Do we need to specify the table name when SELECTing from multiple TABLEs when the selected columns names are unique across all selected tables? How about if they are not unique?

If column names are unique: You don't need to specify the table name or alias.

If column names are not unique: You must use the table name or alias to avoid ambiguity.

Can subqueries return a TABLE? Can we name such a table?

Yes, subqueries can return a table (more precisely, a result set) in SQL. This is commonly referred to as a derived table or inline view. You can also name the result of the subquery using a table alias to reference it in the outer query.

Are we allowed to JOIN a TABLE to itself instead of to another table? If yes, give an example of why we would want such a query. If not, indicate why this would result in an error

Yes, you are allowed to JOIN a table to itself. This is known as a self-join. It is useful when you need to compare rows within the same table

What is a NATURAL JOIN? How does that help?

Relies on identical column names across the tables. Don't need to identify tables

What is the difference between WHERE and HAVING when filtering rows and aggregation

The WHERE clause filters individual rows before aggregation and cannot use aggregate functions, while the HAVING clause filters aggregated groups of rows after the GROUP BY operation and can use aggregate functions. Use WHERE when filtering raw data, and HAVING when filtering based on aggregated results.

What is the difference between OLAP and OLTP?

OLAP (Online Analytical Processing) focuses on analyzing historical data for business insights, while OLTP (Online Transaction Processing) supports real-time transactional operations. OLAP is optimized for read-intensive, complex queries, whereas OLTP is optimized for frequent updates and simple transactions.

True/False: A Transaction is a set of potentially multiple SQL statements.

True

What is ACID? Can you give the meaning of each property listed in the acronym?

ACID transactions: Atomicity (transaction's operations are one whole unit or atom), Consistency (only valid data, following all rules and constraints, is written to the database), Isolation (transaction is not affected by other transactions), and Durability (all changes generated by it are stored permanently)

What is the difference between COMMIT and ROLLBACK?

COMMIT makes all statements permanent; ROLLBACK undoes all statements

Can you have more than one SAVEPOINT in an SQL transaction?

Yes

Can you have more than one COMMIT in an SQL transaction?

No

What are the four Vs listed in the framework of Big Data as studied in class?

Volume, Velocity, Variety, and Veracity

In a MySQL SELECT statement, a filter consists of one or more conditions which are made up of expressions combined with logical operators, which are written out exactly like this example:

NAME not equal to 'James' and AGE less than 50

False

In a SQL SELECT statement, which of the following clauses indicates the condition(s) that rows must satisfy so as to be selected into the result set?

WHERE

SELECT, UPDATE and DELETE statements have a clause which allows us to filter the rows that will make it into the result set. Which clause is this?

WHERE

What kind of relations between data elements is supported in a hierarchical data management system?

Parent-child (single-parent)

Which of the following are NOT MySQL Data Types?

Structured types (array, struct)

The MySQL command-line interface or tool ('mysql') allows users to interactively issue commands and SQL statements to be executed by the MySQL database server.

True

In SQL and relational database terminology, a _________ is a "set of tuples sharing the same attributes."

Table

In a relational data management system, real world data is represented as sets of tables or relations.

True

Give an example of a SQL data definition language (DDL) statement.

CREATE TABLE Example (c1 INT PRIMARY KEY, c2 VARCHAR(30) NOT NULL,c3 VARCHAR(30));

What is a Primary Key?

A primary key is one or more entries in a table that can be used to identify each row in a table uniquely.

shortcut meaning: *

All; Every entry

Using the Article_Stats table with the columns and sample data as shown in this image, write a query to retrieve all rows where the likes are greater than 25, or the dislikes are less than 5, or there are no comments.

SELECT * FROM Article_Stats

WHERE likes > 25 OR dislikes < 5 OR comments = 0;

Using the Student table with the following schema, write a query to retrieve the student ID (sid), first name, and last name for all students whose status is 'Active', sorting the results by last name in ascending order.

SELECT sid, fname, lname

FROM Student

WHERE status = 'Active'

ORDER BY lname ASC;

Which of the following queries, using the GROUP BY clause, is the same as the following one? (choose only one)

SELECT state, zip, phone FROM Table1 GROUP BY 3;

SELECT state s, zip z, phone p FROM Table1 GROUP BY p;

Which of the following operators is a Set Operator in SQL?

UNION

Explain what the difference is between an Inner Join and a Full Outer Join.

Inner joins are the intersection of two sets of rows from both tables (but not all), whereas full outer joins keep all records from both tables

What is the output of these two queries based on the table shown below?

a) SELECT count(*) FROM T;

b) SELECT min(votes) FROM T;

a) Will output the number of rows in the table

b) Will output the minimum real value of votes from the table from the 'votes' column

In the Sakila database, one of the tables is the 'payment' table, which has the schema shown below (column names are shown in the middle). Construct a query that shows the total of the payment amounts made by each customer. Show the customer ID, and the total amount paid by each customer.

SELECT customer_id, SUM(amount) AS total_amount FROM payment GROUP BY customer_id;

Using the Employee table, with the columns and sample data as shown in this image, construct a query that returns all employees who work in the same department. You will need to join the Employee table to itself, and each row in the result set should include two different employees and the department.

SELECT e1.employee AS employee_1, e2.employee AS employee_2, e1.department FROM Employee e1 JOIN Employee e2 ON e1.department = e2.department AND e1.employee <> e2.employee;

In the Sakila database, two of the tables are the 'customer' and the 'payment' tables, which have the schemas shown below.

Construct a query that adds all the payments made by each customer. Show the customer ID, the first name, the last name, and the total amount paid by each customer. Sort the results by customer ID in ascending order.

Include all customers even if they don't have records in the payment table. You will need to join the two tables and make sure that you name the customer table first in the FROM clause (what kind of join should you use?). You will need to use table aliases.

SELECT c.customer_id, c.first_name, c.last_name, SUM(p.amount) AS total_paid FROM customer c LEFT JOIN payment p ON c.customer_id = p.customer_id GROUP BY c.customer_id, c.first_name, c.last_name ORDER BY c.customer_id ASC;

According to the 3V framework for discussing Big Data, which one refers to the fact that it is not just text or numeric data but also pictures, music & video streaming that need to be stored.

Variety

A subquery that is not correlated is executed before the outer query.

True

In a SQL transaction, to make all of its statements permanent (saved in the DB), what instruction is used?

COMMIT

A subquery can only be nested inside a SELECT statement.

False

A subquery must be delimited by (written inside) matching ________?

parentheses

We discussed two Big Data products in class, name one of them.

Google BigQuery & Apache Hadoop

In a DB transaction, when we rollback to a specific savepoint (as shown below), the DB server will undo the entire transaction.

False

What is the difference between a Correlated subquery and a Noncorrelated subquery?

A noncorrelated query is self-contained from the outer query while a correlated query references columns from the outer query.

Given the portion of the Sakila DB shown below, use your own words to fully describe what the following query and subquery will produce as a result set.

--

SELECT f.film_id, f.title, f.rating

FROM FILM f

WHERE f.film_id IN

(SELECT fc.film_idFROM FILM_CATEGORY fcJOIN CATEGORY cON fc.category_id = c.category_id

WHERE c.name = 'Romance');

The subquery joins the film category and category tables by the category_id and returns the film_ids with the Romance category. The outer query then retrieves the film_id, title, and rating from the film table, but only for those films whose film_id is in the list of film_ids returned by the subquery (the films that belong to the 'Romance' category).