Transcript SQL

SQL
SQL
•
•
•
•
•
•
•
Originally developed by IBM
Standardized in 80’s by ANSI and ISO
Language to access relational database and
English-like
non-procedural
Predominant language for database access
Implemented on Oracle, Sybase,
Informix,DB/2, SQL Server
Uses of SQL Code
• As data definition language (DDL)
– Create table, drop table, create index, create
constraints, insert record
• As data manipulation language (DML)
– Update table, insert record, delete record
• As data query language
– Select, create view
• As database management language (Data
control language)
– Create user, grant privileges, revoke privileges
SELECT command
• For retrieving records from one or
more tables
Minimal Syntax (to retrieve one field
from one table)
SELECT <FieldName>
FROM <TableName>
SELECT
SELECT <expression> [as var1]
FROM <TableName>
WHERE <Boolean conditions>
GROUP BY <expression>
ORDER By <expression>
Expression can be a field name, or constant
or system variable or string or arithmetic
or logical expressions that use expressions
Boolean conditions
• Used to filter records for output
• Evaluates to be True or False
• Applied to each record in the table
• Only those that evaluates True are output
Examples
CatID = ‘3’
Videos.Year = 1994 and Rating >3
Rating<3 or Stars <= 2
SELECT Example
• Find videos with rating of 4 or more
• SELECT videos.title, rating
FROM Videos
WHERE videos.rating >= 4
SELECT Example using Alias
• Alias is an alternative column or table
name
SELECT Videos.Title AS
MustSeeVideos, videos.Stars
FROM Videos
WHERE videos.star = 5;
SELECT Example
Using a Function
• Using the COUNT aggregate function
to find totals
SELECT COUNT(VideoID)
FROM Videos
WHERE CatID = ‘2’;
SELECT Example – Boolean
Operators
• AND, OR, and NOT Operators for
customizing conditions in WHERE
clause
SELECT Videos.Title, videos.Cast
FROM Videos
WHERE Videos.cast LIKE ‘*Gibson*’ OR
Videos.cast like ‘*Williams’
Sorting Results with the
ORDER BY Clause
• Sort the results first by STATE, and
within a state by CUSTOMER_NAME
SELECT Videos.Title, Videos.Director
From Videos
ORDER BY Director,
CUSTOMER_NAME;
Comparing with multiple values
SELECT Videos.Title, Videos.Director,
CatID
From Videos
where catID in ('2', '3', '5')
ORDER BY Director;
Comparing with multiple values
SELECT Videos.Title, Videos.Director,
Year
From Videos
where year between 1980 and 1990
ORDER BY Director;
Categorizing Results Using the
GROUP BY Clause
• For use with aggregate functions
SELECT CatID, COUNT(VideoID)
FROM Videos
GROUP BY CatID;
Qualifying Results by Categories
Using the HAVING Clause
• For use with GROUP BY
SELECT CatID, COUNT(VideoID)
FROM Videos
GROUP BY CatID
HAVING COUNT(VideoID) > 1;
• Like a WHERE clause, but it operates on groups
(categories), not on individual rows. Here, only
those groups with total numbers greater than 1
will be included in final result