agilemeadow Devlog

Searching what the reserved words are before you create tables in Oracle is crucial. 

In the following link, you can see the reserved words. 

In my first group project, I remember that we used some reserved words to create tables, so errors occurred when we were creating. I created new tables on eXERD, and the system said Invalid table name.

https://docs.oracle.com/cd/B19306_01/em.102/b40103/app_oracle_reserved_words.htm

If you find it hard to come up with new words instead of reserved words, you can refer to this website for synonyms. 

https://www.wordhippo.com/

In terms of SQL, you can work on the command prompt in other applications like Eclipse. In this post, we are going to look at working with Eclipse.

1. Copy the JDBC Driver file for Oracle to the Java installation location (C:\Program Files\Java\jre1.8.0_211\lib\ext). C:\oraclexe\app\oracle\product\11.2.0\server\jdbc\lib\ojdbc6.jar file

2.Copy to C:\Program Files\Java\jre1.8.0_211\lib\ext

3. Set up Oracle Database Integration in Eclipse
From the Project Explorer screen in Eclipse, you can view the JRE System Library
Right click - Properties - Installed JRE button click
jre8 Select and click the Edit button - Click the Add External JAR button
C:\Program Files\Java\jre8\lib\ext\ojdbc6.jar file

4. Re-drive the Eclipse

5. Create a simple Java-Oracle file and test it.

import java.sql.*;

   public class JDBC_Connect02{
   public static void main(String[] args)  {

   /** ORACLE JDBC Driver Test *****************************************/
	String driver = "oracle.jdbc.driver.OracleDriver"; 
	String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
   /*******************************************************************/

   /** My-SQL JDBC Driver *********************************************/
   //	String driver ="com.mysql.jdbc.Driver";
   //	String url = "jdbc:mysql://localhost/academy";
   /*******************************************************************/

       Connection con = null;

       try{

         Class.forName(driver);

   /**   ORACLE에서 Connection 객체 ***********************************/
          con = DriverManager.getConnection(url, "scott", "tiger" );
   /*******************************************************************/


   /**   My-SQL에서 Connection 객체 ***********************************/
   //	  con = DriverManager.getConnection(url, "totoro", "1234" );
   /*******************************************************************/

         System.out.println("Database connection success.");

       } catch(Exception e){
	    	   System.out.println("Database connection failed.");
		   e.printStackTrace();
       } finally{
		try{
		     if( con != null )        
                     con.close();
		   } catch(Exception e){
			System.out.println( e.getMessage( ));  
                                }
      }
    }
  }  

6. If the database connection failure message appears,
C:\oracle\product\11.2.0\db_1\NETWORK\ADMIN folder listener.ora
tnsnames.ora Check the file Host= computer name, Port=1521, etc.

Setting on Eclipse

1.window - show view - others - Data Management File - Data Source Explorer -> Open
2.Database Connections - Right click - New - Oracle - (Insert like attached) - save password - "ping succeed"

To create SQL file (Connection has to be on)

1. Create SQL package
2. New - others - SQL Development - SQL File - write a file name
3. Connection profile

4.enter 'select * from tab;' block -> Alt + X to execute *To comment out : --

5. To create the table, write "

create table customer( no number(4) primary key,
name varchar2(20),
email varchar2(20),
tel varchar2(20) );

"

6. Again, block 'select * from tab;' -> Alt + x, and you will see this table.

What is Structured Query Language (SQL)?
As a DB standard language (specified by ISO) with its syntax, most databases use SQL to query, enter, modify, and delete data.

Types of SQL

• Data Definition Word (DDL): A database administrator or application programmer is stored in a data dictionary as the language for defining the logical structure of the database.
• Data manipulation words (DML): As the language used to manipulate data stored in the database, perform data retrieval (Retrieval), addition (Insert), deletion, and update operations.
• Data Control Language (DCL): Language used to manage transactions on database systems, such as granting access to data

SQL Statements

*CRUD

In computer programming, create, read, update, and delete(CRUD) are the four basic operations of persistent storage. They correspond to 'insert', 'select', 'update', and 'delete' in SQL so it is vital to remember the statements.

<DDL>

o Create a table

create table table-name (
				column name data type,
				Column name data type, ……….);

create table member01(
				id varchar2(20),
				name varchar2(20),
				address varchar2(50),
				phone varchar2(20));

o Table list output

select * from tab;

o Table structure output

describe member01;

o Rename the table

alter table old table name rename to new table name;
alter table member01 rename to member02;

The table name can also be changed.

 rename member01 to member02;

o Add a new column to the table (add operator)

alter table member01 add (password varchar2(30));

o Modify the column of the table (modify operator)

 alter table member01 modify (password varchar2(50) not null);

Other data types if the column already has data cannot be changed too.

o Delete columns in the table (drop operator)

alter table member01 drop column column_name;
alter table member01 drop column password;

o Delete Constraints

 alter table member01 drop primary key;

o Deleting a table

drop table member01;

How do I delete this temporary table parmanently?

purge recyclebin;

How do I delete a table completely from scratch?

 drop table member01 purge;

<DML>

1. INSERT(Data Input)
Format: insert into tablename (column1, column2,...) values (data1, data2,...);
insert into table name values (data1, data2,...);

insert into dept01(deptno, dname, loc)
			values(10,'ACCOUNTING', 'NEW_YORK');
            
insert into dept01(dname, loc, deptno)
			values('RESEARCH', 'DALLAS', 20);
            
insert into dept01 values(30, 'SALES', 'CHICAGO');
insert into dept01 values(40, 'OPERATIONS','BOSTON');

Following is an example of creating a row by using 'insert'. It has to be '' not "" when we insert the values. SQL is not sensitive with Uppercase/Lowercase, but it has to be distinguished in terms of values. As you see below, here are 2 ways to insert the values. Depending on the situation, one can be chosen.

2. UPDATE (Data Modification)
Format: update table name set column 1 = Value to modify 1,
Column 2 = Value to modify 2,...
where conditional;

If you don't use 'where', the address of the whole table will be changed, so be careful!

After being updated, the address value of table 'test2' is changed.

3. DELETE (Data Deletion)
format: delete from table name where conditional;

To delete it is relatively easy. After deleting from the table, you can see no rows selected.

Setting up

The new version of Oracle takes up a lot of memory and RAM, so I downloaded the XE11 version. OracleXETNSListener and OracleServiceXE have to be constantly running on your computer, so once you download the app, you will find that turning on/off takes more time. Therefore, when you don't use it, you would better set the Startup type "Manual" instead of "Automatic."

Connecting to Client

Open the command prompt and enter 'sqlplus name of the account/password. to show the user's name, enter 'show user'.

You could also enter 'sqlplus,' and the prompt will ask for your user name and password.

To change the account, you could use 'connect account name/password as sysdba'

To be safe from the mistakes you will make with the system account, Oracle offers an account named 'scott' for demonstration purposes. According to legend, this account was named after Bruce Scott, co-author and co-architect of Oracle v1 to v3, and the password was the name of his daughter's cat, Tiger. (What a monument!)

To set your account's password, you can use 'alter user account name identified by password'. To see the list of the table, insert 'select*from tab;' and you will see 4 tables on the list.

To exit, you can either insert 'exit' or 'quit', and you will see this :

*When you forget the password of DBA and USER

It is impossible to know your previous password but you can reset your password. Enter 'sqlplus /"as sysdba"' and check 'show user' first, and enter 'alter user ID identified by yournewpassword;'