What is SQL
SQL stands for “Structured Query Language”. SQL is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems.
SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database. The standard SQL commands such as “Select”, “Insert”, “Update”, “Delete”, “Create”, and “Drop” can be used to accomplish almost everything that one needs to do with a database.
In short, SQL is
- Stands for “Structured Query Language”
- Allows you to access databases
- Is an ANSI (American National Standards Institute) standard computer language
- Can execute queries against a database
- Can retrieve data from a database
- Can insert new records in a database
- Can delete records from a database
- Can update records in a database
SQL is a standard language that works with database programs like MS Access, DB2, Informix, MS SQL Server, Oracle, MySQL, Sybase, and other database systems, Although most of them also have their own additional proprietary extensions that are usually only used on their system.
Basic Structure of SQL
The basic structure of an SQL expression consists of three clauses:
- The select clause corresponds to the projection operation of the relational algebra. It is used to list the attributes desired in the result of a query.
- The from clause corresponds to the Cartesian-product operation of the relational algebra. It list the relations to be scanned in the evaluation of the expression.
- The where clause corresponds to the selection predicate of the relational algebra. It consists of a predicate involving attributes of the relations that appear in the from clause.
A typical SQL query has the form:
select A1, A2,…,An
from r1, r2,…,rm
Each Ai represents an attribute, and each ri a relation. P is a predicate.
SQL Database Table
A relational database system contains one or more objects called tables. The data/information for the database is stored in these tables.
Tables are uniquely identified by their names and are comprised of columns and rows. The database table columns (called also table fields) have their own unique names and have a pre-defined data types. Table columns can have various attributes defining the column functionality (the column is a primary key, there is an index defined on the column, the column has certain default value, etc.).Rows contain the records or actual data for the columns.
Once you run the MS Access 2003 software residing under <Start> and <Programs> menu after installation, you will notice that the software has seven objects or seven areas of operation. In other words you can say that MS Access has the ability to do seven types of jobs. Out of seven we would be discussing following six objects one by one.
Once you click ‘Tables’ object under the Objects group as shown, the right pane of MS Access GUI will show you the list of tables in your database.
1. Click on the Tables object under the Objects group. This is shown as label 1 on the screenshot above.
2. Next step would be to click ‘New’ as we are going to create new table or more precisely new Local Table.
3. The ‘New Table’ window will popup after performing step 2. Select the ‘Design View’ option. You can create new tables in MS Access in Datasheet View too but Design View is more professional way of doing this plus it has more flexibility.
4. Finally press the <OK> button.
On the popped up window you will see all different options of creating new table. Let’s look at how to create a local table in MS Access. Usually whenever you want to create a brand new local table in MS Access, the proper or more flexible way of achieving this goal is to create the table using the Design View. You can also create local tables in MS Access using Datasheet view but our recommendation would be to always go with ‘Design View’.
The label 1 points to the data type of any particular field inside a table. Data type pertaining to any column or field inside a table lets MS Access know in advance what type of data that it maybe expecting in this field e.g. whether it would be Currency, Date or Text.
Label 2: A table is composed of vertical columns called fields. The field names you want for your table, write under this Field Name column as depicted in the screenshot above. Then select the appropriate data type.
Here is a sample table called “customer”. Cust_id, first_name, last_name, city are the columns specified in field name with their data types.
Each data type has attributes, visible to you under the Field Properties area. For example if you have selected ‘First_name’ field with data type as ‘Text’ from the drop down menu, you can adjust the properties relating to this data type e.g. if you want the name field not to be more than 60 characters, the way to do this is to write the Field Size value as 60 mentioned under same Field Properties area.
Microsoft Access Data Types:
Different data types available for building Local Tables in MS Access are given below:
|Text||Use for text or combinations of text and numbers. 255 characters maximum|
|Memo||Memo is used for larger amounts of text. Stores up to 65,536 characters. Note: You cannot sort a memo field. However, they are searchable|
|Number||A number field can store numbersByte: Allows whole numbers from 0 to 255Integer: Allows whole numbers between -32,768 and 32,767Long: Allows whole numbers between -2,147,483,648 and 2,147,483,647Single: Single precision floating-point. Will handle most decimalsDouble: Double precision floating-point. Will handle most decimals|
|Currency||Use for currency. Holds up to 15 digits of whole dollars, plus 4 decimal places. Tip: You can choose which country’s currency to use|
|AutoNumber||AutoNumber fields automatically give each record its own number, usually starting at 1|
|Date/Time||Use for dates and times|
|Yes/No||A logical field can be displayed as Yes/No, True/False, or On/Off. In code, use the constants True and False (equivalent to -1 and 0).Note: Null values are not allowed in Yes/No fields|
|Ole Object||Can store pictures, audio, video, or other BLOBs (Binary Large OBjects)|
|Hyperlink||Contain links to other files, including web pages|
|Lookup Wizard||Let you type a list of options, which can then be chosen from a drop-down list|
Once all your table structure is complete save the table by clicking File and then Save from the menu bar or clicking on the save button icon on the tool bar. Let’s summarize whatever we have said for the design of local tables.
- Write down the field name and then select the data type then move to the next line, write the field name and then select the data type then move to the next line and so on.
- Repeat the process unless you would end up having all the fields and corresponding data types required by your table in the systems.
- Finally click the save icon on the tool bar. MS Access asks for the name of the table you would like to save. Write down whatever name you want to give to that table.
- Click <OK> button.
EXAMPLE: Creating Student_info Table:
The screenshot shows the Design View of table. This GUI you will see after performing 4 steps mentioned under the article ‘Creating Tables’ earlier in this chapter.
1. The label 1 in the screenshot above shows the Field Name column. Under this column write down all the fields or column names you want for your table. Lets create a table with 4 columns or fields i.e.
2. Data Type column is the place where you select the Data Type for each of your fields inside the table. In our case we have selected AutoNumber data type for Student_No field.
Student_No as AutoNumber
Name as Text
Address as Memo
Std as Number
3. Each field in the table has ‘Field Properties’. The items under Field Properties will change based on the Data Type you select for your field.
4. The next you would like to do is to make the id field as ‘primary key’. This can be done by right clicking the id field and selecting the ‘primary key’ option.
Save the table and assign the table name e.g as Student_info.
What is Primary & Foreign Key?
Primary key is basically a constraint. If defined on any column/field you would not be able to insert anything which is already there in that particular column i.e the value of that particular column can not be duplicated.
To establish a link between two tables or in other words to have Primary-Foreign Key relationship between two or more tables, it is imperative to have primary key defined on at least one of the tables.
Primary key of one table acts as the foreign key in other table. A foreign key column can have duplicate values. Once you establish a Primary-Foreign Key relationship between two or more tables it’s like having a constraint on all the tables in the relationship. Such constraint is called ‘Referential Integrity’ constraint. The table having primary key column is called Parent table all the other tables linked to the Parent table via foreign key are called child tables.
Once this Primary-Foreign key relationship is defined in between tables, the following constraints get effective:
- You cannot delete any record from the parent table if there are corresponding record(s) in the child table(s).
- You cannot insert any record in the child table if there is no corresponding record in the parent table.
Let’s look at the example to completely understand the concept behind these constraints. Consider one table having name as ‘suppliers’ and second table having the name as ‘orders’ with the following data in them corresponding:
Once you have defined a primary-foreign key relationship between these two tables with supplier_id of ‘suppliers’ table as primary key and supplier_id of ‘orders’ table as foreign key, if you try to delete supplier_id record 10001 from suppliers table, MS Access will not let you do so. To delete such type of records you have to first delete all the corresponding records from the child table(s). Only then you would be able to delete the record from the parent table.
- How to create a primary key?
- Open the table in Design View.
- Right click on the column you would like to make as ‘Primary Key’ column.
- Select the option ‘Primary Key’ from the popped up menu.
Remember in developing Primary-Foreign Key relationship between tables the story begins with developing primary key first. Before making the column as primary key make sure that all the values in that particular column should be unique or will be unique if it’s the first time you are creating the table.
- How to create foreign key?
Primary Key of parent table is the Foreign Key of the child table. This you should remember always. Now let’s see the step by step process on defining the Primary- Foreign Key relationship in between tables. You are allowed to enter duplicate values in the foreign key column.
- Click on the Tools item on the menu bar and then click the Relationships option from the expanded menu.
- This will open up the ‘Show Table’ window. Make sure the ‘Tables’ tab is selected.
- Select all the tables in your database that you would like to be in your Primary-Foreign Key relationship.
- Finally click <OK> button.
Suppose you have selected two tables; Dept and Emp. In Dept we have Dno as primary key. To develop Primary-Foreign Key relationship, all you have to do is to drag the primary key column on to the column you want to make as foreign key.
Once you perform this kind of drag and drop operation, you will see an ‘Edit Relationships’ window popping up. Over here if you check the ‘Cascade Delete Related Records’, you would be able to delete records from the parent table even if there are corresponding records in the child table. When you check this option, MS Access automatically first delete all the related records from the child table(s) and then delete the parent record(s).
On the other hand if you select the ‘Cascade Update Related Fields’, any update operation on the primary key column will be having a ripple effect on the child table(s). In other words all the corresponding child tables’ foreign key column value(s) will get updated. Finally click the <OK> button once done.
Now you would be able to see 1 and infinity sign ( ) and a line linking two tables. Table having the 1 digit sign means or represents a primary key column whereas the other represents the foreign key column. Duplicates are allowed in foreign key column whereas like explained before duplicates are not allowed in primary key column.