As the previous post, we have shown you eight tables for storing data in the Student DMBS, however, it was not create the data dictionary yet which is called Table or Entity. This tutorial will tell you how to work with SQL statements programming language to look in creating tables in Microsoft Access.
Before we start to the real work on Microsoft Access, we would like to review what is the SQL programming language? SQL is standing for Structure Query Language which is a database programming language use to command data. Most of the RDMS ( Relational Database Management System) are using SQL to perform their commands to retrieve or query the information with their database schema. This tutorial will take only few command which will be used in this project such as Create, Insert, Delete, Update and Select.
Let take a look to Create Statement to create Table or Database:
Create Table Table Name ( Field Name datatype,....., n)
The syntax the above is showing that the Create Table which is the statement to create table/entity then there is Table Name which is presented the name of your table. In the brackets, it performs the Field Name and its Datatype. So what is a Datatype in programming language. Datatype is a classification that specifies which type of value a variable has. As an example: Integer, Char/Varchar or String, Double/Float, Date/Time, Boolean and etc. Let’s see an example below:
Create Table user_info (userid int, username varchar, password varchar)
Let’s take a look to SQL Insert Statement: There are two types of statements which can be used insert statement.
Insert Into Table Name(Field Name,....n) values('x',"xx")
The above statement is performing that the command will pause the Insert Into command in to Table Name which is the name of the table which you are going to insert the values by the Field Name till the n values. Let see the example:
Insert Into user_info(userid, username, password) values(1,'admin','123x133')
Insert Into Table Name values('',"")
The above statement is performing the insert statement without worrying about the field, however, the values part must be the same to the fields in the table. As an example:
Insert Into user_info values(1,'admin','123x133')
Let’s take a look to SQL Select Statement: There are a lot of Select Statements in SQL programming language; however, we only choose simple two styles for you. As below:
Select * from Table Name
The above syntax is performing the query to select all records from Table Name which is the able you want to use. Let’s see an example:
Select * from user_info
The other style is:
Select Field Name,.....n fields from Table Name
The above style is performing the select statement by specifying the field name from the table name which is referred to the name of the table you want to select record from. As example below:
Select username, password from user_info
However, select can be joined from other tables or even with condition as an example below:
Select username, password from user_info where userid=1
The clause Where here is referring to the condition which you would like to apply for the select statement. (Where FieldName=xxx)
Let’s take a look to SQL Delete Statement which is performing the deletion of the record from the table as the syntax below:
Delete from Table Name Where Field Name=xxx
*** Note that the Delete Statement must need the condition Where otherwise, all the record in the table which performs deletion will be gone.
Let’s see an example:
Delete from user_info where userid=1
The other important SQL statement is Update which is using to perform any update of the record in the table of your Database. Let’s take a look to the syntax:
Update Table Name Set Field Name1=xxx, Field Name2=xy,.... where Field Name=xxx
The above statement is trying to update the record in the table by the condition of Where. Remember the key word SET must be used to specify which field name that you are going to update. Let’s see and example:
Update user_info SET username='IT_Admin', password='123' where userid=1
As mentioned the above those statement will be used in our project. Let’s see the next tutorial about how to Create those eight tables.
*** For more information about SQL Statement, please visit https://www.w3schools.com/sql/default.asp