Now that we have learned about Java Database Connectivity (JDBC), it is time to learn about some database concepts which you find useful during interviews or while programming.
The DBMS is the software which is situated between a human being and the data storage. If you want to manipulate the data available in the storage, then you will have to interact the DBMS with SQL commands.
The SQL (Structure Query Language) is a standard which is followed by all the DB vendors to interact with the data and is provided by ANSI (American National Standard Institute).
There is some validation by the DB task to implement their database.
Some ANSI style syntaxes are more complicated and make it tricky to code sometimes. To avoid these complications DB vendors provide their own syntax called the Theta style syntax.
All the DBMS store their data in a tabular format with rows and columns.
Row contains all the values related to one entity.
The column contains similar types of values related to all the entities.
A Database contains tables, and tables contain rows and columns. One row denotes one record. One column represents the common entity of all the rows.
This represents which type of information can be stored in the database. There are eight primitive data types in java. They are divided into numeric, textual and Boolean and null primitive data types.
There are seven Oracle Database data types. They are as follows:
CHAR:
The CHAR data type stores fixed-length character strings. When you create a table with a CHAR column, you must specify a string length (in bytes or characters) between 1 and 2000 bytes, for the CHAR column width. The default is 1 byte. Oracle then guarantees that:
When you insert or update a row in the table, the value for the CHAR column has a fixed length
If you give a shorter value, then the value is blank-padded to the fixed length
If a value is too large, the Oracle Database returns an error.
Oracle Database compares CHAR values using blank-padded comparison semantics.
VARCHAR2:
(reserved keyword for future reference, as varchar will be replaced eventually)
The Varchar2 data type stores variable-length character strings. When you create a table with a VARCHAR2 column, you specify a maximum string length (in bytes or characters) between 1 and 4000 bytes for the VARCHAR2 column. For each row, the Oracle Database stores each value in the column as a variable-length field unless a particular value exceeds the column's maximum length, in which case the Oracle Database returns an error. Using VARCHAR2 and VARCHAR saves a lot of space used by the table.
For example, assume that you have declared a column VARCHAR2 with a maximum size of 50 characters.
In a single-byte character set, if only 10 characters are given for the VARCHAR2 column value in a particular row, the column in the row's row piece stores only 10 characters (10 bytes), not 50.
Oracle Database compares VARCHAR2 values using non-padded comparison semantics.
Varchar Datatype:
The VARCHAR datatype is synonymous with the VARCHAR2 datatype.
Date:
The Date datatype stores point-in-time values (dates and times) in a table. The Date datatype stores the year (including the century), the month, the day, the hours, the minutes, and the seconds (after midnight).
For input and output of dates, the standard Oracle date format is DD-MON-YY, as follows:
‘13-NOV-92’
Blob:
The Blob data type stores unstructured binary data in the database. Blobs can store up to 128 terabytes of binary data.
Blobs participate fully in transactions. Changes made to a Blob value by the DBMS_LOB package, PL/SQL, or the OCI can be committed or rolled back. However, Blob locators cannot span transactions or sessions.
Clob:
CLOBs and NCLOBs participate fully in transactions. Changes made to a CLOB or NCLOB value by the DBMS_LOB package, PL/SQL, or the OCI can be committed or rolled back. However, CLOB and NCLOB locators cannot span transactions or sessions.
You cannot create an object type with NCLOB attributes, but you can specify NCLOB parameters in a method for an object type.
File:
Char, Varchar
text, (Unlimited Char use)
int
long
float
double
date
blob
clob
file
The SQL operations are as follows:
Arithmetic operations (+, -, *, /, mod)
Relation operations (>, >=, <=, <, < >, =)
Logical operations (and, or, not)
In operators
Between and operator
Like operator
Math functions
String functions
Date functions
Conversion functions
Aggregate functions
to -number→ Character to number
to -character→ Number to character or date to character
to -date format→ Character to date or date of one format to date or another
Count
Sum
Average
Max
Min
Question 1: Display the number of books available in the book store.
Answer: Select count (*)
Question 2: Display different types of total books available.
Answer: Select count (*) from books;
Question 3: Display total books available.
Answer: Select sum (qly) from books;
Question 4: Display the maximum cost of books.
Answer: Select max (cost) from books; Select min (cost) from books;
An Index is an ordered list of elements belonging to one column or a combination of two or more columns.
Syntax:
CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,_ _ _ _ _);
There are Two Types of Indexes:
Simple Index
Composite Index
When an index is created on a single column of a tablethen it is called simple index.
Example:
Create index i1 On customers (email);
Now on disk. a space will be occupied with email in ascending order and a pointer to them. You search will become easier now.
When an index is created with a combination of two or more columns then it is called composite index.
Example:
| Customer ID | Customer Name | Phone | City | |
|---|---|---|---|---|
| 1. | ||||
| 2. | ||||
| 3. | ||||
| 4. | ||||
| 5. |
Select from customers where email='….'
This query directly goes through the customers table when there is no index table. But it is time consuming and not efficient.
It goes into the index table when the index is created. Indexes mainly help to speed up the result of a query.
If you do not have any index for assuming columns, then all the elements of that column will be searched for a given element one by one. But it takes more time when data is vast.
To avoid this delay we can create indexes on that column.
When you create an index then all the elements of that column will be created and will be placed in a separate object.
When you search elements, DBMS will use this order list to find an element using a better search algorithm.
No need to use index directly because the index will be used by DBMS automatically wherever select statement contains the column which has the index.
If a primary key exists, DBMS will create the index automatically.
If the tables contain a composite primary key, a composite index is created by the DBMS automatically.
A table can have one or more indexes
Syntax:
Drop index index_name;
Example:
drop index i1;
Sequences are used to generate the values automatically for any given column. They generate only number type value and are not suitable for character type values.
Syntax:
Create sequence seq_name Start with int_value
Min value int_value Max value int_value Increment by int_value Cycle
No cycle;
Example:
Create sequence s1 Start with 101
Increment by 1;
Sequence object has the following variations:
Current Value - return the current value
Next Value - returns the current value and then increment
Example:
Create sequences S2 Start with 101
Max value 1000
Increment by 1 Cycle;
Create sequence S3 Start with 1000
Min Value 101
Increment by -1 No cycle;
This drops a sequence from the table.
Syntax:
Drop sequence seq_name;
Example:
Drop Sequence S1;
Syntax:
Grant select, delete, update, insert, create, drop,_ _ _ _ _.
On object_name to user_name;
Table name, view name index name, seq. name
Example:
Grant select, update on students to kiran; Grant select on ad_view to kiran, vas;
Syntax:
Revoke select, delete, update, insert, create, drop,_ _ _ _ _ _.
On object name from user_name;
Example:
Revoke select, update on students from kiran; Revoke select on ad_view from kiran, vas;
In MySQL:
Create table account (ID int, name char (12), balance double);
In Create:
Create table account (ID number (2), name char (5);
balance number (g,2));
Insert into account values (1,'a', 1000);
Insert into account values (2, 'b', 2000);
Insert into account values (3, 'c', 4000);
Insert into account values (4, 'd', 9000);
Insert into account values (5, 'e', 6000);
Insert into account values (6, 'f', 7000);
Insert into account values (7, 'g', 5000);
Insert into account values (8, '4', 3000);
Insert into account values (9, '4', 4000);
Display nth row(6) (Oracle)
Select *from (select id, name, bal, row number from accounts where
Row num (8) where rn=6;
Select from account limit 5,1
Display rows from m(2) to n(5) (Oracle)
Select *from (select balance, row number from account where row number <7)
Where in between 2 & 5;
Select * from account limit 1,4
Display nth highest
(Select * from account where 3=(select count (distinct balance) from account
where acc.bal<=bal);
Display Top N rows : (Oracle)
Select * from (select *from account order by ID) Where row num < 5;
Select * from (select *from account order by ID description) Where row num <5;
Select * from account where row num <5;
Select * from account limit 4; (limit start index, no. of records)
Display every n(3)th row : (Oracle)
Select * from account where (row ID, 0) in
(Select row ID mod (rownum, 3) from accounts);
Select *from account where (ID.0) in
(select ID, mod(ID, 3) from account);