The schema of these relations is: Courses (course, credits, days, time, room, ti
ID: 3870380 • Letter: T
Question
The schema of these relations is: Courses (course, credits, days, time, room, title, prof, dept) Profs (pname, given, office, ext, email) Students (name, given, linux, major, email) Enroll (linux, course, grade) Coreq (lect, lab) Dayx (days, dayo, day, jour) -- ex: MNF 1 Monlundi All attributes are CHARACTER, except for credits, grade, dayo and ext, which are integer. Primary keys are underlined SQL statements. here is an outline of the various clauses of SELECT. You can type any sql statement in psql, they can cover several lines, and end with a semicolon (:) Up-arrow recalls previous lines. In this outline, keywords are bold, optional things are in [square brackets] and one or more things in curly brackets) · o select (list of attributes and aggregate functionsh o from tablenames o [where [conditions) ] o [group by o [having (conditions) ]] o order by (column (s) [descending] JOIN constructs. You can't use the first form for "outer" joins) for the others, inner join is the default o from people, phone where people.id-phone.id [and ...] o from people [left I right full] join phone on (people.id phone.id) o from people [...] join phone using (id) from people natural [...] join phone o from (students natural join enroll) natural join courses o courses left join labs using(course) - to get also courses without labs Natural join won't work Why? SUBQUERIES can be placed in the where clause, for example o where linux in (select linux from students where name- 'Mouse) o where not exists (select from enroll where enroll.course courses.course) this is a co-related subquery, which must be reevaluated for each row of the outer query, involving coursesExplanation / Answer
1. To insert record into table we need to use insert table queries.
2. We need to use order by column name asc Inorder to get in ascending or alphabetical order. In this distinct is the function which remove duplicate values and as Name is the alias name. Alias names are the names which we get in the output as column headings. Below is the required query
3. select course,days,time,room,title from Courses c,Profs p where c.prof=p.pname and p.pname='Allili';
The above query retrieves the courses thaught by Allili.
4.
select * from Students s,Enroll e where s.linux=e.linux and e.course='CSE207' order by s.name ;