Fundamentals of Database

Fundamentals of Database

M. Marks.: 10
Perform the following operations using SQL.
1. Create a Database (’fdb’) using any one from Oracle, Sybase, DB-2, SQL Server or MySQL
2. Create the following tables and fill them with the data.
Table Name: students
+-+-+-+-+-+
l id I name I birth I gpa l grad l
+-+-+-+-+-+
l 1 I Anderson I 1987-10-22 I 3.9 l 2009 l
l 2 l Jones I 1990-04-16 I 2.4 l 2012 l
l 3 l Hernandez l 1989-08-12 I 3.1 l 2011 l
l 4 l Chen l 1990-02-04 I 3.2 l 2011 l
+-+-+-+-+-+
Table Name: courses
+-+-+-+-+
l id l number I name I quarter l
+-+-+-+-+
l 1 l C8142 l CP 11 l Winter 2015 l
l 2 l ART101 l Finger painting I Fall 2014 l
l 3 l ART101 l Finger painting I Winter 2015 l
l 4 l PE204 l Mud wrestling l Winter 2015 l
+-+-+-+-+
Create a join tab/e courses_students describing which students took which courses.
+-+-+
l course_id l student_id l
+-+-+
l 1 l 1 l
I 3 I 1 l
l 4 l 1 l
l 1 l 2 l
l 2 l 2 l
l 1 l 3 l
l 2 l 4 l
l 4 l 4 l
+-+-+
3. Write SQL query to select students having GPA greater than 3 and the result should be sorted
according to name.
4. Update student’s (Jones) GPA to 2.5.
5. Find all students who took a C5142 course.
6. Delete record of student named ”Jones”.
7. Add one column (“degree”) to students table.
8. Delete table courses_students.