Q.
Perform the following:
1. Creating Tables (With and Without Constraints) 5.
2. Inserting/Updating/Deleting Records in a Table
3. displaying attributes
1. Creating A DEPARTMENT Tables (With and Without Constraints)
CREATE TABLE DEPARTMENT
(DNO VARCHAR2 (20) PRIMARY KEY,
DNAME VARCHAR2 (20),
MGRSTARTDATE DATE);
2. Creating A DEPARTMENT Tables (With and Without Constraints)
CREATE TABLE EMPLOYEE
(SSN VARCHAR2 (20) PRIMARY KEY,
FNAME VARCHAR2 (20),
LNAME VARCHAR2 (20),
ADDRESS VARCHAR2 (20),
SEX CHAR (1),
SALARY INTEGER,
SUPERSSN REFERENCES EMPLOYEE (SSN),
DNO REFERENCES DEPARTMENT (DNO));
3. NOTE: Once DEPARTMENT and EMPLOYEE tables are created we must alter department
table to add foreign constraint MGRSSN using sql command
ALTER TABLE DEPARTMENT ADD MGRSSN REFERENCES EMPLOYEE (SSN);
4. Inserting/Updating/Deleting Records in a EMPLOYEE Table,
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSECE01‘,‘JOHN‘,‘SCOTT‘,‘BANGALORE‘,‘M‘, 450000); INSERT INTO EMPLOYEE (SSN,
FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSCSE01‘,‘JAMES‘,‘SMITH‘,‘BANGALORE‘,‘M‘, 500000); INSERT INTO EMPLOYEE (SSN,
FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSCSE02‘,‘HEARN‘,‘BAKER‘,‘BANGALORE‘,‘M‘, 700000); INSERT INTO EMPLOYEE (SSN,
FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSCSE03‘,‘EDWARD‘,‘SCOTT‘,‘MYSORE‘,‘M‘, 500000); INSERT INTO EMPLOYEE (SSN,
FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSCSE04‘,‘PAVAN‘,‘HEGDE‘,‘MANGALORE‘,‘M‘, 650000); INSERT INTO EMPLOYEE (SSN,
FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSCSE05‘,‘GIRISH‘,‘MALYA‘,‘MYSORE‘,‘M‘, 450000); INSERT INTO EMPLOYEE (SSN,
FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES (‗RNSCSE06‘,‘NEHA‘,‘SN‘,‘BANGALORE‘,‘F‘,
800000); INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSACC01‘,‘AHANA‘,‘K‘,‘MANGALORE‘,‘F‘, 350000); INSERT INTO EMPLOYEE (SSN, FNAME,
LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSACC02‘,‘SANTHOSH‘,‘KUMAR‘,‘MANGALORE‘,‘M‘, 300000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSISE01‘,‘VEENA‘,‘M‘,‘MYSORE‘,‘M‘, 600000);
INSERT INTO EMPLOYEE (SSN, FNAME, LNAME, ADDRESS, SEX, SALARY) VALUES
(‗RNSIT01‘,‘NAGESH‘,‘HR‘,‘BANGALORE‘,‘M‘, 500000);
5. Inserting/Updating/Deleting Records in DEPARTMENT Table,
INSERT INTO DEPARTMENT VALUES (‗1‘,‘ACCOUNTS‘,‘01-JAN01‘,‘RNSACC02‘); INSERT INTO
DEPARTMENT VALUES (‗2‘,‘IT‘,‘01-AUG-16‘,‘RNSIT01‘);
INSERT INTO DEPARTMENT VALUES (‗3‘,‘ECE‘,‘01-JUN-08‘,‘RNSECE01‘); INSERT INTO
DEPARTMENT VALUES (‗4‘,‘ISE‘,‘01-AUG-15‘,‘RNSISE01‘); INSERT INTO DEPARTMENT
VALUES (‗5‘,‘CSE‘,‘01-JUN-02‘,‘RNSCSE05‘);
6. Update
UPDATE EMPLOYEE SET DNO=‘5‘, SUPERSSN=‘RNSCSE06‘ WHERE SSN=‘RNSCSE05‘;
7. Delete entries of employee table where DNO =1;
DELETE FROM EMPLOYEE WHERE DNO=1;
8. Display only few attributes of DEPARTMENT and EMPLOYEE
select fname, ADDRESS from EMPLOYEE;
select dno,dname from DEPARTMENT;
9. Display details of employee whose name is AMIT and salary greater than 50000;
select * from employee where emp_name='Amit' and salary>50000;
Q. 2 Perform the following:
1. Rename the table dept as department
2. Add a new column PINCODE with not null constraints to the existing table DEPT
3. All constraints and views that reference the column are dropped automatically, along with the
column.
4. Rename the column DNAME to DEPT_NAME in dept table
5. Change the data type of column loc as CHAR with size 10
Create Table
CREATE TABLE DEPT(DEPTNO INTEGER, DNAME VARCHAR(10),LOC VARCHAR(4), PRIMARY
KEY(DEPTNO));
1. Rename the table dept as department
ALTER TABLE DEPT RENAME TO DEPARTMENT;
2. Add a new column PINCODE with not null constraints to the existing table DEPT SQL> ALTER
TABLE DEPARTMENT ADD(PINCODE NUMBER(6) NOT NULL);
3. All constraints and views that reference the column are dropped automatically, along with
the column.
ALTER TABLE DEPARTMENT DROP column LOC CASCADE CONSTRAINTS; Table altered.
SQL> desc dept Name Null? Type ----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(38) DNAME VARCHAR2(10) PINCODE NOT NULL NUMBER(6)
4. Rename the column DNAME to DEPT_NAME in dept table
ALTER TABLE DEPT RENAME COLUMN DNAME TO DEPT_NAME ;
5. Change the datatype of colunm loc as CHAR with size 10
ALTER TABLE DEPARTMENT MODIFY LOC CHAR(10) ;
6. Delete table
DROP TABLE DEPARTMENT;
Q. 3 Perform the following
1. Display all the fields of employee table
2. Retrieve employee number and their salary
3. Retrieve average salary of all employee
4. Retrieve number of employee 5. Retrieve distinct number of employee
6. Retrieve total salary of employee group by employee name and count similar names
7. Retrieve total salary of employee which is greater than >120000
8. Display name of employee in descending order
9. Display details of employee whose name is AMIT and salary greater than 50000;
1. Display all the fields of employee table
select * from employee;
2. Retrieve employee number and their salary
select empno, salary from employee;
3. Retrieve average salary of all employee
select avg(salary) from employee;
4. Retrieve number of employee
select count(*) from employee;
5. Retrieve distinct number of employee
select count(DISTINCT emp_name) from employee;
6. Retrieve total salary of employee group by employee name and count similar names
SELECT EMP_NAME, SUM(SALARY),COUNT(*) FROM EMPLOYEE 2 GROUP BY(EMP_NAME);
7. Retrieve total salary of employee which is greater than >120000
SELECT EMP_NAME, SUM(SALARY) FROM EMPLOYEE 2 GROUP BY(EMP_NAME) 3 HAVING
SUM(SALARY)>120000;
8. Display name of employee in descending order
select emp_name from employee 2 order by emp_name desc;
9. Display details of employee whose name is AMIT and salary greater than 50000;
select * from employee 2 where emp_name='Amit' and salary>50000;
