SQL Constraints

• Constraints in SQL are rules applied to a column in a table to control the kind of data that can be stored in that column.

NOT NULL Constraint

• Ensures that a column cannot have a NULL value.
• Syntax:

  CREATE TABLE Users (
      UserID INT NOT NULL,
      Name VARCHAR(100) NOT NULL
  );

DEFAULT Constraint

• Assigns a default value to a column if no value is provided during insert.
• Syntax:

  CREATE TABLE Products (
      ProductID INT,
      Price DECIMAL(10, 2) DEFAULT 0.00
  );

UNIQUE Constraint

• Ensures that all values in a column (or group of columns) are distinct.
• Syntax:

  CREATE TABLE Employees (
      Email VARCHAR(255) UNIQUE
  );

PRIMARY KEY Constraint

• Uniquely identifies each row in a table.
• Automatically applies NOT NULL + UNIQUE.
• Syntax:

  CREATE TABLE Orders (
      OrderID INT PRIMARY KEY
  );

FOREIGN KEY Constraint

• Enforces a link between two tables.
  • Ensures that the value in one column matches a value in another table’s primary key.
• Syntax:

Single Row Functions

• A single-row function is a SQL function that takes input from one row and returns a single value for that row.

Key Characteristics:

• Operates on one row at a time.
• Returns one result per input row.
• Can be used in SELECT, WHERE, ORDER BY, etc.

Different Categories:

• General Functions
• Case Conversion Functions
• Character Functions
• Date Functions
• Number Functions

General Functions

| FUNCTION | DESCRIPTION | EXAMPLE |
| ---------------------------- | ----------------------------------------------------- | -------------------------------- | -------- |
| ISNULL(expression, replacement) | Replaces NULL with a specified value | ISNULL(NULL, 'N/A') → 'N/A' |
| COALESCE(expr1, expr2, …) | Returns the first non-null value | COALESCE(NULL, NULL, 'A') → 'A' |
| CAST(expression AS datatype) | Converts one data type to another | CAST(123 AS VARCHAR) | | CONVERT(datatype, expression, style) | Converts data type with style (useful for dates) | CONVERT(VARCHAR, 456) → '456' |
| NULLIF(expr1, expr2) | Returns NULL if both expressions are equal | NULLIF(5, 5) → NULL |

• These handle nulls, data types, and general operations.

CASE CONVERSION Functions

• Change the letter case in string data.

Character Functions

• These manipulate string data.

More Character Functions

• These manipulate string data.

Date Functions

• Work with and extract parts of date/time values.

More Date Functions

• Work with and extract parts of date/time values.

Number Functions

• Perform operations on numeric values.

Aggregating Data Using the Group Function

• Aggregating data is the process of summarizing or combining data from multiple rows into a single result per group of related values, usually by using aggregate functions.
• Aggregate functions include: COUNT(), SUM(), AVG(), MAX(), MIN()
• To perform aggregation by group, we use the GROUP BY clause.

GROUP BY Clause

• Used to group rows that have the same values in specified columns.
• Commonly used with aggregate functions (like COUNT(), SUM(), AVG(), MAX(), MIN()).
• Syntax:

What it does (GROUP BY)?

• Groups the EMPLOYEES by DEPARTMENT.
• Result: Counts how many employees are in each department.

DISTINCT

• Removes duplicate values from the result set.
• It's used when you want unique values only.
• Syntax:

  SELECT DISTINCT Department
  FROM Employees;

What it does (DISTINCT)?

• Checks the DEPARTMENT Column
• Result: Returns each department only once, even if multiple employees are in the same department.

HAVING Clause

• Is used to filter groups after they’ve been created using group by.
• Use WHERE to filter rows before grouping, and HAVING to filter groups after grouping.
• Syntax:

What it does (HAVING Clause)?

• Groups by department.
• Result: Counts how many employees are in each group.
• Only shows groups where the count is greater than 3.

Brackets

What are brackets used for?

• Passing arguments to functions
• Grouping of operations

What coding languages use brackets?

• JavaScript
• HTML
• CSS

Grouping and priority of operations

• Priority
• Modification
• Resolution
• Nested

Why are brackets important?

• Nesting support
• Grouping and clarity
• Error prevention
• Flow control

Use of brackets according to the code:

• JavaScript: 25%
• HTML: 50%
• CSS: 75%

Exercise one

• Different types of Brackets are mentioned that is needed to be identified.

Exercise two

• Explain whether the expressions are balanced or not and correct them in the indicated area

Use of square brackets

• Data
• Arrays
• Code blocks
• JSON

Exercise three

• Match each bracket with its corresponding use
  -( ) For side comments
• [ ] For additional information
• < > For edits to quotations
• { } For listing choices

Types of brackets

#