module 6

SQL constraints are used to specify rules for the data in a table.
Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.
Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table

Constraints (common)

The following are the most common constraints used in MySQL:

NOT NULL
CHECK
DEFAULT
PRIMARY KEY
AUTO_INCREMENT
UNIQUE
INDEX
ENUM
FOREIGN KEY

Not Null Constraints

By default, a column can hold NULL values.
This constraint specifies that the column cannot have NULL or empty values. The below statement creates a table with NOT NULL constraint.
- - NOT NULL on CREATE TABLE

Unique Constraints

The UNIQUE constraint ensures that all values in a column are different.
Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE constraint.
However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.
UNIQUE CONSTRAINT ON CREATE TABLE

Primary Key Constraints

The PRIMARY KEY constraint uniquely identifies each record in a table.
Primary keys must contain UNIQUE values, and cannot contain NULL values.
A table can have only ONE primary key; and in the table, this primary key can consist of single or multiple columns (fields).

PRIMARY KEY of CREATE TABLE:

CREATE TABLE Persons (ID int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int,

PRIMARY KEY (ID));

Primary Key Constraints (drop)

PRIMARY KEY on ALTER TABLE : To create a PRIMARY KEY constraint on the "ID" column when the table is already created ;

DROP A PRIMARY KEY CONSTRAINT

To drop a PRIMARY KEY constraint, use the following SQL:

- ALTER TABLE table_name DROP PRIMARY KEY

Foreign Key Constraints

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.
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.

Check Constraints

The following SQL creates a CHECK constraint on the "Age" column when the "Persons" table is created. The CHECK constraint ensures that the age of a person must be 18, or older:

- ALTER TABLE persons ADD CHECK (Age>=18)

Check Constraints (more)

CHECK ON ALTER TABLE

To create a CHECK constraint on the "Age" column when the table is already created, use the following SQL:

ALTER TABLE Persons
ADD CHECK (Age>=18);

Default Constraints

The DEFAULT constraint is used to set a default value for a column.

The default value will be added to all new records, if no other value is specified

DEFAULT ON CREATE TABLE:

The following SQL sets a DEFAULT value for the "City" column when the "Persons" table is created:

CREATE TABLE Persons (ID int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Age int,

City varchar(255) DEFAULT 'Sandnes');

Create Index Statement

The CREATE INDEX statement is used to create indexes in tables.

Indexes are used to retrieve data from the database more quickly than otherwise. The users cannot see the indexes, they are just used to speed up searches/queries.

CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name
ON table_name (column1, column2, ...);

Create Unique Index Statement

CREATE UNIQUE INDEX Syntax

Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

Drop Index Statement

The DROP INDEX statement is used to delete an index in a table.

ALTER TABLE table_name
DROP INDEX index_name;

Insert Statement

MySQL INSERT statement is used to store or add data in MySQL table within the database.

We can perform insertion of records in two ways using a single query in MySQL:

Insert record in a single row
Insert record in multiple rows

Insert Statement (single row)

INSERT RECORD IN A SINGLE ROW

Syntax:

INSERT INTO table_name ( field1, field2,...fieldN )
VALUES( value1, value2,...valueN );

If we want to store records without giving all fields, we use the following partial field statements. In such case, it is mandatory to specify field names.

Insert Statement (syntax)

INSERT RECORD IN A SINGLE ROW

Syntax:

INSERT INTO table_name ( field1, field2,...fieldN )
VALUES( value1, value2,...valueN );

The Select Statement

The SELECT statement in MySQL is used to fetch data from one or more tables. We can retrieve records of all fields or specified fields that match specified criteria using this statement.

SELECT STATEMENT SYNTAX

SELECT field_name1, field_name 2,... field_nameN
FROM table_name1, table_name2...
[WHERE condition]
[GROUP BY field_name(s)]
[HAVING condition]
[ORDER BY field_name(s)]
[OFFSET M ][LIMIT N];

The Select Statement (all)

If we want to fetch data from all columns of the table, we need to use all column's names with the select statement. Specifying all column names is not convenient to the user, so MySQL uses an asterisk (*) to retrieve all column data as follows:

SELECT * FROM table_name;

The Where Clause

MySQL WHERE Clause is used with SELECT, INSERT, UPDATE and DELETE clause to filter the results. It specifies a specific position where you have to do the operation.

Syntax:

WHERE conditions;

Parameter:

conditions: It specifies the conditions that must be fulfilled for records to be selected.

The Where Clause (example)

Code Example:

SELECT *
FROM EMPLOYEE
WHERE NAME = ’PETER';

The Where Clause with AND Condition

In this example, we are retrieving data from the table "officers" with AND condition.

SELECT *
FROM officers
WHERE NAME = ’PETER'
AND WORKING_HOURS>10;

The Where Clause with OR Condition

SELECT *
FROM officers
WHERE WORKING_DATE =“2020-10-04”
OR WORKING_HOURS >=10;

The Where Clause with AND or OR Condition

SELECT *
FROM officers
WHERE (OCCUPATION = ’ACTOR' AND WORKING_DATE = ’2020-10-04')
OR (WORKING_HOURS>= 13);

The Order by Clause

The MYSQL ORDER BY Clause is used to sort the records in ascending or descending order.

Syntax:

SELECT expressions
FROM tables
[WHERE conditions]
ORDER BY expression [ ASC | DESC ];

The Order by Clause (Parameters)

Parameters

expressions: It specifies the columns that you want to retrieve.
tables: It specifies the tables, from where you want to retrieve records. There must be at least one table listed in the FROM clause.
WHERE conditions: It is optional. It specifies conditions that must be fulfilled for the records to be selected.
ASC: It is optional. It sorts the result set in ascending order by expression (default, if no modifier is provider).
DESC: It is also optional. It sorts the result set in descending order by expression.

The Order by Clause (sybtax)

If you use MySQL ORDER BY clause without specifying the ASC and DESC modifier then by default you will get the result in ascending order.

syntax

- SELECT *
- FROM officers
- WHERE address = 'Lucknow'
- ORDER BY officer_name;

The Order by Clause (example)

Code Example:

Example to retrieve the data in ascending order.

SELECT *
FROM EMPLOYEE
WHERE WORKING_HOURS >=9
ORDER BY NAME ASC;

The Order by Clause (desc)

Code Example:

Example to retrieve the data in descending order.

SELECT *
FROM EMPLOYEE
WHERE WORKING_DATE= ’2020-10-04'
ORDER BY NAME DESC;

The Order by Clause (asc + desc)

Using both ASC and DESC

Code Example:

SELECT NAME,OCCUPATION
FROM EMPLOYEE
WHERE WORKING_HOURS >10
ORDER BY NAME DESC, OCCUPATION ASC;