Forem: Fahid Latheef A

Django Migrations

Fahid Latheef A — Thu, 30 Sep 2021 10:50:01 +0000

Introduction

In the last episode, we talked about Django Architecture and settings.py file. In this episode, we'll discuss Django Migrations.

Django Migrations

Migrations are used to update the changes (to add a new field, change the name of a field, etc.) we made in the model to the database.

Django creates migration files (a Python file) inside the migration folder for each model to create the table schema, and then each table is mapped to the respective migration files. Django also keeps track of the status of migration files whether they are successfully migrated to the database.

To perform migration-related tasks, we can use these commands.

makemigrations

python manage.py makemigrations

This is used to create migrations files in the migrations folder based on the changes we have done to the Model.

migrate

python manage.py migrate

Based on the migrations file, this command will populate the database schema.

showmigrations

python manage.py showmigrations

This lists all the migrations and their status. If the change is updated in the database, the status will be shown as [X] followed by the migration name.
For example:

[X] 0001_initial
[ ] 0002_auto_20210924_1740

sqlmigrate

Usage:

python manage.py sqlmigrate <app_name> <migration_name>

This is used to display the raw SQL query of the applied migrations. For example:

python manage.py sqlmigrate users 0009_delete_profile

will give the raw SQL query like as below

BEGIN;
DROP TABLE "users_profile" CASCADE;
COMMIT;

Miscellaneous Commands and Uses

syncdb

python manage.py migrate --run-syncdb

This command allows us for creating tables for apps without migrations.

Creating and applying migration for a particular app

If we don't want to apply migrations totally, we can mention app-name to migrate specific apps.

For example:

python manage.py makemigrations blog

python manage.py migrate blog

References

Introduction to Django Architecture and Django Settings

Fahid Latheef A — Mon, 27 Sep 2021 12:31:47 +0000

Introduction

Django is a Python-based web framework that allows us to quickly create web applications. Django takes care of much of the hassle of web development, so we can focus on writing our app without needing to reinvent the wheel.

Django Architecture

Django is based on Model-View-Template (MVT) pattern. MVT consists of 3 components.

Model: Model is responsible for maintaining the data structure of the project, through databases (PostgreSQL, MySQL etc.).

View: Views perform operations and return a response to the user. This response can be the HTML contents of a Web page, or a redirect, or a 404 error.

Templates: Templates is used to generate dynamic HTML pages by using Django's template system. For example, with the use of jinja2, we will be able to display dynamic content on our web pages.

Few Django Security Features

XSS Attack Protection

XSS (Cross Site Scripting) attacks allow a user to inject client-side scripts into the browsers of other users.

Django does have protection against it by using a escape filter that converts potentially harmful HTML characters to unharmful ones.

CSRF Protection

CSRF (Cross-site request forgery) attacks allow a malicious user to execute actions using the credentials of another user without that user’s knowledge or consent.

Django checks for a secret code in each POST request. This ensures that a malicious user cannot “replay” a form POST to our website and have another logged in user unknowingly submit that form.

SQL Injection Protection

SQL injection is a type of attack where a malicious user can execute arbitrary SQL code on a database. This can result in records being deleted or data leakage.

Django’s querysets are protected from SQL injection since their queries are constructed using query parameterization.

Django settings file

DISCLAIMER: The Django version I used to generate the project is 2.2. These settings may or may not exist in future/previous versions of Django.

When we create a new project in Django, a new folder with our project name is created. The 3 files which are found in the folder are settings.py, urls.py and wsgi.py.

urls.py configures our project urls, and wsgi.py configures Django’s primary deployment platform, WSGI, which is used to deploy our project live.

The most important file among these three is the settings.py file, which is used for configuring all settings of our project. Here is a detailed explanation of what each line in settings.py does for our project.

`settings.py` detailed explanation

BASE_DIR: This refers to the absolute path in which manage.py file exists. The manage.py is integral in creating new apps, running server, running django shell etc.

SECRET_KEY: It is used to provide cryptographic signing, and should be set to a unique, unpredictable value. It is used for saving cookies cache. If someone has the secret key, they will be able to modify cookies sent by the application.

DEBUG: This is a toggle that turns on/off debug mode. When in debug mode, we can get detailed error pages, which helps in fixing our application quicker. Moreover, the modifications are dynamically changed when debug is on without the need of manually restarting the server after each change.

Due to the detailed error messages which may expose a lot of important metadata, it is very dangerous to keep DEBUG=True in production and is not advised to do so.

ALLOWED_HOSTS: A list of strings representing the host/domain names that this Django site can serve.

INSTALLED_APPS: A list of strings designating all applications that are enabled in the Django Project. After creating new applications, we have to manually add the application to the Django Installed Apps. Moreover, other outside applications have to be added to the list, so that it works with our project (Eg: crispy_forms).

MIDDLEWARE: Middleware is a framework of hooks into Django’s request/response processing. It’s a light, low-level “plugin” system for globally altering Django’s input or output. For example, Security Middleware is used to maintain the security of the application.

ROOT_URLCONF: This is a string representing the relative path to the urls.py.

TEMPLATES: Settings of Django Template Language used for our project.

WSGI_APPLICATION: Path to wsgi.py file.

DATABASES: A dictionary containing the settings for all databases to be used with Django. By default, Django uses SQLite as the database engine.

AUTH_PASSWORD_VALIDATORS: The list of validators that are used to check the strength of a user’s passwords. By default, no validation is performed and all passwords are accepted.

LANGUAGE_CODE: Represents the name of a language. For example 'en-us'.

TIME_ZONE: Represents the timezone. For example 'UTC'.

USE_I18N: A boolean that specifies whether Django’s translation system should be enabled. This provides a way to turn it off, for performance. If this is set to False, Django will make some optimizations so as not to load the translation machinery.

USE_L10N: A boolean that specifies if localized formatting of data will be enabled by default or not. If this is set to True, Django will display numbers and dates using the format of the current locale.

USE_TZ: A boolean that specifies if datetimes will be timezone-aware by default or not. If this is set to True, Django will use timezone-aware datetimes internally.

STATIC_ROOT: The absolute path to the directory where ./manage.py collectstatic will collect static files for deployment. So we have to only serve this folder while deploying.

STATIC_URL: The URL of which the static files in STATIC_ROOT directory are served.

References

Django Javatpoint

Django Official Documentation

Django Project

Databases: Important Theoretical Concepts

Fahid Latheef A — Sat, 04 Sep 2021 11:13:25 +0000

Introduction

In the previous episode, I discussed the important SQL Statements/Keywords that are useful for day-to-day SQL use. In today's episode, I will try to explain few important Database Concepts that will boost our understanding even further.

Transactions

Transaction is a fundamental concept of all database systems. It bundles multiple steps into a single, all-or-nothing operation. The intermediate states between the steps are not visible to other concurrent transactions, and if some failure occurs that prevents the transaction from completing, then none of the steps affects the database at all.

Transactions can be further tuned by BEGIN, COMMIT and ROLLBACK operations.

BEGIN

Each transaction is explicitly initiated using BEGIN TRAN.

COMMIT

By using COMMIT TRAN, a transaction is closed explicitly and modifications performed by the transaction are made permanent.

ROLLBACK

By using ROLLBACK TRAN, a transaction is explicitly closed and any modifications made by the transaction is discarded.

Each Transaction should follow ACID rules.

ACID

ACID stands for Atomicity, Consistency, Isolation and Durability. This describes a set of properties that apply to data transactions. For a reliable database, all these four attributes should be achieved.

Atomicity

Atomicity is also known as the "All or nothing rule". It means that either the entire transaction takes place at once or doesn’t happen at all. There aren't any partial transactions. Hence it is known as the "All or nothing rule".

Consistency

It says that the database should be consistent before and after the transaction (Data types, triggers, constraints, etc). It refers to the correctness of a database.

Isolation

This property says that the transactions occur independently without interference. This property ensures that the execution of transactions concurrently will result in a state that is equivalent to a state achieved these were executed serially in some order.

Durability

It says that once the transaction is saved or committed, it can’t be “lost”. Data won’t go missing after a power outage or system crash.

LOCKING MECHANISM

Locking is designed to allow SQL Server to work seamlessly in a multi-user environment. Locking is the way that SQL Server manages transaction concurrency.

While objects are locked, SQL Server will prevent other transactions from making any change of data stored in objects affected by the imposed lock. Once the lock is released by committing the changes or by rolling back changes to the initial state, other transactions will be allowed to make required data changes.

CAP THEOREM

CAP Theorem (aka Eric Brewer Theorem) states that we cannot build a general data store that is continually available, sequentially consistent and tolerant to any partition failures. We can only achieve 2 features out of these 3. A combination of 2 must be chosen and this is usually the deciding factor in what technology is used.

Consistency - All the servers in the system will have the same data so users will get the same copy regardless of which server answers their request.
Availability - The system will always respond to a request (even if it's not the latest data or consistent across the system or just a message saying the system isn't working).
Partition Tolerance - The system continues to operate as a whole even if individual servers fail or can't be reached.

TRIGGERS

Trigger: A trigger is a stored procedure in a database that automatically invokes whenever a special event in the database occurs. For example, a trigger can be invoked when a row is inserted into a specified table or when certain table columns are being updated.

Triggers can be run BEFORE or AFTER the triggering statement.

SQL NORMALIZATION

It is the process of structuring a database, following a series of Normal Forms to reduce data redundancy and improve data integrity.

Without Normalization in SQL, we may face many issues such as

Insertion anomaly: It occurs when we cannot insert data to the table without the presence of another attribute
Update anomaly: It is a data inconsistency that results from data redundancy and a partial update of data.
Deletion Anomaly: It occurs when certain attributes are lost because of the deletion of other attributes.

Some examples of Normal Forms are 1NF, 2NF, 3NF, BCNF, etc.

DATABASE ISOLATION LEVELS

Database Isolation levels control the degree of locking that occurs when selecting data. This is to prevent reads and writes of temporary, aborted, or otherwise incorrect data written by concurrent transactions. For many database applications, the majority of database transactions can be constructed using isolation level priority rules, reducing the locking overhead for the system.

These are the priority rules for isolation levels.

Serializable
Repeatable reads
Read committed
Read uncommitted

References

Database Isolation Levels

Triggers

Web Basics Episode 2: Introduction to CSS

Fahid Latheef A — Thu, 02 Sep 2021 06:04:50 +0000

Introduction

In this series, I will try to discuss Web Basics Topics. In the first episode, the HTML Basics were covered. In this episode, I will try to cover the Basic CSS Concepts.

What is CSS?
CSS Example
CSS Selectors
- CSS Selectors Examples
Ways of adding CSS to our HTML file
CSS Box Model
CSS display property
- inline
- inline-block
- block
CSS Position Property
CSS Structural Classes
- Some Structural class examples
CSS Specificity
CSS Media Query
Conclusion
References

What is CSS?

CSS (Cascading Style Sheets) is the language for describing the presentation of Web pages, including colours, layout, and fonts. It allows one to adapt the presentation to different types of devices, such as large screens, small screens, or printers. CSS is independent of HTML and can be used with any XML-based markup language. The separation of HTML from CSS makes it easier to maintain sites, share style sheets across pages, and tailor pages to different environments. Moreover, CSS saves a lot of work by allowing us to control the layout of multiple web pages all at once.

CSS Example

This is an example of an CSS file.

body  {  
background-color:  lightblue;
font-family:  verdana;  
font-size:  20px;  
}  

h1  {  
color:  white;  
text-align:  center;  
}

In this example, we define the backgound-color of the body to light blue, with a font-family of verdana with 20px font-size. Now for the h1 headers, we define the color (text color) white and align the text centrally using text-align.

CSS Selectors

A CSS selector helps us selecting the HTML element(s) we want to style.

We can divide CSS selectors into five categories:

Simple selectors: Select elements based on name, id, class
Combinator selectors: Select elements based on a specific relationship between them
Pseudo-class selectors: Select elements based on a certain state
Pseudo-elements selectors: Select and style a part of an element
Attribute selectors: Select elements based on an attribute or attribute value

CSS Selectors Examples

CSS Element Selector

p {  
text-align:  center;  
color:  red;  
}

Here all <p> (paragraph) elements are styled.

CSS ID Selector

#section-intro {  
text-align:  center;  
color:  red;  
}

Here HTML element with ID section-intro is styled.

The CSS Class Selector

.center {  
text-align:  center;  
color:  red;  
}

Here all HTML elements with class name center is styled.

The CSS Universal Selector

For universally selecting all HTML elements on the page, the following code is used.

* {  
text-align:  center;  
color:  blue;  
}

Ways of adding CSS to our HTML file

There are three ways of inserting a style sheet:

External CSS
Internal CSS
Inline CSS

CSS Box Model

The CSS box model is a box that wraps around every HTML element. It consists of: margins, borders, padding, and the actual content. The image below illustrates the box model:

CSS `display` property

The display property specifies the display behavior of an element.

Usage:

.class-name{
display: block;
}

`inline`

Displays an element as an inline element. Any height and width properties will have no effect on it. Moreover, inline elements are forced to stay on the same line. Here are a few elements that have a default inline property:

span
a
img

`inline-block`

Displays an element as an inline-level block container. You can set height and width values. It’s essentially the same thing as inline, except that we can set height and width values.

`block`

The element block starts on a new line and takes up the full width available. So that means block elements will occupy the entire width of its parent element.

Here are a few elements that have a default block property:

div
h1
p
li
section

CSS Position Property

The position property specifies the type of positioning method used for an element. For example, static, relative and absolute

position: static;
- HTML elements are positioned static by default.
- Static positioned elements are not affected by the top, bottom, left, and right properties.
- An element with position: static; is not positioned in any special way; it is always positioned according to the normal flow of the page.
position: relative;
- An element with position: relative; is positioned relative to its normal position.
- Setting the top, right, bottom, and left properties of a relatively-positioned element will cause it to be adjusted away from its normal position.
- Other content will not be adjusted to fit into any gap left by the element.
position: absolute;
- An element with position: absolute; is positioned relative to the nearest positioned ancestor.
- If an absolute positioned element has no positioned ancestors, it uses the document body, and moves along with page scrolling.
- Absolute positioned elements are removed from the normal flow, and can overlap elements.

CSS Structural Classes

Structural pseudo classes allow access to the child elements present within the hierarchy of parent elements. We can select first-child element, last-child element, alternate elements present within the hierarchy of parent elements.

Some Structural class examples

Example	Select(s)
`:first-child`	The first child of its parent
`:last-child`	The last child of its parent
`:nth-child(2n+1)`	Every odd child
`:nth-child(2n)`	Every even child
`:nth-last-child(2n+1)`	Every odd child element starting from the last element

CSS Specificity

Sometimes, CSS rules conflict. For example, if multiple selectors target the same element in a document, the rules to be applied is determined by CSS specificity. In CSS, different selectors have varying weights. When two or more rules conflict on the same element, the more specific one applies.

These are the Specificity priority of different selectors

Inline CSS: Inline CSS appears as style attributes in the opening tag of HTML elements. Since this CSS is closest to the HTML, it has the highest level of specificity.
ID selectors: An ID is unique to a page element and thus, very specific.
Class selectors, attribute selectors, and pseudo-class selectors: These three selector types have equal specificity. If all three types are applied to the same HTML element, the one appearing latest in the style-sheet will apply and override the rest.
Type selectors: These select all HTML elements that have a given node name and have the syntax element. These are element names and pseudo-elements.

CSS Media Query

Media Query is very helpful in creating responsive web designs which works well with all types of devices. It uses the @media rule to include a block of CSS properties only if a certain condition is true.

@media only screen and (max-width: 600px) {  
body {  
background-color: lightblue;  
}  
}

In the above example, if the browser window is 600px or smaller, we set the background-color to lightblue.

While developing web pages, it is advised to create the mobile version first before other screen sizes.

Conclusion

That concludes the Introduction to CSS topic.

References

Web Basics Episode 1: Introduction to HTML

Fahid Latheef A — Wed, 01 Sep 2021 11:43:58 +0000

Introduction

In this series, I will try to discuss Web Basics Topics. In the first episode, the HTML Basics are covered.

What is HTML?
HTML Document structure
HTML Tags
- Some Basic Tags
- Singleton Tags
- Important Singleton Tags
- Semantic and Unsemantic Tags
- Semantic Page Example
HTML Header Section
- Difference between <header> and <head>
- Navigation Section
- Title
- Favicon
- Charset
- UTF-8, ASCII and UNICODE
- Viewport
HTML Body section
- Block Level tags
- <h1> to <h6>
- Lists
- Page Divisions or Sections
- Inline Tags
Conclusion
References

What is HTML?

HTML (Hyper Text Markup Language) is the language for describing the structure of Web pages. HTML gives authors the means to:

Publish online documents with headings, text, tables, lists, photos, etc.
Retrieve online information via hypertext links, at the click of a button.
Design forms for conducting transactions with remote services, for use in searching for information, making reservations, ordering products, etc.
Include spread-sheets, video clips, sound clips, and other applications directly in their documents.

HTML Document structure

This is a rough structure of an HTML document.

<!DOCTYPE html>
<html>
    <head>
        Document Header
    </head>

    <body>
        Document Body
    </body>
</html>

HTML Tags

HTML is a markup language and makes use of various tags to format the content. Except few tags, most of the tags have their corresponding closing tags. For example, <body> has its closing tag <\body.

Some Basic Tags

Tag	Description
`<!DOCTYPE...>`	This tag defines the document type and HTML version.
`<html>`	This tag encloses the complete HTML document and comprises of document header and body.
`<head>`	This tag represents the document's header.
`<title>`	This tag is used inside the `<head>` tag to mention the document title.
`<body>`	This tag represents the document's body which keeps other HTML tags like `<h1>`, `<div>`, `<p>`, etc.
`<h1>`	This tag represents the top level heading. The tags `<h2>`, `<h3>`, `<h3>`, `<h4>`, `<h5>`, `<h6>` also represents other sizes of headings in which `<h1>` is the biggest and `<h6>` is the smallest.
`<p>`	This tag represents a paragraph.

Singleton Tags

The singleton tags don't require a closing tag to be valid. These elements are usually ones that stand alone on the page.

Important Singleton Tags

Tag	Description
`<br>`	A line break which is used in text content to create a single line break instead of a paragraph.
`<hr>`	A horizontal rule, which is a straight line on a page. These can be used to separate sections in a webpage.
`<img>`	Used to embed an image in an HTML page. Technically, this tag creates a holding space for the image whose path can be referenced through `src` attribute.
`<meta>`	Meta tags are "information about content." They are found in the head of a document and used to convey page information to the browser. There are many different meta tags that you can use on a web-page.
`<input>`	A form element that is used to capture information from visitors.

Semantic and Unsemantic Tags

Semantic tags describes its meaning to both the browser and the developer.
Examples of non-semantic elements: <div> and <span>. These tells nothing about its content.
Examples of semantic elements: <form>, <table>, and <article> . These clearly defines its content.

Semantic Page Example

HTML Header Section

Difference between `<header>` and `<head>`

The <header> element is a structural element that outlines the heading of a segment of a page. It falls within the <body> element.

The <head> element is not displayed on a page and is used to outline metadata, including the document title, and links to external files. It falls directly within the <html> element.

Navigation Section

The <nav> element identifies a section of major navigational links on a page. This allows us to link to other pages within the same website or to parts of the same web page

Title

<title> is used to give titles to web-pages. It is shown in browser's title bar.

Favicon

A favicon is a file containing one or more small icons, associated with a particular website or web page.
Now favicon.ico is used to display icon in our web-page (Typically can be seen close to the title in the title bar).

Charset

A character set (charset) refers to the composite number of different characters that are being used and supported by the html document.
To display an HTML page correctly, a web browser must know the character set used in the page. This is specified in the <meta> tag.

UTF-8, ASCII and UNICODE

ASCII	UNICODE	UTF-8
ASCII defines 128 characters, which map to the numbers 0–127	Unicode defines (less than) 2²¹ characters, which, similarly, map to numbers 0–2²¹	UTF-8 is one possible encoding scheme of Unicode characters
ASCII is subset of UNICODE	UNICODE is super set of ASCII
	Invented to accommodate non-English characters

Viewport

The view-port is the user's visible area of a web page. It varies with the device - it will be smaller on a mobile phone than on a computer screen.
This is defined using <meta tags in HTML

HTML Body section

Block Level tags

`<h1>` to `<h6>`

<h1> to <h6> tags are used for headings with <h1> being the largest and <h6> being the smallest.

Lists

<ul>, <ol> and <li> are used to list items in a web-page.

The list is started with either <ul> or <ol> and then <li> is used to list the elements.

<ul> aka unordered list

Item 1
Item 2
Item 3

<ol> aka ordered list

Item 1
Item 2
Item 3

Page Divisions or Sections

<div> is a non-semantic tag used to divide page contents.
<section> tag is a semantic tag used to define sections in a document.
<header> tag is a semantic tag used for header section in the document.
<footer> tag is a semantic tag used for footer contents in the document.
<nav> tag is a semantic tag used for navigation contents of the document.
<main> is a semantic tag used to define contents that are unique to the document. It should not contain any content that repeats across documents such as sidebars, navigation links, copyright information, site logos, and search forms.
<aside> tag is a semantic tag that is used to set aside the content it is placed in from the section.
<article> is used to specify independent, self-contained content.
<canvas> tag is used to draw graphics, on the fly, via scripting (usually JavaScript). It is transparent, and is only a container for graphics, you must use a script to actually draw the graphics.
<form> tag is used to create an HTML form for user input.
<table>tag is used to create tables in the document.

Inline Tags

An inline element does not start on a new line. An inline element only takes up as much width as necessary.

<span> is an inline container used to mark up a part of a text, or a part of a document.
<label> tag defines a label for several grouped elements, such as in drop-down, input-area etc.
<input> tag is used to create input field where users can enter the data.
<b> tag is used to bold the text.
<i> tag is used to make the text italic.
<img> tag is used to embed image in the web-page.
<button> tag creates a button create a clickable button in the web-page.
<a> tag is used to create links in the web-page.

Conclusion

That concludes the HTML basics. In the next episode, I will cover the Basics of CSS.

References

HTML Basics w3schools

HTML Basics Tutorial Point pdf

Web Design w3org

HTML Singleton tags

Basic SQL Concepts

Fahid Latheef A — Wed, 25 Aug 2021 06:09:20 +0000

Pre-requisites

1. Install PostgreSQL and psql-CLI to your system

If you haven't installed PostgreSQL and psql-CLI in your system, follow the steps in this link.

2. Create a psql role

I have created a role named fahid in the psql. Follow the instructions in the same link to create a role (preferably a role with your computer-name).

3. Access the Postgre prompt

Use either of these commands to access Postgre Prompt. Replace fahid with your_username.

psql

psql -U fahid

Before we start, create a database sample in the psql shell.

4. Create an empty database `sample`

To create an empty database, run the following command in the postgre prompt. (Note: Do not miss the ;)

CREATE DATABASE sample;

To confirm whether the database sample is created, list the databases using the following command in the postgre shell.

\list

You should be able to see the database ipl in the list of databases with your username as the owner.

5. Connect to `sample` database

To connect to the database, run

\c sample

6. Copy the contents to the database

Make sure you download sample.sql to your computer.
Now to copy/import the contents to the database, run

\i /path/to/the/file/sample.sql

6. Display the tables in the database

Run this to display the tables in the database.

\dt

You will be able to see all available tables in the sample database.

SELECT STATEMENT

The most basic statement is SELECT statement. It allows us to retreive records from the table.

To retreive all the columns:

SELECT * FROM customer;

To specifically select one or more columns:

SELECT cust_code, cust_name, cust_city, working_area FROM customer;

WHERE CONDITION

To have a condition in your SELECT statement, we can use WHERE condition.

SELECT cust_code, cust_name, cust_city FROM customer
WHERE cust_city = 'London';

We will get the following output.

 cust_code | cust_name | cust_city 
-----------+-----------+-----------
 C00013    | Holmes    | London
 C00024    | Cook      | London
 C00015    | Stuart    | London
 C00023    | Karl      | London

AND and OR operators

We can add additional conditions and filter the table using AND or OR operators.

SELECT cust_code, cust_name, cust_city FROM customer
WHERE cust_city = 'London' OR cust_city = 'New York';

Selecting Grade 1 customers in India

SELECT cust_code, cust_name, cust_city, grade FROM customer
WHERE cust_country = 'India' AND grade = 1;

ORDER BY

To order the table by a specific column(s), we can use ORDER BY clause.

SELECT cust_code, cust_name, cust_city, payment_amt
FROM customer
ORDER BY payment_amt;

To arrange the result in descending order, we can use DESC keyword.

SELECT cust_code, cust_name, cust_city, payment_amt
FROM customer
ORDER BY payment_amt DESC;

DISTINCT

To remove duplicates and select the distinct elements use DISTINCT keyword.

SELECT DISTINCT cust_city
FROM customer;

BETWEEN

To select data within a range, we can use BETWEEN with AND.

SELECT cust_code, cust_name, payment_amt
FROM customer
WHERE payment_amt BETWEEN 6000 AND 8000;

LIKE

To match one or more characters, LIKE is used.
_ is used to match exactly one character and % is used to match zero or more characters.

Customers starting with the name 'Ra'

SELECT cust_code, cust_name, cust_city
FROM customer
WHERE cust_name LIKE 'Ra%';

COUNT

To count number of results, we can use COUNT

SELECT COUNT(cust_name)
FROM customer
WHERE cust_name LIKE 'Ra%';

OTHER AGGREGATE FUNCTIONS

KEYWORD	USE
SUM	To calculate the sum
AVG	To calculate the average
MIN	To find the minimum of the column
MAX	To find the maximum of the column

AS

We can use AS as alias in SQL

SELECT COUNT(cust_name) AS total
FROM customer
WHERE cust_name LIKE 'Ra%';

GROUP BY

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

SELECT DISTINCT cust_country, COUNT(*) AS count
FROM customer
GROUP BY cust_country;

HAVING

To select countries having count greater than 4

SELECT DISTINCT cust_country, COUNT(*) AS count
FROM customer
GROUP BY cust_country
HAVING COUNT(*) > 4;

JOIN

A JOIN clause is used to combine rows from two or more tables.
ON keyword is used to specify the coommon columns

SUBQUERIES

We can use a query inside a query to further restrict the output data

SELECT * FROM customer 
WHERE cust_code IN 
(SELECT cust_code 
FROM customer 
WHERE payment_amt <  6000);

DELETE

To delete complete table

DELETE FROM customer;

We may also use other conditions to specifically delete rows.

DELETE FROM customer
WHERE cust_name = 'Steven';

DROP

To completely drop the table, we may use

DELETE TABLE customer;

Conclusion

In the next Episode of this Series, we will discuss few Database Theoretical Concepts.

Forem: Fahid Latheef A

Django Migrations

Introduction

Django Migrations

makemigrations

migrate

showmigrations

sqlmigrate

Miscellaneous Commands and Uses

References

Introduction to Django Architecture and Django Settings

Introduction

Django Architecture

Few Django Security Features

XSS Attack Protection

CSRF Protection

SQL Injection Protection

Django settings file

settings.py detailed explanation

References

Databases: Important Theoretical Concepts

Introduction

Transactions

BEGIN

COMMIT

ROLLBACK

ACID

Atomicity

Consistency

Isolation

Durability

LOCKING MECHANISM

CAP THEOREM

TRIGGERS

SQL NORMALIZATION

DATABASE ISOLATION LEVELS

References

Web Basics Episode 2: Introduction to CSS

Introduction

Table of Contents

What is CSS?

CSS Example

CSS Selectors

CSS Selectors Examples

Ways of adding CSS to our HTML file

CSS Box Model

CSS display property

inline

inline-block

block

CSS Position Property

CSS Structural Classes

Some Structural class examples

CSS Specificity

CSS Media Query

Conclusion

References

Web Basics Episode 1: Introduction to HTML

Introduction

Table of Contents

What is HTML?

HTML Document structure

HTML Tags

Some Basic Tags

Singleton Tags

Important Singleton Tags

Semantic and Unsemantic Tags

Semantic Page Example

HTML Header Section

Difference between <header> and <head>

Navigation Section

Title

Favicon

Charset

UTF-8, ASCII and UNICODE

Viewport

HTML Body section

Block Level tags

<h1> to <h6>

Lists

`settings.py` detailed explanation

CSS `display` property

`inline`

`inline-block`

`block`

Difference between `<header>` and `<head>`

`<h1>` to `<h6>`

4. Create an empty database `sample`

5. Connect to `sample` database