Aarti Kakde Dbms - Final - Journal

Name:- Aarti kakde
Roll no.:-. 218048

Subject:- Database Management System
Practical No 1
Create a table Without Constraints and insert values in the table
1 Write a MySQL statement to view list of existing databases.
2 Write a MySQL statement to create a new

database (FYCS_name).
3 Write a MySQL statement to use your database.
4 Write a MySQL statement to create a simple table
countries including columns country_id,country_name and
region_id.
5 Write a MySQL statement to display structure of table

countries.
6 Write a MySQL statement to Create a table

‘Product Including columns p_id, pname,price.
7 Write a MySQL statement to display structure of table Product.

8 Write a MySQL statement to insert 5 records in table product
.Also write a query to display all the records of table product.
9 Write a MySQL statement to Create a table ‘Music’ also

insert the given records in table.
m_id instru_name fees
M001 guitar 500
M002 tabla 800
M003 veena 1000
M004 harmonium 300
AARTI KAKDE
ROLL NO
218048 DBMS
Practical No 2
Create a table with Constraints and insert values in the table
1 Write a MySQL statement to create a table countries set
a constraint NOT NULL.
2) Write a MySQL statement to create a table named jobs including

columns job_id, job_title, min_salary, max_salary set a constraint NOT
NULL and check whether the max_salary amount exceeding the upper limit
25000.
3) Write a MySQL statement to create a table named countries including
columns country_id, country_name and region_id and make sure that no countries
except Italy, India and China will be entered in the table.
4) Write a SQL statement to create a table named jobs including columns job_id,
job_title, min_salary and max_salary, and make sure that, the default value for
job_title is blank and min_salary is 8000 and max_salary is NULL will be entered
automatically at the time of insertion if no value assigned for the specified columns
5) Write a SQL statement to create a table employees including
columns employee_id, first_name, last_name, email, phone_number
hire_date, job_id, salary, commission, manager_id and department_id
Assume the structure of departments table below.
EMPLOYEE_ID , NOT NULL, PRIMARY
KEY, FIRST_NAME , NULL,
LAST_NAME , NOT NULL,
EMAIL , NOT NULL,
PHONE_NUMBER , DEFAULT
NULL, HIRE_DATE , NOT NULL,
JOB_ID , NOT NULL,
SALARY , DEFAULT
NULL,
COMMISSION_PCT , DEFAULT
NULL, MANAGER_ID , DEFAULT
NULL, DEPARTMENT_ID , DEFAULT
NULL.
Practical No 3
AIM:ALTER STATEMENTS
Q1)Create table Dept with following specifications Employee ID primary key, employee
name,departmen id, city , manager id, Salary , date of joining
Insert 10 records
.
Q2)WRITE A QUERY TO ADD A COLUMN BONUS AFTER SALARY
IN TABLE DEPARTMENT
Q3)WRITE A QUERY TO CHANGE THE SIZE OF COLUMN CITY TO 25 IN TABLE

DEPARTMENT
Q4)WRITE A QUERY TO CHANGE NAME OF A COLUMN BONUS TO E-BONUS IN TABLE
DEPARTMENT
Q5)WRITE A QUERY TO ADD COLUMN K_BONUS AS A FIRST COLUMN IN TABLE

DEPARTMENT
Q6)WRITE A QUERY TO DROP A COLUMN K_BONUS AND E-BONUS IN TABLE DEPARTMENT
Q7)WRITE A QUERY TO REMOVE PRIMARY KEY FROM TABLE DEPARTMENT

Q8)WRITE A QUERY TO ADD A CONSTRAINT PRIMARY KEY
ON THE COLUMN EMPLOYEE_ID FROM TABLE DEPARTMENT
Q9)WRITE A QUERY TO CHANGE NAME OF TABLE DEPARTMENT TO

E_DEPARTMENT
Q10)WRITE A QUERY TO DELETE A TABLE COUNTRIES FROM YOUR DATABASE
Q 11): Write a MySQL statements to create a simple table Bike with following constraints:
a)Bik_id: primary key
b)Bik_name: NOT NULL, DEFAULT
c) cost: NOT NULL, DEFAULT
d)DOP: Date
Q12: Write a Query to change name of column bik_name from bike table.
Q13) Write a Query to change size of bik_cost from bike table.
Q14: Write a Query to add servicing column as first column from bike table
.
PRACTICAL NO. 4
• Updating table / Update statements.
Q1) ) Write a Query to change city of employee to pune of emp_id 005

from department table.
Q2) ) Write a Query to increase salary of employee by 5000 wit emp_id

2,4,8 from department table.
Q3) ) Write a Query to increase salary of employee by 10% for city either
Thane or Mulund from department table.
Q4) ) Write a Query to display all records of employee whose date of joining
is between 2016-2020 from department table.
Q5) Write a MySQL statement to create a simple table Bike with

following constraints :
a) Bik_id : primary key
b) Bik_name : NOT NULL , DEFAULT

c) Bik_cost : NOT NULL , DEFAULT
d) DOP : Date
Q6) Write a Query to change bik_cost for bik_id 003 from bike table.
Q7) Write a Query to reduce bikcost by 10000 from bike table.
PRACTICAL NO. 5
• Aggregate function and Group by and Having
condition.
Q1) Write a Query to display sum of salary for all

employees from department table.
Q2) Write a Query to display minimum and maximum salary

paid to all employees from department table.
Q3) Write a Query to display average salary from department table.
Q4) Write a Query to display number of employees from

each department.
Q5) Write a Query to display sum and average salary paid

to employees from each city.
Q6) Write a Query to display sum and average , minimum

and maximum paid to employees from city “MULUND”
DATABASE MANAGEMENT PRACTICAL 6
1) Demonstrate all string functions in MySQL.

 UCASE()
 LCASE: -
 LEFT(str,len) :-
 RIGHT(str,len) :-
 MID(str,start,len) :-
 CONCAT() :-
 LTRIM() :-
 RTRIM() :-
 TRIM() :-
 STRCMP() :-
 REPLACE() :-
1) Demonstrate al mathematical functions in MySQL.
 ABS() :-
 CEIL() / CEILING() :-
 FLOOR :-
 MOD() :-
 POW (X, Y) OR POWER(X,Y) :-
 SQRT() :-
 ROUND() :-
 TRUNCATE() :-
2) Demonstrate all date functions in MySQL.
 ADDDATE() :-
 DATEDIFF() :-
 DAY() :-
 MONTH() :-
 YEAR() :-
 HOUR() :-
 SECOND() :-
 CURDATE() :-
 NOW() :-
3) Write a query to:
a)Display emp_id and emp_name in uppercase
b)Display emp_id and city_name in reverse order
c)Display emp_id, emp_name and length of each

emp_name
a) Display 3rd, 4th, 5th , 6th character from emp_name of all
employees
DBMS PARCTICAL
NO.7
 JOINING OF TABLES.
Q1) Demonstrate inner join using department table and
department information.
Q2) Demonstrate left outer join using department table and
department information.
Q3) Demonstrate right outer join using department table

and department information.
PARACTICAL NO .8
 VIEW
Q1) Write a Query to create a view, view1 on table
department containing emp-id, dept_id,salary.
Q2) Write a Query to update department rename to
“ROHAN”.will view of table is updated by changes in
department table.
Q3)write a Query to update view, view1 as increase salary
of emp_id (1,4,6) by 5%. Will base table department is also
updated by changes in view.
Q4) Write a Query to create a view, view2 on department

table to display emp_id, emp_name.
Q5) write a Query to alter view,
view2 to display emp_name, emp_id, mgr_id,
city_name.
PARCTICAL NO.9
 SUBQUERY.
Q1) Write a Query to display emp_name,salary of employee whose
salary is greater than average salary.
Q2) Write a Query to display emp_id,emp_name, date of joining,

dept_id for all employees whose department is having an
employee whose name starts with “R”.
Q3) Write a Query to display emp_id, emp_name, city_name for
employees whose department is not having a person from
“THANE”.
Q4) Write a Query to display dept_id, avg salary and total salary
from department if avg salary of particular department is greater
than average salary of all employees.
Q5) Write a Query to display dept_id and emp_name,
of all the employees who works in HR department from
table dept_info, dept_info(dept_id,dept_name).

Aarti Kakde Dbms - Final - Journal

Uploaded by

Copyright:

Available Formats

Aarti Kakde Dbms - Final - Journal

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Aarti Kakde Dbms - Final - Journal

Uploaded by

Copyright:

Available Formats

Name:- Aarti kakde

Roll no.:-. 218048

2 Write a MySQL statement to create a new

5 Write a MySQL statement to display structure of table

6 Write a MySQL statement to Create a table

7 Write a MySQL statement to display structure of table Product.

9 Write a MySQL statement to Create a table ‘Music’ also

2) Write a MySQL statement to create a table named jobs including

Q3)WRITE A QUERY TO CHANGE THE SIZE OF COLUMN CITY TO 25 IN TABLE

Q5)WRITE A QUERY TO ADD COLUMN K_BONUS AS A FIRST COLUMN IN TABLE

Q7)WRITE A QUERY TO REMOVE PRIMARY KEY FROM TABLE DEPARTMENT

Q9)WRITE A QUERY TO CHANGE NAME OF TABLE DEPARTMENT TO

Q10)WRITE A QUERY TO DELETE A TABLE COUNTRIES FROM YOUR DATABASE

• Updating table / Update statements.

Q1) ) Write a Query to change city of employee to pune of emp_id 005

Q2) ) Write a Query to increase salary of employee by 5000 wit emp_id

Q5) Write a MySQL statement to create a simple table Bike with

b) Bik_name : NOT NULL , DEFAULT

Q1) Write a Query to display sum of salary for all

Q2) Write a Query to display minimum and maximum salary

Q4) Write a Query to display number of employees from

Q5) Write a Query to display sum and average salary paid

Q6) Write a Query to display sum and average , minimum

1) Demonstrate all string functions in MySQL.

c)Display emp_id, emp_name and length of each

Q3) Demonstrate right outer join using department table

Q4) Write a Query to create a view, view2 on department

Q2) Write a Query to display emp_id,emp_name, date of joining,

You might also like