Practicals Reference

DDL

Commands:

• DROP
• CREATE
• ALTER
• RENAME
• TRUNCATE

-- DROP TABLE
drop table Hospital;
-- CREATE TABLE
create table Hospital(
    name varchar(30),
    expense number(10)
);
-- ALTER TABLE
alter table Hospital add(
    department varchar(30),
    contact_no number(10)
);
-- RENAME COLUMN
rename table contact_no to phone_no;
desc Hospital;
-- TRUNCATE TABLE
truncate table Hospital;

DML

Commands:

• DELETE
• UPDATE
• MERGE
• INSERT

-- DROP TABLE
drop table Employees;
drop table HR;
-- CREATE TABLE
create table Employees(
    name varchar(30),
    salary number(10),
    city varchar(15)
);
create table HR(
    emp_name varchar(30),
    HR_name varchar(30),
    HR_salary number(10)
);
-- INSERT VALUES
insert into Employees values('Rohan',400,'Mumbai');
insert into Employees values('Shera',800,'Pune');

insert into HR values('Shera','Punit',4000);
insert into HR values('Rohan','Sunil',5000);

select * from Employees;
-- DELETE ROW
delete from tejas_students where(salary < 500);
select * from Employees;
-- UPDATE TABLE
update Employees set salary = salary*1.1;
select * from Employees;

CONSTRAINTS

-- DROP TABLE
drop table University;
-- CREATE TABLE
create table University(
    ID number unique,
    name varchar(15) not null,
    city varchar(25) default 'Mumbai',
    age int,
    check (age>=18)
);
-- show insertion and select * ...

PRIMARY AND FOREIGN KEY

CREATE TABLE customers (
    customer_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    email VARCHAR2(100)
);
CREATE TABLE orders (
    order_id NUMBER PRIMARY KEY,
    customer_id NUMBER,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

INSERT INTO customers VALUES (1, 'John', 'Doe', 'john.doe@example.com');
INSERT INTO customers VALUES (2, 'Jane', 'Smith', 'jane.smith@example.com');

INSERT INTO orders (order_id, customer_id, order_date) VALUES (1001, 1);
INSERT INTO orders (order_id, customer_id, order_date) VALUES (1002, 2);

desc orders;
desc customers;

String Operations

DROP TABLE fruits;
-- Create a sample table
CREATE TABLE fruits(
    id NUMBER,
    name VARCHAR2(50)
);

-- Insert dummy values into the table
INSERT INTO fruits (id, name) VALUES (1, 'Apple');
INSERT INTO fruits (id, name) VALUES (2, 'Banana');
INSERT INTO fruits (id, name) VALUES (3, 'Avocado');
INSERT INTO fruits (id, name) VALUES (4, 'Cherry');
INSERT INTO fruits (id, name) VALUES (5, 'Grapes');
INSERT INTO fruits (id, name) VALUES (6, 'Kiwi');
INSERT INTO fruits (id, name) VALUES (7, 'Papaya');
INSERT INTO fruits (id, name) VALUES (8, 'Mango');
INSERT INTO fruits (id, name) VALUES (9, 'Orange');
INSERT INTO fruits (id, name) VALUES (10, 'Pineapple');

-- First SELECT statement to find records where the name starts with 'A'
SELECT *
FROM fruits
WHERE name LIKE 'A%';


-- Second SELECT statement to find records where the name ends with 'o'
SELECT *
FROM fruits
WHERE name LIKE '%o';

JOINS

DROP TABLE tejas_emp;
DROP TABLE tejas_sal;
-- creating a table Employees
CREATE TABLE tejas_emp(
    emp_id number(5),
    name varchar(30)
    );
DESC tejas_emp;
-- creating a table Salary
CREATE TABLE tejas_sal(
    emp_id number(5),
	bonus number(10),
    salary number(10)
    );
DESC tejas_sal;

INSERT INTO tejas_emp VALUES(
    12,
    'One'
);
INSERT INTO tejas_emp VALUES(
    14,
    'Two'
);
INSERT INTO tejas_emp VALUES(
    19,
    'Three'
);
INSERT INTO tejas_sal VALUES(
    12,
    200,
    2000
);
INSERT INTO tejas_sal VALUES(
    14,
    900,
    2800
);
INSERT INTO tejas_sal VALUES(
    19,
    1200,
    12000
);
SELECT * from tejas_sal;
SELECT * from tejas_emp;

-- CROSS JOIN
PRINT 'CROSS JOIN';
SELECT tejas_emp.emp_id,tejas_emp.name,tejas_sal.salary
FROM tejas_emp
CROSS JOIN tejas_sal;

-- NATURAL JOIN
PRINT 'NATURAL JOIN';
SELECT * FROM
tejas_emp NATURAL JOIN tejas_sal;

-- EQUI JOIN
PRINT 'EQUI JOIN';
SELECT * FROM
tejas_emp INNER JOIN tejas_sal
ON tejas_emp.emp_id=tejas_sal.emp_id;

-- OUTER JOINS
INSERT INTO tejas_sal VALUES(
    22,
    200,
    4000
);
INSERT INTO tejas_sal VALUES(
    89,
    1500,
    600
);
INSERT INTO tejas_emp VALUES(
    23,
    'foo'
);

-- LEFT OUTER
PRINT 'LEFT OUTER JOIN';
SELECT * FROM
tejas_emp LEFT OUTER JOIN tejas_sal
ON tejas_emp.emp_id=tejas_sal.emp_id;

-- RIGHT OUTER
PRINT 'RIGHT OUTER JOIN';
SELECT * FROM
tejas_emp RIGHT OUTER JOIN tejas_sal
ON tejas_emp.emp_id=tejas_sal.emp_id;

-- FULL OUTER JOIN
PRINT 'FULL OUTER JOIN';
SELECT * FROM
tejas_emp FULL OUTER JOIN tejas_sal
ON tejas_emp.emp_id=tejas_sal.emp_id;

VIEWS

DROP TABLE Employees;

CREATE TABLE Employees(
    emp_id number(3) PRIMARY KEY,
    name varchar(30),
    salary number(10)
);
INSERT INTO Employees VALUES(1,'Rohit',300);
INSERT INTO Employees VALUES(2,'Munawar',1200);

-- CREATING VIEW
CREATE VIEW Client AS
SELECT name,salary
FROM Employees;
-- SELECTING VIEW
SELECT* FROM Client;