The SQL Database Environment Trivia Quiz

Answer: Invoice Number

A primary key is a column that uniquely identifies a row and should have no duplicates. For instance, if an invoice number is SB1021, there cannot be another row containing that value for that column. Invoice numbers go in that column, and they uniquely identify one specific sale.

Answer: relational model

The relational model was designed at IBM by E.F. Codd in a paper entitled "A Relational Model of Data for Large Shared Databanks".

Answer: Db2

Db2 was released in 1983. It was one of the first database management systems to use the relational model.

Answer: 'Sylvia'

The VARCHAR data type is used for data entries in the form of text. If the length given is greater than the number of characters in the text data entry, the length that is left over will occupy empty space. In this case, "Sylvia" has 6 characters, and the length is 7. The spot for the seventh character will be left vacant.

Depending on the database management system, attempting to assign a numeric value to a VARCHAR field would either give an error or convert the number to a text (possibly losing accuracy or even the correct value if the field is too short).

Answer: INSERT

The INSERT keyword creates a row with values that corresponds with the column's attributes. Here is an SQL syntax example for the INSERT keyword:

INSERT INTO table EMPLOYEE VALUES (234, 'Sanders', 'Ashley', 'N', '20-Jun-22');

In this example, Ashley Nicole Sanders' employee ID is 234 and her hire date is June 20, 2022. So, EmployeeID=234, EmployeeLastName='Sanders', EmployeeFirstName='Ashley', MiddleInitial='N', and HireDate= '20-Jun-22'.

Answer: STUDENT

Since there is no foreign key that refers to the STUDENT table, it can be dropped first. That makes CLASSROOM and BUS independent, and either of those tables can be dropped anytime. TEACHER is still connected to CLASSROOM, but CLASSROOM no longer connected to a table with a foreign key, so it is now droppable. So either CLASSROOM or BUS can be dropped after STUDENT. That leaves TEACHER and CLASSROOM (if BUS is dropped) or TEACHER and BUS (if classroom is dropped). If TEACHER and CLASSROOM remain, TEACHER will be the last table to be dropped since CLASSROOM has a foreign key that connects to teacher, making it being the next table to be dropped. If TEACHER and BUS remain, both tables can be dropped in any order since they are now both independent.

Answer: alias

Sometimes columns and tables are given aliases for the sake of making an SQL statement more readable. Aliases can be created by using the AS keyword. Here is an example of an SQL query using aliases:

SELECT TEAM_COLORS AS COLORS
FROM TEAM AS T;

Even though you can create an alias for a table, table names are not passed back to the caller and are typically not needed.

Answer: False

The COUNT aggregate function only returns the number of rows with non-null values. For example, if your table has 30 rows and 6 of them have null values, the COUNT aggregate function will return 24 rows.

Answer: Distributed

Desktop, workgroup, and enterprise databases are defined by the number of end users. Desktop databases support one user (or a very small number of external connections, but these are often very slow). Workgroup database typically support a small to medium number of people such as committee and sub committee members. Enterprise databases support companies consisting of hundreds of people. Centralized and distributed databases are location based rather than based on how many people use the database.

A centralized database has one server while distributed systems use multiple servers which synchronize data between themselves.

Answer: Inner Join

An inner join in SQL is equivalent to the intersection of two sets in a Venn diagram. Typically values of one table's primary key matches the values of another table's foreign key. The query will return the selected rows based on common attribute values between them.

The left join will return rows with all attributes found on the left side (the right side can be identical or empty), whereas the right join will return all attributes found on the right side. As for the full join, it returns rows for all attributes found in both the left table and right table whether they are shared between the tables or not.