SQL Statement Types

-


               SQL statements can be defined into three major types: Data Definition Language (DDL), Data Manipulation Language (DML), and Data Control Language (DCL). SQL language is relatively easy to learn and has fewer than 100 statements. The way different types of the SQL statements manipulate or control the data is what sets each apart. The three main categories can be broken down as follows:

  • Data Definition Language – These types of statements create something in the database, like a table, index, or other properties of the actual database.
    • ALTER TABLE – modifies a table’s definitions (ALTER TABLE table_name)
    • DROP TABLE – Deletes a table and all of the data permanently (DROP TABLE table_name;)
      • Be very careful using this and ensure backups are created first. The table will permanently deleted along with all of the data.

    • Data Manipulation Language – These are used to manipulate the data, as the name implies, and lets users add, delete, update or retrieve data as needed.
      • FROM – Tells the table the data should be pulled from.
      • INSERT – Insert rows into a table
      • DELETE – Can delete just one row from a table

    • Data Control Language – These are used to control who has access to certain parts of the database.
      • ROLLBACK – Brings data back to its original values
      • GRANT – Givers users permission to access data from a table or object

Geeks4Geeks provided a nice chart that was used to illustrate the different statement types. While they provide extra examples of ones not included in this week’s discussion posting, the flow chart was still handy in knowing the differences between the different SQL statements. Learning about the SQL statements is very interesting because it is very straight forward. All of the commands are very direct and manipulating the data or tables comes relatively easy. 



Comments

Post a Comment

Popular posts from this blog

Employee Management System - Simple Project

-
Image
  The employee management system project is designed to take user input and store the information as variables for later use. As Miller, Vahid, and Lysecky point out, a variable “is used to remember a value for later use" (2015). Creating the variables of employeeName, employeeSSN, employeePhone, employeeEmail, and employeeSalary were to hold the information to display later in the program. Variable types were string with the exception of the salary input that was set as an integer.             Repetition was very useful in writing this code. Each line was the same just changing the variable name and type in the string. Employee salary was the only line that had an addition with the sep= function to remove the space after the $ sign in the code. This was used for appearance purposes. Setting the variables to strings allowed the user to enter dashes if needed and parenthesis if desired for the phone number. Setting the variabl...
Read more

Explicit and Implicit Variables

-
  What is a Variable? Using implicit or explicit variables is an important decision when choosing what language to use when writing a program or starting to code. In simplest terms, a variable is used to remember a value for later use. The way different programming languages use variables is important for programmers to keep in mind when choosing how to code variables. Implicit variables are when the compiler chooses the type of output and explicit variables are when the programmer pre-assigns variables to have a specific output. Examples of Variables Each can be useful depending on the program that is being written. Boudreau points out that implicit variables can often be easier to use in programming and that sometimes the results can be just fine. However, leaving the compiler making that decision making on an assumption can often lead to problems. Explicit assigning of variables gives a more specific output and result and can be much more reliable. Depending on the lan...
Read more

Creating Tables in MySQL

-
Image
  Creating Tables in MySQL           Creating tables in MySQL is important so that a user can add information, delete information, and update an entry using SQL statements. Also, a final statement was created to join all three tables demonstrating the relationships between the three tables. The INSERT function was used to add rows to the first table named tbldvdtitles. The information was inserted and the code and the results are pictured below: MySQL instructions were used by Oracle to guide the use of MySQL. The next table was a table called tbldvdActors with the information inserted and results below: Updating and deleting rows were part of the requirements for the interactive assignment and the following code was used: A third table was created to describe the relationships between actor ID and the ASIN number for each movie and the results were as follows: Joining all three tables was the last step and the results were as follo...
Read more