Mid Term Examination Time: 90 minutes Question 1: (2 points) Given the following schemas:
CARS (Brand, Model, Type, Engine, Seats, Price)
Use Relational Algebra to express following constraints:
1. With a same brand, the higher model, the higher price it is.
2. If price >= 50.000$ then model >=2014 and type=’sedan’ or seats >=6. Question 2: (8 points) Given the following tables: - Table name: STUDENT Student_I Fullname DOB Sex Address Class_I 1 Nguyễn Văn 01/03/1990 True 12 Nguyễn 3 3 Trần Thị Bình
21/02/1989 False 8/12 Lê Đức 5 4
Lê Văn Chiến 16/12/1991 True 5 Hoàng Quốc 3 7 Phạm
Thanh 22/6/1990 False 18 Tây Sơn 7 - Table name: CLASS Class_I Name 3 K14TT 5 K13SPT 6 K15MN 7 K13 TH - Table name: Subject Subject_ID Name No_Credit DBI Database 3 PRF Programming Fundamental 3 PRJ Java programming 4 1 - Table name: Result Student_ID Subject_ID Time Mark 1 DBI 1 4 2 DBI 1 6 1 DBI 2 8 4 PRF 1 9
(Student can insert more data to implement.) Requirements:
Write the SQL statements to perform the following requests:
1. Create all these tables (1 point)
2. Insert data to these tables. (1 point)
3. Show the information of all students: Student_ID, FullName, DOB,
Address, Class Name, Subject Name, Mark (of the first time). (1 point)
4. Update the No_Credit of DBI subject to 5. (1 point)
5. List all subject names that haven’t got any student learn.(1 point)
6. Calculate avg, max, min mark of each subject in the first time.(1 point)
7. Show all students who have birthday in March. (1 point)
8. Count the number of male/female students in each class.(1 point) Finish