Introduction to SQL — CREATE, INSERT and SELECT

SQL (Structured Query Language) is the standard language used to create and work with data in a relational database. Instead of writing a long program, you write short SQL statements that tell the DBMS what you want, and the DBMS does it. Three of the most basic and important statements are CREATE TABLE, INSERT and SELECT. (By convention SQL keywords are written in capital letters, and every statement ends with a semicolon.)

1. Creating a table — CREATE TABLE: this statement defines a new table by listing its fields and the data type of each. A common numeric type is INT (whole numbers) and a common text type is VARCHAR(n) (text up to n characters). We can also mark a field as the PRIMARY KEY.

CREATE TABLE Student (
    RollNo INT PRIMARY KEY,
    Name VARCHAR(50),
    Class VARCHAR(10),
    Marks INT
);

This creates an empty Student table with four fields, where RollNo is the primary key.

2. Adding data — INSERT INTO: this statement adds a new record (row) into a table. You list the values in the same order as the fields.

INSERT INTO Student (RollNo, Name, Class, Marks)
VALUES (1, 'Aarti Deshpande', 'XI-A', 78);

INSERT INTO Student (RollNo, Name, Class, Marks)
VALUES (2, 'Rohan Patil', 'XI-A', 85);

Text values are written inside single quotes; numbers are written without quotes. After these two statements, the Student table has two records.

3. Retrieving data — SELECT: this is the statement used to read data back from a table. To see every column of every row, use the * symbol, which means 'all columns':

SELECT * FROM Student;

To see only some columns, name them:

SELECT Name, Marks FROM Student;

To see only the rows that meet a condition, add a WHERE clause:

SELECT Name, Marks FROM Student
WHERE Class = 'XI-A';
``` …