Published on Feb 22,2017
512 Views
Email Post

What is a Database?

Database is a collection of data in an organized way so that we can easily update and modify data present in the database. The data storage in database is permanent.

Eg: Oracle, MySQL, MS-Access, DB2, Sybase

How to store data in Java database?

Data will be stored in ‘Tables’ in database in the form of ‘Records’. Each row is one record in the database. Table is a collection of relational data in 2D i.e in the form of rows and columns.

What is SQL?

SQL or Structure Query Language is a language used to access and modify the data stored in database.  Through SQL one can insert values, query data, get desired results and retrieve data from database. It uses statements to access the database.

SQL Statements can be divided into two types:

  • DDL
  • DML

The DDL (Data Definition Language) statements are used for creation or deletion of database.

The commands present in DDL are:

  • Create (Create new table/database)
  • Alter (Alter content)
  • Drop (For deleting the entire table)

Usage of Create – The command is used to create table or database:

Create database database_name;//

Create table table_name(column1_name

Datatype, column2_name datatype);

Using MS-Access

Step 1 : Creating new file of file type ‘ Microsoft Access 2007 Database’

Step 2: Name it as dbdata

Step 3: Open folder

Step 4: Enable content when Security Alert pops-up

Step 5: Once inside, go to ‘create’->’query design’

Step 6: Click on SQL (top left corner)

Step 7: Click command prompt (“Query1”) and enter query

Step 8: Enter command: create table tab_name(“name of column1”,”datatype”,varchar(“size”);

Step 9: Run the Program

Step 10: Table ‘tab_name1” will be created

Alter

This command is used to rename an existing table or a column. It can also be used to modify, add and drop a column/field present in an existing table.

The Command for it is:

Renaming table

Alter table table_name rename new_table_name;

Rename column of table

Alter table table_name rename column old_column_name to new_column_name;

Add a column name

Alter table table_name add column_name datatype;

To modify datatype of column of a table

Alter table table_name modify column_name new_datatype;

To drop a column of a table

Alter table table_name drop column column_name;

Drop

This command is used to remove an object (value). We cannot get the table or values in it once the table is dropped.

The command is:

Drop table table_name;

DML (Data Manipulation Language)

These statements are used for managing the data in database. The commands present in DDL are:

  • Insert
  • Update
  • Delete
  • Select

Insert – This command is used to enter the records into the table. Records are the rows getting added into the table.

Command line : insert into table_name values(‘value_col_1’, ‘value_col_2’);

*If it’s string type, character type will be in quotes.

Update– This command is used to update the value of a record or column of a table. We use condition to know where the update is to be made.

Command line: update table set column_name=”value” where some_column_name=”value_of_some_column”;

Delete– This command is to delete the records (or many) from a table by specifying some condition where to delete the record). We use condition to know where the updation is to be made.

Delete from table_name where column_name = “value”;

Select– the select command is used to retrieve the records from the table

Command line: select column1_name, column2_name…. from table_name;

Select * from table_name;

Got a question for us? Mention them in the comments section and we will get back to you. 

Related Posts:

Introduction to JAVA/J2EE & SOA

JAVA/J2EE & SOA course

About Author
edureka
Published on Feb 22,2017

Share on

Browse Categories

Comments
1 Comment