Question

Define SQL views Q6(num1,num2,num3), which give the number of, respectively • distinct local students enrolled in 10S1 in stream(s) named Chemistry. • distinct international students enrolled in 10S1 in programs offered by Faculty of Engineering. • all the distinct students enrolled in 10S1 in the Computer Science (3978) program. Note: • the student IDs are the UNSW ids (i.e. student numbers) defined in the People.unswid field. • Do not count duplicate records.

Answer 1

1.Create view student_view1 AS SELECT distinct *  FROM People where country='USA' AND enroll='10S1' AND stream='chemistry';

Note:Syntax of View is

CREATE VIEW VIEW_NAME AS SELECT column1,column2....FROM TABLE_Name;

So student_view1 is the view name and distinct keyword is used to select rows without duplication.

Country = 'USA' in where clause gives local students

2.Create view student_view2 AS SELECT distinct * FROM People where country != 'USA' AND enroll='10S1' AND stream='Faculty of Engineering';

Note:Same as above. But for international students,means students outside of USA and stream is faculty of engineering.

3.Create view student_view3 AS SELECT distinct * FROM People where enroll='10S1' AND stream='Computer Science';.

Note:For all students we do not specify the country. Only enroll and Stream are specified.