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:
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);
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
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:
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;
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 – 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.