[go: up one dir, main page]
Scribd
Upload
24 views6 pages

MYSQL PRACTICAL FILE ASSIGNMENT

.vh

Uploaded by

hyeah1366
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
24 views6 pages

MYSQL PRACTICAL FILE ASSIGNMENT

.vh

Uploaded by

hyeah1366
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 6

MYSQL

1. (a) Create a table Department with the following fields, datatypes and
constraints:-
• Deptno – tinyint – primary key
• Dname – varchar(12) – not null
• Phone – bigint

SQL Command
create table department (deptno tinyint primary key, dname varchar(12) not null,
phone bigint);
OUTPUT

(b) Create another table Employee with the following fields, datatypes and
constraints:-
• Empno – int – unique
• Ename – varchar(15) – not null
• Sex – char(1)
• Job – varchar(10)
• Hiredate – date
• Salary – float
• Deptno – tinyint – foreign key
SQL Command
create table employee
(empno int unique, deptno tinyint, ename varchar(15) not null, sex char(1), job
varchar(10), hiredate date, salary float, foreign key(deptno) references
department(deptno));
OUTPUT

2. Insert atleast 5 records in Department table and 10 records in Employee table similar
as shown below: Table : Department
Deptno Dname Phone
101 Finance 9675432312
102 Sales 9008888112
103 Advertise 7005464565
104 Law 8811998876
105 Production 9008751111
106 Admin 7806000121

SQL Command
insert into department values (101,'Finance',9675432353), (102,'Sales',9008888112),
(103,'Advertise',7005464565), (104,'Law',8811998876),
(105,'Production',900875111), (106,'Admin',780600121);
OUTPUT

Table : Employee
Empno Deptno Ename Sex Job Hiredate Salary
1 105 Smith Pal M Manager 10-05-1980 1200000
2 104 George Fred M Assistant 07-12-1999 1000000
3 102 Aanya Seth F Assistant 02-02-2000 700000
4 102 Momin F Supervisor 21-07-1995 100000
Banerjee
5 103 Rohit Mathew M Manager 05-04-2001 900000
6 106 Bina Joseph F Advisor 11-07-2000 1500000
7 104 Reeya Vidyarthi F Advisor 06-06-2000 1200000

8 101 Abinash M Manager 28-02-2000 1100000


Prakash
9 105 Prasoon Rajeel M Assistant 15-10-1999 1000000
10 101 Gagan Saket M Supervisor 13-11-2001 800000

SQL Command
insert into employee
values (1,105,'Smith Pal','M','Manager','1980-10-05',1200000),
(2,104,'George Fred','M','Assistant','1999-12-07',1000000),
(3,102, 'Aanya Seth', 'F', 'Assistant', '2000-02-02',700000),
(4,102, 'Momin Banerjee', 'F', 'Supervisor', '1995-07-21',100000),
(5,103, 'Rohit Mathew', 'M', 'Manager', '2001-04-05',900000),
(6,106, 'Bina Joseph', 'F', 'Advisor', '2000-11-07',1500000),
(7,104,'Reeya Vidyarthi','F','Advisor','2000-06-06',1200000),
(8,101,'Abinash Prakash','M','Manager','2000-02-28',1100000),
(9,105,'Prasoon Rajeel','M','Assistant','1999-10-15',1000000),
(10,101,'Gagan Saket','M','Supervisor','2001-11-13',800000);
OUTPUT
3. To display details of employees whose job begins with A or who have joined after
2000.
SELECT * FROM EMPLOYEE WHERE JOB LIKE ‘A%’ OR HIREDATE >
‘2000-12-31’;
OUTPUT

4. To display details of female employees in descending order of salary.


SELECT * FROM EMPLOYEE WHERE SEX= ‘F’ ORDER BY SALARY DESC;
OUTPUT

5. To display employee name, sex and salary increased by 5% under the heading ‘New
Waived Sal’ for all employees.
SELECT ENAME, SEX, SALARY+SALARY*0.05 AS ‘NEW WAIVED SAL’
FROM EMPLOYEE;
OUTPUT
6. To display empno, ename, salary and department (dname) of those employees whose
salary lies in the range of 10-15 lac.
SELECT EMPNO, ENAME, SALARY, DNAME FROM EMPLOYEE E,
DEPARTMENT D WHERE E.DEPTNO = D.DEPTNO AND SALARY BETWEEN
1000000 AND 1500000;

OUTPUT

7. To display deptno, dname, ename and gender of male employees.


SELECT B.DEPTNO, DNAME, ENAME, SEX FROM EMPLOYEE A,
DEPARTMENT B WHERE A.DEPTNO = B.DEPTNO AND SEX= ‘M’;
OUTPUT
8. To display highest and average salary among each job.
SELECT JOB, MAX(SALARY), AVG (SALARY) FROM EMPLOYEE
GROUP BY JOB;
OUTPUT

9. To display those jobs where the number of employees are more than 1.
SELECT JOB, COUNT(*) FROM EMPLOYEE
GROUP BY JOB HAVING COUNT(*)>1;
OUTPUT

10. To delete the record of empno 4.


DELETE FROM EMPLOYEE WHERE EMPNO=4;
OUTPUT

You might also like