Learn online DBMS “Select Command in MySQL” Complete Lesson 14

SELECT COMMAND–The SELECT Command is used to query a database or retrieve information from it. There are various ways in which the SELECT command can be used. The basic structure of a SELECT command contains the SELECT clause followed by the attribute list (separated by comma (,)) you want to select, then FROM clause followed by the table name and lastly WHERE clause which is followed by the condition which can be a Boolean expression or another SELECT command that identifies the tuples to be selected.

Consider the following tables in the “School” Database for all the queries that follow:

Create Table Department

(

Dept_Namevarchar(20) NOT NULL,

Dept_IDinteger PRIMARY KEY,

No_of_Teachersinteger,

Head_Dept integer,

FOREIGN KEY (Head_Dept) REFERENCES Department(Dept_ID)

);

Create Table Teacher

(

Teacher_Namevarchar(20) NOT NULL,

Teacher_IDinteger PRIMARY KEY,

Dept_Nointeger,

  Subject varchar(20),

  FOREIGN KEY (Dept_No) REFERENCES Department(Dept_ID)

); 

Assume that the following data has been inserted in the above tables using the Insert IntoValues Statements:

Query to select the names of all teachers with Teacher_ID greater than 102.

SELECT Teacher_Name

FROM Teacher

WHERE Teacher_ID> 102;

Thus basic logical comparisons equal to (=), less than (<), greater than (>), less than or equal to (<=), greater than or equal to(>=), not equal to(<>) can be used in the WHERE clause.

1. Query to retrieve the names and subject of all teachers having Teacher_ID less than or equal to 105 teaching “DBMS” subject.

SELECT Teacher_Name, Subject

FROM Teacher

WHERE Teacher_ID<= 105 AND Subject = “DBMS”;

Thus Booleanoperations AND, OR can also be used in the WHERE clause.

Query to retrieve department numbers of all the departments having number of teachers between 5 to 20(inclusive).

SELECT Dept_ID

FROM Department

WHERE No_of_Teachers>= 5 AND No_of_Teachers<=20;

Query to retrievenames of all the teachers who belong to Chemistry department.

Note that the above query requires two tables – Teacher and Department. In such queries we haveto provide the JOINcondition in the WHERE clause which would link one table to the other. The above two tables are joined by the attribute –Dept_ID and Dept_No. The query is as follows:

SELECT Teacher_Name

FROM Teacher, Department

WHERE Dept_ID=Dept_No AND Dept_Name=”Chemistry”;

Here Dept_ID=Dept_No is the join condition

andDept_Name =”Chemistry” is the selection condition.

The selection condition will select the Chemistry department tuple in the department table and the join condition as you can observe will select those teachers in the Teacher table who have the Dept_No of the Chemistry Department as given by Dept_ID.

1. We know that same name cannot be used for different attributes within a particular relation however same name be used for attributes if they are in different relations. Suppose the teacher and department table both had same names for the department number, say Dept_ID as shown below:

Create Table Department

(

Dept_Namevarchar(20) NOT NULL,

Dept_IDinteger PRIMARY KEY,

No_of_Teachersinteger,

Head_Deptvarchar(20),

FOREIGN KEY (Head_Dept) REFERENCES Department(Dept_ID)

);

Create Table Teacher

(

Teacher_Namevarchar(20) NOT NULL,

Teacher_IDinteger PRIMARY KEY,

Dept_IDinteger,

  Subject varchar(20),

  FOREIGN KEY (Dept_ID) REFERENCES Department(Dept_ID)

);

In such case when the join condition will be specified, there will be an ambiguity about which Dept_ID we are talking about. To resolve this problem, we have to prefix the name of the attribute with the relation name followed by a period as shown below:

Query to retrieve names of all the teachers who belong to Biology department.

SELECT Teacher_Name

FROM Teacher, Department

WHERE Department.Dept_ID=Teacher.Dept_ID AND 

Dept_Name=”Biology”;

Query to retrieve the department numbers of all departments along with theirhead departments. In this query, same relation will be used twice. But the problem is to resolve the ambiguity of the relations. Hence we will use aliases or tuple variables for Department relation which are created by using the keyword ‘AS’ as shown below:

SELECT D.Dept_ID, H.Dept_ID

From Department AS D, Department AS H

WHERE D.Head_Dept=H.Dept_ID;

In the above query, the selected attributes (D.Dept_ID, H.Dept_ID) have  names which a user may not be able to understand.We can also rename the column names in the result of a query as shown below:

SELECT D.Dept_ID AS Dept_ID, H.Dept_ID AS Head_Dept_ID

From Department AS D, Department AS H

WHERE D.Head_Dept=H.Dept_ID;

Query to retrieve all the information about Teacher with ID=101. In this query we have to specify all the attributes in the SELECT clause. An easier way to do this is to use asterisk (*), which means all the attributes.

SELECT *

FROM Teacher

WHERE Teacher_ID=101;