Scribd
Upload
3K views16 pages

DBMS - GTU Paper Solution

This document contains solutions to questions from a Database Management Systems exam paper. It provides the questions asked and the corresponding SQL queries that would produce the requested output based on the schemas provided. Key questions answered include displaying student names ending in "sh", listing students by SPI, adding primary keys to tables, and performing aggregate functions like counts and sums on table data. The document serves as a reference for students to check their SQL query answers against the solutions.

Uploaded by

Shiv Patel
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
3K views16 pages

DBMS - GTU Paper Solution

This document contains solutions to questions from a Database Management Systems exam paper. It provides the questions asked and the corresponding SQL queries that would produce the requested output based on the schemas provided. Key questions answered include displaying student names ending in "sh", listing students by SPI, adding primary keys to tables, and performing aggregate functions like counts and sums on table data. The document serves as a reference for students to check their SQL query answers against the solutions.

Uploaded by

Shiv Patel
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 16

S. S.

Agrawal Institute of Engineering & Technology, Navsari


Computer Engineering Department

GTU Paper Solution

Subject Name: Database Management Systems-3130703


GUJARAT TECHNOLOGICAL UNIVERSITY
COURSE:BE BRANCH: Compuer Engineering_ SEMESTER: 3 EXAMINATION:WINTER-2019
SUBJECT CODE: ___3130703 SUBJECT NAME: Database Management System
(1) It is compulsary to submit assessment chart and detailed solution for numerical part
(2) Preferably Use only A4 Plain paper
(3) Paper settersare requested not to reveal his/her personal identity.

Que.
No. PAPER SOLUTION/ ANSWER KEY/ ASSESSMENT CHART MARKS

Q-2 (B)

Answer

Q-4 C

Page:1 of 4
Q-7-C

Page:2 of 4
Q-8

Page:3 of 4
Page:4 of 4
Winter - 19
Q.5 (C) Consider following schema and write SQL for given statements.

Student (RollNo, Name, DeptCode, City)

Department (DeptCode, DeptName)

Result (RollNo, Semester, SPI)

1. Display the name of students with RollNo whose name ends with ‘sh’.

Sol: Select Name, RollNo from student where Name Like ‘%Sh’;

2. Display department wise total students whose total students are greater than 500.

Sol: Select Deptcode, Count(Rollno) from Student “Total student” Group by DeptCode>500;

3. List out the RollNo, Name along with SPI of Student.

Sol: Select S.RollNo, S.Name ,R.SPI from Student S, Result R where S.RollNo =R.RollNo;

4. Create RollNo field as primary key for existing Student table.

Sol: Alter Table Student Add Constraint Constraint_Name Primary Key (RollNo);

5. Display student name who got highest SPI in semester 1.

Sol: SELECT MAX(SPI) AS Higest_SPI FROM Result where Semester = 1;

6. Display the list of students whose DeptCode is 5, 6,7,10.

Sol: Select * From Student where DeptCode in(5,6,7,10);

7. Create table Student_New from student table without data.

Sol: CREATE TABLE Student_New AS (SELECT * FROM Student5 WHERE 1=2);

OR
Q.5 (C) Consider the tables given below. Write the SQL queries for the questions given below:

T1 ( Empno, Ename , Salary, Designation)

T2 (Empno, Deptno.)

(1) Display all the details of the employee whose salary is lesser than 10000.

Sol: Select * from T1 where salary<10000;

(2) Display the Deptno in which Employees with name starting with letter ‘S’ is working.

Sol: Select E.Ename,D.Deptno from T1 E, T2 D where Ename Like 's%';

(3) Add a new column Deptname in table T2.

Sol: Alter Table T2 ADD Deptname varchar2(20);

(4) Change the designation of Geeta from ‘Manager’ to ‘Senior Manager’.

Sol: Update T1 Set Designation ='Senior Manager' Where Ename = 'Geeta';

(5) Find the total salary of all the employees department wise.

Sol: Select E.Ename , E.Salary, D.Deptno,Sum (Salary) "Total_Salary" From T1 E, T2 D Group By

D.Deptno ;

(6) Add Empno as primary key in existing table T1.

Sol: Alter Table T1 Add Constraint Constraint_Name Primary Key (Empno);

You might also like