CMPE353/CMSE351 Midterm Study Questions PDF

EASTERN MEDITERRANEAN UNIVERSITY DEPARTMENT OF COMPUTER ENGINEERING CMPE353/CMSE351 Midterm Examination Multiple Choice Study Questions Prof. Dr. Ekrem Varoğlu Q1. Database __________ is the logical design of the database, whereas database _______ contains a snapshot of the data in the...

EASTERN MEDITERRANEAN UNIVERSITY DEPARTMENT OF COMPUTER ENGINEERING CMPE353/CMSE351 Midterm Examination Multiple Choice Study Questions Prof. Dr. Ekrem Varoğlu Q1. Database __________ is the logical design of the database, whereas database _______ contains a snapshot of the data in the database at a given instant in time. a) Instance, Schema b) Relation, Schema c) Relation, Domain d) Schema, Instance Q2. Which one of the following attributes is more likely to be selected as the primary key for a relation which contains data about a student? a) Name b) Street c) Id d) Department Q3. An attribute in a relation can be used as a foreign key if the _______ key from another relation is used as an attribute in that relation. a) Candidate b) Primary c) Super d) Sub Q4. What type of a statement is? CREATE TABLE student (name VARCHAR, id INTEGER) a) DML b) DDL c) View d) Integrity constraint Q5. Which of the following queries given below can replace the query: SELECT name, course_id FROM instructor, teaches WHERE instructor_ID= teaches_ID; a) Select name,course_id from teaches,instructor where instructor_id=course_id; b) Select name, course_id from instructor natural inner join teaches; c) Select name, course_id from instructor; d) Select course_id from instructor join teaches; Q6. Given the relation employee(id, name, salary, dept_id).Which of the following columns are displayed as output following the query: SELECT * FROM employee WHERE salary>10000 AND dept_id=101; a) Salary, dept_id b) Employee c) Salary d) All the columns of employee relation Q7. Which of the following statements contains an error? a) Select * from student where id = 101; b) Select id from student where id = 106; c) Select id from student; d) Select id where id = 119 and name = ‘Jane’; Q8. Which one of the following has to be inserted into the blank to select the dept_name which has Computer Engineering as its ending string in the following query? SELECT emp_name FROM department WHERE dept_name LIKE ’ _____ Computer Engineering’; a) % b) _ c) || d) $ Q9. Which of the following queries given below can replace the query: SELECT name FROM instructor WHERE salary = 90000; a) SELECT name FROM instructor WHERE salary BETWEEN 90000 AND 100000; b) SELECT name FROM employee WHERE salary =100000; c) SELECT name FROM employee WHERE salary BETWEEN 90000 AND 100000; d) SELECT name FROM instructor WHERE salary BETWEEN 100000 AND 90000; Q10. The union operation automatically __________. a) Adds tuples into a table b) Eliminates unique tuples c) Adds common tuples d) Eliminates duplicate tuples Q11. Consider the query given below. After execution, this query will display; (SELECT emp_id FROM employee WHERE dept = ’Toys’) EXCEPT (SELECT emp_id FROM employee WHERE dept = ’Cars’); a) Only tuples from the second part b) Tuples from the first part which are also present in the second part c) Tuples from both parts d) Tuples from the first part but not tuples from the second part Q12. Suppose the attribute phone number is included in a relation, however some phone numbers are not known during first data entry. The missing values will be given as: a) 0 b) Unknown c) Null d) Empty space Q13. The primary key attribute must be: a) Unique b) Not null c) Both Unique and Not null d) Either Unique or Not null Q14. Consider the following query: SELECT COUNT (____ ID) FROM student WHERE semester = ’Spring’ AND YEAR = 2020; We must use the keyword ______in the aggregate expression if we want to eliminate duplicates. a) Distinct b) Count c) Avg d) Primary key Q15. The phrase “greater than at least one” is represented in SQL by _____. a) < all b) < some c) > all d) > some Q16. Which one of the following deletes all the entries but keeps the structure of the relation? a) Delete from r where P; b) Delete from instructor where dept name= ’Finance’; c) Delete from instructor where salary between 13000 and 15000; d) Delete from instructor; Q17. Which of the following is used to insert a tuple from another relation? a) INSERT INTO course (course id, title, dept name, credits) VALUES (’CS-437’, ’DATABASE Systems’, ’Comp. Sci.’, 4); b) INSERT INTO instructor SELECT ID, name, dept name, 18000 FROM student WHERE dept name = ’Music’ AND tot cred > 144; c) INSERT INTO course VALUES (’CS-437’, ’DATABASE Systems’, ’Comp. Sci.’, 4); d) It is not possible to insert a tuple from another relation. Q18. What type of join is used when we want to include rows that do not have matching values? a) Equi-join b) Natural join c) Outer join d) All of the above Q19. Which of the following is the correct syntax for declaring a view where v is view name? a) Create view v as “query name”; b) Create “query expression” as view; c) Create view v as “query expression”; d) Create view “query expression”; Q20. Consider the following query: CREATE TABLE Manager(ID NUMERIC,Name VARCHAR(20),budget NUMERIC,Details VARCHAR(30)); Which of the following integrity constraints should be used to ensure that the value of attribute budget is non-negative? a) Check(budget>0) b) Check(budget0) d) Alter(budget