DEV Community: Adam

Angular Pipes

Adam — Wed, 10 Apr 2024 02:29:36 +0000

Pipes are functions that are used in your UI to transform your data in a simple way. You have the option to create your own pipes to your desired output, and Angular has already built in pipes that you can use.

DatePipe - Formates a date value

UpperCasePipe - Transforms text to uppercase

LowerCasePipe - Transforms text to lowercase

CurrencyPipe - Transform a number to a currency value

PercentPipe - Transforms a number to the percentage string.

DecimalPipe - transforms into a decimal point string

You create your pipes by using the "@Pipe" decorator in your typescript class, your typescript class implements the "PipeTransform" interface to create your transform methods.

You use your pipes, or build-in pipes by using the "|" with your binding variable in your HTML template

<p>{{ 'Your Text Here' | uppercase }}</p>

Pure Pipes

By default when using pipes, they are pure pipes by default. A pure pipe is only called when angular detects changes in a values or parameters to a pipe.

is only called when angular detects change in the value or the parameters passed to a pipe. it only executes only when a Pure change to the input is detected. They are only execute when property has changed. if a objects property has changed, the pipe will no execute because the object reference is still the same. It executed only when the component is loaded

Inpure Pipe

It detects inpure changes. It detects change to composite objects. when adding an element to an existing array, or making changes to objects, that impure pipe will detect those changes to that object. it detects any change. it executes every change with in the component.

The transform method only calls when the angular change detection cycle runs, whether the input data changes.

SQL Aggretate functions

Adam — Mon, 25 Mar 2024 01:51:58 +0000

The purpose of using Aggregate functions in sql is to combinding data to perform and return a single value, there are multiple ways to you can manipulate large amounts of data to simplify to one single value.

AVG() - Calculates the average or mean of values for a specific column, mostly used for integer based columns.
COUNT() - Returns a number of records from a particular column.
MIN() - This function returns the smallest value of a selected column
MAX() - This function returns the largest value of a selected column
SUM() - This adds all values on a numeric column
FIRST() - This will return the first value of a selected column.
LAST() - grabs the last value/element of a particular column in your table
LEN() - This calculates the length of a string of a particular column
UCASE() - This takes string values of a column and uppercase the string
LCASE() - This takes the string of a column and lowercases the value
MID() - extracts the substring of string values in a collection
CONCAT() - combines two or more strings
RAND() - generates a random number from a given range
ROUND() - this rounds a number from a given user defined number
NOW() - This returns the current date and time
FORMAT() - formats a value with a specific format

SQL Table Structure

Adam — Wed, 20 Mar 2024 00:43:09 +0000

Tables are an organized collection of data is stored and represented by a form of columns and rows. Fields are what represents columns in your table. When writing your CREATE sql statement how many columns and what the data type are are specified on the business needs of your application

Shown below a sql statement on creating a table.

CREATE tbl_name (
    id INT NOT NULL AUTO INCREMENT,
    column_Name VARCHAR(50)
    ...
)

Columns

When you create a table, you have the flexibility on how your table is structured. This will be come a representation on how your data is organized.

A column type is a data type that is how it its going to be stored in each data cell. You can have Numeric, Date time and String data types for your columns

CONSTRAINTS

Constraints are used for ensuring the logical, and physical data is stored consistent in your tables. You can specify what type of rules of how records are entered, and updated. This helps with data integrity of your Database.

NOT NULL

This constraint is used to specify a particular column on your table cannot allow NULL values. This rule is used to enforce your application that a particular column needs/require a certain value.
This example shown below "column_name" has a data type of INTEGER, and it cannot be null when you insert or update a record in your table

column_name INT NOT NULL

DEFAULT

This is used to set a default value on a column if there is no data specified when persisting data to a particular column.

This example shown below the column "column_name" has a data type of VARCHAR, with a default value of "Test default text" when you enter a null value when inserting a new record in a table

column_name VARCHAR "Test default text"

INDEX

Indexes are used to retrieve data from a database more quickly. Its mostly used to speed up process when making a query off of a particular index column

CREATE_INDEX idx_name 
ON tbl_name (column1, column2, ...)

PRIMARY KEY

This constraint is used for specify the main indicator of a table for the record, it has a unique value, and most of the time it is usually an integer value and it auto increments when entering new data in a particular table.

Check and make sure what database are you using. Each database has its own syntax on how a primary key is created when generating your tables.

CREATE tbl_name (
    id INT NOT NULL AUTO INCREMENT,
    column_Name VARCHAR(50)
    ... ,
    PRIMARY KEY (id)
)

FOREIGN KEY

A foreign key is used to reference a primary key from a different table. It is usually associated with defining relationships between other tables in your database. It is also used to bring a constraint to prevent from disrupting relationships between different entities in your database

CREATE tbl_name (
    id INT NOT NULL AUTO INCREMENT,
    column_Name VARCHAR(50)
    tble_name_2_id: INT
    ... ,
    PRIMARY KEY (id),
    FOREIGN KEY (tble_name_2_id) REFERENCES tble_name_2(id)
)

UNIQUE

This is to specify a column is only allowed to have unique values from one row to another. This will prevent any duplicated content if you try to enter the same data from a particular column

CREATE tbl_name (
    id INT NOT NULL AUTO INCREMENT,
    column_Name VARCHAR(50) UNIQUE
    ... ,
)

CHECK

This is used to put a condition or limits value ranges on a column you specify when creating your table.

CREATE tbl_name (
    id INT NOT NULL AUTO INCREMENT,
    column_name INT
    ... ,
    CHECK (column_name > 18)
)

SQL Stored Procedures

Adam — Tue, 19 Mar 2024 00:34:05 +0000

Stored Procedures is a collection of SQL statements that can executed in a single unit or function. It is used mostly for encapsulate info, and perform the same tasks multiple time.

You can specify what your procedure name, write as many sql statements that suits your needs within a stored procedure. It is like writing a method/function in SQL, but they don't return a value. its just an easier way to execute multiple sql statements into one location.

`CREATE PROCEDURE procedure_name
AS
BEGIN
    SELECT * FROM tbl_name;
    ... 
END;

EXEC procedure_name;`

You can write Stored Procedures with parameters to pass conditional values, which makes your stored procedure more flexible when quering data.

`CREATE PROCEDURE procedure_name_with_param @ParamName nvarchar(32)
AS
BEGIN
    SELECT * FROM tbl_name WHERE column_name = @ParamName;
    ... 
END;

EXEC procedure_name_with_param @ParamName='param_value';`

SQL Table Relationships

Adam — Mon, 18 Mar 2024 00:14:00 +0000

Joins is where you execute a query to retrieve table by combining two or more tables based on relationships of column ids.

There are multiple joins that you can achieve when retrieving data from your database

INNER JOIN

an Inner join is where you joining two tables and that record will appear if there is a matching records between two tables based on a condition if two columns have matching data.

SELECT column1, column2, ... FROM tbl_name_1 INNER JOIN tbl_name_2 ON tbl_name_1.matching_column = tbl_name_2.matching_column LEFT JOIN

a Left join will show all rows from left side, and any matching results, it will be displayed on the right side of your result list. If there is no match in a row, it will return NULL. In this case, the left table in your join takes precedence over the right join, so make sure which table you are trying to select when finding matches in your data.

Left join is most used when trying to find matches

SELECT column1, column2, ... FROM tbl_name_1 LEFT JOIN tbl_name_2 ON tbl_name_1.matching_column = tbl_name_2.matching_column RIGHT JOIN

A right join is where all records show on your right table, any matching records that you are trying to compare will show on the left side, if there is no match on a particular record, it will display NULL on the left side

SELECT column1, column2, ... FROM tbl_name_1 RIGHT JOIN tbl_name_2 ON tbl_name_1.matching_column = tbl_name_2.matching_column FULL JOIN

A full join will return all records when there is a match on the left or right side. This is shows all data whether there is a match or not.

SELECT column1, column2, ... FROM tbl_name_1 FULL JOIN tbl_name_2 ON tbl_name_1.matching_column = tbl_name_2.matching_column SELF JOIN

SQL CRUD Operations

Adam — Fri, 15 Mar 2024 01:00:47 +0000

(C)REATE

Creating is for inserting information in your tables, this is where you specify how your data is going to represent in table or "entity". It starts with the column names that is going to be inserted, followed by the values that need to be inserted. the end results it enter a new row in your table

`INSERT INTO tbl_name (column_name_one, column_name_two)
VALUES (column_value_one, column_value_two)`

(R)ETREIVE

this is used for quering, or selecting data from a particular table you are looking to retrieve. In your select statement you can specify what the data is stored by selecting what columns you want to display.

`SELECT * FROM tbl_name`

the "*" selector you see shown above will display all columns that is structured in that entity. You can specify what columns you want to see just by specifying a particular column shown below

`SELECT column_name FROM tbl_name`

Conditional Statements

You can also retrieve data from your database by using conditional statements. Using conditional statements, it returns records from your tables that will display records that have a match from what condition you are specifying

Shown below is an example using the "WHERE" clause to specify quering your data based on a condition.

There are also cases when you need to use aggregated functions in your SELECT statement, in this case the WHERE clause won't recognize how to run conditions off of what aggregated function you are using.
This example shown below, this query is using the COUNT() aggregate function, and its using the "HAVING" clause to execute this condition correctly.

There will also be cases when there are going to be similarities in your data, and you need to find a way to group and associate those similarities. The example shown below, this query is grouping the similarities of "column_name" and grouping them by that column while displaying "column_name_two" with that group

Another case to similiaries in your data. if you need to retrieve data (such as text, string) it would be beneficial querying data if a sub strings contains a certain text. Shown below is a query executing a condition if "column_name" contains a substring. In return it will return a list of records where it finds a match.

Using the LIKE clause in this query, you specify your wild card (%).
In this scenario its telling the LIKE clause that "column_name" has a pattern of "substring"

(U)PDATE

This is used to modify current records in your table. When updating records, you can run a query to update all records, or update records based on a condition (i.e. WHERE clauses). It is all based on your business needs on what functionality are you trying to achieve. Shown below this query is updating "tble_name" using a condition to specify what record to update.

(D)ELETE

This is where you are removing tables from a table, you can remove all records from a table