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): Retrieve the student name and their associated stop name from the Admin and Transport tables.

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

Explanation:

This query utilizes an INNER JOIN to merge the Admin and Transport tables. The join condition is the common column S_id. The result set includes the S_name from the Admin table and the Stop_name from the Transport table.