Tuesday, June 24, 2014

SQL: Data Manipulation Language (DML) [Part II]

Posted on Tuesday, June 24, 2014 by
This is part II of a set of SQL notes I wanted to post about my current fitness website project.

These are the links for each part to navigate:

Part I | Part II | Part III | Part IV

Data Manipulation Language (DML) is for managing and work with the data in tables. So once you have created your objects and named/commented them as much as you like, this is the next step. The ability to pull out data, change it, add some, delete them, and merge them are included in DML.

Main functions here are ALSO pretty self explanatory (which is BETTER!):

  • INSERT - insert data into a table
  • UPDATE - updates existing data
  • DELETE - deletes data, can determine what columns/types/or if all data but leave table/or if whole table
  • SELECT - retrieve data from the a database (more on this later in part IV with joining tables) 

USE Fitness
GO
-- Inserting data into a table
INSERT INTO dbo.Recipes (Title, Directions, Ingredients)
       VALUES ('Chicken', 'Fry with olive oil', 'chicken', olive oil')
GO
 
INSERT INTO dbo.Recipes (Title, Directions, Ingredients)
       VALUES ('Salmon Salad', 'Add lettuce, tomato, and cucumbers. Mix.', 'salmon, lettuce, tomato, cucumbers')
GO
 
INSERT INTO dbo.Recipes (Title, Directions, Ingredients)
       VALUES ('Tilapia Soup', 'Add soup. Add tilapia.', 'tilapia, soup')
GO

INSERT INTO dbo.Recipes (Title, Directions, Ingredients)
       VALUES ('KFC chicken', 'Drive to KFC, pickup chicken', 'car, chicken')
GO
 
INSERT INTO dbo.RecipePhotos (FilePath, RecipeId)
       VALUES ('http://mysite.com/chicken_recipe_1.jpeg',
              (SELECT RecipeId FROM dbo.Recipes WHERE Title='Chicken'))
GO
 
INSERT INTO dbo.RecipePhotos (FilePath, RecipeId)
       VALUES ('http://mysite.com/chicken_recipe_2.jpeg',
              (SELECT RecipeId FROM dbo.Recipes WHERE Title='Chicken'))
GO
 
INSERT INTO dbo.RecipePhotos (FilePath, RecipeId)
       VALUES ('http://mysite.com/chicken_recipe_3.jpeg',
              (SELECT RecipeId FROM dbo.Recipes WHERE Title='Chicken'))
GO

INSERT INTO dbo.RecipePhotos (FilePath, RecipeId)
       VALUES ('http://mysite.com/salmon_salad1.jpeg',
              (SELECT RecipeId FROM dbo.Recipes WHERE Title='Salmon Salad'))
GO
 
INSERT INTO dbo.RecipePhotos (FilePath, RecipeId)
       VALUES ('http://mysite.com/salmon_salad2.jpeg',
              (SELECT RecipeId FROM dbo.Recipes WHERE Title='Salmon Salad'))
GO
 
INSERT INTO dbo.RecipePhotos (FilePath, RecipeId)
       VALUES ('http://mysite.com/tilapia_soup1.jpeg',
              (SELECT RecipeId FROM dbo.Recipes WHERE Title='Tilapia Soup'))
GO

Note when you put these into the SQL.. and you hit execute, it will execute everything ABOVE your cursor. If you only highlight a section and hit execute, only that part will be executed. So if you do half of it, execute, then add more, you will want to only highlight the new portion. It will be in yellow for SQL Server but definitely depends on what you are using (could be different years, Oracle, etc.). If you execute the entire file again, you will have duplicates of the beginning half.

In the next section, I will show you how the database actually looks and add in some screenshots to help explain.