Transcript SQL reviews

SQL reviews
Stored Procedures
• Create Procedure
Triggers
TRansaction
•
•
TRY...CATCH (Transact-SQL)
•
Views
What is a View?
•
In SQL, a VIEW is a virtual relation based on the result-set
of a SELECT statement.
•
A view contains rows and columns, just like a real table.
The fields in a view are fields from one or more real tables
in the database. In some cases, we can modify a view and
present the data as if the data were coming from a single
table.
•
Syntax:
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
SQL – Relations, Tables & Views
•
1.
When we say Relation, it could be a Table or a View. There
are three kind of relations:
Stored relations
tables
We sometimes use the term “base relation” or “base
table”
2. Virtual relations
views
3. Temporary results
SQL – Create View
• Example: Create a view with title and year and made by
Paramount studio.
Movie (title, year, length, inColor, studioName,
producerC#)
CREATE VIEW ParamountMovie AS
SELECT title,year
FROM Movie
WHERE studioName = ‘Paramount’;
SQL – Querying View
• A view could be used from inside a query, a stored
procedure, or from inside another view. By adding functions,
joins, etc., to a view, it allows us to present exactly the data
we want to the user.
View
SELECT title
FROM ParamountMovie
WHERE year = ‘1979’;
• Have same result as
Table
SELECT title
FROM Movie
WHERE studioName = ‘Paramount’ AND year =
‘1979’;
SQL - Querying View con’t
• Query involving both view and table
SELECT DISTINCT starName
View
FROM ParamountMovie, StarsIn
Table
WHERE title = movieTitle AND year = movieYear;
SQL - Querying View example
Movie (title, year, length, inColor, studioName, producerC#)
MovieExec (name, address, cert#, netWorth)
CREATE VIEW MovieProd AS
SELECT title, name
FROM Movie, MovieExec
WHERE producerC# = cert#;
SELECT name
FROM MovieProd
WHERE title = ‘Gone With the Wind’;
• Same result as query from tables
SELECT name
FROM Movie, MovieExec
WHERE producerC# = cert# AND title = ‘The War Of the World’;
SQL - Renaming Attributes in View
• Sometime, we might want to distinguish
attributes by giving the different name.
CREATE VIEW MovieProd (movieTitle, prodName) AS
SELECT title, name
FROM Movie, MovieExec
WHERE producerC# = cert#;
SQL - Modifying View
When we modify a view, we actually modify a table through
a view. Many views are not updateable. Here are rules
have been defined in SQL for updateable views:
•
selecting (SELECT not SELECT DISTINCT) some attributes
from one relation R (which may itself be an updateable
view)
 The WHERE clause must not involve R in a subquery.
 The list in the SELECT clause must include enough
attributes that will allow us to insert tuples into the
view as well as table. All other attributes will be filled
out with NULL or the proper default values.
SQL – Modifying View (INSERT)
INSERT INTO ParamountMovie
VALUES (‘Star Trek’, 1979);
To make the view ParamountMovie updateable, we need to add
attribute studioName to it’s SELECT clause because it makes more
sense if the studioName is Paramount instead of NULL.
CREATE VIEW ParamountMovie AS
SELECT studioName, title, year
FROM Movie
WHERE studioName = ‘Paramount’;
Then
INSERT INTO ParamountMovie
VALUES (‘Paramount’, ‘Star Trek’, 1979);
Title
year length inColor studioName
producerC#
‘Star Trek’ 1979
0
NULL ‘Paramount’
NULL
SQL - Modifying View (DELETE)
• Suppose we wish to delete all movies with “Trek”
in their title from the updateable view
ParamountMovie.
DELETE FROM ParamountMovie
WHERE title LIKE ‘%Trek%’;
It is turned into the base table delete
DELETE FROM Movie
WHERE title LIKE ‘%Trek%’ AND studioName =
‘Paramount’;
SQL - Modifying View (UPDATE)
• UPDATE from an updateable view
UPDATE ParamountMovie
SET year = 1979
WHERE title = ‘Star Trek the Movie’;
It is turned into the base table update
UPDATE Movie
SET year = 1979
WHERE title = ‘Star Trek the Movie’ AND studioName =
‘Paramount’;
SQL – View (DROP)
• DROP view: All views can be dropped, whether or not the
view is updateable.
DROP VIEW ParamountMovie;
• DROP VIEW does not affect any tuples of the underlying
relation (table) Movie.
• However, DROP TABLE will delete the table and also make
the view ParamountMovie unusable.
DROP TABLE Movie
SQL - Download MySQL
• Go to http://dev.mysql.com/downloads/ and
download:
 MySQL (Windows User / version 4.1.10a, 5.0.2alpha has bug that keep shutting down the service)
 MySQL Administrator
 MySQL Query Browser
SQL – Install MySQL
• During the installation –
– you can <“Skip Sing-Up”> to fast installation
– Will run Configuration Wizard right after installation automatically
– If the service won’t start, press <cancel>, then run Configuration
Wizard manually again
• Run MySQL Server Instance Config Wizard from windows
menu –
– use default setting unless you know what you are doing
– Modify Security Setting (option: you can start service without doing
this)
– Execute
• If there is an error, try press <back> to go back, then press <next> to
<execute> again
– TCP / IP Networking –
• Try different port number if you are using networking.
SQL - MySQL Administrator
• MySQL Administrator come with MySQL System
Tray Monitor allow you to configure your server
• Run MySQL Administrator –
– For the first time, you may not have “Stored
connection,” you can <Add new Connection>
and give the Connection name.
– Server Host: type “localhost” If you don’t
use networking
SQL - MySQL Query Browser
• Run MySQL Query Browser
– Give a name for the Default Schema
• Schema means Database instance
– You can type command line into the top box or
right Click on schemata to create new table
SQL - Bibliography
• First Course In Database Systems, Jeffery D. Ullman
and Jennifer Widom,1997 Prentice Hall, Inc.
• http://mysql.com
Functions
•CREATE FUNCTION udf_MusteriEnBuyukYas
(
@Id int
-- alacagi parametreleri virgül
--ile ayirarak parametre birden cok parametre verebilirsiniz
)
RETURNS int --döndürecegi degerin tipi
AS
BEGIN
DECLARE @Result int
SET @Result = (SELECT Max(Yas) FROM MUSTERI Where Id >=@Id)
RETURN(@Result)
END
•
•
•
•
•
•
•
Reference
• http://www.yazilimmutfagi.com/10258/veritabani/sqlserver/sql-server-da-function-kullanimi.aspx
• http://shamas-saeed.blogspot.com.tr/p/function-var-podocument.html
• https://www.google.com/url?sa=t&rct=j&q=&esrc=s&sourc
e=web&cd=11&cad=rja&uact=8&ved=0CBsQFjAAOAo&url=
http%3A%2F%2Fwww.slideshare.net%2FEyeGloss%2Ftsqloverview&ei=vkqTVNSSEefmyQOmq4KwBg&usg=AFQjCNF
wOLRyKbep7jEJhtiN47RycqVR9w&sig2=QCBJtMxj5nhVGcCC8ubEw&bvm=bv.82001339,d.bGQ
• http://msdn.microsoft.com/en-us/library/ms188929.aspx