Class: TE(IT) Subject: DBMS Assignment II Semester I (2018-19)

MGM’s College of Engineering, Nanded.
Department of IT
Semester I (2018-19)
Class: TE(IT)       Subject: DBMS         Assignment II

1. Classify the parts of SQL language.

2. Discuss the built-in data types supported by SQL? What is the difference between char and varchar in SQL?

3. What are the different integrity constraints supported by SQL? Explain briefly.

4. Design a database in SQL for the university database having following relations: Department, Course, Instructor, Section and Teaches. Use appropriate primary keys and foreign keys for the relations.

5. Categorize built-in set operations in SQL with examples.

6. Explain how the GROUP BY clause works. Differentiate between WHERE and HAVING clauses with the help of an example.

7. Construct queries for different Join Operations in SQL?

8. Explain the concept of nested queries with examples.

9. Discuss the different operators in SQL, which are used with subqueries/nested queries.

10. Analyze the purpose of view in SQL. Construct a SQL query for creating a view?

11. Create the following queries in SQL, using the university schema. (Refer Question 3.1 Korth 6^th edition)

a. Find the titles of courses in the Comp. Sci. department that have 3 credits

b. Find the IDs of all students who were taught by an instructor named Einstein; make sure 

there are no duplicates in the result.

c. Find the highest salary of any instructor.

12. Create the following inserts, deletes or updates in SQL, using the university schema.

(Refer Question 3.3 Korth 6^th edition)

a. Increase the salary of each instructor in the Comp. Sci. department by 10%.

b. Delete all courses that have never been offered (that is, do not occur in the section relation)

c. Insert every student whose tot cred attribute is greater than 100 as an instructor in the same 

department, with a salary of $10,000.

13. Consider the following insurance database , where the primary keys are underlined. 

  (Refer Question 3.4 Korth 6^th edition)

person (driver id, name, address)
car (license, model, year)
accident (report number, date, location)
owns (driver id, license)
participated (report number, license, driver id, damage amount)

Construct the following SQL queries for this relational database.

a. Find the total number of people who owned cars that were involved in accidents in 2009.

b. Add a new accident to the database; assume any values for required attributes.

c. Delete the Mazda belonging to “John Smith”.

14. List the built-in aggregate functions in SQL with an appropriate example queries.

15. Explain scalar queries with an example.

16. Categorize the string matching operations in SQL with example queries.

Faculty Incharge: Hashmi S A