JDBC SQL syntax

Structured query language (SQL) is a standardized language that allows you to perform operations on the database, such as creating items, reading content, updating content, and deleting entries.

SQL is all that is possible with almost any database support, allowing code to be written to the database independent of the underlying database.

This tutorial gives you SQL, which is a prerequisite for understanding the JDBC overview. This tutorial provides enough SQL to be able to create, read, update, and delete (often referred to as CRUD operations) data from a database.

For more on SQL, you can read our (link: #).

Create database:

CREATE DATABASE

Statement to create a new database. Grammar is:

SQL> CREATE DATABASE DATABASE_NAME;

Example:

The following SQL statement creates a database named EMP:

SQL> CREATE DATABASE EMP;

Delete database:

The DROP DATABASE statement is used to delete an existing DATABASE. Grammar is:

SQL> DROP DATABASE DATABASE_NAME;

Note: to create or delete, a database should have administrator privileges on the database server. Note that the database is deleted to store all the lost data in the database.

Create a table:

The CREATE TABLE statement is used to CREATE a new TABLE. Grammar is:

SQL> CREATE TABLE table_name
(
   column_name column_data_type,
   column_name column_data_type,
   column_name column_data_type
   ...
);

Example:
The following SQL statement creates a table with four fields named Employees:

SQL> CREATE TABLE Employees
(
   id INT NOT NULL,
   age INT NOT NULL,
   first VARCHAR(255),
   last VARCHAR(255),
   PRIMARY KEY ( id )
);

Delete table:
The DROP TABLE statement is used to DROP an existing TABLE. Grammar is:

SQL> DROP TABLE table_name;

Example:
The following SQL statement drops a table named Employees:

SQL> DROP TABLE Employees;

Insert data:

The syntax INSERT is similar to the following, where column1, column2, and so on indicate that the new data appears in each column:

SQL> INSERT INTO table_name VALUES (column1, column2, ...);

Example:
The following SQL INSERT statement inserts the previously created Employees database:

SQL> INSERT INTO Employees VALUES (100, 18, 'Zara', 'Ali');

SELECT the data:
The SELECT statement is used to retrieve data from the database. The SELECT of this syntax is:

SQL> SELECT column_name, column_name, ...
     FROM table_name
     WHERE conditions;

       
        WHERE clauses can use comparison operators such as =,! =, < , > , < =, > =, and BETWEEN and LIKE operators.

Example:
The following SQL statement selects the first and last column names of age where id =100 from the Employees table:

SQL> SELECT first, last, age 
     FROM Employees 
     WHERE id = 100;

       
        The following SQL statement is from the Employees table, where the first column selects age, and the first column contains Zara:

SQL> SELECT first, last, age 
     FROM Employees 
     WHERE first LIKE '%Zara%';

       
        The UPDATE data:
The UPDATE statement is used to UPDATE data. The UPDATE syntax is:

SQL> UPDATE table_name
     SET column_name = value, column_name = value, ...
     WHERE conditions;

       
        WHERE clauses can use comparison operators such as =,! =, < , > , < =, and > =, and BETWEEN and LIKE operators.

Example:

The following SQL UPDATE statement changes the age column of the employee whose ID is 100:

SQL> UPDATE Employees SET age=20 WHERE id=100;

DELETE the data:
The DELETE statement is used to DELETE data from the table. The syntax DELETE is:

SQL> DELETE FROM table_name WHERE conditions;

WHERE clauses can use comparison operators such as =,! =, < , > , < =, and > =, and BETWEEN and LIKE operators.

Example:
The following SQL DELETE statement deletes the record of the employee with ID 100:

SQL> DELETE FROM Employees WHERE id=100;