SELECT EmployeeID ,'INSERT',GETDATE() FROM INSERTED;
INSERT INTO [dbo].[Employee]
([Name])
VALUES
('test')
----------------------------------------
Introduction to the SQL INNER JOIN clause
So far, you have learned how to use the SELECT statement to query data from a single table. However, the SELECT statement is not limited to query data from a single table. The SELECT statement can link multiple tables together.
The process of linking tables is called joining. SQL provides many kinds of joins such as inner join, left join, right join, full outer join, etc. This tutorial focuses on the inner join.
Suppose, you have two tables: A and B.
Table A has four rows: (1,2,3,4) and table B has four rows: (3,4,5,6)
When table A joins with table B using the inner join, you have the result set (3,4) that is the intersection of table A and table B.
See the following picture.
For each row in table A, the inner join clause finds the matching rows in table B. If a row is matched, it is included in the final result set.
1) Using SQL INNER JOIN to join two tables example
SELECT
first_name,
last_name,
employees.department_id,
departments.department_id,
department_name
FROM
employees
INNERJOIN
departments ON departments.department_id = employees.department_id
WHERE
employees.department_id IN (1 , 2, 3);
For each row in the employees table, the statement checks if the value of the department_id column equals the value of the department_id column in the departments table. If the condition
If the condition employees.department_id = departments.department_id is satisfied, the combined row that includes data from rows in both employees and departments tables are included in the result set.
Notice that both employees and departments tables have the same column name department_id, therefore we had to qualify the department_id column using the syntax table_name.column_name.
SQL INNER JOIN 3 tables example
Each employee holds one job while a job may be held by many employees. The relationship between the jobs table and the employees table is one-to-many.
The following database diagram illustrates the relationships between employees, departments and jobs tables:
The following query uses the inner join clauses to join 3 tables: employees, departments, and jobs to get the first name, last name, job title, and department name of employees who work in department id 1, 2, and 3.
SELECT
first_name,
last_name,
job_title,
department_name
FROM
employees e
INNERJOIN departments d ON d.department_id = e.department_id
INNERJOIN jobs j ON j.job_id = e.job_id
WHERE
e.department_id IN (1, 2, 3);
In the previous tutorial, you learned about the inner join that returns rows if there is, at least, one row in both tables that matches the join condition. The inner join clause eliminates the rows that do not match with a row of the other table.
The left join, however, returns all rows from the left table whether or not there is a matching row in the right table.
Suppose we have two tables A and B. The table A has four rows 1, 2, 3 and 4. The table B also has four rows 3, 4, 5, 6.
When we join table A with table B, all the rows in table A (the left table) are included in the result set whether there is a matching row in the table B or not.
SELECT
c.country_name,
c.country_id,
l.country_id,
l.street_address,
l.city
FROM
countries c
LEFTJOIN locations l ON l.country_id = c.country_id
WHERE
c.country_id IN ('US', 'UK', 'CN')
The condition in the WHERE clause is applied so that the statement only retrieves the data from the US, UK, and China rows.
Because we use the LEFT JOIN clause, all rows that satisfy the condition in the WHERE clause of the countries table are included in the result set.
For each row in the countries table, the LEFT JOIN clause finds the matching rows in the locations table.
If at least one matching row found, the database engine combines the data from columns of the matching rows in both tables.
In case there is no matching row found e.g., with the country_id CN, the row in the countries table is included in the result set and the row in the locations table is filled with NULL values.
Because non-matching rows in the right table are filled with the NULL values, you can apply the LEFT JOIN clause to miss-match rows between tables.
For example, to find the country that does not have any locations in the locations table, you use the following query:
SELECT
country_name
FROM
countries c
LEFTJOIN locations l ON l.country_id = c.country_id
WHERE
l.location_id ISNULLORDERBY
country_name;
In theory, a full outer join is the combination of a left join and a right join. The full outer join includes all rows from the joined tables whether or not the other table has the matching row.
If the rows in the joined tables do not match, the result set of the full outer join contains NULL values for every column of the table that lacks a matching row. For the matching rows, a single row that has the columns populated from the joined table is included in the result set.
The following statement illustrates the syntax of the full outer join of two tables:
SELECT column_list
FROM A
FULLOUTERJOIN B ON B.n = A.n;
The following Venn diagram illustrates the full outer join of two tables.
SQL FULL OUTER JOIN examples
Let’s take an example of using the FULL OUTER JOIN clause to see how it works.
First, create two new tables: baskets and fruits for the demonstration. Each basket stores zero or more fruits and each fruit can be stored in zero or one basket.
Third, the following query returns each fruit that is in a basket and each basket that has a fruit, but also returns each fruit that is not in any basket and each basket that does not have any fruit.
SELECT
basket_name,
fruit_name
FROM
fruits
FULLOUTERJOIN baskets ON baskets.basket_id = fruits.basket_id;
A cross join is a join operation that produces the Cartesian product of two or more tables.
In Math, a Cartesian product is a mathematical operation that returns a product set of multiple sets.
For example, with two sets A {x,y,z} and B {1,2,3}, the Cartesian product of A x B is the set of all ordered pairs (x,1), (x,2), (x,3), (y,1) (y,2), (y,3), (z,1), (z,2), (z,3).
The following picture illustrates the Cartesian product of A and B:
Similarly, in SQL, a Cartesian product of two tables A and B is a result set in which each row in the first table (A) is paired with each row in the second table (B). Suppose the A table has n rows and the B table has m rows, the result of the cross join of the A and B tables have n x m rows.
The following picture illustrates the result of the cross join between the table A and table B. In this illustration, the table A has three rows 1, 2 and 3 and the table B also has three rows x, y and z. As the result, the Cartesian product has nine rows:
The company can distribute goods via various channels such as wholesale, retail, eCommerce, and TV shopping. The following statement inserts sales channels into the sales_channel table:
To find the all possible sales channels that a sales organization can have, you use the CROSS JOIN to join the sales_organization table with the sales_channel table as follows:
SELECT
sales_org,
channel
FROM
sales_organization
CROSSJOIN sales_channel;
Sometimes, it is useful to join a table to itself. This type of join is known as the self-join.
We join a table to itself to evaluate the rows with other rows in the same table. To perform the self-join, we use either an inner join or left join clause.
Because the same table appears twice in a single query, we have to use the table aliases. The following statement illustrates how to join a table to itself.
SQL self-join examples
See the following employees table.
The manager_id column specifies the manager of an employee. The following statement joins the employees table to itself to query the information of who reports to whom.
SELECT
e.first_name || ' ' || e.last_name AS employee,
m.first_name || ' ' || m.last_name AS manager
FROM
employees e
INNERJOIN
employees m ON m.employee_id = e.manager_id
ORDERBY manager;
The president does not have any manager. In the employees table, the manager_id of the row that contains the president is NULL.
Because the inner join clause only includes the rows that have matching rows in the other table, therefore the president did not show up in the result set of the query above.
To include the president in the result set, we use the LEFT JOIN clause instead of the INNER JOIN clause as the following query.
SELECT
e.first_name || ' ' || e.last_name AS employee,
m.first_name || ' ' || m.last_name AS manager
FROM
employees e
LEFTJOIN
employees m ON m.employee_id = e.manager_id
ORDERBY manager;
Write SELECT statements to access data from more than one table
View data that generally does not meet a join condition by using outer joins Join a table by using a self-join
Introduction to the SQL INNER JOIN clause
So far, you have learned how to use the SELECT statement to query data from a single table. However, the SELECT statement is not limited to query data from a single table. The SELECT statement can link multiple tables together.
The process of linking tables is called joining. SQL provides many kinds of joins such as inner join, left join, right join, full outer join, etc. This tutorial focuses on the inner join.
Suppose, you have two tables: A and B.
Table A has four rows: (1,2,3,4) and table B has four rows: (3,4,5,6)
When table A joins with table B using the inner join, you have the result set (3,4) that is the intersection of table A and table B.
See the following picture.
For each row in table A, the inner join clause finds the matching rows in table B. If a row is matched, it is included in the final result set.
1) Using SQL INNER JOIN to join two tables example
SELECT
first_name,
last_name,
employees.department_id,
departments.department_id,
department_name
FROM
employees
INNERJOIN
departments ON departments.department_id = employees.department_id
WHERE
employees.department_id IN (1 , 2, 3);
For each row in the employees table, the statement checks if the value of the department_id column equals the value of the department_id column in the departments table. If the condition
If the condition employees.department_id = departments.department_id is satisfied, the combined row that includes data from rows in both employees and departments tables are included in the result set.
Notice that both employees and departments tables have the same column name department_id, therefore we had to qualify the department_id column using the syntax table_name.column_name.
SQL INNER JOIN 3 tables example
Each employee holds one job while a job may be held by many employees. The relationship between the jobs table and the employees table is one-to-many.
The following database diagram illustrates the relationships between employees, departments and jobs tables:
The following query uses the inner join clauses to join 3 tables: employees, departments, and jobs to get the first name, last name, job title, and department name of employees who work in department id 1, 2, and 3.
SELECT
first_name,
last_name,
job_title,
department_name
FROM
employees e
INNERJOIN departments d ON d.department_id = e.department_id
INNERJOIN jobs j ON j.job_id = e.job_id
WHERE
e.department_id IN (1, 2, 3);
In the previous tutorial, you learned about the inner join that returns rows if there is, at least, one row in both tables that matches the join condition. The inner join clause eliminates the rows that do not match with a row of the other table.
The left join, however, returns all rows from the left table whether or not there is a matching row in the right table.
Suppose we have two tables A and B. The table A has four rows 1, 2, 3 and 4. The table B also has four rows 3, 4, 5, 6.
When we join table A with table B, all the rows in table A (the left table) are included in the result set whether there is a matching row in the table B or not.
SELECT
c.country_name,
c.country_id,
l.country_id,
l.street_address,
l.city
FROM
countries c
LEFTJOIN locations l ON l.country_id = c.country_id
WHERE
c.country_id IN ('US', 'UK', 'CN')
The condition in the WHERE clause is applied so that the statement only retrieves the data from the US, UK, and China rows.
Because we use the LEFT JOIN clause, all rows that satisfy the condition in the WHERE clause of the countries table are included in the result set.
For each row in the countries table, the LEFT JOIN clause finds the matching rows in the locations table.
If at least one matching row found, the database engine combines the data from columns of the matching rows in both tables.
In case there is no matching row found e.g., with the country_id CN, the row in the countries table is included in the result set and the row in the locations table is filled with NULL values.
Because non-matching rows in the right table are filled with the NULL values, you can apply the LEFT JOIN clause to miss-match rows between tables.
For example, to find the country that does not have any locations in the locations table, you use the following query:
SELECT
country_name
FROM
countries c
LEFTJOIN locations l ON l.country_id = c.country_id
WHERE
l.location_id ISNULLORDERBY
country_name;
In theory, a full outer join is the combination of a left join and a right join. The full outer join includes all rows from the joined tables whether or not the other table has the matching row.
If the rows in the joined tables do not match, the result set of the full outer join contains NULL values for every column of the table that lacks a matching row. For the matching rows, a single row that has the columns populated from the joined table is included in the result set.
The following statement illustrates the syntax of the full outer join of two tables:
SELECT column_list
FROM A
FULLOUTERJOIN B ON B.n = A.n;
The following Venn diagram illustrates the full outer join of two tables.
SQL FULL OUTER JOIN examples
Let’s take an example of using the FULL OUTER JOIN clause to see how it works.
First, create two new tables: baskets and fruits for the demonstration. Each basket stores zero or more fruits and each fruit can be stored in zero or one basket.
Third, the following query returns each fruit that is in a basket and each basket that has a fruit, but also returns each fruit that is not in any basket and each basket that does not have any fruit.
SELECT
basket_name,
fruit_name
FROM
fruits
FULLOUTERJOIN baskets ON baskets.basket_id = fruits.basket_id;
A cross join is a join operation that produces the Cartesian product of two or more tables.
In Math, a Cartesian product is a mathematical operation that returns a product set of multiple sets.
For example, with two sets A {x,y,z} and B {1,2,3}, the Cartesian product of A x B is the set of all ordered pairs (x,1), (x,2), (x,3), (y,1) (y,2), (y,3), (z,1), (z,2), (z,3).
The following picture illustrates the Cartesian product of A and B:
Similarly, in SQL, a Cartesian product of two tables A and B is a result set in which each row in the first table (A) is paired with each row in the second table (B). Suppose the A table has n rows and the B table has m rows, the result of the cross join of the A and B tables have n x m rows.
The following picture illustrates the result of the cross join between the table A and table B. In this illustration, the table A has three rows 1, 2 and 3 and the table B also has three rows x, y and z. As the result, the Cartesian product has nine rows:
The company can distribute goods via various channels such as wholesale, retail, eCommerce, and TV shopping. The following statement inserts sales channels into the sales_channel table:
To find the all possible sales channels that a sales organization can have, you use the CROSS JOIN to join the sales_organization table with the sales_channel table as follows:
SELECT
sales_org,
channel
FROM
sales_organization
CROSSJOIN sales_channel;
Sometimes, it is useful to join a table to itself. This type of join is known as the self-join.
We join a table to itself to evaluate the rows with other rows in the same table. To perform the self-join, we use either an inner join or left join clause.
Because the same table appears twice in a single query, we have to use the table aliases. The following statement illustrates how to join a table to itself.
SQL self-join examples
See the following employees table.
The manager_id column specifies the manager of an employee. The following statement joins the employees table to itself to query the information of who reports to whom.
SELECT
e.first_name || ' ' || e.last_name AS employee,
m.first_name || ' ' || m.last_name AS manager
FROM
employees e
INNERJOIN
employees m ON m.employee_id = e.manager_id
ORDERBY manager;
The president does not have any manager. In the employees table, the manager_id of the row that contains the president is NULL.
Because the inner join clause only includes the rows that have matching rows in the other table, therefore the president did not show up in the result set of the query above.
To include the president in the result set, we use the LEFT JOIN clause instead of the INNER JOIN clause as the following query.
SELECT
e.first_name || ' ' || e.last_name AS employee,
m.first_name || ' ' || m.last_name AS manager
FROM
employees e
LEFTJOIN
employees m ON m.employee_id = e.manager_id
ORDERBY manager;
This solution has two problems. To start with, you have looked at the departments table to check which department belongs to the location 1700. However, the original question was not referring to any specific departments; it referred to the location 1700.
Because of the small data volume, you can get a list of department easily. However, in the real system with high volume data, it might be problematic.
Another problem was that you have to revise the queries whenever you want to find employees who locate in a different location.
A much better solution to this problem is to use a subquery. By definition, a subquery is a query nested inside another query such as SELECT, INSERT, UPDATE, or DELETE statement. In this tutorial, we are focusing on the subquery used with the SELECT statement.
In this example, you can rewrite combine the two queries above as follows:
SELECT
employee_id, first_name, last_name
FROM
employees
WHERE
department_id IN (SELECT
department_id
FROM
departments
WHERE
location_id = 1700)
ORDERBY first_name , last_name;
The query placed within the parentheses is called a subquery. It is also known as an inner query or inner select. The query that contains the subquery is called an outer query or an outer select.
To execute the query, first, the database system has to execute the subquery and substitute the subquery between the parentheses with its result – a number of department id located at the location 1700 – and then executes the outer query.
Let’s take some examples of using the subqueries to understand how they work.
SQL subquery with the IN or NOT IN operator
In the previous example, you have seen how the subquery was used with the IN operator. The following example uses a subquery with the NOT IN operator to find all employees who do not locate at the location 1700:
SELECT
employee_id, first_name, last_name
FROM
employees
WHERE
department_id NOTIN (SELECT
department_id
FROM
departments
WHERE
location_id = 1700)
ORDERBY first_name , last_name;
In this example, the subquery returns the highest salary of all employees and the outer query finds the employees whose salary is equal to the highest one.
The following statement finds all employees who salaries are greater than the average salary of all employees:
SELECT
employee_id, first_name, last_name, salary
FROM
employees
WHERE
salary > (SELECTAVG(salary)
FROM
employees);
In this example, first, the subquery returns the average salary of all employees. Then, the outer query uses the greater than operator to find all employees whose salaries are greater than the average.
SQL subquery with the EXISTS or NOT EXISTS operator
The EXISTS operator checks for the existence of rows returned from the subquery. It returns true if the subquery contains any rows. Otherwise, it returns false.
The following example finds all departments which have at least one employee with the salary is greater than 10,000:
SELECT
department_name
FROM
departments d
WHEREEXISTS( SELECT1FROM
employees e
WHERE
salary > 10000AND e.department_id = d.department_id)
ORDERBY department_name;
The UNION operator combines result sets of two or more SELECT statements into a single result set. The following statement illustrates how to use the UNION operator to combine result sets of two queries:
To use the UNION operator, you write the dividual SELECT statements and join them by the keyword UNION.
The columns returned by the SELECT statements must have the same or convertible data type, size, and be the same order.
The database system processes the query by executing two SELECT statements first. Then, it combines two individual result sets into one and eliminates duplicate rows. To eliminate the duplicate rows, the database system sorts the combined result set by every column and scans it for the matching rows located next to one another.
To retain the duplicate rows in the result set, you use the UNION ALL operator.
Suppose, we have two result sets A(1,2) and B(2,3). The following picture illustrates A UNION B:
And the following picture illustrates A UNION ALL B
The union is different from the join that the join combines columns of multiple tables while the union combines rows of the tables.
The database system performs the following steps:
First, execute each SELECT statement individually.
Second, combine result sets and remove duplicate rows to create the combined result set.
Third, sort the combined result set by the column specified in the ORDER BY clause.
In practice, we often use the UNION operator to combine data from different tables. See the following employees and dependents tables:
The following statement uses the UNION operator to combine the first name and last name of employees and dependents.
SELECT
first_name,
last_name
FROM
employees
UNIONSELECT
first_name,
last_name
FROM
dependents
ORDERBY
last_name;
In this tutorial, you have learned how to use the UNION operator to combine two or more result sets from multiple queries.
Introduction to SQL INTERSECT operator
The INTERSECT operator is a set operator that returns distinct rows of two or more result sets from SELECT statements.
Suppose, we have two tables: A(1,2) and B(2,3).
The following picture illustrates the intersection of A & B tables.
The purple section is the intersection of the green and blue result sets.
Like the UNION operator, the INTERSECT operator removes the duplicate rows from the final result set.
To use the INTERSECT operator, the columns of the SELECT statements must follow the rules:
The data types of columns must be compatible.
The number of columns and their orders in the SELECT statements must be the same.
SQL INTERSECT with ORDER BY example
To sort the result set returned by the INTERSECT operator, you place the ORDER BY clause at the end of all statements.
For example, the following statement applies the INTERSECT operator to the A and B tables and sorts the combined result set by the id column in descending order.
SELECTidFROM
a
INTERSECTSELECTidFROM
b
ORDERBYidDESC;
Emulate SQL INTERSECT operator using INNER JOIN clause
Most relational database system supports the INTERSECT operator such as Oracle Database, Microsoft SQL Server, PostgreSQL, etc. However, some database systems do not provide the INTERSECT operator like MySQL.
To emulate the SQL INTERSECT operator, you can use the INNER JOIN clause as follows:
It returns the rows in the A table that have matching rows in the B table, which produces the same result as the INTERSECT operator.
Introduction to SQL MINUS operator
Besides the UNION, UNION ALL, and INTERSECT operators, SQL provides us with the MINUS operator that allows you to subtract one result set from another result set.
To use the MINUS operator, you write individual SELECT statements and place the MINUS operator between them. The MINUS operator returns the unique rows produced by the first query but not by the second one.
The following picture illustrates the MINUS operator.
To make the result set, the database system performs two queries and subtracts the result set of the first query from the second one.
In order to use the MINUS operator, the columns in the SELECT clauses must match in number and must have the same or, at least, convertible data type.
We often use the MINUS operator in ETL. An ETL is a software component in data warehouse system. ETL stands for Extract, Transform, and Load. ETL is responsible for loading data from the source systems into the data warehouse system.
After complete loading data, we can use the MINUS operator to make sure that the data has been loaded fully by subtracting data in target system from the data in the source system.
Each employee has zero or more dependents while each dependent depends on one and only one employees. The relationship between the dependents and employees is the one-to-many relationship.
The employee_id column in the dependents table references to the employee_id column in the employees table.
You can use the MINUS operator to find the employees who do not have any dependents. To do this, you subtract the employee_id result set in the employees table from the employee_id result set in the dependents table.
The following query illustrates the idea:
SELECT
employee_id
FROM
employees
MINUSSELECT
employee_id
FROM
dependents;
SQL MINUS with ORDER BY example
To sort the result set returned by the MINUS operator, you place the ORDER BY clause at the end of the last SELECT statement.
For example, to sort the employees who do not have any dependents, you use the following query:
SELECT
employee_id
FROM
employees
MINUSSELECT
employee_id
FROM
dependents
ORDERBY employee_id;
The INTERSECT operator is a set operator that returns distinct rows of two or more result sets from SELECT statements.
Suppose, we have two tables: A(1,2) and B(2,3).
The following picture illustrates the intersection of A & B tables.
The purple section is the intersection of the green and blue result sets.
Like the UNION operator, the INTERSECT operator removes the duplicate rows from the final result set.
To use the INTERSECT operator, the columns of the SELECT statements must follow the rules:
The data types of columns must be compatible.
The number of columns and their orders in the SELECT statements must be the same.
SQL INTERSECT with ORDER BY example
To sort the result set returned by the INTERSECT operator, you place the ORDER BY clause at the end of all statements.
For example, the following statement applies the INTERSECT operator to the A and B tables and sorts the combined result set by the id column in descending order.
SELECTidFROM
a
INTERSECTSELECTidFROM
b
ORDERBYidDESC;
Besides the UNION, UNION ALL, and INTERSECT operators, SQL provides us with the MINUS operator that allows you to subtract one result set from another result set.
To use the MINUS operator, you write individual SELECT statements and place the MINUS operator between them. The MINUS operator returns the unique rows produced by the first query but not by the second one.
The following picture illustrates the MINUS operator.
To make the result set, the database system performs two queries and subtracts the result set of the first query from the second one.
In order to use the MINUS operator, the columns in the SELECT clauses must match in number and must have the same or, at least, convertible data type.
We often use the MINUS operator in ETL. An ETL is a software component in data warehouse system. ETL stands for Extract, Transform, and Load. ETL is responsible for loading data from the source systems into the data warehouse system.
After complete loading data, we can use the MINUS operator to make sure that the data has been loaded fully by subtracting data in target system from the data in the source system.
Each employee has zero or more dependents while each dependent depends on one and only one employees. The relationship between the dependents and employees is the one-to-many relationship.
The employee_id column in the dependents table references to the employee_id column in the employees table.
You can use the MINUS operator to find the employees who do not have any dependents. To do this, you subtract the employee_id result set in the employees table from the employee_id result set in the dependents table.
The following query illustrates the idea:
SELECT
employee_id
FROM
employees
MINUSSELECT
employee_id
FROM
dependents;
SQL MINUS with ORDER BY example
To sort the result set returned by the MINUS operator, you place the ORDER BY clause at the end of the last SELECT statement.
For example, to sort the employees who do not have any dependents, you use the following query:
SELECT
employee_id
FROM
employees
MINUSSELECT
employee_id
FROM
dependents
ORDERBY employee_id;
Save and discard changes with the COMMIT and ROLLBACK statements Explain read consistency
Other Schema Objects
Create a simple and complex view
Retrieve data from views
Introduction to the SQL Views
A relational database consists of multiple related tables e.g., employees, departments, jobs, etc. When you want to see the data of these tables, you use the SELECT statement with JOIN or UNION clauses.
SQL provides you with another way to see the data is by using the views. A view is like a virtual table produced by executing a query. The relational database management system (RDBMS) stores a view as a named SELECT in the database catalog.
Whenever you issue a SELECT statement that contains a view name, the RDBMS executes the view-defining query to create the virtual table. That virtual table then is used as the source table of the query.
Why do you need to use the views
Views allow you to store complex queries in the database. For example, instead of issuing a complex SQL query each time you want to see the data, you just need to issue a simple query as follows:
Views help you pack the data for a specific group of users. For example, you can create a view of salary data for the employees for Finance department.
Views help maintain database security. Rather than give the users access to database tables, you create a view to revealing only necessary data and grant the users to access to the view.
Creating SQL views
To create a view, you use the CREATE VIEW statement as follows:
First, specify the name of the view after the CREATE VIEW clause.
Second, construct a SELECT statement to query data from multiple tables.
For example, the following statement creates the employee contacts view based on the data of the employees and departments tables.
CREATEVIEW employee_contacts ASSELECT
first_name, last_name, email, phone_number, department_name
FROM
employees e
INNERJOIN
departments d ON d.department_id = e.department_id
ORDERBY first_name;
By default, the names of columns of the view are the same as column specified in the SELECT statement. If you want to rename the columns in the view, you include the new column names after the CREATE VIEW clause as follows:
The DROP VIEW statement deletes the view only, not the base tables.
For example, to remove the payroll view, you use the following statement:
DROPVIEW payroll;
Create and maintain indexes
Why SQL Index?
The following reasons tell why Index is necessary in SQL:
SQL Indexes can search the information of the large database quickly.
This concept is a quick process for those columns, including different values.
This data structure sorts the data values of columns (fields) either in ascending or descending order. And then, it assigns the entry for each value.
Each Index table contains only two columns. The first column is row_id, and the other is indexed-column.
When indexes are used with smaller tables, the performance of the index may not be recognized.
Create an INDEX
In SQL, we can easily create the Index using the following CREATE Statement:
CREATEINDEX Index_Name ON Table_Name ( Column_Name);
Here, Index_Name is the name of that index that we want to create, and Table_Name is the name of the table on which the index is to be created. The Column_Name represents the name of the column on which index is to be applied.
CREATEINDEX index_state ON Employee (Emp_State);
Suppose we want to create an index on the combination of the Emp_city and the Emp_State column of the above Employee table. For this, we have to use the following query:
CREATEINDEX index_city_State ON Employee (Emp_City, Emp_State);
Create UNIQUE INDEX
Unique Index is the same as the Primary key in SQL. The unique index does not allow selecting those columns which contain duplicate values.
This index is the best way to maintain the data integrity of the SQL tables.
Syntax for creating the Unique Index is as follows:
CREATEUNIQUEINDEX Index_Name ON Table_Name ( Column_Name);
Introduction to SQL Server clustered indexes
The following statement creates a new table named production.parts that consists of two columns part_id and part_name:
The production.parts table does not have a primary key. Therefore SQL Server stores its rows in an unordered structure called a heap.
When you query data from the production.parts table, the query optimizer needs to scan the whole table to search.
For example, the following SELECT statement finds the part with id 5:
SELECT
part_id,
part_name
FROM
production.parts
WHERE
part_id = 5;
If you display the estimated execution plan in SQL Server Management Studio, you’ll see how SQL Server come up with the following query plan:
Note that to display the estimated execution plan in SQL Server Management Studio, you click the Display Estimated Execution Plan button or select the query and press the keyboard shortcut Ctrl+L:
Because the production.parts table has only five rows, the query executes very fast. However, if the table contains a large number of rows, it’ll take a lot of time and resources to search for data.
To resolve this issue, SQL Server provides a dedicated structure to speed up the retrieval of rows from a table called index.
SQL Server has two types of indexes: clustered index and non-clustered index. We will focus on the clustered index in this tutorial.
A clustered index stores data rows in a sorted structure based on its key values. Each table has only one clustered index because data rows can be only sorted in one order. A table that has a clustered index is called a clustered table.
The following picture illustrates the structure of a clustered index:
A clustered index organizes data using a special structured so-called B-tree (or balanced tree) which enables searches, inserts, updates and deletes in logarithmic amortized time.
In this structure, the top node of the B-tree is called the root node. The nodes at the bottom level are called the leaf nodes. Any index levels between the root and the leaf nodes are known as intermediate levels.
In the B-Tree, the root node and intermediate level nodes contain index pages that hold index rows. The leaf nodes contain the data pages of the underlying table. The pages in each level of the index are linked using another structure called a doubly-linked list.
SQL Server Clustered Index and Primary key constraint
When you create a table with a primary key, SQL Server automatically creates a corresponding clustered index that includes primary key columns.
This statement creates a new table named production.part_prices with a primary key that includes two columns: part_id and valid_from.
If you add a primary key constraint to an existing table that already has a clustered index, SQL Server will enforce the primary key using a non-clustered index:
This statement defines a primary key for the production.parts table:
ALTERTABLEproduction.partsADDPRIMARYKEY(part_id);
Code language:CSS(css)
SQL Server created a non-clustered index for the primary key.
Using SQL Server CREATE CLUSTERED INDEX statement to create a clustered index.
When a table does not have a primary key, which is very rare, you can use the CREATE CLUSTERED INDEX statement to add a clustered index to it.
The following statement creates a clustered index for the production.parts table:
If you open the Indexes node under the table name, you will see the new index name ix_parts_id with type Clustered.
When executing the following statement, SQL Server traverses the index (Clustered Index Seek) to locate the rows, which is faster than scanning the whole table.
SELECT
part_id,
part_name
FROM
production.parts
WHERE
part_id = 5;
Table Variable
A Table Variable is a variable that can store the complete table of the data inside it. It is similar to a Table Variable but as I said a Table Variable is a variable. So how do we declare a variable in SQL? Using the @ symbol. The same is true for a Table Variable. so the syntax of the Table Variable is as follows:
Declare @TempTable TABLE(
id int,
Namevarchar(20)
)
insertinto @TempTable values(1,'Sourabh Somani')
insertinto @TempTable values(2,'Shaili Dashora')
insertinto @TempTable values(3,'Divya Sharma')
insertinto @TempTable values(4,'Swati Soni')
Select * from @TempTable
Difference between temporary tables and Table Variable
There are a difference between temporary tables and temporary variables, it is:
A Table Variable is not available after execution of the complete query so you cannot run a single query but a temporary table is available after executing the query.
For example:
A Transaction (Commit and Rollback) operation is not possible in a Table Variable but in a temporary table we can perform transactiona (Commit and Rollback).
For example:
Declare @TempTable TABLE(
id int,
Namevarchar(20)
)
begin tran T
insertinto @TempTable values(1,'Sourabh Somani')
insertinto @TempTable values(2,'Shaili Dashora')
insertinto @TempTable values(3,'Divya Sharma')
insertinto @TempTable values(4,'Swati Soni')
commit tran T
Select * from @TempTable
or
Declare @TempTable TABLE(
id int,
Namevarchar(20)
)
begin tran T
insertinto @TempTable values(1,'Sourabh Somani')
insertinto @TempTable values(2,'Shaili Dashora')
insertinto @TempTable values(3,'Divya Sharma')
insertinto @TempTable values(4,'Swati Soni')
rollback tran T
Select * from @TempTable
Important Points about Table Variables
The same as a temporary table.
Single query cannot be executed.
When we want to perform a few operations then use a Table Variable otherwise if it is a huge amount of data operation then use a temporary table.
Commit and Rollback (Transaction) cannot be possible with Table Variables so if you want to perform a transaction operation then always go with temporary tables.
What are scalar functions
SQL Server scalar function takes one or more parameters and returns a single value.
The scalar functions help you simplify your code. For example, you may have a complex calculation that appears in many queries. Instead of including the formula in every query, you can create a scalar function that encapsulates the formula and uses it in each query.
Creating a scalar function
To create a scalar function, you use the CREATE FUNCTION statement as follows:
First, specify the name of the function after the CREATE FUNCTION keywords. The schema name is optional. If you don’t explicitly specify it, SQL Server uses dbo by default.
Second, specify a list of parameters surrounded by parentheses after the function name.
Third, specify the data type of the return value in the RETURNS statement.
Finally, include a RETURN statement to return a value inside the body of the function.
The following example creates a function that calculates the net sales based on the quantity, list price, and discount:
The following are some key takeaway of the scalar functions:
Scalar functions can be used almost anywhere in T-SQL statements.
Scalar functions accept one or more parameters but return only one value, therefore, they must include a RETURN statement.
Scalar functions can use logic such as IF blocks or WHILE loops.
Scalar functions cannot update data. They can access data but this is not a good practice.
Scalar functions can call other functions
What is a table-valued function in SQL Server
A table-valued function is a user-defined function that returns data of a table type. The return type of a table-valued function is a table, therefore, you can use the table-valued function just like you would use a table.
Creating a table-valued function
The following statement example creates a table-valued function that returns a list of products including product name, model year and the list price for a specific model year:
CREATEFUNCTION udfProductInYear (
@model_year INT
)
RETURNSTABLEASRETURNSELECT
product_name,
model_year,
list_price
FROM
production.products
WHERE
model_year = @model_year;
The syntax is similar to the one that creates a user-defined function.
The RETURNS TABLE specifies that the function will return a table. As you can see, there is no BEGIN...END statement. The statement simply queries data from the production.products table.
The udfProductInYear function accepts one parameter named @model_year of type INT. It returns the products whose model years equal @model_year parameter.
Once the table-valued function is created, you can find it under Programmability > Functions > Table-valued Functions as shown in the following picture:
The function above returns the result set of a single SELECT statement, therefore, it is also known as an inline table-valued function.
Executing a table-valued function
To execute a table-valued function, you use it in the FROM clause of the SELECT statement:
To modify a table-valued function, you use the ALTER instead of CREATE keyword. The rest of the script is the same.
For example, the following statement modifies the udfProductInYear by changing the existing parameter and adding one more parameter:
ALTERFUNCTION udfProductInYear (
@start_year INT,
@end_year INT
)
RETURNSTABLEASRETURNSELECT
product_name,
model_year,
list_price
FROM
production.products
WHERE
model_year BETWEEN @start_year AND @end_year
Comments
Post a Comment