Dbms Queries

Uploaded by

guptanitesh7276

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

0% found this document useful (0 votes)

9 views

Dbms Queries

Uploaded by

guptanitesh7276

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/ 3

Write sql query for following consider table

EMP(empno,deptno,ename,salary,designation,joiningdate,DOB,city).
1) insert one row into the table
___ INSERT INTO EMP VALUES (1, 10, 'John', 1000, 'Manager', '2020-08-01', '1996-05-20',
'New York');

2) save the data

___COMMIT;

3) undo the insertion of second row

____ROLLBACK;

4) insert two rowsinto the table

____ INSERT INTO EMP VALUES (2, 10, 'Mark', 2000, 'Manager', '2020-09-01', '1995-04-
19', 'New York'), (3, 10, 'Max', 1500, 'Coordinator', '2020-10-01', '1992-03-18', 'New York');

5) create savepoint s1.

____ SAVEPOINT s1;

6) insert one row into the table

___ INSERT INTO EMP VALUES (4, 10, 'Lilly', 1300, 'Analyst', '2020-11-01', '1991-02-17',
'New York');

7) undo upto savepoint s1.

____ ROLLBACK TO SAVEPOINT s1;

Write sql query for following consider table

EMP(empno,deptno,ename,salary,designation,joiningdate,DOB,city)
1) display employees name and number in an increasing order of salary.
____SELECT empno,ename,salary FROM EMP ORDER BY salary ASC;
2) display employees name and employee number dept wise.
____ SELECT empno,ename,deptno FROM EMP ORDER BY deptno;
3)display total salary of all employees.
___ SELECT SUM(salary) FROM EMP;

3) display number of employees deptwise.

____ SELECT deptno,COUNT(empno) FROM EMP GROUP BY deptno;

4) display employee name having experience more than 3 years.

___SELECT ename FROM EMP WHERE DATEDIFF(CURDATE(),joiningdate) > 1095;

5) display employee name starting with "S" and working in deptno 1002.
_____ SELECT ename FROM EMP WHERE ename LIKE "S%" AND deptno=1002;

Write sql query for following consider table

EMP(empno,deptno,ename,salary,designation,joiningdate,DOB,city).
1) display employee name having experience more than 10 years.
____SELECT ename FROM EMP WHERE YEAR(CURDATE()) - YEAR(joiningdate) > 10;

2) display age of employees

_____SELECT FLOOR(DATEDIFF(CURDATE(),DOB)/365.25) AS Age FROM EMP;

3) display average salary of all employee.

______ SELECT AVG(salary) FROM EMP;
4) display name of employee who earned highest salary.
____ SELECT ename FROM EMP WHERE salary = (SELECT MAX(salary) FROM EMP);

Write sql query for following consider table

1) create table EMP(empno,deptname,ename,salary,designation,joining_date).
____ CREATE TABLE EMP(empno INT, deptname VARCHAR(50), ename
VARCHAR(50), salary INT, designation VARCHAR(50), joining_date DATE);
2) display names of employee whose name start with alphabet "A".
______ SELECT ename FROM EMP WHERE ename LIKE 'A%'
3) display names of employee who joined before '1/1/2000'.
_______ SELECT ename FROM EMP WHERE joining_date < '2000-01-01';