Geetanjali College of Science & Commerce (GCSC)

(Approved by Government of Rajasthan, Mohan Lal Shukadiya University, Udaipur (Raj.)

Airport Road – Dabok, Udaipur
Department of Computer Applications (BCA)

Index

Ex.
Program
No.

Write the SQL queries for data definition language and its
1.
commands.(Create , Alter, Drop, Rename)

Write the SQL queries for data manipulation language and its
2.
commands.(Insert, Update, Delete)

Write the SQL queries for listing the table contents using t he Select
3.
command.

4. Write the SQL queries for manipulation of rows in a table.

Write the SQL queries that implement various data constraintson the table.
5.

To create and manipulate various database objects of the Table using

6.
views.
 Lab Manual
Database management system lab

EXPERIMENT NO: 1

Environment: Microsoft Windows

Tools/ Language: Oracle/SQL

Objective: Write the SQL queries for data definition language and its commands

Program 1:
Create table employee which consist of following attributes
EMPLOYEE TABLE
COLUMN NAMES TYPE
EMP_NO NUMBER(5) NOT NULL
EMP_NAME VARCHAR(30)
DESIGNATION CHAR(10)
DOJ DATE
SALARY NUMBER(9,2)
ADDR VARCHAR(30)
DEPT_NAME CHAR(30)

Perform the following SQL queries

1. Create table Employee which consist of above attributes.

2. Alter table name with data type of DEPT_NAME as VARCHAR.

3. Rename table name as EMPLOYEE_TABLE.

4. Rename table name as Employee.

5. Drop table EMPLOYEE_TABLE.

DEPARTMENT OF COMPUTER ENGINEERING & APPLICATIONS Page | 2

 Lab Manual
Database management system lab

Solution:

1. Create table Employee(Emp_no number(5) not null, Emp_name varchar(30),

Designation Varchar (10), DOJ date, Salary number(9,2), ADDR
varchar(30) , Dept_Name char(30));

2. Alter table Employee modify (Dept_Name varchar(30));

3. Rename Employee to Employee_Table;

4. Rename Employee_Table to Employee;

5. Drop table Employee;

DEPARTMENT OF COMPUTER ENGINEERING & APPLICATIONS Page | 3

 Lab Manual
Database management system lab

Program 2:

Customer Table
Column name Datatype
CustomerId Varchar2(6)
CustomerName Varchar2(30)
DateOfReg Date
UserId Varchar2(15)
Password Varchar2(15)

Perform the following SQL queries

1. Create table Customer which consist of above attributes.

2. Alter Table name with Data Type of Password as VARCHAR2 (20).

3. Rename Table name as Customer_table.

4. Rename Table name as Customer.

5. Drop Table Customer.

DEPARTMENT OF COMPUTER ENGINEERING & APPLICATIONS Page | 4

 Lab Manual
Database management system lab

Solution:

1. Create table customer (CustomerId varchar2(6), CustomerName

varchar2(30), DateOfReg Date, UserId varchar2(15), Password
varchar2(15));

2. Alter table Customer modify ( Password varchar2(20) );

3. Rename Customer to Customer_table;

4. Rename Customer_table to Customer;

5. Drop table Customer;

Pre Experiment Questions

1. What is DBMS?

2. What are RDBMS?

Post Experiment Questions

1. Difference between RDBMS and DBMS?

2. What is SQL?

DEPARTMENT OF COMPUTER ENGINEERING & APPLICATIONS Page | 5

 Lab Manual
Database management system lab

EXPERIMENT NO: 2

Environment: Microsoft Windows

Tools/ Language: Oracle/SQL

Objective: Write the SQL queries for data manipulation language and its commands

Program 1:

Insert the following data in Employee table

EMP EMP
DESIGNATION DOJ SALARY ADD DEPT_NAME
NO NAME
1001 Amit Officer 21-Dec-1995 1000 Mathura Marketing
1002 Sumit Clerk 14-May-1982 500 Delhi Accounts
1003 Raj Manager 23-Dec-1984 3500 Bombay Sales
1004 James Analyst 22-Jul-1990 5000 Mathura Software
1005 Amit Analyst 22-Jul-1990 4900 Delhi Production
1006 Jones Clerk 16-Apr-1986 950 Delhi Production

Perform the following SQL queries

1. Insert into table Employee which consist of above attributes

2. Alter Table by deleting DEPT_NAME attributes.

3. Alter Table by adding DEPT_NAME attributes.

DEPARTMENT OF COMPUTER ENGINEERING & APPLICATIONS Page | 6

 Lab Manual
Database management system lab

Solution:
1. Insert into Employee values (‘&Emp_no’, ’&Emp_Name’, ‘&Designation’,
’&DOJ’, ‘&Salary’, ’&Addr’,’ &Dept_Name’);

2. Alter Table Employee drop (Dept_Name);

3. Alter Table Employee Add(Dept_Name varchar2(15));

DEPARTMENT OF COMPUTER ENGINEERING & APPLICATIONS Page | 7

 Lab Manual
Database management system lab

Program 2:
Insert the following data in Customer table

Customer Customer
DORegistration User_Id Password
Id Name
1001 Amit 21-Dec-1995 [email protected] Mathura
1002 Sumit 14-May-1982 [email protected] Delhi
1003 Raj 23-Dec-1984 [email protected] Bombay
1004 James 22-Jul-1990 [email protected] Mathura
1005 Amit 22-Jul-1990 [email protected] Delhi
1006 Jones 16-Apr-1986 [email protected] Delhi

Perform the following SQL queries

1. Insert into table Customer which consist of above attributes

2. Alter Table by deleting password attribute.

3. Alter Table by adding password attribute.

DEPARTMENT OF COMPUTER ENGINEERING & APPLICATIONS Page | 8

 Lab Manual
Database management system lab

Solution:
1. Insert into Customer values (‘&CustomerId’, ’&Customer_Name’,
‘&DateOfRegistration’, ’&UserId’, ‘&Password’);

2. Alter Table Customer Drop (Password);

3. Alter Table Customer Add (Password varchar2 (15));

Pre Experiment Questions

1. What is file system?

2. What are attributes?

Post Experiment Questions

1. Define DDL?

2. How to create a Table?

3. What is the difference between DDL & DML commands?

DEPARTMENT OF COMPUTER ENGINEERING & APPLICATIONS Page | 9

 Lab Manual
Database management system lab

EXPERIMENT NO: 3

Environment: Microsoft Windows

Tools/ Language: Oracle/SQL

Objective: Write the SQL queries for listing the contents of the table using Select command.

Program 1.

Perform the following SQL queries based on Experiment No.1 and Experiment No.2

1. Display all table attributes of Employee using Select command.

2. Display all table attributes of Customer using Select command.

3. Display Customer id and customer name attributes of Customer using Select

command.

4. Display Employee Name, Salary and Department attributes of Customer using Select
command.

5. Using Select Display name of Employee having Designation Analyst.

6. Using Select Display name of customers having Password as Delhi.

DEPARTMENT OF COMPUTER ENGINEERING & APPLICATIONS Page | 10

 Lab Manual
Database management system lab

Solution:
1. Select * from Employee;

2. Select * from Customer;

3. Select CustomerId, CustomerName from Customer;

4. Select Emp_Name, Salary, Dept_Name from Employee;

5. Select Emp_Name from Employee where designation = 'Analyst';

6. Select CustomerName from Customer where password = ‘delhi’;

DEPARTMENT OF COMPUTER ENGINEERING & APPLICATIONS Page | 11

 Lab Manual
Database management system lab

Program 2.
Create table employee which consist of following attributes

EMPLOYEE TABLE

COLUMN NAMES TYPE

EMP_NO NUMBER(5) NOT NULL
EMP_NAME VARCHAR(30)
DESIGNATION CHAR(10)
DOJ DATE
SALARY NUMBER(9,2)
ADDR VARCHAR(30)
DEPT_NAME CHAR(30)

Insert the following data in above table

EMP EMP DEPT_NA

DESIGNATION DOJ SALARY ADD
NO NAME ME
1001 Amit Officer 21-Dec-1995 1000 Mathura Marketing
1002 Sumit Clerk 14-May-1982 500 Delhi Accounts
1003 Raj Manager 23-Dec-1984 3500 Bombay Sales
1004 James Analyst 22-Jul-1990 5000 Mathura Software
1005 Amit Analyst 22-Jul-1990 4900 Delhi Production
1006 Jones Clerk 16-Apr-1986 950 Delhi

Perform the following SQL queries

1. List the Emp name, doj from employee table

2. List the name of employee who is getting 1000 Rs.
3. List the name of emp who belong to Mathura and work in MARKETING department.
4. List the name of emp who are getting their salary in between 800 and 2500.
5. List the employees who are earning more than 1200 but less than 4000.
6. List the employees who have joined after 1st Jan 84 in the order of the joining date.
7. List the employees are located at MATHURA
8. List the employees who are in sales dept.
9. List the departments that are located in Delhi.
10. List the employees who are not work in Delhi.
11. List names of all employees whose designation is 'Analyst’.

DEPARTMENT OF COMPUTER ENGINEERING & APPLICATIONS Page | 12

 Lab Manual
Database management system lab

Solution:
Insert into Employee values (‘&Emp_no’, ’&emp_Name’, ‘&Designation’,
’&Doj’, ‘&Salary’, ’ &Addr’, ’& Dept_Name’);

1. Select EMP_NAME, DOJ from EMPLOYEE;

2. Select EMP_NAME from EMPLOYEE where SALARY = 1000;

3. Select EMP_NAME from EMPLOYEE where ADDR = ‘Mathura’ and

Dept_Name = ‘Marketing’;

4. Select EMP_NAME from EMPLOYEE where Salary > 800 and Salary <2500;

5. Select EMP_NAME from EMPLOYEE where Salary > 1200 and Salary <4000;

6. Select EMP_NAME from EMPLOYEE where DOJ > '01-JAN-1984';

7. Select EMP_NAME from EMPLOYEE where ADDR = MATHURA;

8. Select EMP_NAME from EMPLOYEE where DEPT_NAME = ‘SALES’;

9. Select DEPT_NAME from EMPLOYEE where ADDR = ‘DELHI’;

10. Select EMP_NAME from EMPLOYEE where ADDR ! ='DELHI';

11. Select EMP_NAME from EMPLOYEE where DESIGNATION = ‘ANALYST’;

Pre Experiment Questions

1. What is Relation?

2. What are attributes?

3. How do you insert rows repeatedly using & operator?

Post Experiment Questions

1. What are the different types of databases used?

2. What is the use of where clause?

3. What is the role of DUAL in oracle?

4. How to retrieve specific contents of a Table?

DEPARTMENT OF COMPUTER ENGINEERING & APPLICATIONS Page | 13

 Lab Manual
Database management system lab

Experiment No. 4

Environment: Microsoft Windows

Tools/ Language: Oracle

Objective: Write the SQL queries for manipulation of rows in a table.

Program 1.

Perform Queries for each of the following based on previous tables:

1. Display the description of the EMP table.

2. Display the details of all employees.
3. Display unique Jobs from EMP table.
4. List the employee names those are having five characters in their Names.
5. List the employee names those are starting with ‘J’ and with five characters.
6. List the emp. names those are having six character and third char. must be ‘r’.
7. List the employee names starting with ‘J’ and ending with ‘n’.
8. List the employees in the order of their Salaries.
9. List the details of the employees in order of the ascending of Deptno and descending of
Jobs.

10. List the names of employees who work in either ‘officer’, ‘manager’ or ‘clerk’
position.

11. Delete all employees in the department number 20, delete the department no 20 from
department table as well.

12. Modify the salaries of all employees by giving 10% raise in their salaries.

13. Increment the salary of the employees by 1000 whose salary is less than 1000 and
belong to dept no 30.

14. Delete the employees who are getting less than 1000 rupees.

DEPARTMENT OF COMPUTER ENGINEERING & APPLICATIONS Page | 14

 Lab Manual
Database management system lab

Solution:

1. desc EMPLOYEE;

2. Select * from EMPLOYEE

3. Select distinct DESIGNATION from EMPLOYEE;

4. Select EMP_NAME from EMPLOYEE where EMP_NAME like '…..';

5. Select EMP_NAME from EMPLOYEE where EMP_NAME like 'J ... ';

6. Select EMP_NAME from EMPLOYEE where EMP_NAME like '..r.';

7. Select EMP_NAME from EMPLOYEE where EMP_NAME like 'J%n’;

8. Select EMP_NAME from EMPLOYEE order by SALARY;

9. Select * from EMPLOYEE order by DEPT_NO, DESIGNATION desc;

10. Select EMP_NAME from EMPLOYEE where DESIGNATION IN ('officer',

'Manager', 'Clerk');

11. Delete from EMPLOYEE where DEPT_NO IN (20);

12. Update EMPLOYEE set SALARY = 1.1 × SALARY;

13. Update EMPLOYEE set SALARY = 1000 + SALARY where SALARY< 1000 and
DEPT_NO = 30;
15. delete from EMPLOYEE where SALARY <1000;

DEPARTMENT OF COMPUTER ENGINEERING & APPLICATIONS Page | 15

 Lab Manual
Database management system lab

Program 2.

Work on data as in previous experiments

1. Count the total number of employees.

2. Calculate the average salary of all employees.

3. Determine the minimum and maximum salaries. Rename the titles as ‘max_sal’ and
‘min_sal’ respectively.

4. Count the number of employees having salary greater than or equal to Rs. 1000.

5. Find Maximum, Average, Minimum, total salary of all employee of Company.

6. Find total number of employee in company.

7. Count the different designations of employees working in company.

8. Find total no. of department in company.

9. Find average of salary round off to 2 decimal places.

10. Display the age of all employees.

11. Find year of DOB of employees of DNO 20.

12. Display name of all employees in uppercase and designation in lowercase.

13. Find second alphabet of each employee’s name. (Hint: use substring)

14. Consider the string “Peter Piper picked a peck of pickled peppers. A peck of pickled
peppers Peter Piper picked. If Peter Piper picked a peck of pickled peppers, where the
peck of pickled peppers Peter Piper picked?”
Find 6th occurrence of string ‘pick’. (Hint: use INSTR)

15. Consider String ‘SatyaNadella’ replace this using the key (use translate)
A d E L N S t Y
1 2 3 4 5 6 7 8

16. Display date ‘2014-02-26’ as ‘February 26, 2014’ and ‘26/02/2014’

17. Convert the text ’26/02/2014’ to date.

Solution:

1. Select count (EMP_NAME) from EMPLOYEE;

2. Select AVG(SALARY) from EMPLOYEE;

DEPARTMENT OF COMPUTER ENGINEERING & APPLICATIONS Page | 16

 Lab Manual
Database management system lab

3. Select MAX(SALARY) as 'max_sal' and MIN(SALARY) as 'min_sal' from

EMPLOYEE;

4. Select count (SALARY) from EMPLOYEE where SALARY >=1000;

5. Select MAX(SALARY) as 'max', AVG(SALARY) as 'avg', MIN(SALARY) as

'min' and SUM(SALARY) as 'total' from EMPLOYEE;

6. Select count (EMPLOYEE) from EMPLOYEE;

7. Select count (distinct DESIGNATION) from EMPLOYEE;

8. Select count (distinct DEPT_NAME) from EMPLOYEE;

9. Select ROUND (AVG(Salary), 2) from EMPLOYEE;

10. Select extract (year from DATE_OF_JOIN) from EMPLOYEE where DEPT_NO
= 20;

11. Select UPPER(EMP_NAME) and LOWER (DESIGNATION) from EMPLOYEE;

12. Select SUBSTR(EMP_NAME, 2, 1) from EMPLOYEE;

13. Select instr('Petor Piter piked a pech of pickled peppers. A peck of

pickled peppers Peter Piper picked. If Peter Piper picked a peck of
pickled peppois, Where the peck of pickled peppoes Peter Piper
picked?', 'pick', 1, 6) from dual;

14. Select_translate ('SatyaNodella', 'adeLNSty', '12345678') from dual;

15. Select To_CHAR (date'2014-02-26', 'Month DD, YYYY') from dual;

16. Select To_CHAR (date'2014-02-26', 'DD/MM/ YYYY') from dual;

17. Select TO_DATE ('26/02/2014', 'DD/MM/YYYY') from dual;

Pre Experiment Questions

1. What are aggregate functions?
2. What is data-type of date?
3. Why we use update command?
4. What is the need of pattern matching?

Post Experiment Questions

1. Give names of string functions.
2. How to extract year from date?
3. What is the difference between truncate and delete?
4. How we use IN command?

DEPARTMENT OF COMPUTER ENGINEERING & APPLICATIONS Page | 17

 Lab Manual
Database management system lab

Experiment No. 5

Environment: Microsoft Windows

Tools/ Language: Oracle

OBJECTIVE: Write the SQL queries that implement various types of data constraints on the
table.

Program 1.
Create the following tables and specify constraints at the time of creation.

Employee
Column Data
Size Constraint
Name Type
Empno Number 3 primary key
Ename varchar2 20 Unique
Designation varchar2 20 not null
Salary Number 7,2 default 25000, must lie between 15000 and 50000
DOB Date not null
Dno Number 3 foreign key (references department)

Department

Column Name Data Type Size Constraint

Deptno Number 3 primary key
Dname varchar2 20 Unique
Location varchar2 20 not null, department are located in Delhi, Pune, Agra

Perform the above creation in both as a column level and a table level constraint.

DEPARTMENT OF COMPUTER ENGINEERING & APPLICATIONS Page | 18

 Lab Manual
Database management system lab

Solution:

1. Create table employee (empno number(5) primary key,

ename varchar2(20) unique,
designation varchar2(20) not NULL,
salary number(7,2) default 25000.00,
dob date not NULL,
dno number(3) references department,
constraint salary_check check (salary>15000.00 and salary<50000.00) );

2. Create table department(deptno number(3) primary key,

dname varchar2(20) unique,
addr varchar2(20) not null check (addr in ('Delhi', 'Pune', 'Agra')));

3. Create_table customer(customerid varchar2(6) primary key

check(customerid in ('C%')), customername varchar2(30) notnull, date of
reg date, userid varchar2(15) unique, password varchar2(15) notnull));

4. Create_table bankINFO (Accountno number (10), customerid varchar2(6)

unique reference customer (customerid) on delete cascade, primary
(Accountno, customerid));

5. Create_table new employee (employeeid number (6) primary key,

employee_last_name varchar2(20), cemplyee_mid_name varchar2(20),
emp_first_name varchar2(20), emp_dept varchar2(2) default 'HR',
managerid varchar2(30) reference new employee (employeeid));

6. Create table billing (billid number (4) primary key, accoutno

number(10), customerid varchar2(6), foreign key (account no, customerid)
references banINFO (account_no, customerid), Bill Date date default
SYSDATE, Payment Type varchar2(12));

DEPARTMENT OF COMPUTER ENGINEERING & APPLICATIONS Page | 19

 Lab Manual
Database management system lab

Program 2.
1. Create the schemas as specified in the experiment 7 without specifying any
constraints.

2. Add all constraints by altering the structure of the table.

3. Change the size of Deptno in Department table to 5.

4. Add a new column phone no in the Employee table keeping a constraint of not null
for this column.

5. Drop foreign key dno from employee table.

Solution:
1. Create table employee (empno number(3), ename varchar2(20), designation
varchar2(20), salary number (7, 2), dob date, dno number (3));

Create table department (deptno number(3), dname varchar2(20), location

varchar2(20);

2. Alter table employee modify (empno number(3) primary key, ename

varchar2(20) unique designation varchar2(20) not null, salary number
(7,2) default 25000.00 check (salary >15000.00 ← and salary <50000.00),
dob date not null, dno number (3) references department);

Alter table department modify (deptno number(3) primary key, dname

varchar2(20) unique, location varchar2(20) not null check (location in
('Delhi', 'Pune', 'Agra'));

3. Alter table department modify (dno number(5));

4. Alter table employee add (phone_no number(10) not null);

5. Alter table employee DROP CONSTRAINT dno;

Pre Experiment Questions

1. What is Foreign Key?

2. Difference between foreign and primary key

3. What is difference between table level constraint and column level constraint?

4. Difference between unique and primary key.

DEPARTMENT OF COMPUTER ENGINEERING & APPLICATIONS Page | 20

 Lab Manual
Database management system lab

Post Experiment Questions

1. How to alter Primary Key?

2. How to update a table by enforcing constraint.

3. How to restrict domain of an attribute?

4. Explain Primary Key, Candidate Key, Super Key, Foreign Key

DEPARTMENT OF COMPUTER ENGINEERING & APPLICATIONS Page | 21

 Lab Manual
Database management system lab

EXPERIMENT NO: 6

Environment: Microsoft Windows

Tools/ Language: Oracle/SQL
Objective: To write a simple queries by using views.
Program 1.

Library
Sno Author Subject Publication Price
1 balagursamy Computer science tmh 10000
2 P.K. Sinha Electrical O’reilly 12000
3 T. Khalil Management TMH 20000
4 James brien Machanical phm 15000

Docdet
Docid Name addr wardno DOB Cellno Area
8 Mathi Mtr 5 DSP 1 10000
9 Arjun agra 6 SP 2 12000
112 Gugan Mtr 4 ASP 2 20000
12 Karthik agra 3 CP 1 15000

1. Crate a view of library having subject and author attributes with the name as lib_view.

2. Display all the information of lib_view.

3. Display the structure of lib_view.

4. Display the record having subject name computer science in lib_view.

5. Create a view (lib_view1) from table docdet having name ,dob and cellno attributes
(renamed) of doctor whose id is 12.

6. Display the structure of lib_view1;

7. Create a view (lib_view3) from table docdet having name, addr and wardno attributes of
doctor whose id is 112.

8. Update the lib_view3 view ;

9. Drop the lib_view.

DEPARTMENT OF COMPUTER ENGINEERING & APPLICATIONS Page | 22

 Lab Manual
Database management system lab

Solution:-

1. SQL> create view lib_view as select subject, author from library;

View created.

2. SQL> select * from lib_view;

SUBJECT AUTHOR

computer science balagursamy

Display the structure of lib_view.

3. SQL>desclib_view;
Name Null? Type

SUBJECT CHAR(25)
AUTHOR CHAR(25)

4. SQL> select author from lib_view where subject='computer science';

AUTHOR

Balagursamy

5. SQL> create view lib_view1(name, dob,cellno) as select docname,

docid,cellno from docdet where docid=12 ;
View created.

6. SQL> desc lib_view1;

Name Null? Type

NAME VARCHAR2(5)
DOB NUMBER(10)
CELLNO NUMBER(10)

7. SQL> create view lib_view3 (name,address,wardno) as select

docname,docid,cellno from docdet where docid=112 with check option;

8. SQL>update lib_view3 libno=116 where name=’main’;

Rows selected.
SQL> alter view lib_view3 compile;
View altered.

9. SQL> drop view lib_view;

View dropped.
SQL> select * from lib_view;
no rows selected.

DEPARTMENT OF COMPUTER ENGINEERING & APPLICATIONS Page | 23

 Lab Manual
Database management system lab

Program 2.
To perform nested Queries and joining Queries using DML command.
ename

Emp

EMPNO ENAME JOB DEPTNO SAL

1 Mathi DSP 1 10000
2 Arjun SP 2 12000
3 Gugan ASP 2 20000
4 Karthik CP 1 15000

Dept

DEPTNO DNAME LOC

1 ACCOUNTING NEW YORK
2 RESEARCH DALLAS
3 SALES LONDON
4 OPERATIONS BOSTON

Student1

RegNo Name Marks2 Marks3 Result

101 Juhn 89 80 Pass
102 Raja 70 80 Pass
103 Sharin 70 90 Pass
104 Sam 90 95 Pass

Student2

Name Grade
John S
Raj S
Sam A
Sharin A

DEPARTMENT OF COMPUTER ENGINEERING & APPLICATIONS Page | 24

 Lab Manual
Database management system lab

Sub Queries

1. Display all employee names and salary whose salary is greater than minimum salary of
the company and job title starts with ‘D‘.

2. Issue a query to find all the employees who work in the same job as Arjun.

3. Issue a query to display information about employees who earn more than any employee
in dept 1.

JOIN
1. Display the Student name and grade by implementing a left outer join.

2. Display the Student namest and grade by implementing a right outer join.

3. Display the Student name and grade by implementing a full outer join.

4. Display the Student name and grade by implementing a inner join.

5. Display all information by implementing a left outer join.

6. Display all information by implementing a right outer join.

7. Display all information by implementing a full outer join.

8. Display all information by implementing a inner join.

DEPARTMENT OF COMPUTER ENGINEERING & APPLICATIONS Page | 25

 Lab Manual
Database management system lab

Solution:

1. Select ename, sal from emp where sal>(select min(sal) from emp where
job like 'D%');

2. Select ename from emp where job=(select job from emp where
ename='Arjun');

3. Select * from emp where sal>(select max(sal) from emp where deptno=1);

4. Select stud1.RegNo, stud2.grade from stud1 left outer join stud2 on

stud1.name=stud2.name;

5. Select stud1.Rregno, Grade from stud1 right outer join stud2 on

stud1.name = stud2.name;

6. Select stud1.RegNo, stud2.grade from stud1 full outer join stud2 on

stud1.name=stud2.name;

7. Select stud1.RegNo, satud2.grade from stud1 inner join stud2 on

stud1.name=stud2.name;

8. Select * from stud1 left outer join stud2 on stud1.name=stud2.name;

9. Select * from stud1 right outer join stud2 on stud1.name=stud2.name;

10. Select * from stud1 full outer join stud2 on stud1.name=stud2.name;

11. Select * from stud1 inner join stud2 on stud1.name=stud2.name;

DEPARTMENT OF COMPUTER ENGINEERING & APPLICATIONS Page | 26

 Lab Manual
Database management system lab

Program 3.

Write the SQL queries using Group by and having clause using following table.

Student_marks

St_RollNo St_Name St_Subject St_Marks

1 Mohan Math 70
1 Mohan Physics 75
1 Mohan Chemistry 65
2 Vipul Physics 70
2 Vipul Chemistry 75
2 Vipul Math 60
3 Jitendra Physics 85
3 Jitendra Chemistry 75
3 Jitendra Math 60

Create the above tables with the given attributes and enter some records.

1. Write a query to find the total number of marks obtained by each student.

2. Write a query that gives the total marks obtained by Mohan.

3. Write a query that displays the maximum marks of every subject.

4. Write the SQL query which displays the name of those students whose total of all
subjects is greater than 205.

5. Display name of the students whose subjects total is greater than 205 and roll number is
between 1 to 2.

DEPARTMENT OF COMPUTER ENGINEERING & APPLICATIONS Page | 27

 Lab Manual
Database management system lab

Solution:
1. SELECT st_Name, SUM(st_Marks) AS Total Marks
FROM StudentMarks
GROUP BY st_Name;
Output:
St_Name Total Marks
jitendra 220
Mohan 210
Vipul 205

2. SELECT st_Name, SUM(st_Marks) AS Total Marks

FROM StudentMarks
where st_Name='Mohan'
GROUP BY st_Name;
Output:
St_Name Total Marks
Mohan 210

3. SELECT st_Subject,max(st_Marks) AS Max Marks in Subject

FROM StudentMarks
GROUP BY st_Subject;
Output:
Max Marks in
St_Subject
Subject
Chemistry 75
Math 70
Physics 85

4. SELECT st_Name, SUM(st_Marks) AS Students Scored greater 205

FROM StudentMarks
GROUP BY st_Name
HAVING SUM(st_Marks) > 205;
Output:
Students Scored
St_Name
> 205
Jitendra 220
Mohan 210

5. SELECT st_Name, SUM(st_Marks) AS Students Scored greater 205

FROM StudentMarks
where st_RollNo between 1 and 3
GROUP BY st_Name
HAVING SUM(st_Marks) > 205 ;
Output:
Students Scored
St_Name
> 205
Jitendra 220
Mohan 210

DEPARTMENT OF COMPUTER ENGINEERING & APPLICATIONS Page | 28

 Lab Manual
Database management system lab

Pre Experiment Questions

1. What is the use of sub Queries?
2. Explain different types of Join.

Post Experiment Questions

1. How to alter Primary Key?
2. How to update a table by enforcing constraint?

DEPARTMENT OF COMPUTER ENGINEERING & APPLICATIONS Page | 29