Ex 1:
To create table Employee using SQL command to store the details of employees such as EMPNO, NAME, DESIGNATION, DEPARTMENT, SEX and SALARY. Specify primary key and NOT NULL constraints on the table and allow only ‘M’ or ‘F’ for the column SEX.
-------------------------------------------------------------------------------------------------------
Create table Employee
SQL> CREATE TABLE EMPLOYEE(
EMPNO NUMBER(5) PRIMARY KEY,
NAME VARCHAR2(15) NOT NULL,
DESIGNATION VARCHAR2(20) NOT NULL,
DEPARTMENT VARCHAR2(20) NOT NULL,
SEX CHAR(1) NOT NULL,
SALARY NUMBER(7,2) NOT NULL,
CHECK(SEX IN('M','F')));
Table created.
Display the structure of a table:
SQL> DESCRIBE EMPLOYEE;
Name Null? Type
----------------------------------------------------------
EMPNO NOT NULL NUMBER(5)
NAME NOT NULL VARCHAR2(15)
DESIGNATION NOT NULL VARCHAR2(20)
DEPARTMENT NOT NULL VARCHAR2(20)
SEX NOT NULL CHAR(1)
SALARY NOT NULL NUMBER(7,2)
Insert values into table
SQL> INSERT INTO EMPLOYEE VALUES(&EMPNO,’&NAME’,’&DESIGNATION’,’&DEPARTMENT’,’&SEX’,&SALARY);
10 rows created.
1. Query to display information about all Employees:
SQL> SELECT * FROM EMPLOYEE;
EMPNO NAME DESIGNATION DEPARTMENT S SALARY
-----------------------------------------------------------------------------------
101 DEON MANAGER SALES M 25000
102 HARISH ADMIN COMPUTER M 11000
103 MAYUR CLERCK ACCOUNT F 9500
104 LIKHITH MANAG SALES F 12500
105 PAVAN DIRECTOR MOVIE M 15000
106 SHAN ADMIN COMPUTER M 12000
107 RAGINI DIRECTOR MOVIE F 11000
108 RAMESH PRODUCER MOVIE M 19000
109 AIYAPPA CLEANER OFFICE M 7500
110 DEEPAK CLERCK ACCOUNT F 11000
10 rows selected.
2. Query to display EMPNO, NAME, and DESIGNATION of all employees:
SQL> SELECT EMPNO, NAME, DESIGNATION FROM EMPLOYEE;
EMPNO NAME DESIGNATION
---------------------------------------------------
101 DEON MANAGER
102 HARISH ADMIN
103 MAYUR CLERCK
104 LIKHITH MANAGER
105 PAVAN DIRECTOR
106 SHAN ADMIN
107 RAGINI DIRECTOR
108 RAMESH PRODUCER
109 AIYAPPA CLEANER
110 DEEPAK CLERCK
10 rows selected.
3. Display the details of all females.
SQL> SELECT * FROM EMPLOYEE
WHERE SEX='F';
EMPNO NAME DESIGNATION DEPARTMENT S SALARY
------------------------------------------------------------------------------------------------------
103 MAYUR CLERCK ACCOUNT F 9500
104 LIKHITH MANAGER SALES F 12500
107 RAGINI DIRECTOR MOVIE F 11000
110 DEEPAK CLERCK ACCOUNT F 11000
4. List all the different departments.
SQL> SELECT DISTINCT DEPARTMENT FROM EMPLOYEE;
DEPARTMENT
-------------------
ACCOUNT
COMPUTER
MOVIE
OFFICE
SALES
5. List EMPNO, NAME and DESIGNATION of all employees whose salary is more than 15000.
SQL> SELECT EMPNO, NAME, DESIGNATION
FROM EMPLOYEE
WHERE SALARY>=15000;
EMPNO NAME DESIGNATION
----------------------------------------------
101 DEON MANAGER
105 PAVAN DIRECTOR
108 RAMESH PRODUCER
6. Display the name of the employees who get the maximum basic pay.
SQL> SELECT NAME FROM EMPLOYEE
WHERE SALARY = (SELECT MAX (SALARY) FROM EMPLOYEE);
NAME
--------
DEON
7. Display the number of employees in marketing and sales department.
SQL> SELECT COUNT (*) "NO. OF EMPLOYEES"
FROM EMPLOYEE WHERE DEPARTMENT='SALES'
OR DEPARTMENT='MARKETING';
NO. OF EMPLOYEES
-------------------------
2
8. Display the average maximum, minimum salary of each department.
SQL> SELECT DEPARTMENT,AVG(SALARY),MAX(SALARY),
MIN(SALARY)
FROM EMPLOYEE
GROUP BY DEPARTMENT;
DEPARTMENT AVG(SALARY) MAX(SALARY) MIN(SALARY)
--------------------------------------------------------------------------------------------
ACCOUNT 10250 11000 9500
COMPUTER 11500 12000 11000
MOVIE 15000 19000 11000
OFFICE 7500 7500 7500
SALES 18750 25000 12500
9. List the department which has more than two employees.
SQL> SELECT DEPARTMENT,COUNT(*)"NO. OF EMPLOYEES"
FROM EMPLOYEE
GROUP BY DEPARTMENT
HAVING COUNT(*)>2;
DEPARTMENT NO. OF EMPLOYEES
----------------------------------------------------
MOVIE 3
No comments:
Post a Comment