AUTO INCREMENT a Field
Auto-increment allows a unique number to be generated when a new record is inserted into a
table.
By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each
new record. To insert a new record into the created table, you will not have to specify a value for
the primary key column (a unique value will be added automatically.
To let the AUTO_INCREMENT sequence start with another value, use the following SQL
statement.
ALTER TABLE student AUTO_INCREMENT=100
To insert more than one record at once, we can do this, with each set of field values
separated by a comma:
INSERT INTO example
(example_id, name, value, other_value)
VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
(102, 'Name 3', 'Value 3', 'Other 3'),
(103, 'Name 4', 'Value 4', 'Other 4');
©Juliet 2023 Page 1
SQL WHERE Clause
The WHERE clause is used to extract only those records that fulfill a specified criterion.
The syntax for SQL WHERE
SELECT colum_name(s) FROM table_name WHERE column_name operator value
Operators Allowed in the WHERE Clause
With the WHERE clause, the following operators can be used:-
Operator Description
= Equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN If you know the exact value you want to return for at least one of the columns
SQL AND & OR Operators
In SQL, the AND & OR operators are used to filter records based on more than one condition.
The AND operator
The AND operator displays a record if both the first condition and the second condition is true.
The OR operator displays a record if either the first condition or the second condition is true.
©Juliet 2023 Page 2
Example:
SELECT * from student WHERE firstname=”Janet” AND Lastname=”Njoroge
SQL ORDER BY Keyword
In SQL, The ORDER BY keyword is used to sort the result-set by a specified column.
The ORDER BY keyword sorts the records in ascending order by default.
If you want to sort the records in a descending order, you can use the DESC keyword.
SQL ORDER BY Syntax
Command syntax for sorting records in ascending sequence
SELECT column_Names(s) FROM tableName ORDER BY column_name(s) ASC ;
Command syntax for sorting records in descending sequence
SELECT column_Names(s) FROM tableName ORDER BY column_name(s) DESC ;
SQL OR Operator
To select only the persons with the first name equal to "Dave" OR the first name equal to "John"
Use the following SELECT statement:
SELECT * FROM Student WHERE firstname=”Dave” OR firstname=”John”
©Juliet 2023 Page 3
SQL FOREIGN KEY Constraint
What is a foreign key?
A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY
KEY in another table.
The FOREIGN KEY constraint is used to prevent actions that would destroy links between
tables. The table with the foreign key is called the child table, and the table with the primary key
is called the referenced or parent table.
Example:
Student Table
StudentID Surname Age Gender
100 Njoroge 34 Male
200 Kamau 27 male
300 Otieno 29 Male
400 Akinyi 36 Female
Course table
CourseCode CourseName Duration StudentID
120 BISF 34 months 100
130 BSD 17 months 200
260 BISF 10 months 300
450 DIT 6 months 400
Explanation:
1. The "StudentID" column in the Course table points to the "studentID" column in the
student table.
©Juliet 2023 Page 4
2. The "studentID" column in the "student" table is the PRIMARY KEY in the "students"
table.
3. The "studentID" column in the "courses" table is a FOREIGN KEY in the "course" table.
Note: - The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign
key column, because it has to be one of the values contained in the parent table.
SQL FOREIGN KEY on CREATE TABLE
The following SQL creates a FOREIGN KEY on the "studentID" column when the "course"
table is created:
SQl command:
CREATE TABLE course (courseCode int NOT NULL, courseName text NOT NULL,
studentID int,
PRIMARY KEY (courseCode), FOREIGN KEY (studentID) REFERENCES
student(studentID) );
©Juliet 2023 Page 5
