Wednesday, October 24, 2012

Joins 22-10-12








The HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

SQL HAVING Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value


 SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000



SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Hansen' OR Customer='Jensen'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500


-----------------------------

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer


SQL CREATE VIEW Statement

In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

SQL CREATE VIEW Syntax

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

SQL CREATE VIEW Examples

If you have the Northwind database you can see that it has several views installed by default.
The view "Current Product List" lists all active products (products that are not discontinued) from the "Products" table. The view is created with the following SQL:
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No
We can query the view above as follows:
SELECT * FROM [Current Product List]
Another view in the Northwind sample database selects every product in the "Products" table with a unit price higher than the average unit price:
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)
We can query the view above as follows:
SELECT * FROM [Products Above Average Price]
Another view in the Northwind database calculates the total sale for each category in 1997. Note that this view selects its data from another view called "Product Sales for 1997":
CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName
We can query the view above as follows:
SELECT * FROM [Category Sales For 1997]
We can also add a condition to the query. Now we want to see the total sale only for the category "Beverages":
SELECT * FROM [Category Sales For 1997]
WHERE CategoryName='Beverages'

SQL Updating a View

You can update a view by using the following syntax:

SQL CREATE OR REPLACE VIEW Syntax

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Now we want to add the "Category" column to the "Current Product List" view. We will update the view with the following SQL:
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No

SQL Dropping a View

You can delete a view with the DROP VIEW command.

SQL DROP VIEW Syntax

DROP VIEW view_name
SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name




SQL INNER JOIN Keyword

The INNER JOIN keyword returns 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
PS: INNER JOIN is the same as JOIN.

SQL INNER JOIN Example

The "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
The "Orders" table:
O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15
Now we want to list all the persons with any orders.
We use the following SELECT statement:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName
The result-set will look like this:
LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678
The INNER JOIN keyword returns rows when there is at least one match in both tables. If there are rows in "Persons" that do not have matches in "Orders", those rows will NOT be listed.



SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.

SQL JOIN

The JOIN keyword is used in an SQL statement to query data from two or more tables, based on a relationship between certain columns in these tables.
Tables in a database are often related to each other with keys.
A primary key is a column (or a combination of columns) with a unique value for each row. Each primary key value must be unique within the table. The purpose is to bind data together, across tables, without repeating all of the data in every table.
Look at the "Persons" table:
P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger
Note that the "P_Id" column is the primary key in the "Persons" table. This means that no two rows can have the same P_Id. The P_Id distinguishes two persons even if they have the same name.
Next, we have the "Orders" table:
O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15
Note that the "O_Id" column is the primary key in the "Orders" table and that the "P_Id" column refers to the persons in the "Persons" table without using their names.
Notice that the relationship between the two tables above is the "P_Id" column.

Different SQL JOINs

Before we continue with examples, we will list the types of JOIN you can use, and the differences between them.
  • JOIN: Return rows when there is at least one match in both tables
  • LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
  • RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
  • FULL JOIN: Return rows when there is a match in one of the tables




With SQL, an alias name can be given to a table or to a column.

SQL Alias

You can give a table or a column another name by using an alias. This can be a good thing to do if you have very long or complex table names or column names.
An alias name could be anything, but usually it is short.

SQL Alias Syntax for Tables

SELECT column_name(s)
FROM table_name
AS alias_name

SQL Alias Syntax for Columns

SELECT column_name AS alias_name
FROM table_name

Alias Example

Assume we have a table called "Persons" and another table called "Product_Orders". We will give the table aliases of "p" and "po" respectively.
Now we want to list all the orders that "Ola Hansen" is responsible for.
We use the following SELECT statement:
SELECT po.OrderID, p.LastName, p.FirstName
FROM Persons AS p,
Product_Orders AS po
WHERE p.LastName='Hansen' AND p.FirstName='Ola'
The same SELECT statement without aliases:
SELECT Product_Orders.OrderID, Persons.LastName, Persons.FirstName
FROM Persons,
Product_Orders
WHERE Persons.LastName='Hansen' AND Persons.FirstName='Ola'
As you'll see from the two SELECT statements above; aliases can make queries easier both to write and to read.








Joins 22-10-12


create database coursecatalogs
create table student (reg_no int primary key, s_name varchar(20),
 course_enrolled varchar(20), Section varchar(20) )
select * from student
insert into student values (101, 'abc', 'diploma', 'jk001')
insert into student values (102, 'xyz', 'b.tech', 'rk201')
insert into student values (103, 'qrs', 'diploma', 'jk002')
insert into student values (104, 'twy', 'mba', 'pq601')
create table course (course_code varchar(20) primary key, course_name varchar(20),
 course_duration int, reg_no int foreign key references student (reg_no) )

insert into course values ('CSE200', 'mang DBMS', 5, 101)
insert into course values ('MG7400', 'finance mgmt', 4, 102)
insert into course values ('CSE306', 'com netwks', 3, 101)
insert into course values ('INT201','Graphic tools', 2, 103)
insert into course values ('INT204','web development', 3, null)
select * from course

 select student.reg_no, student.s_name, c.course_name from student cross join course c /* cross join */

select  s.s_name, s.Section, c.course_name from student s inner join course c
 on s.reg_no = c.reg_no where s.Section = 'jk001' or s.Section = 'jk001'

select  s.s_name, s.Section, c.course_name from student s inner join course c  on s.reg_no > c.reg_no

select  s.s_name, s.Section, c.course_name
from student s, course c  where s.reg_no = c.reg_no  /* older method equi join */

select  s.s_name, s.Section, c.course_name
from student s, course c  where s.reg_no > c.reg_no  /* older method non equi join */

select  s.s_name, s.Section, c.course_name from student s, course c /* cross join older method*/

select  s.s_name, s.Section, c.course_name
from student s full outer join course c  on s.reg_no = c.reg_no /* outer join */

create table teacher (teacher_id int primary key , teacher_name varchar(20), t_salary float,
 course_code varchar(20) foreign key references course (course_code) )

insert into teacher values ( 1300, 'aabc' ,10000 ,'CSE200')
insert into teacher values ( 1301, 'xyzq' ,15000 ,'CSE306')
insert into teacher values ( 1302, 'qrst' ,20000 ,NULL)
insert into teacher values ( 1304, 'xyzq' ,2000 ,'CSE306')
insert into teacher values ( 1305, 'qvwx' ,3000 ,null)

select * from teacher
-------------------------------------------

Execution Ques Odd

----------------------------------------



create table teacher (teacher_id int primary key , teacher_name varchar(20), t_salary float,
 course_code varchar(20) foreign key references course (course_code) )

insert into teacher values ( 1300, 'aabc' ,10000 ,'CSE200')
insert into teacher values ( 1301, 'xyzq' ,15000 ,'CSE306')
insert into teacher values ( 1302, 'qrst' ,20000 ,NULL)
insert into teacher values ( 1304, 'xyzq' ,2000 ,'CSE306')
insert into teacher values ( 1305, 'qvwx' ,3000 ,null)
select * from teacher

/* 1 */
select t.teacher_id, t.t_salary, c.course_name from teacher t cross join course c

/* 2 */
select 'Teacher '+t.teacher_name +' having salary '+ cast(t.t_salary as varchar)+ ' teaching course ' + c.course_name +  c.course_code from teacher t  inner join course c on t.course_code = c.course_code


/* 3 */




Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)