SQL LanguageSQL Language

SQL Language SQL Language

Introduction to RDBMS Introduction to RDBMS Basic Data Manipulation - Reading Data Basic Data Manipulation - Reading Data Basic Data Manipulation - Changing Data Basic Data Manipulation - Changing Data Data Manipulation - Joins Data Manipulation - Joins Creating Data Tables and Referential Creating Data Tables and Referential

Integrity Integrity Optional:Optional:

Defining and Using Views Defining and Using Views Summarized Queries Summarized Queries

Introduction to RDBMSIntroduction to RDBMS

The need for SQL standard The need for SQL standard What is a relational database? What is a relational database? Components of a relational database Components of a relational database What does What does

un-normalized/normalized data un-normalized/normalized data means means

Anatomy of a table, Keys and Joins Anatomy of a table, Keys and Joins What is set oriented? What is set oriented?

How would you?1. Add new customer that has no orders yet.2. Remove Order 10445 but keep the customer ‘BERGS’.3. Fix ‘Alfreds Futterkiste’ without having to do it in many

places.

Select Customers.CustomerID , Customers.CompanyName, Orders.OrderID, rders.Freight FROM Customers join Orders on Customers.CustomerID = Orders.CustomerID

What is a relational What is a relational database?database?

‘Orders’ table‘Customers’ table

CustomerID CompanyName OrderID Freight

ALFKI Alfreds Futterkiste 10692 61.02

ALFKI Alfreds Futterkiste 10702 23.94

BERGS Berglunds snabbköp 10445 9.3

CHOPS Chop-suey Chinese 10254 22.98

CustomerID CompanyName

ALFKI Alfreds Futterkiste

BERGS Berglunds snabbköp

CHOPS Chop-suey Chinese

OrderID CutomerID Freight

10692 ALFKI 61.02

10702 ALFKI 23.94

10445 BERGS 9.3

10254 CHOPS 22.98

Data Manipulation - Data Manipulation - Reading DataReading Data

The SELECT and FROM clauses The SELECT and FROM clauses Limiting columns Limiting columns Limiting rowsLimiting rows Calculating Columns Calculating Columns Built-in functions Built-in functions Aliases Aliases Sorting the result set Sorting the result set Nulls, null ability Nulls, null ability

The SELECT and FROM The SELECT and FROM clausesclauses

Return the set of all rows and columns from customers table

SELECT * FROM CUSTOMERS

Limiting columnsLimiting columns

Return the set of all rows for just the CustomerID and CompanyName columns from customers table

SELECT CustomerID, CompanyName FROM CUSTOMERS

Limiting RowsLimiting Rows

Select * from Customers where CustomerID = 'ALFKI' Select * from Orders where CustomerID = 'ALFKI'

Select * from Customers where CustomerID like 'A%'

Select * from Orders where freight > 800.00

Select * from Orders where freight > 101.95 and freight < 102.55

Limiting Rows - Limiting Rows - ContinuedContinued

Select * from Orders where freight >= 101.95 and freight <= 102.55

Select * from Orders where freight between 101.95 and 102.55

select * from customers where CustomerID between 'A%' and 'C%'

select * from customers where CustomerID = 'ALFKI' OR CustomerID = 'BERGS' OR CustomerID = 'FOLIG'

select * from customers where CustomerID IN ('ALFKI', 'BERGS', 'FOLIG')

Calculating ColumnsCalculating Columns

Return the set of all rows for the OrderID, ShipCity, and a new calculated Freight columns from orders table

select OrderID, ShipCity, Freight = Freight * 1.1 from orders

Built-in functionsBuilt-in functionsBuilt-in functions – Standard

SELECT CustomerID, Substring (CompanyName,1,3) AS CompanyNameInitial FROM Customers

Built-in functions – non Standard

SELECT OrderID, Datepart(qq, OrderDate ) OrderDate_qtr FROM orders

AliasesAliases

SELECT newName= CompanyName FROM customers

SELECT CompanyName AS newName FROM customers

Check your DBMS for this: SELECT CompanyName newName FROM customers

Sorting the result setSorting the result set

SELECT OrderID, CustomerID, Freight FROM orders order by CustomerID

SELECT OrderID, CustomerID, Freight FROM orders order by CustomerID DESC

SELECT OrderID, CustomerID, Freight FROM orders order by CustomerID ASC

SELECT OrderID, CustomerID, Freight FROM orders order by CustomerID, Freight DESC

Nulls, null abilityNulls, null ability

select * from customers where region = null

select * from customers where region is null

select CustomerID, region = 'REG_' + region from customers

Data Manipulation - Data Manipulation - Changing DataChanging Data

The Insert Statement The Insert Statement The Update Statement The Update Statement The Delete Statement The Delete Statement

The Insert StatementThe Insert Statement

INSERT INTO Customers (CustomerID, CompanyName,ContactName, Phone)VALUES ('YAISH','Siwek Consulting', 'Yair Siwek', '972-(0)58-808-598')

INSERT INTO Customers SELECT * FROM myTempCustomerList

The Update StatementThe Update Statement

UPDATE Customers SET ContactName = 'Yair S. Siwek'WHERE CustomerID = 'YAISH‘

UPDATE Products SET price = price * 1.1

The Delete StatementThe Delete Statement

DELETE FROM CustomersWHERE CustomerID = 'YAISH'

Data Manipulation - Data Manipulation - JoinsJoins

Inner Join Inner Join CROSS Joins CROSS Joins

Inner JoinInner JoinSELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.FreightFROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.FreightFROM Customers, Orders WHERE Customers.CustomerID = Orders.CustomerID

CROSS JoinsCROSS Joins

SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.FreightFROM Customers CROSS JOIN Orders

Select COUNT(*) FROM CustomersSelect COUNT(*) FROM OrdersSelect COUNT(*) FROM Customers, Orders

Creating Data Tables and Creating Data Tables and Referential IntegrityReferential Integrity

Table definition Table definition Referential integrity rules Referential integrity rules

CREATE TABLE OrdersSampel(

OrderID INTEGER NOT NULL,CustomerID NVARCHAR(5) NOT NULL,Freight MONEY NULL,

PRIMARY KEY (OrderID),FOREIGN KEY (CustomerID)

REFERENCES Customers )

Summarized QueriesSummarized Queries

Aggregate Functions Aggregate Functions GROUP BY and HAVING clauses GROUP BY and HAVING clauses

Summarized Queries – Summarized Queries – Aggregate FunctionsAggregate Functions

SELECT SUM (Orders.Freight) AS TotalFreight from Orders

SELECT MAX (Orders.Freight) AS MaxFreight from Orders

SELECT MIN (Orders.Freight) AS MinFreight from Orders...

Summarized Queries – Summarized Queries – GROUP BY and HAVING GROUP BY and HAVING

clausesclauses

SELECT CustomerID, SUM(Orders.Freight) AS TotalFreight FROM OrdersGroup By CustomerID

SELECT CustomerID, SUM(Orders.Freight) AS TotalFreight FROM OrdersGroup By CustomerID HAVING CustomerID LIKE 'A%'

ReviewReview

Introduction to RDBMS Introduction to RDBMS Basic Data Manipulation - Reading Data Basic Data Manipulation - Reading Data Basic Data Manipulation - Changing Basic Data Manipulation - Changing

Data Data Data Manipulation - Joins Data Manipulation - Joins Creating Data Tables and Referential Creating Data Tables and Referential

Integrity Integrity Optional:Optional:

Summarized Queries Summarized Queries