Lab 1:
1. Creating the necessary tables for the identified database objects
using ORACLE live SQL.
CREATE TABLE DEPARTMENTS(
DEPT_NAME VARCHAR(50),
LOC VARCHAR2(14),
BUDGET NUMBER(7,2)
);
CREATE TABLE COURSES(
COURSE_ID VARCHAR(10),
TITLE VARCHAR(50),
DEPT_NAME VARCHAR(50),
CREDITS VARCHAR(50)
);
CREATE TABLE INSTRUCTORS(
INSTRUCTORS_ID VARCHAR2(50),
DEPT_NAME VARCHAR(50),
SALARY NUMBER(8,2)
);
CREATE TABLE STUDENTS(
STUDENT_ID VARCHAR2(50),
STUDENT_NAME VARCHAR2(50) NOT NULL,
DEPT_NAME VARCHAR(50),
TOTAL_CREDITS VARCHAR(50)
);
CREATE TABLE CLASSROOMS(
BUILDING_NAME VARCHAR(50),
ROOM_NO NUMBER(7),
ROOM_CAPACITY NUMBER(7,2)
);
CREATE TABLE SECTIONS(
COURSE_ID VARCHAR(50),
SEC_ID VARCHAR(50),
YEAR NUMBER(7),
SEMISTER NUMBER(8,2)
);
2. Insert proper values into the tables and display the records.
INSERT INTO DEPARTMENTS VALUES('SCIENCE','BANGALORE’, 2500);
INSERT INTO DEPARTMENTS VALUES('COMMERCE','BANGALORE', 3500);
INSERT INTO DEPARTMENTS VALUES('ARTS','BANGALORE', 2500);
INSERT INTO DEPARTMENTS VALUES('ENGLISH','BANGALORE', 2000);
INSERT INTO DEPARTMENTS VALUES('KANNADA','BANGALORE', 2000);
SELECT * FROM DEPARTMENTS;
INSERT INTO COURSES VALUES('SCI104','PHYSICS','SCIENCE',4);
INSERT INTO COURSES VALUES('COM220','FINANCIAL
MANAGEMENT','COMMERCE',4);
INSERT INTO COURSES VALUES('ART115','PHILOSOPHY','ARTS',4);
INSERT INTO COURSES VALUES('ENG142','GRAMMAR','ENGLISH',4);
INSERT INTO COURSES VALUES('KAN225','KANNADA','KANNADA',4);
SELECT * FROM COURSES;
INSERT INTO INSTRUCTORS VALUES('S253748','SCIENCE',50000);
INSERT INTO INSTRUCTORS VALUES('C453765','COMMERCE',50000);
INSERT INTO INSTRUCTORS VALUES('A6537635','ARTS',45000);
INSERT INTO INSTRUCTORS VALUES('E9873498','ENGLISH',40000);
INSERT INTO INSTRUCTORS VALUES('K654587','KANNADA',40000);
SELECT * FROM INSTRUCTORS;
INSERT INTO STUDENTS VALUES('SS1719215','JOHN','SCIENCE',4);
INSERT INTO STUDENTS VALUES('CS1718424','TOM','COMMERCE',4);
INSERT INTO STUDENTS VALUES('AS1716768','MARY','ARTS',4);
INSERT INTO STUDENTS VALUES('ES1817834','TEENA','ENGLISH',4);
INSERT INTO STUDENTS VALUES('KS1519627','RIA','KANNADA',4);
SELECT * FROM STUDENTS;
INSERT INTO CLASSROOMS VALUES('BLOCK A',1025,45);
INSERT INTO CLASSROOMS VALUES('BLOCK B',2145,50);
INSERT INTO CLASSROOMS VALUES('BLOCK C',1127,60);
INSERT INTO CLASSROOMS VALUES('BLOCK A',1254,45);
INSERT INTO CLASSROOMS VALUES('BLOCK B',1526,45);
SELECT * FROM CLASSROOMS;
INSERT INTO SECTIONS VALUES('SCI104','A6537',2019,4);
INSERT INTO SECTIONS VALUES('COM220','B4215',2016,3);
INSERT INTO SECTIONS VALUES('ART115','C5345',2018,4);
INSERT INTO SECTIONS VALUES('ENG142','A7284',2017,2);
INSERT INTO SECTIONS VALUES('KAN225','B3257',2017,5);
SELECT * FROM SECTIONS;
LAB 2
1. Implement the following Data Definition Language(DDL) SQL Commands
with
the examples of your choice
a. Create Table with the following constraints: NOT NULL, UNIQUE,
PRIMAY KEY, FOREIGN KEY, CHECK, DEFAULT
b. Alter Table for performing the following operations on the existing Table:
Add column, Drop column, Rename column, Add constraints, Drop
constraints
c. Truncate Table
d. Create View
e. Drop Table
CODES USED:
CREATE TABLE EMPLOYEES (
ID INT NOT NULL,
LASTNAME VARCHAR(255) NOT NULL,
FIRSTNAME VARCHAR(255) UNIQUE,
AGE INT,
PRIMARY KEY (ID),
CHECK (AGE>=18),
CITY VARCHAR(255) DEFAULT 'BANGALORE'
);
CREATE TABLE ORDERS (
ORDER_ID INT NOT NULL,
ORDER_NO INT NOT NULL,
EMPLOYEEID INT,
PRIMARY KEY (ORDER_ID),
FOREIGN KEY (EMPLOYEEID) REFERENCES EMPLOYEES(ID)
);
ALTER TABLE EMPLOYEES
ADD GENDER VARCHAR2(20);
ALTER TABLE EMPLOYEES
DROP COLUMN GENDER;
ALTER TABLE EMPLOYEES
RENAME COLUMN CITY TO LOCATON;
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
LASTNAME VARCHAR(255) NOT NULL,
FIRSTNAME VARCHAR(255) NOT NULL,
AGE INT
);
ALTER TABLE CUSTOMERS
ADD CONSTRAINT CK_CUSTOMER PRIMARY KEY (ID,LASTNAME);
ALTER TABLE CUSTOMERS
DROP PRIMARY KEY;
TRUNCATE TABLE CUSTOMERS;
CREATE VIEW DEPARTMENTS_HQ AS
SELECT DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID
FROM HR.DEPARTMENTS
WHERE LOCATION_ID = 1700
WITH CHECK OPTION CONSTRAINT DEPARTMENTS_HQ_CNS;
DROP TABLE CUSTOMERS;
Lab 3
Write the SQL statements to accomplish the following tasks:
(1) Create the following 3 tables for your online store. The primary keys are
underlined, and the foreign keys are shown in italic. You can personalize the
tables if needed to be fit to store the information you need for the products
you sell on your website.
PRODUCT(ProductID, PName, PDescription, Price) – this table records
information about each product.
SALE(SaleID, DeliveryAddress, CreditCard) – this table records information
about each sale/transaction.
SALEITEM(SaleID, ProductID, Quantity) – this table records information
about which products were sold in each sale/transaction
(2) Insert 5 records in each table.
(3) List all records from the PRODUCT, SALE and SALEITEM table.
(4) Update the price of one of your products in the PRODUCT table to cost
Rs.100 morethan original price.
(5) Delete all products with price higher than 10,000 from your PRODUCT
table.
Hint: You can insert such a product first and then use this command to delete
it for testing purposes
Codes used
CREATE TABLE PRODUCT (
PRODUCT_ID INT NOT NULL,
PNAME VARCHAR(255) NOT NULL,
PDESCRIPTION VARCHAR(255) NOT NULL,
PRICE INT,
PRIMARY KEY (PRODUCT_ID)
);
CREATE TABLE SALE (
SALE_ID INT NOT NULL,
DELIVERYADDRESS VARCHAR(255) NOT NULL,
CREDITCARD VARCHAR(255) NOT NULL,
PRIMARY KEY (SALE_ID)
);
CREATE TABLE SALEITEM (
SALEID INT,
PRODUCTID INT,
FOREIGN KEY (SALEID) REFERENCES SALE(SALE_ID),
FOREIGN KEY (PRODUCTID) REFERENCES PRODUCT(PRODUCT_ID),
QUANTITY INT
);
INSERT INTO PRODUCT VALUES(1719215,'SANITIZER','NO MORE
BACTERIA',650);
INSERT INTO PRODUCT VALUES(6773425,'MASK','3 LAYERS',300);
INSERT INTO PRODUCT VALUES(6534787,'EYE DROPPER','CHEMICAL
FREE',1350);
INSERT INTO PRODUCT VALUES(9877865,'MOUTH SPRAY','ANTIB
ACTERIAL',5000);
INSERT INTO PRODUCT VALUES(7453554,'MOUTH WASH','ANTI
SEPTIC',1500);
SELECT * FROM PRODUCT;
INSERT INTO SALE VALUES(5487375,'BANGALORE','YES');
INSERT INTO SALE VALUES(7538275,'HYDERABAD','YES');
INSERT INTO SALE VALUES(4638979,'CHENNAI','NO');
INSERT INTO SALE VALUES(6543627,'BANGALORE','YES');
INSERT INTO SALE VALUES(2423856,'MUMBAI','NO');
SELECT * FROM SALE;
INSERT INTO SALEITEM VALUES(5487375, 1719215, 10);
INSERT INTO SALEITEM VALUES(7538275, 6773425, 20);
INSERT INTO SALEITEM VALUES(4638979, 6534787, 15);
INSERT INTO SALEITEM VALUES(6543627, 9877865, 25);
INSERT INTO SALEITEM VALUES(2423856, 7453554, 15);
SELECT * FROM SALEITEM;
UPDATE PRODUCT
SET PRICE = 3000+100
WHERE PRODUCT_ID = 6773425;
SELECT * FROM PRODUCT;
INSERT INTO PRODUCT VALUES(6242734,'VANISH','POUR RUB
POUT',15000);
SELECT * FROM PRODUCT;
DELETE FROM PRODUCT
WHERE PRICE > 10000;
SELECT * FROM PRODUCT;
LAB 4
(1) With the example relations of your choice, demonstrate
the following:
(a) Aggregate Functions:
Average: avg
Minimum: Min
Maximum: Max
Total: sum
Count: count
(b) Set Operations:
union
intersect
except
(c) Nested Subqueries
Codes used
CREATE TABLE PRODUCT (
PRODUCTID INT NOT NULL,
PNAME VARCHAR(255) NOT NULL,
PDESCRIPTION VARCHAR(255) NOT NULL,
PRICE INT,
PRIMARY KEY (PRODUCTID)
);
INSERT INTO PRODUCT VALUES(1719215,'SANITIZER','NO MORE
BACTERIA',650);
INSERT INTO PRODUCT VALUES(6773425,'MASK','3 LAYERS',300);
INSERT INTO PRODUCT VALUES(6534787,'EYE DROPPER','CHEMICAL
FREE',1350);
INSERT INTO PRODUCT VALUES(9877865,'MOUTH SPRAY','ANTIB
ACTERIAL',5000);
INSERT INTO PRODUCT VALUES(7453554,'MOUTH WASH','ANTI
SEPTIC',1500);
SELECT * FROM PRODUCT;
CREATE TABLE SALE (
SALEID INT NOT NULL,
PRODUCTID INT NOT NULL,
PNAME VARCHAR(255) NOT NULL,
DELIVERYADDRESS VARCHAR(255) NOT NULL,
CREDITCARD VARCHAR(255) NOT NULL,
PRIMARY KEY (SALEID)
);
INSERT INTO SALE
VALUES(5487375,1719215,'SANITIZER','BANGALORE','YES');
INSERT INTO SALE
VALUES(7538275,6325737,'MASK','HYDERABAD','YES');
INSERT INTO SALE VALUES(4638979,6534787,'MOUTH
WASH','CHENNAI','NO');
INSERT INTO SALE
VALUES(6543627,3542567,'MASK','BANGALORE','YES');
INSERT INTO SALE VALUES(2423856,7453554,'EYE
DROPPER','MUMBAI','NO');
SELECT * FROM SALE;
SELECT AVG(PRICE)
FROM PRODUCT;
SELECT MIN(PRICE) AS SMALLESTPRICE
FROM PRODUCT;
SELECT MAX(PRICE) AS LARGESTPRICE
FROM PRODUCT;
SELECT SUM(PRICE)
FROM PRODUCT;
SELECT COUNT(PRICE)
FROM PRODUCT;
SELECT PRODUCTID FROM PRODUCT
UNION
SELECT PRODUCTID FROM SALE
ORDER BY PRODUCTID;
SELECT PRODUCTID FROM PRODUCT
INTERSECT
SELECT PRODUCTID FROM SALE
ORDER BY PRODUCTID;
SELECT PRODUCTID FROM SALE
MINUS
SELECT PRODUCTID FROM PRODUCT
ORDER BY PRODUCTID;
SELECT AVG(PRICE)
FROM PRODUCT;
SELECT *
FROM PRODUCT
WHERE PRICE > 1760;
SELECT *
FROM PRODUCT
WHERE PRICE > (
SELECT AVG(PRICE)
FROM PRODUCT);
SELECT *
FROM SALE
WHERE PRODUCTID = (
SELECT PRODUCTID
FROM PRODUCT
WHERE PRICE = (
SELECT MAX(PRICE)
FROM PRODUCT));
LAB 5
(1) With an example scenario of your choice, demonstrate
the following Join expressions:
a. left outer join
b. right outer join
c. full outer join
d. inner join
Optional Question:
(2) Demonstrate the implementation of Assertions and
Triggers in SQL with an example queries.
Codes used
CREATE TABLE PRODUCT (
PRODUCTID INT NOT NULL,
PNAME VARCHAR(255) NOT NULL,
PDESCRIPTION VARCHAR(255) NOT NULL,
PRICE INT,
PRIMARY KEY (PRODUCTID)
);
INSERT INTO PRODUCT VALUES(1719215,'SANITIZER','NO MORE
BACTERIA',650);
INSERT INTO PRODUCT VALUES(6773425,'MASK','3 LAYERS',300);
INSERT INTO PRODUCT VALUES(6534787,'EYE DROPPER','CHEMICAL
FREE',1350);
INSERT INTO PRODUCT VALUES(9877865,'MOUTH SPRAY','ANTIB
ACTERIAL',5000);
INSERT INTO PRODUCT VALUES(7453554,'MOUTH WASH','ANTI
SEPTIC',1500);
INSERT INTO PRODUCT VALUES(7548548,'LIQUID WASH','USER
FRIENDLY',1200);
INSERT INTO PRODUCT VALUES(5457328,'SPRAY','ANTI SEPTIC',1000);
SELECT * FROM PRODUCT;
CREATE TABLE SALE (
SALEID INT NOT NULL,
PRODUCTID INT NOT NULL,
PNAME VARCHAR(255) NOT NULL,
DELIVERYADDRESS VARCHAR(255) NOT NULL,
CREDITCARD VARCHAR(255) NOT NULL,
PRIMARY KEY (SALEID)
);
INSERT INTO SALE
VALUES(5487375,1719215,'SANITIZER','BANGALORE','YES');
INSERT INTO SALE
VALUES(7538275,6773425,'MASK','HYDERABAD','YES');
INSERT INTO SALE VALUES(4638979,6534787,'MOUTH
WASH','CHENNAI','NO');
INSERT INTO SALE
VALUES(6543627,9877865,'MASK','BANGALORE','YES');
INSERT INTO SALE VALUES(2423856,7453554,'EYE
DROPS','MUMBAI','NO');
INSERT INTO SALE VALUES(7653873,7568756,'MOUTH
FRESHNER','KOLKATA','YES');
INSERT INTO SALE VALUES(9768769,7659870,'EAR
DROPS','MUMBAI','YES');
INSERT INTO SALE VALUES(6547589,3424358,'ENERGY
BOOSTER','CHENNAI','NO');
SELECT * FROM SALE;
SELECT PRODUCT. PRODUCTID, PRODUCT. PNAME, SALE. SALEID
FROM PRODUCT LEFT JOIN SALE
ON PRODUCT. PRODUCTID = SALE. PRODUCTID;
SELECT PRODUCT. PRODUCTID, PRODUCT. PNAME, SALE. SALEID
FROM PRODUCT RIGHT JOIN SALE
ON PRODUCT. PRODUCTID = SALE. PRODUCTID;
SELECT PRODUCT. PRODUCTID, PRODUCT. PNAME, SALE. SALEID
FROM PRODUCT FULL JOIN SALE
ON PRODUCT. PRODUCTID = SALE. PRODUCTID;
SELECT PRODUCT. PRODUCTID, PRODUCT. PNAME, SALE. SALEID
FROM PRODUCT INNER JOIN SALE
ON PRODUCT. PRODUCTID = SALE. PRODUCTID;
SELECT *
FROM PRODUCT RIGHT JOIN SALE
ON PRODUCT. PRODUCTID = SALE. PRODUCTID;
SELECT *
FROM PRODUCT FULL JOIN SALE
ON PRODUCT. PRODUCTID = SALE. PRODUCTID;
CREATE OR REPLACE TRIGGER DISPLAY_PRICE_CHANGES
BEFORE DELETE OR INSERT OR UPDATE ON PRODUCT
FOR EACH ROW
WHEN (NEW.PRODUCTID > 0)
DECLARE
PRICE_DIFF NUMBER;
BEGIN
PRICE_DIFF := :NEW.PRICE - :OLD.PRICE;
DBMS_OUTPUT.PUT_LINE('OLD PRICE: ' || :OLD.PRICE);
DBMS_OUTPUT.PUT_LINE('NEW PRICE: ' || :NEW.PRICE);
DBMS_OUTPUT.PUT_LINE('PRICE DIFFERENCE: ' || PRICE_DIFF);
END;
INSERT INTO PRODUCT (PRODUCTID,PNAME,PDESCRIPTION,PRICE)
VALUES (4665743, 'GEL', 'JELLY', 2500);
UPDATE PRODUCT
SET PRICE = PRICE + 500
WHERE PRODUCTID = 6773425;
