Full Join & Cross Join
FULL JOIN: A FULL JOIN, also known as a FULL OUTER JOIN, combines the results of both the LEFT JOIN and the RIGHT JOIN. It returns all rows from both tables, along with matched rows and unmatched rows from each table. If a row doesn’t have a match in the other table, the missing columns will contain NULL values. Syntax: SELECT column_listFROM table1FULL JOIN table2ON table1.column = table2.column; Example From Todo Project: SELECTusers.name,todos.user_idFROMusersFULL JOIN todosON users.id = todos.user_id CROSS JOIN: A CROSS JOIN, also known as a Cartesian join, combines every row from the first table with every row from the second table. It produces a result set where the number of rows is the product of the number of rows in each table. SELECT column_listFROM table1CROSS JOIN table2; Example From Todo Project:- SELECT users.name, todos.title FROM users CROSS JOIN todos; It’s important to note that CROSS JOINs can lead to a large number of results, especially when joining large tables. Therefore, they should be used carefully and usually in specific scenarios where you explicitly want to combine every row with every other row.
Simple Aggregate Functions
COUNT: The COUNT function is used to count the number of rows in a result set that meet a certain condition. It is often used to count the occurrences of a particular value or to retrieve the total number of rows in a table. SELECT COUNT() FROM orders; — Count all rows in the “orders” table SELECT COUNT() FROM customers WHERE age > 30; — Count customers older than 30 SUM: The SUM function calculates the sum of a numerical column’s values in a result set. It’s commonly used to find the total of a specific numeric attribute. SELECT SUM(price) FROM products; — Calculate the total price of all products AVG: The AVG function computes the average of a numerical column’s values in a result set. It’s used to find the average value of a specific numeric attribute. SELECT AVG(age) FROM employees; — Calculate the average age of employees MIN: The MIN function returns the minimum value from a column in a result set. It’s used to find the smallest value in a numeric or character column SELECT MIN(salary) FROM staff; — Find the lowest salary among staff members MAX: The MAX function retrieves the maximum value from a column in a result set. It’s used to find the largest value in a numeric or character column. SELECT MAX(score) FROM test_results; — Find the highest test score These simple aggregate functions (COUNT, SUM, AVG, MIN, and MAX) are fundamental tools in SQL for performing calculations and summarizing data in various ways. They are used extensively for generating insights and reports from database tables.
SubQueries
A subquery, also known as a nested query or inner query, is a query that is embedded within another query. It’s a powerful feature in SQL that allows you to retrieve data from one or more tables based on the results of another query. Subqueries are often used in situations where you need to perform a query within a query to filter or manipulate data. There are two main types of subqueries: correlated and non-correlated. Non-Correlated Subquery: A non-correlated subquery is a subquery that can be executed independently of the outer query. It’s executed once and its result is used by the outer query to perform filtering or comparison. SELECT product_nameFROM productsWHERE product_id IN (SELECT product_id FROM orders WHERE order_date = ‘2023-07-01’); In this example, the subquery (SELECT product_id FROM orders WHERE order_date = ‘2023-07-01’) retrieves a list of product_id values from the orders table. The outer query then uses this list to retrieve the corresponding product_name values from the products table. Non-Correlated Subquery from Todo-Project: SELECT username FROM users WHERE id IN (SELECT user_id FROM todos WHERE inserted_at = ‘2023-08-11 14:30:04′); Correlated Subquery: A correlated subquery is a subquery that depends on the outer query for its values. It’s executed for each row processed by the outer query, resulting in potentially multiple executions of the subquery. SELECT employee_nameFROM employees eWHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id); In this example, the subquery (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) calculates the average salary for each department. The outer query then compares the salary of each employee with the average salary of their department. Correlated Subquery from Todo-Project:- SELECT usernameFROM usersWHERE id > (SELECT AVG(id) FROM users WHERE name = “sammy”); Subqueries are versatile and can be used in various scenarios, such as filtering, comparison, calculations, and more. They are particularly useful when you need to perform complex queries involving multiple conditions or aggregations, making your SQL queries more dynamic and adaptable.
Extending Interfaces
Interfaces allow you to define the structure of objects. You can extend an interface from another interface to build a new interface that inherits properties from the base interface. This promotes code reuse and allows you to create more specialized interfaces while maintaining a consistent structure. interface BasicAddress {name?: string;street: string;city: string;country: string;postalCode: string;} interface AddressWithUnit extends BasicAddress {unit: string;}
Parameter de-structuring
Parameter De-structuring: Parameter de-structuring allows you to unpack properties of objects passed as function arguments into local variables within the function body. This can make code more readable and eliminate the need for multiple property accesses. type ABC = { a: number; b: number; c: number }; function sum({ a, b, c }: ABC) {console.log(a + b + c);} In this example, the sum function receives an object of type ABC, but instead of accessing a, b, and c properties with abc.a, abc.b, and abc.c, you can directly destructure the properties in the parameter list.
Rest Arguments
Rest Arguments: The rest parameter syntax allows you to pass a variable number of arguments to a function. This is useful when you want to handle multiple arguments without explicitly listing them. The spread syntax is used to pass multiple values from an iterable (like an array) as individual arguments to a function. const arr1 = [1, 2, 3];const arr2 = [4, 5, 6];arr1.push(…arr2); In this example, the push method takes any number of arguments using the spread syntax, allowing you to push the elements of arr2 into arr1.
Rest parameters
Rest parameters in TypeScript and JavaScript allow you to define functions that can accept a variable number of arguments as an array. This is useful when you want to work with functions that can take different numbers of arguments without explicitly specifying them in the function signature. Rest parameters are denoted by the ellipsis (…) followed by a parameter name, and they gather all the remaining arguments into an array. function sum(…numbers: number[]): number {return numbers.reduce((total, num) => total + num, 0);} console.log(sum(1, 2, 3)); // Output: 6console.log(sum(5, 10, 15, 20)); // Output: 50 In this example, the sum function uses a rest parameter named numbers. This allows the function to accept any number of arguments, which are then gathered into an array called numbers. The reduce function is used to sum up all the numbers in the array.
Interface
An interface is a way to define a contract that specifies the structure of an object. It defines the properties and methods that an object should have. Interfaces provide a mechanism for type checking and ensure that objects adhere to a certain shape. interface Person {firstName: string;lastName: string;age: number;} const person: Person = {firstName: “John”,lastName: “Doe”,age: 30}; In this example, the Person interface defines a contract that requires objects of type Person to have firstName, lastName, and age properties. The person object adheres to this contract, so TypeScript will perform type checking to ensure that only objects with the required properties can be assigned to variables of type Person. Interfaces can be used for various purposes: interface Employee extends Person {employeeId: string;position: string;} const employee: Employee = {firstName: “Jane”,lastName: “Smith”,age: 28,employeeId: “12345”,position: “Manager”}; Optional Properties: You can mark properties as optional using the ? symbol. interface Configuration {readonly apiKey: string;baseUrl: string;} const config: Configuration = {apiKey: “abc123”,baseUrl: “https://example.com”}; // Cannot modify apiKey after initializationconfig.apiKey = “newApiKey”; // Error NOTE:- The key distinction is that a type cannot be re-opened to add new properties vs an interface which is always extendable.
Type Aliase
Type aliases in TypeScript allow you to create custom names for existing types, making your code more readable and expressive. They are particularly useful for complex type annotations, unions, intersections, and for creating more meaningful names for combinations of types. Basic Type Alias: You can create a type alias using the type keyword. This is useful for simplifying complex type expressions.type Age = number;type Person = { name: string; age: Age }; Union and Intersection Types: Type aliases can be used to create union and intersection types, combining multiple types into one.type StringOrNumber = string | number;type Point = { x: number; y: number };type Point3D = Point & { z: number }; Function Signatures: Type aliases can also define function signatures.type MathOperation = (a: number, b: number) => number; Generics with Type Aliases: Type aliases can include generic parameters, making them more versatile.type Container = { value: T };type NumberContainer = Container; In simpler terms, NumberContainer is a type alias for a container that holds a value of type number. Type Aliases for Complex Structures: You can use type aliases to define complex and nested structures.type User = {id: number;name: string;address: {street: string;city: string;};}; Mapped Types with Type Aliases: Mapped types can be created using type aliases to transform properties of existing types. type Nullable = { [K in keyof T]: T[K] | null };type NullablePerson = Nullable; Intersection of Types: Type aliases can be used to create intersections of types, combining multiple types into one. type Admin = { isAdmin: boolean };type AdminPerson = Person & Admin;
Functions
Functions: are the primary means of passing data around in JavaScript. TypeScript allows you to specify the types of both the input and output values of functions.enabling better type checking and code clarity.Function Declaration: You can declare a function using the function keyword. You can specify parameter types and the return type using type annotations. function add(a: number, b: number): number {return a + b;} Parameter Type Annotations: When you declare a function, you can add type annotationsafter each parameter to declare what types of parameters the functionaccepts. Parameter type annotations go after the parametername: function greet(name: string){ console.log(“Hello, ” +name.toUpperCase() + “!!”); } Optional Parameters: You can make function parameters optional by adding a ? after the parameter name. Optional parameters must be placed at the end of the parameter list. function greet(name: string, age?: number): string {if (age) {return Hello, ${name}! You are ${age} years old.;} else {return Hello, ${name}!;}} Default Parameters: You can assign default values to function parameters using the = symbol. function greetDefault(name: string, greeting: string = “Hello”): string {return ${greeting}, ${name}!;} Function Types: You can define the type of a function using the arrow => notation. This is particularly useful when you want to declare function types for variables or interfaces. type MathOperation = (a: number, b: number) => number; const subtract: MathOperation = (a, b) => a – b; Anonymous Functions and Lambdas: TypeScript supports anonymous functions and lambda expressions. const multiply = function(x: number, y: number): number { return x * y; }; const power = (base: number, exponent: number): number => base ** exponent; Function Overloading: TypeScript allows you to provide multiple type signatures for a single function using function overloading. function formatOutput(value: string): string; function formatOutput(value: number): string; function formatOutput(value: any): string { return `Value: ${value}`; } Higher-Order Functions: TypeScript supports higher-order functions, which are functions that take other functions as parameters or return functions. function applyOperation(x: number, y: number, operation: MathOperation): number { return operation(x, y); }
