Section 6.2 > Database Operations

Question 1

What does SQL stand for?

Answer 1

Structured Query Language

Question 2

In a SQL query, what is the FROM keyword used for?

Answer 2

Specifies which table to select or delete data from

Question 3

In a SQL query, what is the SELECT keyword used for?

Answer 3

Selects data from a database

Question 4

In a SQL query, what is the UNION keyword used for?

Answer 4

Combines the result set of two or more SELCT statements (only distinct values, they cannot be mixed)

Question 5

In a SQL query, what is the keyword WHERE used for?

Answer 5

Filters a result set to include only records that fulfill a specified condition

Question 6

In a SQL query, what is the JOIN keyword used for?

Answer 6

Joins tables

Question 7

What is product operation?

Answer 7

The product operation creates a result table that includes all of the attributes from the two tables; each row of the second table is added to each row of the first table.

SELECT Shifts.WingSegment, Shifts.Shift,
NurseName.LastName

FROM Shifts

CROSS JOIN NurseName;

Question 8

What is join operation?

Answer 8

A join operation combines two tables, but records are only appended when a matching criterion is met. The result table includes a row with the attributes of both tables only when attributes from the first database table match related attributes from the second database table.

SELECT*
FROM Patient CROSS JOIN Nurse;

Question 9

What are object-oriented (OO) databases?

Answer 9

Object-oriented (OO) databases store complex data and relationships between data directly without using inter-table relations.

An object database is a collection of objects, each with two components: data and instructions. The instructions, or software programs called methods, define what to do with the data in the object.

Question 10

What are NoSQL?

Answer 10

Short for not only SQL, databases can accommodate a wide variety of data models, including key-value, document, columnar, and graph formats. NoSQL is an alternative to traditional relational databases in which data is placed in tables that are carefully designed before the database is built. NoSQL databases are especially useful for working with large sets of distributed data.

In key-value databases (also referred to as stores), each item in the database is stored as an attribute name (or key) together with its value.

Question 11

What is Data Lake?

Answer 11

A data lake is a system of data stored in raw format. It is usually a single store of all enterprise data as well as information derived from data during reporting, transactions, or other activities. Data lakes can include both structured data, such as tables, and unstructured data, such as audio and video recordings.

Question 12

Selection Operation

Answer 12

The selection operation takes rows from one table and creates a new table. The user specifies the table from which the rows are taken and tests the selection criterion. The selection criterion is tested against each row, and when a condition is met, that row is included in the result table. Tests use attribute names, constants, and relational operators, such as AND and OR, which define the criteria. In this example, the syntax is searching for the patient record with the patient ID ‘223344.’:

SELECT * FROM Patient
WHERE PatientID = ‘223344’;

The SELECT statement identifies the records that are being requested, and the asterisk (*) means everything from that table. The parameter after FROM identifies the table name (Patient). The next keyword, WHERE, is the condition the query is requesting.

Question 13

Union Operation

Answer 13

The union operation combines distinct fields from multiple tables that have the same set of attributes and data types. For example, if one column accepts integers and another accepts variable characters, they would not be compatible for a union.

SELECT PatientID, LastName FROM Patient
UNION
SELECT NurseID, LastName FROM Nurse;

Question 14

Product Operation

Answer 14

The product operation creates a result table that includes all of the attributes from the two tables; each row of the second table is added to each row of the first table.

SELECT Shifts.WingSegment, Shifts.Shift, NurseName.LastName
FROM Shifts
CROSS JOIN NurseName;

Notice when you are using more than one table as a source, the table name precedes the attribute name, separated by a period. Shifts.WingSegment refers to the field ‘WingSegment’ from the table ‘Shifts.’

Question 15

Join Operation

Answer 15

A join operation combines two tables, but records are only appended when a matching criterion is met. The result table includes a row with the attributes of both tables only when attributes from the first database table match related attributes from the second database table.

SELECT*
FROM Patient, Nurse;
This syntax selects all the fields from both ‘Patient’ and ‘Nurse’ tables, and joins them into one large table. This is referred to as an implicit join. Alternatively, to get the same results, an explicit join can be used as shown below.

SELECT*
FROM Patient CROSS JOIN Nurse;