DBMS Lab

Prepared by Mohibul Khan kaziranga university

Aggregate function Date : 20/08/2024

It performs a calculation on multiple values and returns a single value.

It is used with GROUP BY and SELECT statement,

1. COUNT(): rerturnds number of values Eg:

SELECT count (amount) from payments.
2. SUM(): returns sum of all values
SELECT sum(amount ) from payment
3. AVG(): returns average values
SELECT avg(amount) from payment
4. MAX(): rerturns maximum values
SELECT max(amount) from payment
5. MIN(): SELECT min(amount) from payment

This Code Only valid for MySQL database

Question:

1. Creating the dept Table:

CREATE TABLE dept (

deptno INT(2),
dname VARCHAR(14),
loc VARCHAR(13),
CONSTRAINT pk_dept PRIMARY KEY (deptno)
);

2. Creating the emp Table:

CREATE TABLE emp (

empno INT(4),
ename VARCHAR(10),
job VARCHAR(9),
mgr INT(4),
hiredate DATE,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT(2),
CONSTRAINT pk_emp PRIMARY KEY (empno),
CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept (deptno)
);

3. Creating the bonus Table (Commented Out):

CREATE TABLE bonus (

ename VARCHAR(10),
job VARCHAR(9),
sal DECIMAL(10,2),
comm DECIMAL(10,2)
);
 DBMS Lab
Prepared by Mohibul Khan kaziranga university
4. Creating the salgrade Table (Commented Out):

CREATE TABLE salgrade (

grade INT,
losal DECIMAL(10,2),
hisal DECIMAL(10,2)
);

5. Inserting Data into the dept Table:

INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');

INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON');

6. Inserting Data into the emp Table:

INSERT INTO emp VALUES (

7839, 'KING', 'PRESIDENT', NULL,
'1981-11-17', 5000, NULL, 10
);
INSERT INTO emp VALUES (
7698, 'BLAKE', 'MANAGER', 7839,
'1981-05-01', 2850, NULL, 30
);
INSERT INTO emp VALUES (
7782, 'CLARK', 'MANAGER', 7839,
'1981-06-09', 2450, NULL, 10
);
INSERT INTO emp VALUES (
7566, 'JONES', 'MANAGER', 7839,
'1981-04-02', 2975, NULL, 20
);
INSERT INTO emp VALUES (
7788, 'SCOTT', 'ANALYST', 7566,
'1987-04-19', 3000, NULL, 20
);
INSERT INTO emp VALUES (
7902, 'FORD', 'ANALYST', 7566,
'1981-12-03', 3000, NULL, 20
);
INSERT INTO emp VALUES (
7369, 'SMITH', 'CLERK', 7902,
'1980-12-17', 800, NULL, 20
);
INSERT INTO emp VALUES (
7499, 'ALLEN', 'SALESMAN', 7698,
'1981-02-20', 1600, 300, 30
);
INSERT INTO emp VALUES (
7521, 'WARD', 'SALESMAN', 7698,
'1981-02-22', 1250, 500, 30
);
INSERT INTO emp VALUES (
7654, 'MARTIN', 'SALESMAN', 7698,
'1981-09-28', 1250, 1400, 30
);
INSERT INTO emp VALUES (
7844, 'TURNER', 'SALESMAN', 7698,
'1981-09-08', 1500, 0, 30
 DBMS Lab
Prepared by Mohibul Khan kaziranga university
);
INSERT INTO emp VALUES (
7876, 'ADAMS', 'CLERK', 7788,
'1987-05-23', 1100, NULL, 20
);
INSERT INTO emp VALUES (
7900, 'JAMES', 'CLERK', 7698,
'1981-12-03', 950, NULL, 30
);
INSERT INTO emp VALUES (
7934, 'MILLER', 'CLERK', 7782,
'1982-01-23', 1300, NULL, 10
);
Q-1: Write a nquery to find all emploee details from emploee table order by salary decending ?
Q-2 : WAQ to find emploee details from employee table whose salary greater than 8000 ?
Q-3: WAQ to find emploee details whose salary is between 5000 and 8000 ?
Q-4: WAQ to display emploee details from emploee table whose name is “ADAMS” and “JAMES” ?
Q-5: SELECT job table total salary with respect to job rom emploee table where total dsalary
greater than 2000 ?
Q-6: WAQ to display hiredate wise maximum salary order by salary ascending ?

Complete solution:
/*complete code */
CREATE DATABASE college;
USE college;
CREATE TABLE dept (
deptno INT(2),
dname VARCHAR(14),
loc VARCHAR(13),
CONSTRAINT pk_dept PRIMARY KEY (deptno)
);

CREATE TABLE emp (

empno INT(4),
ename VARCHAR(10),
job VARCHAR(9),
mgr INT(4),
hiredate DATE,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT(2),
CONSTRAINT pk_emp PRIMARY KEY (empno),
CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept (deptno)
);

CREATE TABLE bonus (

 DBMS Lab
Prepared by Mohibul Khan kaziranga university
ename VARCHAR(10),
job VARCHAR(9),
sal DECIMAL(10,2),
comm DECIMAL(10,2)
);

CREATE TABLE salgrade (

grade INT,
losal DECIMAL(10,2),
hisal DECIMAL(10,2)
);

INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK');

INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON');

INSERT INTO emp VALUES (

7839, 'KING', 'PRESIDENT', NULL,
'1981-11-17', 5000, NULL, 10
);
INSERT INTO emp VALUES (
7698, 'BLAKE', 'MANAGER', 7839,
'1981-05-01', 2850, NULL, 30
);
INSERT INTO emp VALUES (
7782, 'CLARK', 'MANAGER', 7839,
'1981-06-09', 2450, NULL, 10
);
INSERT INTO emp VALUES (
7566, 'JONES', 'MANAGER', 7839,
'1981-04-02', 2975, NULL, 20
);
INSERT INTO emp VALUES (
7788, 'SCOTT', 'ANALYST', 7566,
'1987-04-19', 3000, NULL, 20
);
INSERT INTO emp VALUES (
7902, 'FORD', 'ANALYST', 7566,
'1981-12-03', 3000, NULL, 20
);
INSERT INTO emp VALUES (
7369, 'SMITH', 'CLERK', 7902,
 DBMS Lab
Prepared by Mohibul Khan kaziranga university
'1980-12-17', 800, NULL, 20
);
INSERT INTO emp VALUES (
7499, 'ALLEN', 'SALESMAN', 7698,
'1981-02-20', 1600, 300, 30
);
INSERT INTO emp VALUES (
7521, 'WARD', 'SALESMAN', 7698,
'1981-02-22', 1250, 500, 30
);
INSERT INTO emp VALUES (
7654, 'MARTIN', 'SALESMAN', 7698,
'1981-09-28', 1250, 1400, 30
);
INSERT INTO emp VALUES (
7844, 'TURNER', 'SALESMAN', 7698,
'1981-09-08', 1500, 0, 30
);
INSERT INTO emp VALUES (
7876, 'ADAMS', 'CLERK', 7788,
'1987-05-23', 1100, NULL, 20
);
INSERT INTO emp VALUES (
7900, 'JAMES', 'CLERK', 7698,
'1981-12-03', 950, NULL, 30
);
INSERT INTO emp VALUES (
7934, 'MILLER', 'CLERK', 7782,
'1982-01-23', 1300, NULL, 10
);

/* question-1 */
SELECT *
FROM emp
ORDER BY sal DESC;

/* question-2 */
SELECT *
FROM emp
WHERE sal > 8000;

/* question-3 */
SELECT *
FROM emp
WHERE sal BETWEEN 5000 AND 8000;
 DBMS Lab
Prepared by Mohibul Khan kaziranga university
/* question-4 */
SELECT *
FROM emp
WHERE ename IN ('ADAMS', 'JAMES');

/* question-5 */
SELECT job, SUM(sal) AS total_salary
FROM emp
GROUP BY job
HAVING SUM(sal) > 2000;

/* question-6 */
SELECT hiredate, MAX(sal) AS max_salary
FROM emp
GROUP BY hiredate
ORDER BY max_salary ASC;

Q-1: Write a nquery to find all emploee details from emploee table order by salary decending ?
Ans:

/* question-1 */
SELECT *
FROM emp
ORDER BY sal DESC;

Q-2 : WAQ to find emploee details from employee table whose salary greater than 8000 ?
Ans:

/* question-2 */
SELECT *
FROM emp
 DBMS Lab
Prepared by Mohibul Khan kaziranga university
WHERE sal > 8000;

Q-3: WAQ to find emploee details whose salary is between 5000 and 8000 ?
Ans:

/* question-3 */
SELECT *
FROM emp
WHERE sal BETWEEN 5000 AND 8000;

Q-4: WAQ to display emploee details from emploee table whose name is “ADAMS” and “JAMES” .
Ans:

/* question-4 */
SELECT *
FROM emp
WHERE ename IN ('ADAMS', 'JAMES');

Q-5: SELECT job table total salary with respect to job rom emploee table where total dsalary
greater than 2000 ?
Ans:

/* question-5 */
SELECT job, SUM(sal) AS total_salary
FROM emp
GROUP BY job
HAVING SUM(sal) > 2000;
 DBMS Lab
Prepared by Mohibul Khan kaziranga university

Q-6: WAQ to display hiredate wise maximum salary order by salary ascending ?
Ans:

SELECT hiredate, MAX(sal) AS max_salary

FROM emp
GROUP BY hiredate
ORDER BY max_salary ASC;

03/09/24

make a employee table in MySQL that will contains E,name, E.id, E.Desg, E.email.
1. Select all the employees whose name starts with "E" ?
2. Select all the employee whose name ends with “D” ?
3. Select all the employee whose name has “S” in the ?
4. Select all the employee name and their designation whose name has exactly six character ?
5. Select all the employee departmentwise ?

CREATE DATABASE company;

USE company;

CREATE TABLE Employee (

E_id INT PRIMARY KEY AUTO_INCREMENT,
E_name VARCHAR(100) NOT NULL,
 DBMS Lab
Prepared by Mohibul Khan kaziranga university
E_Desg VARCHAR(100) NOT NULL,
E_email VARCHAR(100) NOT NULL
);

-- Insert data into the Employee table

INSERT INTO Employee (E_name, E_Desg, E_email)
VALUES
('Edward', 'Developer', 'edward@example.com'),
('Emily', 'Manager', 'emily@example.com'),
('David', 'Tester', 'david@example.com'),
('Susan', 'Designer', 'susan@example.com'),
('Sandra', 'Analyst', 'sandra@example.com'),
('Elvis', 'Consultant', 'elvis@example.com');

-- Select all employees whose name starts with "E"

SELECT * FROM Employee
WHERE E_name LIKE 'E%';

-- Select all employees whose name ends with "D"

SELECT * FROM Employee
WHERE E_name LIKE '%D';

-- Select all employees whose name has "S" in it

SELECT * FROM Employee
WHERE E_name LIKE '%S%';

-- Select employee names and their designations whose names have exactly six characters
SELECT E_name, E_Desg FROM Employee
WHERE CHAR_LENGTH(E_name) = 6;

-- Select all employees department-wise

SELECT E_Desg AS Department, GROUP_CONCAT(E_name) AS Employees
FROM Employee
GROUP BY E_Desg;

H.W:

10/09/24
DDL : Data definitioon language
• Create table
• Altering table
• Drop table
• Truncate
• Renaming
TCL :
 DBMS Lab
Prepared by Mohibul Khan kaziranga university

Question :
For the table “emp” that contains “id,name,email” column
For the table “std” that contains “sid,name” column
• By Adding a new column in a table .
ALTER TABLE emp
ADD eadd VARCHAR(100)
• By chaning the size of a column
ALTER table emp
MODIFY email to email(20);
• BY changing the column name
ALTER TABLE emp CHANGE NAME emploee(30)
• By adding a foreign key
Alter table name emp
Add foreign key sid(emp)
References sid(std)
Code:
-- Step 1: Create a database
CREATE DATABASE company_db;

-- Step 2: Use the database

USE company_db;

-- Step 3: Create the 'std' table (students table)

CREATE TABLE std (
sid INT PRIMARY KEY,
name VARCHAR(50)
);

-- Step 4: Create the 'emp' table (employees table)

CREATE TABLE emp (
id INT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(50),
sid INT -- This column will be used as a foreign key
);

-- Step 5: Add a new column 'eadd' to the 'emp' table

ALTER TABLE emp
ADD eadd VARCHAR(100);

-- Step 6: Modify the size of the 'email' column in the 'emp' table
ALTER TABLE emp
MODIFY email VARCHAR(20);

-- Step 7: Change the column name 'name' to 'emploee' in the 'emp' table
 DBMS Lab
Prepared by Mohibul Khan kaziranga university
ALTER TABLE emp
CHANGE name emploee VARCHAR(30);

-- Step 8: Add a foreign key from the 'emp' table to the 'std' table
ALTER TABLE emp
ADD CONSTRAINT fk_sid
FOREIGN KEY (sid)
REFERENCES std(sid);

select * from emp;

Question – 2:
Create a table B.tech 3rd sem
• Id- foreighn key
• Name
• Selection
• Courses

Query:
1. Add a new column teacher
2. Make teacher as a foreign key
3. Change the column name selection to branch
4. Rename the table to BtechKU
-- Step 1: Create a database

CREATE DATABASE company_db;

-- Step 2: Use the database

USE company_db;

-- Step 3: Create the 'std' table (students table)

CREATE TABLE std (

sid INT PRIMARY KEY,

name VARCHAR(50)

);

-- Step 4: Create the 'emp' table (employees table)

 DBMS Lab
Prepared by Mohibul Khan kaziranga university
CREATE TABLE emp (

id INT PRIMARY KEY,

name VARCHAR(50),

email VARCHAR(50),

sid INT -- This column will be used as a foreign key

);

-- Step 5: Add a new column 'eadd' to the 'emp' table

ALTER TABLE emp

ADD eadd VARCHAR(100);

-- Step 6: Modify the size of the 'email' column in the 'emp' table

ALTER TABLE emp

MODIFY email VARCHAR(20);

-- Step 7: Change the column name 'name' to 'emploee' in the 'emp' table

ALTER TABLE emp

CHANGE name emploee VARCHAR(30);

-- Step 8: Add a foreign key from the 'emp' table to the 'std' table

ALTER TABLE emp

ADD CONSTRAINT fk_sid

FOREIGN KEY (sid)

REFERENCES std(sid);

select * from emp;

-- Step 1: Create the 'Btech3rdSem' table

 DBMS Lab
Prepared by Mohibul Khan kaziranga university
CREATE TABLE Btech3rdSem (

id INT,

name VARCHAR(50),

selection VARCHAR(50),

courses VARCHAR(100),

PRIMARY KEY (id)

);

-- Step 2: Add a new column 'teacher'

ALTER TABLE Btech3rdSem

ADD teacher INT;

-- Step 3: Make 'teacher' a foreign key

-- Assuming there is a 'teachers' table already created, where 'id' is the primary key

ALTER TABLE Btech3rdSem

ADD CONSTRAINT fk_teacher

FOREIGN KEY (teacher)

REFERENCES teachers(id);

-- Step 4: Change the column name 'selection' to 'branch'

ALTER TABLE Btech3rdSem

CHANGE selection branch VARCHAR(50);

-- Step 5: Rename the table to 'BtechKU'

ALTER TABLE Btech3rdSem

RENAME TO BtechKU;

24/09/24
 DBMS Lab
Prepared by Mohibul Khan kaziranga university
DML : data manupilating language

• Insert value to table

• Update to table
• Retieve to table
• Delete to table
Q. Student (id , name, roll,mark, add)
1. Select student name and address whose marks is > 30
2. Select all the students whose name ends with “s”
3. Select all the student whose marks in the range 70-90
4. Select all the student whose address belongs to jorhat, sivsagar, guwahaty.
5. Select total marks and average marks of student
6. Select the total no of students

Ans : OK Tested by teacher

CREATE DATABASE college;

USE college;

CREATE TABLE Student (

sid INT PRIMARY KEY,
sname VARCHAR(100),
sroll VARCHAR(20),
smark INT,
sadd VARCHAR(100)
);

INSERT INTO Student (sid, sname, sroll, smark, sadd) VALUES

(1, 'John Doe', 'ET23BTHCS001', 85, 'Jorhat'),
(2, 'Jane Smith', 'ET23BTHCS002', 92, 'Sivsagar'),
(3, 'James Harris', 'ET23BTHCS003', 75, 'Guwahati'),
(4, 'Chris Evans', 'ET23BTHCS004', 60, 'Jorhat'),
(5, 'Martha Jones', 'ET23BTHCS005', 45, 'Sivsagar'),
(6, 'Lucas White', 'ET23BTHCS006', 25, 'Guwahati'),
(7, 'Patricia Summers', 'ET23BTHCS007', 88, 'Jorhat'),
(8, 'Alex Turner', 'ET23BTHCS008', 35, 'Sivsagar');

-- question 1
SELECT sname, sadd
FROM Student
WHERE smark > 30;

-- question 2
SELECT *
FROM Student
 DBMS Lab
Prepared by Mohibul Khan kaziranga university
WHERE sname LIKE '%s';

-- question 3
SELECT *
FROM Student
WHERE smark BETWEEN 70 AND 90;

-- question 4
SELECT *
FROM Student
WHERE sadd IN ('Jorhat', 'Sivsagar', 'Guwahati');

-- question 5
SELECT SUM(smark) AS total_marks, AVG(smark) AS average_marks
FROM Student;

-- question 6
SELECT COUNT(*) AS total_students
FROM Student;

1.

2.

3.
 DBMS Lab
Prepared by Mohibul Khan kaziranga university

4.

5.

6.

Date : 22 oct 2024

Question:

Shoppe
ID Sname Area
S01 ABC Comp CP
S02 All Soft GK ii
S03 Tech CP
Shopee
S04 HI Tech NP
Store

Accessories
No Name Price Id
 DBMS Lab
Prepared by Mohibul Khan kaziranga university
A01 Mother Bord 12000 S01
A02 Hard Disk 5000 S01
A03 Keyboard 500 S02
A04 Mouse 300 S04
A05 LCD 6000 S03
A06 Hard Disk 4500 S04

Query:
1. Too display name and price of all accessories in assending order of price ?
2. To display id and Sname of all shoppe location in NP ?
3. To display min and max price of each name of accessories ?
4. To display Name, Price of all accessories and their respective Sname where they are available ?

Answer:

CREATE DATABASE ShopDatabase;

USE ShopDatabase;

-- Create the Shoppe table

CREATE TABLE Shoppe (
ID VARCHAR(5) PRIMARY KEY,
Sname VARCHAR(50),
Area VARCHAR(20)
);

-- Insert data into the Shoppe table

INSERT INTO Shoppe (ID, Sname, Area) VALUES
('S01', 'ABC Comp', 'CP'),
('S02', 'All Soft', 'GK ii'),
('S03', 'Tech Shopee', 'CP'),
('S04', 'HI Tech Store', 'NP');

-- Create the Accessories table

CREATE TABLE Accessories (
No VARCHAR(5) PRIMARY KEY,
Name VARCHAR(50),
Price DECIMAL(10, 2),
Id VARCHAR(5),
FOREIGN KEY (Id) REFERENCES Shoppe(ID)
);

-- Insert data into the Accessories table

INSERT INTO Accessories (No, Name, Price, Id) VALUES
('A01', 'Mother Bord', 12000, 'S01'),
('A02', 'Hard Disk', 5000, 'S01'),
 DBMS Lab
Prepared by Mohibul Khan kaziranga university
('A03', 'Keyboard', 500, 'S02'),
('A04', 'Mouse', 300, 'S04'),
('A05', 'LCD', 6000, 'S03'),
('A06', 'Hard Disk', 4500, 'S04');

-- Query 1: Display name and price of all accessories in ascending order of price
SELECT Name, Price
FROM Accessories
ORDER BY Price ASC;

-- Query 2: Display ID and Sname of all shops located in NP

SELECT ID, Sname
FROM Shoppe
WHERE Area = 'NP';

-- Query 3: Display minimum and maximum price of each accessory name

SELECT Name, MIN(Price) AS Min_Price, MAX(Price) AS Max_Price
FROM Accessories
GROUP BY Name;

-- Query 4: Display Name, Price of all accessories and their respective shop name (Sname) where they are available
SELECT A.Name, A.Price, S.Sname
FROM Accessories A
JOIN Shoppe S ON A.Id = S.ID;

1:

2:

3:

4:
 DBMS Lab
Prepared by Mohibul Khan kaziranga university