Question 31
2 April 2024 · Computer Science (083)
Consider the tables Admin and Transport given below :
Table : Admin
| S_id | S_name | Address | S_type |
|---|---|---|---|
| S001 | Sandhya | Rohini | Day Boarder |
| S002 | Vedanshi | Rohtak | Day Scholar |
| S003 | Vibhu | Raj Nagar | NULL |
| S004 | Atharva | Rampur | Day Boarder |
Table : Transport
| S_id | Bus_no | Stop_name |
|---|---|---|
| S002 | TSS10 | Sarai Kale Khan |
| S004 | TSS12 | Sainik Vihar |
| S005 | TSS10 | Kamla Nagar |
Write SQL queries for the following : (i) Display the student name and their stop name from the tables Admin and Transport. (ii) Display the number of students whose S_type is not known. (iii) Display all details of the students whose name starts with 'V'. (iv) Display student id and address in alphabetical order of student name, from the table Admin.
Answer
(i) SELECT S_name, Stop_name FROM Admin, Transport WHERE Admin.S_id = Transport.S_id;
(ii) SELECT COUNT(*) FROM Admin WHERE S_type IS NULL;
(iii) SELECT * FROM Admin WHERE S_name LIKE 'V%';
(iv) SELECT S_id, Address FROM Admin ORDER BY S_name;
Explanation
(i) Equi-join on S_id. (ii) IS NULL counts unknown S_type. (iii) LIKE 'V%' matches names starting with V. (iv) ORDER BY sorts alphabetically.