Hi, this is
a simple sql teaching session.
SQL CREATE TABLE Syntax
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
)
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
)
First of
all we are going to create two tables namely department and employee.
CREATE TABLE department
(
DepartmentID INT,
DepartmentName VARCHAR(20)
);
CREATE TABLE employee
(
LastName VARCHAR(20),
DepartmentID INT
);
SQL INSERT INTO Syntax
It is possible to write the INSERT INTO statement in two forms.The first form doesn't specify the column names where the data will be inserted, only their values:
INSERT INTO table_name
VALUES (value1, value2, value3,...)
The second form specifies both the column names and the values to be
inserted:VALUES (value1, value2, value3,...)
INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)
VALUES (value1, value2, value3,...)
Now we are going to insert values to these tables by the following queries.
INSERT INTO department(DepartmentID, DepartmentName) VALUES(31, 'Sales');
INSERT INTO department(DepartmentID, DepartmentName) VALUES(33, 'Engineering');
INSERT INTO department(DepartmentID, DepartmentName) VALUES(34, 'Clerical');
INSERT INTO department(DepartmentID, DepartmentName) VALUES(35, 'Marketing');
INSERT INTO employee(LastName, DepartmentID) VALUES('Rafferty', 31);
INSERT INTO employee(LastName, DepartmentID) VALUES('Jones', 33);
INSERT INTO employee(LastName, DepartmentID) VALUES('Steinberg', 33);
INSERT INTO employee(LastName, DepartmentID) VALUES('Robinson', 34);
INSERT INTO employee(LastName, DepartmentID) VALUES('Smith', 34);
INSERT INTO
employee(LastName,
DepartmentID) VALUES('John', NULL);
The SQL SELECT Statement
The SELECT statement is used to select data from a database.The result is stored in a result table, called the result-set.
SQL SELECT Syntax
SELECT column_name(s)
FROM table_name
andFROM table_name
SELECT * FROM table_name
SELECT * FROM employee
The result will be like this.
SELECT * FROM department
Here the result is
SQL INNER JOIN Keyword
The INNER JOIN keyword
return rows when there is at least one match in both tables.
SQL INNER JOIN Syntax
SELECT
column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
SELECT * FROM employee INNER JOIN department ON
employee.DepartmentID =
department.DepartmentID;
The result is
Sql Full Outer Join : Syntax
Select *
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name ;
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name ;
SELECT e.*,d.* FROM employee e full outer JOIN department d ON
e.DepartmentID =
d.DepartmentID
The result will be like.
SELECT e.*,d.* FROM employee e full outer JOIN department
d ON e.DepartmentID
= d.DepartmentID
where d.DepartmentID is null and e.DepartmentID is null;
The result will be like
Syntax : SQL Right Join
Select *
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name ;
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name ;
SELECT * FROM employee RIGHT JOIN
department ON employee.DepartmentID
= department.DepartmentID;
The result will be
Replace(str1, str2, str3): In str1, find where str2 occurs, and replace it with str3.
SELECT e1.DepartmentID,
DepartmentID=replace((SELECT LastName
AS
[data()]
FROM
employee e2 WHERE e2.DepartmentID=e1.DepartmentID ORDER BY DepartmentID
FOR XML PATH('')),' ',',')FROM employee e1 GROUP
BY DepartmentID
The result will be like
Sql cross join
Description
The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product.If, WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN.
An alternative way of achieving the same result is to use column names separated by commas after SELECT and mentioning the table names involved, after a FROM clause.
Syntax
Select *
FROM table1
CROSS JOIN table2;
FROM table1
CROSS JOIN table2;
SELECT * from employee cross join department
The result will be
CROSS APPLY
SELECT employee.*,
department.* FROM employee
CROSS APPLY
(
SELECT *
FROM department
--WHERE department.DepartmentID =
employee.DepartmentID
where
employee.LastName='Rafferty') department
The result will be like
No comments:
Post a Comment