PostgreSQL Tasks:

SAKTHIVEL S

By: Sakthivel

4 September 2024 at 18:03

Table I created table name is “movies”

TASK 1 : Show details for Vijay acted movies.

SELECT * FROM movies WHERE actor=’vijay’;

TASK 2 : Show Vijay movies names only.

SELECT movie FROM movies WHERE actor=’vijay’;

TASK 3 : Show details, lokesh kanagaraj directed movie and actor name starts with ‘k’ and ends with vowels[a,e,i,o,u].

SELECT * FROM movies WHERE director=’lokesh kanagaraj’ AND actor LIKE ‘k%’ AND actor SIMILAR TO ‘%[aeiou]’;

TASK 4 : Show movie name only, lokesh kanagaraj directed movie and actor name starts with ‘k’ and ends with vowels[a,e,i,o,u].

SELECT movie FROM movies WHERE director=’lokesh kanagaraj’ AND actor LIKE ‘k%’ AND actor SIMILAR TO ‘%[aeiou]’;

TASK 5 : Show movie name and actor name only, lokesh kanagaraj directed movie and actor name starts with ‘k’ and ends with vowels[a,e,i,o,u].

SELECT movie,actor FROM movies WHERE director=’lokesh kanagaraj’ AND actor LIKE ‘k%’ AND actor SIMILAR TO ‘%[aeiou]’;

TASK 6 : Show the details, director name starts and ends with same letter.

SELECT * FROM movies WHERE LEFT(director, 1)=RIGHT(director, 1);

TASK 7 : Show the movie name only, the director name starts and ends with same letter.

SELECT movie FROM movies WHERE LEFT(director, 1)=RIGHT(director, 1);

TASK 8 : Show the director name only, the director name start and ends with same letter.

SELECT director FROM movies WHERE LEFT(director, 1)=RIGHT(director, 1);

TASK 9 : Show the movie name, the actors have only five character.

SELECT movie FROM movies WHERE LENGTH(actor)=5;

TASK 10 : Show the movie name and actors name, actors have only five character.

SELECT movie,actor FROM movies WHERE LENGTH(actor)=5;

TASK 11 : Add a column salary_in_crore and values.

ALTER TABLE movies ADD COLUMN salary_in_crore INT;

UPDATE movies SET salary_in_crore =100 WHERE actor = ‘vijay’;

UPDATE movies SET salary_in_crore =70 WHERE actor = ‘kamal’;

UPDATE movies SET salary_in_crore =90 WHERE actor = ‘ajith’;

UPDATE movies SET salary_in_crore =40 WHERE actor = ‘karthi’;

UPDATE movies SET salary_in_crore =110 WHERE actor = ‘rajini’;

UPDATE movies SET salary_in_crore =50 WHERE actor = ‘dhanush’;

UPDATE movies SET salary_in_crore =5 WHERE actor = ‘soori’;

SELECT * FROM movies;

TASK 12 : Show actor name and salary,order by salary high to low.

SELECT actor, salary_in_crore FROM movies ORDER BY salary_in_crore DESC;

TASK 13 : Show top 3 actor based on salary.

SELECT DISTINCT actor, salary_in_crore FROM movies ORDER BY salary_in_crore DESC LIMIT 3;

SAKTHIVEL S
To import a .CSV/Excel file to PostgreSQL table:
4 September 2024 at 15:11

To import a .CSV/Excel file to PostgreSQL table:

SAKTHIVEL S

By: Sakthivel

4 September 2024 at 15:11

Insert values From a .csv file

Step 1: Create a .csv or .xlsx file

Step 2: Insert values from a .csv or .xlsx file

Step 3: Copy the file and paste in to the location where pSQL installed:

-> “/var/lib/postgresql” and paste the file

Step 4: Go to the terminal and connect the database.

Step 5: Create table in the database with the columns given in the file header.

Step 6: Enter the follwing query in psql

-> COPY table_name FROM ‘/var/lib/postgresql/fine_name.csv’ WITH CSV HEADER;

Here, “movies” is the table name and the location where the “movies.csv” file is present.

9 values copied

Step 7: Then, write a select query to fetch all the rows.

successfully import the datas in to postgres database from csv or excel file

Parotta Salna
Different Database Models
23 August 2024 at 01:50

Different Database Models

Parotta Salna

By: Mr.ParottaSalna

23 August 2024 at 01:50

Database models define the structure, relationships, and operations that can be performed on a database. Different database models are used based on the specific needs of an application or organization. Here are the most common types of database models:

1. Hierarchical Database Model

Structure: Data is organized in a tree-like structure with a single root, where each record has a single parent but can have multiple children.
Usage: Best for applications with a clear hierarchical relationship, like organizational structures or file systems.
Example: IBM’s Information Management System (IMS).
Advantages: Fast access to data through parent-child relationships.
Disadvantages: Rigid structure; difficult to reorganize or restructure.

2. Network Database Model

Structure: Data is organized in a graph structure, where each record can have multiple parent and child records, forming a network of relationships.
Usage: Useful for complex relationships, such as in telecommunications or transportation networks.
Example: Integrated Data Store (IDS).
Advantages: Flexible representation of complex relationships.
Disadvantages: Complex design and navigation; can be difficult to maintain.

3. Relational Database Model

Structure: Data is organized into tables (relations) where each table consists of rows (records) and columns (fields). Relationships between tables are managed through keys.
Usage: Widely used in various applications, including finance, retail, and enterprise software.
Example: MySQL, PostgreSQL, Oracle Database, Microsoft SQL Server.
Advantages: Simplicity, data integrity, flexibility in querying through SQL.
Disadvantages: Can be slower for very large datasets or highly complex queries.

4. Object-Oriented Database Model

Structure: Data is stored as objects, similar to objects in object-oriented programming. Each object contains both data and methods for processing the data.
Usage: Suitable for applications that require the modeling of complex data and relationships, such as CAD, CAM, and multimedia databases.
Example: db4o, ObjectDB.
Advantages: Seamless integration with object-oriented programming languages, reusability of objects.
Disadvantages: Complexity, not as widely adopted as relational databases.

5. Document-Oriented Database Model

Structure: Data is stored in document collections, with each document being a self-contained piece of data often in JSON, BSON, or XML format.
Usage: Ideal for content management systems, real-time analytics, and big data applications.
Example: MongoDB, CouchDB.
Advantages: Flexible schema design, scalability, ease of storing hierarchical data.
Disadvantages: May require denormalization, leading to potential data redundancy.

6. Key-Value Database Model

Structure: Data is stored as key-value pairs, where each key is unique, and the value can be a string, number, or more complex data structure.
Usage: Best for applications requiring fast access to simple data, such as caching, session management, and real-time analytics.
Example: Redis, DynamoDB, Riak.
Advantages: High performance, simplicity, scalability.
Disadvantages: Limited querying capabilities, lack of complex relationships.

7. Column-Family Database Model

Structure: Data is stored in columns rather than rows, with each column family containing a set of columns that are logically related.
Usage: Suitable for distributed databases, handling large volumes of data across multiple servers.
Example: Apache Cassandra, HBase.
Advantages: High write and read performance, efficient storage of sparse data.
Disadvantages: Complexity in design and maintenance, not as flexible for ad-hoc queries.

8. Graph Database Model

Structure: Data is stored as nodes (entities) and edges (relationships) forming a graph. Each node represents an object, and edges represent the relationships between objects.
Usage: Ideal for social networks, recommendation engines, fraud detection, and any scenario where relationships between entities are crucial.
Example: Neo4j, Amazon Neptune.
Advantages: Efficient traversal and querying of complex relationships, flexible schema.
Disadvantages: Not as efficient for operations on large sets of unrelated data.

9. Multimodel Database

Structure: Supports multiple data models (e.g., relational, document, graph) within a single database engine.
Usage: Useful for applications that require different types of data storage and querying mechanisms.
Example: ArangoDB, Microsoft Azure Cosmos DB.
Advantages: Flexibility, ability to handle diverse data requirements within a single system.
Disadvantages: Complexity in management and optimization.

10. Time-Series Database Model

Structure: Specifically designed to handle time-series data, where each record is associated with a timestamp.
Usage: Best for applications like monitoring, logging, and real-time analytics where data changes over time.
Example: InfluxDB, TimescaleDB.
Advantages: Optimized for handling and querying large volumes of time-stamped data.
Disadvantages: Limited use cases outside of time-series data.

11. NoSQL Database Model

Structure: An umbrella term for various non-relational database models, including key-value, document, column-family, and graph databases.
Usage: Ideal for handling unstructured or semi-structured data, and scenarios requiring high scalability and flexibility.
Example: MongoDB, Cassandra, Couchbase, Neo4j.
Advantages: Flexibility, scalability, high performance for specific use cases.
Disadvantages: Lack of standardization, potential data consistency challenges.

Summary

Each database model serves different purposes, and the choice of model depends on the specific requirements of the application, such as data structure, relationships, performance needs, and scalability. While relational databases are still the most widely used, NoSQL and specialized databases have become increasingly important for handling diverse data types and large-scale applications.

Terraform code for AWS Postgresql RDS

TK Dhanasekar

By: Dhanasekar

7 January 2024 at 17:19

create directory postgres and navigate
$ mkdir postgres && cd postgres
create main.tf file
$ vim main.tf

provider "aws" {
}
resource "aws_security_group" "rds_sg" {
  name = "rds_sg"
  ingress {
    from_port       = 5432
    to_port         = 5432
    protocol        = "tcp"
    cidr_blocks = ["0.0.0.0/0"]
  }
}

resource "aws_db_instance" "myinstance" {
  engine               = "postgres"
  identifier           = "myrdsinstance"
  allocated_storage    =  20
  engine_version       = "14"
  instance_class       = "db.t3.micro"
  username             = "myrdsuser"
  password             = "myrdspassword"
  parameter_group_name = "default.postgres14"
  vpc_security_group_ids = ["${aws_security_group.rds_sg.id}"]
  skip_final_snapshot  = true
  publicly_accessible =  true
}

output "rds_endpoint" {
  value = "${aws_db_instance.myinstance.endpoint}"
}

save and exit
$ terraform init
$ terraform plan
$ terraform apply -auto-approve
Install postgres client in local machine
$ sudo apt install -y postgresql-client
To access AWS postgresql RDS instance
$ psql -h <end_point_URL> –p=5432 –username=myrdsuser –password –dbname=mydb
To destroy postgresql RDS instance
$ terraform destroy -auto-approve

Normal view