Question

Consider the tables Admin and Transport given below:

Table: Admin

\[ \begin{array}{|c|c|c|c|} \hline \textbf{S\_id} & \textbf{S\_name} & \textbf{Address} & \textbf{S\_type} \\ \hline S001 & Sandhya & Rohini & Day Boarder \\ S002 & Vedanshi & Rohtak & Day Scholar \\ S003 & Vibhu & Raj Nagar & NULL \\ S004 & Atharva & Rampur & Day Boarder \\ \hline \end{array} \]

Table: Transport

\[ \begin{array}{|c|c|c|} \hline \textbf{S\_id} & \textbf{Bus\_no} & \textbf{Stop\_name} \\ \hline S002 & TSS10 & Sarai Kale Khan \\ S004 & TSS12 & Sainik Vihar \\ S005 & TSS10 & Kamla Nagar \\ \hline \end{array} \]

Write SQL queries for the following:

1. Display the student name and their stop name from the tables Admin and Transport.
2. Display the number of students whose S_type is not known.
3. Display all details of the students whose name starts with 'V'.
4. Display student ID and address in alphabetical order of student name, from the table Admin.

Hint:

Use table aliases to simplify SQL queries and ensure clarity when referencing multiple tables in joins or conditions.

Answer 1

Query (i): Display the student name and their stop name from the tables Admin and Transport.

SELECT A.S_name, T.Stop_name FROM Admin A, Transport T WHERE A.S_id = T.S_id;

Explanation:

The INNER JOIN combines the tables Admin and Transport based on the common column S_id.
The query retrieves the S_name from Admin and Stop_name from Transport.