Sample Solution to Practice Queries

Q1  Write a query that counts the students for every major (in the majorsin relation) and prints the count and the department name


SELECT count(*),dept 
FROM MajorsIn 
GROUP BY dept;



Q2. Write a query to find all the rooms whose names start with the word 'cas' (case insensitive).

SELECT * 
FROM Room 
WHERE name like 'cas%';


Q3. Write the name and the capacity of the room that course cs105 is taught


SELECT Room.name,capacity 
FROM Course,Room 
WHERE id=room AND Course.name='CS 105';


Q4. Write a query that lists all departments whose names consists of more than one words.

SELECT * 
FROM department 
WHERE name like '% %';



Q5. Write a query that selects all the names of the rooms with 50<capacity<250 or with a name that does not contain the letter 'A'

SELECT name 
FROM Room 
WHERE (capacity>50 AND capacity<250) OR (name NOT LIKE '%A%');



Q6. Write a query that retrieves all departments that are assigned to some office.


SELECT name 
FROM department 
WHERE office IS NOT NULL;



Q7. Write a query that counts the students majoring in computer science


SELECT count(*) 
FROM MajorsIn 
WHERE dept = 'comp sci';


Q8. Write a query that outputs the names, the courses, the room, the start_times and end_times of all undergraduate students(join 4 tables!)


SELECT s.name AS student, c.name AS course, r.name AS room, c.start_time, c.end_time
FROM student AS s, course AS c, enrolled as e,room AS r
WHERE s.id=e.student AND c.name=e.course AND c.room=r.id AND e.credit_status='ugrad';



Q9. Create a list of all students who are not enrolled in 'CS 105'


SELECT name
FROM Student
WHERE id NOT IN (SELECT student
                	 FROM Enrolled
	                 WHERE course = 'CS 105');