CREATE table student (
stud_id INT,
name varchar(20),
Primary key(stud_id)
);
select * from Student;
insert into student values(1,'A
livesql.oracle.com
CREATE TABLE Employee (
FName VARCHAR(12),
Minit VARCHAR(12),
LName VARCHAR(12),
SSN char(9),
Super_ssn char(9),
Bdate char(10),
Salary numeric(7,2),
PRIMARY KEY (SSN));
CREATE TABLE Employee (
FName VARCHAR(12),
Minit VARCHAR(12),
LName VARCHAR(12),
SSN char(9) NOT NULL,
Super_ssn char(9),
Bdate char(10),
Salary numeric(7,2),
PRIMARY KEY (SSN));
alter table Employee add address varchar(40);
alter table Employee drop column address;
alter table Employee modify FName varchar(15);
CREATE TABLE DEPT(
DNAME VARCHAR(10) NOT NULL,
DNUMBER INTEGER NOT NULL,
MGRSSN CHAR(9),
MGRSTARTDATE CHAR(9),
PRIMARY KEY (DNUMBER),
UNIQUE (DNAME),
CONSTRAINT fk_dept
FOREIGN KEY (MGRSSN)
REFERENCES Employee(SSN));
INSERT INTO Employee
VALUES ('Richard','K','Marini', '653298653','653298653', '30-DEC-52',37000);
INSERT INTO Employee
VALUES ('Rich','K','Mani', '653668653','653298653', '29-DEC-54',35000);
INSERT INTO Employee
VALUES ('Manu','K','Mkini', '123456789','653298653', '3-FEB-52',30000);
select * from Employee;
delete from Employee where salary=35000;
UPDATE Employee
SET FName = 'vivan'
WHERE SSN = 123456789;
UPDATE Employee
SET Salary = SALARY *2
WHERE SSN=123456789;
set operation
CREATE TABLE first(
NAME CHAR(15) NOT NULL);
CREATE TABLE second(
D INTEGER NOT NULL,
NAME CHAR(15) NOT NULL);
INSERT INTO first
VALUES (1,'abhi');
INSERT INTO first
VALUES (2,'sid');
select * from first;
INSERT INTO second
VALUES (2,'sid');
INSERT INTO second
VALUES (3,'manu');
select * from second;
SELECT * FROM first UNION SELECT * FROM second; CREATE TABLE first(
D INTEGER NOT NULL,
NAME CHAR(15) NOT NULL;
Aggregate fuction
SELECT APPROX_COUNT_DISTINCT(SSN) AS Approx_Distinct_SSN
FROM Employee;
SELECT COUNT(*)
FROM Employee;
CREATE TABLE Employee (
FName VARCHAR(12),
Minit VARCHAR(12),
LName VARCHAR(12),
SSN char(9),
Super_ssn char(9),
Bdate char(10),
address char(15),
Salary numeric(7,2),
PRIMARY KEY (SSN));
Drop table Employee;
INSERT INTO Employee
VALUES ('Richard','K','Marini', '653298653','653298653', '30-
DEC-52','delhi',37000);
INSERT INTO Employee
VALUES ('Rich','K','Mani', '653668653','653298653', '29-DEC-
54','mumbai',35000);
INSERT INTO Employee
VALUES ('Manu','K','Mkini', '123456789','653298653', '3-FEB-
52','Delhi',30000);
INSERT INTO Employee
VALUES ('Amir','K','Kaha', '622298653','653298653', '3-DEC-
50','goa',37000);
INSERT INTO Employee
VALUES ('sheetal','K','iyer', '653683653','653298653', '29-mar-
54','Ahmdabad',35000);
INSERT INTO Employee
VALUES ('shivani','p','pushkar', '123456700','653298653', '3-
june-52','Delhi',30000);
select * from Employee;
select * from Employee where address='mumbai';
CREATE TABLE Employee (
Emp_id INTEGER NOT NULL,
Emp_name VARCHAR(12),
Job_name VARCHAR(12),
Manager_id INTEGER,
Hiredate char(10),
Salary numeric(7,2),
commision numeric(7,2),
dep_id INTEGER,
CONSTRAINT fk_dept
FOREIGN KEY (dep_id)
REFERENCES DEPT(dep_id));
FName VARCHAR(12),
Minit VARCHAR(12),
LName VARCHAR(12),
SSN char(9) NOT NULL,
Super_ssn char(9),
Bdate char(10),
Salary numeric(7,2),
PRIMARY KEY (SSN));
alter table Employee add address varchar(40);
alter table Employee drop column address;
alter table Employee modify FName varchar(15);
CREATE TABLE DEPT(
dep_name VARCHAR(10) NOT NULL,
dep_id INTEGER NOT NULL,
dep_location VARCHAR(10));
Simple queries
CREATE TABLE DEPT(
dep_name VARCHAR(10) NOT NULL,
dep_id INTEGER NOT NULL,
dep_location VARCHAR(10),
PRIMARY KEY (dep_id));
Drop table DEPT;
CREATE TABLE Employee (
Emp_id INTEGER NOT NULL,
Emp_name VARCHAR(12),
Job_name VARCHAR(12),
Manager_id INTEGER,
Hiredate char(10),
Salary numeric(7,2),
commision numeric(7,2),
dep_id INTEGER,
CONSTRAINT fk_dept
FOREIGN KEY (dep_id)
REFERENCES DEPT(dep_id));
CREATE TABLE sal_grade(
grade INTEGER NOT NULL,
min_salary INTEGER NOT NULL,
max_salary INTEGER NOT NULL,
INSERT INTO Sal_grade
VALUES ('1','800','1300');
INSERT INTO Sal_grade
VALUES ('2','1301','1500');
INSERT INTO Sal_grade
VALUES ('3','1501','2100');
INSERT INTO Sal_grade
VALUES ('4','2101','3100');
INSERT INTO Sal_grade
VALUES ('5','3101','999');
INSERT INTO Employee
VALUES ('68319','Kayling','president', '68319','1991-11-18',
6000,400,'1001');
INSERT INTO Employee
VALUES ('66928','blaze','manager', '68319','1991-05-01',
2750,NULL,'3001');
INSERT INTO Employee
VALUES ('67832','clare','manager', '68319','1991-06-09',
2550,NULL,'1001');
INSERT INTO Employee
VALUES ('65646','jonas','manager', '68319','1991-04-02',
2957,NULL,'2001');
INSERT INTO Employee
VALUES ('67858','scarlet','analysts', '65646','1997-04-19',
3100,NULL,'2001');
INSERT INTO Employee
VALUES ('69062','frank','analyst', '65646','1991-12-03',
3100,NULL,'2001');
INSERT INTO Employee
VALUES ('63679','sandrine','clerk', '69062','1990-12-18',
900,NULL,'2001');
INSERT INTO Employee
VALUES ('64989','adelyn','salesman', '66928','1991-02-20',
1700,400,'3001');
INSERT INTO Employee
VALUES ('65271','wade','salesman', '66928','1991-02-22',
1350,500,'3001');
INSERT INTO Employee
VALUES ('66564','madden','salesman', '66928','1991-09-28',
1350,1500,'3001');
INSERT INTO Employee
VALUES ('68454','tucker','salesman', '66928','1991-09-08',
1600,0,'3001');
INSERT INTO Employee
VALUES ('68736','adnres','clerk', '67858','1997-05-23',
1200,NULL,'2001');
INSERT INTO Employee
VALUES ('69324','marker','clerk', '67832','1992-01-23',
1400,NULL,'1001');
select * from Employee;
INSERT INTO DEPT
VALUES ('FINANCE','1001','sydney');
INSERT INTO DEPT
VALUES ('audit','2001','melbourne');
INSERT INTO DEPT
VALUES ('marketing','3001','perth');
INSERT INTO DEPT
VALUES ('production','4001','brisbame');
select * from DEPT;
https://www.w3resource.com/sql-exercises/employee-database-
exercise/index.php?passed=passed
group by and having
Write a query in SQL to list the no. of employees and average
salary within each department for each job name.
SELECT count(*),
avg(salary),
dep_id,
job_name
FROM employees
GROUP BY dep_id,
job_name;
Write a query in SQL to check whether all the employees
numbers are indeed unique.
SELECT emp_id,
count(*)
FROM employees
GROUP BY emp_id;
Write a query in SQL to list the name of departments where
atleast 2 employees are working in that department.
SELECT d.dep_name,
count(*)
FROM employees e,
department d
WHERE e.dep_id = d.dep_id
GROUP BY d.dep_name
HAVING count(*) >= 2;
Write a query in SQL to list the no. of employees in each
department where the no. is less than 4.
SELECT dep_id,
count(*)
FROM employees
GROUP BY dep_id
HAVING count(*)<4;
Write a query in SQL to list the department where at least two
employees are working.
SELECT dep_id,
count(*)
FROM employees
GROUP BY dep_id
HAVING count(*) >= 2;
Write a query in SQL to display the number of employee for
each job in each department.
SELECT dep_id,
job_name,
count(*)
FROM employees
GROUP BY dep_id,
job_name;
Write a query in SQL to list the manager no and the number of
employees working for those managers in ascending order on
manager id.
SELECT w.manager_id,
count(*)
FROM employees w,
employees m
WHERE w.manager_id = m.emp_id
GROUP BY w.manager_id
ORDER BY w.manager_id ASC;
Write a query in SQL to list the employee id, name, location,
department of all the departments 1001 and 2001.
SELECT e.emp_id,
e.emp_name,
e.dep_id,
d.dep_location,
d.dep_name
FROM employees e,
department d
WHERE e.dep_id = d.dep_id
AND e.dep_id IN (1001,
2001);
Write a query in SQL to list the employee id, name, department
id, location of all the employees.
SELECT e.emp_id,
e.emp_name,
e.dep_id,
d.dep_location
FROM employees e,
department d
WHERE e.dep_id = d.dep_id ;
Write a query in SQL to find the average salary and average
total remuneration(salary and commission) for each type of
job.
SELECT job_name,
avg(salary),
avg(salary+commission)
FROM employees
GROUP BY job_name;
Write a query in SQL to find the highest salary from all the
employees.
SELECT max(salary)
FROM employees;
Write a query in SQL to list the employee id, name, salary, and
department id of the employees in ascending order of salary
who works in the department 1001.
SELECT e.emp_id,
e.emp_name,
e.salary,
e.dep_id
FROM employees E
WHERE e.dep_id = 1001
ORDER BY e.salary ASC;
Write a query in SQL to list the employees who are senior to
their own MANAGERS.
SELECT *
FROM employees w,
employees m
WHERE w.manager_id = m.emp_id
AND w.hire_date < m.hire_date;
Write a query in SQL to list the details of the employees along
with the details of their departments.
SELECT *
FROM employees e,
department d
WHERE e.dep_id= d.dep_id;
Write a query in SQL to list the employees in ascending order
of designations of those, joined after the second half of 1991.
Write a query in SQL to list the employees in ascending order
of the salary whose annual salary is below 25000.
SELECT *
FROM employees
WHERE (12*salary) < 25000
ORDER BY salary ASC;
Write a query in SQL to list all the employees except
PRESIDENT and MANAGER in ascending order of salaries.
SELECT *
FROM employees
WHERE job_name NOT IN ('PRESIDENT',
'MANAGER')
ORDER BY salary ASC;
Write a query in SQL to list the employees who are drawing the
salary less than 1000 and sort the output in ascending order on
salary.
SELECT *
FROM employees
WHERE salary < 1000
ORDER BY salary;
Write a query in SQL to display the location of CLARE.
SELECT dep_location
FROM department d,
employees e
WHERE e.emp_name = 'CLARE'
AND e.dep_id = d.dep_id ;
Write a query in SQL to list the employees in descending order
who are either 'CLERK' or 'ANALYST'.
SELECT *
FROM employees
WHERE job_name='CLERK'
OR job_name='ANALYST'
ORDER BY job_name DESC;
Write a query in SQL to list the details of the employees in
ascending order to the department_id and descending order to
the jobs
SELECT *
FROM employees
ORDER BY dep_id ASC,
job_name DESC;
Write a query in SQL to list the employees in the ascending
order of their salaries.
SELECT *
FROM employees
ORDER BY salary ASC;
Nested queries and complex queries
https://www.w3resource.com/sql-
exercises/employee-database-exercise/
subqueries-exercises-on-employee-
database.php?passed=passed
Write a query in SQL to display all the details of managers.
SELECT *
FROM employees
WHERE emp_id IN
(SELECT manager_id
FROM employees);
Write a query in SQL to display the employee ID, name, job
name, hire date, and experience of all the managers.
SELECT emp_id,
emp_name,
job_name,
hire_date,
age(CURRENT_DATE, hire_date) "Experience"
FROM employees
WHERE emp_id IN
(SELECT manager_id
FROM employees);
Write a query in SQL to list all the employees of grade 2 and 3.
SELECT *
FROM employees e,
salary_grade s
WHERE e.salary BETWEEN s.min_sal AND s.max_sal
AND s.grade IN (2, 3);
Write a query in SQL to display all the employees of grade 4
and 5 who are working as ANALYST or MANAGER.
SELECT *
FROM employees e,
salary_grade s
WHERE e.salary BETWEEN s.min_sal AND s.max_sal
AND s.grade IN (4,
5)
AND e.emp_id IN
(SELECT e.emp_id
FROM employees e
WHERE e.job_name IN ('MANAGER',
'ANALYST'));
Write a query in SQL to list the details of the employees whose
salary is more than the salary of JONAS.
SELECT *
FROM employees
WHERE salary >
(SELECT salary
FROM employees
WHERE emp_name = 'JONAS');
Write a query in SQL to list the employees who works in the
same designation as FRANK.
SELECT *
FROM employees
WHERE job_name =
(SELECT job_name
FROM employees
WHERE emp_name = 'FRANK');
Write a query in SQL to list the employees who are senior to
ADELYN.
SELECT *
FROM employees
WHERE hire_date <
(SELECT hire_date
FROM employees
WHERE emp_name = 'ADELYN');
Write a query in SQL to list the employees of department ID
2001 who works in the designation same as department ID
1001.
SELECT *
FROM employees e,
department d
WHERE d.dep_id = 2001
AND e.dep_id = d.dep_id
AND e.job_name IN
(SELECT e.job_name
FROM employees e,
department d
WHERE e.dep_id = d.dep_id
AND d.dep_id =1001);
Write a query in SQL to list the employees whose salary is
same as the salary of FRANK or SANDRINE. List the result in
descending order of salary.
SELECT *
FROM employees
WHERE salary IN
(SELECT salary
FROM employees e
WHERE (emp_name = 'FRANK'
OR emp_name = 'BLAZE')
AND employees.emp_id <> e.emp_id)
ORDER BY salary DESC;
Write a query in SQL to list the employees whose salary is
more than the total remuneration of the SALESMAN.
SELECT *
FROM employees
WHERE salary >
(SELECT max(salary+commission)
FROM employees
WHERE job_name = 'SALESMAN');
Write a query in SQL to list the employees whose designation
is same as the designation of SANDRINE or ADELYN.
SELECT *
FROM employees
WHERE job_name IN
(SELECT job_name
FROM employees
WHERE emp_name = 'SANDRINE'
OR emp_name = 'ADELYN');
Write a query in SQL to find the details of highest paid
employee.
SELECT *
FROM employees
WHERE salary IN
(SELECT max(salary)
FROM employees);
Write a query in SQL to find the highest paid employees in the
department MARKETING.
SELECT *
FROM employees
WHERE salary IN
(SELECT max(salary)
FROM employees
WHERE dep_id IN
(SELECT d.dep_id
FROM department d
WHERE d.dep_name = 'MARKETING'));
Write a query in SQL to find the total salary given to the
MANAGER
SELECT SUM (salary)
FROM employees
WHERE job_name = 'MANAGER';
Write a query in SQL to list the name, job name, department
name, location for those who are working as a manager.
SELECT e.emp_name,
e.job_name,
d.dep_name,
d.dep_location
FROM employees e,
department d
WHERE e.dep_id = d.dep_id
AND e.emp_id IN
(SELECT manager_id
FROM employees) ;
Write a query in SQL to list the managers whose salary is more
than the average salary of his employees.
SELECT *
FROM employees m
WHERE m.emp_id IN
(SELECT manager_id
FROM employees)
AND m.salary >
(SELECT avg(e.salary)
FROM employees e
WHERE e.manager_id = m.emp_id );
Write a query in SQL to list the managers who are not working
under the PRESIDENT.
SELECT *
FROM employees
WHERE emp_id IN
(SELECT manager_id
FROM employees)
AND manager_id NOT IN
(SELECT emp_id
FROM employees
WHERE job_name = 'PRESIDENT');
Write a query in SQL to list the name of the department where
number of employees is equal to the number of characters in
the department name.
SELECT *
FROM department d
WHERE length(dep_name) IN
(SELECT count(*)
FROM employees e
WHERE e.dep_id = d.dep_id );
Write a query in SQL to list the employees who are not
working in the department MARKETING.
SELECT *
FROM employees
WHERE dep_id NOT IN
(SELECT dep_id
FROM department
WHERE dep_name = 'MARKETING');
Write a query in SQL to list the name of the employees who are
getting the highest salary of each department.
SELECT e.emp_name,
e.dep_id
FROM employees e
WHERE e.salary IN
(SELECT max(salary)
FROM employees
GROUP BY dep_id) ;
Write a query in SQL to list the employees whose salary is
equal or more to the average of maximum and minimum salary.
SELECT *
FROM employees
WHERE salary >=
(SELECT (max(salary)+min(salary))/2
FROM employees);
Write a query in SQL to find out the least 5 earners of the
company.
SELECT *
FROM employees e
WHERE 5>
(SELECT count(*)
FROM employees
WHERE e.salary >salary);.
Write a query in SQL to list the name of the department where
number of employees is equal to the number of characters in
the department name.
SELECT *
FROM department d
WHERE length(dep_name) IN
(SELECT count(*)
FROM employees e
WHERE e.dep_id = d.dep_id );
Simple queries lab
https://artoftesting.com/sql-queries-for-
interview#Basic_SQL_Query_Interview_Questions
nested queries for lab
Write a query in SQL to find the number of employees are
performing the duty of a manager.
SELECT count(*)
FROM employees
WHERE emp_id IN
(SELECT manager_id
FROM employees);
Write a query in SQL to find the maximum average salary
drawn for each job name except for PRESIDENT.
SELECT max(myavg)
FROM
(SELECT avg(salary) myavg
FROM employees
WHERE job_name != 'PRESIDENT'
GROUP BY job_name) a;
Write a query in SQL to list the details of most recently hired
employees of department 3001.
SELECT *
FROM employees
WHERE hire_date IN
(SELECT max(hire_date)
FROM employees
WHERE dep_id = 3001) AND dep_id=3001;
Write a query in SQL to list the employees whose salary is
same as any one of the employee.
SELECT *
FROM employees
WHERE salary IN
(SELECT salary
FROM employees e
WHERE employees.emp_id <> e.emp_id);
Write a query in SQL to list the employees of department 1001
whose salary is more than the salary of ADELYN.
SELECT *
FROM employees
WHERE dep_id = 1001
AND salary >
(SELECT salary
FROM employees
WHERE emp_name = 'ADELYN');
Write a query in SQL to list the details of the employees
working at PERTH.
SELECT *
FROM employees
WHERE dep_id IN
(SELECT dep_id
FROM department
WHERE department.dep_location = 'PERTH');
Write a query in SQL to display the unique department of the
employees.
SELECT *
FROM department
WHERE dep_id IN
(SELECT DISTINCT dep_id
FROM employees);
Complex queries
Write a query in SQL to list the name, salary, and commission
for those employees whose net pay is greater than or equal to
the salary of any other employee in the company.
SELECT e.emp_name,
e.salary,
e.commission
FROM employees e
WHERE
(SELECT max(salary+commission)
FROM employees) >= ANY
(SELECT salary
FROM employees);
Write a query in SQL to list the highest paid employees
working under KAYLING.
SELECT *
FROM employees
WHERE salary IN
(SELECT max(salary)
FROM employees
WHERE manager_id IN
(SELECT emp_id
FROM employees
WHERE emp_name = 'KAYLING'));
Write a query in SQL to list the ID, name,location,salary, and
department of the all the employees belonging to the
department where KAYLING works.
SELECT e.emp_id,
e.emp_name,
d.dep_location,
e.salary,
d.dep_name
FROM employees e,
department d
WHERE e.dep_id=d.dep_id
AND e.dep_id IN
(SELECT dep_id
FROM employees
WHERE emp_name = 'KAYLING'
AND employees.emp_id <> e.emp_id);
Write a query in SQL to list the employees whose designation
is same as either the designation of ADELYN or the salary is
more than salary of WADE.
SELECT *
FROM employees
WHERE job_name =
(SELECT job_name
FROM employees
WHERE emp_name = 'ADELYN')
OR salary >
(SELECT salary
FROM employees
WHERE emp_name = 'WADE');
Write a query in SQL to list the department ID and their
average salaries for those department where the average
salary is less than the averages for all departments.
SELECT dep_id,
avg(salary)
FROM employees
GROUP BY dep_id
HAVING avg(salary) <
(SELECT avg(salary)
FROM employees);
Procedures
create table Emo(
id numeric,
salary numeric);
CREATE OR REPLACE PROCEDURE p1(id IN NUMBER, sal IN NUMBER)
AS
BEGIN
INSERT INTO emo VALUES(id, sal);
DBMS_OUTPUT.PUT_LINE('VALUE INSERTED.');
END;
exec p1(5,4);
select * from emo;
function
create or replace function getsal (no IN number) return number
is
sal number(2);
begin
select salary into sal from emo where id=no;
return sal;
end;
select getsal(5) from dual;
cursor
CREATE TABLE customer (
id INTEGER NOT NULL,
name VARCHAR(12),
age INTEGER,
Address VARCHAR(12),
Salary numeric(7,2));
INSERT INTO customer
VALUES (1,'Ramesh',32,'mumbai',2000);
INSERT INTO customer
VALUES (2,'dinesh',42,'delhi',40000);
INSERT INTO customer
VALUES (3,'Rajesh',35,'goa',7000);
select * from customer;
drop table customer;
DECLARE
total_rows number(2);
BEGIN
UPDATE customer
SET salary = salary + 500;
IF sql%notfound THEN
dbms_output.put_line('no customers selected');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers selected ');
END IF;
END;
DECLARE
c_id customer.id%type;
c_name customer.name%type;
c_addr customer.address%type;
CURSOR c_customer is
SELECT id, name, address FROM customer;
BEGIN
OPEN c_customer;
LOOP
FETCH c_customer into c_id, c_name, c_addr;
EXIT WHEN c_customer%notfound;
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
END LOOP;
CLOSE c_customer;
END;
https://www.wiseowl.co.uk/sql/exercises/standard/stored-procedures/
https://www.sqlshack.com/sql-server-stored-procedures-for-beginners/
https://data-flair.training/blogs/stored-procedure-in-sql/
https://www.datacamp.com/community/tutorials/introduction-indexing-
sql?
utm_source=adwords_ppc&utm_campaignid=1455363063&utm_adgroupid=65083
631748&utm_device=c&utm_keyword=&utm_matchtype=b&utm_network=g&utm_a
dpostion=&utm_creative=332602034364&utm_targetid=dsa-
429603003980&utm_loc_interest_ms=&utm_loc_physical_ms=9302184&gclid=
CjwKCAiAnIT9BRAmEiwANaoE1RA8sjbsSXVCO4lNutpvUrvdxkDBLW5HF3OgjsvLnufp
sJVvliypthoCgHgQAvD_BwE
indexing
https://www.youtube.com/watch?v=NgYiO6vu1zk
lab on procedure
