SQL Basics, Part 2

1. SQL Basics, Part 2

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. SQL

3. 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.

4. 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. SQL

5. 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 TableName (field1, field2, field3, …) VALUES (value1, value2, value3) • For example, to add myself as a Being in the planetary system, I could use the following query: INSERT INTO Beings (BeingID, BeingName, HomePlanetID, BirthYear) VALUES(16, ‘Bob’, 2, 12) SQL

6. 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. SQL

7. 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 JerseyColor will be “Blue”. • Here’s the SQL query that will add this new team to the table: • INSERT INTO Teams(TeamID,TeamName,SponsorName,TeamLocation,JerseyColor) 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. SQL

8. 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’) SQL

9. 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.

10. UPDATE • UPDATE alters the values in specified fields of selected records. • The generic form is: • UPDATE TableName SET field1 = value1, field2 = value2 [, …] 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!

11. 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 PlayerID=4 Will only change the name of one player (4) SQL

12. Examples • Luke (BeingID = 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 HomePlanetID = 4 WHERE BeingID = 1 • How NOT to do it: • UPDATE Beings SET HomePlanetID = 4. This moves everybody to planet 4! • UPDATE Beings SET HomePlanetID = 4 WHERE HomePlanetID = 1. This moves everybody on planet 1 to planet 4. • UPDATE Beings SET HomePlanetID = 4 WHERE BeingName = “Luke”. Use the primary key whenever possible. Since BeingName 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.

13. More UPDATE examples • You can update multiple fields in one UPDATE. • For example: Planet SQL (PlanetID = 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 PlanetRadius=8, PlanetDensity=7 WHERE PlanetID=1 • To change multiple fields, just make a list of “field=value” items separated by commas following the “SET” keyword.

14. 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!

15. 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 TableName 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”.

16. 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. SQL

17. DELETE FROM Examples • “DELETE FROM Players WHERE PlayerID=3” will remove one player (PlayerID 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 FirstName=‘Tom’” risks removing multiple records if there is more than one ‘Tom’ in the table. SQL

18. DELETE FROM Examples • “DELETE FROM Beings WHERE HomePlanetID = 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. SQL

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

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

21. 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.

22. 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:

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

24. The INNER JOIN Query • Here’s the query which creates the list of first basemen: SELECT Players.FirstName, Players.LastName, Players.Position, Teams.TeamName, Teams.JerseyColor FROM Teams INNER JOIN Players ONTeams.TeamID = Players.TeamID WHERE Players.Position='1st Base'

25. 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 one-to-many relationship, the FROM clause looks like this: FROM Table1 INNER JOIN Table2 ON Table1.PrimaryKey = Table2.ForeignKey • In our example, the FROM clause is this: FROM Teams INNER JOIN Players ON Teams.TeamID = Players.TeamID

26. 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.FirstName, Players.LastName, Players.Position, Teams.TeamName, Teams.JerseyColor FROM Teams INNER JOIN Players ON Teams.TeamID = Players.TeamID WHERE Players.Position='1st Base'

27. 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:

28. 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.

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

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

31. Here are the results

32. Here’s the SQL: SELECT Beings.BeingName, Beings.HomePlanetID, Jobs.JobName, Jobs.Salary, BeingsJobs.Percentage, BeingsJobs.PlanetID FROM Jobs INNER JOIN (Beings INNER JOIN BeingsJobs ON Beings.BeingID = BeingsJobs.BeingID) ON Jobs.JobID = BeingsJobs.JobID WHERE (((Beings.HomePlanetID)=1));

33. 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.

34. Old version of INNER JOIN • Here’s the query we looked at before: SELECT Players.FirstName, Players.LastName, Players.Position, Teams.TeamName, Teams.JerseyColor FROM Teams INNER JOIN Players ON Teams.TeamID = Players.TeamID WHERE Players.Position='1st Base' • Here’s the old way: SELECT Players.FirstName, Players.LastName, Players.Position, Teams.TeamName, Teams.JerseyColor FROM Players,Teams WHERE Players.TeamID=Teams.TeamID AND Players.Position='1st 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.