FYBCOM

Database Management Systems

Practical number Topics

1 Creating a simple database, table and displaying structure

2 Creating Table/ Inserting Data

3 Modifying Tables

4 Altering data/Updating data

5 Built in Functions

6 Simple queries using select statement

7 Query with complex condition
8 Functions and Aggregating data using Group Function

9 Multi Table Queries

10 Sub Queries

11 Transaction
 Practical 1
(Creating a simple database, table and displaying structure)
Q.1.Create a database of your div and rollno. E.g. B070

Q.2 Create a database university.

Q.3 Create a database student.

Q. 4 Delete database student

Q.5 Display all the databases.

Q.6 Select the database B70.

Q. 7 In the current database, create a table named OFFICE containing information of the
employee with the following columns, Employee number (EN, integer, Primary key), Employee
Name ( EName, Character variable width of 20 columns, should not be empty), Gender (Gender,
character width 1 column, with default value as “M”) and date of Joining (DOJ, date).

Q. 8 Display the structure of the table office.

 Practical 2
(Creating Table/ Inserting Data)
Q. 1 Create table employee with the following details:

Information Column Data type

name
Employee number emp_no smallint unsigned
First name f_name varchar(15)

Last name l_name varchar(15)

Job code j_code smallint unsigned

Q. 2 Display the structure of the table employee.

Q.3 Create a table CAR with the following description:

Car_make Varchar(10) not null

Regn_no Varchar(15) primary key

Cost Integer 7, decimal 2

Dop Date

Q.4 Display the structure of the table car.

Q.5 Create a table called MY_EMPLOYEE containing the columns employee id which should be
incremented by 1 automatically and will be the primary key, last name and first name character, having
variable width of 20 columns, salary 7 integers and 2 decimal.

Q.6 Display the structure of the above table.

Q.7 Add a row to the table without naming the columns. Use your own data, but give employee id as 1.

Q.8 Add another row to the table using the column names. Do not enter data for employee id.

Q.9 Add 3 rows of data together to the table.

Q.10 Display all the records in the table

 Practical 3
(Modifying Tables)
Q 1. Create a table PAY in the current database with the following structure and display the table:

Column name Data type and attributes

Empno Integer
Name Character

Age Integer

City Character

Dob Date
Salary 6 integers, 2 decimal

i) Add a column gender of character type size 1 to this table.

ii) Add column bonus decimal (7,2) to this table after salary column with default value 2000.
iii) Change the size of column name to varchar(25)
iv) Change the name of column bonus to mbonus
v) Delete the column mbonus
vi) Display table pay.
vii) Rename the table pay to epay
viii) Create table newpay using epay(with fields empno, name, city, salary) and display the table.
ix) Delete the table newpay

Q.2 Create a table MARKS containing roll number (RNO, small integer, without sign ), Name of the
student (SNAME, Varchar(11)), Date of Birth ( birth_dt, date) and marks in three subjects (M1, M2,
M3; small integers with size 3 but without minus sign). RNO should be primary key and should increase
automatically.

i) Display the structure of this table.

ii) Change the type of name column to VARCHAR(10).
iii) Change the name of column BIRTH_DT to DOB.
iv) Add a new column AGE with default value 18.
v) Add a new column m4 with integer size 3 without negative sign.
vi) Delete column m4.
vii) Change the name of the table marks to Result.
 Practical 4
( Inserting/ Altering data)
Q. .1 Create a table PAYROLL of the following structure
(Empid smallint primary key, Name varchar(20), Age smallint default 20, City varchar(20)
Salary 7 integers 2 decimal, Doj date)
i) Enter 5 records
ii) Display all the records
iii) Change the age of employee id 104 to 30.
iv) Change the city as ‘Nashik’ and doj as ‘2015-02-20’ for empid 103.
v) Increase the salary of employees by 1000 where age is more than 30.
vi) Increase salary of each employee by 1000.
vii) Decrease salary by 500 if above 15000.
viii) For Mayur , change date of birth to 4th October 87.
ix) For employee number 102 change name to null.

Q.2 Create table Student in the current database with the fields
Rollno, id primary key, fname, lname, marks in 3 subjects.
i) Enter 5 records.
ii) Display all the records.
iii) Change the lname of student id 4524 as ‘Mehta’
iv) Change the marks M1 of student Tina as 77.
v) Delete the row of student id 4526.
 Practical 5
( Built in Functions)
Q.1 Use functions to:

i) Display the system date and time.

ii) Display current date
iii) Display current month.
iv) Find the square root of number 34.564, 6445.67
v) Find the remainder of 10 divided by 3
vi) Find the absolute value of -5
vii) Find 3 raised to the power 4, 7 raised to the power 3.
viii) Display the value of 536.35214 rounded to 2 decimal places, 1 decimal place, nearest integer.
ix) Display the above number rounded to nearest 10, nearest 100 and 1000 place.
Q. 2 Create a table MARKS (Same as done in Practical 3)
containing roll number (RNO, small integer, without sign ), Name of the student (SNAME, Varchar(11)),
Date of Birth ( birth_dt, date) and marks in three subjects (M1, M2, M3; small integers with size 3 but
without minus sign). RNO should be primary key and should increase automatically.
Enter 5 records and preform the following queries
i) Display name in uppercase, name and name in lower case from the MARKS table.
ii) Display name, first 2 characters of name, last 3 characters of name and 2 characters
starting from 3rd character of name.
iii) Display name, reverse name and name of characters in name from MARKS
iv) Display name, weekday name and month name for each row from MARKS.
v) Display a message <name> has scored <m1> in subject 1 for each row.
vi) Display name and date of birth as ‘Saturday, September 1, 2023’.
vii) Display name and date of birth in format dd/mm/yy i.e 1/9/23.
viii) Display name and date of birth in dd-mmm-yy ie 1-Sep-23
 Practical 6
(Simple queries using select statement)
Q.1 Create a table MARKS (Same as done in Practical 3)
containing roll number (RNO, small integer, without sign ), Name of the student (SNAME, Varchar(11)),
Date of Birth ( birth_dt, date) and marks in three subjects (M1, M2, M3; small integers with size 3 but
without minus sign). RNO should be primary key and should increase automatically.
Enter 5 records and preform the following queries
1) Display all rows and all columns from MARKS table.
2) Display only names and marks in second subject from MARKS table.
3) Display marks in 3rd subject with heading as ‘Marks 3’ and name form MARKS table.
4) Display all columns, total of marks and average of marks from MARKS table.
5) Display name of the student and percentage of m1 on total marks from MARKS table.
6) Display only first 3 rows from MARKS table.
7) Display 3 rows starting from 3rd records.
8) Display only 3rd record.
9) Display 5 records starting from 4th record.
10) Display records from MARKS table if M1 is equal to 75.
11) Display name and roll number from MARKS table if M3 is above 30.
12) Display records where marks 2 is 60 or less.
13) Display rows if M1 not equal to 75.
14) Display roll number and name if marks 1 is more than marks 3
15) Display records where total marks are above 200.
16) Display records where average marks is below 70.
17) Display records from MARKS table if m3 are between 70 and 75.
18) Display name and marks 2 from MARKS table if marks 2 is 85 or 75 or 65.
19) Display records with birth date between 10th Dec 87 and 15th Aug 88.
Q.2 Create a table COMPANY with the following description:

Information Column name data type

Employee number emp_id smallint

First name fname varchar(10)

Last name lname varchar(10)

Email email varchar(20)

Job id jobid smallint

Salary salary 6 integer, 2 decimal

Date of joining doj date

i) Enter 5 records.

ii) Display all the rows.

iii) Display fname, salary of all employees earning more than 50000.

iv) Display lname, jobid for jobid = 102

v) Display fname, salary whose salary is in range 30000 to 60000.

vi) Display fname, lname, salary in the ascending order of lname.

vii) Display fname, lname, salary in the descending order of salary.

viii) Display fname in upper case, lname in lower case from the table.

ix) Display message <fname>has got salary < salary>

ix) Display fname as First Name, lname as Last Name and doj as Date of Joining.

x) Display fname and lname as “Employee Name”, salary from table.

 Practical 7
(Query with complex condition)
Q.1 Create a table MARKS (Same as done in Practical 3)
containing roll number (RNO, small integer, without sign), Name of the student (SNAME, Varchar (11)),
Date of Birth ( birth_dt, date) and marks in three subjects (M1, M2, M3; small integers with size 3 but
without minus sign). RNO should be primary key and should increase automatically.
Enter 5 records and preform the following queries
1) Display records from MARKS table where name is starting with A.
2) Display names from MARKS table where names are ending with “y”.
3) Display records which have name not starting with a.
4) Display name and marks in 3 subjects where 2nd character of name is a.
5) Display name and birth date where second last character of name is a.
6) Display records where name has exactly 4 characters.
7) Display records where name contains a.
8) Display records where names are between j and p.
9) Display records where marks of each subject are 65 or more.
10) Display records where marks in any subject are less than 60.
11) Display rows where marks in each subject are 60 or above and average of marks is above 70.
12) Display name and marks 1 if marks 1 is above 70 but below 80.
13) Display records if name is starting with A or V
14) Display records where m2 is in the range of 60 to 72, both inclusive
15) Display records where m2 is in the range of 60 to 72, both exclusive.
16) Display records where marks 1 is either 71 or 75 or 76.
17) Display names of roll number is not equal to 3.
18) Display records if name is starting with A or V and m1 is above 72.
19) Display records where marks 1 is not between 70 and 80.
20) Make marks 1 as null for roll number 2 from MARKS table.
21) Add a row with just name as “amita”.
22) Display records from marks table where marks 1 is null.
23) Display records from MARKS table where marks 1 is not null.
 Practical 8
(Functions and Aggregating data using Group Function)

Q.1 Create table Branch in ZEE database with following structure.

Column Column Name Type

Branch Code Br_code Small Int Unsigned Primary key

Branch Location Location Varchar (20)

Branch zone Zone Varchar (20)

Branch Head Br_head Int(3)

Branch Target Target Decimal(10,2)

Branch Actual Actual Decimal(10,2)

1) Enter following records

br_code location zone br_head target Actual

18 Palghar Western 113 25000.00 NULL

20 Andheri Western 112 330000.00 330000.00

22 Bhiwandi Central 110 200000.00 170000.00

25 Kalyan Central 113 350000.00 170000.00

34 Kanjurmarg Central 115 320000.00 280000.00

2) Display records from branch table if location is after ‘I’.

3) Display records from branch table in the alphabetical order of location.
4) Display records from branch table in the descending order of actual.
5) Display location and difference between target and actual with a lable ‘diff’ from branch
table in the ascending order of difference.
 6) Display records form branch table in the alphabetical order of zone and within zone
alphabetical order of location.
7) Display unique values for zone from branch table.
8) Display total number of records, total number of location, target, actual from branch table.
9) Display lowest from actual column and highest from target and average of actual from branch
table.

Practical 9 3-11-2023
(Aggregating data using Group Function)
Q.1 Create table “emp1” in a new database.

Column Column name Type

Employee Number e_no Smallint & Unsigned

Employee name Name Varchar (20)

Age of employee Age Smallint & Unsigned

Branch Rep_br Smallint, unsigned,

Designation Desg Varchar(10)

Joining date Join_dt Date

Gender Gender Varchar(1)

 1) Enter following 5 records.

e_no Name Age Rep_br Desg Join_dt Gend

101 Sancia ’sa 23 34 Actn 2004-02-24 F

102 Aurora Vaz 24 25 Officer 2007-09-23 F

103 Jesse Lopez 21 30 Manager 2005-04-21 F

104 Parth Parmar 23 29 Agent 2006-03-18 M

105 Tania Gomes 22 33 Actn 2008-01-20 F

2) Display rows in the alphabetical order of names where designation starts with A.
Select * from emp1
Where desg like ‘a%’
Order by name;

3) Display rows in Chronological order of “Joining date” where name ends with Z
4) Display rows in the descending order of age and within age in the descending order of name.
Select * from emp1
Order by age desc, name desc;

5) Display number of employees working for each designation.

Select desg,count(desg)
From emp1
Group by desg;

6) Display number of employees in each gender.

Select gender, count(gender)
From emp1
Group by ender;

7) Display min age, max age and average age gender wise.
8) Display designation wise number of employees , where number of employees designation
wise more than 2.
Select desg, count(desg)
From emp1
Group by desg
Having count(desg) > 2;
 Practical 10 07-11-2023
(Multi table Queries)

Q.1 = Create a table MARKS (Same as done in Practical 3)

containing roll number (RNO, small integer, without sign ), Name of the student (SNAME, Varchar(11)),
Date of Birth ( birth_dt, date) and marks in three subjects (M1, M2, M3; small integers with size 3 but
without minus sign). RNO should be primary key and should increase automatically.
Q.2= Create table ADMN

Column Column Name Type

Roll Number RNO Smallint unsigned

Short address ADDR varchar(10)

Phone Number PHNO Int unsigned

1) Insert following values in table ADMN

rno addr phno

4 Panvel 29994872

2 Ulhasnagar 25267624

3 Naigaon 28435756

1 Kalyan 28345012

5 Andheri 27399102
2)Display all columns from both the tables.

Select marks.rno, sname, birth_dt, m1,m2,m3, addr, phno

From marks, admn
Where marks.rno = admn.rno;
3)Display student name, address and phone number for all the rows.
Select sname,addr,phno
From marks,admn
Where marks.rno = admn.rno;
Or
Select sname,addr,phno
From marks a, admn b
Where a.rno = b.rno;

4)Display name, address and total marks with header TOTAL if total of marks are above 200

5)Display roll number, name and address if address is Andheri

6) Display”<name> residing at <address> has scored <marks-1> in subject 1” .

7) Display date of birth, marks in three subjects , address for each student using inner join.
8) Display “<name> has scored <total marks> in exams who lives in <address>” using simple join.

Practical 11
(SUB QUERIES)

GO IN ZEE DATABASE
Q.1= Create table Branch with following structure.
Column Column Name Type

Branch Code Br_code Small Int Unsigned Primary key

Branch Location Location Varchar (20)

 Branch zone Zone Varchar (20)

Branch Head Br_head Int(3)

Branch Target Target Decimal(10,2)

Branch Actual Actual Decimal(10,2)

1) Enter following records

br_code location zone br_head target Actual

34 Palghar Western 101 250000.00 295000.00

25 Andheri Western 102 330000.00 330000.00

30 Bhiwandi Central 103 200000.00 170000.00

29 Kalyan Central 104 350000.00 170000.00

33 Kanjurmarg Central 105 320000.00 280000.00

1) Display location, zone and branch code of the branch where target is highest.
2) DISPLAY RECORDS WHERE TARGET IS ABOVE THE AVERAGE TARGET.
3) Display location, target where target is less than average target.
Q4 Display average target of zone ‘Central’.
Q.5 Display maximum, minimum target zone wise.
Q.6 Display all the records from table student. Use Transaction(start –Rollback)
And delete some rows from the table and undo the changes.

Q.2= Create table empl ( same as Practical 8) & enter 5 records (same as Practical 8).
1) Display employee number, name, desiganation who working in same zone.
2) Display name, gender of employees who are working in western zone.
3) Dispay name and location where first character of location is same as last character of any
name.

Q.3 = Create table CUSTOMER with following structure.

 Column Column name Type

Customer Identification CUST_ID INT(4) UNSIGNED NOT NULL

PRIMARY KEY

Customer name NAME VARCHAR(20)

Branch code BR_CD SMALLINT

Amt.Collected by COLL_BY INT(3)

1) INSERT following values

cust_id name br_cd coll_by

1055 Aurora Vaz 25 105

1074 Sancia Dsa 34 104

1095 Parth Parmar 29 103

1104 Jesse Lopez 30 101

1111 Tania Gomes 33 102

Q.4= Create table DEPOSITED with following structure.

Column Column Name Type

Account Number ANO Int(5) unsigned not null

primary key

Amount deposited AMT Decimal(9,0)

Customer id CUST_ID Int(4) unsigned

1) insert following values
ANO AMT CUST_ID

12001 100000 1055

12002 80000 1074

12003 60000 1055

12004 45000 1104

12005 90000 1111

2) Display total amount collected by each branch

3) Display customer id and customer name who have deposited more than one time.
4) Display customer id and name who have deposited between 60000 and 90000.
5) Display name of the employee and age , if age of an employee is above the average age.
6) Display name of customer, total amount deposited , if total amount deposited is less than
average deposit
7) Create nested sub query , list customer name, whose deposit is collected by employee
working in BHIWANDI location.