SQL Syntax

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

This chapter gives an overview of SQL, which is a prerequisite to understand JDBC concepts. After going through this chapter, you will be able to Create, Create, Read, Update, and Delete (often referred to as CRUD operations) data from a database.

Create Database

The CREATE DATABASE statement is used for creating a new database. The syntax is −


                    SQL> CREATE DATABASE EMP;
Drop Database

The DROP DATABASE statement is used for deleting an existing database. The syntax is −


                    SQL> DROP DATABASE DATABASE_NAME;
Create Table

The CREATE TABLE statement is used for creating a new table. The syntax 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 named Employees with four columns −


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

The DROP TABLE statement is used for deleting an existing table. The syntax is −


                    SQL> DROP TABLE table_name;
Example

The following SQL statement deletes a table named Employees −


                    SQL> DROP TABLE Employees;
INSERT Data

The syntax for INSERT, looks similar to the following, where column1, column2, and so on represents the new data to appear in the respective columns −


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

The following SQL INSERT statement inserts a new row in the Employees database created earlier −


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

The SELECT statement is used to retrieve data from a database. The syntax for SELECT is −


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

The WHERE clause can use the comparison operators such as =, !=, <, >, <=,and >=, as well as the BETWEEN and LIKE operators.

Example

The following SQL statement selects the age, first and last columns from the Employees table, where id column is 100 −


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

The following SQL statement selects the age, first and last columns from the Employees table where first column contains Zara −


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

The UPDATE statement is used to update data. The syntax for UPDATE is −


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

The WHERE clause can use the comparison operators such as =, !=, <, >, <=,and >=, as well as the 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 Data

The DELETE statement is used to delete data from tables. The syntax for DELETE is −


                    SQL> DELETE FROM table_name WHERE conditions;

The WHERE clause can use the comparison operators such as =, !=, <, >, <=,and >=, as well as the BETWEEN and LIKE operators.

Example

The following SQL DELETE statement deletes the record of the employee whose id is 100 −


                    SQL> DELETE FROM Employees WHERE id=100;