SQL Syntax

SQL Syntax is easy and most of the database action you can done using SQL statement. Following are fewer most frequently useful SQL Query syntax,

SQL Syntax References


SQL CREATE DATABASE Syntax

CREATE DATABASE database_name;

Example: 

SQL> CREATE DATABASE user_data;

SQL DROP DATABASE Syntax

DROP DATABASE database_name;

Example: 

SQL> DROP DATABASE user_data;

SQL CREATE TABLE Syntax

CREATE TABLE [ IF NOT EXISTS ] table_name(
    column_name datatype[(size)] [ NULL | NOT NULL ],
    column_name datatype[(size)] [ NULL | NOT NULL ],
    [ constraint_name 
        PRIMARY KEY ( col1, col2, ... ) |
        FOREIGN KEY ( col1, col2, ... ) REFERENCES table_2 [ ( col1, col2, ... ) 
            [ ON UPDATE | ON DELETE 
                [ NO ACTION | SET NULL | SET DEFAULT | CASCADE ] 
            ] 
        ] |
        UNIQUE ( col1, col2, ... ) |
        CHECK ( expression )
    ]
    ...
);

Example: 

SQL> CREATE TABLE users_info(
      no NUMBER(3) NOT NULL,
      name VARCHAR(30),
      address VARCHAR(70),
      contact_no VARCHAR(12),
      PRIMARY KEY (no)
);

SQL DESCRIBE table column Syntax

DESCRIBE table_name;

SQL INSERT Syntax

Insert row values: 

INSERT INTO table_name [ ( column_name1, column_name2, ... ) ]
    VALUES ( value1_row1, value2_row1, ... );

Example: 

SQL> INSERT INTO users_info (no,name,address)
      VALUES (1, 'Opal Kole', '63 street Ct.');

Insert multiple row values: 

INSERT ALL
    INTO table_name [ (column_name1, column_name2, ...) ] VALUES (record1_value1, record1_value2, ...)
    INTO table_name [ (column_name1, column_name2, ...) ] VALUES (record2_value1, record2_value2, ...)
    INTO table_name [ (column_name1, column_name2, ...) ] VALUES (record3_value1, record3_value2, ...)
    ....
    SELECT * FROM dual;

Example: 

SQL> INSERT ALL
  INTO users_info (no, name, address, contact_no) VALUES (4, 'Paul Singh', '1343 Prospect St', 000-444-7141)
  INTO users_info (no, name, address, contact_no) VALUES (5, 'Ken Myer', '137 Clay Road', 000-444-7084)
  INTO users_info (no, name, address, contact_no) VALUES (6, 'Jack Evans', '1365 Grove Way', 000-444-7957)
  INTO users_info (no, name, address, contact_no) VALUES (7, 'Reed Koch', '1274 West Street', 000-444-4784)
SELECT * FROM dual;

4 rows created.

SQL UPDATE Syntax

UPDATE table_name 
    SET column_name1 = value1, column_name2 = value2 , ...
    [ WHERE condition ]
    [ LIMIT number ];

Example: 

SQL> UPDATE users_info 
    SET name = "Beccaa Moss" , address ="2500 green city."
    WHERE no = 3;

SQL DELETE Syntax

DELETE FROM table_name 
    [ WHERE condition ]
    [ LIMIT number ];

Example: 

SQL> DELETE users_info 
    WHERE no = 3;

SQL SELECT Syntax

SELECT [ DISTINCT | ALL ] 
    column_name1, column_name2, aggregate_function(column_name) ....
    [ FROM table_name ]
    [ WHERE condition ]
    [ GROUP BY groupby_column_name1, .... ]
    [ HAVING having_clause ]
    [ ORDER BY order_column_name1 [ ASC | DESC ], .... ];

Example:

We have to fetch all table columns using asterisk (*), 

SQL> SELECT * FROM users_info WHERE no = 3;

SQL ALTER TABLE Syntax

ALTER TABLE table_name RENAME TO new_table_name;
ALTER TABLE table_name ADD column_name datatype[(size)];
ALTER TABLE table_name MODIFY column_name column_datatype[(size)];
ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;
ALTER TABLE table_name DROP COLUMN column_name;

Example:
Add new column to a 'users_info' table

SQL> ALTER TABLE users_info ADD postalcode VARCHAR2(8);

SQL DROP TABLE Syntax

DROP TABLE [ IF EXISTS ] 
    table_name1, table_name2, ...;

Example: 

SQL> DROP TABLE users_info ;

SQL COMMIT Syntax

SQL> COMMIT;

SQL ROLLBACK Syntax

SQL> ROLLBACK;

SQL SET LINESIZE Syntax

SQL Line Size command is use to set a number of character per row.

SET linesize N;

Example: 

SQL> SET linesize 300;