SQL DML Data Manipulation LanguageDML Are used for

SQL - DML

Data Manipulation Language(DML) • Are used for managing data: – SELECT • retrieve data from the a database – INSERT • insert data into a table – UPDATE • updates existing data within a table – DELETE • deletes all records from a table

INSERT • Simple functionality – insert a row of data into a specified table: INSERT INTO <table. Name> VALUES (<valuelist>) • Example: INSERT INTO hotel VALUES (1, 'The Pope', 'Vaticanstreet 1 1111 Bishopcity');

INSERT • Things to notice about INSERT – The value list must match the field list for the table into which the record is inserted – If we try to insert a record with a key field which already exists, we will get an error – Null values can be inserted if the table definition allows it – The field list can be specified explicitly

UPDATE • Updates the value(s) for specified field(s), for the rows mathcing a given condition UPDATE <table. Name> SET field 1 = value 1, field 2 = value 2, … WHERE <condition>

UPDATE • Example: update hotel set name ='The Great Pope' where hotel_no= 1;

UPDATE • Things to notice about UPDATE – For each field update, the type of the value must match the type of the field – The WHERE clause is optional – if you leave it out, all records in the table are updated! – It is not considered an error if zero rows are changed, so pay attention to the condition in the WHERE clause…

Delete • SQL syntax> – DELETE FROM table_name WHERE some_column=some_value • Delete all rows from GUEST : – DELETE FROM Guest; • Delete all rooms from Hotel with Hotel_No=1: – DELETE FROM Room where Hotel_no = 1;

What if? • Try this command: – Delete from hotel where hotel_no = 1; • Can you delete the following tables in this order: – DELETE FROM hotel; – DELETE FROM Room; – DELETE FROM Guest; – DELETE FROM Booking;

What if? • Example 1: update hotel set hotel_no = 100 where hotel_no= 1; • Example 2: update guest set guest_no = 10 where guest_no= 1;

Exercises • With the data in place, run the below commands on the database – – INSERT INTO Hotel VALUES ( specify your own values) INSERT INTO Room VALUES (specify your own values) UPDATE Booking SET Price = Price* 1. 30; DELETE FROM Room WHERE (Room_no = 8) • Now formulate commands yourself, in order to: – Insert data about ” Scandic Roskilde” in the table Hotel (you can find the data on the Internet, or make it up yourself) – Insert data representing the fact that Hotel Scandic have 10 rooms with room numbers 101, 102, 103, 201, 202, 203, 301, 302, 303, 400 – Update the name of the Hotel ”Scandic Roskilde” to ”The new Scandic Roskilde” – Insert data for a booking of a room at the hotel ”THe new Scandic Roskilde”

SQL query • An SQL query is an SQL statement, which specifies a subset of the data in the database • A subset in terms of – Tables – Fields – Conditions on fields

Hotel. DB • HOTEL: (Hotel_No, Name, Address) • ROOM: (Room_No, Hotel_No, Types, Price) • BOOKING: (Boking. ID, Hotel_No, Guest_No, Date_From, Date_To, Room_No) • GUEST: (Guest_No, Name, Address)

Table: Guest_No Name Address 1 Eva Paradisvej 3, 1111 Bispeborg 2 Adam Paradisvej 7, 1111 Bispeborg 3 Goeg Sunset Blvd. 8, 2222 Hjemby 4 Gokke Sunset Blvd. 8, 2222 Hjemby 5 Fy Klovnevej 87, 3333 Lilleby 6 Bi Bredgade 198, 3333 Lilleby 7 Romeo Kaerlighedstunellen 1, 4444 Borgerslev 8 Julie Kaerlighedstunellen 2, 4444 Borgerslev 9 Godzilla Dommervænget 16 A, 4000 Roskilde 10 King. Kong Hyrdevænget 38, 4000 Roskilde

SQL query • The most basic SQL query looks like: Which fields do I want SELECT <fieldlist> FROM <tablename> From what table do I want the fields

SELECT Guest_no, Name FROM Guest_No Name Address 1 Eva Paradisvej 3, 1111 Bispeborg 2 Adam Paradisvej 7, 1111 Bispeborg 3 Goeg Sunset Blvd. 8, 2222 Hjemby 4 Gokke Sunset Blvd. 8, 2222 Hjemby 5 Fy Klovnevej 87, 3333 Lilleby 6 Bi Bredgade 198, 3333 Lilleby 7 Romeo Kaerlighedstunellen 1, 4444 Borgerslev 8 Julie Kaerlighedstunellen 2, 4444 Borgerslev 9 Godzilla Dommervænget 16 A, 4000 Roskilde 10 King. Kong Hyrdevænget 38, 4000 Roskilde

SELECT * FROM Guest_No Name Address 1 Eva Paradisvej 3, 1111 Bispeborg 2 Adam Paradisvej 7, 1111 Bispeborg 3 Goeg Sunset Blvd. 8, 2222 Hjemby 4 Gokke Sunset Blvd. 8, 2222 Hjemby 5 Fy Klovnevej 87, 3333 Lilleby 6 Bi Bredgade 198, 3333 Lilleby 7 Romeo Kaerlighedstunellen 1, 4444 Borgerslev 8 Julie Kaerlighedstunellen 2, 4444 Borgerslev 9 Godzilla Dommervænget 16 A, 4000 Roskilde 10 King. Kong Hyrdevænget 38, 4000 Roskilde

SQL query • A slightly more complex SQL statement looks like: SELECT <fieldlist> FROM <tablename> WHERE <condition> Which fields do I want From what table do I want the fields What conditions must the fields fulfill

SQL query • The WHERE part is a logical expression, specifying conditions on certain fields • Five fundamental types of criteria – Comparison (<, > , =) – Range (< AND >) – Set membership (belongs to a set of values) – Pattern match (for string fields) – Null (is the value of the field a null value)

Table: Guest_No Name Address 1 Eva Paradisvej 3, 1111 Bispeborg 2 Adam Paradisvej 7, 1111 Bispeborg 3 Goeg Sunset Blvd. 8, 2222 Hjemby 4 Gokke Sunset Blvd. 8, 2222 Hjemby 5 Fy Klovnevej 87, 3333 Lilleby 6 Bi Bredgade 198, 3333 Lilleby 7 Romeo 8 Julie 9 Godzilla 10 King. Kong SELECT * 1, 4444 Borgerslev Kaerlighedstunellen 2, 4444 Borgerslev FROM Guest Dommervænget 16 A, 4000 Roskilde WHERE Guest_No < 5 Hyrdevænget 38, 4000 Roskilde

SQL query • Note that we can build arbitrarily complex logical expressions, using the usual logical operators: AND, OR, NOT • Rules are the same as for logical expressions in C# • Use () to make expressions easier to read, and/or to ”overrule” evaluation rules

Table: Guest_No Name Address 1 Eva Paradisvej 3, 1111 Bispeborg 2 Adam Paradisvej 7, 1111 Bispeborg 3 Goeg Sunset Blvd. 8, 2222 Hjemby 4 Gokke Sunset Blvd. 8, 2222 Hjemby 5 Fy Klovnevej 87, 3333 Lilleby 6 Bi Bredgade 198, 3333 Lilleby 7 Romeo 8 Julie 9 Godzilla 10 King. Kong SELECT Name Kaerlighedstunellen 1, 4444 Borgerslev FROM Guest Kaerlighedstunellen 2, 4444 Borgerslev Dommervænget 16 A, 4000 Roskilde < 5 WHERE Guest_No Hyrdevænget 38, 4000 Roskilde

SQL query - range • A range search is an SQL query where a value should be within a certain range • Actually just a two-part comparision query SELECT * FROM Guest WHERE ((Guest_no <= 6) AND (Guest_no >= 3))

SQL query - range • Another notation for range seach uses the keyword BETWEEN SELECT * FROM Guest WHERE Guest_no BETWEEN 1 AND 6

SQL query - range • We can create a ”negated” version of a range query using NOT BETWEEN SELECT * FROM Guest WHERE Guest_no NOT BETWEEN 1 AND 6

Exercise – SQL queries • Now formulate queries yourself, in order to retrieve the below data: – Get all fields for rooms where the type is ’F’ in the hotel with Hotel_no = 1 – Get all fields for rooms that are not a ’F’ family or a ’D’ double room – Get all bookings that are after the 15. 3. 2011 but allso before the 15. 4. 2011 – Get all bookings for hotel_no = 1 and guest_no = 2 that are after the 15. 3. 2011 but allso before the 15. 4. 2011 – Get all booking for Hotel_no = 2 • Formulate your queries.

SQL query – set membership • A set membership search is an SQL query where a value must belong to a given set of values • We use the IN keyword SELECT * FROM Guest WHERE Name IN (’Adam’, ’Eva’)

SQL query – set membership • Note that these two queries are equivalent SELECT * FROM Guest WHERE Name IN (’Adam’, ’Eva’) SELECT * FROM Guest WHERE ((Name = ’Adam’) OR (Name = ’Eva’))

SQL query – set membership • We can create a ”negated” version of a set membership query using NOT IN SELECT * FROM Guest WHERE Name NOT IN (’Adam’, ’Eva’)

Exercise – SQL queries • Now formulate queries yourself, in order to retrieve the below data: – Get all guests from where hotel_no 1, 3, 4 – Get all rooms from hotel_no 1 that are not a double og family room – Get all guest that did not book a room in the period 15. 3. 2011 to 15. 4. 2011 • Formulate your own queries

SQL query – pattern match • A pattern match search is an SQL query where a (string) value must match a given pattern • We use the LIKE keyword • The hard part is choosing the correct pattern to match against – several ways to formulate a pattern

SQL query – pattern match • A pattern is formulated using two special characters % and _ • % : wildcard: any sequence of zero or more characters • _ : any single character

SQL query – pattern match Pattern Meaning ’s%’ Any string starting with ’S’, of any length (at least 1) (’super’, ’s 123’, ’s 123’) ’s_ _ _’ Any string starting with ’S’, of length exactly 4 (’such’, ’s 123’, ’ssss’, ’s 1’) ’%s’ Any string ending with ’s’, of any length (at least 1) (’Spurs’, ’ 123 s’, ’ 1 2 s’) ’%s%’ Any string containing an ’s’, of any length (at least 1) (’Spurs’, ’basin’, ’ s ’, ’ 12 s 34’) ’%s_ _ _% Exercise…

SQL query – pattern match SELECT * FROM Guest WHERE Name LIKE ’P%’ SELECT * FROM Guest WHERE Name LIKE ’_ _ _ _’

SQL query – pattern match • We can create a ”negated” version of a pattern match query using NOT LIKE SELECT * FROM Hotel WHERE Name NOT LIKE 'D%'

SQL query – null • A null search is an SQL query where a value must be a null value • We use the IS NULL keyword • A null value…? • We may allow a field to have an ”undefined” or null value, if it makes sense SELECT * FROM Guest WHERE Address IS NULL

SQL query – pattern match • We can create a ”negated” version of a null query using IS NOT NULL SELECT * FROM Guest WHERE Address IS NOT NULL

Exercise – SQL queries • With the data in place, run the below queries on the database – – SELECT * FROM Hotel WHERE name LIKE ’%D%’ SELECT * FROM hotel WHERE Address LIKE '%n‘ SELECT * FROM Hotel WHERE Address LIKE '%_ _ _%' SELECT * FROM Booking WHERE Date_From IS NOT NULL • Now formulate queries yourself, in order to retrieve the below data: – Get all hotels from Roskilde – Get all hotels – Get Bookings that have a date for Date_from but not for Date_to (insert a new row to test it) – Get all Hotels with a name starting with ’P’ and have a length of 4 characters – Get all Hotels containing a ’P’ or a ’p’ • Formulate your own queries