1 / 26

SQL Language

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.

muncel
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. SQL Language

  2. 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

  3. 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?

  4. 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

  5. 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

  6. The SELECT and FROM clauses Return the set of allrows and columns from customers table SELECT * FROM CUSTOMERS

  7. Limiting columns Return the set of allrows for just the CustomerID and CompanyName columns from customers table SELECT CustomerID, CompanyNameFROM CUSTOMERS

  8. 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

  9. 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')

  10. 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

  11. 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

  12. Aliases SELECT newName= CompanyName FROM customers SELECT CompanyName AS newName FROM customers Check your DBMS for this: SELECT CompanyName newName FROM customers

  13. 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

  14. Nulls, null ability select * from customers where region = null select * from customers where region is null select CustomerID, region = 'REG_' + region from customers

  15. Data Manipulation - Changing Data • The Insert Statement • The Update Statement • The Delete Statement

  16. 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

  17. The Update Statement UPDATE Customers SET ContactName = 'Yair S. Siwek' WHERE CustomerID = 'YAISH‘ UPDATE Products SET price = price * 1.1

  18. The Delete Statement DELETE FROM Customers WHERE CustomerID = 'YAISH'

  19. Data Manipulation - Joins • Inner Join • CROSS Joins

  20. 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

  21. 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

  22. Creating Data Tables and Referential Integrity • Table definition • 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 )

  23. Summarized Queries • Aggregate Functions • GROUP BY and HAVING clauses

  24. 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 . . .

  25. 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%'

  26. 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

More Related