FAIRFIELD INSTITUTE OF MANAGEMENT AND TECHNOLOGY
DATABASE MANAGEMENT SYSTEM LAB
PACTICAL FILE
SUBJECT CODE- BCA(176)
SUBMITTED TO:- SUBMITTED BY:-
MS. JYOTI CHANDAN KUMAR
ASST. PROFESSOR JHA
IT DEPARTMENT
FIMT
1. Give description about E-R diagram with all terminology in pictorial form.
2. Write queries to execute following DDL commands :
CREATE
ALTER.
DROP
3. Write queries to execute following DML commands :
INSERT: Insert five records in each table.
UPDATE
DELETE
4. Write the queries to execute DCL commands.
5. Create table using following integrity constraints:
Primary Key
Unique Key
Not Null
Foreign Key
6. Write queries to execute following Aggregate functions
Sum, Avg, Count, Minimum and Maximum
7. Write the queries to execute following clauses:
Group By
Having
8. Differentiate IS NULL and IS NOT NULL by applying onto table.
9. Apply following logical operators onto the table:
BETWEEN, IN, AND, OR and NOT
10. Take two tables and show following results:
Intersection
Union
Set Difference
11. Apply Selection and Projection relational operation on table and generate output.
12. Retrieve data from more than one table using inner join, left outer, right outer and
full outer Joins.
13. Create index and View on a table in DBMS.
1: Give description about E-R diagram with all terminology in pictorial form.
+------------------+ +--------------+
| Student | | Course |
+------------------+ +--------------+
| Student ID (PK) | | Course ID |
| Name | | Title |
| Age | | Credits |
+------------------+ +--------------+
| |
| |
| +---------------------+ |
+---| Enrollment |---+
+---------------------+
| Enroll ID (PK) |
| Student ID (FK) |
| Course ID (FK) |
+---------------------+
2. Write queries to execute following DDL commands :
CREATE
ALTER.
DROP
Create:
CREATE TABLE company.employees (
id INT,
name VARCHAR(50),
age INT
);
Alter:
ALTER TABLE company.employees
ADD salary DECIMAL(10,2);
Drop:
DROP TABLE company.employees;
3: Write queries to execute following DML commands : • INSERT: Insert five records
in each table. • UPDATE • DELETE
INSERT:
INSERT INTO employees (id, name, age)
VALUES (1, 'John Doe', 30),
(2, 'Jane Smith', 28),
(3, 'Michael Johnson', 35),
(4, 'Emily Davis', 32),
(5, 'Robert Brown', 29);
UPDATE:
UPDATE employees
SET age = 31
WHERE id = 1;
DELETE:
DELETE FROM employees
WHERE id = 3;
4: Write the queries to execute DCL commands
GRANT:
GRANT SELECT, INSERT ON employees TO user1;
REVOKE:
REVOKE SELECT ON employees FROM user1;
5 : Create table using following integrity constraints: • Primary Key • Unique Key •
Not Null • Foreign Key
PRIMARY KEY:
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
age INT
);
UNIQUE KEY:
CREATE TABLE students (
student_id INT UNIQUE,
name VARCHAR(50),
age INT
);
NOT NULL KEY:
CREATE TABLE products (
id INT,
name VARCHAR(50) NOT NULL,
price DECIMAL(10,2)
);
FOREIGN KEY:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(50)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATE,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
6 : Write queries to execute following Aggregate functions • Sum, Avg, Count,
Minimum and Maximum
SUM:
SELECT SUM(quantity) AS total_quantity
FROM sales;
AVG:
SELECT AVG(price) AS average_price
FROM products;
COUNT:
SELECT COUNT(*) AS total_customers
FROM customers;
MINIMUM:
SELECT MIN(age) AS min_age
FROM employees;
MAXIMUM:
SELECT MAX(salary) AS max_salary
FROM employees;
7 : Write the queries to execute following clauses: • Group By • Having
GROUP BY:
SELECT category, SUM(quantity) AS total_quantity
FROM sales
GROUP BY category;
HAVING:
SELECT category, SUM(quantity) AS total_quantity
FROM sales
GROUP BY category
HAVING SUM(quantity) > 100;
8 : Differentiate IS NULL and IS NOT NULL by applying onto table.
name Salary
John 3000
Mary null
adam 5000
IS NULL:
SELECT *
FROM employees
WHERE salary IS NULL;
IS NOT NULL:
SELECT *
FROM employees
WHERE salary IS NOT NULL;
9 : Apply following logical operators onto the table: • BETWEEN, IN, AND, OR and
NOT
id name price
1 Laptop 1000
2 Phone 500
3 Tablet 800
4 Tv 1500
5 headphone 100
BETWEEN:
SELECT *
FROM products
WHERE price BETWEEN 500 AND 1000;
IN:
SELECT *
FROM products
WHERE name IN ('Laptop', 'Phone');
AND:
SELECT *
FROM products
WHERE price > 800 AND name = 'Tablet';
OR:
SELECT *
FROM products
WHERE price < 200 OR name = 'TV';
NOT:
SELECT *
FROM products
WHERE price <> 100;
10 : Take two tables and show following results .Intersection • Union • Set
Difference
Id name
1 John
2 Mary
3 Adam
4 Emily
id name
3 Adam
4 Emily
5 Sarah
6 Michael
INTERSECTION:
SELECT *
FROM table1
INTERSECT
SELECT *
FROM table2;
id name
3 Adam
4 Emily
UNION:
SELECT *
FROM table1
UNION
SELECT *
FROM table2;
id name
1 John
2 Mary
3 Adam
4 Emily
5 Sarah
6 Michael
SET DIFFERENCE:
SELECT *
FROM table1
EXCEPT
SELECT *
FROM table2;
id name
1 John
2 Mary
11 : Apply Selection and Projection relational operation on table and generate
output.
Employee First name Last department salary
id name
1 john smith engineering 5000
2 Mary Johnson hr 4000
3 David miller engineering 6000
4 Sarah Davis sales 4500
SELECTION:
SELECT *
FROM employees
WHERE department = 'Engineering';
Employee id Fist name Last name department salary
1 john smith engineering 5000
3 David miller engineering 6000
PROJECTION:
SELECT first_name, salary
FROM employees;
First name salary
John 5000
Mary 4000
David 6000
Sarah 4500
12 : Retrieve data from more than one table using inner join, left outer, right outer
and full outer Joins.
id name city
1 John smith New York
2 Mary Johnson London
3 David miller Sydney
Order id customer total
1001 1 500
1002 2 700
1003 1 300
1004 3 900
INNER JOIN:
SELECT customers.Name, orders.OrderID, orders.Total
FROM customers
INNER JOIN orders ON customers.ID = orders.Customer;
name Oder id total
John smith 1001 500
Mary Johnson 1002 700
John smith 1003 300
David miller 1004 900
LEFT OUTER JOIN:
SELECT customers.Name, orders.OrderID, orders.Total
FROM customers
LEFT JOIN orders ON customers.ID = orders.Customer;
name Oder id total
John smith 1001 500
Mary Johnson 1002 700
John smith 1003 300
David miller 1004 900
RIGHT OUTER JOIN:
SELECT customers.Name, orders.OrderID, orders.Total
FROM customers
RIGHT JOIN orders ON customers.ID = orders.Customer;
Name
13 :Create index and View on a table in DBMS.
CREATING AN INDX:
CREATE INDEX index_name
ON table_name (column_name);
CREATE INDEX idx_customers_city
ON customers (City);
CREATING A VIEW:
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
CREATE VIEW view_customers AS
SELECT ID, Name, City
FROM customers;
