Practical No.
1
Aim: Draw E-R diagram and convert entities and relationships to relation
table for a given scenario.
a. Bank
Bank system
b. College
College system
PRACTICAL NO 3
Aim: Defining data
A ) Using CREATE statement
Q.1) Write a SQL statement to create a simple table of countries including columns
country_id, country_name and region_id.
Sol:
➔ create table countries(country_id INT ,country_name
VARCHAR(50),region_id INT);
Q.2) Write a SQL statement to create a table country1 set a constraint NOT
NULL.keep columns same as above table.
Sol:
➔ create table country_1(country_id INT NOT NULL ,
country_name VARCHAR(50) NOT NULL,
region_id INT NOT NULL);
Q.3) Write a SQL 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.
Sol:
➔ create table countries1(country_id INT,
country_name VARCHAR(30),
region_id INT,
check (country_name IN('india','italy','china')));
Q.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 EMPTY will be entered
automatically at the time of insertion if no value assigned for the specified columns.
Sol:
➔ create table job1(id int, job_title varchar(50) default ‘not
available', min_salary int check(min_salary>8000),
max_salary int default 00);
➔ insert into job1 values(01,default,10000,default);
B) Using ALTER statement
Q1] Add a column capital to table countries using ALTER.
Sol:
➔ alter table countries add capital varchar(50);
Q2]Modify column country_nm and set datatype to varchar(70).
➔ Alter table countries modify country_nm varchar(70);
Q3] Delete column capital from table country.
➔ Alter table countries drop capital;
Q4] Rename column country_id to c_id from table country.
➔ alter table countries rename column country_id to c_id;
Q5] Rename table countries to country.
➔ alter table countries rename to country;
C) DROP STATEMENT
Q1] Create a database ‘trail’ and using drop statement drop that database.
➔ drop database trail;
Q2] Removing table using DROP statement.
➔ create table student(roll_no int,name varchar(50),age int);
➔ drop table student;
D) TRUNCATE STATEMENT
Q1] Remove all data from table jobs.
➔ TRUNCATE TABLE jobs;
E) RENAME STATEMENT
Q1] Rename column country_id to c_id from table country.
➔ alter table countries rename column country_id to c_id;
Q2] Rename table countries to country.
➔ alter table countries rename to country;
PRACTICAL-4
AIM: Manipulating data
● Using INSERT statement
Q1) Insert any 5 records in table students
➔ create table student(roll_no INT,s_name varchar(50),
contact INT,
dob DATE,
city VARCHAR(50));
➔ INSERT INTO student values
(1,"Motu",1234,"2005-07-01","damapur"),
(2,"Patlu",123,"2005-09-21","udaypur"),
(3,"Dr jhatka",234,"2005-04-05","nigeria"),
(4,"singham",134,"2005-07-23","afganistan"),
(5,"Ghasitaram",124,"2005-06-12","daman");
➔ select * from student;
Q2)Insert any 5 records in table employee
➔ create table employee (e_id INT,e_name
VARCHAR(50),salary INT,dept_id INT);
➔ insert into employee values
(1,"Lungi Ngidi",200,101),
(2,"adam markram",2000,102),
(3,"kagiso rabada",500,103),
(4,"kushal mendis",800,104),
(5,"david warner",100,105);
➔ select * from employee;
● Using UPDATE statement
Q1) Update s_name of roll_no 1 to ‘Raj’.
➔ update students set s_name="Raj" where roll_no =1;
Q2) Update roll_no 3 set contact as 1234567 and address as ‘America’.
➔ update students set contact=1234567, city="America" where
roll_no =3;
● Using DELETE statement
Q1) Delete records of employee whose salary is less than 200.
➔ DELETE FROM employees WHERE salary<200;
● Using SELECT statement
Q1) Display all records of table students.
➔ select * from student;
Q2) Display records of student whose city is ‘Damapur’.
➔ select * from students where city="damapur";
Practical No 5
Aim: Creating and managing tables
Creating table with constraint: NOT NULL, UNIQUE, PRIMARY KEY,
FOREIGN KEY
Q1) Create table Customer with columns c_id, c_name, order_id, address,
Set c_id as a PRIMARY KEY NOT NULL, c_name and order_id as NOT
NULL.
Create table Orders with columns c_id, o_id, o_name, quantity. Assign c_id
as a FOREIGN KEY.
→
Output:
Output:
Practical No 6
Aim: Restricting and sorting data
a. Using DISTINCT, IN, AS, SORT, LIKE
Consider a table named "student", having the following records.
Q1) Display unique values for address from table student.
Output:
Q2) Using ‘IN’ keyword display records of students from ‘Delhi’ and ‘Kolkata’.
Output:
Q3) Using AS keyword display address of student.
Output:
Q4) Display student names from table student sort in Descending order.
Output:
Q5) Display names of students starting from ‘R’.
Output:
b) Using GROUP BY, HAVING, ORDER BY clause.
Consider a table named "officers" table, having the following records.
Q1) Write a query to count repetitive number of cities in the column address
using GROUP BY clause.
SELECT address, COUNT(*)
FROM officers
GROUP BY address;
Output:
Practical No 7
Aim: Aggregate and mathematical functions
Consider following table employee.
Q1) Display sum of salaries of all employees.
Output:
Q2) Display count of employees having salary greater than 15000.
Output:
Q3) Display average salary of all employees.
Output:
Q4) Display min salary and max salary from employee.
Practical No 8
Aim: Views and Joins
a) Creating a view & Selecting from view
Consider following table student2.
Create a view named ‘class’ with columns roll_no,s_name,Class.
Display all records from view ‘class’
SELECT * FROM class;
Output:
b) Dropping a view
Delete view named ‘class’
DROP VIEW class;
Practical No 9
Aim: Database Trigger
Using CREATE OR REPLACE trigger:
Consider table main with following data:
create a trigger t1 which will insert data automatically into backup table when
data from main table is deleted.
CREATE TRIGGER t1
before DELETE ON main
for each row
Begin
insert into backup values(old.id, old.salary); end
Now if we run following query:
delete from main where id=1;
Deleted data is automatically inserted in backup table:
select * from backup;
Practical No 10
CREATE and DROP INDEX
Consider we have table student2 with following data:
Create an index as ‘stud_roll’ assign roll_no, s_name, address as index
columns.
CREATE INDEX stud_roll
ON Persons (roll_no, s_name, address);
Drop INDEX stud_roll:
mysql> DROP INDEX stud_roll ON student2;
