Category Archives: Learn SQL

Day 42 – AUTO INCREMENT Field

In SQL, an AUTO INCREMENT field is a column that automatically generates a unique numeric value when a new row is inserted into a table. The AUTO INCREMENT field is often used as a primary key because it guarantees the uniqueness of each row in the table.

The syntax for defining an AUTO INCREMENT field varies depending on the database system being used. In MySQL, for example, the AUTO INCREMENT field is defined as follows:

CREATE TABLE table_name (
   id INT AUTO_INCREMENT PRIMARY KEY,
   column1 datatype,
   column2 datatype,
   .....
);

In the above syntax, id is the name of the AUTO INCREMENT field, INT is the data type of the field, AUTO_INCREMENT is the keyword that tells MySQL to generate a new value for the field automatically, and PRIMARY KEY specifies that the field is the primary key for the table.

When a new row is inserted into the table, the AUTO INCREMENT field is automatically assigned the next available integer value. Each subsequent row inserted into the table will have a unique value for the AUTO INCREMENT field.

For example, let’s say we have a table named “employees” with columns “id”, “name”, and “salary”. We want to define an AUTO INCREMENT field for the “id” column. The CREATE TABLE statement for this table would be as follows:

CREATE TABLE employees (
   id INT AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(50),
   salary DECIMAL(10,2)
);

In the above example, we have defined an AUTO INCREMENT field for the “id” column of the “employees” table. When a new row is inserted into the table, the “id” field will be automatically assigned the next available integer value.

It is important to note that not all database systems support the AUTO INCREMENT field syntax. In Microsoft SQL Server, for example, the equivalent functionality is provided by the IDENTITY property, and in Oracle, it is provided by the SEQUENCE object.

Day 41 – SQL CREATE INDEX Statement

In SQL, an index is a database object that improves the performance of SQL queries by allowing them to retrieve data more efficiently. An index is created on one or more columns of a table to help the database engine quickly find the rows that match a search condition. The CREATE INDEX statement is used to create an index on one or more columns of a table.

The syntax for creating an index on a table is as follows:

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

In the above syntax, index_name is the name of the index, table_name is the name of the table on which the index is being created, column1, column2, etc. are the columns on which the index is being created.

If you specify UNIQUE keyword in the CREATE INDEX statement, the index will enforce uniqueness on the specified columns, i.e., it will not allow duplicate values in the indexed columns.

For example, let’s say we have a table named “employees” with columns “id”, “first_name”, “last_name”, and “department”. We want to create an index on the “last_name” column to improve the performance of queries that search for employees by last name. The CREATE INDEX statement for this index would be as follows:

CREATE INDEX idx_last_name
ON employees (last_name);

In the above example, we have created an index named “idx_last_name” on the “last_name” column of the “employees” table.

It is important to note that creating an index may improve the performance of queries that search for data in the indexed column(s), but it may also slow down the performance of queries that update or insert data into the table. Therefore, it is recommended to create indexes on columns that are frequently used in WHERE clauses of SELECT statements and not on columns that are frequently updated.

Day 40 – SQL DEFAULT Constraint

In SQL, a DEFAULT constraint is used to set a default value for a column when a new row is inserted into the table and the value for that column is not provided. The DEFAULT constraint is defined at the time of creating the table or added later using the ALTER TABLE statement.

The syntax for defining a DEFAULT constraint when creating a table is as follows:

CREATE TABLE table_name (
   column1 datatype DEFAULT default_value,
   column2 datatype,
   column3 datatype,
   .....
);

In the above syntax, column1 is the name of the column on which the DEFAULT constraint is defined, datatype is the data type of the column, and default_value is the value that will be assigned to the column if no value is specified during insertion.

The syntax for adding a DEFAULT constraint to an existing table is as follows:

ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT default_value;

In the above syntax, column_name is the name of the column on which the DEFAULT constraint is being added, and default_value is the value that will be assigned to the column if no value is specified during insertion.

For example, let’s say we have a table named “employees” with a column “hire_date”, and we want to set the default value of hire date to the current date when a new row is inserted into the table. We can define a DEFAULT constraint on the hire_date column as follows:

CREATE TABLE employees (
   id INT PRIMARY KEY,
   name VARCHAR(50),
   hire_date DATE DEFAULT CURRENT_DATE,
   salary DECIMAL(10,2)
);

In the above example, we have defined a DEFAULT constraint on the hire_date column to set the default value to the current date if no value is provided during insertion.

When a DEFAULT constraint is defined, if no value is specified during insertion, the default value will be assigned to the column. If a value is provided during insertion, the specified value will be assigned to the column instead of the default value.

Day 39 – SQL CHECK Constraint

In SQL, a CHECK constraint is used to ensure that the values in a column meet a specified condition or set of conditions. The CHECK constraint is defined at the time of creating the table or added later using the ALTER TABLE statement.

The syntax for defining a CHECK constraint when creating a table is as follows:

CREATE TABLE table_name (
   column1 datatype CHECK (condition),
   column2 datatype,
   column3 datatype,
   .....
);

In the above syntax, column1 is the name of the column on which the CHECK constraint is defined, datatype is the data type of the column, and condition is the expression that must be evaluated to TRUE for the value to be inserted or updated in the column.

The syntax for adding a CHECK constraint to an existing table is as follows:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CHECK (condition);

In the above syntax, constraint_name is the name of the CHECK constraint, and condition is the expression that must be evaluated to TRUE for the value to be inserted or updated in the column.

For example, let’s say we have a table named “employees” with a column “age”, and we want to ensure that the age of an employee is not less than 18 and not greater than 60. We can define a CHECK constraint on the age column as follows:

CREATE TABLE employees (
   id INT PRIMARY KEY,
   name VARCHAR(50),
   age INT CHECK (age >= 18 AND age <= 60),
   salary DECIMAL(10,2)
);

In the above example, we have defined a CHECK constraint on the age column to ensure that the age is between 18 and 60 (inclusive).

When a CHECK constraint is violated, the database will not allow the insertion or update of data in the column that violates the constraint.

Day 38 – SQL FOREIGN KEY Constraint

In SQL, a FOREIGN KEY constraint is used to link two tables together based on a relationship between a column(s) in one table and the primary key column(s) in another table. The FOREIGN KEY constraint ensures that the values in the referencing column(s) in the child table exist in the referenced column(s) of the parent table or are null.

The syntax for defining a FOREIGN KEY constraint when creating a table is as follows:

CREATE TABLE table_name1 (
   column1 datatype PRIMARY KEY,
   column2 datatype,
   column3 datatype,
   .....
   FOREIGN KEY (column2) REFERENCES table_name2(column1)
);

In the above syntax, table_name1 is the child table, column2 is the referencing column in the child table, table_name2 is the parent table, and column1 is the referenced column in the parent table.

The syntax for adding a FOREIGN KEY constraint to an existing table is as follows:

ALTER TABLE table_name1
ADD CONSTRAINT fk_name FOREIGN KEY (column2) REFERENCES table_name2(column1);

In the above syntax, fk_name is the name of the FOREIGN KEY constraint.

It is important to maintain data integrity while creating FOREIGN KEY constraints. The referenced column(s) in the parent table must be defined as a primary key or a unique constraint. Also, the datatype and size of the referencing column(s) in the child table must match the datatype and size of the referenced column(s) in the parent table. If the FOREIGN KEY constraint is violated, the database will not allow the insertion, update, or deletion of data in the child table.

Day 37 – SQL PRIMARY KEY Constraint

In SQL, the PRIMARY KEY constraint is used to specify a column or a set of columns that uniquely identify each row in a table. The primary key constraint ensures that the values in the specified column(s) are unique and cannot be null.

A table can have only one primary key, and it can be defined at the time of creating the table or added later using the ALTER TABLE statement. When a primary key is defined on a column, the database automatically creates an index on that column for faster searching and sorting of data.

The syntax for defining a primary key constraint when creating a table is as follows:

CREATE TABLE table_name (
   column1 datatype PRIMARY KEY,
   column2 datatype,
   column3 datatype,
   .....
);

The syntax for adding a primary key constraint to an existing table is as follows:

ALTER TABLE table_name
ADD PRIMARY KEY (column1, column2, ...);

In the above syntax, column1, column2, etc. are the names of the columns that you want to include in the primary key.

It is important to choose the right column(s) to define as the primary key because the primary key will be used as a reference by other tables, and changing the primary key can have significant impacts on the database’s performance and data integrity.

Day 36 – SQL UNIQUE constraint

SQL UNIQUE constraint is used to ensure that each value in a column or a combination of columns is unique across all rows in a table. When you define a column with the UNIQUE constraint, it means that the values in that column must be unique for every row in the table.

Here’s an example of how to create a table with a UNIQUE constraint on a column:

CREATE TABLE students (
   id INT NOT NULL,
   name VARCHAR(50) NOT NULL,
   email VARCHAR(100) UNIQUE,
   age INT
);

In this example, the “email” column has the UNIQUE constraint, which means that every value in this column must be unique across all rows in the “students” table. The “id”, “name”, and “age” columns do not have a UNIQUE constraint and can contain duplicate values.

If you try to insert a row into the “students” table with a value that already exists in the “email” column, SQL Server will return an error. For example, the following statement would fail:

INSERT INTO students (id, name, email, age)
VALUES (1, 'John', 'john@example.com', 25);

This is because the value “john@example.com” already exists in the “email” column, and it has a UNIQUE constraint. To avoid this error, you need to provide a unique value for the “email” column in the INSERT statement or update the existing row with a different value.

Day 35 – SQL NOT NULL Constraint

SQL NOT NULL constraint is used to ensure that a column in a table cannot have a NULL value. When you define a column with the NOT NULL constraint, it means that the column must have a value for every row in the table.

Here’s an example of how to create a table with a NOT NULL constraint on a column:

CREATE TABLE employees (
   id INT NOT NULL,
   name VARCHAR(50) NOT NULL,
   age INT,
   salary DECIMAL(10, 2) NOT NULL
);

In this example, the “id”, “name”, and “salary” columns have the NOT NULL constraint. This means that a value must be provided for these columns for every row inserted into the “employees” table. The “age” column does not have a NOT NULL constraint, which means that it can have a NULL value.

If you try to insert a row into the “employees” table without providing a value for a column with the NOT NULL constraint, SQL Server will return an error. For example, the following statement would fail:

INSERT INTO employees (id, name, salary)
VALUES (1, 'John', 5000.00);

This is because the “age” column does not have a value specified, and it has a NOT NULL constraint. To avoid this error, you need to provide a value for the “age” column in the INSERT statement or remove the NOT NULL constraint on the “age” column.

Day 34 – SQL Constraints

In SQL, constraints are used to specify rules and restrictions on the data that can be stored in a table. They ensure data integrity and consistency by preventing invalid data from being entered into the table.

There are several types of constraints that can be defined on a table, including:

1. NOT NULL constraint: This constraint ensures that a column cannot have a NULL value.
2. PRIMARY KEY constraint: This constraint ensures that a column or a combination of columns uniquely identifies each row in the table.
3. UNIQUE constraint: This constraint ensures that each value in a column or a combination of columns is unique across all rows in the table.
4. FOREIGN KEY constraint: This constraint ensures that the values in a column or a combination of columns in one table correspond to the values in a primary key column or a unique key column in another table.
5. CHECK constraint: This constraint ensures that the values in a column satisfy a specified condition.

Here’s an example of how to define constraints on a table:

CREATE TABLE employees (
   id INT NOT NULL PRIMARY KEY,
   name VARCHAR(50) NOT NULL,
   salary DECIMAL(10, 2) NOT NULL,
   department_id INT NOT NULL,
   CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(id),
   CONSTRAINT ck_salary CHECK (salary > 0)
);

In this example, we define a primary key constraint on the “id” column and a foreign key constraint on the “department_id” column that references the “id” column in the “departments” table. We also define a check constraint on the “salary” column to ensure that it is greater than 0.

Constraints can also be added or removed using the ALTER TABLE statement. For example, to add a unique constraint on the “name” column of the “employees” table, you can use the following statement:

ALTER TABLE employees
ADD CONSTRAINT uk_name UNIQUE (name);

And to remove the unique constraint on the “name” column, you can use the following statement:

ALTER TABLE employees
DROP CONSTRAINT uk_name;

Constraints play an important role in maintaining data integrity and ensuring the accuracy and reliability of the data stored in a table.

Day 32 – SQL DROP TABLE

SQL DROP TABLE statement is used to delete an existing table in a database. The syntax for the DROP TABLE statement is as follows:

DROP TABLE table_name;

In this syntax, “table_name” is the name of the table that you want to delete.

Here’s an example of how to drop a table named “employees”:

DROP TABLE employees;

When this statement is executed, SQL Server will delete the “employees” table and all its associated data and objects such as indexes, constraints, and triggers.

It’s important to note that dropping a table is a permanent action and cannot be undone. Therefore, it’s recommended to take a backup of the table data before dropping it. Additionally, ensure that you have the required permissions to drop a table.