SQL(Structured Query Language)
This is a common language through which we can interact with the database.
SQL is classified mainly into three categories.
1.DDL(Data Definition Language)
1. Create 2. Alter 3. Truncate 4. Drop
2. DML(Data Manipulation Language).
1. Select 2. Insert 3. Update 4. Delete
3. TCL(Transaction Control Language).
1. Commit 2.Rollback 3. Save point
4. DCL(Data Control Language)
DDL Command
The Data Definition Language (DDL) is used to create and destroy databases and database objects. These commands will primarily be used by database administrators during the setup and removal phases of a database project. Let's take a look at the structure and usage of four basic DDL commands:
Create:
This command is used to create database object such as –Tables, Views, Indexes, synonyms, sequences, stored procedures, Triggers, Functions, Packages and User-define data types.
Example:-
CREATE TABLE Employee(
empno number(4) constraint pk_empno primary key,
ename varchar2(50) constraint nn_ename not null,
salary number(10,2),
hire_date data,
gender char(1) constraint chk_gen check(gender in ('M', 'F', 'm', 'f')),
email varchar2(50) unique
);
Alter:
This command is used to modify the structure of the database object.
Example:- ALTER TABLE Employee ADD CONSTRAINT nn_hdata NOT NULL hire_date;
Drop:
This command is used to remove the objects from the database.
Example:- DROP TABLE Employee;
Truncate:
Using this command we can permanently remove the rows from a table. Here we cannot append any clauses the truncate statement.
Example:-TRUNCATE TABLE Employee;
DML Command
The Data Manipulation Language (DML) is used to retrieve, insert and modify database information. These commands will be used by all database users during the routine operation of the database. Let's take a brief look at the basic DML commands:
SELECT
It allows database users to retrieve the specific information they desire from an operational database.
Example
SELECT *FROM Employee
INSERT
Using this command we can append data into tables.
Example
INSERT INTO Employee(empno,ename,salary,hire_date,gender,e-mail)
VALUES(1234, 'JOHN', '8000', '18-AUG-80', 'M', 'john@gmail.com');
UPDATE
The UPDATE command can be used to modify information contained within a table, either in bulk or individually.
Example:- UPDATE Employee SET salary=1500;
UPDATE Employee SET salary=1500 WHERE empno=1235;
DELETE
This command is used to remove the data form the tables.
Example:-DELETE FROM Employee;
DELETE FROM Employee WHWRE empno =1236
Join Operation :-
The join operation defined for relational databases is often referred to as a natural join. In this type of join, two relations are connected by their common attributes. A SQL JOIN clause combines records from two or more tables in a database.[1] It creates a set that can be saved as a table or used as is. A JOIN is a means for combining fields from two tables by using values common to each. ANSI standard SQL specifies four types of JOINs: INNER, OUTER, LEFT, and RIGHT.
Inner join:-
An inner join is the most common join operation used in applications, and represents the default join-type. Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The result of the join can be defined as the outcome of first taking the Cartesian product (or cross-join) of all records in the tables (combining every record in table A with every record in table B) - then return all records which satisfy the join predicate.
Example SQL statement
SELECT *FROM employee
INNER JOIN department
ON employee.DepartmentID = department.DepartmentID
Equi-join:-
An equi-join, also known as an equijoin, is a specific type of comparator-based join, or theta join, that uses only equality comparisons in the join-predicate. Using other comparison operators (such as <) disqualifies a join as an equi-join. The query shown above has already provided an example of an equi-join: SELECT *FROM employee INNER JOIN department ON employee.DepartmentID = department.DepartmentID
Natural join:-
A natural join offers a further specialization of equi-joins. The join predicate arises implicitly by comparing all columns in both tables that have the same column-name in the joined tables. The resulting joined table contains only one column for each pair of equally-named columns. The above sample query for inner joins can be expressed as a natural join in the following way:- SELECT *FROM employee NATURAL JOIN department
Cross join:- A cross join, cartesian join or product provides the foundation upon which all types of inner joins operate. A cross join returns the cartesian product of the sets of records from the two joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or where the join-condition is absent from the statement. In other words, a cross join combines every row in B with every row in A. The number of rows in the result set will be the number of rows in A times the number of rows in B.Thus, if A and B are two sets, then the cross join is written as A × B. The SQL code for a cross join lists the tables for joining (FROM), but does not include any filtering join-predicate. Example of an explicit cross join: SELECT * FROM employee CROSS JOIN department Example of an implicit cross join: SELECT *FROM employee, department;
Outer join:- An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table(s) one retains the rows from (left, right, or both). No implicit join-notation for outer joins exists in standard SQL. Left outer join:- The result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result—but with NULL in each column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate). If the left table returns one row and the right table returns more than one matching row for it, the values in the left table will be repeated for each distinct row on the right table. For example, this allows us to find an employee's department, but still shows the employee(s) even when they have not been assigned to a department (contrary to the inner-join example above, where unassigned employees are excluded from the result). Example of a left outer join, with the additional result row italicized: SELECT * FROM employee LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID Right outer joins A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in B. A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate). For example, this allows us to find each employee and his or her department, but still show departments that have no employees. Example right outer join, with the additional result row italicized: SELECT * FROM employee RIGHT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID
Full outer join
A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side. For example, this allows us to see each employee who is in a department and each department that has an employee, but also see each employee who is not part of a department and each department which doesn't have an employee. Example full outer join: SELECT * FROM employee FULL OUTER JOIN department ON employee.DepartmentID = department.DepartmentID UNION SELECT *FROM employee RIGHT JOIN department ON employee.DepartmentID = department.DepartmentID WHERE employee.DepartmentID IS NULL SQLite does not support right join, so outer join can be emulated as follows: SELECT employee.*, department.*FROM employee LEFT JOIN department ON employee.DepartmentID = department.DepartmentID UNION ALL SELECT employee.*, department.* FROM department LEFT JOIN employee ON employee.DepartmentID = department.DepartmentID WHERE employee.DepartmentID IS NULL Self-join A self-join is joining a table to itself.This is best illustrated by the following example. Example A query to find all pairings of two employees in the same country is desired. If you had two separate tables for employees and a query which requested employees in the first table having the same country as employees in the second table, you could use a normal join operation to find the answer table. However, all the employee information is contained within a single large table An example solution query could be as follows: SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country FROM Employee F, Employee S WHERE F.Country = S.Country AND F.EmployeeID < S.EmployeeID ORDER BY F.EmployeeID, S.EmployeeID; Relational operations The union operator combines the tuples of two relations and removes all duplicate tuples from the result. The relational union operator is equivalent to the SQL UNION operator. The intersection operator produces the set of tuples that two relations share in common. Intersection is implemented in SQL in the form of the INTERSECT operator. The difference operator acts on two relations and produces the set of tuples from the first relation that do not exist in the second relation. Difference is implemented in SQL in the form of the EXCEPT or MINUS operator. The cartesian product of two relations is a join that is not restricted by any criteria, resulting in every tuples of the first relation being matched with every tuple of the second relation. The cartesian product is implemented in SQL as the CROSS JOIN join operator.
GROUP BY Clause :-
The GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns.
The syntax for the GROUP BY clause is:
SELECT column1, column2, ... column_n, aggregate_function (expression) FROM tables WHERE predicates
GROUP BY column1, column2, ... column_n;
aggregate_function can be a function such as SUM, COUNT, MIN, or MAX.
Example using the SUM function
For example, you could also use the SUM function to return the name of the department and the total sales (in the associated department).
SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department;Because you have listed one column in your SELECT statement that is not encapsulated in the SUM function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.
Example using the COUNT function
For example, you could use the COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year.
SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;
Example using the MIN function
For example, you could also use the MIN function to return the name of each department and the minimum salary in the department.
SELECT department, MIN(salary) as "Lowest salary"
FROM employees
GROUP BY department;
Example using the MAX function
For example, you could also use the MAX function to return the name of each department and the maximum salary in the department.
SELECT department, MAX(salary) as "Highest salary"
FROM employees
GROUP BY department;
HAVING Clause:-The HAVING clause is used in combination with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns.
The syntax for the HAVING clause is:
SELECT column1, column2, ... column_n, aggregate_function (expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n
HAVING condition1 ... condition_n; aggregate_function can be a function such as SUM, COUNT, MIN, or MAX.
Example using the SUM function
For example, you could also use the SUM function to return the name of the department and the total sales (in the associated department). The HAVING clause will filter the results so that only departments with sales greater than $1000 will be returned.
SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 1000;
Example using the COUNT function
For example, you could use the COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year. The HAVING clause will filter the results so that only departments with more than 10 employees will be returned.
SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department
HAVING COUNT(*) > 10;
Example using the MIN function
For example, you could also use the MIN function to return the name of each department and the minimum salary in the department. The HAVING clause will return only those departments where the starting salary is $35,000.
SELECT department, MIN(salary) as "Lowest salary"
FROM employees
GROUP BY department
HAVING MIN(salary) = 35000;
Example using the MAX function
For example, you could also use the MAX function to return the name of each department and the maximum salary in the department. The HAVING clause will return only those departments whose maximum salary is less than $50,000.
SELECT department, MAX(salary) as "Highest salary"
FROM employees
GROUP BY department
HAVING MAX(salary) < 50000;
This is a common language through which we can interact with the database.
SQL is classified mainly into three categories.
1.DDL(Data Definition Language)
1. Create 2. Alter 3. Truncate 4. Drop
2. DML(Data Manipulation Language).
1. Select 2. Insert 3. Update 4. Delete
3. TCL(Transaction Control Language).
1. Commit 2.Rollback 3. Save point
4. DCL(Data Control Language)
DDL Command
The Data Definition Language (DDL) is used to create and destroy databases and database objects. These commands will primarily be used by database administrators during the setup and removal phases of a database project. Let's take a look at the structure and usage of four basic DDL commands:
Create:
This command is used to create database object such as –Tables, Views, Indexes, synonyms, sequences, stored procedures, Triggers, Functions, Packages and User-define data types.
Example:-
CREATE TABLE Employee(
empno number(4) constraint pk_empno primary key,
ename varchar2(50) constraint nn_ename not null,
salary number(10,2),
hire_date data,
gender char(1) constraint chk_gen check(gender in ('M', 'F', 'm', 'f')),
email varchar2(50) unique
);
Alter:
This command is used to modify the structure of the database object.
Example:- ALTER TABLE Employee ADD CONSTRAINT nn_hdata NOT NULL hire_date;
Drop:
This command is used to remove the objects from the database.
Example:- DROP TABLE Employee;
Truncate:
Using this command we can permanently remove the rows from a table. Here we cannot append any clauses the truncate statement.
Example:-TRUNCATE TABLE Employee;
DML Command
The Data Manipulation Language (DML) is used to retrieve, insert and modify database information. These commands will be used by all database users during the routine operation of the database. Let's take a brief look at the basic DML commands:
SELECT
It allows database users to retrieve the specific information they desire from an operational database.
Example
SELECT *FROM Employee
INSERT
Using this command we can append data into tables.
Example
INSERT INTO Employee(empno,ename,salary,hire_date,gender,e-mail)
VALUES(1234, 'JOHN', '8000', '18-AUG-80', 'M', 'john@gmail.com');
UPDATE
The UPDATE command can be used to modify information contained within a table, either in bulk or individually.
Example:- UPDATE Employee SET salary=1500;
UPDATE Employee SET salary=1500 WHERE empno=1235;
DELETE
This command is used to remove the data form the tables.
Example:-DELETE FROM Employee;
DELETE FROM Employee WHWRE empno =1236
Join Operation :-
The join operation defined for relational databases is often referred to as a natural join. In this type of join, two relations are connected by their common attributes. A SQL JOIN clause combines records from two or more tables in a database.[1] It creates a set that can be saved as a table or used as is. A JOIN is a means for combining fields from two tables by using values common to each. ANSI standard SQL specifies four types of JOINs: INNER, OUTER, LEFT, and RIGHT.
Inner join:-
An inner join is the most common join operation used in applications, and represents the default join-type. Inner join creates a new result table by combining column values of two tables (A and B) based upon the join-predicate. The result of the join can be defined as the outcome of first taking the Cartesian product (or cross-join) of all records in the tables (combining every record in table A with every record in table B) - then return all records which satisfy the join predicate.
Example SQL statement
SELECT *FROM employee
INNER JOIN department
ON employee.DepartmentID = department.DepartmentID
Equi-join:-
An equi-join, also known as an equijoin, is a specific type of comparator-based join, or theta join, that uses only equality comparisons in the join-predicate. Using other comparison operators (such as <) disqualifies a join as an equi-join. The query shown above has already provided an example of an equi-join: SELECT *FROM employee INNER JOIN department ON employee.DepartmentID = department.DepartmentID
Natural join:-
A natural join offers a further specialization of equi-joins. The join predicate arises implicitly by comparing all columns in both tables that have the same column-name in the joined tables. The resulting joined table contains only one column for each pair of equally-named columns. The above sample query for inner joins can be expressed as a natural join in the following way:- SELECT *FROM employee NATURAL JOIN department
Cross join:- A cross join, cartesian join or product provides the foundation upon which all types of inner joins operate. A cross join returns the cartesian product of the sets of records from the two joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or where the join-condition is absent from the statement. In other words, a cross join combines every row in B with every row in A. The number of rows in the result set will be the number of rows in A times the number of rows in B.Thus, if A and B are two sets, then the cross join is written as A × B. The SQL code for a cross join lists the tables for joining (FROM), but does not include any filtering join-predicate. Example of an explicit cross join: SELECT * FROM employee CROSS JOIN department Example of an implicit cross join: SELECT *FROM employee, department;
Outer join:- An outer join does not require each record in the two joined tables to have a matching record. The joined table retains each record—even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table(s) one retains the rows from (left, right, or both). No implicit join-notation for outer joins exists in standard SQL. Left outer join:- The result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result—but with NULL in each column from B. This means that a left outer join returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate). If the left table returns one row and the right table returns more than one matching row for it, the values in the left table will be repeated for each distinct row on the right table. For example, this allows us to find an employee's department, but still shows the employee(s) even when they have not been assigned to a department (contrary to the inner-join example above, where unassigned employees are excluded from the result). Example of a left outer join, with the additional result row italicized: SELECT * FROM employee LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID Right outer joins A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the "right" table (B) will appear in the joined table at least once. If no matching row from the "left" table (A) exists, NULL will appear in columns from A for those records that have no match in B. A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate). For example, this allows us to find each employee and his or her department, but still show departments that have no employees. Example right outer join, with the additional result row italicized: SELECT * FROM employee RIGHT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID
Full outer join
A full outer join combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side. For example, this allows us to see each employee who is in a department and each department that has an employee, but also see each employee who is not part of a department and each department which doesn't have an employee. Example full outer join: SELECT * FROM employee FULL OUTER JOIN department ON employee.DepartmentID = department.DepartmentID UNION SELECT *FROM employee RIGHT JOIN department ON employee.DepartmentID = department.DepartmentID WHERE employee.DepartmentID IS NULL SQLite does not support right join, so outer join can be emulated as follows: SELECT employee.*, department.*FROM employee LEFT JOIN department ON employee.DepartmentID = department.DepartmentID UNION ALL SELECT employee.*, department.* FROM department LEFT JOIN employee ON employee.DepartmentID = department.DepartmentID WHERE employee.DepartmentID IS NULL Self-join A self-join is joining a table to itself.This is best illustrated by the following example. Example A query to find all pairings of two employees in the same country is desired. If you had two separate tables for employees and a query which requested employees in the first table having the same country as employees in the second table, you could use a normal join operation to find the answer table. However, all the employee information is contained within a single large table An example solution query could be as follows: SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country FROM Employee F, Employee S WHERE F.Country = S.Country AND F.EmployeeID < S.EmployeeID ORDER BY F.EmployeeID, S.EmployeeID; Relational operations The union operator combines the tuples of two relations and removes all duplicate tuples from the result. The relational union operator is equivalent to the SQL UNION operator. The intersection operator produces the set of tuples that two relations share in common. Intersection is implemented in SQL in the form of the INTERSECT operator. The difference operator acts on two relations and produces the set of tuples from the first relation that do not exist in the second relation. Difference is implemented in SQL in the form of the EXCEPT or MINUS operator. The cartesian product of two relations is a join that is not restricted by any criteria, resulting in every tuples of the first relation being matched with every tuple of the second relation. The cartesian product is implemented in SQL as the CROSS JOIN join operator.
GROUP BY Clause :-
The GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns.
The syntax for the GROUP BY clause is:
SELECT column1, column2, ... column_n, aggregate_function (expression) FROM tables WHERE predicates
GROUP BY column1, column2, ... column_n;
aggregate_function can be a function such as SUM, COUNT, MIN, or MAX.
Example using the SUM function
For example, you could also use the SUM function to return the name of the department and the total sales (in the associated department).
SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department;Because you have listed one column in your SELECT statement that is not encapsulated in the SUM function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.
Example using the COUNT function
For example, you could use the COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year.
SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;
Example using the MIN function
For example, you could also use the MIN function to return the name of each department and the minimum salary in the department.
SELECT department, MIN(salary) as "Lowest salary"
FROM employees
GROUP BY department;
Example using the MAX function
For example, you could also use the MAX function to return the name of each department and the maximum salary in the department.
SELECT department, MAX(salary) as "Highest salary"
FROM employees
GROUP BY department;
HAVING Clause:-The HAVING clause is used in combination with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns.
The syntax for the HAVING clause is:
SELECT column1, column2, ... column_n, aggregate_function (expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n
HAVING condition1 ... condition_n; aggregate_function can be a function such as SUM, COUNT, MIN, or MAX.
Example using the SUM function
For example, you could also use the SUM function to return the name of the department and the total sales (in the associated department). The HAVING clause will filter the results so that only departments with sales greater than $1000 will be returned.
SELECT department, SUM(sales) as "Total sales"
FROM order_details
GROUP BY department
HAVING SUM(sales) > 1000;
Example using the COUNT function
For example, you could use the COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year. The HAVING clause will filter the results so that only departments with more than 10 employees will be returned.
SELECT department, COUNT(*) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department
HAVING COUNT(*) > 10;
Example using the MIN function
For example, you could also use the MIN function to return the name of each department and the minimum salary in the department. The HAVING clause will return only those departments where the starting salary is $35,000.
SELECT department, MIN(salary) as "Lowest salary"
FROM employees
GROUP BY department
HAVING MIN(salary) = 35000;
Example using the MAX function
For example, you could also use the MAX function to return the name of each department and the maximum salary in the department. The HAVING clause will return only those departments whose maximum salary is less than $50,000.
SELECT department, MAX(salary) as "Highest salary"
FROM employees
GROUP BY department
HAVING MAX(salary) < 50000;
No comments:
Post a Comment