[CS] Schema & Query Design Day-76

What is Schema?

A description of how data is organized in a database and the relationships between different entities.

Entity?

An Entity is a unique unit of information. It can be represented as a table in the database.

Field?

There are Fields that describe the properties of an Entity. Field corresponds to Column.
(필드는 열에 해당됩니다.)

Record?

Items stored in Table. Record corresponds to Row. If Column has Country, Row has South Korea, Japan, America, etc....

Relational database

• data: all the single items that are stored in a database, either individually or as a set.

• table: Tables are database objects that contain all the data in a database.

• key: Key is an attribute or a set of attributes that help to uniquely identify row in table.

relationships between database tables

• One-to-one: Both tables can have only one record on either side of the relationship. (Normally not use a lot)

• One-to-many: The primary key table contains only one record that relates to none, one, or many records in the related table. This relationship is similar to the one between you and a parent. You have only one mother, but your mother may have several children.

• Many-to-many: For instance, if you have several siblings, so do your siblings (have many siblings). Many-to-many relationships require a third table, known as an associate or linking table, because relational systems can’t directly accommodate the relationship.

SQL Query grammer

GROUP BY
When searching for data, group it and search it.

SELECT * FROM customers GROUP BY State;

You can group by state.

HAVING
You can filter the searched results with GROUP BY.

SELECT CustomerId, AVG(Total) FROM invoices GROUP BY CustomerId HAVAING AVG(Total) > 6.00;

Group the CustomerId in the invoices table and look up the result with an average greater than 6.

COUNT
Used to check the number of records.

SELECT *, COUNT(*) FROM customers GROUP BY State;

SUM()
Returns the sum of Records.

SELECT InvoiceId, SUM(UnitPrice) FROM invoice_items GROUP BY InvoiceId;

In a Table called invoice_items, group by InvoiceId Field and sum the UnitPrice Field values.

AVG()
The AVG function is a function that calculates the average value of Records.

SELECT TrackId, AVG(UnitPrice) FROM invoice_itmes GROUP BY TrackId;

MAX(), MIN()
Returns the maximum and minimum values.

SELECT CustomerId, MIN(Total) FROM invoices GROUP BY CustomerId;