What are the different types of tables in Oracle?

Vaibhav Kothia May 26, 2023 0 Comments

1. Heap-Organized Tables: These are the most common type of tables in Oracle. They are organized as a heap, which means that data rows are stored in an unordered fashion. For example, a table named “EMPLOYEES” can be created with the following command:

CREATE TABLE employees (
employee_id INTEGER,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
email VARCHAR2(50),
salary NUMBER
);

2. Index-Organized Tables: These tables are organized based on an index. This means that the data is stored in an ordered fashion based on the index key. For example, a table named “ORDERS” can be created with the following command:

CREATE TABLE orders (
order_id INTEGER,
customer_id INTEGER,
order_date DATE,
order_total NUMBER,
CONSTRAINT orders_pk PRIMARY KEY(order_id)
) ORGANIZATION INDEX;

3. Clustered Tables: These tables are organized based on a cluster key. This means that the data is stored in an ordered fashion based on the cluster key. For example, a table named “PRODUCTS” can be created with the following command:

CREATE TABLE products (
product_id INTEGER,
product_name VARCHAR2(50),
product_price NUMBER,
CONSTRAINT products_pk PRIMARY KEY(product_id)
) CLUSTER product_cluster(product_name);

4. Temporary Tables: These tables are used to store temporary data. They are usually created and populated with data for a specific purpose and then dropped when no longer needed. For example, a table named “TEMP_DATA” can be created with the following command:

CREATE GLOBAL TEMPORARY TABLE temp_data (
data_id INTEGER,
data_value VARCHAR2(50)
);

What is the difference between a view and a table?

Vaibhav Kothia May 26, 2023 0 Comments

A view is a virtual table that is created from the result set of a SQL query. It does not contain any data itself, but rather references the underlying tables and columns. For example, a view can be created to show the total number of orders from a certain time period.

A table is a physical representation of data in a database. It contains the actual data that is stored in the database. For example, a table can be created to store customer information, such as name, address, and phone number.

What is the difference between a primary key and a foreign key?

Vaibhav Kothia May 26, 2023 0 Comments

A primary key is a unique identifier for a row in a table. It can be a single column or a combination of columns. For example, a table of students may have a primary key of student_id, which is a unique identifier for each student.

A foreign key is a field in a table that is used to link two tables together. It is a reference to the primary key of another table. For example, a table of students may have a foreign key of class_id, which is a reference to the primary key of the class table.

What services does AWS provide?

Vaibhav Kothia May 26, 2023 0 Comments

AWS provides a variety of cloud-based services, including:

1. Compute services: Amazon Elastic Compute Cloud (EC2) for virtual servers and Amazon Lightsail for managed virtual private servers.

2. Storage services: Amazon Simple Storage Service (S3) for object storage, Amazon Elastic Block Store (EBS) for block storage, Amazon Elastic File System (EFS) for file storage, and Amazon Glacier for archival storage.

3. Database services: Amazon Relational Database Service (RDS) for relational databases, Amazon DynamoDB for NoSQL databases, and Amazon Redshift for data warehousing.

4. Networking services: Amazon Virtual Private Cloud (VPC) for secure networking, Amazon Route 53 for domain name system (DNS) services, and Amazon Direct Connect for private connections to AWS.

5. Security services: Amazon Identity and Access Management (IAM) for user authentication and authorization, Amazon CloudWatch for monitoring, and AWS Shield for DDoS protection.

6. Management services: AWS CloudFormation for infrastructure as code, AWS OpsWorks for configuration management, and AWS CloudTrail for logging and auditing.

7. Application services: Amazon API Gateway for API management, Amazon Simple Queue Service (SQS) for message queues, and Amazon Simple Notification Service (SNS) for notifications.

What are the different types of data types supported by PostgreSQL?

Vaibhav Kothia May 26, 2023 0 Comments

PostgreSQL supports a variety of data types:

1. Numeric Types:
– Integer (e.g. 1,2,3)
– Real (e.g. 3.1415)
– Double Precision (e.g. 2.718281828459045)
– Numeric (e.g. 12345.6789)
– Decimal (e.g. 123.45)

2. Character Types:
– Character (e.g. ‘A’)
– Character Varying (e.g. ‘Hello World’)
– Text (e.g. ‘This is a text’)

3. Date/Time Types:
– Date (e.g. ‘2021-02-19’)
– Time (e.g. ‘15:30:00’)
– Timestamp (e.g. ‘2021-02-19 15:30:00’)
– Interval (e.g. ‘1 day’)

4. Boolean Type:
– Boolean (e.g. true or false)

5. Geometric Types:
– Point (e.g. (1.5, 2.5))
– Line (e.g. [ (1.5, 2.5), (2.5, 3.5) ])
– Box (e.g. ( (1.5, 2.5), (2.5, 3.5) ))
– Circle (e.g. )

6. Network Address Types:
– Inet (e.g. ‘127.0.0.1/32’)
– Cidr (e.g. ‘127.0.0.1/24’)

7. Bit String Types:
– Bit (e.g. B’1010’)
– Bit Varying (e.g. B’1001’)

8. UUID Types:
– UUID (e.g. ‘a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11’)

What are the advantages of using SQLite?

Vaibhav Kothia May 26, 2023 0 Comments

SQLite is a lightweight, open source, serverless relational database management system (RDBMS) that is used for data storage and retrieval.

Advantages of using SQLite include:

1. Easy to Setup and Use: SQLite is a self-contained, serverless, zero-configuration, and transactional SQL database engine. It does not require a separate server process or system to operate.

2. Compact and Lightweight: SQLite is very small in size and requires minimal memory and disk space. The entire database is stored in a single file, making it easy to manage and back up.

3. High Performance: SQLite is capable of handling large amounts of data with ease. It is very fast and can handle thousands of queries per second.

4. Cross-Platform Support: SQLite is available for multiple platforms, including Windows, Linux, Mac OS X, and Android.

5. Open Source: SQLite is free and open source software, released under the BSD-3 Clause license.

Example:

Let’s say you are developing a mobile application and you need to store some data. You could use SQLite to create a database and store the data in it. Your application can then use SQL queries to access and manipulate the data stored in the database. This makes it easy to store, retrieve, and update data in the database.