Tuesday, July 17, 2012

SQL Teaching Session.


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,
....
)

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:
INSERT INTO table_name (column1, column2, column3,...)
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
and
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


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 ;


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 ;


SELECT * FROM employee RIGHT  JOIN department ON employee.DepartmentID = department.DepartmentID;

The result will be



The Replace function in SQL is used to update the content of a string. The function call is REPLACE() for MySQL, Oracle, and SQL Server. The syntax of the Replace function is:
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;



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