Sql language
This presentation is the property of its rightful owner.
Sponsored Links
1 / 26

SQL Language PowerPoint PPT Presentation


  • 78 Views
  • Uploaded on
  • Presentation posted in: General

SQL Language. SQL Language. Introduction to RDBMS Basic Data Manipulation - Reading Data Basic Data Manipulation - Changing Data Data Manipulation - Joins Creating Data Tables and Referential Integrity Optional: Defining and Using Views Summarized Queries. Introduction to RDBMS.

Download Presentation

SQL Language

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Sql language

SQL Language


Sql language1

SQL Language

  • Introduction to RDBMS

  • Basic Data Manipulation - Reading Data

  • Basic Data Manipulation - Changing Data

  • Data Manipulation - Joins

  • Creating Data Tables and Referential Integrity

  • Optional:

    • Defining and Using Views

    • Summarized Queries


Introduction to rdbms

Introduction to RDBMS

  • The need for SQL standard

  • What is a relational database?

  • Components of a relational database

  • What does un-normalized/normalized data means

  • Anatomy of a table, Keys and Joins

  • What is set oriented?


What is a relational database

What is a relational database?

  • How would you?

  • Add new customer that has no orders yet.

  • Remove Order 10445 but keep the customer ‘BERGS’.

  • Fix ‘Alfreds Futterkiste’ without having to do it in many places.

‘Customers’ table

‘Orders’ table

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


Data manipulation reading data

Data Manipulation - Reading Data

  • The SELECT and FROM clauses

  • Limiting columns

  • Limiting rows

  • Calculating Columns

  • Built-in functions

  • Aliases

  • Sorting the result set

  • Nulls, null ability


The select and from clauses

The SELECT and FROM clauses

Return the set of allrows and columns from customers table

SELECT * FROM CUSTOMERS


Limiting columns

Limiting columns

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

SELECT CustomerID, CompanyNameFROM CUSTOMERS


Limiting rows

Limiting 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 continued

Limiting Rows - Continued

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 columns

Calculating 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 functions

Built-in functions

Built-in functions – Standard

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

Built-in functions – nonStandard

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


Aliases

Aliases

SELECT newName= CompanyName FROM customers

SELECT CompanyName AS newName FROM customers

Check your DBMS for this:

SELECT CompanyName newName FROM customers


Sorting the result set

Sorting 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 ability

Nulls, null ability

select * from customers where region = null

select * from customers where region is null

select CustomerID, region = 'REG_' + region

from customers


Data manipulation changing data

Data Manipulation - Changing Data

  • The Insert Statement

  • The Update Statement

  • The Delete Statement


The insert statement

The Insert Statement

INSERTINTO Customers (CustomerID, CompanyName,ContactName, Phone)

VALUES('YAISH','Siwek Consulting', 'Yair Siwek', '972-(0)58-808-598')

INSERTINTO Customers SELECT * FROM myTempCustomerList


The update statement

The Update Statement

UPDATE Customers SET ContactName = 'Yair S. Siwek'

WHERE CustomerID = 'YAISH‘

UPDATE Products SET price = price * 1.1


The delete statement

The Delete Statement

DELETE FROMCustomers

WHERE CustomerID = 'YAISH'


Data manipulation joins

Data Manipulation - Joins

  • Inner Join

  • CROSS Joins


Inner join

Inner Join

SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.Freight

FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID

SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.Freight

FROM Customers, Orders WHERECustomers.CustomerID = Orders.CustomerID


Cross joins

CROSS Joins

SELECT Customers.CustomerID, Customers.CompanyName, Orders.OrderID, Orders.Freight

FROM Customers CROSS JOIN Orders

Select COUNT(*) FROM Customers

Select COUNT(*) FROM Orders

Select COUNT(*) FROM Customers, Orders


Creating data tables and referential integrity

Creating Data Tables and Referential Integrity

  • Table definition

  • Referential integrity rules

CREATE TABLE OrdersSampel

(

OrderIDINTEGER NOT NULL,

CustomerIDNVARCHAR(5)NOT NULL,

FreightMONEYNULL,

PRIMARY KEY (OrderID),

FOREIGN KEY (CustomerID)

REFERENCES Customers

)


Summarized queries

Summarized Queries

  • Aggregate Functions

  • GROUP BY and HAVING clauses


Summarized queries aggregate functions

Summarized Queries – Aggregate 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 group by and having clauses

Summarized Queries – GROUP BY and HAVING clauses

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

Group By CustomerID

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

Group By CustomerID

HAVING CustomerID LIKE 'A%'


Review

Review

  • Introduction to RDBMS

  • Basic Data Manipulation - Reading Data

  • Basic Data Manipulation - Changing Data

  • Data Manipulation - Joins

  • Creating Data Tables and Referential Integrity

  • Optional:

    • Summarized Queries


  • Login