Database Basic
What is ACID Property?
ACID Property:- It is a concept for evaluation of databases (all RDBMS) and their architecture.
Atomicity – Atomicity states the principle of All or none.
This means that either all the SQL Statements within the transaction will be executed or no Sql statement will be executed.
Consistency:- Consistency states that only valid data will be written into the database.
That means if any Sql transaction violets the rules or constraints define on the database to make it consistent, then all the statements within the transaction will be Rollback. Or in other words the whole transaction will be rolled back.
Isolation :- The Isolation state that if multiple transaction try to work on the database at the same time,then these transaction will not interfere with each other. It means the second transaction will not work unless and until the previous transaction completes its work and the work is commited.
Durability:- Durability states that once the transaction is committed, then the database should not be lost in case of Software failures or hardware failures. Durability is maintained with the help of the database backups and transaction logs.
How to view data from the database? What is SQL?
SQL is stands for Structure query language. This language is used to create, manipulate and manage database. There are many databases software available for writing SQL. Some of the most famous Database Software’s are:-
Basic SQL is a really simple language, simpler than HTML. The commands are so simple, that you can even guess them.
For instance, if you want to create tables the command is CREATE Table. Similarly,
1. Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:
CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object
2. Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:
SELECT - retrieve data from the a database
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remain
MERGE - UPSERT operation (insert or update)
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency
3. Data Control Language (DCL) statements. Some examples:
GRANT - gives user's access privileges to database
REVOKE - withdraw access privileges given with the GRANT command
COMMIT - save work done.
SAVEPOINT - identify a point in a transaction to which you can later roll back.
ROLLBACK - restore database to original since the last COMMIT.
SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use.
What is Database and Table?
A database is a large, organized collection of information. We store our Data in Database using Tables. Tables are containers that store data. They are composed of columns (vertically) and rows (horizontally).
You can think of database as a folder or as file cabinet. A database consists of one or many tables. It’s like a file cabinet consists of one or many files. Inside files there are pages, in the same way inside tables there is data. So the structure is like this
File Cabinet -> has files -> have Pages
Database -> has Tables -> have Data
For example, a database for a shopping cart website may have many tables for storing products, customers, orders.
What is DATA?
Data is a representation of facts, concepts or instructions in a formal manner suitable for communication, interpretation or processing by human beings or by computers. Data is stored in tables as rows and different information about the data subject as columns.
RDBMS - (Relational DataBase Management System)
The software that handles our data or databases is known as RDBMS (Relational Database Management System).
Examples of RDBS are ORACLE, MySQL, Microsoft SQL Server, MS Access, DB2 and many more...
In RDBMS tables have relations with each other or in other words, tables are joined/connected with each other.
SQL Concepts
There are some SQL Concepts that we must know before we can start using SQL Language.
Tables Consist of Columns and Rows. When we create table we specify the names and types of columns, to make sure that proper data is stored in our columns.
The main Database concepts that we are going to learn here are
- Data types
- Primary Key
- Auto Increment
- Not Null
- Unsigned
To understand these Database Concepts consider the following table. We have created a table here for storing our products information. You can think of this table as a record register for a shop keeper.
In this table our columns are ItemID, ItemName, ItemPrice, ItemCat:
Data Type
Notice that in the above table every column has a specific type of data. For example, ItemID has only numbers (1, 2, 3).
Similarly ItemName has few characters string for example benz, laptop. That means every column has a special datatype.
- ItemID is int
- ItemName is varchar
- ItemPrice is decimal
- ItemCat is varchar
Primary Key
Primary Key uniquely identifies a record in table. Consider the above table example, If you notice every Value in ItemID column in unique and not repeated. That is why ItemID column in this example is Primary key column. That means two or more Item Names, Prices, Category, Description can be similar. But Item ID will always be unique.
- Primary Key is always unique
- Primary Key never repeated or kept null
- Primary Key can be on one column or many columns together
- MySQL will automatically index Primary Key column
Auto Increment
Auto Increment is used when we don't want to enter value and we want SQL to handle values. For example in above table ItemID column has product IDs. We will enter name, price, cat, description of products and database will automatically assign it an id, if we define our ItemID column as auto increment. In MySQL the keyword that defines auto increment is auto_increment, on the other hand the same action is performed in MSSQL server by identity.
Un-Signed
Unsigned means that the column can only have a zero or positive value.
What is ACID Property?
ACID Property:- It is a concept for evaluation of databases (all RDBMS) and their architecture.
This means that either all the SQL Statements within the transaction will be executed or no Sql statement will be executed.
Consistency:- Consistency states that only valid data will be written into the database.
That means if any Sql transaction violets the rules or constraints define on the database to make it consistent, then all the statements within the transaction will be Rollback. Or in other words the whole transaction will be rolled back.
Isolation :- The Isolation state that if multiple transaction try to work on the database at the same time,then these transaction will not interfere with each other. It means the second transaction will not work unless and until the previous transaction completes its work and the work is commited.
Durability:- Durability states that once the transaction is committed, then the database should not be lost in case of Software failures or hardware failures. Durability is maintained with the help of the database backups and transaction logs.
How to view data from the database? What is SQL?
SQL is stands for Structure query language. This language is used to create, manipulate and manage database. There are many databases software available for writing SQL. Some of the most famous Database Software’s are:-
- Microsoft SQL Server - Oracle - MySQL Server - DB2 - Sybase - Microsoft AccessMAIN SQL Commands
Basic SQL is a really simple language, simpler than HTML. The commands are so simple, that you can even guess them.
For instance, if you want to create tables the command is CREATE Table. Similarly,
INSERT INTO is for inserting data into table DELETE FROM is for deleting data from tables SELECT FROM is for selecting or viewing data from tables UPDATE is for updating data in tablesIn SQL Language has following types of Commands:
1. Data Definition Language (DDL) statements are used to define the database structure or schema. Some examples:
CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object
2. Data Manipulation Language (DML) statements are used for managing data within schema objects. Some examples:
SELECT - retrieve data from the a database
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remain
MERGE - UPSERT operation (insert or update)
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency
3. Data Control Language (DCL) statements. Some examples:
GRANT - gives user's access privileges to database
REVOKE - withdraw access privileges given with the GRANT command
COMMIT - save work done.
SAVEPOINT - identify a point in a transaction to which you can later roll back.
ROLLBACK - restore database to original since the last COMMIT.
SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use.
What is Database and Table?
A database is a large, organized collection of information. We store our Data in Database using Tables. Tables are containers that store data. They are composed of columns (vertically) and rows (horizontally).
You can think of database as a folder or as file cabinet. A database consists of one or many tables. It’s like a file cabinet consists of one or many files. Inside files there are pages, in the same way inside tables there is data. So the structure is like this
File Cabinet -> has files -> have Pages
Database -> has Tables -> have Data
For example, a database for a shopping cart website may have many tables for storing products, customers, orders.
What is DATA?
Data is a representation of facts, concepts or instructions in a formal manner suitable for communication, interpretation or processing by human beings or by computers. Data is stored in tables as rows and different information about the data subject as columns.
RDBMS - (Relational DataBase Management System)
The software that handles our data or databases is known as RDBMS (Relational Database Management System).
Examples of RDBS are ORACLE, MySQL, Microsoft SQL Server, MS Access, DB2 and many more...
In RDBMS tables have relations with each other or in other words, tables are joined/connected with each other.
SQL Concepts
There are some SQL Concepts that we must know before we can start using SQL Language.
Tables Consist of Columns and Rows. When we create table we specify the names and types of columns, to make sure that proper data is stored in our columns.
The main Database concepts that we are going to learn here are
- Data types
- Primary Key
- Auto Increment
- Not Null
- Unsigned
To understand these Database Concepts consider the following table. We have created a table here for storing our products information. You can think of this table as a record register for a shop keeper.
In this table our columns are ItemID, ItemName, ItemPrice, ItemCat:
ItemID
|
ItemName
|
ItemPrice
|
ItemCat
|
1
|
Premonition
|
100
|
New
|
2
|
Benz
|
299
|
Cars
|
3
|
Laptop
|
999
|
Computers
|
Data Type
Notice that in the above table every column has a specific type of data. For example, ItemID has only numbers (1, 2, 3).
Similarly ItemName has few characters string for example benz, laptop. That means every column has a special datatype.
- ItemID is int
- ItemName is varchar
- ItemPrice is decimal
- ItemCat is varchar
Primary Key
Primary Key uniquely identifies a record in table. Consider the above table example, If you notice every Value in ItemID column in unique and not repeated. That is why ItemID column in this example is Primary key column. That means two or more Item Names, Prices, Category, Description can be similar. But Item ID will always be unique.
- Primary Key is always unique
- Primary Key never repeated or kept null
- Primary Key can be on one column or many columns together
- MySQL will automatically index Primary Key column
Auto Increment
Auto Increment is used when we don't want to enter value and we want SQL to handle values. For example in above table ItemID column has product IDs. We will enter name, price, cat, description of products and database will automatically assign it an id, if we define our ItemID column as auto increment. In MySQL the keyword that defines auto increment is auto_increment, on the other hand the same action is performed in MSSQL server by identity.
Un-Signed
Unsigned means that the column can only have a zero or positive value.
No comments:
Post a Comment