sql - dml. data manipulation language(dml) are used for managing data: –select retrieve data from...
TRANSCRIPT
![Page 1: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/1.jpg)
SQL - DML
![Page 2: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/2.jpg)
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
![Page 3: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/3.jpg)
INSERT
• Simple functionality – insert a row of data into a specified table:
INSERT INTO <tableName>VALUES (<valuelist>)
• Example:INSERT INTO hotel VALUES (1,'The Pope','Vaticanstreet 1
1111 Bishopcity');
![Page 4: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/4.jpg)
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
![Page 5: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/5.jpg)
UPDATE
• Updates the value(s) for specified field(s), for the rows mathcing a given condition
UPDATE <tableName>
SET field1 = value1, field2 = value2,…
WHERE <condition>
![Page 6: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/6.jpg)
UPDATE
• Example:
update hotel
set name ='The Great Pope'
where hotel_no= 1;
![Page 7: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/7.jpg)
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…
![Page 8: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/8.jpg)
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;
![Page 9: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/9.jpg)
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;
![Page 10: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/10.jpg)
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;
![Page 11: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/11.jpg)
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”
![Page 12: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/12.jpg)
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
![Page 13: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/13.jpg)
HotelDB
• HOTEL: (Hotel_No, Name, Address)
• ROOM: (Room_No, Hotel_No, Types, Price)
• BOOKING: (BokingID, Hotel_No, Guest_No, Date_From, Date_To, Room_No)
• GUEST: (Guest_No, Name, Address)
![Page 14: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/14.jpg)
Table: Guest
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 16A, 4000 Roskilde
10 KingKong Hyrdevænget 38, 4000 Roskilde
![Page 15: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/15.jpg)
SQL query
• The most basic SQL query looks like:
SELECT <fieldlist>
FROM <tablename>
Which fields do I want
From what table do I want the fields
![Page 16: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/16.jpg)
SELECT Guest_no, Name FROM Guest
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 16A, 4000 Roskilde
10 KingKong Hyrdevænget 38, 4000 Roskilde
![Page 17: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/17.jpg)
SELECT * FROM Guest
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 16A, 4000 Roskilde
10 KingKong Hyrdevænget 38, 4000 Roskilde
![Page 18: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/18.jpg)
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
![Page 19: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/19.jpg)
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)
![Page 20: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/20.jpg)
Table: Guest
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 16A, 4000 Roskilde
10 KingKong Hyrdevænget 38, 4000 Roskilde
SELECT *FROM GuestWHERE Guest_No < 5
![Page 21: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/21.jpg)
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 expres-sions in C#
• Use () to make expressions easier to read, and/or to ”overrule” evaluation rules
![Page 22: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/22.jpg)
Table: Guest
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 16A, 4000 Roskilde
10 KingKong Hyrdevænget 38, 4000 Roskilde
SELECT NameFROM GuestWHERE Guest_No < 5
![Page 23: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/23.jpg)
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))
![Page 24: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/24.jpg)
SQL query - range
• Another notation for range seach uses the keyword BETWEEN
SELECT *
FROM Guest
WHERE Guest_no BETWEEN 1 AND 6
![Page 25: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/25.jpg)
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
![Page 26: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/26.jpg)
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– 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.
![Page 27: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/27.jpg)
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’)
![Page 28: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/28.jpg)
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’))
![Page 29: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/29.jpg)
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’)
![Page 30: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/30.jpg)
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
![Page 31: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/31.jpg)
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
![Page 32: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/32.jpg)
SQL query – pattern match
• A pattern is formulated using two special characters % and _
• % : wildcard: any sequence of zero or more characters
• _ : any single character
![Page 33: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/33.jpg)
SQL query – pattern match
Pattern Meaning
’s%’ Any string starting with ’S’, of any length (at least 1)
(’super’, ’s’, ’s123’, ’s 123’)
’s_ _ _’ Any string starting with ’S’, of length exactly 4
(’such’, ’s123’, ’ssss’, ’s 1’)
’%s’ Any string ending with ’s’, of any length (at least 1)
(’Spurs’, ’s’, ’123s’, ’ s’, ’1 2s’)
’%s%’ Any string containing an ’s’, of any length (at least 1)
(’Spurs’, ’s’, ’basin’, ’ s ’, ’12s34’)
’%s_ _ _% Exercise…
![Page 34: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/34.jpg)
SQL query – pattern match
SELECT *
FROM Guest
WHERE Name LIKE ’P%’
SELECT *
FROM Guest
WHERE Name LIKE ’_ _ _ _’
![Page 35: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/35.jpg)
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%'
![Page 36: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/36.jpg)
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 ”unde-fined” or null value, if it makes sense
SELECT *FROM GuestWHERE Address IS NULL
![Page 37: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/37.jpg)
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
![Page 38: 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](https://reader036.vdocuments.us/reader036/viewer/2022081603/56649f165503460f94c2cf5e/html5/thumbnails/38.jpg)
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