CARS (cid, mid, cyear)

BUYERS (bid, bname, bcity, age)

MANUFACTURERS (maname, location)
MODELS (mid, maname, model)
SALESPEOPLE (sid, sname, years_employed)
TRANSACTIONS (bid, cid, sid, amount, month, day, year)

Print the sid of all salespeople who have sold both a Ford and a Toyota in 1997.

Please explain your first q again

only those salesamn who sold both ford & toyota or one of them
in 1997 and toyota & ford are model name or maname
any how i am replying both questions......

Ans Q#1
==================
selct distinct t.sid,s.sname
from transactions t,salespeople s,cars c,models m,MANUFACTURERS ma
where t.sid = s.sid and
t.cid = c.cid and
c.mid = m.mid
m.maname = ma.maname
and t.year = 1997
and mn.mname in ('Toyota','Ford')

Result------------------
SID SNAME CID CYEAR MID MODEL MANAME
-----------------------------------------------------------------------

Ans of Q#2
------------------------------
select t.sid,s.sname,to_char(sum(t.amount),'fm$999999.00') tot_amount
from transactions t, salespeople s
where t.sid = s.sid and
year = 1997
group by sid,year
having sum(t.amount)
in
(
select max(t_amount) max_amount from
(
select sum(amount) t_amount from transactions
where year = 1997
group by sid,year
)
)

result query 2
========================

sid sname tot_amounnt

--------------------------------
1234 Alexandar $98745

2. Print the sname and total sales amount of the salesperson who had the highest total sales (in
dollars) for 1997.

select s.sname, to_char(sum(t.amount), 'fm$999999.00')

from transactions t, salespeople s
where t.sid = s.sid
group by s.sname, t.year
having (t.year = 1997) and max(sum(t.amount));
SQL Comparison Keywords
There are other comparison keywords available in sql which are used to enhance the search
capabilities of a sql query. They are "IN", "BETWEEN...AND", "IS NULL", "LIKE".

Comparision
Description
Operators
column value is similar to specified
LIKE
character(s).
column value is equal to any one of
IN
a specified set of values.
column value is between two
BETWEEN...AND values, including the end values
specified in the range.
IS NULL column value does not exist.

SQL LIKE Operator

The LIKE operator is used to list all rows in a table whose column values match a specified
pattern. It is useful when you want to search rows to match a specific pattern, or when you do not
know the entire value. For this purpose we use a wildcard character '%'.

For example: To select all the students whose name begins with 'S'

SELECT first_name, last_name

FROM student_details
WHERE first_name LIKE 'S%';

The output would be similar to:

first_name last_name
------------- -------------
Stephen Fleming
Shekar Gowda

The above select statement searches for all the rows where the first letter of the column
first_name is 'S' and rest of the letters in the name can be any character.

There is another wildcard character you can use with LIKE operator. It is the underscore
character, ' _ ' . In a search string, the underscore signifies a single character.

For example: to display all the names with 'a' second character,
SELECT first_name, last_name
FROM student_details
WHERE first_name LIKE '_a%';

The output would be similar to:

first_name last_name
------------- -------------
Rahul Sharma

NOTE:Each underscore act as a placeholder for only one character. So you can use more than
one underscore. Eg: ' __i% '-this has two underscores towards the left, 'S__j%' - this has two
underscores between character 'S' and 'i'.

SQL BETWEEN ... AND Operator

The operator BETWEEN and AND, are used to compare data for a range of values.

For Example: to find the names of the students between age 10 to 15 years, the query would be
like,

SELECT first_name, last_name, age

FROM student_details
WHERE age BETWEEN 10 AND 15;

The output would be similar to:

first_name last_name age

------------- ------------- ------
Rahul Sharma 10
Anajali Bhagwat 12
Shekar Gowda 15

SQL IN Operator:
The IN operator is used when you want to compare a column with more than one value. It is
similar to an OR condition.

For example: If you want to find the names of students who are studying either Maths or
Science, the query would be like,

SELECT first_name, last_name, subject

FROM student_details
WHERE subject IN ('Maths', 'Science');

The output would be similar to:

 first_nam last_nam
subject
e e
------------
------------- ----------
-
Anajali Bhagwat Maths
Shekar Gowda Maths
Rahul Sharma Science
Stephen Fleming Science

You can include more subjects in the list like ('maths','science','history')

NOTE:The data used to compare is case sensitive.

SQL IS NULL Operator

A column value is NULL if it does not exist. The IS NULL operator is used to display all the
rows for columns that do not have a value.

For Example: If you want to find the names of students who do not participate in any games,
the query would be as given below

SELECT first_name, last_name

FROM student_details
WHERE games IS NULL

There would be no output as we have every student participate in a game in the table
student_details, else the names of the students who do not participate in any games would be
displayed.
SQL ORDER BY
The ORDER BY clause is used in a SELECT statement to sort results either in ascending or
descending order. Oracle sorts query results in ascending order by default.

Syntax for using SQL ORDER BY clause to sort data is:

SELECT column-list
FROM table_name [WHERE condition]
[ORDER BY column1 [, column2, .. columnN] [DESC]];

database table "employee";

id name dept age salary location

100 Ramesh Electrical 24 25000 Bangalore

101 Hrithik Electronics 28 35000 Bangalore

102 Harsha Aeronautics 28 35000 Mysore

103 Soumya Electronics 22 20000 Bangalore

104 Priya InfoTech 25 30000 Mangalore

For Example: If you want to sort the employee table by salary of the employee, the sql query
would be.

SELECT name, salary FROM employee ORDER BY salary;

The output would be like

name salary

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

Soumya 20000

Ramesh 25000

Priya 30000

Hrithik 35000

Harsha 35000
The query first sorts the result according to name and then displays it.

You can also use more than one column in the ORDER BY clause.

If you want to sort the employee table by the name and salary, the query would be like,

SELECT name, salary FROM employee ORDER BY name, salary;

The output would be like:

name salary

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

Soumya 20000

Ramesh 25000

Priya 30000

Harsha 35000

Hrithik 35000

NOTE:The columns specified in ORDER BY clause should be one of the columns selected in
the SELECT column list.

You can represent the columns in the ORDER BY clause by specifying the position of a column
in the SELECT list, instead of writing the column name.

The above query can also be written as given below,

SELECT name, salary FROM employee ORDER BY 1, 2;

By default, the ORDER BY Clause sorts data in ascending order. If you want to sort the data in
descending order, you must explicitly specify it as shown below.

SELECT name, salary

FROM employee
ORDER BY name, salary DESC;

The above query sorts only the column 'salary' in descending order and the column 'name' by
ascending order.

If you want to select both name and salary in descending order, the query would be as given
below.
SELECT name, salary
FROM employee
ORDER BY name DESC, salary DESC;

How to use expressions in the ORDER BY Clause?

Expressions in the ORDER BY clause of a SELECT statement.

For example: If you want to display employee name, current salary, and a 20% increase in the
salary for only those employees for whom the percentage increase in salary is greater than 30000
and in descending order of the increased price, the SELECT statement can be written as shown
below

SELECT name, salary, salary*1.2 AS new_salary

FROM employee
WHERE salary*1.2 > 30000
ORDER BY new_salary DESC;

The output for the above query is as follows.

name salary new_salary

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

Hrithik 35000 37000

Harsha 35000 37000

Priya 30000 36000

NOTE:Aliases defined in the SELECT Statement can be used in ORDER BY Clause.

SQL GROUP Functions

Group functions are built-in SQL functions that operate on groups of rows and return one value
for the entire group. These functions are: COUNT, MAX, MIN, AVG, SUM, DISTINCT

SQL COUNT (): This function returns the number of rows in the table that satisfies the
condition specified in the WHERE condition. If the WHERE condition is not specified, then the
query returns the total number of rows in the table.

For Example: If you want the number of employees in a particular department, the query would
be:

SELECT COUNT (*) FROM employee

WHERE dept = 'Electronics';

The output would be '2' rows.

If you want the total number of employees in all the department, the query would take the form:

SELECT COUNT (*) FROM employee;

The output would be '5' rows.

SQL DISTINCT(): This function is used to select the distinct rows.

For Example: If you want to select all distinct department names from employee table, the
query would be:

SELECT DISTINCT dept FROM employee;

To get the count of employees with unique name, the query would be:

SELECT COUNT (DISTINCT name) FROM employee;

SQL MAX(): This function is used to get the maximum value from a column.

To get the maximum salary drawn by an employee, the query would be:

SELECT MAX (salary) FROM employee;

SQL MIN(): This function is used to get the minimum value from a column.

To get the minimum salary drawn by an employee, he query would be:

SELECT MIN (salary) FROM employee;

SQL AVG(): This function is used to get the average value of a numeric column.

To get the average salary, the query would be

SELECT AVG (salary) FROM employee;

SQL SUM(): This function is used to get the sum of a numeric column

To get the total salary given out to the employees,

SELECT SUM (salary) FROM employee;

© 2007-09 Beginner-Sql-Tutorial.com | Privacy Policy - Term of Use | All rights Reserved

SQL GROUP BY Clause

The SQL GROUP BY Clause is used along with the group functions to retrieve data grouped
according to one or more columns.

For Example: If you want to know the total amount of salary spent on each department, the
query would be:

SELECT dept, SUM (salary)

FROM employee
GROUP BY dept;

The output would be like:

dept salary
---------------
--------------
-
Electrical 25000
Electronics 55000
Aeronautics 35000
InfoTech 30000

NOTE: The group by clause should contain all the columns in the select list expect those used
along with the group functions.

SELECT location, dept, SUM (salary)

FROM employee
GROUP BY location, dept;

The output would be like:

location dept salary

--------------
------------- -----------
-
Bangalore Electrical 25000
Bangalore Electronics 55000
Mysore Aeronautics 35000
Mangalore InfoTech 30000

SQL HAVING Clause

Having clause is used to filter data based on the group functions. This is similar to WHERE
condition but is used with group functions. Group functions cannot be used in WHERE Clause
but can be used in HAVING clause.

For Example: If you want to select the department that has total salary paid for its employees
more than 25000, the sql query would be like;

SELECT dept, SUM (salary)

FROM employee
GROUP BY dept
HAVING SUM (salary) > 25000

The output would be like:

dept salary
------------- -------------
Electronics 55000
Aeronautics 35000
InfoTech 30000

When WHERE, GROUP BY and HAVING clauses are used together in a SELECT statement,
the WHERE clause is processed first, then the rows that are returned after the WHERE clause is
executed are grouped based on the GROUP BY clause. Finally, any conditions on the group
functions in the HAVING clause are applied to the grouped rows before the final output is
displayed.

Syntax to add a column

ALTER TABLE table_name ADD column_name datatype;

For Example: To add a column "experience" to the employee table, the query would be like

ALTER TABLE employee ADD experience number(3);

Syntax to drop a column

ALTER TABLE table_name DROP column_name;

For Example: To drop the column "location" from the employee table, the query would be like

ALTER TABLE employee DROP location;

Syntax to modify a column

ALTER TABLE table_name MODIFY column_name datatype;

For Example: To modify the column salary in the employee table, the query would be like

ALTER TABLE employee MODIFY salary number(15,2);

SQL RENAME Command

The SQL RENAME command is used to change the name of the table or a database object.

If you change the object's name any reference to the old name will be affected. You have to
manually change the old name to the new name in every reference.

Syntax to rename a table

RENAME old_table_name To new_table_name;

For Example: To change the name of the table employee to my_employee, the query would be
like

RENAME employee TO my_emloyee;

L Integrity Constraints
Integrity Constraints are used to apply business rules for the database tables.

The constraints available in SQL are Foreign Key, Not Null, Unique, Check.

Constraints can be defined in two ways

1) The constraints can be specified immediately after the column definition. This is called
column-level definition.
2) The constraints can be specified after all the columns are defined. This is called table-level
definition.

1) SQL Primary key:

This constraint defines a column or combination of columns which uniquely identifies each row
in the table.

Syntax to define a Primary key at column level:

column name datatype [CONSTRAINT constraint_name] PRIMARY KEY

Syntax to define a Primary key at table level:

[CONSTRAINT constraint_name] PRIMARY KEY (column_name1,column_name2,..)

 column_name1, column_name2 are the names of the columns which define the primary
Key.
 The syntax within the bracket i.e. [CONSTRAINT constraint_name] is optional.

For Example: To create an employee table with Primary Key constraint, the query would be
like.

Primary Key at table level:

CREATE TABLE employee

( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10)
);

or

CREATE TABLE employee

( id number(5) CONSTRAINT emp_id_pk PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10)
);

Primary Key at table level:

CREATE TABLE employee

( id number(5),
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10),
CONSTRAINT emp_id_pk PRIMARY KEY (id)
);

2) SQL Foreign key or Referential Integrity :

This constraint identifies any column referencing the PRIMARY KEY in another table. It
establishes a relationship between two columns in the same table or between different tables. For
a column to be defined as a Foreign Key, it should be a defined as a Primary Key in the table
which it is referring. One or more columns can be defined as Foreign key.

Syntax to define a Foreign key at column level:

[CONSTRAINT constraint_name] REFERENCES Referenced_Table_name(column_name)

Syntax to define a Foreign key at table level:

[CONSTRAINT constraint_name] FOREIGN KEY(column_name) REFERENCES

referenced_table_name(column_name);

For Example:

1) Lets use the "product" table and "order_items".

Foreign Key at column level:

CREATE TABLE product

( product_id number(5) CONSTRAINT pd_id_pk PRIMARY KEY,
product_name char(20),
supplier_name char(20),
unit_price number(10)
);

CREATE TABLE order_items

( order_id number(5) CONSTRAINT od_id_pk PRIMARY KEY,
product_id number(5) CONSTRAINT pd_id_fk REFERENCES, product(product_id),
product_name char(20),
supplier_name char(20),
unit_price number(10)
);

Foreign Key at table level:

CREATE TABLE order_items

( order_id number(5) ,
product_id number(5),
product_name char(20),
supplier_name char(20),
unit_price number(10)
CONSTRAINT od_id_pk PRIMARY KEY(order_id),
CONSTRAINT pd_id_fk FOREIGN KEY(product_id) REFERENCES product(product_id)
);

2) If the employee table has a 'mgr_id' i.e, manager id as a foreign key which references primary
key 'id' within the same table, the query would be like,

CREATE TABLE employee

( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
mgr_id number(5) REFERENCES employee(id),
salary number(10),
location char(10)
);

3) SQL Not Null Constraint :

This constraint ensures all rows in the table contain a definite value for the column which is
specified as not null. Which means a null value is not allowed.

Syntax to define a Not Null constraint:

[CONSTRAINT constraint name] NOT NULL

For Example: To create a employee table with Null value, the query would be like

CREATE TABLE employee

( id number(5),
name char(20) CONSTRAINT nm_nn NOT NULL,
dept char(10),
age number(2),
salary number(10),
location char(10)
);

4) SQL Unique Key:

This constraint ensures that a column or a group of columns in each row have a distinct value. A
column(s) can have a null value but the values cannot be duplicated.

Syntax to define a Unique key at column level:

[CONSTRAINT constraint_name] UNIQUE

Syntax to define a Unique key at table level:

[CONSTRAINT constraint_name] UNIQUE(column_name)

For Example: To create an employee table with Unique key, the query would be like,

Unique Key at column level:

CREATE TABLE employee

( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10) UNIQUE
);

or

CREATE TABLE employee

( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10) CONSTRAINT loc_un UNIQUE
);

Unique Key at table level:

CREATE TABLE employee

( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10),
CONSTRAINT loc_un UNIQUE(location)
);

5) SQL Check Constraint :

This constraint defines a business rule on a column. All the rows must satisfy this rule. The
constraint can be applied for a single column or a group of columns.
Syntax to define a Check constraint:

[CONSTRAINT constraint_name] CHECK (condition)

For Example: In the employee table to select the gender of a person, the query would be like

Check Constraint at column level:

CREATE TABLE employee

( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
gender char(1) CHECK (gender in ('M','F')),
salary number(10),
location char(10)
);

Check Constraint at table level:

CREATE TABLE employee

( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
gender char(1),
salary number(10),
location char(10),
CONSTRAINT gender_ck CHECK (gender in ('M','F'))
);
SQL Joins
SQL Joins are used to relate information in different tables. A Join condition is a part of the sql
query that retrieves rows from two or more tables. A SQL Join condition is used in the SQL
WHERE Clause of select, update, delete statements.

The Syntax for joining two tables is:

SELECT col1, col2, col3...

FROM table_name1, table_name2
WHERE table_name1.col2 = table_name2.col1;

If a sql join condition is omitted or if it is invalid the join operation will result in a Cartesian
product. The Cartesian product returns a number of rows equal to the product of all rows in all
the tables being joined. For example, if the first table has 20 rows and the second table has 10
rows, the result will be 20 * 10, or 200 rows. This query takes a long time to execute.

Lets use the below two tables to explain the sql join conditions.

database table "product";

product_id product_name supplier_name unit_price

100 Camera Nikon 300
101 Television Onida 100
102 Refrigerator Vediocon 150
103 Ipod Apple 75
104 Mobile Nokia 50

database table "order_items";

order_id product_id total_units customer

5100 104 30 Infosys
5101 102 5 Satyam
5102 103 25 Wipro
5103 101 10 TCS

SQL Joins can be classified into Equi join and Non Equi join.

1) SQL Equi joins

It is a simple sql join condition which uses the equal sign as the comparison operator. Two types
of equi joins are SQL Outer join and SQL Inner join.
For example: You can get the information about a customer who purchased a product and the
quantity of product.

2) SQL Non equi joins

It is a sql join condition which makes use of some comparison operator other than the equal sign
like >, <, >=, <=

DIFF SITE W3schools.com

SQL Advanced

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

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

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 return 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 LEFT JOIN Keyword

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no
matches in the right table (table_name2).

SQL LEFT JOIN Syntax

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

PS: In some databases LEFT JOIN is called LEFT OUTER JOIN.

SQL LEFT 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 and their orders - if any, from the tables above.

We use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo

FROM Persons
LEFT 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

Svendson Tove  

The LEFT JOIN keyword returns all the rows from the left table (Persons), even if there are no
matches in the right table (Orders).

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword Return all rows from the right table (table_name2), even if there are
no matches in the left table (table_name1).

SQL RIGHT JOIN Syntax

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

PS: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

SQL RIGHT 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 orders with containing persons - if any, from the tables above.

We use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo

FROM Persons
RIGHT 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

    34764

The RIGHT JOIN keyword returns all the rows from the right table (Orders), even if there are no
matches in the left table (Persons).

SQL FULL JOIN Keyword

The FULL JOIN keyword return rows when there is a match in one of the tables.

SQL FULL JOIN Syntax

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

SQL FULL 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 and their orders, and all the orders with their persons.

We use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo

FROM Persons
FULL 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

Svendson Tove  

    34764

The FULL JOIN keyword returns all the rows from the left table (Persons), and all the rows from
the right table (Orders). If there are rows in "Persons" that do not have matches in "Orders", or if
there are rows in "Orders" that do not have matches in "Persons", those rows will be listed as
well.

The SQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements.

Notice that each SELECT statement within the UNION must have the same number of columns.
The columns must also have similar data types. Also, the columns in each SELECT statement
must be in the same order.

SQL UNION Syntax

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

Note: The UNION operator selects only distinct values by default. To allow duplicate values,
use UNION ALL.

SQL UNION ALL Syntax

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

PS: The column names in the result-set of a UNION are always equal to the column names in the
first SELECT statement in the UNION.

SQL UNION Example

Look at the following tables:

"Employees_Norway":

E_ID E_Name

01 Hansen, Ola

02 Svendson, Tove

03 Svendson, Stephen

04 Pettersen, Kari

"Employees_USA":

E_ID E_Name
01 Turner, Sally

02 Kent, Clark

03 Svendson, Stephen

04 Scott, Stephen

Now we want to list all the different employees in Norway and USA.

We use the following SELECT statement:

SELECT E_Name FROM Employees_Norway

UNION
SELECT E_Name FROM Employees_USA

The result-set will look like this:

E_Name

Hansen, Ola

Svendson, Tove

Svendson, Stephen

Pettersen, Kari

Turner, Sally

Kent, Clark

Scott, Stephen

Note: This command cannot be used to list all employees in Norway and USA. In the example
above we have two employees with equal names, and only one of them will be listed. The
UNION command selects only distinct values.

SQL UNION ALL Example

Now we want to list all employees in Norway and USA:

SELECT E_Name FROM Employees_Norway

UNION ALL
SELECT E_Name FROM Employees_USA

Result

E_Name

Hansen, Ola

Svendson, Tove

Svendson, Stephen

Pettersen, Kari

Turner, Sally

Kent, Clark

Svendson, Stephen

Scott, Stephen

The SQL SELECT INTO Statement

The SELECT INTO statement selects data from one table and inserts it into a different table.

The SELECT INTO statement is most often used to create backup copies of tables.

SQL SELECT INTO Syntax

We can select all columns into the new table:

SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_tablename

Or we can select only the columns we want into the new table:

SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_tablename
SQL SELECT INTO Example
Make a Backup Copy - Now we want to make an exact copy of the data in our "Persons" table.

We use the following SQL statement:

SELECT *
INTO Persons_Backup
FROM Persons

We can also use the IN clause to copy the table into another database:

SELECT *
INTO Persons_Backup IN 'Backup.mdb'
FROM Persons

We can also copy only a few fields into the new table:

SELECT LastName,FirstName
INTO Persons_Backup
FROM Persons

SQL SELECT INTO - With a WHERE Clause

We can also add a WHERE clause.

The following SQL statement creates a "Persons_Backup" table with only the persons who lives
in the city "Sandnes":

SELECT LastName,Firstname
INTO Persons_Backup
FROM Persons
WHERE City='Sandnes'
SQL SELECT INTO - Joined Tables
Selecting data from more than one table is also possible.

The following example creates a "Persons_Order_Backup" table contains data from the two
tables "Persons" and "Orders":

SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id

The SQL SELECT INTO Statement

The SELECT INTO statement selects data from one table and inserts it into a different table.

The SELECT INTO statement is most often used to create backup copies of tables.

SQL SELECT INTO Syntax

We can select all columns into the new table:

SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_tablename

Or we can select only the columns we want into the new table:

SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_tablename

SQL SELECT INTO Example

Make a Backup Copy - Now we want to make an exact copy of the data in our "Persons" table.

We use the following SQL statement:

SELECT *
INTO Persons_Backup
FROM Persons

We can also use the IN clause to copy the table into another database:

SELECT *
INTO Persons_Backup IN 'Backup.mdb'
FROM Persons

We can also copy only a few fields into the new table:

SELECT LastName,FirstName
INTO Persons_Backup
FROM Persons

SQL SELECT INTO - With a WHERE Clause

We can also add a WHERE clause.

The following SQL statement creates a "Persons_Backup" table with only the persons who lives
in the city "Sandnes":

SELECT LastName,Firstname
INTO Persons_Backup
FROM Persons
WHERE City='Sandnes'

SQL SELECT INTO - Joined Tables

Selecting data from more than one table is also possible.

The following example creates a "Persons_Order_Backup" table contains data from the two
tables "Persons" and "Orders":

SELECT Persons.LastName,Orders.OrderNo
INTO Persons_Order_Backup
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id

The CREATE DATABASE Statement

The CREATE DATABASE statement is used to create a database.

SQL CREATE DATABASE Syntax

CREATE DATABASE database_name

CREATE DATABASE Example

Now we want to create a database called "my_db".

We use the following CREATE DATABASE statement:

CREATE DATABASE my_db

SQL ALTER TABLE Statement

« Previous Next Chapter »

The ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

SQL ALTER TABLE Syntax

To add a column in a table, use the following syntax:

ALTER TABLE table_name

ADD column_name datatype
To delete a column in a table, use the following syntax (notice that some database systems don't
allow deleting a column):

ALTER TABLE table_name

DROP COLUMN column_name

To change the data type of a column in a table, use the following syntax:

ALTER TABLE table_name

ALTER COLUMN column_name datatype

SQL ALTER TABLE Example

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

Now we want to add a column named "DateOfBirth" in the "Persons" table.

We use the following SQL statement:

ALTER TABLE Persons

ADD DateOfBirth date

Notice that the new column, "DateOfBirth", is of type date and is going to hold a date. The data
type specifies what type of data the column can hold. For a complete reference of all the data
types available in MS Access, MySQL, and SQL Server, go to our complete Data Types
reference.

The "Persons" table will now like this:

P_Id LastName FirstName Address City DateOfBirth

1 Hansen Ola Timoteivn 10 Sandnes  

2 Svendson Tove Borgvn 23 Sandnes  

3 Pettersen Kari Storgt 20 Stavanger  

Change Data Type Example

Now we want to change the data type of the column named "DateOfBirth" in the "Persons" table.

We use the following SQL statement:

ALTER TABLE Persons

ALTER COLUMN DateOfBirth year

Notice that the "DateOfBirth" column is now of type year and is going to hold a year in a two-
digit or four-digit format.

DROP COLUMN Example

Next, we want to delete the column named "DateOfBirth" in the "Persons" table.

We use the following SQL statement:

ALTER TABLE Persons

DROP COLUMN DateOfBirth

The "Persons" table will now like this:

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 CREATE INDEX statement is used to create indexes in tables.

Indexes allow the database application to find data fast; without reading the whole table.

Indexes
An index can be created in a table to find data more quickly and efficiently.

The users cannot see the indexes, they are just used to speed up searches/queries.

Note: Updating a table with indexes takes more time than updating a table without (because the
indexes also need an update). So you should only create indexes on columns (and tables) that
will be frequently searched against.

SQL CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name

ON table_name (column_name)

SQL CREATE UNIQUE INDEX Syntax

Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEX index_name

ON table_name (column_name)

Note: The syntax for creating indexes varies amongst different databases. Therefore: Check the
syntax for creating indexes in your database.

CREATE INDEX Example

The SQL statement below creates an index named "PIndex" on the "LastName" column in the
"Persons" table:

CREATE INDEX PIndex

ON Persons (LastName)
If you want to create an index on a combination of columns, you can list the column names
within the parentheses, separated by commas:

CREATE INDEX PIndex

ON Persons (LastName, FirstName)

The FORMAT() Function

The FORMAT() function is used to format how a field is to be displayed.

SQL FORMAT() Syntax

SELECT FORMAT(column_name,format) FROM table_name

Parameter Description

column_name Required. The field to be formatted.

format Required. Specifies the format.

SQL FORMAT() Example

We have the following "Products" table:

Prod_Id ProductName Unit UnitPrice

1 Jarlsberg 1000 g 10.45

2 Mascarpone 1000 g 32.56

3 Gorgonzola 1000 g 15.67

Now we want to display the products and prices per today's date (with today's date displayed in
the following format "YYYY-MM-DD").

We use the following SELECT statement:

SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate

FROM Products
The result-set will look like this:

ProductName UnitPrice PerDate

Jarlsberg 10.45 2008-10-07

Mascarpone 32.56 2008-10-07

Gorgonzola 15.67 2008-10-07

SQL Functions

SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

Useful aggregate functions:

  AVG() - Returns the average value
 COUNT() - Returns the number of rows
 FIRST() - Returns the first value
 LAST() - Returns the last value
 MAX() - Returns the largest value
 MIN() - Returns the smallest value
 SUM() - Returns the sum

SQL Scalar functions

SQL scalar functions return a single value, based on the input value.

Useful scalar functions:

 UCASE() - Converts a field to upper case

 LCASE() - Converts a field to lower case
 MID() - Extract characters from a text field
 LEN() - Returns the length of a text field
 ROUND() - Rounds a numeric field to the number of decimals specified
 NOW() - Returns the current system date and time
 FORMAT() - Formats how a field is to be displayed

Tip: The aggregate functions and the scalar functions will be explained in details in the next
chapters.

SQL Hosting
If you want your web site to be able to store and display data from a database, your web server
should have access to a database system that uses the SQL language.

If your web server will be hosted by an Internet Service Provider (ISP), you will have to look for
SQL hosting plans.

The most common SQL hosting databases are MySQL, MS SQL Server, and MS Access.

You can have SQL databases on both Windows and Linux/UNIX operating systems.
Below is an overview of which database system that runs on which OS.

MS SQL Server

Runs only on Windows OS.

MySQL

Runs on both Windows and Linux/UNIX operating systems.

MS Access (recommended only for small websites)

Runs only on Windows OS.

To learn more about web hosting, please visit our Hosting tutorial.

Aftr completin dis document tk dis test

http://www.w3schools.com/sql/sql_quiz.asp