Author Archives: Febronei

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.

Day 33 – SQL ALTER TABLE

SQL ALTER TABLE statement is used to modify the structure of an existing table in a database. The syntax for the ALTER TABLE statement is as follows:

ALTER TABLE table_name
ADD column_name datatype [NULL | NOT NULL] [DEFAULT default_value];

ALTER TABLE table_name
ALTER COLUMN column_name datatype [NULL | NOT NULL] [DEFAULT default_value];

ALTER TABLE table_name
DROP COLUMN column_name;

ALTER TABLE table_name
ALTER COLUMN column_name [NULL | NOT NULL];

ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_type (column1, column2, ...);

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

In this syntax, “table_name” is the name of the table that you want to modify, and the different options following the ALTER TABLE statement allow you to add, modify, or remove columns, change column data types or constraints.

Here’s an example of how to add a new column named “email” of data type VARCHAR(100) to an existing table named “employees”:

ALTER TABLE employees
ADD email VARCHAR(100) NOT NULL;

When this statement is executed, SQL Server will add a new column named “email” of data type VARCHAR(100) to the “employees” table.

You can also modify existing columns by changing their data type or nullability, as shown in the following example:

ALTER TABLE employees
ALTER COLUMN email VARCHAR(200) NULL;

This statement modifies the “email” column of the “employees” table, changing its data type to VARCHAR(200) and allowing null values.

You can also add or remove constraints on the columns of an existing table, as shown in the following example:

ALTER TABLE employees
ADD CONSTRAINT pk_employee PRIMARY KEY (id);

ALTER TABLE employees
DROP CONSTRAINT pk_employee;

This statement first adds a primary key constraint named “pk_employee” on the “id” column of the “employees” table, and then removes it.

Day 30 – SQL BACKUP DATABASE

SQL BACKUP DATABASE statement is used to create a backup of an existing database in a SQL Server instance. The backup can be used to restore the database in case of data loss or to migrate the database to another SQL Server instance. The syntax for the BACKUP DATABASE statement is as follows:

BACKUP DATABASE database_name
TO disk = 'backup_file_path'

In this syntax, “database_name” is the name of the database that you want to backup and “backup_file_path” is the file path and name of the backup file.

Here’s an example of how to backup a database named “my_database” to a backup file named “my_database_backup.bak” located in the “C:\SQLBackups” folder:

BACKUP DATABASE my_database
TO disk = 'C:\\SQLBackups\\my_database_backup.bak';

When this statement is executed, SQL Server will create a backup file named “my_database_backup.bak” in the “C:\SQLBackups” folder that contains the backup of the “my_database” database.

It’s important to note that backups should be taken regularly to ensure that the data is safe and can be restored in case of data loss. Additionally, ensure that you have the required permissions to backup a database.

Day 31 – SQL CREATE TABLE

SQL CREATE TABLE statement is used to create a new table in a database. The syntax for the CREATE TABLE statement is as follows:

CREATE TABLE table_name (
   column1 datatype [NULL | NOT NULL],
   column2 datatype [NULL | NOT NULL],
   ...
   columnN datatype [NULL | NOT NULL]
);

In this syntax, “table_name” is the name of the new table that you want to create, and “column1” through “columnN” are the names of the columns in the table.

Here’s an example of how to create a new table named “employees” with three columns: “id”, “name”, and “salary”:

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

When this statement is executed, SQL Server will create a new table named “employees” with three columns: “id” of data type INT, “name” of data type VARCHAR(50), and “salary” of data type DECIMAL(10, 2).

You can also specify additional constraints on the columns, such as PRIMARY KEY, UNIQUE, and FOREIGN KEY constraints. Here’s an example that shows some additional constraints:

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 FOREIGN KEY REFERENCES departments(id)
);

In this example, we add 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.

Day 29 – SQL DROP DATABASE

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

DROP DATABASE database_name;

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

Here’s an example of how to drop a database named “my_database”:

DROP DATABASE my_database;

When this statement is executed, SQL Server will delete the database named “my_database” and all its associated objects such as tables, views, stored procedures, and functions.

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

Day 28 – SQL CREATE DATABASE

SQL CREATE DATABASE statement is used to create a new database in a SQL Server instance. The syntax for the CREATE DATABASE statement is as follows:

CREATE DATABASE database_name;

In this syntax, “database_name” is the name of the new database that you want to create.

Here’s an example of how to create a new database named “my_database”:

CREATE DATABASE my_database;

When this statement is executed, SQL Server will create a new database named “my_database” in the default location configured for the SQL Server instance.

You can also specify additional options when creating a new database, such as specifying the location of the database files, the default collation, and other database-level settings. Here’s an example that shows some additional options:

CREATE DATABASE my_database
ON
( NAME = my_database_data, FILENAME = 'C:\\SQLData\\my_database_data.mdf'),
( NAME = my_database_log, FILENAME = 'C:\\SQLLogs\\my_database_log.ldf')
COLLATE SQL_Latin1_General_CP1_CI_AS;

In this example, we create a new database named “my_database” and specify the file locations for the primary data and log files. We also specify the collation for the database to be “SQL_Latin1_General_CP1_CI_AS”.

Day 27 – SQL Stored Procedures

SQL stored procedures are a type of program stored in a database that can be executed on demand. A stored procedure is a precompiled set of one or more SQL statements that are stored in the database and can be called repeatedly by client applications or other database objects.

Stored procedures can be used to simplify complex SQL operations, improve performance, and provide a layer of abstraction between client applications and the database. They can also be used to enforce security, encapsulate business rules, and provide a standard interface for interacting with the database.

Here’s an example of how to create a stored procedure in SQL:

CREATE PROCEDURE get_customer_orders
    @customer_id int
AS
BEGIN
    SELECT order_id, order_date, order_amount
    FROM orders
    WHERE customer_id = @customer_id
    ORDER BY order_date DESC;
END

In this example, we create a stored procedure named “get_customer_orders” that accepts a parameter named “@customer_id” and returns the order details for that customer. When executed, the stored procedure will run the SQL SELECT statement and return the results.

Once the stored procedure is created, it can be executed by calling its name and passing in the required parameter:

EXEC get_customer_orders @customer_id = 12345;

In this example, we execute the “get_customer_orders” stored procedure and pass in the value “12345” for the “@customer_id” parameter. The stored procedure will then return the order details for the specified customer.

Day 26 – SQL CASE Expression

SQL CASE expression is a conditional expression used to perform a variety of operations in SQL queries, including conditional logic, data transformations, and aggregations. It is similar to the IF-THEN-ELSE construct found in many programming languages.

The syntax for the SQL CASE expression is as follows:

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    WHEN valueN THEN resultN
    ELSE default_result
END

In this syntax, the expression is evaluated and compared against each value in the WHEN clause. If a match is found, the corresponding result is returned. If no matches are found, the default_result is returned.

Here’s an example of how the SQL CASE expression can be used in a query:

Suppose you have a table named “orders” that contains information about customer orders, including order ID, customer ID, and order amount. You want to create a new column in the table called “order_status” that indicates whether each order is above or below a certain threshold amount.

SELECT order_id, customer_id, order_amount,
    CASE
        WHEN order_amount > 1000 THEN 'Above Threshold'
        ELSE 'Below Threshold'
    END AS order_status
FROM orders;

In this example, the CASE expression is used to create a new column named “order_status” that indicates whether each order is above or below a threshold of $1000. If an order has an order amount greater than 1000, the “Above Threshold” string is returned. Otherwise, the “Below Threshold” string is returned. The resulting query output will contain the original columns from the “orders” table, as well as the new “order_status” column indicating whether each order is above or below the threshold.

Day 25 – SQL SELECT INTO Statement

The SQL SELECT INTO statement is used to create a new table and populate it with data from an existing table. The new table is created based on the columns in the SELECT statement and the data is inserted into the new table from the source table.

The syntax for the SELECT INTO statement is as follows:

SELECT column1, column2, column3, ...
INTO new_table
FROM source_table
WHERE condition;

In this syntax, the SELECT statement specifies the columns to be copied from the source_table into the new_table. The INTO clause specifies the name of the new table that will be created. The WHERE clause is optional and is used to filter the data that will be copied to the new table based on a specific condition.

It is important to note that the SELECT INTO statement creates a new table with the specified columns and data, and therefore, it should only be used if you want to create a new table with a specific set of columns and data. If you want to copy data from one table to another existing table, you should use the INSERT INTO statement instead.

Additionally, it is important to make sure that the new table does not already exist in the database, or else the SELECT INTO statement will fail.

Suppose you have a table named “orders” that contains information about customer orders, including order ID, customer ID, order date, and order amount. You want to create a new table named “recent_orders” that contains only the orders placed in the last 30 days.

SELECT order_id, customer_id, order_date, order_amount
INTO recent_orders
FROM orders
WHERE order_date >= DATEADD(day, -30, GETDATE());

In this example, the SELECT INTO statement creates a new table named “recent_orders” and copies the columns “order_id,” “customer_id,” “order_date,” and “order_amount” from the “orders” table. The WHERE clause filters the data to only include orders placed within the last 30 days. The resulting “recent_orders” table will contain only the recent orders data from the “orders” table.