Querying Relational Data in DBMS

A relational database query is a question about the data, and the answer consists of a new relation containing the result. For example, we might want to find all students AGE less than 18 or all students enrolled in perticular course.

The SELECT statement is used to fetch the data from a database table which returns this data in the form of a result table. These result tables are called result-sets.

SELECT column1, column2, ...
FROM table_name;

If you want to select all the fields available in the table, use the following syntax:

SELECT * FROM table_name;

The symbol ´*´ means that we retain all fields of selected tuples in the result.

We can retrieve rows corresponding to students who are younger than 18 withthe following SQL query:

SELECT * FROM Students WHERE age < 18;

The condition age < 18 in the WHERE clause specifies that we want to select only tuples in which the age field has a value less than 18.

In addition to selecting a subset of tuples, a query can extract a subset of the fields of each selected tuple. we can compute the student_id and First_name of students who are younger than 18 with the following query:

SELECT ID,FirstName FROM Students WHERE age < 18;

SQL Aliases

Aliases are the temporary names given to tables or columns. An alias is created with the AS keyword.

SELECT column_name AS alias_name
FROM table_name;

SELECT column_name(s)
FROM table_name AS alias_name;

SELECT studentID AS ID, 
FROM students AS S;

Aliases can be useful when:

• There are more than one table involved in a query
• Functions are used in the query
• Column names are big or not very readable
• Two or more columns are combined togeth

SELECT data from Multiple Tables

We can also combine information from multiple tables.

SELECT table1.column1, table2.column2 
FROM table1, table2  
WHERE table1.column1 = table2.column1;

SELECT S.name, E.cid
FROM Students AS S, Enrolled AS E
WHERE S.sid = E.sid;