Lab 3
Group : C Date of Submission : Feb 4 2010
1. Write the following simple SQL Queries on the University Schema
Find the names of all the instructors from Comp. Sci. department
Find the course id and titles of all courses taught by an instructor named 'Srinivasan'
Find the ID and name of instructors who have taught a course in the Comp. Sci. department, even if they are themselves not from the Comp. Sci. department. To test this query, make sure you add appropriate data, and include the corresponding insert statements along with your query.
Find the courses which are offered in both 'Fall' and 'Spring' semester.
Find names of instructors who have taught at least one course in Spring 2009
The Next task is to Create the Railway schema using the commands in the Railway DDL script; the commands can be copy-pasted into pgAdmin3. The DDL script also contains a description of the railway schema. Refer to that for clarity.
Insert sample data using the command in the file SampleRailwayData.
Try inserting more data into these tables.
Write the following Queries for Railway Schema
Find the pairs of stations(station codes) which have a track with distance less than 20Kms between them.
Find the IDs of all the trains which have a stop at THANE
Find the names of all trains that start at MUMBAI.
List all the stations in order of visit by the train 'CST-AMR_LOCAL'.
Find the name of the trains which stop at Thane, before the 6th stop in the route of the train.
--
Head, Dept of Computer Science and Engineering
First Floor , Engineering Block I,
Chandigarh Engineering College, Landran (Mohali)-140307, Punjab, India
Phone : +91-0172-3984200 Ext 216
+91-0172-3984216 (Direct)
+ 91- 9781925246 (Mobile)
Fax : +91-0172-3984207
E-Mail:cecm.cse@gmail.com
URL : www.cecmohali.org/cse.asp
soultions
4. List all the stations in order of visit by the train 'CST-AMR_LOCAL'.
ans plze