How to Retrieve Information from a Database?

After you have designed a database in MSSQL Server or MySQL, how do you retrieve specific information from the database? You need to know Structured Query Language (SQL) and that is what will help you retrieve information. Read on for highlights.

First of all, you need to know what a schema and instance mean for database tables.

A schema includes all the fields of the table while instance means collection of data at a specific moment.

For example, you have created the following tables for a small university database. (I give you only the schemas of tables here.)

Student:

matNr|sName

Professor:

pName|psalary

Class:

classNr|room|day|pname

Takes:

matNr|classNr|grade

TA:

matNr|classNr|hours|tasalary

The bold field(s) of a table represent the primary key of that table which uniquely identifies entities (tables) within an entity set (a set of similar tables).

Now several queries to the database and SQL statements for those queries could be:

1) List all students of the university.

SELECT sName FROM Student

Here SELECT is an SQL keyword that is targeting to display names of students in the university from Student table. FROM is also an SQL keyword.

2) List students whose grade was 'A' in CSE 303.

SELECT sName FROM Student, Takes WHERE Student.matNr=Takes.matNr AND classNr = 'CSE 303' AND grade = 'A'

Here SELECT is targeting to show the student names from a join of Student and Takes tables given that classNr or course is 'CSE 303' and student's grade is 'A'.

3) Display the names of TAs whose salary is greater than $1500.

SELECT sName from Student, TA WHERE Student.matNr=TA.matNr AND tasalary>1500

4) Which professor takes classes on CSE 303?

SELECT pName FROM Class, Professor WHERE Class.pName = Professor.pName AND classNr='CSE 303'

5) List Students of CSE Department.

SELECT sName from Student, Takes WHERE Student.matNr=Takes.matNr AND classNr='CSE%'

Here the query asks to list students of CSE department. But in our tables we have no field for department. So we find a way out by a join of Student and Takes tables and writing classNr= 'CSE%' meaning the courses beginning with the characters 'CSE'.

Summing up, those are a few examples how you use SQL query language to extract your desired information from a specific database, in my case, a small university database. You can find other query languages such as relational algebra to extract information from databases. But SQL is more user-friendly, widespread and popular.

Mr. Satish Kartan has been working with SQL Server for the past 20 years. To read more, please visit Satish Kartan's blog at http://www.sqlfood.com/ where he has shared more details.