Transcript SELECT * FROM Customers WHERE

SQL STATEMENTS
What Is a Database?
 What is a database and database management system.
A database is a collection of related data.
A database management system (DBMS) is a collection of
programs that enables users to create and maintain a database.
 Why use a database management system?
To control redundancy.
To restrict unauthorized access.
To permit inference and actions using rules.
To provide multiple user interfaces.
To representing complex relationships among data.
To enforce integrity constraints.
To provide backup and recovery.
 The foundation of every Relational Database Management





System is a database object called table.
Every database consists of one or more tables, which store
the database’s data/information.
Each table has its own unique name and consists of columns
and rows.
The database table columns (called also table fields) have
their own unique names and have a pre-defined data types.
Table columns can have various attributes defining the
column functionality .
While table columns describe the data types, the table rows
contain the actual data for the columns.
Here is an example of a simple database
table:
 Table: Customers
FirstName
LastName
Email
DOB
Phone
John
Smith
[email protected] 2/4/1968
626 222-2222
Steven
Goldfish
[email protected]
4/4/1974
323 455-4545
Paula
Brown
[email protected]
5/24/1978
416 323-3232
James
Smith
[email protected]
20/10/1980 416 323-8888
Primary Key (PK)
 A primary key is a table column that can be used to
uniquely identify every row of the table.
 Any column that has this property will do -- these
columns are called candidate keys.
 A table can have many candidate keys but only one
primary key. The primary key cannot be null.
ForeignKey
 A foreign key is a column, or combination of columns, that
contain values that are found in the primary key of some
table (including, possibly, itself).
 A foreign key may be null, and almost always is not unique.
ClrPK
ColourName
10
yellow
20
AdjPK
AdjectiveName
ClrFK
904
angry
20
913
envious
30
937
lazy
-
941
lonely
40
954
fearful
10
979
jealous
30
991
furious
20
red
30
green
40
blue
 As you can see, the ClrFK column in the second table is a
foreign key to the ClrPK primary key in the first table.
 Notice that the ClrPK values are unique and not null, but the
ClrFK values may be null and often repeat.
 A null foreign key means that that particular row does not
participate in the relationship. The fact that many foreign key
values repeat simply reflects the fact that it's a one-to-many
relationship.
 In a one-to-many relationship, the primary key has the "one"
value, and the foreign key has the "many" values. The trick to
remembering this is to keep in mind that the primary key must
be unique.
What SQL?
 SQL or SEQUEL ?
 SQL (pronounced "ess-que-el")
 The acronym SQL stands for Structured Query Language
 SQL is used to communicate with a database. According to ANSI (American
National Standards Institute), it is the standard language for relational
database management systems. SQL statements are used to perform tasks
such as update data on a database, or retrieve data from a database. Some
common relational database management systems that use SQL are: Oracle,
Sybase, Microsoft SQL Server, Access, Ingres, etc. Although most database
systems use SQL, most of them also have their own additional proprietary
extensions that are usually only used on their system. However, the standard
SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and
"Drop" can be used to accomplish almost everything that one needs to do
with a database.
 SQL is short for Structured Query Language
and is a widely used database language,
providing means of data manipulation (store,
retrieve, update, delete) and database
creation.
 SQL (Structured Query Language) is a
database sublanguage for querying and
modifying relational databases. It was
developed by IBM Research in the mid 70's
and standardized by ANSI in 1986.
 The basic structure in SQL is the statement.
Semicolons separate multiple SQL
statements.
 SQL is not case sensitive
Language statements:
 Data Manipulation language
 Data Definition language
 Data control language
1.Data Manipulation language
Database
DML
actions:
 DML is used for the following operations on
data:
 S – Select …….. > Queries
 U - Update
 I - Insert
 D - Delete
1.Selecting Data
 The select statement is used to query the database and retrieve
selected data that match the criteria that you specify. Here is the
format of a simple select statement:
SELECT * FROM Table1
 The column names that follow the select keyword determine which
columns will be returned in the results. You can select as many column
names that you'd like, or you can use a "*" to select all columns.
SELECT Column1, Column2, Column3,
FROM Table1
 The table name that follows the keyword from specifies the table that
will be queried to retrieve the desired results.
SQL DISTINCT
 The SQL DISTINCT clause is used together with
the SQL SELECT keyword, to return a dataset
with unique entries for certain database table
column.
 SQL SELECT DISTINCT is a very useful way to
eliminate retrieving duplicate data reserved for
very specific situations.
 We will use our Customers database table to
illustrate the usage of SQL DISTINCT.
FirstName
LastName
Email
DOB
John
Smith
John.Smith@y
2/4/1968
ahoo.com
626 222-2222
Steven
Goldfish
goldfish@fishh
4/4/1974
ere.net
323 455-4545
Paula
Brown
pb@herowndo
5/24/1978
main.org
416 323-3232
James
Smith
jim@supergig.
co.uk
416 323-8888
20/10/1980
SELECT DISTINCT LastName FROM Customers
LastName
Smith
Goldfish
Brown
Phone
WHERE clause
 The SQL WHERE clause is used to select data
conditionally, by adding it to already existing
SQL SELECT query.
 The where clause (optional) specifies which
data values or rows will be returned or
displayed, based on the criteria described
after the keyword where.
 Conditional selections used in the where
clause:
 WHERE clause is used to filter out rows and only
selecting data that meets the conditional
statement.
 we can use any of the following operators in
conjunction with the SQL WHERE clause:
Operator
Description
=
Equal
<>
Not equal
>
Greater than
<
Less than
>=
Greater than or equal
<=
Less than or equal
BETWEEN
Between an inclusive range
LIKE
Search for a pattern
IN
If you know the exact value you want to return for at
least one of the columns
SQL - Where with Multiple
Conditions
 A WHERE statement can accept multiple
conditional statements. What this means is
that we are able to select rows meeting two
different conditions at the same time.
 The AND & OR operators are used to filter
records based on more than one condition.
WHERE clause with AND operator
 The SQL AND clause is used when you want
to specify more than one condition in your
SQL WHERE clause, and at the same time
you want all conditions to be true.
Customers
FirstName
LastName
Email
DOB
Phone
John
Smith
John.Smith@y
2/4/1968
ahoo.com
626 222-2222
Steven
Goldfish
goldfish@fishh
4/4/1974
ere.net
323 455-4545
Paula
Brown
pb@herowndo
5/24/1978
main.org
416 323-3232
James
Smith
jim@supergig.
co.uk
416 323-8888
20/10/1980
SELECT * FROM Customers
WHERE FirstName = 'John' AND LastName = 'Smith'
FirstName
LastName
Email
DOB
Phone
John
Smith
[email protected]
2/4/1968
626 222-2222
WHERE clause with OR operator
 The SQL OR clause will return all rows
satisfying any of the conditions listed in the
WHERE clause.
Customers
FirstName
LastName
Email
DOB
John
Smith
John.Smith@y
2/4/1968
ahoo.com
626 222-2222
Steven
Goldfish
goldfish@fishh
4/4/1974
ere.net
323 455-4545
Paula
Brown
pb@herowndo
5/24/1978
main.org
416 323-3232
James
Smith
jim@supergig.
co.uk
416 323-8888
20/10/1980
Phone
SELECT * FROM Customers
WHERE FirstName = 'James' OR FirstName = 'Paula'
FirstName
LastName
Email
DOB
Phone
Paula
Brown
pb@herown
domain.org
5/24/1978
416 323-3232
James
Smith
jim@supergi
g.co.uk
20/10/1980
416 323-8888
Combine AND & OR :
 You can combine AND and OR clauses
anyway you want and you can use
parentheses to define your logical
expressions.
Customers
FirstName
LastName
Email
DOB
John
Smith
John.Smith@y
2/4/1968
ahoo.com
626 222-2222
Steven
Goldfish
goldfish@fishh
4/4/1974
ere.net
323 455-4545
Paula
Brown
pb@herowndo
5/24/1978
main.org
416 323-3232
James
Smith
jim@supergig.
co.uk
416 323-8888
20/10/1980
Phone
SELECT * FROM Customers
WHERE (FirstName = 'James' OR FirstName = 'Paula') AND
LastName = 'Brown'
FirstName
LastName
Email
DOB
Phone
Paula
Brown
pb@herown
domain.org
5/24/1978
416 323-3232
ORDER BY clause
 ORDER BY is the SQL command used to sort
rows as they are returned from a SELECT query.
 The ORDER BY keyword sort the records in
ascending order by default.
 If you want to sort the records in a descending
order, you can use the DESC keyword.
 SQL ORDER BY Syntax
SELECT column_name(s)
FROM table_name
ORDER BY column_name(s) ASC|DESC
Customers
FirstName
LastName
Email
DOB
Phone
John
Smith
[email protected]
2/4/1968
626 222-2222
Steven
Goldfish
[email protected]
4/4/1974
323 455-4545
Paula
Brown
[email protected]
5/24/1978
416 323-3232
James
Smith
[email protected]
20/10/1980
416 323-8888
SELECT * FROM Customers ORDER BY DOB
FirstName
LastName
Email
DOB
Phone
John
Smith
[email protected]
2/4/1968
626 222-2222
Steven
Goldfish
[email protected]
4/4/1974
323 455-4545
Paula
Brown
[email protected]
5/24/1978
416 323-3232
James
Smith
[email protected]
20/10/1980
416 323-8888
SELECT * FROM Customers
ORDER BY DOB DESC
FirstName
LastName
Email
DOB
Phone
James
Smith
jim@supergi
g.co.uk
20/10/1980
416 323-8888
Paula
Brown
pb@herownd
5/24/1978
omain.org
416 323-3232
Steven
Goldfish
goldfish@fish
4/4/1974
here.net
323 455-4545
John
Smith
John.Smith@
2/4/1968
yahoo.com
626 222-2222
IN Operator
 SQL IN is an operator used to pull data
matching a list of values.
 A scenario where this proves useful would be
if we wanted to retrieve customer data for
two or more customers. We can use the IN
operator to specify a list of customer names,
and SQL will retrieve rows reflecting every
customer in the list.
 THE SQL IN syntax looks like this:
SELECT Column1, Column2, Column3, …
FROM Table1
WHERE Column1 IN (Valu1, Value2, …)
 THE SQL IN syntax looks like this:
SELECT Column1, Column2, Column3, …
FROM Table1
WHERE Column1 NOT IN (Valu1, Value2, …)
EmployeeHours table
Employee
Date
Hours
John Smith
5/6/2004
8
Allan Babel
5/6/2004
8
Tina Crown
5/6/2004
8
John Smith
5/7/2004
9
Allan Babel
5/7/2004
8
Tina Crown
5/7/2004
10
John Smith
5/8/2004
8
Allan Babel
5/8/2004
8
Tina Crown
5/8/2004
9
SELECT * FROM EmployeeHours WHERE Hours IN (9, 10)
Employee
Date
Hours
John Smith
5/7/2004
9
Tina Crown
5/7/2004
10
Tina Crown
5/8/2004
9
LIKE operator
 The SQL LIKE clause is very useful when you
want to specify a search condition within your
SQL WHERE clause, for a specified pattern in
a column.
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern
 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
Customers table
FirstName
LastName
Email
DOB
Phone
John
Smith
[email protected]
2/4/1968
626 222-2222
Steven
Goldfish
[email protected]
4/4/1974
323 455-4545
Paula
Brown
[email protected]
5/24/1978
416 323-3232
James
Smith
[email protected]
20/10/1980
416 323-8888
SELECT * FROM Customers WHERE FirstName LIKE 'J%'
FirstNam
e
LastName Email
DOB
John
Smith
John.Smit
h@yahoo. 2/4/1968
com
James
Smith
jim@supe
rgig.co.uk
20/10/198
0
Phone
626 2222222
416 3238888
select first, last, from empinfo where last LIKE '%s';
Sample Table: empinfo
first
last
id
age
city
state
John
Jones
99980
45
Payson
Arizona
Mary
Jones
99982
25
Payson
Arizona
Eric
Edwards
88232
32
San Diego
California
first
last
John
Jones
Mary
Jones
Eric
Edwards
Mary Ann
Edwards
88233
32
Phoenix
Arizona
Mary Ann
Edwards
Ginger
Howell
98002
42
Cottonwood
Arizona
Erica
Williams
Sebastian
Smith
92001
23
Gila Bend
Arizona
Gus
Gray
22322
35
Bagdad
Arizona
Mary Ann
May
32326
52
Tucson
Arizona
Erica
Williams
32327
60
Show Low
Arizona
Leroy
Brown
32380
22
Pinetop
Arizona
Elroy
Cleaver
32382
22
Globe
Arizona
select first, last, age from empinfo where last LIKE
'%illia%';
Sample Table: empinfo
first
last
id
age
city
state
John
Jones
99980
45
Payson
Arizona
Mary Ann
Edwards
88233
32
Phoenix
Arizona
Sebastian
Smith
92001
23
Gila Bend
Arizona
Mary Ann
May
32326
52
Tucson
Arizona
Erica
Williams
32327
60
Show Low
Arizona
Leroy
Brown
32380
22
Pinetop
Arizona
first
last
age
Erica
Williams
60
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
SELECT * FROM Persons WHERE FirstName LIKE '_la'
P_Id
LastName
FirstName
Address
City
1
Hansen
Ola
Timoteivn 10 Sandnes
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
SELECT * FROM Persons WHERE LastName LIKE '[bsp]%'
P_Id
LastName
FirstName
Address
City
2
Svendson
Tove
Borgvn 23
Sandnes
3
Pettersen
Kari
Storgt 20
Stavanger
SELECT * FROM Persons WHERE LastName LIKE '[!bsp]%'
P_Id
LastName
FirstName
Address
City
1
Hansen
Ola
Timoteivn 10 Sandnes
SELECT * FROM Customers WHERE Phone LIKE '[4-6]_6%'
This SQL expression will return all customers satisfying the following
conditions:
• The Phone column starts with a digit between 4 and 6 ([4-6])
• Second character in the Phone column can be anything (_)
• The third character in the Phone column is 6 (6)
• The remainder of the Phone column can be any character string (%)
FirstName
LastName
Email
DOB
Phone
John
Smith
[email protected]
2/4/1968
626 222-2222
Steven
Goldfish
[email protected]
4/4/1974
323 455-4545
Paula
Brown
[email protected]
5/24/1978
416 323-3232
James
Smith
[email protected]
20/10/1980 416 323-8888
OUTPUT
FirstName
LastName
Email
DOB
Phone
John
Smith
[email protected]
2/4/1968
626 222-2222
Paula
Brown
[email protected]
5/24/1978
416 323-3232
James
Smith
[email protected]
20/10/1980
416 323-8888
The TOP Clause
 The TOP clause is used to specify the number
of records to return.
 SQL Server Syntax
SELECT TOP number|percent
column_name(s)
FROM table_name
VARIABLES
Variables
 There will be times when you need to hold a
value or work with a value that does not
come directly from a column.
 A variable can be declared at any time
within a set of T-SQL.
 A variable has a finite lifetime.
DECLARE @variable_name datatype,
@variable_name2 datatype
 All variables have to be preceded with an @ sign.
 More than one variable can be declared, although




multiple variables should be separated by a comma and
held on one line of code.
If you move to a second line of code, then you need to
prefix the first variable with another DECLARE
statement.
All variables can hold a NULL value, and there is not an
option to say that the variable cannot hold a NULL value.
By default then, when a variable is declared, it will have
an initial value of NULL.
It is also not possible at declaration to assign a value to a
variable.
 To assign a value to a variable, you can use a
SET statement or a SELECT statement.
 It is standard to use SET to set a variable
value when you are not working with any
tables.
DECLARE @MyDate datetime, @CurrPriceInCents money
SET @MyDate = GETDATE()
SELECT @CurrPriceInCents = CurrentPrice * 100
FROM ShareDetails
WHERE ShareId = 2
SELECT @MyDate,@CurrPriceInCents
SQL FUNCTIONS
SQL FUNCTIONS
 SQL has many built-in functions for
performing calculations on data.
 Aggregate functions.
 Datetime function.
AGGREGATE FUNCTIONS
Aggregations
 An aggregation is where SQL Server
performs a function on a set of data to
return one aggregated value per grouping of
data.
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
 MAX() - Returns the largest value
 MIN() - Returns the smallest value
 SUM() - Returns the sum
1.SQL COUNT()
 The SQL COUNT aggregate function is used
to count the number of rows in a database
table.
SELECT COUNT(*)
FROM table_name
SELECT COUNT(Column1)
FROM Table1
SELECT COUNT(DISTINCT
column_name) FROM table_name
 If we want to count the number of customers in
our Customers table, we will use the following
SQL COUNT statement:
SELECT COUNT(LastName) AS NumberOfCustomers FROM Customers
FirstName
LastName
Email
DOB
Phone
John
Smith
[email protected]
2/4/1968
626 222-2222
Steven
Goldfish
[email protected]
4/4/1974
323 455-4545
Paula
Brown
[email protected]
5/24/1978
416 323-3232
James
Smith
[email protected]
20/10/1980
416 323-8888
NumberOfCustomers
4
2.SQL AVG():
 The SQL AVG aggregate function selects the
average value for certain table column.
SELECT AVG(Column1)
FROM Table1
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
SELECT AVG(OrderPrice) AS OrderAverage FROM Orders
OrderAverage
950
3.SQL SUM():
 The SQL SUM aggregate function allows
selecting the total for a numeric column.
SELECT SUM(Column1)
FROM Table1
Sales:
CustomerID
Date
SaleAmount
2
5/6/2004
$100.22
1
5/7/2004
$99.95
3
5/7/2004
$122.95
3
5/13/2004
$100.00
4
5/22/2004
$555.55
SELECT SUM(SaleAmount) FROM Sales
SaleAmount
$978.67
SELECT SUM(SaleAmount) FROM Sales WHERE CustomerID = 3
SaleAmount
$222.95
4.SQL MAX():
 The SQL MAX aggregate function allows us
to select the highest (maximum) value for a
certain column.
SELECT MAX(Column1)
FROM Table1
5.SQL MIN():
 The SQL MIN aggregate function allows us to
select the lowest (minimum) value for a
certain column.
SELECT MIN(Column1)
FROM Table1
DATETIME FUNCTIONS
DATETIME functions:
 Although the SQL standard defines a
DATETIME data type and its components,
YEAR, MONTH,DAY, HOUR, MINUTE, and
SECOND, it doesn’t dictate how a DBMS
makes this data available.
 Each DBMS offers functions that extract parts
of DATETIMEs. Let’s look at some examples
ofT-SQL DATETIME functions.
 Getdate()
 Dateadd()
 Datediff()
 Datename()
 Datepart()
1.Getdate():
 GETDATE() is a great function for returning
the exact date and time from the system.
Select getdate() as ‘System date’
2.Datepart(datepart,date):
 If you wish to achieve returning part of a date
from a date variable, column, or value, you
can use DATEPART() within a SELECT
statement.
 The datepart option applies to all of the
date functions and details what you want
to add from milliseconds from years.
 These are defined as reserved words and
therefore will not be surrounded by quotation
marks.
Select datepart(dw, getdate())
3. DATENAME(datepart, date)
 Returning the name of the part of the date is
great for using with things like customer
statements.
 Changing the number 6 to the word June
makes for more pleasant reading.
SELECT DATENAME(dw,getdate())
4. DATEADD(datepart, number, date)
 If you want to add or subtract an amount of
time to a column or a variable and display a
new value in a rowset, or set a variable with
that new value, the DATEADD() will do this.
SELECT DATEADD(hh,4,getdate())
5. DATEDIFF(datepart, startdate,
enddate)
 To find the difference between two dates,
you would use the function DATEDIFF().
DECLARE @FirstTime datetime, @SecondTime datetime
SET @FirstTime = '24 March 2006 3:00 PM'
SET @SecondTime = '24 March 2006 3:33PM'
SELECT DATEDIFF(ms,@FirstTime,@SecondTime)
String Functions
String functions
 String functions can act on those data types that
are character based, such as varchar and char.








ASCII()
CHAR()
RIGHT()
LEFT()
LOWER()
UPPER()
LTRIM()
RTRIM()
1. ASCII()
 ASCII() will convert a single character to the
equivalent ASCII code.
 This function will return the ASCII code of the
first character within a string. If the string has
more than one character, then only the first
will be taken.
DECLARE @StringTest char(10)
SET @StringTest = ASCII('Robin ')
SELECT @StringTest
2.CHAR()
 The reverse of ASCII() is the CHAR() function,
which takes a numeric value and turns it into
an alphanumeric character.
 If the conversion results in a value greater
 than 255, the last value for an ASCII character,
then NULL is returned.
DECLARE @StringTest char(10)
SET @StringTest = ASCII('Robin ')
SELECT CHAR(@StringTest)
3. LEFT()
 LEFT() return the first n left characters from a
string-based variable.
DECLARE @StringTest char(10)
SET @StringTest = 'Robin '
SELECT LEFT(@StringTest,3)
4. RIGHT()
 The opposite of LEFT() is of course, RIGHT()
and this function will return a set of
characters from the right-hand side.
DECLARE @StringTest char(10)
SET @StringTest = ' Robin'
SELECT RIGHT(@StringTest,3)
5.LOWER()
 To change alphabetic characters within a
string, ensuring that all characters are in
lowercase, you can use the LOWER() function.
6.UPPER()
 The upper() is the reverse of the LOWER()
function and will change all characters
touppercase.
7.LTRIM()
 There will be times that leading spaces occur
in a string and you want to remove them.
LTRIM()
will trim these spaces on the left.
DECLARE @StringTest char(10)
SET @StringTest = ' Robin'
SELECT 'Start-'+LTRIM(@StringTest),'Start-'+@StringTest
8. RTRIM()
 When you have a CHAR() data type, no matter how many
characters you enter, the variable will be filled on the
right, known as right padded, with spaces. To remove
these, use RTRIM. This will change the data from a fixedlength CHAR() to a variable length value.
DECLARE @StringTest char(10)
SET @StringTest = 'Robin'
SELECT @StringTest+'-End',RTRIM(@StringTest)+'-End'
Mathematical Functions
Mathematical Functions
 Single-row functions return a single result
row for every row of a queried table or view.








Abs()
Len()
Floor()
Ceiling()
Round()
Power()
Sqrt()
Sign()
Ranking function:
 We can add sequence row number to a SQL
select query starting from 1 onwards.
 Using built in SQL function ROW_NUMBER()
ROW_NUMBER() OVER (ORDER BY column)
ROW_NUMBER() OVER
([<partition_by_clause>]<order_by_clause>)
sales_employee
name
territory
sales_
amount
A
X
100
B
X
200
C
X
200
D
X
E
SELECT name ,territory ,ROW_NUMBER()
OVER ( ORDER BY sales_amount ) AS
[row_number] FROM sales_employee
OUTPUT:
name
territory
sales_
amount
ROW_
NUMBER
A
X
100
1
300
B
X
200
2
X
400
C
X
200
3
F
Y
300
D
X
300
5
G
Y
300
E
X
400
8
F
Y
300
6
H
Y
500
G
Y
300
7
I
Y
600
H
Y
500
9
J
Z
200
I
Y
600
10
K
Z
700
J
Z
200
4
K
Z
700
11
 SELECT
name ,territory ,ROW_NUMBER() OVER (PARTITION BY
territory ORDER BY sales_amount) AS [row_number]
name
territory
sales_
amount
ROW_
NUMBER
A
X
100
1
B
X
200
2
C
X
200
3
D
X
300
4
E
X
400
5
F
Y
300
1
G
Y
300
2
H
Y
500
3
I
Y
600
4
J
Z
200
1
K
Z
700
2
Group By clause:
 The GROUP BY clause is used to organize
output rows into groups.
 The SELECT list can include aggregate
functions and produce summary values for
each group.
 Using aggregations, as has just been
demonstrated, works well when you just wish
a single row
 of results for a specific filtered item. If you
wish to find the average price of several
shares, you
 may be thinking you need to provide a
SELECT AVG() for each share.
Having Clause
 Having clause is used to filter data based on
the group functions.
 consider following table
EMPLOYRR
BONUS
A
1000
B
2000
A
500
C
700
B
1250
Emp_bonus
 calculate the total bonus that each employee
received.
 select employee, sum(bonus) from
emp_bonus group by employee;
 Result is look like this
EMPLOYRR
Sum(BONUS)
A
1500
B
3250
A
700
 To find the employees who received more
than $1,000 in bonuses .
 select employee, sum(bonus) from
emp_bonus group by employee where
sum(bonus) > 1000;
 select employee, sum(bonus) from
emp_bonus group by employee having
sum(bonus) > 1000;
EMPLOYEE
BOUNS
A
1500
B
3250
 The difference between the having and
where clause in sql is that the where clause
can not be used with aggregates, but the
having clause can.
SQL COMMENTS:
 Single line comment
 Use -- (double hyphens)
-- this is single line comment
 Multi line comment:
 Use /*…*/
/* this is
multi line
comments*/
Insert statement:
 The SQL INSERT INTO syntax has 2 main
forms and the result of either of them is
adding a new row into the database table.
INSERT INTO Table1
VALUES (value1, value2, value3…)
INSERT INTO Table1 (Column1,
Column2, Column3…)
VALUES (Value1, Value2, Value3…)
Update statements:
 The SQL UPDATE clause changes the data in existing
database row(s) .
 SQL WHERE clause is used in our SQL UPDATE
statement in order to specify which row(s) we intend to
update.
UPDATE Table1
SET Column1 = Value1, Column2 = Value2
WHERE Some_Column = Some_Value
CUSTOMER TABLE:
FirstName
LastName Email
DOB
Phone
John
Smith
[email protected]
m
2/4/1968
626 222-2222
Steven
Goldfish
[email protected]
4/4/1974
323 455-4545
Paula
Brown
[email protected] 5/24/1978
416 323-3232
James
Smith
[email protected]
416 323-8888
20/10/1980
UPDATE Customers
SET Phone = '626 555-5555'
WHERE LastName = 'Smith'
FirstName
LastName
Email
DOB
Phone
John
Smith
[email protected]
m
2/4/1968
626 555-5555
Steven
Goldfish
[email protected]
4/4/1974
323 455-4545
Paula
Brown
[email protected] 5/24/1978
416 323-3232
James
Smith
[email protected]
626 555-5555
20/10/1980
Remember:
 If we don’t specify a WHERE clause in the SQL
expression above, all customers' PHONE will
be updated to ‘'626 555-5555 ', so be careful
with the SQL UPDATE command usage.
Delete statement:
 SQl DELETE command is used to remove
data from a database.
 The SQL DELETE command has the
following generic SQL syntax:
DELETE FROM Table1
WHERE Some_Column = Some_Value
CUSTOMER TABLE:
FirstName
LastName
Email
DOB
Phone
John
Smith
[email protected] 2/4/1968
626 222-2222
Steven
Goldfish
[email protected]
4/4/1974
323 455-4545
Paula
Brown
[email protected]
5/24/1978
416 323-3232
James
Smith
[email protected]
20/10/1980
416 323-8888
DELETE FROM Customers
WHERE LastName = 'Smith'
FirstName
LastName
Email
DOB
Phone
Steven
Goldfish
[email protected]
4/4/1974
323 455-4545
Paula
Brown
[email protected] 5/24/1978
416 323-3232
2.Data definition language
Database ddl actions:
 DDL actions are used to define and manage
database objects.
 Following are the DDL actions:
 Create database/table
 Alter table
 Drop database/table
1.The CREATE DATABASE Statement
 The CREATE DATABASE statement is used to
create a database.
CREATE DATABASE database_name
2.CREATE TABLE
 The CREATE TABLE statement is used to
create a table in a existing database.
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
)
Example:
CREATE TABLE customer
(
C_Id int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
3.Alter Table:
 The ALTER TABLE statement is used to add,
delete, or modify columns in an existing
table.
SYNTAX
1.To delete a column in a table:
ALTER TABLE table name
DROP COLUMN column name
2.To change the data type of a column in a table:
ALTER TABLE table_name
ALTER COLUMN column_name
datatype
3.To add a column in a table:
ALTER TABLE table_name
ADD column_name datatype
Drop Command in SQL:
 Drop command is used to remove an Object
from a database.
 When Drop Table command is used, it deletes
all rows from that table, then removes the
table from the database and removes all
references to that table.
Syntax:
DROP TABLE Table_Name
Ex: DROP TABLE Department
Truncate Command in SQL:
 Truncate Command is used to delete all the
records from a table and frees the space in
disc.
Syntax: TRUNCATE TABLE Table_Name
Ex: TRUNCATE TABLE Department
Difference between Delete and
Truncate Command:
 Delete Command deletes records from a table
based on a condition or deletes all the records if
no condition is specified. But it doesn't free the
space.
 Truncate Command deletes all the records from
a table as well as frees up the space.
Difference between Drop and Truncate
Command in SQL:
 Truncate Command removes all rows from a table, but
the table structure, all relationships, constraints remain
in the Database.
 Drop Command removes the entire table, all
relationships associated with that table, all constraints in
that table.
 We can insert new records in a truncated table. But it is
not possible when a table is dropped as it doesn't exist.