Notes

login as: 0187cs171050
[email protected]'s password:
Last login: Thu Nov 7 11:26:19 2019 from 172.18.158.158
[0187cs171050@SISTecGN ~]$ rlwrap sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Thu Nov 7 11:30:11 2019

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Enter user-name: cs171050
Enter password:
Last Successful login time: Thu Nov 07 2019 11:27:32 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Produ
With the Partitioning, OLAP, Advanced Analytics and Real Application Teions

SQL> create table Employee(Employee_ID NUMBER(20) primary key,Employee_Name VARCHAR(30),Employee_Dept VARCHAR(4),Employee_Salary NUMBER(10));

Table created.

SQL> desc Employee
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(20)
EMPLOYEE_NAME VARCHAR2(30)
EMPLOYEE_DEPT VARCHAR2(4)
EMPLOYEE_SALARY NUMBER(10)

SQL> insert into Employee(Employee_ID,Employee_Name,Employee_Dept,Employee_Salary) values(1,'Chirag','CS',45000);

1 row created.

SQL> insert into Employee(Employee_ID,Employee_Name,Employee_Dept,Employee_Salary) values(2,'Bilal','CE',45000);

1 row created.

SQL> insert into Employee(Employee_ID,Employee_Name,Employee_Dept,Employee_Salary) values(3,'Harsh','ME',450);

1 row created.

SQL> insert into Employee(Employee_ID,Employee_Name,Employee_Dept,Employee_Salary) values(4,'Jatin','EC',50);

1 row created.

SQL> desc Employee
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(20)
EMPLOYEE_NAME VARCHAR2(30)
EMPLOYEE_DEPT VARCHAR2(4)
EMPLOYEE_SALARY NUMBER(10)

SQL> select * from Employee
2 ;

EMPLOYEE_ID EMPLOYEE_NAME EMPL EMPLOYEE_SALARY
----------- ------------------------------ ---- ---------------
1 Chirag CS 45000
2 Bilal CE 45000
3 Harsh ME 450
4 Jatin EC 50



SQL> select * from Employee where Employee_dept='CS';

EMPLOYEE_ID EMPLOYEE_NAME EMPL EMPLOYEE_SALARY
----------- ------------------------------ ---- ---------------
1 Chirag CS 45000



SQL> select * from Employee where Employee_Name LIKE 'C%';

EMPLOYEE_ID EMPLOYEE_NAME EMPL EMPLOYEE_SALARY
----------- ------------------------------ ---- ---------------
1 Chirag CS 45000
SQL> SELECT AVG(Employee_salary), COUNT(*) from Employee;

AVG(EMPLOYEE_SALARY) COUNT(*)
-------------------- ----------
22625 4

SQL>

SQL> select * from Employee where Employee_Name LIKE 'A%';

no rows selected


SQl>SELECT Employee_name, MAX(Employee_salary) AS salary
2 FROM employee
3 WHERE Employee_salary < (SELECT MAX(Employee_salary)
4 FROM Employee);

EMPLOYEE_NAME EMPLOYEE_SALARY
--------------------------------------------
Harsh 450
SQL> SELECT employee_dept, COUNT(*)

4

#JOIN


SELEcT * from student NATURAL JOIN result
SELEcT * from student LEFT OUTER JOIN result
SELEcT * from student RIGHT JOIN result
SELEcT * from student FULL JOIN result


#question 3
A database trigger is special stored procedure
that is run when specific actions occur within a database.
Most triggers are defined to run when changes are made to
a table’s data. Triggers can be defined to run instead of
or after DML (Data Manipulation Language) actions such as
INSERT, UPDATE, and DELETE.

Triggers help the database designer ensure certain actions
,such as maintaining an audit file, are completed
regardless of which program or user makes changes
to the data.

The programs are called triggers since an event
, such as adding a record to a table, fires their
execution.