SQL

1. 1 SQL Structured Query Language (Simple Query Language) Lecture slides by Dr. Sara Cohen Update by: Haggai Roitman � Winter 2007/8

2. 2 What is the Relational Model? Relation = Table Note: Overloading of the word "relation". Here we DO NOT mean relationships from an ER diagram! Relations have a schema which states: the names of the columns of the relation Relations also have names An instance of a relation is a set of tuples (rows) Can be empty! No duplicates!

3. 3 Query Components A query can contain the following clauses SELECT FROM WHERE GROUP BY HAVING ORDER BY Only SELECT and FROM are mandatory Order of clauses is always as above

4. 4 Basic SQL Query

5. 5 Basic SQL Query

6. 6 Cartesian Product Cartesian product is a binary operation � it gets 2 relations as input The result of a Cartesian product of two relations is a new relation that contains a tuple for each pair of tuples from the two input relation (concatenated). The number of tuples in the results is always the product of the number of tuples in each input relation Denoted with: x

7. 7 Example Relations

8. 8

9. 9 Basic SQL Query

10. 10 Query Without WHERE

11. 11 Query Without Projection

12. 12 Query Without Projection, Without WHERE

13. 13 Example Tables Used

14. 14 What Are You Asking?

15. 15 And What Now?

16. 16 Range Variables

17. 17 A Few SELECT Options Select all columns: SELECT * FROM Sailors S; Rename selected columns: SELECT S.sname AS Sailors_Name FROM Sailors S; Applying functions (e.g., Mathematical manipulations) SELECT (age-5)*2 FROM Sailors S;

18. 18 The WHERE Clause Numerical and string comparison: !=,<>,=, <, >, >=, <=, between(between val1 AND val2) String comparison is according to the alphabetical order! Logical components: AND, OR, NOT Null verification: IS NULL, IS NOT NULL Example: SELECT sname FROM Sailors WHERE age>=40 AND rating IS NOT NULL ;

19. 19 The LIKE Operator A pattern matching operator Basic format: colname LIKE pattern Example: _ is a single character % is 0 or more characters

20. 20 What is this?

21. 21 Are any of these the same?

22. 22 Example Suppose we have the following table instances:

23. 23 Example (cont.)

24. 24 Sailors who�ve reserved two different boats

25. 25 Names of sailors that reserved red boats

26. 26 Color of boats reserved by Bob

27. 27 Order Of the Result The ORDER BY clause can be used to sort results by one or more columns The default sorting of the columns in the ORDER BY clause is in ascending order Can specify ASC or DESC

28. 28 Example

29. 29 Names of sailors that reserved either red or green boats

30. 30 Other Relational Algebra Operators So far, we have seen selection, projection and Cartesian product How do we do operators UNION and MINUS? UNION (A, B) � union of all the elements from the two sets. MINUS(A, B) � all the elements of group A minus elements that also exist in the set B.

31. 31 Sailors who�ve reserved red or green boat

32. 32 Sailors who�ve reserved red and green boat

33. 33 Nested Queries

34. 34 Nested Queries

35. 35 Rewrite the Previous Query Using MINUS

36. 36 Set-Comparison Queries

37. 37 Commands used during the laboratory part of the SQL lecture

38. 38 MySQL � Creating and using a database mysql>CREATE DATABASE Temp; This command creates a new DB in MySQL mysql>USE Temp; To select DB named �Temp� for further working with it

39. 39 MySQL � Tables Creating mysql>SHOW TABLES; Shows the list of all the existing tables in the current DB mysql>CREATE TABLE Sailors( >sid INTEGER PRIMARY KEY, >sname VARCHAR(10), >rating INTEGER, >birthday DATE);

40. 40 MySQL � Tables Creating �cont. mysql>CREATE TABLE Boats( >bid INTEGER PRIMARY KEY, >color VARCHAR(10));

41. 41 MySQL � Tables Creating �cont. mysql>CREATE TABLE Reserves( >sid INTEGER, >bid INTEGER, >day DATE, >PRIMARY KEY(sid, bid, day), >FOREIGN KEY(sid) REFERENCES Sailors(sid), >FOREIGN KEY(bid) REFERENCES Boats(bid));

42. 42 MySQL � Looking into the Tables Schema mysql>DESCRIBE Sailors; Describes the table Sailors (returns its schema)

43. 43 MySQL � drop vs. delete mysql>DROP TABLE Boats; Removes the whole table Boats (with its schema) mysql>DELETE FROM Boats WHERE color = �green�; Will remove all the records that satisfy the condition �color=�green��

44. 44 MySQL � Updating a Table Records We can update rows in a table General format: UPDATE Table SET Field1=value1,,,FieldN=valueN WHERE Cond Now we can increase the city�s population: UPDATE Sailors SET sname = �Bob Johnson� WHERE sid = 12;

45. 45 MySQL � Inserting single records into the table There are two formats used to insert data into table: mysql>INSERT INTO table_name SET -> columnName1 = value1, -> columnName2 = value2, -> ... ->; mysql>INSERT INTO table_name -> (columnName1, columnName2, ...) -> VALUES (value1, value2, ...); 1st Option may not be working in this version

46. 46 Loading data from a text file into the table Loading a large set of records at once per line (values separated by tabs) * Use \N for a null value Inserting the File Data Set mysql> LOAD DATA LOCAL INFILE '/path/file.dat' INTO TABLE Table_Name;

47. 47 References Reference Manual: http://dev.mysql.com/doc/refman/5.0/en/ In the book of Ramakrishnan, chapter 5 For further reading only: chapters 3 (creating tables from ERD) and 4 (the mathematical background of SQL � relational algebra)

48. 48 Asignment #4 (optional!!!) Given the following tables, fill the SQL queries in the next slides:

49. 49 Lab Task I Create DB named TEMP1 Select this DB Create the three Tables from the previous slide and see the schema of the tables Write text files named �suppliers.dat�, �parts.dat�, �catalog.dat� and save them in some directory, the files should include the appropriate test data for the tables Load the data to the tables from the files Try your homework SQL queries and see if you get the correct results, try to fix the queries Now let�s see the correct queries, try to run the queries if they are different from yours

50. 50 The commands: mysql> CREATE DATABASE Temp; Query OK, 1 row affected (0.08 sec) mysql> USE Temp; Database changed mysql> SHOW Tables; Empty set (0.07 sec) mysql> CREATE TABLE Suppliers( -> sid INTEGER PRIMARY KEY, -> sname VARCHAR(10), -> email VARCHAR(12)); Query OK, 0 rows affected (0.19 sec) mysql> CREATE TABLE Parts( -> pid INTEGER PRIMARY KEY, -> pname VARCHAR(10), -> color VARCHAR(3) CHECK(color ="RED" or color = "GREEN" or color = "BLUE") -> );

51. 51 Commands � cont. mysql> CREATE Table Catalog( -> sid INTEGER, -> pid INTEGER, -> cost INTEGER, -> FOREIGN KEY (sid) REFERENCES Suppliers (sid) ON DELETE CASCADE, -> FOREIGN KEY (pid) REFERENCES Parts (pid) ON DELETE CASCADE); Query OK, 0 rows affected (0.52 sec)

52. 52 Creating the data files (in Wordpad) Suppliers.dat: 1 Moshe mmm@tx 2 Tehila teh@gmail 3 SONY sony@com Ericson eric@t2 Parts.dat: 10 Table RED 11 Book GREEN 12 IPhone GREEN 13 TV BLUE Laptop RED

53. 53 Loading the Data to DB: mysql> LOAD DATA LOCAL INFILE 'C:/Documents and Settings/lera/Desktop/suppliers.dat' INTO TABLE Suppliers; Then insert data into Parts and finally into Catalog Start trying your SQL queries!?

54. 54 Query 1 Find the names of suppliers who supply some red part.

55. 55 Query 2 Find the pids of parts supplied by at least two different suppliers

56. 56 Query 3 Find the pids of the most expensive parts supplied by suppliers named Sony

57. 57 Query 1 Find the names of suppliers who supply some red part.

58. 58 Query 2 Find the pids of parts supplied by at least two different suppliers

59. 59 Query 3 Find the pids of the most expensive parts supplied by suppliers named Sony

60. 60 Web Development(General Idea) Communication Protocol (HTTP) HTML Language

61. 61 How Does It all Work?

62. 62 HTML(HyperText Markup Language) Web pages are written in HTML, which defines the style in which the page should be displayed.

63. 63

64. 64

65. 65

66. 66 HTTP

67. 67 Common Protocols In order for two remote machines to �understand� each other they should ��speak the same language�� and coordinate their ��conversation�� The solution is to use protocols, e.g., FTP: File Transfer Protocol SMTP: Simple-Mail Transfer Protocol NNTP: Network-News Transfer Protocol HTTP: HyperText Transfer Protocol

68. 68 The HTTP �Conversation" A Web Browser knows how to send an HTTP request for a resource A Web Server is a program that listens for HTTP requests and knows how to send appropriate HTTP responses There are 2 standard versions of HTTP: HTTP 1.0 and HTTP 1.1

69. 69 Resources and URLs

70. 70 Resources A resource is a chunk of information that can be identified by a URL (Universal Resource Locator) A resource can be A file, e.g., html, text, image A dynamically created page (more about this later on) What we see on the browser can be a combination of some resources When an html page is displayed with images we are actually seeing several resources at once

71. 71 Basic Syntax

72. 72 HTML

73. 73 What is HTML? It is a Markup Language It is used to write web pages: specify the role of different parts of the page and the style that should be used when displaying the page HTML gives authors the means to: Publish online documents with text, images, etc. Retrieve online information via hypertext links Design forms for conducting transactions with remote services, for searching for information, making reservations, ordering products, etc.

74. 74 A simple HTML page

75. 75 A simple HTML page HTML contains text, separated by tags

76. 76 Some General Rules HTML page is surrounded by the html tag 2 Basic parts: Head: Consists of things that describe the document (e.g., title � shown on the browser bar) Body: Consists of the content of the document

77. 77 More General Rules Tags are not case sensitive (<head>, <HEAD>, <Head> are the same) Whitespace in an html document is ignored HTML files should end with .htm or .html In HTML, there is an exception to almost every rule!

78. 78 The <BODY> Headings: <h1>, �, <h6> where h1 is the largest one Paragraphs: <p> (optional closing tag) Line breaks: <br> (no closing tag) Horizontal lines: <hr> (no closing tag) Formatted text: bold <b>, italics <i>, underline <u> Font colors and styles: <font color = �red� face=�Arial�>

79. 79 Another Example <html><head> <title>Example 2</title></head><!-- Here is a comment --> <body> <h1>This is an example of an HTML page</h1> <p>Here is <b>emphasized</b> text and there is also <i>italic</i> text here. <br> Here is a new line </p> <p>Is this <font color=�blue� face=�Arial�>easy</font>? <p><hr>And some parting words... Good Bye </body> </html>

80. 80 Another Example

81. 81 Targil HTML Use the file HTML.zip that I sent to you by e-mail with this ppt Unzip the file in any folder in the TEMP directory Now let�s complete the file, so it will look like the website from the next slide

82. 82 Main Screen List Table Image

83. 83 Links Basic Link: <a href="http://iestud.technion.ac.il">Visit IEStud!</a> Marked Point (an anchor): <a name=�point� href=�URL" >Visit URL!</a> Going to a marked point: <a href=�#point">GoTo point!</a>

84. 84 Frames With frames, you can display more than one HTML document in the same browser window. Each HTML document is called a frame, and each frame is independent of the others Example: <frameset cols="25%,75%"> �� <frame src="frame_a.htm"> �� <frame src="frame_b.htm"> </frameset>

85. 85 List Screen

86. 86 LIST Tags There are two basic kinds of list: <OL> Ordered List </OL> <UL> Unordered List </UL> To add a List Item use the tag <LI> Example: <LI> First Item Bullet Type is defined as below: <UL type=�square�>

87. 87 Table Screen

88. 88 Tables

89. 89 Tables (2) Example: <table border="1"> <tr> <th>Heading</th> <th>Another Heading</th> </tr> <tr> <td>row 1, cell 1</td> <td>row 1, cell 2</td> </tr> <tr> <td>row 2, cell 1</td> <td>row 2, cell 2</td> </tr> </table>

90. 90 The Last Assignment Your Job is to build your homepage (you can use the HTML.zip file or make a completely new HTML file, without frames).

91. 91 The Last Assignment Your website should include: at least one list, at least few pictures ordered in a table, a list of favorite links and if you don�t use frames, then anchors with jumps to the middle and beginning of the page Once you have a tx account, you could place your files in the public_html directory and have your website on tx