ITDA1001 [Lesson 6] Copyright © 2018 VIT, All Rights Reserved 1
Copyright © 2015 – 2018, Victorian Institute of Technology.
The contents contained in this document may not be reproduced in any form or by any means, without the written permission of VIT,
other than for the purpose for which it has been supplied. VIT and its logo are trademarks of Victorian Institute of Technology.
SELECT STATEMENT
ITDA1001 [Lesson 6] Copyright © 2018 VIT, All Rights Reserved 2
Topic
• Select Statement (Cont…)
ITDA1001 [Lesson 6] Copyright © 2018 VIT, All Rights Reserved 3
Select Statement (Cont..)
• Sorting the output by Order by
• Example (Ascending)
– SELECT * FROM Products where UnitsInStock > 50 and
UnitsInStock <= 65 order by ProductName
• Output
ITDA1001 [Lesson 6] Copyright © 2018 VIT, All Rights Reserved 4
Select Statement (Cont..)
• Sorting the output by Order by
• Example (Descending)
– SELECT * FROM Products where UnitsInStock > 50 and
UnitsInStock <= 65 order by ProductName desc
• Output
ITDA1001 [Lesson 6] Copyright © 2018 VIT, All Rights Reserved 5
Select Statement (Cont..)
• Sorting the output by Order by
• Example
– SELECT Country, Companyname, contactname, ContactTitle FROM
Customers order by Country, ContactName desc,
• Output
ITDA1001 [Lesson 6] Copyright © 2018 VIT, All Rights Reserved 6
Select Statement (Cont..)
• Using Null with where
• Example
– select CompanyName, contactname, ContactTitle from Customers
where Fax is null
• Output
ITDA1001 [Lesson 6] Copyright © 2018 VIT, All Rights Reserved 7
Select Statement (Cont..)
• Using Null with where
• Example
– select CompanyName, contactname, ContactTitle, Fax from
Customers where Fax is not null
• Output
ITDA1001 [Lesson 6] Copyright © 2018 VIT, All Rights Reserved 8
Select Statement (Cont..)
• Using Between with where
• Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
• Example
SELECT ProductName, UnitPrice, UnitsInStock FROM Products
WHERE UnitPrice BETWEEN 10 AND 12;
• Output
ITDA1001 [Lesson 6] Copyright © 2018 VIT, All Rights Reserved 9
Select Statement (Cont..)
• SQL Aliases
– Alias Column Syntax
SELECT column_name AS alias_name
FROM table_name;
• Example
SELECT CustomerID as ID, ContactName AS Customer
FROM Customers;
• Output
ITDA1001 [Lesson 6] Copyright © 2018 VIT, All Rights Reserved 10
Select Statement (Cont..)
• Syntax
SELECT column1, column2, …
FROM table_name
WHERE columnN LIKE pattern;
| LIKE Operator | Description |
| WHERE CustomerName LIKE ‘a%’ | Finds any values that start with “a” |
| WHERE CustomerName LIKE ‘%a’ | Finds any values that end with “a” |
| WHERE CustomerName LIKE ‘%or%’ | Finds any values that have “or” in any position |
| WHERE CustomerName LIKE ‘_r%’ | Finds any values that have “r” in the second position |
| WHERE CustomerName LIKE ‘a_%_%’ |
Finds any values that start with “a” and are at least 3 characters in length |
| WHERE ContactName LIKE ‘a%o’ | Finds any values that start with “a” and ends with “o” |
ITDA1001 [Lesson 6] Copyright © 2018 VIT, All Rights Reserved 11
Select Statement (Cont..)
• Example
SELECT * FROM Customers
WHERE ContactName LIKE ‘a%’;
• Output
ITDA1001 [Lesson 6] Copyright © 2018 VIT, All Rights Reserved 12
Select Statement (Cont..)
• Queries can use arithmetic calculations.
• Example
– Select ProductName, UnitPrice, UnitsInStock, unitprice * UnitsInStock
as Total from Products
• Output
ITDA1001 [Lesson 6] Copyright © 2018 VIT, All Rights Reserved 13
Select Statement (Cont…)
• SELECT TOP clause is used to specify the number of
records to return.
• The SELECT TOP clause is useful on large tables with
thousands of records. Returning a large number of records
can impact on performance.
• Syntax
– SELECT TOP numberpercent column_name(s)
FROM table_name
WHERE condition;
ITDA1001 [Lesson 6] Copyright © 2018 VIT, All Rights Reserved 14
Select Statement (Cont…)
• Example
– SELECT TOP 5 * FROM Products;
• Output
ITDA1001 [Lesson 6] Copyright © 2018 VIT, All Rights Reserved 15
Select Statement (Cont…)
• Example
– SELECT TOP 5 PERCENT * FROM Products;
• Output
ITDA1001 [Lesson 6] Copyright © 2018 VIT, All Rights Reserved 16
Select Statement (Cont…)
• Comments are used to explain sections of SQL statements,
or to prevent execution of SQL statements.
• Single Line Comments
– Single line comments start with –.
– Any text between — and the end of the line will be ignored (will not be
executed).
• Example
– –Single line comment below statement gives all records of Customers
SELECT * FROM Customers;
• Another Example
– SELECT * FROM Customers — Comment: all records of Customers;
ITDA1001 [Lesson 6] Copyright © 2018 VIT, All Rights Reserved 17
Select Statement (Cont…)
• Multi-line Comments
– Multi-line comments start with /* and end with */.
– Any text between /* and */ will be ignored.
• Example
– /*Display all the fields
of all the records
in the Customers table:*/
SELECT * FROM Customers;
• Another Example
– /*SELECT * FROM Customers;
SELECT * FROM Shippers;
SELECT * FROM Regions;
SELECT * FROM Categories;*/
SELECT * FROM Suppliers;
ITDA1001 [Lesson 6] Copyright © 2018 VIT, All Rights Reserved 18
Select Statement (Cont…)
• IN Operator
– The IN operator allows you to specify multiple values in a WHERE
clause.
• Syntax
– SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, …);
• Example
– SELECT * FROM Customers
WHERE Country IN (‘India’, ‘Pakistan’, ‘Srilanka’, ‘Australia’);
ITDA1001 [Lesson 6] Copyright © 2018 VIT, All Rights Reserved 19
Select Statement (Cont…)
• Another Syntax
– SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);
• Example
– SELECT * FROM Customers
WHERE Country IN (Select Country from Suppliers);
ITDA1001 [Lesson 6] Copyright © 2018 VIT, All Rights Reserved 20
Select Statement (Cont…)
• EXISTS Operator
– The EXISTS operator is used to test for the existence of any record
in a subquery.
– The EXISTS operator returns true if the subquery returns one or
more records.
• Syntax
– SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);
ITDA1001 [Lesson 6] Copyright © 2018 VIT, All Rights Reserved 21
Select Statement (Cont…)
• Example
– SELECT ContactName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE
SupplierId = Suppliers.supplierId AND UnitsInStock > 0);
– Here, in above example column “supplierId” in Product table is
foreign key to column “supplierId” in Suppliers table.
– The above SQL statement returns lists of the ContactName with a
product stock more than 0.
ITDA1001 [Lesson 6] Copyright © 2018 VIT, All Rights Reserved 22
Select Statement (Cont…)
• Example
– SELECT ContactName
FROM Suppliers
WHERE EXISTS (SELECT ProductName FROM Products WHERE
SupplierId = Suppliers.supplierId AND UnitPrice = 100);
– Here, in above example column “supplierId” in Product table is
foreign key to column “supplierId” in Suppliers table.
– The above SQL statement returns lists of the ContactName with a
product price equal to 100.
ITDA1001 [Lesson 6] Copyright © 2018 VIT, All Rights Reserved 23
Select Statement (Cont…)
• ANY and ALL Operators
– The ANY and ALL operators are used with a WHERE or HAVING
clause.
– The ANY operator returns true if any of the subquery values meet
the condition.
• ANY Syntax
– SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name FROM table_name WHERE condition);
ITDA1001 [Lesson 6] Copyright © 2018 VIT, All Rights Reserved 24
Select Statement (Cont…)
• ANY example
– SELECT ProductName, UnitsInStock
FROM Products
WHERE ProductID = ANY (SELECT ProductID FROM [Order
Details] WHERE UnitPrice > 20);
– The above SQL statement returns Product Names and Units in
Stock, if it finds ANY records in the Order Details table that UnitPrice
> 20.
ITDA1001 [Lesson 6] Copyright © 2018 VIT, All Rights Reserved 25
Select Statement (Cont…)
• ANY another example
– SELECT ProductName, UnitsInStock
FROM Products
WHERE ProductID = ANY (SELECT ProductID FROM [Order
Details] WHERE UnitPrice = 20);
– The above SQL statement returns Product Names and Units in
Stock, if it finds ANY records in the Order Details table that UnitPrice
= 20.
ITDA1001 [Lesson 6] Copyright © 2018 VIT, All Rights Reserved 26
Select Statement (Cont…)
• The ALL operator returns true if all of the subquery values
meet the condition.
• ALL Syntax
– SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name FROM table_name WHERE condition);
ITDA1001 [Lesson 6] Copyright © 2018 VIT, All Rights Reserved 27
Select Statement (Cont…)
• ALL example
– SELECT ProductName, UnitsInStock
FROM Products
WHERE ProductID = ALL (SELECT ProductID FROM [Order
Details] WHERE UnitPrice > 20);
– The above SQL statement returns Product Names and Units in
Stock, if it finds ALL records in the Order Details table has UnitPrice
> 20.
ITDA1001 [Lesson 6] Copyright © 2018 VIT, All Rights Reserved 28
Summary
• Revision of Key Concepts
• Lab Work
– Lesson 6 – Activity 6 (Lesson_6 Activity.docx)
• Questions and Answer
The post SELECT STATEMENT ITDA1001 appeared first on My Assignment Online.