Tag Archives: SQL

Day 82 – Views

These are virtual tables that are created based on the result set of a query. Views can be used to simplify complex queries by hiding the underlying complexity, or to provide a level of security by controlling data access.

CREATE VIEW vwTopCustomers
AS
SELECT TOP 10 * FROM Customers
ORDER BY Sales DESC

SELECT * FROM vwTopCustomers

In this example, a view named vwTopCustomers is created to retrieve the top 10 customers from the Customers table based on their sales. The view is then queried using a standard SELECT statement.

Day 81 – Triggers

These are database objects that are automatically executed in response to specific events, such as an update or insert operation on a table. Triggers can be used to enforce data integrity constraints, audit data changes, or send notifications when certain events occur.

CREATE TRIGGER trUpdateCustomer
ON Customers
AFTER UPDATE
AS
BEGIN
    -- Perform some action after a customer record is updated
END

In this example, a trigger named trUpdateCustomer is created to execute some action after a record in the Customers table is updated. The specific action would be defined within the BEGIN and END statements.

Day 80 – Stored Procedures

Stored Procedures: These are pre-compiled SQL statements that are stored in the database and can be executed multiple times. They are useful in reducing network traffic by allowing the client to execute a single procedure call instead of multiple SQL statements. Stored procedures can also be used to enforce business rules and provide a level of security by controlling data access.

CREATE PROCEDURE spGetCustomers
AS
BEGIN
    SELECT * FROM Customers
END

EXEC spGetCustomers

In this example, a stored procedure named spGetCustomers is created to retrieve all customers from the Customers table. The procedure is then executed using the EXEC statement.

Day 79 – SQL FOREIGN KEY Constraint

In SQL, a FOREIGN KEY constraint is a way to enforce referential integrity between two tables. It ensures that the data in the child table (the table containing the foreign key) always refers to valid data in the parent table (the table being referred to by the foreign key).

To create a FOREIGN KEY constraint, you need to specify the column(s) in the child table that will contain the foreign key, as well as the parent table and column(s) that the foreign key will refer to. Here’s an example:

CREATE TABLE orders (
   order_id INT PRIMARY KEY,
   customer_id INT,
   order_date DATE,
   FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

In this example, we’re creating a table called “orders” with an order_id, customer_id, and order_date column. The customer_id column is a foreign key that references the customers table. The REFERENCES keyword specifies the parent table and column that the foreign key refers to.

With this FOREIGN KEY constraint in place, we can ensure that no orders are placed for non-existent customers. If a customer is deleted from the customers table, any orders that reference that customer will also be deleted (or updated, depending on the ON DELETE behavior specified in the constraint).

In summary, the FOREIGN KEY constraint is an important tool for maintaining data integrity in a database. It allows you to create relationships between tables and ensure that those relationships are always valid.

Day 78 – MySQL CONCAT() Function

In MySQL, the CONCAT() function is used to concatenate two or more strings into a single string. The basic syntax of the CONCAT() function is as follows:

CONCAT(string1, string2, ...)

Here, string1, string2, etc. are the strings to concatenate. You can pass any number of strings as arguments to the CONCAT() function, separated by commas.

For example, suppose we have a table called “students” with columns “first_name” and “last_name”. To concatenate the first and last names into a single column called “full_name”, we can use the following query:

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM students;

This will return a result set with a single column called “full_name”, where each row contains the concatenated value of the “first_name” and “last_name” columns, separated by a space.

You can also use the CONCAT() function with other MySQL string functions, such as UPPER() or LOWER(), to manipulate the input strings before concatenation. For example:

SELECT CONCAT(UPPER(first_name), ' ', LOWER(last_name)) AS full_name
FROM students;

This will return a result set where the “first_name” column is converted to uppercase and the “last_name” column is converted to lowercase before concatenation.

Day 77- SQL SET

In SQL, the SET keyword is used to update the values of one or more columns in a table. The basic syntax of an UPDATE statement with SET keyword is as follows:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Here, table_name is the name of the table to update, column1, column2, etc. are the names of the columns to update, value1, value2, etc. are the new values for the corresponding columns, and condition is an optional condition to filter the rows that will be updated.

For example, suppose we have a table called “employees” with columns “id”, “name”, “salary”, and “department”. To update the salary of all employees in the “sales” department by a fixed percentage, we can use the following query:

UPDATE employees
SET salary = salary * 1.1
WHERE department = 'sales';

This will update the “salary” column of all rows in the “employees” table where the “department” column is equal to “sales” by multiplying the current value by 1.1.

Day 75 – SQL RIGHT JOIN Keyword

In SQL, a RIGHT JOIN is a type of join that returns all the records from the right table (the second table specified in the join clause) and matching records from the left table (the first table specified in the join clause). If there are no matching records in the left table, NULL values are returned for those columns.

The basic syntax for a RIGHT JOIN is as follows:

SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Here, column1, column2, etc. are the names of the columns to be returned in the result set, table1 is the name of the first table, table2 is the name of the second table, and column_name is the name of the column(s) that are used to join the two tables.

For example, consider the following two tables, “employees” and “departments”:

employees:
+----+----------+-------+
| ID | Name     | DeptID|
+----+----------+-------+
| 1  | John     | 1     |
| 2  | Mary     | 2     |
| 3  | David    | 3     |
| 4  | Sarah    | 2     |
| 5  | Michael  | NULL  |
+----+----------+-------+

departments:
+--------+--------------+
| DeptID | Department   |
+--------+--------------+
| 1      | HR           |
| 2      | IT           |
| 3      | Finance      |
+--------+--------------+

To return all employees and their departments, including employees with no department (i.e., NULL department ID), we can use a RIGHT JOIN:

SELECT e.Name, d.Department
FROM employees e
RIGHT JOIN departments d
ON e.DeptID = d.DeptID;

This will return the following result set:

+---------+--------------+
| Name    | Department   |
+---------+--------------+
| John    | HR           |
| Mary    | IT           |
| David   | Finance      |
| Sarah   | IT           |
| Michael | NULL         |
+---------+--------------+

Note that the “Michael” row is included in the result set, even though there is no matching department record for that employee.

Day 76 – SQL SELECT TOP, LIMIT and ROWNUM Keywords

In SQL, the keywords TOP, LIMIT, and ROWNUM are used to limit the number of rows returned in a query result set.

• TOP is used in Microsoft SQL Server and Sybase databases to limit the number of rows returned by a query. The basic syntax is: SELECT TOP n column1, column2, ... FROM table_name WHERE condition; Here, n is the number of rows to be returned, column1, column2, etc. are the names of the columns to be returned, table_name is the name of the table to query, and condition is an optional condition to filter the results.
• LIMIT is used in MySQL, PostgreSQL, SQLite, and some other databases to limit the number of rows returned by a query. The basic syntax is: SELECT column1, column2, ... FROM table_name WHERE condition LIMIT n; Here, n is the number of rows to be returned, column1, column2, etc. are the names of the columns to be returned, table_name is the name of the table to query, and condition is an optional condition to filter the results.
• ROWNUM is used in Oracle databases to limit the number of rows returned by a query. The basic syntax is: SELECT column1, column2, ... FROM ( SELECT column1, column2, ... FROM table_name WHERE condition ORDER BY column_name ) WHERE ROWNUM <= n; Here, n is the number of rows to be returned, column1, column2, etc. are the names of the columns to be returned, table_name is the name of the table to query, condition is an optional condition to filter the results, and column_name is the name of the column used to sort the results.

For example, suppose we have a table called “employees” with columns “id”, “name”, and “salary”. To return the top 10 highest paid employees in Microsoft SQL Server, we can use the following query:

SELECT TOP 10 name, salary
FROM employees
ORDER BY salary DESC;

To achieve the same result in MySQL, we can use the following query:

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 10;

To achieve the same result in Oracle, we can use the following query:

SELECT name, salary
FROM (
  SELECT name, salary
  FROM employees
  ORDER BY salary DESC
)
WHERE ROWNUM <= 10;

Day 74 – SQL CREATE PROCEDURE Keyword

In SQL, the CREATE PROCEDURE statement is used to create a stored procedure, which is a set of SQL statements that can be stored in a database and executed later as a single unit. Stored procedures are commonly used to encapsulate complex SQL logic that is reused across multiple database applications.

The basic syntax for creating a stored procedure is as follows:

CREATE PROCEDURE procedure_name
  [ ( parameter [ datatype ] [ = default ] [, ...] ) ]
AS
BEGIN
  SQL statement(s);
END;

Here, procedure_name is the name of the stored procedure, parameter is an optional parameter that can be passed to the stored procedure, datatype is the data type of the parameter, and default is the default value of the parameter.

For example, to create a stored procedure that retrieves all orders for a given customer, the SQL statement would be:

CREATE PROCEDURE sp_GetCustomerOrders
  @CustomerID int
AS
BEGIN
  SELECT *
  FROM Orders
  WHERE CustomerID = @CustomerID;
END;

In this example, the stored procedure is named “sp_GetCustomerOrders” and accepts a single parameter, @CustomerID. The stored procedure uses a SELECT statement to retrieve all orders from the “Orders” table where the CustomerID column matches the @CustomerID parameter.

Once the stored procedure is created, it can be executed by calling its name and passing the required parameter(s), like so:

EXEC sp_GetCustomerOrders @CustomerID = 1234;

This will execute the “sp_GetCustomerOrders” stored procedure with a value of 1234 for the @CustomerID parameter, returning all orders for the specified customer.

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.