JSS Academy of Technical Education Noida

Department of Computer Science and Engineering

EXP 1 :- Write the queries for Data Definition and Data Manipulation
Language.
Query

CREATE TABLE STUDENT (

Roll_No BIGINT(10) PRIMARY KEY,

Name VARCHAR(30) NOT NULL,

Branch VARCHAR(10) NOT NULL,

Year TINYINT NOT NULL,

Section VARCHAR(4),

Hostel VARCHAR(10),

F_name VARCHAR(30) NOT NULL,

Address VARCHAR(50)

);

CREATE TABLE BOOK (

Book_id VARCHAR(10) PRIMARY KEY,

Title VARCHAR(100) NOT NULL,

Author VARCHAR(30) NOT NULL,

Publisher VARCHAR(40) NOT NULL,

Cost DECIMAL(6, 2),

Copies INT CHECK (Copies > 0),

UNIQUE (Title)

);

CREATE TABLE TRANSACTION (

Roll_No BIGINT(10),

Book_id VARCHAR(10),

Date_Issue DATE,

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

Date_Return DATE,

Fine DECIMAL(4, 2),

PRIMARY KEY (Roll_No, Book_id),

FOREIGN KEY (Roll_No) REFERENCES STUDENT(Roll_No),

FOREIGN KEY (Book_id) REFERENCES BOOK(Book_id)

);

CREATE TABLE empl018 (

empno VARCHAR(5),

empname CHAR(10),

job CHAR(10),

city CHAR(10),

salary DECIMAL(10, 2),

deptno VARCHAR(5)

);

CREATE TABLE projo18 (

pno VARCHAR(5),

pname CHAR(10),

eno VARCHAR(5)

);

CREATE TABLE dept018 (

dno VARCHAR(5),

dname CHAR(10),

dlocation CHAR(10),

pno VARCHAR(10)

);

desc empl018;
Nitin Kumar 2200910100115
 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

desc projo18;

desc dept018;

insert into empl018 values('e1', 'Charu', 'HR', 'Agra', 50000, 'd1');

insert into empl018(empno, empname, job, city, salary, deptno)values('e2',

'Meenamkshi', 'IT', 'Jhansi', 40000, 'd2');

INSERT INTO empl018 (empno, empname, job, city, salary, deptno)

VALUES ('e3', 'Ankita', 'Marketing', 'Delhi', 60000, 'd3');

insert into empl018 values('e4','jai','Finance','Bangalore','40000','d4');

insert into empl018 values('e5','Anil','Sales','Bangalore','10000','d5');

insert into empl018 values('e6','Shivani','Sales','Bangalore','70000','d6');

insert into projo18 values('p1','DBMS','e1');

insert into projo18 values('p1','DBMS','e5');

insert into projo18 values('p2','NW','e3');

insert into dept018 values('d1','cse','Delhi','p1');

insert into dept018 values('d1','cse','Delhi','p2');

insert into dept018 values('d2','IT','delhi','p1');

select * from empl018;

select * from projo18;

select * from dept018;

select empno,empname from empl018;

ALTER TABLE empl018

ADD age TINYINT;

desc empl018;

insert into empl018(age)values(20);

select * from empl018;

alter table empl018 drop column age;

Nitin Kumar 2200910100115
 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

desc empl018;

ALTER TABLE empl018

MODIFY empname VARCHAR(15);

desc empl018;

RENAME TABLE empl018 TO emp018;

create table faketble(sno varchar(2), name char(10));

insert into faketble values('1','mini');

select * from faketble;

truncate table faketble;

select * from faketble;

drop table faketble;

UPDATE emp018

SET salary = salary * 1.25

WHERE salary = 40000;

SELECT * FROM emp018;

DELETE FROM emp018

WHERE empno = 'e2';

ALTER TABLE STUDENT ADD DOB DATE;

ALTER TABLE BOOK ADD Co_Author VARCHAR(30);

INSERT INTO STUDENT (Roll_No, Name, Branch, Year, Section, Hostel, F_name, Address)

VALUES (1209113053, 'Roffery', 'CS', 4, 'CS1', 'Sanskriti', 'John', '12/45 Lajpat Nagar, Delhi');

INSERT INTO STUDENT (Roll_No, Name, Branch, Year, Section, Hostel, F_name, Address)

VALUES (1209113059, 'Ron', 'EC', 4, 'EC-2', 'Sanskriti', 'Jay', '12/85 Sec-67, Noida');

INSERT INTO BOOK (Book_ID, Title, Author, Co_Author, Publisher, Cost, Copies)

VALUES ('BB-095', 'Database Management System', 'Korth','Schilberschutz', 'TATA Mc-Graw',

1595.56, 300);

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

INSERT INTO TRANSACTION (Roll_No, Book_ID, Date_Issue, Date_Return, Fine)

VALUES (1209113053, 'BB-095', '2014-08-23', '2014-09-15', 12.00);

SELECT empname

FROM emp018

WHERE salary > 30000 AND city = 'Agra';

SELECT empname

FROM emp018

WHERE salary > 70000 OR city = 'Agra';

SELECT empname

FROM emp018

WHERE city != 'Jhansi';

SELECT empno, empname, city

FROM emp018

WHERE empno BETWEEN 'e1' AND 'e2';

SELECT empname

FROM emp018

WHERE salary < 60000;

SELECT empname

FROM emp018

WHERE salary > 70000;

SELECT empname

FROM emp018

WHERE deptno = 'd4';

SELECT empno, empname, salary

FROM emp018

WHERE salary = 50000;

Nitin Kumar 2200910100115
 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

SELECT *

FROM emp018

WHERE salary = 50000;

COMMIT;

SELECT empno, empname

FROM emp018

WHERE empname LIKE 'C%';

SELECT empno, empname

FROM emp018

WHERE empname LIKE '%hi%';

OUTPUT-
+---------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| empno | varchar(5) | YES | | NULL | |
| empname | char(10) | YES | | NULL | |
| job | char(10) | YES | | NULL | |
| city | char(10) | YES | | NULL | |
| salary | decimal(10,2) | YES | | NULL | |
| deptno | varchar(5) | YES | | NULL | |
+---------+---------------+------+-----+---------+-------+
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| pno | varchar(5) | YES | | NULL | |
| pname | char(10) | YES | | NULL | |
| eno | varchar(5) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| dno | varchar(5) | YES | | NULL | |
| dname | char(10) | YES | | NULL | |
| dlocation | char(10) | YES | | NULL | |
| pno | varchar(10) | YES | | NULL | |
+-----------+-------------+------+-----+---------+-------+
+-------+------------+-----------+-----------+----------+--------+
| empno | empname | job | city | salary | deptno |
+-------+------------+-----------+-----------+----------+--------+
| e1 | Charu | HR | Agra | 50000.00 | d1 |
| e2 | Meenamkshi | IT | Jhansi | 40000.00 | d2 |
| e3 | Ankita | Marketing | Delhi | 60000.00 | d3 |
| e4 | jai | Finance | Bangalore | 40000.00 | d4 |

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

| e5 | Anil | Sales | Bangalore | 10000.00 | d5 |

| e6 | Shivani | Sales | Bangalore | 70000.00 | d6 |
+-------+------------+-----------+-----------+----------+--------+
+------+-------+------+
| pno | pname | eno |
+------+-------+------+
| p1 | DBMS | e1 |
| p1 | DBMS | e5 |
| p2 | NW | e3 |
+------+-------+------+
+------+-------+-----------+------+
| dno | dname | dlocation | pno |
+------+-------+-----------+------+
| d1 | cse | Delhi | p1 |
| d1 | cse | Delhi | p2 |
| d2 | IT | delhi | p1 |
+------+-------+-----------+------+
+-------+------------+
| empno | empname |
+-------+------------+
| e1 | Charu |
| e2 | Meenamkshi |
| e3 | Ankita |
| e4 | jai |
| e5 | Anil |
| e6 | Shivani |
+-------+------------+
+---------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| empno | varchar(5) | YES | | NULL | |
| empname | char(10) | YES | | NULL | |
| job | char(10) | YES | | NULL | |
| city | char(10) | YES | | NULL | |
| salary | decimal(10,2) | YES | | NULL | |
| deptno | varchar(5) | YES | | NULL | |
| age | tinyint | YES | | NULL | |
+---------+---------------+------+-----+---------+-------+
+-------+------------+-----------+-----------+----------+--------+------+
| empno | empname | job | city | salary | deptno | age |
+-------+------------+-----------+-----------+----------+--------+------+
| e1 | Charu | HR | Agra | 50000.00 | d1 | NULL |
| e2 | Meenamkshi | IT | Jhansi | 40000.00 | d2 | NULL |
| e3 | Ankita | Marketing | Delhi | 60000.00 | d3 | NULL |
| e4 | jai | Finance | Bangalore | 40000.00 | d4 | NULL |
| e5 | Anil | Sales | Bangalore | 10000.00 | d5 | NULL |
| e6 | Shivani | Sales | Bangalore | 70000.00 | d6 | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | 20 |
+-------+------------+-----------+-----------+----------+--------+------+
+---------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| empno | varchar(5) | YES | | NULL | |
| empname | char(10) | YES | | NULL | |
| job | char(10) | YES | | NULL | |
| city | char(10) | YES | | NULL | |
| salary | decimal(10,2) | YES | | NULL | |
| deptno | varchar(5) | YES | | NULL | |
+---------+---------------+------+-----+---------+-------+
+---------+---------------+------+-----+---------+-------+

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

| Field | Type | Null | Key | Default | Extra |

+---------+---------------+------+-----+---------+-------+
| empno | varchar(5) | YES | | NULL | |
| empname | varchar(15) | YES | | NULL | |
| job | char(10) | YES | | NULL | |
| city | char(10) | YES | | NULL | |
| salary | decimal(10,2) | YES | | NULL | |
| deptno | varchar(5) | YES | | NULL | |
+---------+---------------+------+-----+---------+-------+
+------+------+
| sno | name |
+------+------+
| 1 | mini |
+------+------+
+-------+------------+-----------+-----------+----------+--------+
| empno | empname | job | city | salary | deptno |
+-------+------------+-----------+-----------+----------+--------+
| e1 | Charu | HR | Agra | 50000.00 | d1 |
| e2 | Meenamkshi | IT | Jhansi | 50000.00 | d2 |
| e3 | Ankita | Marketing | Delhi | 60000.00 | d3 |
| e4 | jai | Finance | Bangalore | 50000.00 | d4 |
| e5 | Anil | Sales | Bangalore | 10000.00 | d5 |
| e6 | Shivani | Sales | Bangalore | 70000.00 | d6 |
| NULL | NULL | NULL | NULL | NULL | NULL |
+-------+------------+-----------+-----------+----------+--------+
+---------+
| empname |
+---------+
| Charu |
+---------+
+---------+
| empname |
+---------+
| Charu |
+---------+
+---------+
| empname |
+---------+
| Charu |
| Ankita |
| jai |
| Anil |
| Shivani |
+---------+
+-------+---------+------+
| empno | empname | city |
+-------+---------+------+
| e1 | Charu | Agra |
+-------+---------+------+
+---------+
| empname |
+---------+
| Charu |
| jai |
| Anil |
+---------+
+---------+
| empname |
+---------+
| jai |

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

+---------+
+-------+---------+----------+
| empno | empname | salary |
+-------+---------+----------+
| e1 | Charu | 50000.00 |
| e4 | jai | 50000.00 |
+-------+---------+----------+
+-------+---------+---------+-----------+----------+--------+
| empno | empname | job | city | salary | deptno |
+-------+---------+---------+-----------+----------+--------+
| e1 | Charu | HR | Agra | 50000.00 | d1 |
| e4 | jai | Finance | Bangalore | 50000.00 | d4 |
+-------+---------+---------+-----------+----------+--------+
+-------+---------+
| empno | empname |
+-------+---------+
| e1 | Charu |
+-------+---------+
+-------+---------+
| empno | empname |
+-------+---------+
| e6 | Shivani |
+-------+---------+

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

EXP 2:- To explore ‘select’ statement using various clauses like where, order
by, between, like, group by, having and logical/relational operators.To
implement various DML statement.
Query-

CREATE TABLE EMPLOYEE (

eno DECIMAL(4,0) NULL,

ename VARCHAR(10) NULL,

ejob VARCHAR(9) NULL,

emgr DECIMAL(4,0) NULL,

doj DATE NULL,

sal DECIMAL(7,2) NULL,

perks DECIMAL(7,2) NULL,

dno DECIMAL(2,0) NULL

);

INSERT INTO EMPLOYEE (eno, ename, ejob, emgr, doj, sal, perks, dno)

VALUES

(11, 'x', 'clerk', 34, '1980-10-07', 800.00, NULL, 10),

(12, 'y', 'sales', 24, '1985-12-15', 1600.00, 500.00, 20),

(13, 'z', 'sales', 34, '1981-04-25', 1600.00, 300.00, 10),

(14, 'xy', 'manager', 23, '1981-05-13', 2975.00, NULL, 20),

(21, 'xz', 'analyst', 34, '1987-05-12', 1100.00, NULL, 30),

(22, 'yz', 'clerk', 14, '1981-11-22', 850.00, NULL, 30),

(23, 'zz', 'president', NULL, '1984-01-22', 5000.00, NULL, 10),

(24, 'xx', 'manager', 23, '1980-10-27', 2400.00, 0.00, 10),

(31, 'yy', 'sales', 24, '1980-05-22', 1500.00, 1500.00, 20),

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

(32, 'xyz', 'analyst', 24, '1985-11-05', 1600.00, 300.00, 20),

(33, 'zyx', 'sales', 24, '1987-03-03', 1200.00, 700.00, 10),

(34, 'yxz', 'manager', 23, '1985-06-13', 2500.00, NULL, 30);

SELECT * FROM EMPLOYEE;

SELECT ename, sal FROM EMPLOYEE;

SELECT dno, eno, emgr FROM EMPLOYEE;

SELECT * FROM EMPLOYEE WHERE dno = 20;

SELECT ename, sal FROM EMPLOYEE WHERE sal > 1000;

SELECT eno, ename FROM EMPLOYEE WHERE ejob = 'manager';

SELECT eno, ename FROM EMPLOYEE WHERE ejob = 'clerk' AND dno = 20;

SELECT ename, ejob FROM EMPLOYEE WHERE ejob IN ('analyst', 'sales');

SELECT ename FROM EMPLOYEE WHERE doj < '1984-09-30';

COMMIT;

OUTPUT-
+------+-------+-----------+------+------------+---------+---------+------+
| eno | ename | ejob | emgr | doj | sal | perks | dno |
+------+-------+-----------+------+------------+---------+---------+------+
| 11 | x | clerk | 34 | 1980-10-07 | 800.00 | NULL | 10 |
| 12 | y | sales | 24 | 1985-12-15 | 1600.00 | 500.00 | 20 |
| 13 | z | sales | 34 | 1981-04-25 | 1600.00 | 300.00 | 10 |
| 14 | xy | manager | 23 | 1981-05-13 | 2975.00 | NULL | 20 |
| 21 | xz | analyst | 34 | 1987-05-12 | 1100.00 | NULL | 30 |
| 22 | yz | clerk | 14 | 1981-11-22 | 850.00 | NULL | 30 |
| 23 | zz | president | NULL | 1984-01-22 | 5000.00 | NULL | 10 |
| 24 | xx | manager | 23 | 1980-10-27 | 2400.00 | 0.00 | 10 |
| 31 | yy | sales | 24 | 1980-05-22 | 1500.00 | 1500.00 | 20 |
| 32 | xyz | analyst | 24 | 1985-11-05 | 1600.00 | 300.00 | 20 |
| 33 | zyx | sales | 24 | 1987-03-03 | 1200.00 | 700.00 | 10 |
| 34 | yxz | manager | 23 | 1985-06-13 | 2500.00 | NULL | 30 |
+------+-------+-----------+------+------------+---------+---------+------+
+-------+---------+
| ename | sal |
+-------+---------+
| x | 800.00 |
| y | 1600.00 |
| z | 1600.00 |

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

| xy | 2975.00 |
| xz | 1100.00 |
| yz | 850.00 |
| zz | 5000.00 |
| xx | 2400.00 |
| yy | 1500.00 |
| xyz | 1600.00 |
| zyx | 1200.00 |
| yxz | 2500.00 |
+-------+---------+
+------+------+------+
| dno | eno | emgr |
+------+------+------+
| 10 | 11 | 34 |
| 20 | 12 | 24 |
| 10 | 13 | 34 |
| 20 | 14 | 23 |
| 30 | 21 | 34 |
| 30 | 22 | 14 |
| 10 | 23 | NULL |
| 10 | 24 | 23 |
| 20 | 31 | 24 |
| 20 | 32 | 24 |
| 10 | 33 | 24 |
| 30 | 34 | 23 |
+------+------+------+
+------+-------+---------+------+------------+---------+---------+------+
| eno | ename | ejob | emgr | doj | sal | perks | dno |
+------+-------+---------+------+------------+---------+---------+------+
| 12 | y | sales | 24 | 1985-12-15 | 1600.00 | 500.00 | 20 |
| 14 | xy | manager | 23 | 1981-05-13 | 2975.00 | NULL | 20 |
| 31 | yy | sales | 24 | 1980-05-22 | 1500.00 | 1500.00 | 20 |
| 32 | xyz | analyst | 24 | 1985-11-05 | 1600.00 | 300.00 | 20 |
+------+-------+---------+------+------------+---------+---------+------+
+-------+---------+
| ename | sal |
+-------+---------+
| y | 1600.00 |
| z | 1600.00 |
| xy | 2975.00 |
| xz | 1100.00 |
| zz | 5000.00 |
| xx | 2400.00 |
| yy | 1500.00 |
| xyz | 1600.00 |
| zyx | 1200.00 |
| yxz | 2500.00 |
+-------+---------+
+------+-------+
| eno | ename |
+------+-------+
| 14 | xy |
| 24 | xx |
| 34 | yxz |
+------+-------+
+-------+---------+
| ename | ejob |
+-------+---------+
| y | sales |
| z | sales |

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

| xz | analyst |
| yy | sales |
| xyz | analyst |
| zyx | sales |
+-------+---------+
+-------+
| ename |
+-------+
| x |
| z |
| xy |
| yz |
| zz |
| xx |
| yy |
+-------+

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

DBMS Lab Assignment

Write SQL statements for the following and check result for evey query by select command
except the first :

List the name, salary and PF of all employees. (where PF is 10% of salary)

List the employee name, salary, PF, HRA, DA and gross;

List empno, name and salary of all employees in the ascending order of salary.

List the employee name and hiredate in ascending/descending of hiredate.

List the total salary of employees

Display the maximum salary of employee working as salesman

Display the maximum ,min and average of employee salary working in department 10.

List the number of employees working with the company.

List the number of jobs available in the company.

List the maximum salary of employees.

List the maximum salary of employees working as salesman .

Save the Table by COMMIT Command

Query-

CREATE TABLE EMPLOYEE (

eno DECIMAL(4,0),

ename VARCHAR(10),

ejob VARCHAR(9),

emgr DECIMAL(4,0),

doj DATE,

sal DECIMAL(7,2),

perks DECIMAL(7,2),

dno DECIMAL(2,0),

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

age INT,

PRIMARY KEY (eno)

);

INSERT INTO EMPLOYEE (eno, ename, ejob, emgr, doj, sal, perks, dno, age)

VALUES

(11, 'x', 'clerk', 34, '1980-10-07', 800.00, NULL, 10, 25),

(12, 'y', 'sales', 24, '1985-12-15', 1600.00, 500.00, 10, 26),

(13, 'z', 'sales', 34, '1981-04-25', 1600.00, 300.00, 10, 26),

(14, 'xy', 'manager', 23, '1981-05-13', 2975.00, NULL, 20, 29),

(21, 'xyz', 'analyst', 14, '1987-05-12', 1800.00, NULL, 30, 24),

(22, 'yz', 'clerk', 14, '1981-11-22', 850.00, NULL, 30, 25),

(23, 'xx', 'president', NULL, '1984-01-22', 5000.00, NULL, 30, 40),

(24, 'xx', 'manager', 23, '1980-10-27', 2400.00, 0.00, 10, 32),

(31, 'y', 'sales', 24, '1980-05-22', 1500.00, 1500.00, 10, 28),

(32, 'xyz', 'analyst', 14, '1985-11-22', 1800.00, NULL, 20, 35),

(33, 'zyx', 'sales', 24, '1987-03-03', 1200.00, 700.00, 10, 26),

(34, 'yyx', 'manager', 23, '1985-06-13', 2500.00, NULL, 30, 33);

SELECT ename, sal, (sal * 0.10) AS PF

FROM EMPLOYEE;

SELECT ename, sal, (sal * 0.10) AS PF,

(sal * 0.20) AS HRA,

(sal * 0.50) AS DA,

(sal + (sal * 0.20) + (sal * 0.50)) AS GROSS

FROM EMPLOYEE;
Nitin Kumar 2200910100115
 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

SELECT eno, ename, sal

FROM EMPLOYEE

ORDER BY sal ASC;

SELECT ename, doj

FROM EMPLOYEE

ORDER BY doj ASC;

SELECT ename, doj

FROM EMPLOYEE

ORDER BY doj DESC;

SELECT SUM(sal) AS TOTAL_SALARY

FROM EMPLOYEE;

SELECT MAX(sal) AS MAX_SALARY

FROM EMPLOYEE

WHERE ejob = 'sales';

SELECT MAX(sal) AS MAX_SALARY,

MIN(sal) AS MIN_SALARY,

AVG(sal) AS AVG_SALARY

FROM EMPLOYEE

WHERE dno = 10;

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

SELECT COUNT(*) AS NUM_EMPLOYEES

FROM EMPLOYEE;

SELECT COUNT(DISTINCT ejob) AS NUM_JOBS

FROM EMPLOYEE;

SELECT MAX(sal) AS MAX_SALARY

FROM EMPLOYEE;

SELECT MAX(sal) AS MAX_SALARY

FROM EMPLOYEE

WHERE ejob = 'sales';

COMMIT;

OUTPUT-
+-------+---------+----------+
| ename | sal | PF |
+-------+---------+----------+
| x | 800.00 | 80.0000 |
| y | 1600.00 | 160.0000 |
| z | 1600.00 | 160.0000 |
| xy | 2975.00 | 297.5000 |
| xyz | 1800.00 | 180.0000 |
| yz | 850.00 | 85.0000 |
| xx | 5000.00 | 500.0000 |
| xx | 2400.00 | 240.0000 |
| y | 1500.00 | 150.0000 |
| xyz | 1800.00 | 180.0000 |
| zyx | 1200.00 | 120.0000 |
| yyx | 2500.00 | 250.0000 |
+-------+---------+----------+
+-------+---------+----------+-----------+-----------+-----------+
| ename | sal | PF | HRA | DA | GROSS |
+-------+---------+----------+-----------+-----------+-----------+
| x | 800.00 | 80.0000 | 160.0000 | 400.0000 | 1360.0000 |
| y | 1600.00 | 160.0000 | 320.0000 | 800.0000 | 2720.0000 |
| z | 1600.00 | 160.0000 | 320.0000 | 800.0000 | 2720.0000 |

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

| xy | 2975.00 | 297.5000 | 595.0000 | 1487.5000 | 5057.5000 |

| xyz | 1800.00 | 180.0000 | 360.0000 | 900.0000 | 3060.0000 |
| yz | 850.00 | 85.0000 | 170.0000 | 425.0000 | 1445.0000 |
| xx | 5000.00 | 500.0000 | 1000.0000 | 2500.0000 | 8500.0000 |
| xx | 2400.00 | 240.0000 | 480.0000 | 1200.0000 | 4080.0000 |
| y | 1500.00 | 150.0000 | 300.0000 | 750.0000 | 2550.0000 |
| xyz | 1800.00 | 180.0000 | 360.0000 | 900.0000 | 3060.0000 |
| zyx | 1200.00 | 120.0000 | 240.0000 | 600.0000 | 2040.0000 |
| yyx | 2500.00 | 250.0000 | 500.0000 | 1250.0000 | 4250.0000 |
+-------+---------+----------+-----------+-----------+-----------+
+-----+-------+---------+
| eno | ename | sal |
+-----+-------+---------+
| 11 | x | 800.00 |
| 22 | yz | 850.00 |
| 33 | zyx | 1200.00 |
| 31 | y | 1500.00 |
| 12 | y | 1600.00 |
| 13 | z | 1600.00 |
| 21 | xyz | 1800.00 |
| 32 | xyz | 1800.00 |
| 24 | xx | 2400.00 |
| 34 | yyx | 2500.00 |
| 14 | xy | 2975.00 |
| 23 | xx | 5000.00 |
+-----+-------+---------+
+-------+------------+
| ename | doj |
+-------+------------+
| y | 1980-05-22 |
| x | 1980-10-07 |
| xx | 1980-10-27 |
| z | 1981-04-25 |
| xy | 1981-05-13 |
| yz | 1981-11-22 |
| xx | 1984-01-22 |
| yyx | 1985-06-13 |
| xyz | 1985-11-22 |
| y | 1985-12-15 |
| zyx | 1987-03-03 |
| xyz | 1987-05-12 |
+-------+------------+
+-------+------------+
| ename | doj |
+-------+------------+
| xyz | 1987-05-12 |
| zyx | 1987-03-03 |
| y | 1985-12-15 |
| xyz | 1985-11-22 |
| yyx | 1985-06-13 |
| xx | 1984-01-22 |
| yz | 1981-11-22 |
| xy | 1981-05-13 |
| z | 1981-04-25 |
| xx | 1980-10-27 |
| x | 1980-10-07 |
| y | 1980-05-22 |
+-------+------------+
+--------------+
| TOTAL_SALARY |

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

+--------------+
| 24025.00 |
+--------------+
+------------+
| MAX_SALARY |
+------------+
| 1600.00 |
+------------+
+------------+------------+-------------+
| MAX_SALARY | MIN_SALARY | AVG_SALARY |
+------------+------------+-------------+
| 2400.00 | 800.00 | 1516.666667 |
+------------+------------+-------------+
+---------------+
| NUM_EMPLOYEES |
+---------------+
| 12 |
+---------------+
+----------+
| NUM_JOBS |
+----------+
| 5 |
+----------+
+------------+
| MAX_SALARY |
+------------+
| 5000.00 |
+------------+
+------------+
| MAX_SALARY |
+------------+
| 1600.00 |
+------------+

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

EXP 3:- To implement nested and correlated queries and to create various
views and indexes and to implement Cursors and triggers using PL/SQL.
Query-

SET LINESIZE 100

SET PAGESIZE 50

SET COLSEP ' | '

COLUMN BookID FORMAT 999

COLUMN Title FORMAT A30

COLUMN Author FORMAT A30

COLUMN PublishedYear FORMAT 9999

CREATE TABLE BOOK (

BookID NUMBER PRIMARY KEY,

Title VARCHAR2(100),

Author VARCHAR2(100),

PublishedYear NUMBER

);

INSERT INTO BOOK (BookID, Title, Author, PublishedYear) VALUES (1, 'Database
Management Systems', 'Raghu Ramakrishnan', 2020);

INSERT INTO BOOK (BookID, Title, Author, PublishedYear) VALUES (2, 'Operating Systems',
'Andrew S. Tanenbaum', 2018);

UPDATE BOOK

SET Title = 'DBMS'

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

WHERE Title = 'Database Management Systems';

SET LINESIZE 100

SET PAGESIZE 50

SET COLSEP ' | '

COLUMN Roll_No FORMAT 999

COLUMN Name FORMAT A20

COLUMN Hostel FORMAT A20

COLUMN Course FORMAT A20

CREATE TABLE STUDENT (

Roll_No NUMBER PRIMARY KEY,

Name VARCHAR2(100),

Hostel VARCHAR2(100),

Course VARCHAR2(100)

);

INSERT INTO STUDENT (Roll_No, Name, Hostel, Course) VALUES (101, 'Alice', 'Hostel A',
'Computer Science');

INSERT INTO STUDENT (Roll_No, Name, Hostel, Course) VALUES (102, 'Bob', 'Hostel B',
'Mechanical Engineering');

INSERT INTO STUDENT (Roll_No, Name, Hostel, Course) VALUES (100, 'Charlie', 'Hostel C',
'Electrical Engineering');

UPDATE STUDENT

SET Hostel = NULL;

SELECT * FROM STUDENT;

SELECT *

FROM STUDENT

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

ORDER BY Roll_No;

INSERT INTO BOOK (BookID, Title, Author, PublishedYear)

VALUES (3, 'Networking', 'John Doe', 2023);

INSERT INTO BOOK (BookID, Title, Author, PublishedYear)

VALUES (4, 'Computer Graphics', 'James Smith', 2022);

SELECT * FROM BOOK WHERE Title = 'Networking';

SELECT *

FROM BOOK

WHERE Title LIKE 'Computer%';

CREATE TABLE TRANSACTION (

Transaction_id NUMBER PRIMARY KEY,

Book_id NUMBER,

Issue_date DATE,

Return_date DATE,

FOREIGN KEY (Book_id) REFERENCES BOOK(BookID)

);

INSERT INTO TRANSACTION (Transaction_id, Book_id, Issue_date, Return_date)

VALUES (1, 4, TO_DATE('2024-11-01', 'YYYY-MM-DD'), TO_DATE('2024-11-15', 'YYYY-MM-

DD'));

SELECT b.*

FROM BOOK b

JOIN TRANSACTION t ON b.BookID = t.Book_id;

SELECT *

FROM BOOK

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

WHERE BookID NOT IN (

SELECT Book_id

FROM TRANSACTION

);

ALTER TABLE STUDENT

ADD Year NUMBER;

UPDATE STUDENT

SET Year = 4

WHERE Roll_No = 101; -- Replace 101 with the actual Roll_No of the student

SELECT *

FROM STUDENT

WHERE Hostel IS NOT NULL

AND Year = 4

AND Course = 'Computer Science';

ALTER TABLE TRANSACTION

ADD Roll_No NUMBER;

ALTER TABLE TRANSACTION

ADD CONSTRAINT fk_roll_no

FOREIGN KEY (Roll_No) REFERENCES STUDENT(Roll_No);

UPDATE TRANSACTION

SET Roll_No = 101

WHERE Transaction_id = 1;

SELECT S.Name

FROM STUDENT S
Nitin Kumar 2200910100115
 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

JOIN TRANSACTION T ON S.Roll_No = T.Roll_No

WHERE T.Return_date IS NULL

OR T.Return_date < SYSDATE;

INSERT INTO BOOK (BookID, Title, Author, PublishedYear)

VALUES (5, 'DBMS', 'Korth', 2024);

SELECT S.Name

FROM STUDENT S

JOIN TRANSACTION T ON S.Roll_No = T.Roll_No

JOIN BOOK B ON T.Book_id = B.BookID

WHERE B.Title = 'DBMS'

AND B.Author = 'Korth';

SELECT *

FROM STUDENT S

WHERE NOT EXISTS (

SELECT 1

FROM TRANSACTION T

WHERE T.Roll_No = S.Roll_No

);

SELECT DISTINCT S.Name

FROM STUDENT S

JOIN TRANSACTION T ON S.Roll_No = T.Roll_No;

ALTER TABLE BOOK

ADD Price NUMBER;

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

UPDATE BOOK

SET Price = 500

WHERE BookID = 1;

UPDATE BOOK

SET Price = 200

WHERE BookID = 2;

UPDATE BOOK

SET Price = 400

WHERE BookID = 3;

UPDATE BOOK

SET Price = 350

WHERE BookID = 4;

UPDATE BOOK

SET Price = 1000

WHERE BookID = 5;

SELECT Title

FROM (

SELECT Title, Price, RANK() OVER (ORDER BY Price DESC) AS Price_Rank

FROM BOOK

WHERE Price_Rank = 2;

SELECT S.Name

FROM STUDENT S

JOIN TRANSACTION T ON S.Roll_No = T.Roll_No

WHERE T.Return_date IS NULL

Nitin Kumar 2200910100115
 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

OR T.Return_date < SYSDATE - 42;

SELECT S.Name AS Student_Name, B.Title AS Book_Title

FROM STUDENT S

JOIN TRANSACTION T ON S.Roll_No = T.Roll_No

JOIN BOOK B ON T.Book_id = B.BookID;

SELECT S.Name

FROM STUDENT S

JOIN TRANSACTION T ON S.Roll_No = T.Roll_No

JOIN BOOK B ON T.Book_id = B.BookID

WHERE S.Year = 4

AND S.Course = 'Computer Science'

AND B.Title = 'Graphics'

AND T.Return_date IS NULL

AND T.Issue_date < SYSDATE - 15;

INSERT INTO STUDENT (Roll_No, Name, Hostel, Year, Course)

VALUES (110, 'Anant', 'Hostel A', 4, 'CSE');

INSERT INTO STUDENT (Roll_No, Name, Hostel, Year, Course)

VALUES (112, 'Bunny', 'Hostel B', 4, 'CSE');

INSERT INTO STUDENT (Roll_No, Name, Hostel, Year, Course)

VALUES (113, 'Charu', 'Hostel C', 4, 'CSE');

CREATE VIEW CSE_4th_Year_Students AS

Nitin Kumar 2200910100115
 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

SELECT *

FROM STUDENT

WHERE Year = 4 AND Course = 'CSE';

SELECT * FROM CSE_4th_Year_Students;

OUTPUT-

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

EXP 4:- To enforce various integrity constraint and to implement various DCL
Statements

Query-
CREATE TABLE STUDENT (
Roll_No NUMBER PRIMARY KEY,
Name VARCHAR2(100),
Year NUMBER,
Branch VARCHAR2(50)
);
INSERT INTO STUDENT (Roll_No, Name, Year, Branch) VALUES (1005, 'Sonia', 4, 'CSE');
INSERT INTO STUDENT (Roll_No, Name, Year, Branch) VALUES (1006, 'John', 3, 'ECE');
INSERT INTO STUDENT (Roll_No, Name, Year, Branch) VALUES (1007, 'Alice', 4, 'CSE');

SET SERVEROUTPUT ON;

DECLARE
rollno NUMBER;
br STUDENT.Branch%TYPE;
yr STUDENT.Year%TYPE;
nm STUDENT.Name%TYPE;
BEGIN

rollno := &rollno;

SELECT Branch, Year, Name

INTO br, yr, nm
FROM STUDENT
WHERE Roll_No = rollno;

IF yr = 4 THEN
IF br = 'CSE' THEN
DBMS_OUTPUT.PUT_LINE('NAME OF STUDENT OF CSE BRANCH OF 4 YEAR IS: ' ||
nm);
ELSE
DBMS_OUTPUT.PUT_LINE('WRONG VALUE... STUDENT IS NOT OF SPECIFIED BRANCH
OR YEAR');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('WRONG VALUE... STUDENT IS NOT OF SPECIFIED BRANCH OR
YEAR');
END IF;
EXCEPTION

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('No student found with the given Roll_No.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

OUTPUT-
Enter value for rollno: old 8: rollno := &rollno;
new 8: rollno := 1005;
NAME OF STUDENT OF CSE BRANCH OF 4 YEAR IS: Sonia

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

EXP 5:- To Implement Cursors and triggers using PL/SQL.

Query-
CREATE TABLE employee018 (
empn VARCHAR2(10) PRIMARY KEY,
ename VARCHAR2(50),
salary NUMBER(8, 2),
job VARCHAR2(30)
);
INSERT INTO employee018 (empn, ename, salary, job) VALUES ('e1', 'Meenakshi', 75000,
'Director');
INSERT INTO employee018 (empn, ename, salary, job) VALUES ('e2', 'Deepak', 6500,
'Analyst');
INSERT INTO employee018 (empn, ename, salary, job) VALUES ('e3', 'Devesh', 7000,
'Manager');
INSERT INTO employee018 (empn, ename, salary, job) VALUES ('e4', 'Shashank', 2000,
'Clerk');
INSERT INTO employee018 (empn, ename, salary, job) VALUES ('e5', 'Shantnu', 8000,
'Manager');

SET SERVEROUTPUT ON;

DECLARE
CURSOR cl IS
SELECT *
FROM employee018
WHERE salary > 2500
AND job <> 'Director';

emprec employee018%ROWTYPE;
BEGIN
OPEN cl;
DBMS_OUTPUT.PUT_LINE('Records fetched are:');

IF cl%ISOPEN THEN
LOOP
FETCH cl INTO emprec;
EXIT WHEN cl%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(emprec.empn || ' ' || emprec.ename || ' ' ||

emprec.salary || ' ' || emprec.job);

IF emprec.job = 'Manager' THEN

UPDATE employee018

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

SET salary = salary + 500

WHERE empn = emprec.empn;

ELSIF emprec.job = 'Analyst' THEN

UPDATE employee018
SET salary = salary + 750
WHERE empn = emprec.empn;
END IF;
END LOOP;
CLOSE cl;
END IF;
END;
/
CREATE TABLE cust_mast (
cust VARCHAR2(10),
cust_name VARCHAR2(50),
dob DATE
);
INSERT INTO cust_mast (cust, cust_name, dob)
VALUES ('c1', 'Meenakshi', TO_DATE('26-SEP-91', 'DD-MON-YY'));

INSERT INTO cust_mast (cust, cust_name, dob)

VALUES ('c2', 'Shivani', TO_DATE('27-DEC-92', 'DD-MON-YY'));

INSERT INTO cust_mast (cust, cust_name, dob)

VALUES ('c3', 'Shantanu', TO_DATE('30-SEP-72', 'DD-MON-YY'));

CREATE TABLE audit_sys (

cust_no VARCHAR2(10),
cust_name VARCHAR2(50),
dob DATE,
action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE OR REPLACE TRIGGER audit_sys
BEFORE DELETE OR UPDATE ON cust_mast
FOR EACH ROW
BEGIN

INSERT INTO audit_sys (cust_no, cust_name, dob)

VALUES (:old.cust, :old.cust_name, :old.dob);
END;
/
DELETE FROM cust_mast WHERE cust = 'c3';
SELECT * FROM audit_sys;
UPDATE cust_mast

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

SET dob = TO_DATE('25-NOV-1990', 'DD-MON-YYYY')

WHERE cust = 'c2';
SELECT * FROM audit_sys;

OUTPUT-
Records fetched are:
e2 Deepak 6500 Analyst
e3 Devesh 7000 Manager
e5 Shantnu 8000 Manager

CUST_NO CUST_NAME DOB

---------- -------------------------------------------------- ---------
ACTION_TIME
---------------------------------------------------------------------------
c3 Shantanu 30-SEP-72
11-NOV-24 04.28.39.509049 AM

CUST_NO CUST_NAME DOB

---------- -------------------------------------------------- ---------
ACTION_TIME
---------------------------------------------------------------------------
c3 Shantanu 30-SEP-72
11-NOV-24 04.28.39.509049 AM

c2 Shivani 27-DEC-92
11-NOV-24 04.28.39.544777 AM

CUST_NO CUST_NAME DOB

---------- -------------------------------------------------- ---------
ACTION_TIME
---------------------------------------------------------------------------
c3 Shantanu 30-SEP-72
11-NOV-24 04.28.39.509049 AM

c2 Shivani 27-DEC-92
11-NOV-24 04.28.39.544777 AM

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

EXP 6:- To implement Procedure and Fucntions using PL/SQL

PROCEDURE TO INSERT NUMBER

CREATE TABLE emp1 (
id NUMBER(3),
First_name VARCHAR2(20)
);
INSERT INTO emp1 VALUES (101, 'Nithya');
INSERT INTO emp1 VALUES (102, 'Maya');
SELECT * FROM emp1;
CREATE OR REPLACE PROCEDURE insert_num(p_num NUMBER) IS
BEGIN
INSERT INTO emp1 (id, First_name) VALUES (p_num, USER);
END insert_num;
/
EXEC insert_num(3);
SELECT * FROM emp1;

OUTPUT-
Table created. 1 row(s) inserted. 1 row(s) inserted.

Result Set 1

ID FIRST_NAME
101 Nithya
102 Maya
2 rows selected. Procedure created. Statement processed.

Result Set 2

ID FIRST_NAME
101 Nithya
102 Maya
3 APEX_PUBLIC_USER

Function to find factorial

CREATE OR REPLACE FUNCTION fact(n NUMBER)

RETURN NUMBER IS

i NUMBER(10);

f NUMBER := 1;

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

BEGIN

FOR i IN 1..n LOOP

f := f * i;

END LOOP;

RETURN f;

END;

SELECT fact(2) AS FACTORIAL FROM dual;

OUTPUT-

Function created.

Result Set 3

FACTORIAL
2

PL/SQL PROGRAM FOR BONUS CALCULATION-

SET SERVEROUTPUT ON;

DECLARE

salary NUMBER := 10000;

bonus NUMBER;

BEGIN

IF salary > 5000 THEN

bonus := salary * 0.5;

ELSE

bonus := 0;

END IF;

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

DBMS_OUTPUT.PUT_LINE('Bonus: ' || bonus);

END;

OUTPUT-

Statement processed.
Bonus: 5000

PROGRAM FOR ARMSTRONG NUMBER-

SET SERVEROUTPUT ON;

DECLARE

a NUMBER;

b NUMBER := 0;

i NUMBER := 153;

BEGIN

a := i;

WHILE a > 0 LOOP

b := b + POWER(MOD(a, 10), 3);

a := TRUNC(a / 10);

END LOOP;

IF b = i THEN

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

DBMS_OUTPUT.PUT_LINE(i || ' IS AN ARMSTRONG NUMBER');

ELSE

DBMS_OUTPUT.PUT_LINE(i || ' IS NOT AN ARMSTRONG NUMBER');

END IF;

END;

OUTPUT-

Statement processed.
153 IS AN ARMSTRONG NUMBER

PROGRAM FOR MULTIPLICATION TABLE:-

SET SERVEROUTPUT ON;

DECLARE

a NUMBER;

b NUMBER;

i NUMBER;

n NUMBER;

s NUMBER;

BEGIN

a := 1;

b := 10;

n := 5;

FOR i IN a..b LOOP

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

s := i * n;

DBMS_OUTPUT.PUT_LINE(i || '*' || n || '=' || s);

END LOOP;

END;

OUTPUT-

Statement processed.
1*5=5
2*5=10
3*5=15
4*5=20
5*5=25
6*5=30
7*5=35
8*5=40
9*5=45
10*5=50

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

DBMS LAB ASSIGNMENT 2

Query-

CREATE TABLE Employee_details (

EmpID INT PRIMARY KEY,

EmpName VARCHAR(100),

Designation VARCHAR(50),

Salary DECIMAL(10, 2)

);

INSERT INTO Employee_details (EmpID, EmpName, Designation, Salary)

VALUES

(1, 'John Doe', 'Manager', 75000),

(2, 'Jane Smith', 'Developer', 60000),

(3, 'Mike Johnson', 'Analyst', 50000);

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

DESCRIBE Employee_details;

ALTER TABLE Employee_details

ADD Contact VARCHAR(15);

ALTER TABLE Employee_details

MODIFY Designation VARCHAR(100);

DESCRIBE Employee_details;

RENAME TABLE Employee_details TO Employee_data;

-- DROP TABLE removes the table along with all its data and structure.

-- TRUNCATE TABLE removes all the data from the table but keeps the structure.

CREATE TABLE Product (

Prod_no INT PRIMARY KEY,

Prod_name VARCHAR(100),

Quantity INT,

Sell_price DECIMAL(10, 2)

);

INSERT INTO Product (Prod_no, Prod_name, Quantity, Sell_price)

VALUES

(101, 'Laptop', 10, 50000),

(102, 'Mobile', 25, 20000),

(103, 'Tablet', 15, 30000);

ALTER TABLE Product

ADD Product_Brand VARCHAR(50);

DESCRIBE Product;

DROP TABLE Product;

SHOW TABLES;

Nitin Kumar 2200910100115

 JSS Academy of Technical Education Noida
Department of Computer Science and Engineering

OUTPUT
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| EmpID | int | NO | PRI | NULL | |
| EmpName | varchar(100) | YES | | NULL | |
| Designation | varchar(50) | YES | | NULL | |
| Salary | decimal(10,2) | YES | | NULL | |
+-------------+---------------+------+-----+---------+-------+
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| EmpID | int | NO | PRI | NULL | |
| EmpName | varchar(100) | YES | | NULL | |
| Designation | varchar(100) | YES | | NULL | |
| Salary | decimal(10,2) | YES | | NULL | |
| Contact | varchar(15) | YES | | NULL | |
+-------------+---------------+------+-----+---------+-------+
+---------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------+------+-----+---------+-------+
| Prod_no | int | NO | PRI | NULL | |
| Prod_name | varchar(100) | YES | | NULL | |
| Quantity | int | YES | | NULL | |
| Sell_price | decimal(10,2) | YES | | NULL | |
| Product_Brand | varchar(50) | YES | | NULL | |
+---------------+---------------+------+-----+---------+-------+
+----------------------------------+
| Tables_in_sandbox_db |
+----------------------------------+
| employee_data |
+----------------------------------+

Nitin Kumar 2200910100115