SQL Basics Part 2 DML Action Queries The

SQL Basics, Part 2

DML: Action Queries • The SELECT queries that you have seen so far are a part of DQL, Data Query Language, one of the four parts of SQL. • SELECT queries can display data from tables in a variety of ways, but they do not modify the data. • Data Manipulation Language, DML, is the part of SQL which is used to actually modify the data in the tables. DML queries are also known as “action” queries. • There are three main types of DML queries: inserts, updates, and deletes. 2

Action Queries in Access • To run action queries using the Access query designer, you will have to okay it first by “enabling potentially harmful content”. • This is similar to enabling macros in Excel. 3

DDL: INSERT INTO • The INSERT INTO command is used to add rows to a table. • Some DBMSs allow this to be shortened to just INSERT (SQL Server, for example). • Access requires both words: INSERT INTO • Since we are adding a new row to a table, we must specify values for all fields that are part of the primary key, and for any other fields that are declared to be required. • Non-key, non-required fields can be given values or not. If no value is specified for a field, that cell (that field in the new row) will have a value of NULL. • NULL means “we don’t know what it is”; it does not mean zero or blank. 4

INSERT INTO (single row) • This form of INSERT INTO is used to add one row to a database table. • The generic format of this type of query is: INSERT INTO Table. Name (field 1, field 2, field 3, …) VALUES (value 1, value 2, value 3) • For example, to add myself as a Being in the planetary system, I could use the following query: INSERT INTO Beings (Being. ID, Being. Name, Home. Planet. ID, Birth. Year) VALUES(16, ‘Bob’, 2, 12) 5

INSERT INTO (single row shorthand) • A shorthand version of INSERT INTO can be used when you are including values for all fields in the same order they are listed in the table: INSERT INTO Beings VALUES(17, "Jim", 2, 12) • Note that you must always include values for the primary key field(s) and any other fields that are required in your INSERT INTO queries. • Access will refuse the insert and give you an error message if you do not. • There is a multi-row version of INSERT INTO, but you must know how to do SELECT queries first. • Therefore, we will look at multi-row INSERT INTOs later. 6

EXAMPLE • Here is the Teams table from assignment 1’s Softball database: We want to add a new team: The name will be “IOE”, the sponsor will be “COE”, the location will be “Ann Arbor”, and the Jersey. Color will be “Blue”. Here’s the SQL query that will add this new team to the table: INSERT INTO Teams(Team. ID, Team. Name, Sponsor. Name, Team. Location, Jersey. Color) VALUES(4, ’IOE’, ’COE’, ’Ann Arbor’, ’Blue’) Note that the values for the text datatypes are in single quotes, while numbers are not. Let’s try this in the SQL Query Tool—Note that “SELECT * FROM Teams” is how you display everything in the Teams table. 7

Shorthand Example • Let’s add another team, this time using the shorthand version. • Here’s the table again: The shorthand version: INSERT INTO Teams VALUES(5, ’Wolverines’, ’UM’, ’Ann Arbor’, ’Maize’) 8

Using SQL View • The Access query designer doesn’t provide graphical tools for building action queries. * • You will need to open query designer, close the add table dialog without adding any tables, and then switch to SQL view. • Type your query in the SQL view textbox. * Of course, Access allows you to perform inserts, updates, and deletes directly on tables in the Datasheet view. 9

UPDATE • UPDATE alters the values in specified fields of selected records. • The generic form is: • UPDATE Table. Name SET field 1 = value 1, field 2 = value 2 [, …] WHERE conditions • The values can be simple data entries, calculations, or even subqueries. They can refer to other fields in the table (or even the same field). • Warning! UPDATE queries can change lots of data very quickly, and the results may be irreversible! 10

UPDATE • Warning! Without a WHERE clause, UPDATE commands will change ALL records in a table! UPDATE Players SET Name=‘Kim’ will make EVERY player have the name ‘Kim’ • Whereas UPDATE Players SET Name=‘Kim’ WHERE Player. ID=4 Will only change the name of one player (4) 11

Examples • Luke (Being. ID = 1) lives on planet 1, but is moving to planet 4. Here’s the query that makes the change in the database: • UPDATE Beings SET Home. Planet. ID = 4 WHERE Being. ID = 1 • How NOT to do it: – UPDATE Beings SET Home. Planet. ID = 4. This moves everybody to planet 4! – UPDATE Beings SET Home. Planet. ID = 4 WHERE Home. Planet. ID = 1. This moves everybody on planet 1 to planet 4. – UPDATE Beings SET Home. Planet. ID = 4 WHERE Being. Name = “Luke”. Use the primary key whenever possible. Since Being. Name isn’t the primary key, there’s no guarantee that Luke is the only Luke. • Note that the WHERE clause in an update query works just like it does in a SELECT, except here it specifies which records to modify, not which records to display. 12

More UPDATE examples • You can update multiple fields in one UPDATE. • For example: Planet SQL (Planet. ID = 1) is contracting due to global cooling. Its radius has shrunk from 10 to 8, while its density has increased from 5 to 7. Here’s the SQL: UPDATE Planets SET Planet. Radius=8, Planet. Density=7 WHERE Planet. ID=1 • To change multiple fields, just make a list of “field=value” items separated by commas following the “SET” keyword. 13

More UPDATE examples • In a fit of uncommon generosity, the professor decides to add three points to everyone’s score. Here’s the SQL: UPDATE Grades SET Score = Score + 3 • Note that the value part (right side) of the equals can refer to fields in the table, including the same one if needed. • Of course, the prof needs to be careful not to allow a student to run this query; he or she might run it repeatedly! 14

DELETE FROM • UPDATE queries can be dangerous; DELETE FROM queries are crossing the freeway blindfolded while carrying plutonium. Backing up and triple-checking are definitely recommended before running DELETE FROM queries. • Be sure to use a WHERE clause with DELETE FROM queries: DELETE FROM Planets wipes out the entire planetary system! • The basic format is DELETE FROM Table. Name WHERE conditions • DELETE FROM removes entire rows; it does not remove columns. If you run the DELETE FROM Planets query, the planets will be gone, but you’ll still have a table with fields to hold planet data (if you ever get any new planets). • Access requires both words (“DELETE FROM”). MS SQL Server and other DBMS’s require only “DELETE”. 15

DELETE FROM • SQL can be used to remove rows from a table using the “DELETE FROM” command. • The basic format: • DELETE FROM [Table] WHERE [conditions] • Obviously, DELETE FROM must be used carefully. For example, the following query will remove ALL data from the Players table: • DELETE FROM Players • We put conditions in the where clause to restrict the rows which will be deleted. 16

DELETE FROM Examples • “DELETE FROM Players WHERE Player. ID=3” will remove one player (Player. ID 3) from the Players table. • This is the safest way to use DELETE FROM: Use the primary key of the table to specify which record(s) to delete. • “DELETE FROM Players WHERE First. Name=‘Tom’” risks removing multiple records if there is more than one ‘Tom’ in the table. 17

DELETE FROM Examples • “DELETE FROM Beings WHERE Home. Planet. ID = 2” will delete all beings from planet 2. • “DELETE FROM Planets” will remove all rows from the Planets table. • “DELETE FROM Beings WHERE Age BETWEEN 25 AND 30” will remove everyone with age 25, 26, 27, 28, 29, or 30. 18

Example • To delete a single row from a table, use the primary key in the WHERE clause: DELETE FROM Beings WHERE Being. ID = 17 • This guarantees that you won’t accidentally remove other rows, because the primary key uniquely identifies a row. 19

DISTINCT • It is possible to write queries which return duplicate rows. For example: • SELECT Player. Position FROM Players • That query will return one row for every player (no WHERE clause), but several rows will say “ 1 st Base”. • If you just want to see rows that are different from each other, use DISTINCT: • SELECT DISTINCT Player. Position FROM Players • This will list all of the different positions, with no duplicates. 20

INNER JOINS • As you recall, good relational database design requires that separate entities have their data stored in separate tables. • However, we frequently want to view data about two or more entities in an single query result set. • INNER JOINs provide the way to do this.

JOINS • Suppose that you want a human-friendly list of the first basemen in the softball league, like this: As you can see from the relationship diagram, the desired fields come from two fields: 22

• This would be extremely tedious if we had to do it by hand: – Write down the names of the first basemen, then use their Team. ID numbers to look up the team information (Team. Name and Jersey. Color) in the Teams table. – Fortunately, computers are excellent at tedious! – The INNER JOIN statement in SQL does this work for you. 23

The INNER JOIN Query • Here’s the query which creates the list of first basemen: SELECT Players. First. Name, Players. Last. Name, Players. Position, Teams. Team. Name, Teams. Jersey. Color FROM Teams INNER JOIN Players ON Teams. Team. ID = Players. Team. ID WHERE Players. Position='1 st Base' 24

INNER JOIN Syntax • In all previous examples, the only thing between “FROM” and “WHERE” was a table name, because all of those queries were on a single table. • When you query two tables linked by a oneto-many relationship, the FROM clause looks like this: FROM Table 1 INNER JOIN Table 2 ON Table 1. Primary. Key = Table 2. Foreign. Key In our example, the FROM clause is this: FROM Teams INNER JOIN Players ON Teams. Team. ID = Players. Team. ID 25

Fields in INNER JOINs • Since an INNER JOIN query returns fields from both tables, you must specify which table each field comes from. • You do this by prefacing each field name with the name of the table it comes from, followed by a period. • This applies to the SELECT, FROM, WHERE, and ORDER BY clauses: SELECT Players. First. Name, Players. Last. Name, Players. Position, Teams. Team. Name, Teams. Jersey. Color FROM Teams INNER JOIN Players ON Teams. Team. ID = Players. Team. ID WHERE Players. Position='1 st Base' 26

INNER JOINs and Access • INNER JOIN queries can get pretty complex, especially for many-to-many relationships. • The Access Query Builder can help you to create the SQL you need, if you have your relationships created properly. • Let’s look at the same query created using the query builder: 27

• If you create the query this way using the query builder, Access will write the SQL for you! • To see it, simply switch to the SQL View. • Access usually adds a lot of parentheses which are unneeded, but it can really help in creating INNER JOIN queries. 28

INNER JOINs on Many-to-Many Relationships • In the Planetary. System database, every being can have multiple jobs. • Each job is on a particular planet, and takes a certain percentage of the Being’s time. 29

• Here’s the query designer, creating a query to show what jobs the Beings from planet 1 do: 30

Here are the results 31

Here’s the SQL: SELECT Beings. Being. Name, Beings. Home. Planet. ID, Jobs. Job. Name, Jobs. Salary, Beings. Jobs. Percentage, Beings. Jobs. Planet. ID FROM Jobs INNER JOIN (Beings INNER JOIN Beings. Jobs ON Beings. Being. ID = Beings. Jobs. Being. ID) ON Jobs. Job. ID = Beings. Job. ID WHERE (((Beings. Home. Planet. ID)=1)); 32

Another way to do INNER JOINS • INNER JOINs are a fairly recent addition to SQL • The old way to do a join was just to list the tables separated by commas, and put the joining criteria in the WHERE clause. 33

Old version of INNER JOIN • Here’s the query we looked at before: SELECT Players. First. Name, Players. Last. Name, Players. Position, Teams. Team. Name, Teams. Jersey. Color FROM Teams INNER JOIN Players ON Teams. Team. ID = Players. Team. ID WHERE Players. Position='1 st Base' Here’s the old way: SELECT Players. First. Name, Players. Last. Name, Players. Position, Teams. Team. Name, Teams. Jersey. Color FROM Players, Teams WHERE Players. Team. ID=Teams. Team. ID AND Players. Position='1 st Base' SQL books say that the “INNER JOIN” syntax runs faster. However, you may see the old version, and it will help to be able to recognize it as a JOIN. 34