SQL query to learn from basics
create database my_db //create database
create table persons
(
p_id int,
lastname varchar(255),
firstname varchar(255),
city varchar(255),
Country varchar(255)
) //create table
select * from persons //view contents
drop table persons //delete table
create table persons (
pid int not null primary key,
firstname varchar(255),
lastname varchar(255),
city varchar(255),
country varchar(255)
) //create table with primary key
create table orders (
oid int not null primary key,
ordername varchar(255),
pid int foreign key references persons(pid)
) //create table with foreign key
select * from persons //view contents
select * from orders //view contents
alter table orders add odate varchar(255) //addc column
select * from orders //view contents
alter table persons drop column country //delete column
select * from persons //view contents
alter table persons add pcount int //add collumn
select * from persons //view contents
alter table persons drop column pcount //delete column
select * from persons //view contents
alter table persons add pcount int identity //add auto increment
field to table here it is pcount
select * from persons // view contents
insert into persons values(1,'xyz','abc','pun')
insert into persons values(2,'xyz2','abc2','pun2')
insert into persons values(3,'xyz3','abc3','pun3')
insert into persons values(4,'xyz4','abc4','pun4')
insert into persons values(5,'xyz5','abc5','pun5') //insert data
select * from persons //view contents
insert into orders values(1,'xyz11',1,getdate())
insert into orders values(2,'xyz22',2,getdate())
insert into orders values(3,'xyz33',3,getdate())
insert into orders values(4,'xyz44',4,getdate())
insert into orders values(5,'xyz55',5,getdate()) //inset data
select * from orders //view contents
insert into persons values(7,'xyz6','abc6','pun6') //insert data
select * from persons //view contents
update persons set pid=6 where pid=7 //update data in table
select * from persons //view contents
delete from persons where pid=6 //delete row from table
select * from persons //view contents
insert into orders values(6,'xyz66',1,getdate())
insert into orders values(7,'xyz77',1,getdate())
insert into orders values(8,'xyz88',2,getdate())
insert into orders values(9,'xyz99',5,getdate())
insert into orders values(10,'xyz1010',5,getdate()) //insert data
select * from orders //view contents
select * from orders order by pid asc //order by asc-ascending and
desc-descending
select distinct pid from orders //shows distinct value in column pid
select * from persons where pid=5 and city='pun5' //using and in
where select statement
select * from persons where pid=5 or city='pun4' //using or in
where select statement
select * from persons where city like 'pun%' //to see data for
specific type (here city starting with pun)
insert into persons values(7,'abc7','xyz7','pun7')
insert into persons values(8,'abc8','xyz8','pun8')
insert into persons values(6,'abc6','xyz6','pun6') //insert data
select * from persons //view contents
SQL Wildcards
SQL wildcards can substitute for one or more characters when
searching for data in a database.
SQL wildcards must be used with the SQL LIKE operator.
With SQL, the following wildcards can be used:
Wildcard Description
% A substitute for zero or more characters
_ A substitute for exactly one character
[charlist] Any single character in charlist
[^charlist]or[!charlist] Any single character not in charlist
insert into persons values(10,'xyz10','abc10','pun10')
insert into persons values(20,'xyz20','abc20','pun20')
insert into persons values(21,'xyz21','abc21','pun21') //insert
values
select * from persons where pid like '2_' //to see pid with onle
starting 2
select * from persons where pid like '[12]%' //to see pid starting
with 1 0r 2 [] symbolizes it
select * from persons where pid like '[^12]%' //to see pid with
starting not equal to 1 or 2
select top 3 * from persons //to view top 3 roes of persons table(for
top n use n instead of 3)
select top 20 percent * from persons //to view to first top 20%
rows of the total rows in persons table
select * from persons where pid in (1,2,3) // to use or clause on a
column in a particular table
select * from persons where pid between 2 and 4 //to view data
between range for a particular column
select * from persons where pid not between 2 and 4 //to view data
from given table apart from the mentioned range
select p.pid from persons as p // to use short names when writing
complex queries(here p is used instead of persons)
select pid,pname,pssn from persons
union
select oid,oname,onumber from orders //unio is to view contents to
two tables at the same time (Note: the datatypes of cloumn mentioned in
both select queries should be same)
select * into new_persons from persons //into keyword is used to copy
contents of table person in to new table new_persons(Basically used to
take backup)
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.
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.
select * from persons inner join orders on persons.pid=orders.pid
//inner join
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).
select * from persons left join orders on persons.pid=orders.pid
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).
select * from persons right join orders on persons.pid=orders.pid
SQL FULL JOIN Keyword
The FULL JOIN keyword return rows when there is a match in one of the
tables.
select * from persons full join orders on persons.pid=orders.pid
create view peor as select pssn,pname from persons inner join orders on
persons.pid=orders.oid //create view using two tables and join query
drop view peor // delete view
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
select avg(pid)from orders //to get average of a column (Can onle use
it on int type)
select count(pid)from orders // gives total number of rows in tables
select count(distinct pid) from orders //to count distinct elements
in table
select max(pname) from persons // to get maximum value from column
select min(onumber) from orders // to get minimum value from column
select sum(oid) from orders //to add contents of column
select len(pname) from persons //to see length of a particular data
in a particular row and particular column
The GROUP BY Statement
The GROUP BY statement is used in conjunction with the aggregate
functions to group the result-set by one or more columns.
SQL GROUP BY Syntax
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
SQL GROUP BY Example
We have the following "Orders" table:
O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen
Now we want to find the total sum (total order) of each customer.
We will have to use the GROUP BY statement to group the customers.
We use the following SQL statement:
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
The result-set will look like this:
Customer SUM(OrderPrice)
Hansen 2000
Nilsen 1700
Jensen 2000
select pid,count(oname) from orders group by pid
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
SQL HAVING Example
We have the following "Orders" table:
O_Id OrderDate OrderPrice Customer
1 2008/11/12 1000 Hansen
2 2008/10/23 1600 Nilsen
3 2008/09/02 700 Hansen
4 2008/09/03 300 Hansen
5 2008/08/30 2000 Jensen
6 2008/10/04 100 Nilsen
Now we want to find if any of the customers have a total order of less
than 2000.
We use the following SQL statement:
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000
The result-set will look like this:
Customer SUM(OrderPrice)
Nilsen 1700
Now we want to find if the customers "Hansen" or "Jensen" have a total
order of more than 1500.
We add an ordinary WHERE clause to the SQL statement:
SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Hansen' OR Customer='Jensen'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500
The result-set will look like this:
Customer SUM(OrderPrice)
Hansen 2000
Jensen 2000
select pname,sum(pssn)as p from persons where pname='xyz2' group by
pname having count(pssn)>2
