Category Archives: Learn SQL

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.

Day 24 – SQL EXISTS Operator

The SQL EXISTS operator is a logical operator that is used to test the existence of a subquery. It is typically used in conjunction with a correlated subquery to determine if any rows exist in a table based on a condition specified in the subquery.

The syntax for the EXISTS operator is as follows:

SELECT column_name(s)
FROM table_name
WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);

In this syntax, the main query retrieves data from a table, and the subquery checks for the existence of data in another table based on a specific condition. If the subquery returns any rows, the EXISTS operator returns true and the main query retrieves data as per the specified column names.

The EXISTS operator can be used with other SQL clauses, such as SELECT, UPDATE, DELETE, and INSERT. It is particularly useful when you want to filter results based on a condition that is not directly present in the table being queried. For example, you might use the EXISTS operator to find all customers who have placed orders in the last 30 days, even if their contact information is stored in a separate table.

Day 23 – SQL HAVING Clause

The SQL HAVING clause is used in conjunction with the GROUP BY clause to filter the results of a query based on the result of an aggregate function.

The syntax for the HAVING clause is as follows:

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING aggregate_function(condition)

In this syntax, column_name(s) refers to the column(s) that you want to retrieve data from, table_name refers to the table that you want to retrieve data from, condition is an optional parameter that can be used to filter the results, and aggregate_function is a function that operates on the grouped data and returns a single result.

When you use the HAVING clause, the result set is first grouped based on the columns specified in the GROUP BY clause. Then, the aggregate function specified in the HAVING clause is applied to the grouped data. If the result of the aggregate function satisfies the condition specified in the HAVING clause, then the group is included in the result set.

For example, suppose you have a table called orders with columns customer_name, order_date, and order_amount. You could use the following query to group the orders by customer and retrieve the total amount of orders for each customer that is greater than 1000:

SELECT customer_name, SUM(order_amount)
FROM orders
GROUP BY customer_name
HAVING SUM(order_amount) > 1000

This query would return a result set that shows the total order amount for each customer in the orders table, but only for those customers whose total order amount is greater than 1000.

Note that the HAVING clause is used to filter the results of a query after the data has been grouped, whereas the WHERE clause is used to filter the results of a query before the data has been grouped.