NestJS Fundamentals: Day1

Quick Setup

Install NestJS CLI

npm install -g @nestjs/cli

Create project

nest new todo-app

Start development server

npm run start:dev

Generate resource (creates Controller, Service, Module, DTO, Entity)

nest generate resource todos

Basic API Structure

The generated files provide a complete CRUD API: - GET /todos - Get all todos - POST /todos - Create new todo - GET /todos/:id - Get single todo - PATCH /todos/:id - Update todo - DELETE /todos/:id - Delete todo

Day 2: Service Layer and Data Validation

Install Validation Dependencies

npm install class-validator class-transformer

Entity Definition

// src/todos/entities/todo.entity.ts
export class Todo {
  id: number;
  title: string;
  description?: string;
  completed: boolean;
  priority: 'low' | 'medium' | 'high';
  dueDate?: Date;
  createdAt: Date;
  updatedAt: Date;

  constructor(partial: Partial) {
    Object.assign(this, partial);
    this.completed = this.completed || false;
    this.priority = this.priority || 'medium';
    this.createdAt = this.createdAt || new Date();
    this.updatedAt = new Date();
  }
}

Create Todo DTO

// src/todos/dto/create-todo.dto.ts
import { IsString, IsNotEmpty, IsOptional, IsEnum, IsDateString, MaxLength } from 'class-validator';

export class CreateTodoDto {
  @IsString()
  @IsNotEmpty({ message: 'Title is required' })
  @MaxLength(100, { message: 'Title must be 100 characters or less' })
  title: string;

  @IsString()
  @IsOptional()
  @MaxLength(500, { message: 'Description must be 500 characters or less' })
  description?: string;

  @IsEnum(['low', 'medium', 'high'], { message: 'Priority must be low, medium, or high' })
  @IsOptional()
  priority?: 'low' | 'medium' | 'high';

  @IsDateString({}, { message: 'Please provide a valid date format' })
  @IsOptional()
  dueDate?: string;
}

Update Todo DTO

// src/todos/dto/update-todo.dto.ts
import { PartialType } from '@nestjs/mapped-types';
import { IsBoolean, IsOptional } from 'class-validator';
import { CreateTodoDto } from './create-todo.dto';

export class UpdateTodoDto extends PartialType(CreateTodoDto) {
  @IsBoolean({ message: 'Completed status must be true or false' })
  @IsOptional()
  completed?: boolean;
}

Service Implementation

// src/todos/todos.service.ts
import { Injectable, NotFoundException } from '@nestjs/common';
import { CreateTodoDto } from './dto/create-todo.dto';
import { UpdateTodoDto } from './dto/update-todo.dto';
import { Todo } from './entities/todo.entity';

@Injectable()
export class TodosService {
  private todos: Todo[] = [];
  private currentId = 1;

  create(createTodoDto: CreateTodoDto): Todo {
    const todo = new Todo({
      id: this.currentId++,
      title: createTodoDto.title,
      description: createTodoDto.description,
      priority: createTodoDto.priority || 'medium',
      dueDate: createTodoDto.dueDate ? new Date(createTodoDto.dueDate) : undefined,
    });

    this.todos.push(todo);
    return todo;
  }

  findAll(): Todo[] {
    return this.todos;
  }

  findOne(id: number): Todo {
    const todo = this.todos.find(todo => todo.id === id);
    if (!todo) {
      throw new NotFoundException(`Todo with ID ${id} not found`);
    }
    return todo;
  }

  update(id: number, updateTodoDto: UpdateTodoDto): Todo {
    const todoIndex = this.todos.findIndex(todo => todo.id === id);
    if (todoIndex === -1) {
      throw new NotFoundException(`Todo with ID ${id} not found`);
    }

    const existingTodo = this.todos[todoIndex];
    const updatedTodo = new Todo({
      ...existingTodo,
      ...updateTodoDto,
      dueDate: updateTodoDto.dueDate ? new Date(updateTodoDto.dueDate) : existingTodo.dueDate,
      updatedAt: new Date(),
    });

    this.todos[todoIndex] = updatedTodo;
    return updatedTodo;
  }

  remove(id: number): { message: string } {
    const todoIndex = this.todos.findIndex(todo => todo.id === id);
    if (todoIndex === -1) {
      throw new NotFoundException(`Todo with ID ${id} not found`);
    }

    this.todos.splice(todoIndex, 1);
    return { message: `Todo with ID ${id} has been deleted` };
  }

  findByStatus(completed: boolean): Todo[] {
    return this.todos.filter(todo => todo.completed === completed);
  }

  findByPriority(priority: 'low' | 'medium' | 'high'): Todo[] {
    return this.todos.filter(todo => todo.priority === priority);
  }

  getStats(): { total: number; completed: number; pending: number } {
    const total = this.todos.length;
    const completed = this.todos.filter(todo => todo.completed).length;
    const pending = total - completed;
    return { total, completed, pending };
  }
}

Controller Implementation

import { Controller, Get, Post, Body, Patch, Param, Delete, Query, ParseIntPipe, ValidationPipe } from '@nestjs/common';
import { TodosService } from './todos.service';
import { CreateTodoDto, Priority } from './dto/create-todo.dto';
import { UpdateTodoDto } from './dto/update-todo.dto';

@Controller('todos')
export class TodosController {
  constructor(private readonly todosService: TodosService) {}

  @Post()
  create(@Body(ValidationPipe) createTodoDto: CreateTodoDto) {
    // @Body(ValidationPipe) does several things:
    // 1. Extracts JSON from request body
    // 2. Validates it against CreateTodoDto rules
    // 3. Transforms it to CreateTodoDto instance
    // 4. If validation fails, automatically returns 400 error
    return this.todosService.create(createTodoDto);
  }

  @Get()
  findAll(@Query("status")status?: string, @Query("priority")priority?: Priority) {
    if (status === "completed") {
      return this.todosService.findByStatus(true);
    }

    if (status === "pending") {
      return this.todosService.findByStatus(false);
    }
    
    if (priority && ['low', 'medium', 'high'].includes(priority)) {
      return this.todosService.findByPriority(priority);
    }

    return this.todosService.findAll();
  }

  @Get('stats')
  getStats() {
    return this.todosService.getByStats();
  }

  @Get(":id")
  findOne(@Param('id', ParseIntPipe) id: number) {
    // @Param('id') extracts the :id from URL
    // ParseIntPipe converts string "123" to number 123
    // If conversion fails, automatically returns 400 error
    return this.todosService.findOne(+id);
  }

  @Patch(':id')
  update(@Param('id', ParseIntPipe) id: number, @Body(ValidationPipe) updateTodoDto: UpdateTodoDto) {
    return this.todosService.update(+id, updateTodoDto);
  }

  @Delete(':id')
  remove(@Param('id', ParseIntPipe) id: number) {
    return this.todosService.remove(+id);
  }
}

NestJS with Prisma

NestJS with Prisma Setup Guide

Prisma is a next-generation ORM that easily accesses databases with an auto-generated query builder for TypeScript and Node.js. This guide will walk you through setting up Prisma in a NestJS application.

Installation and Initialization

1. Installing Prisma

To get started, install Prisma and the Prisma client:

# Install Prisma as a development dependency
npm i @prisma --save-dev 

# Install Prisma client
npm i @prisma/client 

2. Initialize Prisma

Initialize Prisma in your project by running:

# This will create a new Prisma directory with schema.prisma file
npx prisma init 

3. Configuration

The prisma/schema.prisma file is the main configuration file for Prisma. Here is an example configuration for connecting to a MySQL database:

// schema.prisma
datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

Note: The DATABASE_URL environment variable should be defined in your .env file, and it typically looks something like this:

DATABASE_URL="mysql://root:password@localhost:3306/[database-name]"

4. Migrations

To create an initial migration and apply it to your database:

# Create a new migration and apply it to the database
npx prisma migrate dev --name init

• Every time you make changes to schema.prisma, you need to run npx prisma generate to regenerate the Prisma client.
• If the changes involve structural alterations to the database schema (like adding/removing tables, modifying columns, changing data types, etc.), you should also run npx prisma migrate dev --name [name] to create a new migration file.
• If you only change annotations (e.g., @id, @default) or validation rules in your schema without affecting the underlying database structure, you generally don't need to create a new migration. However, running npx prisma generate is still required to ensure that the Prisma client reflects the latest schema configuration.

Setting Up Prisma in NestJS

5. Creating a Database Module

First, create a new resource for the database service:

# Generate a new resource named 'database' which includes a module, controller, and service
nest g resource database

Modify the generated database.module.ts to provide the Prisma service:

// database.module.ts
import { Module } from '@nestjs/common';
import { DatabaseService } from './database.service';

@Module({
  providers: [DatabaseService],
  exports: [DatabaseService], // Exporting so it can be used in other modules
})
export class DatabaseModule {}

Define the Prisma service in database.service.ts:

// database.service.ts
import { Injectable, OnModuleInit } from '@nestjs/common';
import { PrismaClient } from '@prisma/client';

@Injectable()
export class DatabaseService extends PrismaClient implements OnModuleInit {
  async onModuleInit() {
    await this.$connect(); // Connects to the database on module initialization
  }
}

// Prisma Schema configuration
// prisma/schema.prisma

datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model Employee {
    id        Int       @id @default(autoincrement()) // Primary key with auto-increment
    name      String    
    email     String    @unique // Unique constraint on email field
    role      Role      // Enum type Role
    createdAt DateTime  @default(now()) // Set to current time on creation
    updatedAt DateTime  @updatedAt // Automatically updates on record modification
}

enum Role {
    ADMIN
    ENGINEER
    INTERN
}

6. Using the Prisma Client in Other Services

To use the Prisma client, import the DatabaseModule in the module where you want to use it.

// employees.module.ts
import { Module } from '@nestjs/common';
import { EmployeesService } from './employees.service';
import { EmployeesController } from './employees.controller';
import { DatabaseModule } from 'src/database/database.module';

@Module({
  imports: [DatabaseModule], // Import the DatabaseModule
  controllers: [EmployeesController],
  providers: [EmployeesService],
})
export class EmployeesModule {}

7. Creating a Service with Prisma Integration

Basic EmployeesService using Prisma for database operations:

// employees.service.ts
import { Injectable } from '@nestjs/common';
import { Prisma, Role } from '@prisma/client';
import { DatabaseService } from 'src/database/database.service';

@Injectable()
export class EmployeesService {
  constructor(private readonly databaseService: DatabaseService) {}

  async create(createEmployeeDto: Prisma.EmployeeCreateInput) {
    return this.databaseService.employee.create({ data: createEmployeeDto });
  }

  async findAll(role?: Role) {
    if (role) {
      return this.databaseService.employee.findMany({ where: { role } });
    }
    return this.databaseService.employee.findMany();
  }

  async findOne(id: number) {
    return this.databaseService.employee.findUnique({ where: { id } });
  }

  async update(id: number, updateEmployeeDto: Prisma.EmployeeUpdateInput) {
    return this.databaseService.employee.update({
      where: { id },
      data: updateEmployeeDto,
    });
  }

  async remove(id: number) {
    return this.databaseService.employee.delete({ where: { id } });
  }
}

8. Create Controller

The corresponding controller would handle incoming requests and use the service methods:

// employees.controller.ts
import {
  Controller,
  Get,
  Post,
  Body,
  Patch,
  Param,
  Delete,
  ParseIntPipe,
  Query,
  ValidationPipe,
} from '@nestjs/common';
import { EmployeesService } from './employees.service';
import { Prisma, Role } from '@prisma/client';

@Controller('employees')
export class EmployeesController {
  constructor(private readonly employeesService: EmployeesService) {}

  @Post()
  create(@Body(ValidationPipe) createEmployeeDto: Prisma.EmployeeCreateInput) {
    return this.employeesService.create(createEmployeeDto);
  }

  @Get()
  findAll(@Query('role') role?: Role) {
    return this.employeesService.findAll(role);
  }

  @Get(':id')
  findOne(@Param('id', ParseIntPipe) id: number) {
    return this.employeesService.findOne(id);
  }

  @Patch(':id')
  update(
    @Param('id', ParseIntPipe) id: number,
    @Body() updateEmployeeDto: Prisma.EmployeeUpdateInput,
  ) {
    return this.employeesService.update(id, updateEmployeeDto);
  }

  @Delete(':id')
  remove(@Param('id', ParseIntPipe) id: number) {
    return this.employeesService.remove(id);
  }
}

NestJS notes

NestJS Notes

NestJS is a progressive Node.js framework for building efficient, reliable, and scalable server-side applications. It's built with TypeScript and uses strong design patterns like modules, controllers, and services.

Installation and Setup

To get started with NestJS, you'll need to install the CLI and create a new project:

# Install NestJS CLI globally
npm i -g @nestjs/cli 

# Create a new NestJS project
nest new [project-name]

# Create a new module
nest g module [module-name]

# Create a new controller
nest g controller [controller-name]

# Create a new service
nest g service [service-name]

# Create a new resource (module, controller, and service together)
nest g resource [resource-name]

Core Concepts

1. Decorators

Decorators in NestJS (indicated by the @ symbol) are special functions that can modify classes and methods. They are used to define a class as a module, controller, or service.

// A basic controller using a decorator
import { Controller, Get } from '@nestjs/common';

@Controller('users') // Defines the route as /users
export class UsersController {
  @Get() // Handles GET requests to /users
  getAllUsers() {
    return "This will return all users";
  }
}

2. Controllers

Controllers are responsible for handling incoming requests and returning responses to the client. They usually define the routes of your application.

3. Services

Services contain the business logic of the application. They can be injected into controllers to handle complex tasks, such as database operations.

// A simple service
import { Injectable } from '@nestjs/common';

@Injectable()
export class UsersService {
  private users = [];

  createUser(user) {
    this.users.push(user);
  }

  getUsers() {
    return this.users;
  }
}

4. Decorators for Request Handling

Decorators can also be used inside methods to extract request parameters, body, or query data.

• @Param('id') extracts a route parameter.
• @Body() extracts the body of the request.
• @Query('name') extracts query parameters.

// Example using different decorators
import { Controller, Get, Param, Body, Query } from '@nestjs/common';

@Controller('users')
export class UsersController {
  @Get(':id')
  getUserById(@Param('id') id: string) {
    return `This action returns a user with ID ${id}`;
  }

  @Get()
  getUserByName(@Query('name') name: string) {
    return `This action returns users with name ${name}`;
  }

  @Post()
  createUser(@Body() user: any) {
    return `This action creates a user with name ${user.name}`;
  }
}

5. Pipes

Pipes transform or validate the data before it is handled by the route handler.

• ParseIntPipe ensures that a parameter is an integer.
• ValidationPipe validates incoming data against the defined DTO (Data Transfer Object) schema.

// Example of using ParseIntPipe
@Get(':id')
getUserById(@Param('id', ParseIntPipe) id: number) {
  return `This action returns a user with ID ${id}`;
}

6. DTOs (Data Transfer Objects)

DTOs define the shape of the data that flows in and out of the application. They can include validation rules using libraries like class-validator.

// Example DTO with validation
import { IsString, IsEmail } from 'class-validator';

export class CreateUserDto {
  @IsString()
  name: string;

  @IsEmail()
  email: string;
}

7. Validation and Transformation

To use DTO validation with class-validator and transformation with class-transformer:

# Install the required packages
npm i class-validator class-transformer

// Example of using class-transformer and class-validator
import { Transform } from 'class-transformer';
import { IsString, IsEmail } from 'class-validator';

export class CreateUserDto {
  @IsString()
  name: string;

  @IsEmail()
  email: string;

  @Transform(({ value }) => value.toLowerCase())
  password: string;
}

8. Exception Handling

NestJS has built-in support for exception handling. You can create custom exceptions by extending HttpException or use built-in ones like NotFoundException.

// users.service.ts
import { NotFoundException } from '@nestjs/common';

...
  findAll(role?: 'INTERN' | 'ENGINEER' | 'ADMIN') {
    if (role) {
      const roleArray = this.users.filter((user) => user.role === role);
      if (roleArray.length === 0) {
        throw new NotFoundException('Role not found');
      }
      return roleArray;
    }

    return this.users;
  }

9. Global Prefix

A global prefix can be set for all routes in the application, making it easier to version APIs.

// Setting a global prefix for all routes
// main.ts
...
const app = await NestFactory.create(AppModule);
app.setGlobalPrefix('api'); // All routes will be prefixed with /api
await app.listen(3000);

With the global prefix set, the route defined as:

@Controller('notes')
export class NotesController {}

Will become /api/notes.

Example:

// users.module.ts

import { Module } from '@nestjs/common';
import { UsersController } from './users.controller';
import { UsersService } from './users.service';

@Module({
  imports: [],
  controllers: [UsersController],
  providers: [UsersService],
})
export class UsersModule {}

// users.service.ts
import { Injectable, NotFoundException } from '@nestjs/common';
import { CreateUserDto } from './dto/create-user.dto';
import { UpdateUserDto } from './dto/update-user.dto';

@Injectable() // This decorator is used to define a class as a provider.
export class UsersService {
  private users = [
    {
      id: 1,
      name: 'Leanne Graham',
      email: 'Sincere@april.biz',
      role: 'INTERN',
    },
    {
      id: 2,
      name: 'Ervin Howell',
      email: 'Shanna@melissa.tv',
      role: 'INTERN',
    },
    {
      id: 3,
      name: 'Clementine Bauch',
      email: 'Nathan@yesenia.net',
      role: 'ENGINEER',
    },
    {
      id: 4,
      name: 'Patricia Lebsack',
      email: 'Julianne.OConner@kory.org',
      role: 'ENGINEER',
    },
    {
      id: 5,
      name: 'Chelsey Dietrich',
      email: 'Lucio_Hettinger@annie.ca',
      role: 'ADMIN',
    },
  ];

  findAll(role?: 'INTERN' | 'ENGINEER' | 'ADMIN') {
    if (role) {
      const roleArray = this.users.filter((user) => user.role === role);
      if (roleArray.length === 0) {
        throw new NotFoundException('Role not found');
      }
      return roleArray;
    }

    return this.users;
  }

  findOne(id: number) {
    const user = this.users.find((user) => user.id === id);

    if (!user) {
      throw new NotFoundException('User not found');
    }

    return user;
  }

  findOneByName(name: string) {
    return this.users.find((user) => user.name === name);
  }

  create(createUserDto: CreateUserDto) {
    // get the user with the highest id
    const usersByHighestId = [...this.users].sort((a, b) => b.id - a.id);
    const newUser = {
      id: usersByHighestId[0].id + 1,
      ...createUserDto,
    };

    this.users.push(newUser);
    return newUser;
  }

  update(id: number, updateUserDto: UpdateUserDto) {
    this.users = this.users.map((user) => {
      if (user.id === id) {
        return { ...user, ...updateUserDto };
      }

      return user;
    });

    return this.findOne(id);
  }

  remove(id: number) {
    const removeUser = this.findOne(id);
    this.users = this.users.filter((user) => user.id !== id);

    return removeUser;
  }
}

// users.controller.ts
import {
  Body,
  Controller,
  Delete,
  Get,
  Param,
  ParseIntPipe,
  Patch,
  Post,
  Query,
  ValidationPipe,
} from '@nestjs/common';
import { UsersService } from './users.service';
import { CreateUserDto } from './dto/create-user.dto';
import { UpdateUserDto } from './dto/update-user.dto';

@Controller('users')
export class UsersController {
  constructor(private readonly usersService: UsersService) {}


  @Get()
  findAll(@Query('role') role?: 'INTERN' | 'ENGINEER' | 'ADMIN') {
    return this.usersService.findAll(role);
  }

  @Get(':id')
  findOne(@Param('id', ParseIntPipe) id: number) {
    return this.usersService.findOne(id);
  }

  @Post()
  create(
    @Body(ValidationPipe)
    createUserDto: CreateUserDto,
  ) {
    return this.usersService.create(createUserDto);
  }

  @Patch(':id')
  update(
    @Param('id', ParseIntPipe) id: number,
    @Body(ValidationPipe)
    updateUserDto: UpdateUserDto,
  ) {
    return this.usersService.update(id, updateUserDto);
  }

  @Delete(':id')
  remove(@Param('id', ParseIntPipe) id: number) {
    return this.usersService.remove(id);
  }
}

// dto/create-user.dto.ts
import { IsEmail, IsEnum, IsNotEmpty, IsString } from 'class-validator';

export class CreateUserDto {
  @IsString()
  @IsNotEmpty()
  name: string;

  @IsEmail()
  email: string;

  @IsEnum(['INTERN', 'ENGINEER', 'ADMIN'])
  role: 'INTERN' | 'ENGINEER' | 'ADMIN';
}

// dto/update-user.dto.ts
import { CreateUserDto } from './create-user.dto';
import { PartialType } from '@nestjs/mapped-types';

export class UpdateUserDto extends PartialType(CreateUserDto) {}

MySQL Notes

MySQL Terminal Notes

---

1. Keys

---

Primary Key

A primary key is a column or a set of columns that uniquely identifies each row in a table. A table can have only one primary key.

Example:

CREATE TABLE my_table (id INT PRIMARY KEY, name VARCHAR(255), age INT);

Foreign Keys

A foreign key is a column or set of columns that establishes a link between two tables by referencing the primary key of another table.

Example:

CREATE TABLE my_table (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    age INT,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

Composite Keys

A composite key is a primary key that consists of multiple columns. It is used when a single column is not sufficient to uniquely identify a row.

Example:

CREATE TABLE my_table (
    id INT,
    name VARCHAR(255),
    age INT,
    PRIMARY KEY (id, name)
);

2. Database Operations

Create Database

To create a new database, use the following command:

Example:

CREATE DATABASE my_database;

Delete a database

To delete a database, use the following command:

Example:

DROP DATABASE my_database;

Select a Database

To select a database, use the following command:

Example:

USE my_database;

Display All Databases

To display all databases, use the following command:

Example:

SHOW DATABASES;

3. Table Operations

Create Table

To create a new table, use the following command:

Example:

CREATE TABLE my_table (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    age INT
);

Create Table with Constraints

To create a table with constraints, use the following command:

Example:

CREATE TABLE my_table (
    id INT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age INT CHECK (age >= 18)
);

-- type of constraints: PRIMARY KEY, UNIQUE, NOT NULL, CHECK, DEFAULT, FOREIGN KEY

Delete Table

To delete a table, use the following command:

Example:

DROP TABLE my_table;

Display All Tables

To display all tables in a database, use the following command:

Example:

SHOW TABLES;

Insert Data

To insert data into a table, use the following command:

Example:

INSERT INTO my_table  VALUES (1, 'John Doe', 30);

Insert Data to Specific Columns

To insert data into specific columns of a table, use the following command:

Example:

INSERT INTO my_table (name, age) VALUES ('Jane Doe', 25);

Add a New Column

To add a new column to a table, use the following command:

Example:

ALTER TABLE my_table ADD email VARCHAR(255);

Delete a Column

To delete a column from a table, use the following command:

Example:

ALTER TABLE my_table DROP COLUMN email;

4. Data Manipulation

Update Data in a Table

To update data in a table, use the following command:

Example:

UPDATE my_table SET age = 26 WHERE name = 'John';

Update Multiple Columns

To update multiple columns in a table, use the following command:

Example:

UPDATE my_table SET age = 26, name = 'Jane' WHERE id = 1;

Delete Data from a Table

To delete data from a table, use the following command:

Example:

DELETE FROM my_table WHERE id = 1;

Retrieve Data from a Table

To retrieve data from a table, use the following command:

Example:

SELECT * FROM my_table;

Retrieve Specific Columns

To retrieve specific columns from a table, use the following command:

Example:

SELECT name, age FROM my_table;

Retrieve Data in Order

To retrieve data from a table in a specific order, use the following command:

Example:

SELECT * FROM my_table ORDER BY age DESC;

5. Queries with Conditions

WHERE Clause

The WHERE clause is used to filter records based on a specified condition.

Example:

SELECT * FROM my_table WHERE age > 25;

Using Order By and Limit

To retrieve a limited number of records from a table in a specific order, use the following command:

Example:

SELECT * FROM my_table ORDER BY age DESC LIMIT 5;

6. Joins

Inner Joins

An inner join is used to combine rows from two or more tables based on a related column between them.

Example:

SELECT orders.order_id, customers.customer_name
FROM orders
JOIN customers ON orders.customer_id = customers.customer_id;

Left Joins

A left join returns all rows from the left table and the matched rows from the right table. The result is NULL from the right side if there is no match.

Example:

SELECT orders.order_id, customers.customer_name
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.customer_id;

output:
order_id | customer_name
1       | John Doe
2       | Jane Doe
3       | NULL

Right Joins

A right join returns all rows from the right table and the matched rows from the left table. The result is NULL from the left side if there is no match.

Example:

SELECT orders.order_id, customers.customer_name
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.customer_id;

output:
order_id | customer_name
1       | John Doe
2       | Jane Doe
NULL    | Alice Smith

Full Outer Joins

A full outer join returns all rows when there is a match in either the left or right table. The result is NULL from both sides when there is no match.

Example:

SELECT orders.order_id, customers.customer_name
FROM orders
FULL OUTER JOIN customers ON orders.customer_id = customers.customer_id;

output:
order_id | customer_name
1       | John Doe
2       | Jane Doe
NULL    | Alice Smith
3       | NULL

7. Aggregate Functions

Count

The COUNT function is used to count the number of rows in a table.

Example:

SELECT COUNT(*) FROM my_table;

Sum

The SUM function is used to calculate the sum of a column.

Example:

SELECT SUM(salary) FROM employees;

Average

The AVG function is used to calculate the average value of a column.

Example:

SELECT AVG(salary) FROM employees;

Min and Max

The MIN and MAX functions are used to find the minimum and maximum values in a column, respectively.

Example:

SELECT MIN(salary) FROM employees;
SELECT MAX(salary) FROM employees;

8. Group By

The GROUP BY clause is used to group rows that have the same values into summary rows.

Example:

SELECT department, COUNT(*) FROM employees GROUP BY department;

output:
department | count
HR         | 5
IT         | 10

9. Subqueries

A subquery is a query within another query. It can be used to return values that are used in the main query.

Example:

SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

10. Wildcards

The % wildcard is used to match any sequence of characters.

Example:

SELECT * FROM employees WHERE name LIKE 'J%';

The _ wildcard is used to match any single character.

Example:

SELECT * FROM employees WHERE name LIKE 'J_n';

10. Unions

The UNION operator is used to combine the result sets of two or more SELECT statements.

Example:

-- Find a list of employee and branch names
SELECT employee.first_name AS Employee_Branch_Names FROM employee
UNION
SELECT branch.branch_name FROM branch;

-- Find a list of all clients & branch suppliers' names
SELECT client.client_name AS Non-Employee_Entities FROM client
UNION
SELECT branch_supplier.supplier_name FROM branch_supplier;

11. Nested Queries

Example:

SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE name = 'IT');

12. Triggers

A trigger is a set of SQL statements that are automatically executed when a specified event occurs.

Example:

Delimiter //
CREATE TRIGGER my_trigger
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
END //
Delimiter ;

Basic Query Syntax

-- Find all employees
SELECT *
FROM employee;

-- Find all clients
SELECT *
FROM clients;

-- Find all employees ordered by salary
SELECT *
from employee
ORDER BY salary ASC/DESC;

-- Find all employees ordered by sex then name
SELECT *
from employee
ORDER BY sex, name;

-- Find the first 5 employees in the table
SELECT *
from employee
LIMIT 5;

-- Find the first and last names of all employees
SELECT first_name, employee.last_name
FROM employee;

-- Find the forename and surnames names of all employees
SELECT first_name AS forename, employee.last_name AS surname
FROM employee;

-- Find out all the different genders
SELECT DISCINCT sex
FROM employee;

-- Find all male employees
SELECT *
FROM employee
WHERE sex = 'M';

-- Find all employees at branch 2
SELECT *
FROM employee
WHERE branch_id = 2;

-- Find all employee's id's and names who were born after 1969
SELECT emp_id, first_name, last_name
FROM employee
WHERE birth_day >= 1970-01-01;

-- Find all female employees at branch 2
SELECT *
FROM employee
WHERE branch_id = 2 AND sex = 'F';

-- Find all employees who are female & born after 1969 or who make over 80000
SELECT *
FROM employee
WHERE (birth_day >= '1970-01-01' AND sex = 'F') OR salary > 80000;

-- Find all employees born between 1970 and 1975
SELECT *
FROM employee
WHERE birth_day BETWEEN '1970-01-01' AND '1975-01-01';

-- Find all employees named Jim, Michael, Johnny or David
SELECT *
FROM employee
WHERE first_name IN ('Jim', 'Michael', 'Johnny', 'David');

-- Find the number of employees
SELECT COUNT(super_id)
FROM employee;

-- Find the average of all employee's salaries
SELECT AVG(salary)
FROM employee;

-- Find the sum of all employee's salaries
SELECT SUM(salary)
FROM employee;

-- Find out how many males and females there are
SELECT COUNT(sex), sex
FROM employee
GROUP BY sex

-- Find the total sales of each salesman
SELECT SUM(total_sales), emp_id
FROM works_with
GROUP BY emp_id;

-- Find the total amount of money spent by each client
SELECT SUM(total_sales), client_id
FROM works_with
GROUP BY client_id;

-- Find the first_name, last_name, and total amount of sales for each employee
SELECT employee.first_name, employee.last_name, SUM(works_with.total_sales) FROM employee JOIN works_with ON works_with.emp_id = employee.emp_id GROUP BY works_with.emp_id;

Vocabulary: Model-View-Controller (MVC)

MVC (Model-View-Controller):

MVC is a design pattern commonly used in software development to organize the structure of an application. It divides an application's components into three main parts:

Model: The Model represents the data and the logic to manipulate that data. It's responsible for managing the application's data and business logic. This can include data storage, retrieval, validation, and other operations that deal with the data itself.

View: The View is responsible for presenting the data to the user. It displays the information and interacts with the user interface. The View is often concerned with how the data is displayed and doesn't contain the business logic.

Controller: The Controller acts as an intermediary between the Model and the View. It receives input from the user through the View, processes that input by interacting with the Model, and updates the View accordingly. The Controller handles user interactions and orchestrates the flow of data and actions between the Model and the View.

(from ChatGPT)

Vocabulary: Call signature

A call signature in TypeScript is a way to define the type of a function or method within an interface or type. It specifies the shape of a function, including the parameter types and the return type. This allows you to describe the signature of functions that can be called, including their arguments and return values. (from ChatGPT)

interface MyFunction {
  (param1: number, param2: string): boolean;
}

const myFunc: MyFunction = (num, str) => {
  return num > parseInt(str);
};

console.log(myFunc(5, '3')); // Output: true

Vocabulary: Index signature

In TypeScript, an index signature (also known as an indexable type) allows you to define the types of properties that are not known in advance but can be accessed using square bracket notation. Index signatures are particularly useful when working with objects that have dynamic property names. (from ChatGPT)

interface MyObject {
  [key: string]: number;
}

const myData: MyObject = {
  apple: 5,
  banana: 3,
};

console.log(myData.apple); // Output: 5
console.log(myData.banana); // Output: 3