Scribd
Upload
38 views5 pages

As 5

The document provides SQL commands for creating and manipulating an EMP table, including inserting employee data and querying it based on various conditions. It outlines the structure of the EMP table with fields such as Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, and Deptno. Additionally, it includes several SELECT statements to retrieve specific employee information based on different criteria.

Uploaded by

refet44052
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
38 views5 pages

As 5

The document provides SQL commands for creating and manipulating an EMP table, including inserting employee data and querying it based on various conditions. It outlines the structure of the EMP table with fields such as Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, and Deptno. Additionally, it includes several SELECT statements to retrieve specific employee information based on different criteria.

Uploaded by

refet44052
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/ 5

1. Create EMP table, insert the values and show the full table as given below.

Empno Ename Job Mgr Hiredate Sal Comm Deptno


7369 Smith Clerk 7902 1980-12-17 800.00 NULL 20
7499 Allen Salesman 7698 1981-02-20 1600.00 NULL 30
7521 Ward Salesman 7698 1981-02-22 1250.00 3000.00 30
7566 Jones Manager 7839 1981-04-02 2975.00 500.00 20
7654 Marin Salesman 7698 1981-09-28 1250.00 NULL 20
7698 Blake Manager 7839 1981-05-01 2850.00 1400.00 30
7782 Clark Manager 7839 1981-06-09 2450.00 NULL 10
7788 Scott Analyst 7566 1982-12-09 3000.00 NULL 20
7839 King President NULL 1981-11-17 5000.00 NULL 10
7844 Turner Salesman 7698 1981-09-08 1500.00 0.000 30
7876 Adams Clerk 7788 1983-01-12 1100.00 NULL 20
7900 James Clerk 7698 1981-12-03 950.00 NULL 30
7902 Ford Analyst 7566 1981-12-04 3000.00 NULL 20
7934 Miller Clerk 7782 1982-01-23 1300.00 NULL 10

Access and Display Data

Use the EMP table to complete the following exercises:


CREATE TABLE EMP (
Empno INT,
Ename VARCHAR(50),
Job VARCHAR(50),
Mgr INT,
Hiredate DATE,
Sal DECIMAL(10,2),
Comm DECIMAL(10,2),
Deptno INT
);

Output:
-- INSERT INTO EMP (Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno)
VALUES
Output:

-- Show the full EMP table

SELECT * FROM EMP;


Output:

2. Select emp_name, job, sal and dept.no from employee;

SELECT Ename, Job, Sal, Deptno

FROM EMP;
Output:
3. Select emp_name as employee from employee where sal > 2000;
SELECT Ename AS Employee
FROM EMP
WHERE Sal > 2000;
Output:

4. Select * from employee where NOT job = “salesman” and NOT job = “ clerk” order by
emp_name;
SELECT Ename
FROM EMP
WHERE Job NOT IN ('Clerk', 'Salesman')
ORDER BY Ename;
Output:
5. Select emp_name from employee where emp_name like “%s%”;
SELECT Ename
FROM EMP
WHERE Ename LIKE '%S%';
Output:

6. Select emp_name as name , sal as salary from employee where comm>sal;


SELECT Ename AS Name, Sal AS Salary
FROM EMP
WHERE Comm > Sal;
Output:

7. Select * from employee where hiredate like “1993%”;


SELECT Ename
FROM EMP
WHERE YEAR(Hiredate) = 1993;
Output:
8. Select emp_name, job from employee where dept_no = 10 or dept_no = 20 ORDER BY
emp_name;
SELECT Ename, Job
FROM EMP
WHERE Deptno IN (10, 20)
ORDER BY Ename;
Output:

9. Select emp_name, job, sal, comm from employee where job = “Manager”;
SELECT Ename, job, sal
FROM Ename
WHERE job = “Manager”;
Output:

10. Select emp_name, sal, comm FROM employee where job = “salesman” AND sal> comm
ORDER BY Sal . DESC;

Select Ename, sal, comm FROM EMP where job = “salesman” AND sal> comm

ORDER BY Sal DESC;

Output:

You might also like