Part 6

Chapter 6 l.jpg
1 / 186
0
0
1407 days ago, 623 views
PowerPoint PPT Presentation

Presentation Transcript

Slide 1

Part 6 Structured Query Language

Slide 2

SQL The social administrators characterize reasonable information control capacities. They are not a detail for an information get to dialect. These administrators infer a specific usefulness.

Slide 3

Introduction to SQL IBM in the mid-1970s as SEQUEL SQL is a standard slight varieties among executions information get to dialect that is installed in application programs consequence of a SQL articulation is a connection Transform-arranged dialect

Slide 4

Introduction to SQL Four verbs SELECT, UPDATE, DELETE, INSERT Basic CRUD usefulness Create - INSERT Read - SELECT Update - UPDATE Delete - DELETE

Slide 5

SELECT "We should SELECT the deception which speaks to our disposition, and grasp it with enthusiasm, on the off chance that we need to be upbeat." Cyril Connolly

Slide 6

Introduction to SQL General Form: SELECT [DISTINCT] Item(s) FROM table(s) [WHERE predicate] [GROUP BY field(s) [HAVING predicate]] [ORDER BY field(s)]; [ Optional parameters ]

Slide 7

Simple Retrieval (SELECT) Retrieve lines from SUPPLIER for providers situated in Finland SELECT * FROM SUPPLIER WHERE SUPPLIER.SupplierCity = 'FINLAND';

Slide 8

Simple Retrieval (SELECT) SELECT * FROM SUPPLIER WHERE SUPPLIER.SupplierCity = 'FINLAND'; Do you think utilizing * (all sections) is, all in all, a great practice? In applications? For investigating a table yes, yet installing it in an application is hazardous. Request of segments can change, segment names can change. It's normally best to specify the segment names in the SELECT.

Slide 10

SELECT "What is referred to as achievement expect almost the same number of nom de plumes as there are the individuals who look for it." Stephen Birmingham

Slide 11

Simple Retrieval (SELECT) Often you need to display the segments in the outcome not in their local configuration, but rather in an option, more application particular arrangement. We do this with a segment Alias.

Slide 12

Simple Retrieval (SELECT) SELECT OrderDetail.OrderID FROM [Order Details] AS OrderDetail WHERE OrderDetail.UnitPrice = 14 We can likewise utilize a Table Alias For this situation, we can now reference the Order Details table in the question without the troublesome [ ]'s.

Slide 13

Simple Retrieval (SELECT) I frequently utilize Aliases to curtail long protest names. As a short hand, it is advantageous; yet it makes the inquiry substantially less meaningful and viable. Significant nom de plumes, much the same as significant identifiers in writing computer programs, is imperative.

Slide 14

Simple Retrieval (SELECT) SELECT Orders.OrderID, [Order Details].OrderDate FROM Orders, [Order Details] WHERE Order.OrderID=[Order Details].OrderID AND [Order Details].UnitPrice = 14 Becomes… SELECT O.OrderID, O.OrderDate FROM Orders O, [Order Details] OD WHERE O.OrderID=OD.OrderID AND OD.UnitPrice = 14

Slide 15

Simple Retrieval (WHERE) The WHERE proviso indicates a condition or conditions that confines the lines return in the outcome set.

Slide 16

Simple Retrieval (WHERE) WHERE conditions can be as perplexing as they should be. List the OrderID, OrderDate, and RequiredDate of requests for worker 5, dispatched to the USA, for either clients SAVEA or RATTC, that has a cargo cost surpassing $50. SELECT OrderID, OrderDate, RequiredDate FROM Orders WHERE EmployeeID = 5 AND ShipCountry = "USA" AND (CustomerID = "SAVEA" OR CustomerID = 'RATTC') AND Freight > 50

Slide 17

Simple Retrieval (WHERE) An option method for communicating the OR proviso underneath is with the IN() statement. SELECT OrderID, OrderDate, RequiredDate FROM Orders WHERE EmployeeID = 5 AND ShipCountry = "USA" AND (CustomerID = "SAVEA" OR CustomerID = 'RATTC') AND Freight > 50 Becomes… SELECT OrderID, OrderDate, RequiredDate FROM Orders WHERE EmployeeID = 5 AND ShipCountry = "USA" AND CustomerID IN ('SAVEA','RATTC') AND Freight > 50

Slide 18

Simple Retrieval (WHERE) The IN() proviso is a case of an uncorrelated subquery. The expression assesses to TRUE if the test expression coordinates any qualities in the rundown. test_expression [NOT] IN (value1, value2,… valueN) The rundown can be "hard-coded" as beneath, or it can be the consequence of a SELECT explanation. The main confinement is that the SELECT proclamation needs to give back a counted list, not a framework (a rundown of qualities versus a table). SELECT OrderID, OrderDate, RequiredDate FROM Orders WHERE EmployeeID = 5 AND ShipCountry = "USA" AND CustomerID IN ('SAVEA','RATTC') AND Freight > 50

Slide 19

Simple Retrieval (WHERE) test_expression [NOT] IN (value1, value2,… valueN) The rundown can be "hard-coded" as underneath, or it can be the aftereffect of a SELECT articulation. The main limitation is that the SELECT explanation needs to give back an identified rundown, not a framework (a rundown of qualities versus a table). SELECT OrderID, OrderDate, RequiredDate FROM Orders WHERE EmployeeID = 5 AND ShipCountry = "USA" AND CustomerID IN (SELECT CustomerID FROM Customers WHERE Condition = esteem) AND Freight > 50 We're going to invest significantly more energy in uncorrelated subqueries later…

Slide 20

Simple Retrieval (WHERE) Speaking of complex WHERE provisions, how might you answer this question? List the EmployeeID, OrderID, and OrderDate for Employees 1 and 2 where they have orders put around the same time.

Slide 21

Simple Retrieval (WHERE) How might you yield the information with the end goal that the worker records were consecutive as opposed to next to each other? List the EmployeeID, OrderID, and OrderDate for Employees 1 and 2 where they have orders put around the same time.

Slide 22

Simple Retrieval (WHERE) "The contrast BETWEEN the right word and the right word is the distinction BETWEEN lightning and a lightning bug. " Mark Twain

Slide 23

Simple Retrieval (WHERE) The BETWEEN proviso indicates a scope of qualities to test against the test expression. test_expression [NOT] BETWEEN begin_value AND end_value

Slide 24

Simple Retrieval (WHERE) "All ladies get to be LIKE their moms. That is their catastrophe. No man does. That is his. " Oscar Wilde

Slide 25

Simple Retrieval (WHERE) The LIKE proviso performs character design coordinating. match_expression [NOT] LIKE example List the CustomerID and Phone number of clients that have a telephone number with region code (503).

Slide 26

Simple Retrieval (WHERE) The LIKE statement performs character design coordinating. Give back all the Quarterly Productivity comes about: WHERE Report LIKE "Q_ProductivityResult"

Slide 27

Simple Retrieval (WHERE) A word about NULL… "The very difficulty in which I wind up to demonstrate that God is not, finds to me his presence ." Voltaire

Slide 28

Simple Retrieval (WHERE) NULL infers that a property estimation has not been provided. It is not vacant string or zero. It has no presence. Allowing NULL "qualities" in your database presents equivocalness with respect to the "presence" of an esteem. Invalid can mean: The esteem is obscure The esteem is not proper The esteem is known to be clear

Slide 29

Simple Retrieval (WHERE) DBMS's give a NULL capacity to check for NULL qualities. In SQL Server this capacity IS [NOT] NULL List the Employee name of the worker who doesn't answer to anybody.

Slide 30

Simple Retrieval (WHERE) List the Employee name of the worker who doesn't answer to anybody. Ought to this be NULL? What does it imply that Fuller has a Null esteem for ReportsTo?

Slide 31

Simple Retrieval (WHERE) " ORDER is never watched; it is turmoil that draws in consideration since it is cumbersome and meddlesome." Eliphas Levi "

Slide 32

Simple Retrieval (ORDER BY) Although the request of lines and sections is insignificant in a "genuine" connection, the request of the outcome set is. Arrange BY indicates the sort for the outcome set. Arrange BY { order_by_expression [ ASC | DESC ] ORDER BY can sort on different segments.

Slide 33

Simple Retrieval (ORDER BY)

Slide 34

Simple Retrieval (ORDER BY) Note that the sort fields don't need to show up in the SELECT articulation

Slide 35

Simple Retrieval (ORDER BY) The enhancer first did a sweep of the accessible lists. SQL Server consequently makes a record on the essential key. In the event that the information are to be sorted by a field habitually, you would need to make and list on that field to enhance sorting execution.

Slide 36

Simple Retrieval (Built-in Functions) Also known as total capacities, these capacities perform standard computations on columns or gatherings of lines of records. Total capacities give back a solitary esteem. Except for COUNT, total capacities disregard NULLs. A portion of the capacities in SQL Server are: AVG, COUNT, MAX, MIN, SUM

Slide 37

Simple Retrieval (Built-in Functions) Display the normal item UnitPrice. SELECT AVG(UnitPrice) AverageUnitPrice FROM Products Display The quantity of requests set by a particular client.

Slide 38

Simple Retrieval (Built-in Functions) What is the aggregate deals for a particular item?

Slide 39

Simple Retrieval (Built-in Functions) What is the most elevated and least UnitPrice in the Products table?

Slide 40

Simple Retrieval (Built-in Functions) List the ProductID and it's UnitPrice for the item that has the most elevated UnitPrice. Why doesn't this work?

Slide 41

Simple Retrieval (Built-in Functions) Here's an additionally difficult one. List the normal number of years the workers in the representative table with job_lvl more noteworthy than 200 have been with the distributer (expecting nobody has resigned). That is, figure the distinction between the hire_date and the present date in years and take the normal.

Slide 42

Simple Retrieval (GROUP BY) It is frequently the case that we need to perform total capacities on gatherings of records. Aggregate BY partitions a table into gatherings. Gatherings can comprise of section names or results or comp

SPONSORS