Author Archives: Febronei

Day 73 – SQL FOREIGN KEY Keyword

In SQL, a FOREIGN KEY is a constraint that is used to enforce referential integrity between two tables. The FOREIGN KEY constraint is used to link two tables together based on the value of a column or set of columns in each table. The purpose of this constraint is to ensure that data in one table corresponds to data in another table.

The basic syntax for creating a FOREIGN KEY constraint is as follows:

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

  CONSTRAINT fk_constraint_name
    FOREIGN KEY (column_name)
    REFERENCES parent_table (parent_column_name)
);

Here, table_name is the name of the child table, column1, column2, etc. are the columns in the child table, fk_constraint_name is the name of the FOREIGN KEY constraint, column_name is the name of the column in the child table that references the parent table, parent_table is the name of the parent table, and parent_column_name is the name of the column in the parent table that is referenced by the child table.

For example, to create a FOREIGN KEY constraint that links the “Orders” table to the “Customers” table based on the “CustomerID” column, the SQL statement would be:

CREATE TABLE Orders
(
  OrderID int NOT NULL,
  CustomerID int NOT NULL,
  OrderDate date,

  CONSTRAINT FK_CustomersOrders
    FOREIGN KEY (CustomerID)
    REFERENCES Customers(CustomerID)
);

In this example, the FOREIGN KEY constraint is named “FK_CustomersOrders” and links the “Orders” table to the “Customers” table based on the “CustomerID” column. The constraint ensures that any value entered into the “CustomerID” column in the “Orders” table must correspond to a value in the “CustomerID” column of the “Customers” table.

Day 72 – SQL EXISTS Keyword

SQL EXISTS keyword is used to check for the existence of a record or set of records in a subquery. The EXISTS operator returns a Boolean value (TRUE or FALSE) indicating whether or not the subquery returns any rows.

The basic syntax for using the EXISTS keyword is as follows:

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

Here, column_name(s) are the columns that you want to retrieve from the table, table_name is the name of the table that you want to query, and subquery is a SELECT statement that returns a set of records to be evaluated.

For example, to check if any orders have been placed by a customer with a customer ID of 1234, the SQL statement would be:

SELECT *
FROM Orders
WHERE EXISTS (
  SELECT *
  FROM Customers
  WHERE Customers.CustomerID = Orders.CustomerID
  AND Customers.CustomerID = 1234
);

In this example, the subquery retrieves all customers with a customer ID of 1234, and the outer query retrieves all orders where the customer ID matches the results of the subquery. If any orders exist for the specified customer ID, the EXISTS operator returns TRUE.

It’s important to note that the EXISTS operator does not return any actual data from the subquery, only a Boolean value indicating whether or not the subquery returns any rows. Therefore, the EXISTS operator is often used in combination with other operators, such as NOT, to check for the absence of a record or set of records.

Day 71 – SQL EXEC Keyword

SQL EXEC keyword is used to execute a stored procedure or a dynamically constructed SQL statement within a stored procedure in SQL Server. The basic syntax for using the EXEC keyword is as follows:

EXEC procedure_name

Here, procedure_name is the name of the stored procedure that you want to execute. The EXEC keyword can also be used to execute a dynamically constructed SQL statement within a stored procedure. In this case, the SQL statement is passed as a string to the EXEC keyword, and the string is executed as a separate batch.

For example, to execute a stored procedure named “GetCustomerByID” that takes a customer ID as a parameter, the SQL statement would be:

EXEC GetCustomerByID 1234;

This would execute the “GetCustomerByID” stored procedure with a customer ID of 1234.

Alternatively, to execute a dynamically constructed SQL statement within a stored procedure, the SQL statement would be constructed as a string and passed as a parameter to the EXEC keyword, as follows:

DECLARE @sql VARCHAR(MAX);
SET @sql = 'SELECT * FROM Customers WHERE CustomerID = 1234';
EXEC (@sql);

This would execute a SELECT statement that retrieves all columns from the “Customers” table where the customer ID is equal to 1234. The SQL statement is constructed as a string and passed as a parameter to the EXEC keyword, which executes the statement as a separate batch.

Day 70 – SQL DROP TABLE and TRUNCATE TABLE Keywords

SQL DROP TABLE and TRUNCATE TABLE keywords are used to remove all data and the table structure from a table in a database. Although both commands have similar functionality, they differ in how they accomplish the task.

The DROP TABLE Keyword:

The DROP TABLE keyword is used to remove an entire table from a database, including all data, indexes, and constraints associated with the table. The basic syntax for using the DROP TABLE keyword is as follows:

DROP TABLE table_name;

Here, table_name is the name of the table that you want to drop.

For example, to drop a table named “Customers”, the SQL statement would be:

DROP TABLE Customers;

This would remove the entire “Customers” table from the database.

It’s important to note that the DROP TABLE command is a permanent action and cannot be undone. Therefore, it’s recommended to use the DROP TABLE command with caution and to always make a backup of the table or the database before executing a DROP TABLE statement.

The TRUNCATE TABLE Keyword:

The TRUNCATE TABLE keyword is used to remove all data from a table, but it preserves the table structure, indexes, and constraints. The basic syntax for using the TRUNCATE TABLE keyword is as follows:

TRUNCATE TABLE table_name;

Here, table_name is the name of the table that you want to truncate.

For example, to truncate a table named “Customers”, the SQL statement would be:

TRUNCATE TABLE Customers;

This would remove all data from the “Customers” table but would preserve the table structure, indexes, and constraints.

It’s important to note that the TRUNCATE TABLE command is also a permanent action and cannot be undone. Therefore, it’s recommended to use the TRUNCATE TABLE command with caution and to always make a backup of the table or the database before executing a TRUNCATE TABLE statement.

Day 69 – SQL SELECT DISTINCT Keyword

SQL SELECT DISTINCT keyword is used to retrieve unique or distinct values from a table. The SELECT DISTINCT statement is useful when you want to eliminate duplicate rows from the result set returned by a SELECT statement.

The basic syntax for using the SELECT DISTINCT keyword is as follows:

SELECT DISTINCT column1, column2, ...
FROM table_name
WHERE condition;

Here, column1, column2, … are the names of the columns whose unique values will be retrieved, and table_name is the name of the table from which the values will be retrieved. The WHERE clause is optional and can be used to filter the rows that will be included in the result set.

For example, to retrieve the unique values from a column named “City” in a table named “Customers”, the SQL statement would be:

SELECT DISTINCT City
FROM Customers;

This would retrieve all unique values from the “City” column in the “Customers” table.

Alternatively, to retrieve the unique values from two columns named “City” and “Country” in a table named “Customers”, the SQL statement would be:

SELECT DISTINCT City, Country
FROM Customers
WHERE Country = 'USA';

This would retrieve all unique combinations of values from the “City” and “Country” columns in the “Customers” table, but only for customers who live in the United States.

It’s important to note that the SELECT DISTINCT statement may be slower than a regular SELECT statement because it requires the database to compare and eliminate duplicate rows. Therefore, it’s recommended to use the SELECT DISTINCT statement only when necessary and to use appropriate indexing and query optimization techniques to improve performance.

Day 68 – SQL DESC Keyword

SQL DESC keyword is short for “DESCRIBE”, and it is used to display the structure or schema of a table in a database. The DESC keyword can be used to retrieve information about the columns in a table, including their names, data types, and any constraints that are defined on them.

The basic syntax for using the DESC keyword is as follows:

DESC table_name;

Here, table_name is the name of the table whose structure will be described.

For example, to describe the structure of a table named “Customers”, the SQL statement would be:

DESC Customers;

This would display information about the columns in the “Customers” table, including their names, data types, and any constraints that are defined on them.

The output of the DESC command will vary depending on the database management system (DBMS) being used. Some DBMSs may display additional information, such as the default values or indexes that are defined on the columns.

It’s important to note that the DESC command is not part of the SQL standard, and it may not be supported by all database management systems. In some cases, the equivalent command may be “SHOW COLUMNS FROM table_name” or “SHOW FIELDS FROM table_name”.

Day 67 – SQL DELETE Keyword

SQL DELETE keyword is used to delete one or more rows from a table. The DELETE statement can be used to remove either all rows or a subset of rows based on certain criteria.

The basic syntax for using the DELETE keyword is as follows:

DELETE FROM table_name
WHERE condition;

Here, table_name is the name of the table from which rows will be deleted, and condition is the expression that specifies which rows to delete. If the WHERE clause is omitted, then all rows in the table will be deleted.

For example, to delete all rows from a table named “Customers”, the SQL statement would be:

DELETE FROM Customers;

This would delete all rows from the “Customers” table.

Alternatively, to delete only the rows from the “Customers” table where the “City” column is set to “Paris”, the SQL statement would be:

DELETE FROM Customers
WHERE City = 'Paris';

This would delete only the rows from the “Customers” table where the “City” column is set to “Paris”.

It’s important to note that the DELETE statement permanently removes the specified rows from the table, and this operation cannot be undone. Therefore, it’s recommended to use the DELETE statement with caution and to always make a backup of the table or the database before executing a DELETE statement.

Day 66 – SQL CREATE VIEW Keyword

SQL CREATE VIEW keyword is used to create a virtual table based on the result of a SELECT query. A view is a stored SQL query that can be referenced as if it were a table, and it can be used to simplify complex queries, to provide a controlled view of data to different users or applications, or to hide sensitive or irrelevant data from users.

The basic syntax for using the CREATE VIEW keyword is as follows:

CREATE VIEW view_name
AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Here, view_name is the name of the new view, and the SELECT statement defines the columns and data that will be included in the view. The table_name and condition are optional, and they define the table(s) and the condition(s) that the data should be retrieved from.

For example, to create a view named “student_info” that retrieves information about students from a “Students” table, the SQL statement would be:

CREATE VIEW student_info
AS
SELECT StudentID, FirstName, LastName, Email
FROM Students
WHERE Status = 'Active';

This would create a new view named “student_info” that retrieves the columns “StudentID”, “FirstName”, “LastName”, and “Email” from the “Students” table, but only for students whose “Status” is set to “Active”.

Once a view has been created, it can be used like a regular table in SQL queries, and any changes made to the underlying tables will be reflected in the view. However, it’s important to note that views do not store any data themselves, and they are only a virtual representation of the underlying data.

Day 65 – SQL CREATE UNIQUE INDEX Keyword

SQL CREATE UNIQUE INDEX keyword is used to create a new unique index on one or more columns of a table. A unique index is similar to a regular index, but it enforces a constraint that ensures that each value in the indexed column(s) is unique, i.e., it cannot be duplicated.

The basic syntax for using the CREATE UNIQUE INDEX keyword is as follows:

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

Here, index_name is the name of the new unique index, and table_name is the name of the table on which the index will be created. The column1, column2, etc. are the names of the columns that will be included in the index.

For example, to create a unique index named “idx_students_email” on the “Students” table, based on the “Email” column, the SQL statement would be:

CREATE UNIQUE INDEX idx_students_email
ON Students (Email);

This would create a new unique index named “idx_students_email” on the “Students” table, based on the “Email” column.

Unlike regular indexes, unique indexes cannot be used to enforce a foreign key constraint, as they only ensure that the values in the indexed column(s) are unique within the same table. However, unique indexes can be used to improve the performance of SQL queries that involve the indexed column(s), as they allow the database to quickly retrieve data based on the values in the indexed column(s).

Day 64 – SQL CREATE PROCEDURE

SQL CREATE PROCEDURE keyword is used to create a stored procedure in a database. A stored procedure is a pre-compiled set of SQL statements that can be executed by a database server. Stored procedures are often used to encapsulate complex business logic, to provide an interface to legacy code, or to improve performance by reducing the number of network round-trips required to execute a series of related SQL statements.

The basic syntax for using the CREATE PROCEDURE keyword is as follows:

CREATE PROCEDURE procedure_name
    [ ( parameter_name data_type [ = default_value ] ) [ , ... ] ]
AS
BEGIN
    sql_statement;
    [ sql_statement; ... ]
END;

Here, procedure_name is the name of the new stored procedure, and parameter_name is an optional parameter that can be used to pass values into the stored procedure. The data_type specifies the data type of the parameter, and default_value is an optional default value for the parameter.

The AS keyword is used to begin the definition of the stored procedure, and the BEGIN and END keywords are used to define the body of the stored procedure, which can contain one or more SQL statements.

For example, to create a stored procedure named “get_student_info” that retrieves information about a student from a database, the SQL statement would be:

CREATE PROCEDURE get_student_info
    @student_id INT
AS
BEGIN
    SELECT * FROM Students WHERE StudentID = @student_id;
END;

This would create a new stored procedure named “get_student_info” that accepts a single parameter, @student_id, and returns a result set containing all columns from the “Students” table where the “StudentID” column matches the specified value.

Once a stored procedure has been created, it can be executed by calling its name from another SQL statement or from an application program that connects to the database.