In general there are two types of database languages. These are:

1. Data Definition language (DDL):

This is used to describe the structure of your database. In other words this language is used to:

1. Define the structure of all tables: this is achieved by identifying number of

   attributes, their names and types and length of each of attributes.

2. Define keys.
3. Define constraints

Some database packages such as Microsoft Access, Paradox, Oracle ..etc provide both graphical user interface and commands to perform the above tasks.

1. Data manipulation Languages (DML):

This type of languages are used to retrieve, store, manipulate data , create forms, queries, reports and application programs. Example of these languages are: SQL, Query by example and relational Algebra and calculus languages. In general there are two types of Data Manipulation languages; these are:

1. procedural languages. When these languages used the user must write all steps and procedure to obtain the result wanted by the user. Relational Algebra is an example of these type languages. Note that computer languages such as Cobol, Fortran, C, C++, Java, Basic, Pascal .. etc. are all procedural languages.
2. Non Procedural languages. Non procedural languages are much easier to use it than procedural languages. The users of these languages do not need to tell the system how to find the results, all they have to do is to ask for the end result they want. For example SQL is a nonprocedural language and assume that we have a table (file) of employee and we want to find the names and phones of all female employees who make less than $30,000 per a year. The following SQL statement produces the result which we wanted:

Relational Algebra is a procedural Data Manipulation Language. It has the following operations:

In relational database terminology tables are called relations, rows of the tables called tuples.

Union: The union of two relations is formed by adding the tuples of the two relations to create a third relation.

Difference: The difference of two relations is a third relation containing tuples that occurs in the first relation but not in the second relation.

Intersection: The intersection of two relations is a third relation containing the tuples that appear in both relations.

Product (sometimes called Cartesian product): The product of two relations is the concatenation of every tuple of one relation with every tuple of a second relation.

Projection: The projection is an operation that selects specified attributes (columns) from a relation

Selection: The selection is an operation that selects specified tuples (rows) from a relation.

Join: The join operation is a combination of the product, selection and possibly projection operations.

Divide: Isolates attribute values for attributes other than the join attributes.

SQL (Structured Query Language)

SQL is a non procedural Data Manipulation Language. Following are some important SQL statements:

Select attribute(s)

From table(s)

Where conditions

Examples:

1. Select Name, Major

From Student

2. Select Name, Major

3. Select Name, Major

Where GP >3.50 and Major ="Math"

4. The following select statement selects all attributes from table Student

   Select *

   From Student

5. The following select statement selects all Majors from table Student

   Select Major

   From Student

6. The following select statement selects all Majors (no duplicate) from table Student:

   Select Distinct Major

   From Student

7. The following select statement select all students from table Student who major either in Math or Accounting:

   Select Name

   From Student

   Where Major IN ["Math", "Accounting"]

    

8. The following select statement select all students from table Student who do not major neither in Math or in Accounting:

   Select Name

   From Student

   Where Major NOT IN ["Math", "Accounting"]

9. The following select statement selects all students from table Student who major either in Math or Accounting, the result is sorted by Name.

   Select Name, Major, Age

   From Student

   Where Major IN ["Math", "Accounting"]

   Order By Name

10. The following select statement select all students from table Student who major either in Math or Accounting, the result is sorted in ascending order by Major and in descending order by age

    Select Name, Major, Age

    From Student

    Where Major IN ["Math", "Accounting"]

    Order BY Major ASC, Age Desc

    Some SQL Built-in Functions:

    Count, Avg

11. The following select statement returns number of students

    Select count(*)

    From Stdent

12. The following two select statements return different results:

    Select count (Major) From Student

    Select count (Distinct Major) From Student

14. The following groups together those rows that have the same value of

    Major.

    Select Major, count (*)

    From Student

    Grouped By Major

15. Select only those majors which has more than 2 students

    Select Major, count (*)

    From Student

    Grouped By Major

    Having count (*) > 2

    Joining with SQL

16. Find the Student’s Id, Student’s name and the class name for every student.