SQL Reference

Example

CREATE TABLE Employee (
    ID INT AUTO_INCREMENT,
    Name VARCHAR(255),
    DOB DATE,
    PRIMARY KEY (ID)
);

INSERT INTO Employee (Name, DOB)
VALUES ('John Doe', '1980-05-12');

Output

Query OK, 0 rows affected (0.03 sec)

Query OK, 1 row affected (0.01 sec)

Explanation

In the example above, a table “Employee” is created with the columns ID, Name, and DOB. The “ID” column is set as the primary key and is associated with AUTO_INCREMENT attribute. This assigns a unique identifier value (UID) to all new records in an ascending order starting from 1. When inserting a new row, the “ID” field is not required to be specified as it automatically increments.

Example

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name TEXT,
  department TEXT
);

INSERT INTO employees (id, name, department)
VALUES (1, 'John Doe', 'HR'),
       (2, 'Jane Doe', 'Finance'),
       (3, 'Max Smith', 'IT');

SELECT * FROM employees;

Output

 id |    name    | department
----+------------+------------
  1 | John Doe   | HR
  2 | Jane Doe   | Finance
  3 | Max Smith  | IT

Explanation

The code first creates a table ‘employees’ with three columns: id, name, and department. It then inserts three rows into the table. The SELECT statement retrieves all the rows from the table and displays them. The output shows the rows that were inserted with their respective ids, names, and departments.

Example

SELECT NEWID() as UID;

Output

|                 UID                  |
|--------------------------------------|
| 6F9619FF-8B86-D011-B42D-00C04FC964FF |

Explanation

In SQL Server, NEWID() function is used to generate a uniqueidentifier (UID) in the form of a Globally Unique Identifier (GUID). The example code selects a newly generated uniqueidentifier. The output showcases the result set with a single record, representing the newly generated UID.

Example

CREATE TABLE students (
    id NUMBER GENERATED AS IDENTITY,
    name VARCHAR2(100),
    age NUMBER
);

Output

Table STUDENTS created.

Explanation

In this example, an IDENTITY column is used to auto-generate a Unique ID (id) for each record in the students table. The id column will automatically be filled with a unique and incrementing number every time a new record is added to the table. Hence, the need for manually entering the id for each record is not required, reducing the possibility of error and data inconsistency.