Transcript document

Information Technologies
and Microsoft SQL Server
Day 2
by Alper Özpınar
[email protected]
Parts of a database
 Attributes (fields)
 An attribute or field is a component of a record that
describes something about an item.
 Records
 A record is the representation of an individual item.
 Table
 A collection of records
 Database
 A collection of tables and rules for accessing the tables
What is a relational database?
 Originally developed by E.F. Codd in 1970
 Organizes data into tables where each item is
a row and the attributes of the item are in
columns.
 Different from “flat file” databases because
you can define “relationships” between items
in different tables.
Parts of a database
Record
Tables
Attribute/Field
• Records become “rows”
• Attributes/fields become “columns”
• Rules determine the relationship
between the tables and tie the data
together to form a database
Kinds of Relationships
 “One to One”
 One row of a table matches exactly to another

One person, one id number, one address
 “One to Many”
 One row of a table matches many of another

One person, many phone numbers
 “Many to Many”
 One row may match many of another or many
rows match one row of another
Creating a database
 What information are we trying to store?
 How do we describe the information?
 Phone Book/Contact entries









Name
Address
Company
Phone Number
URL/Web Page
Age
Height (in meters)
Birthday
When we added the entry
Data Types
 Binary
 Database specific binary objects
 Pictures, digital signatures, etc.
 Boolean
 True/False values
 Character
 Fixed width or variable size
 Numeric
 Integer, Real (floating decimal point), Money
 Temporal
 Time, Date, Timestamp
Phone Book/Contact Record
Name
Address
Company
Phone Number
URL/Web Page
Age
Height
Birthday
When we added the entry
Character
Character
Character
Character
Character
Integer
Real (float)
Date
Timestamp
Basic SQL Commands
 Creating tables with CREATE
 Adding data with INSERT
 Viewing data with SELECT
 Removing data with DELETE
 Modifying data with UPDATE
 Destroying tables with DROP
SQL Select
 SELECT "column_name" FROM
"table_name“


SELECT * FROM customers
SELECT name,surname FROM customers
SQL Select & Where
 SELECT "column_name"
FROM "table_name"
WHERE "condition“
 SELECT *
FROM employee
WHERE salary>1000
SQL Select & Where & Conditions
 SELECT "column_name"
FROM "table_name"
WHERE "simple condition"
{[AND|OR] "simple condition"}+
 SELECT store_name
FROM Store_Information
WHERE Sales > 1000
OR (Sales < 500 AND Sales > 275)
And & Or









Logical Operations
1 and 1 =1
1 and 0 =0
0 and 1 =0
0 and 0 =0
1 or 1 = 1
0 or 1 = 1
1 or 0 = 1
0 or 0 = 0
SQL Select & IN
 SELECT "column_name"
FROM "table_name"
WHERE "column_name" IN ('value1',
'value2', ...)
 The number of values in the parenthesis
can be one or more, with each values
separated by comma. Values can be
numerical or characters. If there is only
one value inside the parenthesis, this
commend is equivalent to
 WHERE "column_name" = 'value1'
SQL Between
 SELECT "column_name"
FROM "table_name"
WHERE "column_name" BETWEEN
'value1' AND 'value2‘
 SELECT *
FROM Store_Information
WHERE Date BETWEEN 'Jan-06-1999' AND
'Jan-10-1999'
SQL Like
 LIKE allows you to do a search based on a
pattern rather than specifying exactly what is
desired (as in IN) or spell out a range (as in
BETWEEN). The syntax for is as follows:
 SELECT "column_name"
FROM "table_name"
WHERE "column_name" LIKE {PATTERN}
 SELECT *
FROM Store_Information
WHERE store_name LIKE '%AN%'
SQL Like
 'A_Z': All string that starts with 'A', another character,
and end with 'Z'. For example, 'ABZ' and 'A2Z' would
both satisfy the condition, while 'AKKZ' would not
(because there are two characters between A and Z
instead of one).
 'ABC%': All strings that start with 'ABC'. For example,
'ABCD' and 'ABCABC' would both satisfy the
condition.
 '%XYZ': All strings that end with 'XYZ'. For example,
'WXYZ' and 'ZZXYZ' would both satisfy the condition.
 '%AN%': All string that contain the pattern 'AN'
anywhere. For example, 'LOS ANGELES' and 'SAN
FRANCISCO' would both satisfy the condition.
SQL Order By
 SELECT "column_name"
FROM "table_name"
[WHERE "condition"]
ORDER BY "column_name" [ASC, DESC]
 For more columns

ORDER BY "column_name1" [ASC, DESC],
"column_name2" [ASC, DESC]
 SELECT store_name, Sales, Date
FROM Store_Information
ORDER BY Sales DESC
SQL Aggregate Functions
 AVG
 COUNT
 MAX
 MIN
 SUM
 The syntax for using functions is,
 SELECT "function type"("column_name")
FROM "table_name"
 SELECT SUM(Sales) FROM Store_Information
SQL Count
 SELECT COUNT("column_name")
FROM "table_name"
 Select Count(*) from Customers
 Counts the number of rows in table
Count Distinct
 COUNT and DISTINCT can be used together
in a statement to fetch the number of distinct
entries in a table.
 SELECT COUNT(DISTINCT store_name)
FROM Store_Information
SQL Group By
 SELECT "column_name1",
SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1“
 SELECT store_name, SUM(Sales)
FROM Store_Information
GROUP BY store_name
SQL Having
 Instead of using the WHERE clause in the SQL
statement, though, we need to use the HAVING
clause, which is reserved for aggregate functions.
The HAVING clause is typically placed near the end
of the SQL statement, and a SQL statement with the
HAVING clause may or may not include the GROUP
BY clause. The syntax for HAVING is,
 SELECT "column_name1",
SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1"
HAVING (arithmetic function condition)
SQL Having
 SELECT store_name, SUM(sales)
FROM Store_Information
GROUP BY store_name
HAVING SUM(sales) > 1500
SQL Alias
 SELECT "table_alias"."column_name1"
"column_alias"
FROM "table_name" "table_alias"
 Two reason to use
 Change column name
 Take data from two table or table alias
 SELECT A1.store_name Store, SUM(A1.Sales)
"Total Sales"
FROM Store_Information A1
GROUP BY A1.store_name
Joining Tables
 Used for combining data from different tables
SQL Join
 With tables named A1 and A2
 SELECT A1.region_name REGION,
SUM(A2.Sales) SALES
FROM Geography A1, Store_Information
A2
WHERE A1.store_name = A2.store_name
GROUP BY A1.region_name
Different types of JOINs
 “Inner Join”
 Unmatched rows in either table aren’t printed
 “Left Outer Join”
 All records from the “left” side are printed
 “Right Outer Join”
 All records from the “right” side are printed
 “Full Outer Join”
 All records are printed
 Multiple Table Join
 Join records from multiple tables
SQL Union
 The purpose of the SQL UNION command is
to combine the results of two queries
together. In this respect, UNION is somewhat
similar to JOIN in that they are both used to
related information from multiple tables. One
restriction of UNION is that all corresponding
columns need to be of the same data type.
Also, when using UNION, only distinct values
are selected (similar to SELECT DISTINCT).
SQL Union
 [SQL Statement 1]
UNION
[SQL Statement 2]
 we want to find out all
the dates where there
is a sales transaction.
SQL Union
 SELECT Date FROM Store_Information
UNION
SELECT Date FROM Internet_Sales
 Result:
SQL Union ALL
 The purpose of the SQL UNION ALL
command is also to combine the results of
two queries together. The difference between
UNION ALL and UNION is that, while UNION
only selects distinct values, UNION ALL
selects all values.
 The syntax for UNION ALL is as follows:
 [SQL Statement 1]
UNION ALL
[SQL Statement 2]
SQL Union All
SELECT Date FROM Store_Information
UNION ALL
SELECT Date FROM Internet_Sales
Record Operations
SQL Insert
 INSERT INTO table_name (col_name1, …
col_namen)
VALUES (value1, …, valuen)
 INSERT INTO Corvettes(Vette_id,
Body_style, Miles, Year, State)
VALUES (37, 'convertible', 25.5, 1986, 17)
SQL Update
 To change one or more values of a row in a table
UPDATE table_name
SET col_name1 = value1,
…
col_namen = valuen
WHERE col_name = value
 The WHERE clause is the primary key of the row
to be updated
 UPDATE Corvettes
SET Year = 1996
WHERE Vette_id = 17
SQL Delete
 Delete has the following syntax:
delete rel-name where qualification
 Example: Fire all those sailors whose rating
is less than 2.
delete sailors
where rating < 2