The log covers basic information about relational database and SQL usage.

Data is organized and stored into tables. A row represents an entire data record within each table. Each column has a name describing the kind of data found in that column.

SQL Usage

Aggregate Function

Common Aggregation Functions

  • count(column_name), returns the total number of rows
  • sum(column_name), returns the added sum of values
  • avg(column_name)
  • max(column_name)
  • min(column_name)

Aggregates Within Clauses

GROUP BY, condenses a group of columns into a single row.

SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name;

HAVING, restricts the groups of rows to only those who meet the specified condition.

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value (optional)
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

###Constraints

Identifying Constraints

The default behavior of database tables can be permissive. Constraints can cover these shortcomings,

  • Prevent NULL values
  • Ensure column values are unique
  • Provide additional validations

Database will automatically assign computer-generated constraint names unless you specifically assign a name to a constraint. Constraint name helps you find it when you choose to alter it. Code snippet below is a sample query with a table constraint,

CREATE TABLE Promotions
(
  id int,
  name varchar(50) NOT NULL,
  category varchar(15),
  CONSTRAINT unique_name UNIQUE (name, category)
);

Value Constraints