How to View & Learn SQL in MS Access

Learning SQL is not limited to IT professional, Even those working in Non IT environment example  working in operations, MIS generally interact with internal IT teams, its necessary to learn SQL  to enhance the existing skills. You don’t need to install Oracle to Learn SQL. You can use Oracle  Apex , web browser which  is explained in the previous sections or If  you are using Microsoft Access, you can easily learn SQL.

To Create Table Goto Create Table & Add the Fields & Data. Example of Employee & Salary Table.

Under View, select SQL view, You should be able to view the output of the SQL query as below

 

 

You can use this SQL View Window for practicing your SQL Queries.  Type the SQL Query in this view & click on Run to get the output.

Below are the Basic SQL Queries & all the Queries are executed from Microsoft Access:

Select:

To list all the data from employee table, type the below Query & Run.

select * from employee

To use conditions in select query use “Where” condition, Below Query will list record of empname Kiran from Employee Table.

select * from employee where empname=”kiran”

Create Table:

To create new table use “Create Table” and the table name with the field and data type within the small bracket.

create table empcontact(empid number, empphno number, empmail text)

Insert Records in the Table:

To insert record into the table use “Insert Into” table name with the values within the small bracket.

Insert into empcontact values(100,2222222,’contactme@testmail.com’)

Update Table:

To change or update the existing data in the table use Update

update empcontact set empmail=’contactme1@testmail.com’ where empid=100

SQL SubQuery:

Subquery is the Inner or Nested Query. Below query search’s for the record where salary is greater than 30000 from the salary table.

select * from employee where empid in (select empid from salary where salary>30000)

Simple Natural Join:

To join multiple tables, after from use the table name with the alias, the alias names are referred in the select as below

select e.empid, e.empname,s.salary from employee e, salary s where e.empid=s.empid

Aggregate Functions: Having, Group

Having is used with Group. This filters data based on the Group.

select e.empdept, sum(s.salary) from employee e, salary s
group by e.empdept
having sum(s.salary)>50000

Leave a Reply

Your email address will not be published. Required fields are marked *

CommentLuv badge